Spire.Xls : String value getting converted to Number

Fri Sep 18, 2015 3:07 pm

Hi Team,

While writing a string to excel, it is getting converted to number. for example, string value "2015090433791826" is getting converted to "2.01509E+15" in the generated excel.

Attached is the generated excel

Can you please suggest a way I can write a string which looks like a NUMBER as a string only.

Really Appreciate your response on it.

Mon Sep 21, 2015 2:20 am

Hello,

Thanks for your inquiry.
Please try the below code:

Code: Select all: workbook.Worksheets[0].Range["A1"].Text = "2015090433791830";

Best wishes,
Amy
E-iceblue support team

Mon Sep 21, 2015 7:44 am

Hi Amy,

I am loading data into the worksheet using a datatable using method sheet.InsertDataTable. How can I use the below line with this method.

I would really appreciate your help on this.

Mon Sep 21, 2015 8:05 am

Hello,

You only need to set its NumberFormat after inserting data.

Code: Select all: workbook.Worksheets[0].Range["A2"].NumberFormat = "0";

Best wishes,
Amy
E-iceblue support team

Mon Sep 21, 2015 8:10 am

Hi Amy,

Thanks for your response. But adding the number format to "0" causes the rounding in the string for example

"2015090433791826" is getting converted to "2015090433791830"

Mon Sep 21, 2015 8:53 am

Hello,

Please use this method workbook.Worksheets[0].InsertDataTable(dt, true, 1, 1,true); to insert dataTable.

Best wishes,
Amy
E-iceblue support team

Mon Sep 21, 2015 2:11 pm

Hi Amy,

Thanks a lot for your prompt responses.
This has fixed the earlier issue however the excel is showing a warning for all such cells that "number is stored as text". Is there a property by which I can suppress these warnings?

Thanks a lot once again.

Manoj

Tue Sep 22, 2015 1:24 am

Hello Manoj,

Please set cellRange's IgnoreErrorOptions property, like this code:

Code: Select all: workbook.Worksheets[0].Range["A2"].IgnoreErrorOptions = IgnoreErrorType.NumberAsText;

Best wishes,
Amy
E-iceblue support team

Wed Sep 23, 2015 6:39 am

Hello Manoj,

Has your problem been resolved?
Thanks for your feedback.

Best wishes,
Amy
E-iceblue support team