title: Insert new rows into an Apache POI spreadsheet based on the content of a cell
body: <p><strong>NOTE: I have searched everywhere and this is not a duplicate question.</strong></p>
  
  <p>I'm trying to create/insert a fixed number of "new" rows into a spreadsheet, using Apache POI (poi-4.1.0) and Java, based on the content of a cell in the same row - See the diagram below (the "|" represents a column break):</p>
  
  <p>1   Foo | 1001, 1002, 1003   |Yes<br>
  2   Bar | 1010               |Yes<br>
  3   Slf | 2500, 1500, 5200   |Yes  </p>
  
  <p>Essentially, what would happen is that I would then insert two new rows between rows 1 &amp; 2 and again after line 3, duplicating all data from the source row, except that instead of having three values (or however many there may be) in column two, there would only be one - See the diagram below (the "|" represents a column break):</p>
  
  <p>1   Foo  | 1001 |Yes<br>
  2   Foo1 | 1002 |Yes<br>
  3   Foo2 | 1003 |Yes<br>
  4   Bar  | 1001 |Yes<br>
  5   Slf  | 2500 |Yes<br>
  6   Slf1 | 1500 |Yes<br>
  7   Slf2 | 5200 |Yes  </p>
  
  <p>This process would be repeated, only on the cells that have multiple values, until all rows in the file had been read and processed. I should note that the new lines would be appended in the same file.</p>
  
  <p>Here is what I have for code (I used the code on <a href="https://stackoverflow.com/questions/5785724/how-to-insert-a-row-between-two-rows-in-an-existing-excel-with-hssf-apache-poi/5786426">this page</a> as a template and attempted to update it to match the current release of POI I am using):</p>
  
  <pre><code>public class XLSXFileAdd {
  private static final String prpfile = "src/main/resources/fileinfo/directories.properties";
  
  public static void main(String[] args) throws Exception{      
      File infile = new File(XLSXFileAdd.getCIFile()); //Gets the fully-qualified path to the input file.
      XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(infile));
      XSSFSheet sheet = workbook.getSheet("Sheet1"); //Opens the XLSX document at the specified worksheet.
      String msNum; //The string that will hold the contents of the cell with multiple values.
      XLSXFileAdd xfa = new XLSXFileAdd();
      for(int i = 1; i &lt;= sheet.getLastRowNum(); i++){
          Row row = sheet.getRow(i);            
          msNum = String.valueOf(row.getCell(2));
          if(i == 2 &amp;&amp; msNum.length() &gt; 4){ //If the current column in the row is equal to the cell that could contain multiple values and the number of values in the cell are greater than 1 (length is greater than 4 for multiple values)
              xfa.copyRows(workbook,sheet,i, i);
          }else{
              //Read and parse the file normally (the method used here works without issue so I have not copied it to reduce clutter and confusion).
          }               
      }
  }
  
  private static String getCIFile(){
      File propfile = new File(prpfile);
      Properties properties = new Properties();
      try{
          FileInputStream fis = new FileInputStream(propfile);
          properties.load(fis);
      }catch(IOException ex){
          Logger.getLogger(XLSXFileAdd.class.getName()).log(Level.SEVERE, null, ex);
  }
      String filename = (String)properties.get("xlsx.input.custdata");
      return filename;
  }
  
  private void copyRows(XSSFWorkbook workbook,XSSFSheet worksheet,int sourceRowNum, int destinationRowNum){
      //Get source &amp; destination row
      Row newRow = worksheet.getRow(destinationRowNum);
      Row sourceRow = worksheet.getRow(sourceRowNum);
  
      //Check if the row will be overwritten; if the row is populated, push down all rows by one and 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++){
          Cell oldCell = sourceRow.getCell(i);
          Cell newCell = newRow.createCell(i);
  
          //If the old is not populated (null), jump to next cell
          if(oldCell == null){
              newCell = null;
              continue;
          }
  
          //Set newly created cells to the style of the source cell
          newCell.setCellStyle(oldCell.getCellStyle());
  
          //Set the cell data type
          newCell.setCellType(oldCell.getCellType());
  
          //Set the value of the cell
          switch(oldCell.getCellType()){
              case _NONE:
                  newCell.setCellValue(oldCell.getStringCellValue());
                  break;
              case BLANK:
                  break;
              case BOOLEAN:
                  newCell.setCellValue(oldCell.getBooleanCellValue());
                  break;
              case ERROR:
                  newCell.setCellErrorValue(oldCell.getErrorCellValue());
                  break;
              case FORMULA:
                  newCell.setCellFormula(oldCell.getCellFormula());
                  break;
              case NUMERIC:
                  newCell.setCellValue(oldCell.getNumericCellValue());
                  break;
              case STRING:
                  newCell.setCellValue(oldCell.getRichStringCellValue());
                  break;       
          }           
      }
  
      //Check for merged regions and copy said regions 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);
          }
      }        
  }
  </code></pre>
  
  <p>When I run the code, it runs normally and says it completed successfully, however, when I attempt to open the modified file it complains about corrupted data and removes all but two lines from the file (if I allow MS Excel to attempt to repair it). I have also tried redirecting the output to a different file, but the results are the same - it corrupts the data and only shows two lines with a blank row between them.</p>
  
  <p>So my question(s) is/are this: 
  1) Is there a better way to do what I am wanting to do? 
  2) If there is not [a better way], what am I doing wrong that is causing it to corrupt all the data I am trying to write. </p>