Losing precision when saving workbook

Aspose.Cells Product Family
1

I have a Workbook containing double values, and I want to keep the precision of those values when saving. I notice that when I save the sheet to csv format (haven’t tried other formats), Aspose Cells uses the StringValue of that cell when saving. This strips some decimals from the output, and I don’t want that.


For example:
A cell with Value: 1.42857142857143 will have StringValue 1,428571429
I want Aspose to use Value.ToSring() when saving, instead of StringValue.

Is there any way to accomplish that?
2

Hi,


Thanks for your query.

Well, it might be MS Excel’s behavior regarding displayed numeric values with longer decimal places. Aspose.Cells works the similar way as MS Excel does. Anyways, could you provide us your sample code (runnable) and template files (XLSX, CSV etc.), we will it soon.

Thank you.
3

Hi Ronald,


Thank you for contacting Aspose support.

As mentioned by Amjad, it is Excel’s behavior to use the display values while exporting the spreadsheets to CSV format therefore it may not be overridden using the Aspose.Cells APIs alone at moment. However, if you wish to export all original values to CSV then you may use the following combination of Aspose.Cells & .NET’s System.Text. Please check the attached archive for resultant CSV and feel free to tweak the code to meet your exact requirements. In case you still face any difficulty, please share the samples (spreadsheets & code) as requested in our previous response.

C#

Workbook book = new Workbook(“C:/temp/book1.xlsx”);
Worksheet sheet = book.Worksheets[0];
DataTable table = sheet.Cells.ExportDataTable(0, 0, sheet.Cells.MaxDataRow + 1, sheet.Cells.MaxDataColumn + 1, new ExportTableOptions());

StringBuilder sb = new StringBuilder();
IEnumerable<string> columnNames = table.Columns.Cast().
Select(column => column.ColumnName);
sb.AppendLine(string.Join(",", columnNames));
foreach (DataRow row in table.Rows)
{
IEnumerable<string> fields = row.ItemArray.Select(field => field.ToString());
sb.AppendLine(string.Join(",", fields));
}
File.WriteAllText(“C:/temp/test.csv”, sb.ToString());
4

Thanks Babar and Amjad for the swift responses.


Too bad excel forces the use of display values when exporting to CSV. I hoped to avoid a workaround. Since I use these CSV files for unit testing, comparing stored calculation results to current results, I need them to match exactly.

Thanks for the demo code, it will help me get started.

Ronald
5

Hi,

Thanks for using Aspose.Cells.

Please check if the following workaround suits your needs. It converts the value of cell A1 to string and then saves to csv and the output csv does not lose precision.

I have attached the source excel file used in this code and output csv file for your reference.

C#
string filePath = @“F:\Shak-Data-RW\Downloads\source.xlsx”;

Workbook workbook = new Workbook(filePath);

Worksheet worksheet = workbook.Worksheets[0];

Cell cell = worksheet.Cells[“A1”];
cell.PutValue(cell.StringValueWithoutFormat);

workbook.Save(“output.csv”);

6

Thanks Shakeel for the sample code, but then I would have to store every double value as a string in my workbook. I prefer to keep them as numeric values.


I guess saving as CSV has just been a bad choice for this purpose. We should have gone for excel format instead. Maybe we’ll change to that.

Thanks again for the great support, good people of Aspose!
7

Hi,

Thanks for your feedback and using Aspose.Cells.

Newer excel formats like xls and xlsx is definitely a good choice than older format like csv.

However, when you load your workbook, it does not change your existing workbook on the disk. All changes are done inside the memory and then you save it in csv format. So nothing happens to a workbook present on hard drive. It means, you will never have to store your double value as a string inside your workbook on hard drive. All conversion of values into string will happen on runtime. Thanks for your kind comment and appreciation. Best wishes.