Hi,
Within an existing Excel-worksheet (creeated with Excel 2010) I want to
copy cells which are containing array formulas from one worksheet to
another one.
I am using the following code snippet to do this:
*****************
CODE
*****************
XSSFCell sourceCell = (XSSFCell)sourceRow.getCell(iCell);
if(sourceCell != null){
for (int iNewRow = 0; iNewRow < nRowsToCreate; iNewRow++) {
Row targetRow = newSheet.getRow(newRowStartIndex + iNewRow);
XSSFCell targetCell = (XSSFCell) targetRow.createCell(iCell);
switch (sourceCell.getCellType()) {
case Cell.CELL_TYPE_FORMULA:
CTCellFormula f = sourceCell.getCTCell().getF();
if(f != null){
try {
// create a dummy formula so we can get and change
it afterwards
targetCell.setCellFormula("1");
CTCellFormula f2 = targetCell.getCTCell().getF();
f2.setStringValue(f.getStringValue());
if ((f.getT() == STCellFormulaType.ARRAY) &&
(f.getRef() != null)) {
// f2.setT(f.getT());
// f2.setRef(f.getRef());
f2.setStringValue(f.getStringValue());
CellRangeAddress r =
sourceCell.getArrayFormulaRange();
newSheet.setArrayFormula(f.getStringValue(),
sourceCell.getArrayFormulaRange());
}
} catch (FormulaParseException e) {
// ignore formula parse exceptions as parsing may
not be possible until
// all sheets have been updated!
}
}
break;
}
}
}
*****************
END CODE
*****************
My problem is that the cells where I am trying array formulas into never
contain array formulas after saving the worksheet as a new file and
opening it in Excel 2010. They only contain normal formulas instead.
I also tried the following commented code:
// f2.setT(f.getT());
// f2.setRef(f.getRef());
which tries to set the cell properties directly on low level. However
Excel crashes while opening the file if I uncomment this code.
Can anybody please enlighten me how to do this correctly?
Thank you,
chris
