Microsoft Excel has a tendency to display number format based columns with no prefix of ZEROs. For example, if there is a value ‘007’ excel displays it as ‘7’, excel display it as ‘007’ only if the column is set in text format. You can see the difference in the below screenshot
Please go through the below URL for an example to generate an XMLP report:
But if you observe the data in the emp table, you could see three rows have leading zeros for employee number( Please note, I have updated the data of seeded emp table with prefix of 00 and also changed the data type of empno column to varchar2(6) for the sake of example.)
As the output is of excel type the empno column is considered as Number column (as the entire column consists of number format data) and the leading zeros are removed in display.
We have multiple ways to resolve this, I have listed them below.
- Open the RTF Template in MS Word.
- Go to Data -> Load XML Data.
- Once the data is loaded successfully, double click on that field.
- Under field properties window set the field formatting type as “Regular Text” and set the check box “Force LTR”.
This is the best method I have found, In this method we need to use an equal-to symbol before the field and enclose the field in double quotes for example:- =”ENUM”
The theory is that excel will concatenate the values together because it has quotes around it, it will treat it as a string rather than simply a value.
You can see in the above screenshot that Enum column values are turned to blue color as the content is explicit converted to text format from number format.
Hope this article is useful for those who are in need to show leading zeros for number columns in XMLP reports. If you have any best solution, please leave a comment and share with the readers.
Credits:- Thanks to my friend Kiran Reddy for helping me to get this knowledge.
UPDATE: on 20-OCT-2015
Here is another way to set format style ‘Force LTR’ in a programmatic way to prevent Excel from suppressing leading zeros:
<fo:bidi-override direction="ltr" unicode-bidi="bidi-override"> <?XML_ELEMENT?> </fo:bidi-override>