IsStringsPreserved bug

Aspose.Cells Product Family
1

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

2

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);

3

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

4

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);

5

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

6

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);

7

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

8

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.

9

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

10

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);

11

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

Olivier