Excel cell value gets rounded off

Hi -- I have 18 digit number that i need to show in excel but in excel that number gets rounded off and also if number more than 15 digit means excel will show 0 for after 15 digits so, how can i show the exponential value to original value in excel.

Workbook wb = new Workbook();

Worksheet sheet = wb.Worksheets[0];

Cell cell = sheet.Cells["A1"];


cell.PutValue("999999999999999999", true, true);

Style style = cell.GetStyle();

style.Custom = "0"; // decimal value 0
cell.SetStyle(style, true);

//Save the Excel file.
wb.Save("c:\\MyBook.xls", SaveFormat.Excel97To2003);


Thanks,
Gowtham

Hi Gowtham,


Thank you for contacting Aspose support.

Please note, it is the default behaviour of Excel application that it turns the numeric values to exponential notation as soon as the length of the value passes the threshold. You can only avoid this situation by treating the value as text, this way, Excel will keep the original value as it is. In order to insert a text values, the most appropriate way is to pass false as second argument (isConverted) to the Cell.PutValue method. Please check following piece of code and its resultant spreadsheet.

C#

Workbook wb = new Workbook();
Worksheet sheet = wb.Worksheets[0];
Cell cell = sheet.Cells[“A1”];
cell.PutValue(“999999999999999999”, false, true);
wb.Save(dir + “MyBook.xls”, SaveFormat.Excel97To2003);

Hi Babar Raza – From Html only i am getting value (html to excel conversion) so, while gettting value from the cell (below) i am getting exponential value (1E+18)


Cell cell = workbook.Worksheets[range.Worksheet.Name].Cells[range.FirstRow, range.FirstColumn];

after that i am setting custom style as “0” for decimal value (convert exponential to number).If i tried to convert text means excel have exponential value only as (1E+18)
Am i doing anything wrong. please guide me. Thanks


Hi Gowtham,


What I understand is that you are loading the HTML and converting it to spreadsheet format. If this is correct, could you please share the sample HTML for investigation. By the way, if your input HTML contains the numeric value as Text, you can set the HTMLLoadOptions.ConvertNumericData property to false in order to avoid the conversion to numeric values at the time of loading.

Hi Babar Raza – I created a example please see below and You told to use HtmlLoadOptions.ConvertNumericData property but I read aspose below url document it says HTML file should Microsoft Excel oriented but in my scenario html is not Microsoft excel oriented. In the below example you will see the values get rounded off.


URL : Open Different Microsoft Excel Versions Files|Documentation

string html = @“
<table data-cache=”“not-cached”" class=““sortable””>
<td class=““even””>999999999999999999
";

// aspose XLS prep
byte[] byteArray = Encoding.UTF8.GetBytes(html);
Aspose.Cells.LoadOptions loadOptions = new Aspose.Cells.LoadOptions(LoadFormat.Html);
MemoryStream stream = new MemoryStream(byteArray);
Workbook workbook = new Workbook(stream, loadOptions);
Worksheet sheet = workbook.Worksheets[0];
sheet.AutoFitColumns();
Aspose.Cells.Style style = workbook.Worksheets[0].Cells[0].GetStyle();
style.Custom = “0”;
workbook.Worksheets[0].Cells[0].SetStyle(style, true);
Response.AppendHeader(“content-disposition”, “attachment; filename=export.xls”);
Response.ContentType = “application/vnd.ms-excel; name=export.xls”;
MemoryStream outstream = new MemoryStream();
workbook.Save(outstream, SaveFormat.Excel97To2003);
Response.BinaryWrite(outstream.ToArray());


Thanks,
Gowtham

Hi Gowtham,


Thank you for sharing the sample code.

I have evaluated the scenario on my side and I was not able to get the desired results therefore I have raised these concerns with the product team by logging a ticket with Id CELLSNET-44348. Please spare us little time to further look into the matter and get back with updates in this regard.

Hi,

Thanks for using Aspose.Cells.

We will provide you a fix soon.

FYI:
Please use HTMLLoadOptions as load options. Please see the following code:


C#

HTMLLoadOptions loadOptions = new Aspose.Cells.HTMLLoadOptions(LoadFormat.Html);
loadOptions.ConvertNumericData = false;
MemoryStream stream = new MemoryStream(byteArray);
Workbook workbook = new Workbook(stream, loadOptions);
Worksheet sheet = workbook.Worksheets[0];

We have fixed the issue that LoadOptions.ConvertNumericData does not
effect loading html file and we have obsoleted the
LoadOptions.ConvertNumericData property too.

Hi Shakeel Faiz – I am able to see the huge number as text instead of Exponential number after using your code but I have another issue. In my html have both numbers and text. After using above code all numbers are treated as text.

In below html I have two td. I want only 1st td (99999999…) as text next td as number but if i add

loadOptions.ConvertNumericData = false; then all numbers are treated as text. I have uploaded my .xls file output.

string html = @"

<table data-cache="“not-cached”" class="“sortable”">

<td class="“even”">999999999999999999

<td class="“odd”">10.8%

";

