We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

IsStringsPreserved bug

Hi,


I am currently using Aspose.Cells version 4.0.1.0 for reading an excel file.
In that file, I have a cell containing a value like 541453102167600000. The issue is Cell.StringValue return me a Number with scientific notation whereas I would like to read it as a string value.
I've tried to set the IsStringsPreserved property to true in the Cells parent object with no success.
I've tried also to set the Style.Number = 49 on the cell before reading the value but with neither success

Could you please provide me a solution, but be aware that I can't have touch the original file (so no possibility to add a single quote in front of the value or a @)

Thanks,

Olivier

Hi Olivier,

Please try:

Workbook wb = new Workbook();
Cell cell = wb.Worksheets[0].Cells["A1"];
cell.PutValue(541453102167600000);
cell.Style.Custom = "0";
Console.WriteLine(cell.StringValue);

Thanks for your solution.
However it doesn't work when the number starts with some 0 (eg: 001453102167600000)
The begininning 0 are removed. Do you have a fix?

Regards,

Olivier

Hi Oliver,

This is not a problem, but a .net routine to format string. To get your expected string, you have to set correct format.

Please try:

Workbook wb = new Workbook();
Cell cell = wb.Worksheets[0].Cells["A1"];
cell.PutValue(001453102167600000);
cell.Style.Custom = "000000000000000000";
Console.WriteLine(cell.StringValue);

I am soory to say it didn't fix the issue since for some reasons the last 4 digits are rounded for unknown reasons. I invite to make a test with the following number:
001453106155031916 becomes 001453106155031000 and with the custom template you gave me (000000000000000000).

Thanks for your support

Olivier

Double value is not accurate enough to show all the digits. You can try:

Workbook wb = new Workbook();
Cell cell = wb.Worksheets[0].Cells["A1"];

decimal number = 001453106155031916;

cell.PutValue(number.ToString("000000000000000000"));
Console.WriteLine(cell.StringValue);

Don't forget that I am reading a file and not creating a file. What I found strange is that the cell interprets its value in any case as a number whereas I would expect a string. I was thinking it was the purpose of the IsStringsPreserved property or the Style.Number = 49 case. Do you have a solution, for reading xls file?

Thanks for your effort,

Olivier

IsStringPreserved property is used to save the number to file as string. To read a cell value as string, just use Cell.StringValue property.

However, a number can be converted to a string in different ways depending on the number format.

To serve your need, please try:

Workbook wb = new Workbook();
wb.Open("d:\\test\\book1.xls");
Cell cell = wb.Worksheets[0].Cells["A1"];

Console.WriteLine(cell.StringValue);

I attached the sample file here. If it still can serve your need, please post a sample file here. Then I can see how to make it.

Your example works because the cell number format is set to Text in your Excel file. But in my case I am opening a CSV file where no format are specified. I am able to not alter the value in Excel if I use the Import Text File (Excel 2000) wizard and specified the cell format to Text. I was hoping that I could the same kind of operation with your component. Is it possible?

Regards,

Olivier

Hi Olivier,

I think now I understand your need. Please try:

Workbook wb = new Workbook();

wb.ConvertNumericData = false;

wb.Open("d:\\test\\book1.csv", FileFormatType.CSV);

Thanks, it was indeed my solution.
Thanks again for your support.

Olivier