Hello,
We have a problem after the calculate Formula with aspose, we discovered that aspose delete both spaces in Formula and line break.
this is the code that we used:
Workbook workbook = new Workbook(“file.xlsx”);
workbook.calculateFormula(true);
workbook.save(“file.xlsx”);
You find also in the attatchement file, the screenshot before and after the aspose calculate.after.PNG (7.3 KB)
before.PNG (6.7 KB)
1 Like
Thanks for the screenshots.
This is same with MS Excel’s behavior. I tried to insert space in the formula for a cell like “=MIN(A1: B1)” but when I press enter the space is gone and the formula is shown as “=MIN(A1:B1)” which is correct. Could you please share your input Excel file, we will check it soon.
@amjad.sahi
Thanks for your response,
To get a space in the formula, you should replace ‘:’ by ‘;’ and make space and after press enter.
example: “=MIN(A1:B1)” you replace it by “=MIN(A1; B1)”
I tried in MS Excel manaully but it produces error, see the screenshot attached. The “;” char does not work to be replaced with “:” in MS Excel.
sc_shot1.png (33.0 KB)
@amjad.sahi
You find the attatchement file two examples of excel files before and after the aspose calculate.demo.7z (13.8 KB)
@Kouraiem
Yes, Aspose deleted spaces in Formula for those spaces are useless.
We have no plan to kee them now.
@Kouraiem
Line breaks in the formula will be removed too if they have no effect on the calculation result
@simon.zhao @amjad.sahi
For us, there’s no effect on the calculation result, but we have to keep the same formula format.
The delete of spaces and the line breaks cause of as us a problem.
Could you elaborate what problems you face when not retaining those unnecessary spaces and line breaks, please provide complete details of usage of spaces and line breaks for your scenario. We might check it.
Spaces and line breaks in formula are special tokens in ms excel’s formula data structure and we do not support to preserve them while parsing formulas. It is also impossible for us to support it in near future.
As workaround, you may load the xlsx template file with ParsingFormulaOnOpen as false for LoadOptions. With this option, Cell.Formula will give you the original string(with spaces and line breaks) of the formula.
However, please note, when you perform some other operations, such as calculating formulas, inserting/deleting range of cells, …etc., all formulas will be parsed and those spaces and line breaks will be lost. For such situation, maybe you may consider to get all formula expressions and keep them in your own data model before performing other operations.