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

Question

Somehow I manage to create new rows between two rows in an existing excel file  The problem is  some of the formatting were not include along the shifting of the rows    One of this  is the row that are hide are not relatively go along during the shift  What I mean is ex    rows from 20 to 30 is hidden  but when a create new rows the formating still there  The hidden rows must also move during the insertion creation of new rows  it should be 21 to 31    Another thing is  the other object in the sheet that are not in the cell  Like the text box are not move along after the new row is created  Its like the position of these object are fixed  But I want it to move  the same thing as I insert a new row or paste row in excel  If there is a function of inserting a new row  please let me know   This what I have right now  just a snippet from my code   HSSFWorkbook wb   new HSSFWorkbook template     template is the source of file HSSFSheet sheet   wb getSheet  SAMPLE    HSSFRow newRow  HSSFCell cellData   int createNewRowAt   9    Add the new row between row 9 and 10  sheet shiftRows createNewRowAt  sheet getLastRowNum    1  true  false   newRow   sheet createRow createNewRowAt   newRow   sheet getRow createNewRowAt     If copy and paste of rows is possible that would be big help  But I already ask it here and can t find a solution  So I decided to create a row as an interim solution  I m done with it but having a problem like this    Any help will be much appreciated  Thanks

User · Answer

As to formulas being  updated  in the new row  since all the copying occurs after the shift  the old row  now one index up from the new row  has already had its formula shifted  so copying it to the new row will make the new row reference the old rows cells   A solution would be to parse out the formulas BEFORE the shift  then apply those  a simple String array would do the job   I m sure you can code that in a few lines    At start of function   ArrayList lt String gt  fArray   new ArrayList lt String gt     Row origRow   sheet getRow sourceRow   for  int i   0  i  lt  origRow getLastCellNum    i          if  origRow getCell i     null  amp  amp  origRow getCell i  getCellType      Cell CELL TYPE FORMULA           fArray add origRow getCell i  getCellFormula         else fArray add null       Then when applying the formula to a cell   newCell setCellFormula fArray get i

User · Answer

Referencing Qwerty s answer  if the destRow isnot null  sheet shiftRows   will change the destRow s reference to the next row  so we should always create a new row   if  destRow    null      sheet shiftRows destination  sheet getLastRowNum    1     destRow   sheet createRow destination

User · Answer

I ve implemented this in Kotlin like this   fun Sheet buildRow   rowNum Int     Row       val templateRow   this getRow  rowNum       this shiftRows  rowNum 1  sheet lastRowNum  1       val newRow   this createRow  rowNum 1       templateRow cellIterator   forEach           newRow createCell  it columnIndex   cellStyle   it cellStyle           return templateRow     It doesn t copy the cell values  just the format  Should be applicable to Java as well

User · Answer

Referencing Qwerty s answer  you can avoid to inflate XL size by re-using cellStyle  And when the type is CELL TYPE BLANK  getStringCellValue returns    instead of null   private static void copyRow Sheet worksheet  int sourceRowNum  int destinationRowNum         Get the source   new row   Row newRow   worksheet getRow destinationRowNum     Row 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  lt  sourceRow getLastCellNum    i             Grab a copy of the old new cell     Cell oldCell   sourceRow getCell i       Cell newCell   newRow createCell i           If the old cell is null jump to next cell     if  oldCell    null          newCell   null        continue                Use old cell style     newCell setCellStyle oldCell getCellStyle             If there is a cell comment  copy     if  newCell 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        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

User · Answer

I merged some of the other answers and comments in the following implementation  tested with Apache POI v3 9   I have only one rownum parameter because I shift down the target row and copy it in the new empty row   Formulas are handled as expected  they are not copied verbatim  with one exception  references to cells that are above the copied line are not updated  the workaround is to replace these explicit references  if any  with references calculated using INDIRECT   as suggested by this post   protected void copyRow Sheet worksheet  int rowNum        Row sourceRow   worksheet getRow rowNum          Save the text of any formula before they are altered by row shifting     String   formulasArray   new String sourceRow getLastCellNum         for  int i   0  i  lt  sourceRow getLastCellNum    i              if  sourceRow getCell i     null  amp  amp  sourceRow getCell i  getCellType      Cell CELL TYPE FORMULA               formulasArray i    sourceRow getCell i  getCellFormula               worksheet shiftRows rowNum  worksheet getLastRowNum    1       Row newRow   sourceRow     Now sourceRow is the empty line  so let s rename it     sourceRow   worksheet getRow rowNum   1      Now the source row is at rowNum 1         Loop through source columns to add to new row     for  int i   0  i  lt  sourceRow getLastCellNum    i                 Grab a copy of the old new cell         Cell oldCell   sourceRow getCell i           Cell newCell              If the old cell is null jump to next cell         if  oldCell    null                continue            else               newCell   newRow createCell i                         Copy style from old cell and apply to new cell         CellStyle newCellStyle   worksheet getWorkbook   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                  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 formulasArray i                    break              case Cell CELL TYPE NUMERIC                  newCell setCellValue oldCell getNumericCellValue                     break              case Cell CELL TYPE STRING                  newCell setCellValue oldCell getRichStringCellValue                     break              default                     break                           If there are any merged regions in the source row  copy to new row     for  int i   0  i  lt  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                       I m using this implementation in production code

User · Answer

For people who are looking to insert a row between two rows in an existing excel with XSSF  Apache POI   there is already a method  copyRows  implemented in the XSSFSheet   import org apache poi ss usermodel CellCopyPolicy  import org apache poi xssf usermodel XSSFSheet  import org apache poi xssf usermodel XSSFWorkbook  import java io FileInputStream  import java io FileOutputStream   public class App2 throws Exception      public static void main String   args           XSSFWorkbook workbook   new XSSFWorkbook new FileInputStream  input xlsx             XSSFSheet sheet   workbook getSheet  Sheet1            sheet copyRows 0  2  3  new CellCopyPolicy             FileOutputStream out   new FileOutputStream  output xlsx            workbook write out           out close

User · Answer

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  lt  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  lt  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

User · Answer

I came across the same issue recently  I had to insert new rows in a document with hidden rows and faced the same issues with you  After some search and some emails in apache poi list  it seems like a bug in shiftrows   when a document has hidden rows

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

Examples related to java

Examples related to excel

Examples related to apache-poi

Examples related to poi-hssf