When importing an Excel file and running the calculateFormula, the output file displays a #VALUE error. Upon inspection, the formulas in these cells have been altered, with @ symbols added to the formulas.
Details:
Input File: A control Excel file (cf Input_CTRL_STE_CONC.xlsx) was created with the following elements:
Excel Formulas: Formulas were included in the “Result” column.{}
IssueEncountered: After importing this Excel file and running the calculateFormula, the output file (cf Excel_Controle_File_Output.xlsx) generated by aspose shows the following issues:
#VALUE Errors : Cells H3 and H4 display a #VALUE error.
Modified Formulas : Clicking on these cells reveals that the formulas have been altered, with @ symbols added to them.
We need the formulas to remain unchanged and functional after importing the file and calculateFormula.
It seems you are encountering issues with the calculateFormula method in Aspose.Cells for Java, specifically related to the alteration of formulas in your Excel file after importing it. The addition of “@” symbols to the formulas, which leads to #VALUE! errors, is indeed a concern.
Here are some steps you can take to troubleshoot and potentially resolve this issue:
Check Formula Compatibility: Ensure that the formulas in your original Excel file are compatible with Aspose.Cells. Some Excel functions or formulas may not be fully supported or may behave differently when processed by Aspose.Cells.
Review Formula Settings: You can adjust the formula settings in Aspose.Cells to better handle the formulas during import. This can be done by configuring the FormulaSettings class, which allows you to specify how formulas are treated during calculations.
Use the Correct Calculation Method: When calling the calculateFormula method, ensure you are using it correctly. You can call it on the Workbook, Worksheet, or Cell class depending on your needs. For example:
Workbook workbook = new Workbook("cf Input_CTRL_STE_CONC.xlsx");
workbook.calculateFormula();
workbook.save("cf Excel_Controle_File_Output.xlsx");
Inspect the Formulas After Import: After importing the Excel file, inspect the formulas in the affected cells (H3 and H4) to see how they have changed. This can provide insight into why the #VALUE! error is occurring.
Update Aspose.Cells: Ensure you are using the latest version of Aspose.Cells, as updates may include bug fixes and improvements related to formula handling.
If the problem persists, consider reaching out to Aspose support with specific details about the formulas and the version of Aspose.Cells you are using. They may provide additional insights or fixes related to the ticket number CELLSNET-47162.
For more detailed information on handling formulas in Aspose.Cells, you can refer to the official documentation on calculating formulas here.
If you have any further questions or need additional assistance, feel free to ask!
I checked your template Excel file “Input_CTRL_STE_CONC.xlsx” and found your workbook contains formulas having links to external sources or data. That’s why Aspose.Cells cannot calculate those formulas and obtain the results. Please use inline formulas (e.g., formulas should refer to the worksheets cells/range in the workbook). This way, Aspose.Cells could calculate all the formulas fine and will get you correct results in the output files.