Подтвердить что ты не робот

POI/Excel: применение формул "относительным" способом

Я использую Apache POI для управления файлами Excel (.xls) с помощью Java.

Я пытаюсь создать новую ячейку, содержимое которой является результатом формулы, как если бы пользователь скопировал/вставил формулу (то, что я называю "относительным" способом, противоположным "абсолютному").

Чтобы прояснить ситуацию, вот простой пример:

  • Ячейка A1 содержит "1", B1 содержит "2", A2 содержит "3", B2 содержит "4".
  • Ячейка A3 содержит следующую формулу "= A1 + B1".

Если я копирую формулу в ячейку А4 в Excel, она становится "=A2+B2": Excel динамически адаптирует содержимое формулы.

К сожалению, я не могу получить тот же результат программно. Единственное решение, которое я нашел, - маркировать формулу и делать грязную работу самостоятельно, но я действительно сомневаюсь, что это должно быть сделано именно таким образом. Мне не удалось найти то, что я ищу, в руководствах или в API.

Есть ли более простой способ решить эту проблему? Если это так, не могли бы вы указать мне правильное направление?

С наилучшими пожеланиями,

Nils

4b9b3361

ОТВЕТЫ

Ответ 1

Я тоже думаю, что нет простого способа сделать это.

Даже примеры HSSF и XSSD на сайте POI, например. TimesheetDemo выполнить конструкцию формулы вручную. например вокруг линии 110

String ref = (char)('A' + j) + "3:" + (char)('A' + j) + "12";
cell.setCellFormula("SUM(" + ref + ")");

Ответ 2

В моем смысле, user2622016 прав, за исключением того, что его решение управляет ссылками cell, а не ссылками area (например, для =SUM(A1:B8) он не работает)).

Вот как я исправил это:

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);
}

Я до сих пор не знаю, правильно ли я применил все формулы ячеек, но это работает для меня быстро и надежно.

Ответ 3

Я заглянул в класс FormulaEvaluator и нашел некоторые внутренние классы POI, которые могут сделать для нас работу.

FormulaParser, который анализирует String в массиве "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 теперь является нашей формулой в обратном полях. Теперь пройдите через все элементы и измените ссылки один за другим, как вы пожелаете:

/* 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 );
    }

И вы готовы передать "parse things" обратно в String:

formula = FormulaRenderer.toFormulaString(workbookWrapper, ptgs);
cell.setCellFormula( formula );

Ответ 4

Другой способ скопировать формулу относительно, протестирован с помощью 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));
}

Обновить общую формулу по мере необходимости:

sharedFormula.convertSharedFormulas(sharedFormulaPtg, rowIndexOffset, columnIndexOffset);

Как и в poi 3.12, SharedFormula не поддерживает ссылку на ячейку/формулу с других листов (= 'Sheet1'! A1). Здесь обновление для 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;
    }

}

Ответ 5

Я не думаю, что есть. POI должен был бы проанализировать формулу (учитывая A1 против $A $1 против $A1 и т.д.), И я не считаю, что она имеет такую ​​емкость. Когда я это делал в прошлом, мне всегда приходилось управлять этим. Извините - не ответ, на который вы надеялись!

Ответ 6

вы можете попробовать некоторые сторонние библиотеки excel, большинство из них могут обрабатывать формулы относительного/абсолютного диапазона.