How to Insert a Row Between Two Rows in an Existing Excel with Hssf (Apache Poi)

How to insert a row between two rows in an existing excel with HSSF (Apache POI)

Helper function to copy rows shamelessly adapted from here

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.FileInputStream;
import java.io.FileOutputStream;

public class RowCopy {

    public static void main(String[] args) throws Exception{
        HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream("c:/input.xls"));
        HSSFSheet sheet = workbook.getSheet("Sheet1");
        copyRow(workbook, sheet, 0, 1);
        FileOutputStream out = new FileOutputStream("c:/output.xls");
        workbook.write(out);
        out.close();
    }

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

Insert new rows into an Apache POI spreadsheet based on the content of a cell

So I managed to solve my problem. Instead of trying to append below the source row, I decided to append the row to the end of the file instead, which made the logic much easier. Here is the code that I created to solve the issue:

public void addAddtlRows(Sheet sheet,Workbook workbook,DataFormatter formatter, ImportDataFormatter fmt, File file){

     //Loads and parses the regular expression into memory and creates a new StringBuilder() instance.
    final Pattern p = Pattern.compile(regex);
    StringBuilder sb = new StringBuilder();

    //Create the array which holds all the entries from a cell that contains multiple entries
    String[] sysNumber;

    //The number of the last row in the sheet.
    int lastRow = sheet.getLastRowNum();

    //Instantiates an integer that will be assigned the length of the array later
    int arrayLength;

    //Loops through the each row in the sheet 
    for(int r = 1; r < lastRow; r++){
        Row row = sheet.getRow(r);
        String cellData = formatter.formatCellValue(row.getCell(2));
        String active = formatter.formatCellValue(row.getCell(4));

        if((cellData.length() > 4) && (active.equals("Yes"))){

             /** Checks whether or not we are on the cell containing the
             * numbers and whether or not they are currently active.
             * If we are, get values for all cells in the row
             */
            String an = formatter.formatCellValue(row.getCell(0));
            String cn = formatter.formatCellValue(row.getCell(1));
            String ca = formatter.formatCellValue(row.getCell(3));
            String es = formatter.formatCellValue(row.getCell(4));
            String i10 = formatter.formatCellValue(row.getCell(5));
            String i9 = formatter.formatCellValue(row.getCell(6));
            String ia = formatter.formatCellValue(row.getCell(7));
            String rp = formatter.formatCellValue(row.getCell(8));

            /**
             * Checks the contents of the cell for more than one entry
             * If the cell contains more than one number, process
             * the data accordingly
             */

            fmt.setSysNum(cellData);
            String[] sys = String.valueOf(fmt.getSysNum()).split(",");

            /**
             * Assign the length value of the 'sysNumber' array to
             * the integer 'arrayLength'
             */
            arrayLength = sys.length;

            /**
             * Loop through each entry in the string array, creating
             * a new row on each iteration and pasting the data from
             * the old cells to the new ones
             */
            for(int n = 0; n < arrayLength; n++){
                Row nRow = sheet.createRow(sheet.getPhysicalNumberOfRows());
                nRow.createCell(0).setCellValue(an);
                nRow.createCell(1).setCellValue(cn);
                nRow.createCell(2).setCellValue(sys[n]);
                nRow.createCell(3).setCellValue(ca);
                nRow.createCell(4).setCellValue(es);
                nRow.createCell(5).setCellValue(i10);
                nRow.createCell(6).setCellValue(i9);
                nRow.createCell(7).setCellValue(ia);
                nRow.createCell(8).setCellValue(rp);

            } 
        }              
    }

    //Writes the newly added contents of the worksheet to the workbook.
    try {
    workbook.write(new FileOutputStream(file));
    } catch (FileNotFoundException ex) {
    Logger.getLogger(MapMultipleSNToDBFields.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
    Logger.getLogger(MapMultipleSNToDBFields.class.getName()).log(Level.SEVERE, null, ex);
    }
}

Edit or add a new row in excel file using apache POI

To add rows in between multiple rows, you need to shift all the rows below it down by one row, and create a new row: for example if you need to add another row in 5th position, and the sheet contains 10 rows just shift 5 to 10 rows by one step down

    sheet.shiftRows(5, 10, 1);

and add new row in 5th position

    sheet.createRow(5);

You can use shiftRows to delete as well, in that case you just need to pass the third argument in minus. You can see here as well

NOTE: Delete first row carefully... In proper handling may corrupt your excel file.

Insert a Row in Excel Using Java Apache POI

I have a solution which is working very well:

Workbook wb3=WorkbookFactory.create(new FileInputStream("Book1.xls"));
Sheet sh=wb3.getSheet("sheet1");  
int rows=sh.getLastRowNum();

Shift the number of rows down the sheet.

sh.shiftRows(2,rows,1);   

Here

• 2 -- Position at which we need to insert row
• rows -- Total rows
• 1 -- How many rows we are going to insert

The reason why we are doing the above process is to make an empty row; only then can we create a new row.

Now we shifted the rows, then we can do our stuff

Coding :

sh.createRow(1);

The above code is used to insert a row at the 1st position, as we defined.

---

Related Topics