Copy row formula with range in excel

Question

I'm creating an application in which I'm generating an excel sheet after some inputs from the user. If user enter 5 records, I'm coping the initial/first row(cells with formulas) and then inserting each values in cells. The problem is after coping row, the formula range doesn't being updated for eg. if initial row is 10th and cell A has a formula like SUM(G10:K10) then after coping the row 10th to 11th formula remains the same i.e SUM(G10:K10) instead of SUM(G11:K11). Can anyone help me to solve this problem. Here's the code

HSSFSheet existingSheet = existingWorkBook.getSheetAt(0);
ExcelFile.copyRow(existingWorkBook, existingSheet, startRowColumn - 2, startRowColumn - 1);


public static void copyRow(HSSFWorkbook workbook, HSSFSheet worksheet, int sourceRowNum, int destinationRowNum) {
    // Get the source / new row
    HSSFRow newRow = worksheet.getRow(destinationRowNum);
    HSSFRow sourceRow = worksheet.getRow(sourceRowNum);

    // If the row exist in destination, push down all rows by 1 else create a new row
    if (newRow != null) {
        worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1);
    } else {
        newRow = worksheet.createRow(destinationRowNum);
    }

    // Loop through source columns to add to new row
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        // Grab a copy of the old/new cell
        HSSFCell oldCell = sourceRow.getCell(i);
        HSSFCell newCell = newRow.createCell(i);

        // If the old cell is null jump to next cell
        if (oldCell == null) {
            newCell = null;
            continue;
        }

        // Copy style from old cell and apply to new cell
        HSSFCellStyle newCellStyle = workbook.createCellStyle();
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
        ;
        newCell.setCellStyle(newCellStyle);

        // If there is a cell comment, copy
        if (oldCell.getCellComment() != null) {
            newCell.setCellComment(oldCell.getCellComment());
        }

        // If there is a cell hyperlink, copy
        if (oldCell.getHyperlink() != null) {
            newCell.setHyperlink(oldCell.getHyperlink());
        }

        // Set the cell data type
        newCell.setCellType(oldCell.getCellType());

        // Set the cell data value
        switch (oldCell.getCellType()) {
            case Cell.CELL_TYPE_BLANK:
                newCell.setCellValue(oldCell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                newCell.setCellValue(oldCell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_ERROR:
                newCell.setCellErrorValue(oldCell.getErrorCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                newCell.setCellFormula(oldCell.getCellFormula());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                newCell.setCellValue(oldCell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING:
                newCell.setCellValue(oldCell.getRichStringCellValue());
                break;
        }
    }

    // If there are are any merged regions in the source row, copy to new row
    for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
        CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i);
        if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
            CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(),
                    (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())), cellRangeAddress.getFirstColumn(),
                    cellRangeAddress.getLastColumn());
            worksheet.addMergedRegion(newCellRangeAddress);
        }
    }


}

After coping a row, here's the code to insert cell values.

Iterator rowIter = existingSheet.rowIterator();
while (rowIter.hasNext()) {
            HSSFRow oldRow = (HSSFRow) rowIter.next();
            if (rowCounterOld >= startRowColumn - 2 && !isRecordInserted) {
                if (configFieldsIndexes != null && configFieldsIndexes.size() > 0) {
                    for (Map.Entry entry : configFieldsIndexes.entrySet()) {
                        for (Map.Entry<String, String> entry1 : valueMap.entrySet()) {
                            if (((Map.Entry) entry1).getKey().toString().equalsIgnoreCase(entry.getKey().toString())) {
                                try {
                                    float value = Float.valueOf(entry1.getValue());
                                    oldRow.getCell(ExcelFile.getExcelColumnNumber(entry.getValue().toString())).setCellValue(value);
                                } catch (NumberFormatException e) {
                                    e.printStackTrace();
                                    oldRow.getCell(ExcelFile.getExcelColumnNumber(entry.getValue().toString())).setCellValue(entry1.getValue());
                                }

                                break;
                            }
                        }

                    }
                    isRecordInserted = true;
                    lastInsertedRow = rowCounterOld;

                }
            }
            rowCounterOld++;
            //rowCounterNew++;
        }

HSSFFormulaEvaluator.evaluateAllFormulaCells(existingWorkBook);

See https://stackoverflow.com/questions/47594254/apache-poi-update-formula-references-when-copying/47606426#47606426 for a `copyFormula` method. — Axel Richter, Aug 19 '19 at 14:39

score 1 · Answer 1 · answered Aug 19 '19 at 13:25

1

If you have an initial formula in A10, then simple use the VBA Copy method to fill A11:

Sub KopyFormula()
    Dim A10 As Range, A11 As Range

    Set A10 = Range("A10")
    Set A11 = Range("A11")

    A10.Copy A11
End Sub

answered Aug 19 '19 at 13:25

Gary's Student

95,722
10
59
99

How to do that in android? – Nirmal Prajapat Aug 19 '19 at 13:34

Copy row formula with range in excel

1 Answers1