blocks|key|327347|text|我不认为有。POI必须解析公式(考虑A1、$A$1和$A1等因素)我不相信它有这样的能力。当我在过去这样做的时候，我总是不得不自己管理它。抱歉，答案不是你想要的！|type|unstyled|depth|inlineStyleRanges|entityRanges|data|327348|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

I don't think there is. POI would have to parse the formula (taking into account A1 vs. $A$1 vs. $A1 etc.) and I don't believe it has that capacity. When I've done this in the past I've always had to manage this myself. Sorry - not the answer you hoped for!

blocks|key|329729|text|我也认为没有一种简单的方法可以做到这一点。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|329730|即使是POI网站上的HSSF+and+XSSD+examples，例如TimesheetDemo，也会手动构建公式。例如在110行附近|offset|length|329731|String+ref+=+(char)('A'+%2B+j)+%2B+"3:"+%2B+(char)('A'+%2B+j)+%2B+"12";
cell.setCellFormula("SUM("+%2B+ref+%2B+")");|code-block|syntax|javascript|329732|entityMap|0|LINK|mutability|MUTABLE|url|http://poi.apache.org/spreadsheet/examples.html|1|https://github.com/apache/poi/blob/trunk/src/examples/src/org/apache/poi/examples/ss/TimesheetDemo.java^0|0|A|M|0|Z|D|1|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@$D|W|E|X|1|Y]|$D|Z|E|10|1|11]]|A|$]]|$1|F|3|G|5|H|7|12|8|@]|9|@]|A|$I|J]]|$1|K|3|-4|5|6|7|13|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]|S|$5|N|O|P|A|$Q|T]]]]

I too think that there isn't an easy way to do this.
Even the <a href="http://poi.apache.org/spreadsheet/examples.html" rel="nofollow noreferrer">HSSF and XSSD examples</a> on the POI site e.g. <a href="https://github.com/apache/poi/blob/trunk/src/examples/src/org/apache/poi/examples/ss/TimesheetDemo.java" rel="nofollow noreferrer">TimesheetDemo</a> do the formula construction manually. e.g. around line 110
<pre><code>String ref = (char)('A' + j) + &quot;3:&quot; + (char)('A' + j) + &quot;12&quot;;
cell.setCellFormula(&quot;SUM(&quot; + ref + &quot;)&quot;);
</code></pre>

blocks|key|327365|text|你可以尝试一些第三方excel库，它们中的大多数都可以处理相对/绝对范围公式。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|327366|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

you can try some third party excel librarys,most of them can handle the relative/absolute range formulas.

blocks|key|3081913|text|我查看了FormulaEvaluator类，发现了一些可以为我们做这项工作的POI内部类。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3081914|FormulaParser，它将字符串解析为"parse+things“数组：|3081915|String+formula+=+cell.getCellFormula();
XSSFEvaluationWorkbook+workbookWrapper+=+
+++++++++++++XSSFEvaluationWorkbook.create((XSSFWorkbook)+workbook);++
/*+parse+formula+*/
Ptg[]+ptgs+=+FormulaParser.parse(formula,+workbookWrapper,+
+++++++++++++FormulaType.CELL,+0+/*sheet+index*/+);|code-block|syntax|javascript|3081916|ptgs现在是我们用反向波兰语表示的公式。现在遍历所有元素，并根据需要逐个修改引用：|3081917|/*+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()+%2B+0+);
++++++++if(+ref.isRowRelative()+)
++++++++++++ref.setRow(+ref.getRow()+%2B+1+);
++++}|3081918|现在就可以将"parse+thing“返回给String了：|3081919|formula+=+FormulaRenderer.toFormulaString(workbookWrapper,+ptgs);
cell.setCellFormula(+formula+);|3081920|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|V|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|W|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|F|7|Y|8|@]|9|@]|A|$G|H]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

I looked inside FormulaEvaluator class and found some POI internal classes that can do the work for us.

FormulaParser, which parses String to array of "parse things":

<pre><code>String formula = cell.getCellFormula();
XSSFEvaluationWorkbook workbookWrapper = 
 XSSFEvaluationWorkbook.create((XSSFWorkbook) workbook); 
/* parse formula */
Ptg[] ptgs = FormulaParser.parse(formula, workbookWrapper, 
 FormulaType.CELL, 0 /*sheet index*/ );
</code></pre>

ptgs is now our formula in reverse polish notation. Now go through all elements and modify references one by one as you wish:

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

And you're ready to render "parse things" back to String:

<pre><code>formula = FormulaRenderer.toFormulaString(workbookWrapper, ptgs);
cell.setCellFormula( formula );
</code></pre>

