Hi,
There is a situation in our application where we write formulas equivalent to the file attached, there are 3 sheets named “helperSheet”, “Sheet1” and “Sheet2”. In “helperSheet” A1 cell the values are being calculated by formula “SUMPRODUCT((A10:A20<>”")/COUNTIF(A10:A20,A10:A20&""))", and this “Sheet1” this value is set as formula “helperSheet!A1”, and in “Sheet2” the value is taken from A1 and set in A1 sheet of Sheet2 by “Sheet1!A1”.
This workbook is loaded and we create a new sheet named “CopiedSheet”, and I copy values from “Sheet2” into newly create sheet (i.e. into “CopiedSheet”), then I call calculateFormula on workbook object and I iterate through cells of newly created sheet and if its formula I get the value of it and set in the same cell (so we are replacing formula with value. We save the workbook with new name.
The newly created workbook does contain “CopiedSheet” sheet, but the value in A1 is zero, but we expect the value to be “5” as its the formula value in “Sheet2”.
Could you please let me know if I am missing something or this is an bug (and if there is an workaround for the issue).
----------------code-----------------------
Workbook wb = new Workbook(new FileInputStream(“res/exportIssue.xls”));
// Create a worksheet object and obtain the first sheet.
Worksheet sheet = wb.getWorksheets().get(“Sheet2”);
Worksheet addedSheet = wb.getWorksheets().add(“CopiedSheet”);
addedSheet.copy(sheet);
wb.calculateFormula();
Cells cells = addedSheet.getCells();
int maxColumn = sheet.getCells().getMaxColumn();
int maxRow = sheet.getCells().getMaxRow();
for (int i = 0; i <= maxRow; i++) {
for (int j = 0; j <= maxColumn; j++) {
Cell cell = cells.get(i, j);
if (cell.isFormula()) {
Object value = cell.getValue();
cell.setValue(value);
}
}
}
wb.save(“copiedFile-” + System.currentTimeMillis() + “.xls”);
---------------------------------------------
thanks and regards
mpujari
Hi,
Thanks for the template file and sample code.
I can notice the issue as you have mentioned by using your code. Even I used Cells.removeFormulas() method which is specifically used to replace the formulas with values after copying the relevant sheet. Also, I tried to use WorksheetCollection.addCopy() but still the result is the same as per your.
I have logged a ticket with an id “CELLSJAVA-40466” for your issue. We will look into your issue soon. Once we have any update on it, we will let you know here.
Thank you.
The issues you have found earlier (filed as CELLSJAVA-40466) have been fixed in this update.
This message was posted using Notification2Forum from Downloads module by aspose.notifier.