how to insert new rows with values in the same she

Question

I want to insert new rows while writing in Excel sheet.

Here is my code:

public static void addValuesInWorkbook(String pathAndFileName, String sheetName,
            int rowNum, String valuesString,String delimeter) {
        if(pathAndFileName.length() > 0 && sheetName.length() > 0 && rowNum >= 0 && valuesString.length() > 0 && delimeter.length() > 0)
        {
            String[] colValues= null;
            if("|".equals(delimeter))
                colValues = valuesString.split("\\|");
            else
                colValues = valuesString.split(delimeter);
            int cellnum = 0;
            FileInputStream fsIP;
            try {
                fsIP = new FileInputStream(new File(pathAndFileName));
             //Read the spreadsheet that needs to be updated
            if(rowNum > 0)
            {
                rowNum--;               //As row indexing starts from 0
            }
            HSSFWorkbook wb = new HSSFWorkbook(fsIP); //Access the workbook
            HSSFSheet sheet = wb.getSheet(sheetName);
            HSSFRow row = sheet.getRow(((rowNum>0)?rowNum:0));
            HSSFCell cell=null;
            for(String colValue:colValues)
            {   if(row!=null){
                cell = row.getCell(cellnum);
                if(cell==null)
                    cell = row.createCell(cellnum);
                }
            else if (row == null)
                {
                    row =sheet.createRow(rowNum);                   //Create a row if it does not exist
                    cell = row.createCell(cellnum);
                }

                cell.setCellValue(colValue);
                cellnum++;
            }
            fsIP.close(); //Close the InputStream

            FileOutputStream output_file =new FileOutputStream(new File(pathAndFileName));  //Open FileOutputStream to write updates

            wb.write(output_file); //write changes

            output_file.close();  //close the stream  
            } catch (FileNotFoundException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            }

        }

Values are getting inserted with headers(column names ) but whenever new rows are inserted it replaces the older row values.

Please help.

Answer 1

This is because you are creating new rows on a row index which already has values. You need to shift the existing rows 1 row down, so the index you want to insert a row is "free".

In other words, "createRow()" will always create a new row and index x, no matter if there is already one.

Use sheet.shiftRows(..) See: https://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFSheet.html#shiftRows(int, int, int)

The first argument should be the index of the row where you want to insert something, the second one the number of your last row and the third one should be 1. So all rows beginning from x will be shifted one row down and you can insert your row at x.

Example: every line is a row with content.

1 ----------------------
2 ----------------------
3 ---------------------- < you want to insert a new row at index 3
4 ----------------------
5 ----------------------
6 ----------------------
7 ----------------------

shiftRows(3,7,1) will do this:

1 ----------------------
2 ----------------------
3 empty row 
4 ---------------------- < your "old" row #3
5 ----------------------
6 ----------------------
7 ----------------------
8 ----------------------

createRow(3), set cell values:

1 ----------------------
2 ----------------------
3 ////////////////////// < your new row #3 witht he new content
4 ---------------------- < your "old" row #3
5 ----------------------
6 ----------------------
7 ----------------------
8 ----------------------