We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Setting formula with long string literal results in corrupt Excel

Hi,


Using Aspose Cells for Java, version 17.02.1.
Excel has a limitation regarding the length of string literals inside formulas.
However, Aspose Cells does not respect or enforce this limit, making it very easy to produce corrupt workbooks without any feedback.

Sample code:
<pre style=“background-color: rgb(255, 255, 255); font-family: “Courier New”; font-size: 9pt;”>Workbook workbook = new Workbook();
WorksheetCollection worksheets = workbook.getWorksheets();
Worksheet worksheet = worksheets.get(0);
Cell cell = worksheet.getCells().get(0, 0);
StringBuilder stringLiteral = new StringBuilder();
for(int i = 0; i <= 255; i++) {
stringLiteral.append(“A”);
}
String formula = “=" + stringLiteral.toString() + ";
cell.setFormula(formula);
workbook.save(“c:/dev/stringliteral.xlsx”);

Note that lowering the length of the literal (for example, to 250 characters) will generate a valid workbook. However, the code above, unchanged, will generate a workbook that will trigger corruption warning in Excel.

Please make setFormula check that the inserted formula in fact is valid regarding the maximum string literal length limitation of Excel.

Kind regards,
Taras

Hi,


Thanks for the sample code and details.

I observed the issue as you mentioned by using your sample code. I found that by setting a formula with long string literal results in corrupt Excel file. As MS Excel itself has 255 characters limits for formula literals, so it would be better to provide some API (e.g method) or some check for (setFormula method) to evaluate if the inserted formula in fact is valid regarding the maximum string literals length limitation of MS Excel.

I have logged a ticket with an id “CELLSJAVA-42205” for your issue. We will check if we could devise some good workaround to be shared or provide an API (e.g new method/overload) to support your needs.

Once we have an update on it, we will let you know here.

Thank you.

Hi,


We are pleased to inform you that we have figured out your issue (logged earlier as “CELLSJAVA-42205”) now. We will soon share the fix with details in the next few days here.

Thank you.

Hi Amjad,


Thank you. Will the code now simply throw an exception when attempting to set such a formula?

Thanks,
Taras

Hi,


Please let the new fix come out, so we could provide you with complete details.

Thank you.
Hi,

TarasTielkes:
Thank you. Will the code now simply throw an exception when attempting to set such a formula?

Yes, an exception will be thrown if the length of string is greater than the the maximum string literals length limitation of MS Excel.

Thank you.

Hi,


Please try our latest version/fix: Aspose.Cells for Java v17.02.9

Your issue should be fixed in it.

Let us know your feedback.

Thank you.

Hi Amjad,


The fix seems to work correctly.
Thank you for implementing this, it will catch a number of problems with generated formulas much earliers.

Kind regards,
Taras

Hi,


Thanks for your feedback.

Good to know that your issue is sorted out by the new fix, we have closed your ticket now. Feel free to write us back in case you have further comments or questions, we will be happy to assist you soon.

Thank you.

The issues you have found earlier (filed as CELLSJAVA-42205) have been fixed in Aspose.Cells for Java 17.3.0.


This message was posted using Notification2Forum from Downloads module by Aspose Notifier.