I'm using Apache's POI to manipulate Excel (.xls) files with Java.
I'm trying to create a new cell whom content is the result of a formula as if the user had copied/pasted the formula (what i call the "relative" way, as opposite to "absolute").
To make myself clearer, here is a simple example:
If I copy the formula to the A4 cell under excel, it becomes "=A2+B2"
: excel is adapting the content of the formula dynamically.
Unfortunately I cannot get the same result programatically. The only solution I found is to tokenize the formula and do the dirty work myself, but I really doubt that this is supposed to be done that way. I was not able to find what I'm looking for in the guides or in the API.
Is there an easier way to solve this problem ? If it's the case, can you please point me in the right direction ?
Best regards,
Nils
Overview. HSSF is the POI Project's pure Java implementation of the Excel '97(-2007) file format. XSSF is the POI Project's pure Java implementation of the Excel 2007 OOXML (. xlsx) file format. HSSF and XSSF provides ways to read spreadsheets create, modify, read and write XLS spreadsheets.
Create an XSSFSheet from an existing sheet in the XSSFWorkbook. Save the content in the underlying package part. Returns the workbook's data format table (a factory for creating data format strings). Create an XSSFSheet for this workbook, adds it to the sheets and returns the high level representation.
In my sense, user2622016 is right, except his solution manages only cell references, as opposed to area references (it won't work for =SUM(A1:B8)
for instance).
Here's how I fixed this :
private void copyFormula(Sheet sheet, Cell org, Cell dest) {
if (org == null || dest == null || sheet == null
|| org.getCellType() != Cell.CELL_TYPE_FORMULA)
return;
if (org.isPartOfArrayFormulaGroup())
return;
String formula = org.getCellFormula();
int shiftRows = dest.getRowIndex() - org.getRowIndex();
int shiftCols = dest.getColumnIndex() - org.getColumnIndex();
XSSFEvaluationWorkbook workbookWrapper =
XSSFEvaluationWorkbook.create((XSSFWorkbook) sheet.getWorkbook());
Ptg[] ptgs = FormulaParser.parse(formula, workbookWrapper, FormulaType.CELL
, sheet.getWorkbook().getSheetIndex(sheet));
for (Ptg ptg : ptgs) {
if (ptg instanceof RefPtgBase) // base class for cell references
{
RefPtgBase ref = (RefPtgBase) ptg;
if (ref.isColRelative())
ref.setColumn(ref.getColumn() + shiftCols);
if (ref.isRowRelative())
ref.setRow(ref.getRow() + shiftRows);
} else if (ptg instanceof AreaPtg) // base class for range references
{
AreaPtg ref = (AreaPtg) ptg;
if (ref.isFirstColRelative())
ref.setFirstColumn(ref.getFirstColumn() + shiftCols);
if (ref.isLastColRelative())
ref.setLastColumn(ref.getLastColumn() + shiftCols);
if (ref.isFirstRowRelative())
ref.setFirstRow(ref.getFirstRow() + shiftRows);
if (ref.isLastRowRelative())
ref.setLastRow(ref.getLastRow() + shiftRows);
}
}
formula = FormulaRenderer.toFormulaString(workbookWrapper, ptgs);
dest.setCellFormula(formula);
}
I still don't know if I had it correct for all cell formulas, but it works for me, fast and reliable.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With