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.
Thanks for your response.
We are integrating inside of our application an Excel file provided by the European Central Bank. This file is protected and secured.
Integrating it inside of our application makes disappear the spaces and line breaks in their formulas. This is considered by the European Central bank as a corruption and reject systematically the file that we send back.
ASPOSE is modifying a file that is not supposed to be therefore we have unplugged ASPOSE in order to run the Stress Testing exercise.
@johnson.shi
Thanks for your response.
It is strictly forbidden to modify the official Excel file provided by the European Central Bank, the proposed solution is impossible on our side.
We would like you to prevent ASPOSE to modify any formulas. Could you please provide us an estimation on the resolution of this issue otherwise we can’t use ASPOSE.
@BENRHIM_Malek,
Thanks for your feedback. We will evaluate your issue further and get back to you soon.
Keeping spaces and line breaks in formula is a complicated task for us. Anyways, we will look into this feature and evaluate the possibility of supporting it.
Because maybe we cannot support it soon, as an workaround, we think you may restore those original formulas after performing all other operations(such as calculating formulas, deleting/inserting cell ranges) and before saving the updated workbook. The sample code:
class KeepFormulaWithSpaces : LightCellsDataHandler
{
public bool StartSheet(Worksheet sheet)
{
return true;
}
public bool StartRow(int rowIndex)
{
return true;
}
public bool ProcessRow(Row row)
{
return true;
}
public bool StartCell(int columnIndex)
{
return true;
}
public bool ProcessCell(Cell cell)
{
if (cell.IsFormula)
{
string fml = cell.Formula;
return fml.IndexOf(' ') > -1 || fml.IndexOf('\n') > -1;
}
return false;
}
}
...
Workbook mainWb = new Workbook(path);
//... do your business here
Workbook backupWb = new Workbook(path, new LoadOptions()
{
ParsingFormulaOnOpen = false,
LightCellsDataHandler = new KeepFormulaWithSpaces(),
LoadFilter = new LoadFilter(LoadDataFilterOptions.Formula)
});
FormulaParseOptions po = new FormulaParseOptions() { Parse = false };
foreach (Worksheet ws in backupWb.Worksheets)
{
Cells cs0 = mainWb.Worksheets[ws.Name].Cells;
foreach (Cell c in ws.Cells)
{
Cell c0 = cs0[c.Row, c.Column];
c0.SetFormula(c.Formula, po, c0.Value);
}
}
mainWb.Save(dest);
Corresponding code for Java:
private static class KeepFormulaWithSpaces implements LightCellsDataHandler
{
public boolean startSheet(Worksheet sheet)
{
return true;
}
public boolean startRow(int rowIndex)
{
return true;
}
public boolean processRow(Row row)
{
return true;
}
public boolean startCell(int columnIndex)
{
return true;
}
public boolean processCell(Cell cell)
{
if (cell.isFormula())
{
String fml = cell.getFormula();
return fml.indexOf(' ') > -1 || fml.indexOf('\n') > -1;
}
return false;
}
}
...
Workbook mainWb = new Workbook(path);
//... do your business here
LoadOptions lopt = new LoadOptions();
lopt.setParsingFormulaOnOpen(false);
lopt.setLightCellsDataHandler(new KeepFormulaWithSpaces());
lopt.setLoadFilter(new LoadFilter(LoadDataFilterOptions.FORMULA));
Workbook backupWb = new Workbook(path, lopt);
FormulaParseOptions po = new FormulaParseOptions();
po.setParse(false);
for (Worksheet ws : (Iterable<Worksheet>) backupWb.getWorksheets())
{
Cells cs0 = mainWb.getWorksheets().get(ws.getName()).getCells();
for (Cell c : (Iterable<Cell>) ws.getCells())
{
Cell c0 = cs0.get(c.getRow(), c.getColumn());
c0.setFormula(c.getFormula(), po, c0.getValue());
}
}
mainWb.save(dest);
We have opened the following new ticket in our internal issue tracking system. You will get the notification here when we get some updates for it.
Issue ID(s): CELLSNET-47162@johnson.shi
Thanks for your response.
Manipulating two Workbooks from a large file creates performance and resource problems.
We also do not have the right to change locked cell formulas.
Can you prioritize problem solving in your backlogs and give us time estimates?
What actions should we take to prioritize this request?
Please note, your issue/ticket is logged under normal (free) support model where issues are resolved on first come first serve basis. The resolution time of the issue depends upon the number of issues or other tasks logged prior to it.
We have however recorded your concerns and will surely inform you once we have some updates in this regard. Your patience is highly appreciated in this regard.
Moreover, the ticket can be escalated only to some extent under this mode which we have already done. You may check our paid support option where issues are prioritized and posses highest priority if your issue is a real blocker.
We apologize for your inconvenience.
With corresponding priority services, we will give high priority for this task correspondingly. According to our rough evaluation of this feature, with priority support we think we may try to support this feature and include it into the next release 23.7. And if we can finish this task earlier, we will provide hotfix for you as soon as this feature is available.
Thank you for your reply .
Could you please give me the estimated date of release 23.7 ?