Hi,
I’m using Aspose.Cells to export Excel documents to CSV. I found that the resulting CSV of an Excel containing SUBTOTAL formulas differs from the original Excel. The formula calculation seems to be incorrect and as a result the calculated values in my CSV differs from Excel. Maybe I must enable some settings before exporting but I could not find anything about it.
Workbook wb = new Workbook();
wb.open(…);
wb.calculateFormula();
wb.save(…);
The version I’m currently using is Aspose.Cells version 2.5.4 .
Kind regards,.
Hi,
Thanks for your sample file.
I executed the below source code to generate a CSV that seems to be identical to the input Excel file. I have attached my output and a snapshot for your reference. Also, attached to my reply is latest fix version of Aspose.Cells JAVA v2.5.4.4. You may give a try to this latest JAR and if you still face any difficulty then please attach snapshots highlighting the incorrect results.
JAVA
Workbook book = new Workbook();
book.open(“c:\temp\subtotals.xls”);
book.calculateFormula();
// book.getWorkbookSettings().setCalculationMode(com.aspose.cells.CalculationMode.AUTOMATIC);
book.save(“C:\temp\out.csv”,FileFormatType.CSV);
Hi,
I tested it with the latest version included in this thread (v2.5.4.4) and I got the same (wrong) results.
I decided to create a new project and only include the Aspose related libraries and tested again. Now it is working properly, so it seems there are some conflicting libraries! Have you ever heard of Aspose conflicting with other libraries?
Kind regards,.
Hi,
It has nothing to do with conflicting libraries. It seems to go wrong with the calculateFormula() method. I took the code from your reply and when I run it on my machine it fails. When I comment out the line book.calculateFormula() everything goes well. I think this is a bug, because the formulas must be calculated before saving to prevent possible uncalculated values in the result.
Workbook book = new Workbook();
book.open(“c:\temp\subtotals.xls”);
// book.calculateFormula();
book.save(“C:\temp\out.csv”,FileFormatType.CSV);
Kind regards,.
Hi,
Thanks for the analysis.
Up to my knowledge, I have never encountered a problem with Aspose.Cells API conflicting with other libraries. If my results posted in 309868 are correct then it could be a cause, as I am referencing only Aspose.Cells JAVA related libraries in my project to test your scenario. The same project is producing valid results for me.
Regarding Workbook.CalculateFormula method, your understanding is correct. It must be called before saving the workbook to prevent any possible un-calculated values in result. I am not sure if there is some problem/bug in this method. Because on my end, I am unable to replicate your said issue.
We have just release another fix version of Aspose.Cells JAVA v2.5.4.5 [attached]. Can you please test your scenario with this latest JAR and let us know of your results?