I am evaluating Aspose.Cells for Java for possible use with our product, and I am running into a problem. Our goal is to allow users to upload template files to our program, and then run reports on that template, outputting an excel doc based off of that template with our data inserted into it.
For the most part Aspose.Cells seems to be doing the job, but whenever I run the template through Aspose and add our data to it, any formulas that existed in the template file are replaced with the static value of whatever the formula’s result was in the template.
Formula in Template: =COUNT(B1:(INDIRECT((“R”&ROW()-1)&“C”&COLUMN(),FALSE)))
(Number of cells above the current one that contain numbers)
Result of Formula in Template: 0
Code:
Workbook workbook = new Workbook();
workbook.open(ris); //My File input stream
Worksheet worksheet = workbook.getWorksheets().getSheet(0);
Cell countCell = worksheet.getCell(6, 1); //This cell should contain the formula
String value = countCell.getStringValue(); // “0.0”
String formula = countCell.getFormula(); // "null"
Cell value in resulting Excel file: 0
Expected calculated value in resulting Excel file: 4
Expected actual value in resulting Excel file: =COUNT(B1:(INDIRECT((“R”&ROW()-1)&“C”&COLUMN(),FALSE)))
I cannot handle any formulas programatically, because I cannot know what formulas may be in the user-uploaded template. All I want is for any formulas present in the original template to remain exactly the same when passed through Aspose. Is there a way to do this? Thank you.
-Dylan Gulick
Jama Software
Hi Dylan,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
Thank you for considering Aspose.
Well, I tested your issue with the latest version of Aspose.Cells and it works fine. Please download and try the latest version and if you still face any problem, please share your template file here and we will check it soon. Following is the link to the latest version of Aspose.Cells for Java.
Aspose.Cells for Java (Latest Version)
Thank You & Best Regards,
Thank you for the quick response. After further testing I’ve determined that the formulas are kept correctly when using an .xlt file, but are removed when using an .xltx file (contents of both files are the same). Is there a reason for this, and/or are there plans to allow support for xltx files? Thank you very much.
-Dylan Gulick
Jama Software
Hi,
Could you attach your sample xltx file here, we can check your issue soon.
Thank you.
Here are the xlt and xltx files I’ve been using.
Hi,
Thanks for providing us the template files.
We found the issue, the formula is null for XLTX file. We will figure it out soon.
Your issue has been logged into our issue tracking system with an issue id: CELLSJAVA-16261.
Thank you.
Hi,
To further update you, currently our formula engine cannot parse such formulas
correctly when reading the xltx template files. We will try to enhance it
later when we finish some other important tasks on hand.
Thanks for your understanding!
That is unfortunate, because using xltx files is integral to our project. As I said earlier, we do not need to manipulate the formulas using aspose, we simply need the formulas to still be there after we import/export the template file into and out of aspose. Is there any way to get at least this level of functionality easily? And if not, what time frame are we looking at? Thank you for your help.
-Dylan Gulick
Jama Software
Also, I am coming up with a similar problem that has to do with conditional formatting. Any conditional formatting I set (in both xlt and xltx files) is stripped out when imported/exported into/out of aspose. Is this a related issue?
-Dylan Gulick
Jama Software
Hi Jama,
Do you read conditional formatting attributes at run time from the template file, or you simply save/save as the template file(having the conditional formatting applied in it)?
Could you post your template excel file here.
Thank you.
I have a template file with various formatting and formula in it. I then open that template file with aspose (workbook.open(inputStream)). I insert several rows into the file and then save it to an output stream (workbook.save(outputStream)) which is then sent to the user to save/open as an excel file (xls/xlsx). As I said, all I need is for aspose to retain the formatting/formula, I don’t need to edit them. I’ve attached an example template file. Thank you.
-Dylan Gulick
Jama Software
Hi,
Which version of the product you are using. Please try the attached latest version, I have tested your scenario a bit, opening/re-saving your template file to various formats e.g xlt, xls, xlsx, the conditional formatting is retained in the generated files.
Please try the Aspose.Cells for Java (Latest Version) and let us know if you still find any issue, if so, kindly give us your sample code and output file here.
Thank you.
I’ve been using v2.2.0 the whole time. After further testing it appears that the problem is caused by setting the save type as EXCEL97TO2003 (workbook.save(outputStream, FileFormatType.EXCEL97TO2003). When I have EXCEL97TO2003 as the file type, I get a warning every time I open the resulting document that warns that data may have been lost and when I open it the conditional formatting has disappeared. When I set the type to EXCEL2007, while forcing the return file type to still be xls, I get an error warning that the file is in a different format than specified by the file extension. When I click through that error the file opens and it behaves like one would expect, including having all the conditional formatting/formulas from the template. Saving that file as an xls still retains formatting/formulas, etc… How can I get it to retain the conditional formatting while saving to an xls file? Thank you for your help.
-Dylan Gulick
Jama Software
Here is some of the code I’ve been using to generate the file:
Workbook workbook = new Workbook();
workbook.open(inputStream);
Worksheet worksheet = workbook.getWorksheets().getSheet(0);
//Setting 100 columns across and docs.size()+startingColumnIndex+500 rows down as not locked
for (int i = 0; i < 100; i++) {
for (int j = 0; j < (dtos.size() + startingColumnIndex + 500); j++) {
Cell cell = worksheet.getCell(j, i);
Style style = cell.getStyle();
style.setCellLocked(false);
cell.setStyle(style);
}
}
worksheet.getCells().insertColumns(startingColumnIndex, 5);
worksheet.getCells().insertRows(startingRowIndex + 1, documents.size());
Row row = worksheet.getCells().getRow(startingRowIndex + 1);
Cell cell = row.getCell(startingColumnIndex);
cell.setValue(“TEST”);
Style style = cell.getStyle();
style.setCellLocked(true);
cell.setStyle(style);
Protection prot = new Protection();
prot.setEditingContentsAllowed(false);
worksheet.protect(prot);
workbook.save(outputStream, FileFormatType.EXCEL97TO2003);
Hi,
After an initial test, we found the issue (as you have mentioned) using your sample code with your template xlt file. We will figure it out soon.
Your issue has been logged into our issue tracking system with an issue id: CELLSJAVA-16358. We will let you know when it is sorted out.
Thank you.
Thank you for your help. Do you have an estimate on when you might be able to sort this issue out?
-Dylan Gulick
Jama Software
Hi,
We will check if we can provide you an eta for the issue. We will get back to you soon.
Thank you.
Hi,
Please try the attached version.
We have fixed the issue of conditional
formatting.
Thank you.
Thank you very much, that seems to take care of most of our problems. We will be purchasing a license soon. Please keep me informed about how the xltx issue is progressing. Thanks again for all the help.
-Dylan Gulick
Jama Software
Hi,
Please try the attached version.
Now we support to preserve the formulas that we cannot parse
when reading excel2007 template files. Those formulas are only kept as String in
data model and cannot be moved or calculated at the moment. When you need to save the workbook to
excel2007 files, those formulas will be saved back. But when will save the workbook
as excel2003 files, they will be lost.
Thanks for your understanding!
That works wonderfully! Thank you very much for all your help, we now have everything working the way we need it to and have purchased a license. I really appreciate the swiftness of your responses and, as a developer myself, am impressed with how quickly you were able to correct the problems I found. Thanks again!
-Dylan Gulick
Jama Software