Exporting telephone numbers to Excel and the leading 0 is being dropped

[George Bamber]

Hi

I'm exporting data to an Excel file I'm creating using the XlsIO control.

For some reason, sometimes the leading 0 is being dropped off the phone numbers. It doesn't make sense why some cells have the correct number and others not.

I've tried everything that I can think of but I'm now stumped.

Any ideas what I need to do get the phone numbers correct?

Thanks

George  

[Konduru Keerthi Konduru Ravichandra Raju]

Hi Geroge, 

Greetings from Syncfusion. 

Leading zeros will be omitted from the value in Excel cell and this is the behavior of Microsoft Excel. To maintain the leading zeros, we suggest you to first set the number format as “Text” and then assign the value to Excel cell. 

Code Snippet: 

//Assign the number format as "Text" and then set a number with leading zero 

worksheet.Range["A2"].NumberFormat = "@"; 

worksheet.Range["A2"].Value = "0123456789"; 

We have prepared a simple for you and the same can be downloaded from below link. 

Sample Link: https://www.syncfusion.com/downloads/support/forum/164736/ze/F164736_Console-1366816011.zip  

Kindly try the suggestion and let us know if this helps. 

Regards, 

Keerthi. 

[George Bamber]

Hi Keerthi

Thank you for the example. I was using SetValueRowCol and strangely that seems to ignores the NumberFormat setting.

Changing to using Range[].Value fixes my problem.

Not sure if that means SetValueRowCol has a bug in it? Using your sample but changing it to use SetValueRowCol removes the leading 0.

Kind regards

George

[Konduru Keerthi Konduru Ravichandra Raju]

Hi George, 

Thanks for the update. 

We are able to reproduce the reported issue at our end and validating it currently. We will share the validation details in 2 business days, on April 27^th,2021. 

Regards, 

Keerthi. 

[Konduru Keerthi Konduru Ravichandra Raju]

Hi George, 

We appreciate your patience. 

The SetValueRowCol method will try to detect type of data and set the value in Excel cell accordingly. While doing that the zero is skipped and considered as number. Whereas IRange.Value will consider number formats, so it was works as expected. SetValueRowCol will not consider number formats. 

So, we suggest you set the number format as “0000000000” to get the leading zeros as expected. 

worksheet.Range["A2"].NumberFormat = "0000000000";  

Regards, 

Keerthi.