byte[] byteArray = Encoding.UTF8.GetBytes(html);

HTMLLoadOptions loadOptions = new Aspose.Cells.HTMLLoadOptions(LoadFormat.Html);

loadOptions.ConvertNumericData = false;

MemoryStream stream = new MemoryStream(byteArray);

Workbook workbook = new Workbook(stream, loadOptions);

Worksheet sheet = workbook.Worksheets[0];

sheet.AutoFitColumns();

Aspose.Cells.Style style = workbook.Worksheets[0].Cells[0].GetStyle();

style.Custom = “0”;

workbook.Worksheets[0].Cells[0].SetStyle(style, true);

// write to response

//Response.AppendHeader(“content-disposition”, “attachment; filename=export.xlsx”);

//Response.ContentType = “application/vnd.ms-excel; name=export.xlsx”;

Response.AppendHeader(“content-disposition”, “attachment; filename=export.xls”);

Response.ContentType = “application/vnd.ms-excel; name=export.xls”;

MemoryStream outstream = new MemoryStream();

workbook.Save(outstream, SaveFormat.Excel97To2003);

Response.BinaryWrite(outstream.ToArray());

Thanks,

Gowtham

Hi Gowtham,


Thank you for writing back.

Please note, it is the correct behaviour of ConvertNumericData property that when set to false, all the numeric values will be treated as text. If you have more data that you do not wish to be represented as Text, you have to perform a few additional steps. Please check the following piece of code which re-inserts the value of B1 as numeric and set the format to percentage.

C#

string html = @"
<table data-cache="“not-cached”" class="“sortable”">


<td class="“even”">999999999999999999
<td class="“odd”">10.8%



";

byte[] byteArray = Encoding.UTF8.GetBytes(html);
HTMLLoadOptions loadOptions = new Aspose.Cells.HTMLLoadOptions(LoadFormat.Html);
loadOptions.ConvertNumericData = false;
MemoryStream stream = new MemoryStream(byteArray);
Workbook workbook = new Workbook(stream, loadOptions);
Worksheet sheet = workbook.Worksheets[0];

Aspose.Cells.Style style = workbook.CreateStyle();
style.Custom = “0”;
workbook.Worksheets[0].Cells[“A1”].SetStyle(style, new StyleFlag() { NumberFormat = true});
style.Custom = “0.0%”;
workbook.Worksheets[0].Cells[“B1”].PutValue(workbook.Worksheets[0].Cells[“B1”].StringValue, true);
workbook.Worksheets[0].Cells[“B1”].SetStyle(style, new StyleFlag() { NumberFormat = true });
sheet.AutoFitColumns();

Hi Babar Raza – I tried the code which you gave and works perfectly. I have 500 records and 8 columns and I want only one (1) columns need to be text which have long digits. Is there any possible way to say this column format as text before putting values. Thanks.

Hi Gowtham,


Thank you for the confirmation on provided solution. Please note, this solution is viable for small matrix (rows x columns) however, if you wish to import larger matrix and perform the same operation then it may impose performance penalty. Based on this scenario I had a discussion with the product team and we have concluded to provide an option that could allow to retain the large numeric values as they are while importing the data to spreadsheet formats. This way, you will be able to control the conversion of long numeric values to exponential notation. The said feature request has been logged as CELLSNET-44376. Please spare us little time for feasibility analysis and implementation. In the meanwhile, we will keep you posted with updates in this regard.
Hi again,

This is to update you that we have resolved the ticket logged earlier as CELLSNET-44376. We will shortly share the fix here after ensuring the quality and incorporating other enhancements.

Hi,

Thanks for your using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells for .NET (Latest Version) and let us know your feedback.

Hi Gowtham,


Please use the HTMLLoadOptions.KeepPrecision property in order to control the parsing of string value having length more than 15. Please also check the following piece of code and attached resultant spreadsheet for your reference. Give it a try against the latest version of Aspose.Cells for .NET 8.7.2.5 (shared in our previous response) to feed us back.

C#

string html = @"
<table data-cache="“not-cached”" class="“sortable”">


<td class="“even”">9999999999999999
<td class="“odd”">10.8%



";

byte[] byteArray = Encoding.UTF8.GetBytes(html);
HTMLLoadOptions loadOptions = new Aspose.Cells.HTMLLoadOptions(LoadFormat.Html);
loadOptions.KeepPrecision = true;
MemoryStream stream = new MemoryStream(byteArray);
Workbook workbook = new Workbook(stream, loadOptions);
Worksheet sheet = workbook.Worksheets[0];
sheet.AutoFitColumns();
workbook.Save(dir + “output.xlsx”);

The issues you have found earlier (filed as CELLSNET-44376;CELLSNET-44348) have been fixed in this update.


This message was posted using Notification2Forum from Downloads module by Aspose Notifier.

Thanks. I am able to see the desired output after I get the latest Aspose.Cells.dll (.NET 8.7.2.5 ).

Hi Gowtham,


Thank you for the confirmation. It is good to know that you are up & running. Please feel free to contact us back in case you need our further assistance with Aspose APIs.