JAVA POI复制单元格的时候 单元格里面的公式为什么没有发生变化呢
这个是我 复制 Excel 行 的代码
poiUtil.copyRows(12, 12, 14, curSheet);
这行的公式是
复制出新的行之后 里面的公式还是
我想要的结果是 =F15/D15
请大神指导一下 用POI操作的时候 怎么 可以让公式跟着改变呢
已经解决了
public void copyRows(int startRow, int endRow, int pPosition, XSSFSheet currentSheet, XSSFWorkbook book) {
int pStartRow = startRow – 1;
int pEndRow = endRow – 1;
int targetRowFrom;
int targetRowTo;
int columnCount;
CellRangeAddress region = null;
int i;
int j;
if (pStartRow == -1 || pEndRow == -1) {
return;
}
if (pStartRow == pPosition) {
return;
}
for (i = 0; i < currentSheet.getNumMergedRegions(); i++) {
region = currentSheet.getMergedRegion(i);
if ((region.getFirstRow() >= pStartRow) && (region.getLastRow() <= pEndRow)) {
targetRowFrom = region.getFirstRow() - pStartRow + pPosition;
targetRowTo = region.getLastRow() - pStartRow + pPosition;
CellRangeAddress newRegion = region.copy();
newRegion.setFirstRow(targetRowFrom);
newRegion.setFirstColumn(region.getFirstColumn());
newRegion.setLastRow(targetRowTo);
newRegion.setLastColumn(region.getLastColumn());
currentSheet.addMergedRegion(newRegion);
}
}
for (i = pStartRow; i <= pEndRow; i++) {
XSSFRow sourceRow = currentSheet.getRow(i);
columnCount = sourceRow.getLastCellNum();
if (sourceRow != null) {
XSSFRow newRow = currentSheet.createRow(pPosition - pStartRow + i);
newRow.setHeight(sourceRow.getHeight());
for (j = 0; j < columnCount; j++) {
XSSFCell templateCell = sourceRow.getCell(j);
if (templateCell != null) {
XSSFCell newCell = newRow.createCell(j);
copyCell(templateCell, newCell);
if (templateCell.getCellType() == Cell.CELL_TYPE_FORMULA) {
XSSFCell fromCell = getRow(pStartRow).getCell(j);
XSSFCell destCell = getRow(pPosition).getCell(j);
copyFormula(fromCell, destCell, book);
}
}
}
}
}
}
public static void copyFormula(Cell srcCell, Cell destCell, XSSFWorkbook book) {
// 相対参照で数式をコピーする
// srcCell コピー元 destCell コピー先
String formula = srcCell.getCellFormula();
EvaluationWorkbook ew;
FormulaRenderingWorkbook rw;
Ptg[] ptgs;
ew = XSSFEvaluationWorkbook.create((XSSFWorkbook) book);
ptgs = FormulaParser.parse(formula, (XSSFEvaluationWorkbook) ew, FormulaType.CELL, 0);
// 詳しいことはわからないが、最後のパラメータはシート番号を0始まりで指定
rw = (XSSFEvaluationWorkbook) ew;
for (Ptg ptg : ptgs) {
// 座標の計算
int shiftRows = destCell.getRowIndex() - srcCell.getRowIndex();
int shiftCols = destCell.getColumnIndex() - srcCell.getColumnIndex();
if (ptg instanceof RefPtgBase) {
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) {
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);
}
}
}
destCell.setCellFormula(FormulaRenderer.toFormulaString(rw, ptgs));
}
原文作者:一段记忆叫青春
原文地址: https://blog.csdn.net/weixin_43786896/article/details/99706505
本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系博主进行删除。
原文地址: https://blog.csdn.net/weixin_43786896/article/details/99706505
本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系博主进行删除。
相关文章