blocks|key|3081978|text|在我看来，user2622016是正确的，除了他的解决方案只管理单元格引用，而不是区域引用(例如，它不适用于=SUM(A1:B8)+)。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|CODE|entityRanges|data|3081979|下面是我如何解决这个问题的：|3081980|private+void+copyFormula(Sheet+sheet,+Cell+org,+Cell+dest)+{
++++if+(org+==+null+%7C%7C+dest+==+null+%7C%7C+sheet+==+null+
++++++++++++%7C%7C+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()+%2B+shiftCols);
++++++++++++if+(ref.isRowRelative())
++++++++++++++++ref.setRow(ref.getRow()+%2B+shiftRows);
++++++++}+else+if+(ptg+instanceof+AreaPtg)+//+base+class+for+range+references
++++++++{
++++++++++++AreaPtg+ref+=+(AreaPtg)+ptg;
++++++++++++if+(ref.isFirstColRelative())
++++++++++++++++ref.setFirstColumn(ref.getFirstColumn()+%2B+shiftCols);
++++++++++++if+(ref.isLastColRelative())
++++++++++++++++ref.setLastColumn(ref.getLastColumn()+%2B+shiftCols);
++++++++++++if+(ref.isFirstRowRelative())
++++++++++++++++ref.setFirstRow(ref.getFirstRow()+%2B+shiftRows);
++++++++++++if+(ref.isLastRowRelative())
++++++++++++++++ref.setLastRow(ref.getLastRow()+%2B+shiftRows);
++++++++}
++++}
++++formula+=+FormulaRenderer.toFormulaString(workbookWrapper,+ptgs);
++++dest.setCellFormula(formula);
}|code-block|syntax|javascript|3081981|我仍然不知道我是否对所有的单元格公式都是正确的，但它对我来说是有效的，快速可靠。|3081982|entityMap^0|W|3|15|2|1I|B|0|0|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@$9|S|A|T|B|C]|$9|U|A|V|B|C]|$9|W|A|X|B|D]]|E|@]|F|$]]|$1|G|3|H|5|6|7|Y|8|@]|E|@]|F|$]]|$1|I|3|J|5|K|7|Z|8|@]|E|@]|F|$L|M]]|$1|N|3|O|5|6|7|10|8|@]|E|@]|F|$]]|$1|P|3|-4|5|6|7|11|8|@]|E|@]|F|$]]]|Q|$]]

In my sense, user2622016 is right, except his solution manages only cell references, as opposed to area references (it won't work for <code>=SUM(A1:B8)</code> for instance).

Here's how I fixed this :

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

I still don't know if I had it correct for all cell formulas, but it works for me, fast and reliable.

blocks|key|329788|text|另一种相对复制公式的方法，使用poi+3.12进行测试|type|unstyled|depth|inlineStyleRanges|entityRanges|data|329789|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));
}|code-block|syntax|javascript|329790|根据需要更新共享公式：|329791|sharedFormula.convertSharedFormulas(sharedFormulaPtg,+rowIndexOffset,+columnIndexOffset);|329792|从poi+3.12开始，SharedFormula不支持来自其他工作表的单元格引用/公式(='Sheet1'!A1)。以下是SharedFormula的更新：|329793|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;+%2B%2Bk)+{
++++++++++++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+%2B+currentcolumn+&+this._columnWrappingMask:column;
++++}

++++protected+int+fixupRelativeRow(int+currentrow,+int+row,+boolean+relative)+{
++++++++return+relative?row+%2B+currentrow+&+this._rowWrappingMask:row;
++++}

}|329794|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

Another way to copy formula relatively, tested with poi 3.12

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

Update shared formula as needed:

<pre><code>sharedFormula.convertSharedFormulas(sharedFormulaPtg, rowIndexOffset, columnIndexOffset);
</code></pre>

As of poi 3.12, SharedFormula doesn't support cell reference/formula from other sheets (='Sheet1'!A1). Here's an update to SharedFormula:

<pre><code>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 &lt; 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 &amp; this._columnWrappingMask:column;
 }

 protected int fixupRelativeRow(int currentrow, int row, boolean relative) {
 return relative?row + currentrow &amp; this._rowWrappingMask:row;
 }

}
</code></pre>

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: 

<ul>
<li>Cell A1 contains "1",B1 contains "2", A2 contains "3", B2 contains "4".</li>
<li>Cell A3 contains the following formula "=A1+B1".</li>
</ul>

If I copy the formula to the A4 cell under excel, it becomes <code>"=A2+B2"</code> : 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

POI / Excel : applying formulas in a "relative" way

Java

我正在使用Apache和Java操作.xls文件。我正在尝试创建一个新的单元格，它的内容是公式的结果，就好像用户复制/粘贴了公式一样(我称之为“相对”方式，与“绝对”方式相反)。为了让我更清楚，这里有一个简单的例子：单元格A1包含"1"，B1包含"2"，A2包含"3"，B2包含“4”。单元格A3包含以下公式“=A1+B...

问POI / Excel :以“相对”方式应用公式
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问POI / Excel :以“相对”方式应用公式EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问POI / Excel :以“相对”方式应用公式
EN