POI / Excel: appliquer les formules de manière "relative"
J'utilise le POI D'Apache pour manipuler Excel (.xls fichiers) avec Java.
je suis en train de créer une nouvelle cellule dont le contenu est le résultat d'une formule, comme si l'utilisateur avait copié/collé de la formule (ce que j'appelle la "relative", par opposition à "absolue").
pour être plus clair, voici un exemple simple : La cellule A1 contient "1",la cellule B1 contient "2", La cellule A2 contient "3", la cellule B2 contient "4". La cellule A3 contient la formule suivante: "=A1+B1". Si je copie la formule à L'A4 cellule sous excel, elle devient "= A2+B2": excel adapte dynamiquement le contenu de la formule.
malheureusement je ne peux pas obtenir le même résultat de façon programmatique. La seule solution que j'ai trouvée est de tokeniser la formule et de faire le sale boulot moi-même, mais je doute vraiment que ce soit censé être fait de cette façon. Je n'ai pas pu trouver ce que je cherche dans les guides ou dans L'API.
y a-t-il un moyen plus facile de résoudre ce problème ? Si c'est le cas, pouvez-vous s'il vous plaît point moi dans la bonne direction ?
cordialement,
Nils
6 réponses
moi aussi je pense qu'il n'y a pas une façon facile de faire cela.
Même exemples HSSF et XSSD sur le site POI par ex. TimesheetDemo faites la construction de la formule manuellement. par exemple autour de la ligne 110
String ref = (char)('A' + j) + "3:" + (char)('A' + j) + "12";
cell.setCellFormula("SUM(" + ref + ")");
À mon sens, user2622016 est droit, à l'exception de sa solution ne gère que cellule références, par opposition à références (il ne fonctionnera pas pour =SUM(A1:B8) par exemple).
Voici comment j'ai résolu ce :
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);
}
Je ne sais toujours pas si je l'ai eu correct pour toutes les formules de cellules, mais il fonctionne pour moi, rapide et fiable.
j'ai regardé à l'intérieur de FormulaEvaluator classe et trouvé quelques classes internes POI qui peuvent faire le travail pour nous.
FormulaParser, qui analyse la chaîne de caractères en tableau de "parse things":
String formula = cell.getCellFormula();
XSSFEvaluationWorkbook workbookWrapper =
XSSFEvaluationWorkbook.create((XSSFWorkbook) workbook);
/* parse formula */
Ptg[] ptgs = FormulaParser.parse(formula, workbookWrapper,
FormulaType.CELL, 0 /*sheet index*/ );
ptgs est maintenant notre formule en notation polonaise inversée. Passez maintenant en revue tous les éléments et modifiez les références une par une comme vous le souhaitez:
/* re-calculate cell references */
for( Ptg ptg : ptgs )
if( ptg instanceof RefPtgBase ) //base class for cell reference "things"
{
RefPtgBase ref = (RefPtgBase)ptg;
if( ref.isColRelative() )
ref.setColumn( ref.getColumn() + 0 );
if( ref.isRowRelative() )
ref.setRow( ref.getRow() + 1 );
}
Et vous êtes prêt à rendre "analyser les choses" retour à la Chaîne:
formula = FormulaRenderer.toFormulaString(workbookWrapper, ptgs);
cell.setCellFormula( formula );
un Autre moyen de copier la formule relativement, testé avec poi 3.12
public static void copyCellFormula(Workbook workbook, int sheetIndex, int rowIndex, int sourceColumnIndex, int destinationColumnIndex){
XSSFEvaluationWorkbook formulaParsingWorkbook = XSSFEvaluationWorkbook.create((XSSFWorkbook) workbook);
SharedFormula sharedFormula = new SharedFormula(SpreadsheetVersion.EXCEL2007);
Sheet sheet = workbook.getSheetAt(sheetIndex);
Row lookupRow = sheet.getRow(rowIndex);
Cell sourceCell = lookupRow.getCell(sourceColumnIndex);
Ptg[] sharedFormulaPtg = FormulaParser.parse(sourceCell.getCellFormula(), formulaParsingWorkbook, FormulaType.CELL, sheetIndex);
Ptg[] convertedFormulaPtg = sharedFormula.convertSharedFormulas(sharedFormulaPtg, 0, 1);
Cell destinationCell = lookupRow.createCell(destinationColumnIndex);
destinationCell.setCellFormula(FormulaRenderer.toFormulaString(formulaParsingWorkbook, convertedFormulaPtg));
}
mise à Jour partagé la formule:
sharedFormula.convertSharedFormulas(sharedFormulaPtg, rowIndexOffset, columnIndexOffset);
à partir de la poi 3.12, SharedFormula ne supporte pas les cellules de référence / formule des autres feuilles (='Sheet1'!A1). Voici une mise à jour de SharedFormula:
public class SharedFormula {
private final int _columnWrappingMask;
private final int _rowWrappingMask;
public SharedFormula(SpreadsheetVersion ssVersion) {
this._columnWrappingMask = ssVersion.getLastColumnIndex();
this._rowWrappingMask = ssVersion.getLastRowIndex();
}
public Ptg[] convertSharedFormulas(Ptg[] ptgs, int formulaRow, int formulaColumn) {
Ptg[] newPtgStack = new Ptg[ptgs.length];
RefPtgBase areaNPtg = null;
AreaPtgBase var9 = null;
Object ptg = null;
byte originalOperandClass = 0;
for(int k = 0; k < ptgs.length; ++k) {
ptg = ptgs[k];
originalOperandClass = -1;
if(!((Ptg)ptg).isBaseToken()) {
originalOperandClass = ((Ptg)ptg).getPtgClass();
}
if(ptg instanceof RefPtgBase) {
if(ptg instanceof Ref3DPxg) {
areaNPtg = (Ref3DPxg)ptg;
this.fixupRefRelativeRowAndColumn(areaNPtg, formulaRow, formulaColumn);
ptg = areaNPtg;
}else if(ptg instanceof Ref3DPtg) {
areaNPtg = (Ref3DPtg)ptg;
this.fixupRefRelativeRowAndColumn(areaNPtg, formulaRow, formulaColumn);
ptg = areaNPtg;
}else {
areaNPtg = (RefPtgBase)ptg;
ptg = new RefPtg(this.fixupRelativeRow(formulaRow, areaNPtg.getRow(), areaNPtg.isRowRelative()), this.fixupRelativeColumn(formulaColumn, areaNPtg.getColumn(), areaNPtg.isColRelative()), areaNPtg.isRowRelative(), areaNPtg.isColRelative());
}
((Ptg)ptg).setClass(originalOperandClass);
}else if(ptg instanceof AreaPtgBase) {
if(ptg instanceof Area3DPxg) {
var9 = (Area3DPxg)ptg;
this.fixupAreaRelativeRowAndColumn(var9, formulaRow, formulaColumn);
ptg = var9;
}else if(ptg instanceof Area3DPxg) {
var9 = (Area3DPtg)ptg;
this.fixupAreaRelativeRowAndColumn(var9, formulaRow, formulaColumn);
ptg = var9;
}else {
var9 = (AreaPtgBase)ptg;
ptg = new AreaPtg(this.fixupRelativeRow(formulaRow, var9.getFirstRow(), var9.isFirstRowRelative()), this.fixupRelativeRow(formulaRow, var9.getLastRow(), var9.isLastRowRelative()), this.fixupRelativeColumn(formulaColumn, var9.getFirstColumn(), var9.isFirstColRelative()), this.fixupRelativeColumn(formulaColumn, var9.getLastColumn(), var9.isLastColRelative()), var9.isFirstRowRelative(), var9.isLastRowRelative(), var9.isFirstColRelative(), var9.isLastColRelative());
}
((Ptg)ptg).setClass(originalOperandClass);
}else if(ptg instanceof OperandPtg) {
ptg = ((OperandPtg)ptg).copy();
}
newPtgStack[k] = (Ptg)ptg;
}
return newPtgStack;
}
protected void fixupRefRelativeRowAndColumn(RefPtgBase areaNPtg, int formulaRow, int formulaColumn){
areaNPtg.setRow(this.fixupRelativeRow(formulaRow, areaNPtg.getRow(), areaNPtg.isRowRelative()));
areaNPtg.setColumn(this.fixupRelativeColumn(formulaColumn, areaNPtg.getColumn(), areaNPtg.isColRelative()));
areaNPtg.setRowRelative(areaNPtg.isRowRelative());
areaNPtg.setColRelative(areaNPtg.isColRelative());
}
protected void fixupAreaRelativeRowAndColumn(AreaPtgBase var9, int formulaRow, int formulaColumn){
var9.setFirstRow(this.fixupRelativeRow(formulaRow, var9.getFirstRow(), var9.isFirstRowRelative()));
var9.setLastRow(this.fixupRelativeRow(formulaRow, var9.getLastRow(), var9.isLastRowRelative()));
var9.setFirstColumn(this.fixupRelativeColumn(formulaColumn, var9.getFirstColumn(), var9.isFirstColRelative()));
var9.setLastColumn(this.fixupRelativeColumn(formulaColumn, var9.getLastColumn(), var9.isLastColRelative()));
var9.setFirstRowRelative(var9.isFirstRowRelative());
var9.setLastRowRelative(var9.isLastRowRelative());
var9.setFirstColRelative(var9.isFirstColRelative());
var9.setLastColRelative(var9.isLastColRelative());
}
protected int fixupRelativeColumn(int currentcolumn, int column, boolean relative) {
return relative?column + currentcolumn & this._columnWrappingMask:column;
}
protected int fixupRelativeRow(int currentrow, int row, boolean relative) {
return relative?row + currentrow & this._rowWrappingMask:row;
}
}
je ne pense pas qu'il y est. L'IPE devrait analyser la formule (en prenant en compte A1 par rapport à 1 $A par rapport à A1, etc.) et je ne crois pas qu'il a cette capacité. Quand j'ai fait ça dans le passé, j'ai toujours dû gérer ça moi-même. Désolé - pas la réponse que vous espériez!
vous pouvez essayer quelques librarys tiers excel,la plupart d'entre eux peuvent gérer les formules de gamme relative/absolue.