Hi Teams,
Greetings of the day!
We have recently upgraded to Aspose.Cells version 26.2.0 for .NET 9.
We have below code
ExportTableOptions options = new ExportTableOptions();
options.CheckMixedValueType = true;
DataTable dataGIAttribute = dataSheet.Cells.ExportDataTable(0, firstColumn - 1, maxDataRow + 1, maxDataColumn - firstColumn + 2, options);
DataTable globalTemplateField = dataSheet.Cells.ExportDataTable(firstFieldRow, firstColumn - 1, maxDataRow - firstFieldRow + 1, maxDataColumn - firstColumn + 2, options);
Then I am reading the values down the line with below code.
string fieldValue = dataGIAttribute.Rows[row][column].ToString();
if (!string.IsNullOrEmpty(fieldValue) && !string.IsNullOrWhiteSpace(fieldValue) && fieldValue != “-”)
I had entered 0.059 in a cell and then clicked "Decrease Decimal (show fewer decimal places) icon present in excel in “Home” menu. In cell, it showed 0.06 but it had the value 0.059 with it.
I want to export the datatable in such a way it extracts “0.059” instead of “0.06”.
Can any one please suggest?
The solutions I have tried are
ExportTableOptions options = new ExportTableOptions();
options.ExportAsString = true;
options.FormatStrategy = CellValueFormatStrategy.None;
DataTable dataGIAttribute = dataSheet.Cells.ExportDataTable(0, firstColumn - 1, maxDataRow + 1, maxDataColumn - firstColumn + 2, options);
Its working perfectly fine in the environments where aspose upgrade is not deployed yet. That is, it retrieves “0.059” and not “0.06”
@Nikhil_Laad
The default value ExportTableOptions.FormatStrategy is CellValueFormatStrategy.CellStyle, so
If the DataType of DataColumn is string, so the value will be same as view in the cell.
If you do not want to format as cell style, please set ExportTableOptions.FormatStrategy as CellValueFormatStrategy.None
@Nikhil_Laad
We have tried to similate the case with code below:
cell.PutValue(0.059);
style.Custom = "0.00";
cell.SetStyle(style);
Console.WriteLine(cell.StringValue);
ExportTableOptions options = new ExportTableOptions();
options.ExportAsString = true;
options.FormatStrategy = CellValueFormatStrategy.None;
DataTable dt = cells.ExportDataTable(0, 0, 1, 1, options);
Console.WriteLine(dt.Rows[0][0].ToString());
We found the exported value is the raw value instead of “0.06”. If you replace the FormatStrategy as:
options.FormatStrategy = CellValueFormatStrategy.DisplayString;
the exported value will be “0.06”. We think those results are just the expected ones. Would you please provide your sample template file and code to reproduce the issue so we can figure the issue out?
Thanks @johnson.shi for immediate response. The suggested ExportTableOptions has helped quite a lot, still I have one concern. I am getting “0.058999999999999997” instead of “0.059”. Can you please provide your inputs on the same.
Originally, this was the reason that debugger was not stopping at my conditional breakpoint where I was expecting it to stop for either “0.059” or “0.06”. This made me think the attributes are not working as expected.
Thanks @simon.zhao for immediate response. The suggested ExportTableOptions has helped quite a lot, still I have one concern. I am getting “0.058999999999999997” instead of “0.059”. Can you please provide your inputs on the same.
Originally, this was the reason that debugger was not stopping at my conditional breakpoint where I was expecting it to stop for either “0.059” or “0.06”. This made me think the attributes are not working as expected.
@Nikhil_Laad
When exporting double values as string, generally we use “G17” to format it. However, for some cases such as 0.059, G17 cannot give the expected result as the literal value. For handling the string representation of double values, even ms excel itself cannot process all cases as expected either. For such kind of issue, we provide one option for user to determine how to format the double values to string: CellsHelper.SignificantDigitsType and WorkbookSettings.SignificantDigitsType. The first is for global settings for all workbook instances and the second one is for specific Workbook instance.
For your case, we think Digits15 or Rounding17 should be able to fit your requirement. The code like:
wb.Settings.SignificantDigitsType = SignificantDigitsType.Digits15;
ExportTableOptions options = new ExportTableOptions();
...
Thanks Much @johnson.shi, this suggestion has helped and solved my problem.
@Nikhil_Laad
You are very welcome! We are glad to hear that the suggested approach for handling decimal precision during the export process resolved the issue for you.
Thank you for the positive feedback. Please feel free to reach out if you have any further questions or need additional assistance with our APIs.
Hi @johnson.shi,
We have one adverse impact of this change.
It works absolutely fine for decimal values, but it is returning non expected values for percentage fields. It is rounding of them as well. A cell had “290%” but it rounded it to “2.9” which broke our one job. Can you please on this scenario how we can handle both the cases at the same time?
@Nikhil_Laad
Could you share your template file and example codes ? We will check it ASAP.
@Nikhil_Laad
Due to the different requirements and scenarios for different users, it is hard or even impossible for us to determine whether the decimal values should be exported as the raw values or the formatted values. For your case that requires different formatting strategy for different values, we think you have to handle them with your own logic and code. You may extends ExportTableOptions to override the method PreprocessExportedValue(int, int, CellValue) accordingly. For example, you may check the cell value firstly, if it is numeric, then get the cell according to its row and column index and then check its formatted result, if the result contains %, then export the formatted result, otherwise export the raw value(rounding to 15-digit if needed).