POI / Excel : "상대적" 방식으로 공식 적용

POI / Excel : "상대적" 방식으로 공식 적용

Apache의 POI를 사용하여 Java로 Excel(.xls) 파일을 조작하고 있습니다.

저는 마치 사용자가 공식을 복사/붙여넣은 것처럼 공식의 결과인 내용의 새로운 셀을 만들려고 합니다("절대"와 반대되는 "상대적" 방식).

여기 간단한 예가 있습니다.

• 셀 A1은 "1", B1은 "2", A2는 "3", B2는 "4"를 포함합니다.
• 셀 A3는 다음 공식 "=A1+B1"을 포함합니다.

만약 내가 엑셀로 A4 셀에 공식을 복사한다면, 그것은"=A2+B2"excel은 수식의 내용을 동적으로 적용하고 있습니다.

안타깝게도 저는 같은 결과를 프로그램적으로 얻을 수 없습니다.제가 찾은 유일한 해결책은 공식을 토큰화하고 더러운 작업을 직접 하는 것입니다. 하지만 저는 이것이 그렇게 되어야 하는지 정말 의심스럽습니다.가이드나 API에서 찾고 있는 것을 찾을 수 없었습니다.

이 문제를 좀 더 쉽게 해결할 수 있는 방법이 없을까요? 그렇다면 올바른 방향을 가르쳐 주시겠습니까?

안부 전합니다,

닐스

내가 보기에, user2622016은 맞습니다. 그의 솔루션은 영역 참조와 반대로 셀 참조만 관리합니다(그것은 작동하지 않을 것입니다).=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);
}

제가 모든 세포 공식을 정확하게 썼는지는 아직 모르겠지만, 빠르고 안정적으로 작동합니다.

저도 이것을 하는 쉬운 방법이 없다고 생각합니다.

POI 사이트의 HSSF 및 XSSD 예제도 마찬가지입니다.타임시트Demo 공식을 수동으로 구성합니다(예: 110 라인 주변).

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

FormulaEvalator 클래스 내부를 살펴보니 이 작업을 수행할 수 있는 POI 내부 클래스가 몇 개 있었습니다.

문자열을 "parse things" 배열로 구문 분석하는 FormulaParser:

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

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 기준으로 Shared Formula는 다른 시트의 셀 참조/편집을 지원하지 않습니다(= 'Sheet1'!A1). 다음은 공유 공식에 대한 업데이트입니다.

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

}

없을 것 같습니다. POI는 공식을 구문 분석해야 합니다(A1 대 $A$1 대).$A1 등) 그리고 저는 그것이 그런 용량을 가지고 있다고 생각하지 않습니다.과거에 제가 이 일을 했을 때, 저는 항상 스스로 이 일을 처리해야 했습니다.죄송합니다 - 당신이 바라던 답이 아닙니다!

타사 Excel 라이브러리를 사용해 볼 수 있으며, 대부분 상대/절대 범위 공식을 처리할 수 있습니다.

언급URL : https://stackoverflow.com/questions/1636759/poi-excel-applying-formulas-in-a-relative-way