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,
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.