Export to Excel: preserve leading zero
Hi,
We're exporting reports to Excel, which works nicely. However, in the GUI of our application we have a text field in which the end user inputs a string like "027". When exporting this text, Excel (or Report Builder) ommits the leading zero, so it shows up as "27".
How can we preserve the leading zero in the report? Can this be done?
In Excel, when you input a number starting with a single quote, the value of the cell is treated as a string. Unfortunately this trick does not work if you do this from Report Builder.
We're exporting reports to Excel, which works nicely. However, in the GUI of our application we have a text field in which the end user inputs a string like "027". When exporting this text, Excel (or Report Builder) ommits the leading zero, so it shows up as "27".
How can we preserve the leading zero in the report? Can this be done?
In Excel, when you input a number starting with a single quote, the value of the cell is treated as a string. Unfortunately this trick does not work if you do this from Report Builder.
Comments
Hi Martin,
RB exports the data, data type, and format as a separate cell attributes. This provides the best quality output. For DBText the field data type is used, for Variable the variable data type is used. For Label, text data type is used.
I tested with DBText connected to integer field and with Variable set to integer data type. I specified display format of 000000;-000000 so that all numbers are displayed with 6 digits, for example 000027 and this is exported to Excel correctly - a number with a display format. I also tested with Label, I set caption to 000027 and this is correctly exported as text. In both tests the leading zeros are displayed in Excel.
Best regards,
Nard Moseley
Digital Metaphors
www.digital-metaphors.com