title: How to copy a Excel Sheet into another Workbook using Apache POI for Java?
body: <p>I am trying to copy a Excel sheet into another Excel Workbook. All the possibilities I found browsing the web won't work with formulars. </p>
  
  <p>The best code I found: <a href="http://www.coderanch.com/t/420958/open-source/Copying-sheet-excel-file-excel" rel="nofollow">http://www.coderanch.com/t/420958/open-source/Copying-sheet-excel-file-excel</a></p>
  
  <p>Do you have any ideas how I can get this to work?</p>
  
  <p>Thanks,
  Lomin</p>
  
  <p>Edit: Added the Code:</p>
  
  <pre><code>import java.util.HashMap;
  import java.util.Map;
  import java.util.Set;
  import java.util.TreeSet;
  
  import org.apache.poi.hssf.usermodel.HSSFCell;
  import org.apache.poi.hssf.usermodel.HSSFCellStyle;
  import org.apache.poi.hssf.usermodel.HSSFRow;
  import org.apache.poi.hssf.usermodel.HSSFSheet;
  import org.apache.poi.ss.util.CellRangeAddress;
  
    /**  
     *  
     * @author jk  
     * getted from http://jxls.cvs.sourceforge.net/jxls/jxls/src/java/org/jxls/util/Util.java?revision=1.8&amp;view=markup  
     * by Leonid Vysochyn   
     * and modified (adding styles copying)  
     * modified by Philipp Löpmeier (replacing deprecated classes and methods, using generic types)  
     */ 
    public final class Util {   
  
          /**
           * DEFAULT CONSTRUCTOR.
           */
          private Util() {}
  
        /**
         * @param newSheet the sheet to create from the copy.
         * @param sheet the sheet to copy.
         */
        public static void copySheets(HSSFSheet newSheet, HSSFSheet sheet){   
            copySheets(newSheet, sheet, true);   
        }   
  
        /**
         * @param newSheet the sheet to create from the copy.
         * @param sheet the sheet to copy.
         * @param copyStyle true copy the style.
         */
        public static void copySheets(HSSFSheet newSheet, HSSFSheet sheet, boolean copyStyle){   
            int maxColumnNum = 0;   
            Map&lt;Integer, HSSFCellStyle&gt; styleMap = (copyStyle) ? new HashMap&lt;Integer, HSSFCellStyle&gt;() : null;   
            for (int i = sheet.getFirstRowNum(); i &lt;= sheet.getLastRowNum(); i++) {   
                HSSFRow srcRow = sheet.getRow(i);   
                HSSFRow destRow = newSheet.createRow(i);   
                if (srcRow != null) {   
                    Util.copyRow(sheet, newSheet, srcRow, destRow, styleMap);   
                    if (srcRow.getLastCellNum() &gt; maxColumnNum) {   
                        maxColumnNum = srcRow.getLastCellNum();   
                    }   
                }   
            }   
            for (int i = 0; i &lt;= maxColumnNum; i++) {   
                newSheet.setColumnWidth(i, sheet.getColumnWidth(i));   
            }   
        }   
  
        /**
         * @param srcSheet the sheet to copy.
         * @param destSheet the sheet to create.
         * @param srcRow the row to copy.
         * @param destRow the row to create.
         * @param styleMap -
         */
        public static void copyRow(HSSFSheet srcSheet, HSSFSheet destSheet, HSSFRow srcRow, HSSFRow destRow, Map&lt;Integer, HSSFCellStyle&gt; styleMap) {   
            // manage a list of merged zone in order to not insert two times a merged zone
          Set&lt;CellRangeAddressWrapper&gt; mergedRegions = new TreeSet&lt;CellRangeAddressWrapper&gt;();   
            destRow.setHeight(srcRow.getHeight());   
            // pour chaque row
            for (int j = srcRow.getFirstCellNum(); j &lt;= srcRow.getLastCellNum(); j++) {   
                HSSFCell oldCell = srcRow.getCell(j);   // ancienne cell
                HSSFCell newCell = destRow.getCell(j);  // new cell 
                if (oldCell != null) {   
                    if (newCell == null) {   
                        newCell = destRow.createCell(j);   
                    }   
                    // copy chaque cell
                    copyCell(oldCell, newCell, styleMap);   
                    // copy les informations de fusion entre les cellules
                    //System.out.println("row num: " + srcRow.getRowNum() + " , col: " + (short)oldCell.getColumnIndex());
                    CellRangeAddress mergedRegion = getMergedRegion(srcSheet, srcRow.getRowNum(), (short)oldCell.getColumnIndex());   
  
                    if (mergedRegion != null) { 
                      //System.out.println("Selected merged region: " + mergedRegion.toString());
                      CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow(), mergedRegion.getLastRow(), mergedRegion.getFirstColumn(),  mergedRegion.getLastColumn());
                        //System.out.println("New merged region: " + newMergedRegion.toString());
                        CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion);
                        if (isNewMergedRegion(wrapper, mergedRegions)) {
                            mergedRegions.add(wrapper);
                            destSheet.addMergedRegion(wrapper.range);   
                        }   
                    }   
                }   
            }   
  
        }   
  
        /**
         * @param oldCell
         * @param newCell
         * @param styleMap
         */
        public static void copyCell(HSSFCell oldCell, HSSFCell newCell, Map&lt;Integer, HSSFCellStyle&gt; styleMap) {   
            if(styleMap != null) {   
                if(oldCell.getSheet().getWorkbook() == newCell.getSheet().getWorkbook()){   
                    newCell.setCellStyle(oldCell.getCellStyle());   
                } else{   
                    int stHashCode = oldCell.getCellStyle().hashCode();   
                    HSSFCellStyle newCellStyle = styleMap.get(stHashCode);   
                    if(newCellStyle == null){   
                        newCellStyle = newCell.getSheet().getWorkbook().createCellStyle();   
                        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());   
                        styleMap.put(stHashCode, newCellStyle);   
                    }   
                    newCell.setCellStyle(newCellStyle);   
                }   
            }   
            switch(oldCell.getCellType()) {   
                case HSSFCell.CELL_TYPE_STRING:   
                    newCell.setCellValue(oldCell.getStringCellValue());   
                    break;   
              case HSSFCell.CELL_TYPE_NUMERIC:   
                    newCell.setCellValue(oldCell.getNumericCellValue());   
                    break;   
                case HSSFCell.CELL_TYPE_BLANK:   
                    newCell.setCellType(HSSFCell.CELL_TYPE_BLANK);   
                    break;   
                case HSSFCell.CELL_TYPE_BOOLEAN:   
                    newCell.setCellValue(oldCell.getBooleanCellValue());   
                    break;   
                case HSSFCell.CELL_TYPE_ERROR:   
                    newCell.setCellErrorValue(oldCell.getErrorCellValue());   
                    break;   
                case HSSFCell.CELL_TYPE_FORMULA:   
                    newCell.setCellFormula(oldCell.getCellFormula());   
                    break;   
                default:   
                    break;   
            }   
  
        }   
  
        /**
         * Récupère les informations de fusion des cellules dans la sheet source pour les appliquer
         * à la sheet destination...
         * Récupère toutes les zones merged dans la sheet source et regarde pour chacune d'elle si
         * elle se trouve dans la current row que nous traitons.
         * Si oui, retourne l'objet CellRangeAddress.
         * 
         * @param sheet the sheet containing the data.
         * @param rowNum the num of the row to copy.
         * @param cellNum the num of the cell to copy.
         * @return the CellRangeAddress created.
         */
        public static CellRangeAddress getMergedRegion(HSSFSheet sheet, int rowNum, short cellNum) {   
            for (int i = 0; i &lt; sheet.getNumMergedRegions(); i++) { 
                CellRangeAddress merged = sheet.getMergedRegion(i);   
                if (merged.isInRange(rowNum, cellNum)) {   
                    return merged;   
                }   
            }   
            return null;   
        }   
  
        /**
         * Check that the merged region has been created in the destination sheet.
         * @param newMergedRegion the merged region to copy or not in the destination sheet.
         * @param mergedRegions the list containing all the merged region.
         * @return true if the merged region is already in the list or not.
         */
        private static boolean isNewMergedRegion(CellRangeAddressWrapper newMergedRegion, Set&lt;CellRangeAddressWrapper&gt; mergedRegions) {
          return !mergedRegions.contains(newMergedRegion);   
        }   
  
    } 
  </code></pre>
  
  <p>and: </p>
  
  <pre><code>import org.apache.poi.ss.util.CellRangeAddress;
  
  
  
   public class CellRangeAddressWrapper implements Comparable&lt;CellRangeAddressWrapper&gt; {
  
    public CellRangeAddress range;
  
    /**
     * @param theRange the CellRangeAddress object to wrap.
     */
    public CellRangeAddressWrapper(CellRangeAddress theRange) {
          this.range = theRange;
    }
  
    /**
     * @param o the object to compare.
     * @return -1 the current instance is prior to the object in parameter, 0: equal, 1: after...
     */
    public int compareTo(CellRangeAddressWrapper o) {
  
                if (range.getFirstColumn() &lt; o.range.getFirstColumn()
                            &amp;&amp; range.getFirstRow() &lt; o.range.getFirstRow()) {
                      return -1;
                } else if (range.getFirstColumn() == o.range.getFirstColumn()
                            &amp;&amp; range.getFirstRow() == o.range.getFirstRow()) {
                      return 0;
                } else {
                      return 1;
                }
  
    }
  
   }
  </code></pre>