title: How to clone an excel sheet from one workbook to another workbook using Apache POI and Java?
body: <p>I am trying to copy a formatted excel sheet from one workbook to another which contains merged cells, font size, font color, font face.</p>
  
  <p>I am able to copy all the sheets from multiple excel workbook to a single workbook but I am unable to get the formatting. Please help</p>
  
  <pre><code>import java.io.FileInputStream;
  import java.io.FileOutputStream;
  import java.io.IOException;
  
  import org.apache.poi.hssf.usermodel.HSSFSheet;
  import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  import org.apache.poi.ss.usermodel.Cell;
  import org.apache.poi.ss.usermodel.CellStyle;
  import org.apache.poi.ss.usermodel.CellType;
  import org.apache.poi.ss.usermodel.DateUtil;
  import org.apache.poi.ss.usermodel.Row;
  import org.apache.poi.ss.usermodel.Workbook;
  
  public class CopyExcelSheets {
      public static void main(String args[]) throws IOException {
  
          //Excel sheet names to read from
          String [] excelSheets = {"Crystal1.xls","Crystal2.xls","Crystal3.xls","Crystal4.xls","Crystal5Complex.xls"};
          //Root folder from where the excel files are read
          final String ROOT_FOLDER = "C:\\demo\\shaji\\";
          //Output file name for merged excel file (consolidated file)
          final String OUTPUT_FILE = "C:\\demo\\shaji\\mergedOutput.xls";
  
          Workbook [] workbook = new Workbook[excelSheets.length];
  
          for(int i = 0; i &lt; excelSheets.length; i++) {
              workbook[i] = new HSSFWorkbook(new FileInputStream(ROOT_FOLDER + excelSheets[i]));
          }
  
          Workbook newWorkbook = new HSSFWorkbook();
  
          for(int i = 0; i &lt; excelSheets.length; i++) {
              newWorkbook = CopyExcelSheets.copy(workbook[i], newWorkbook, "Book-" + i);
          }       
  
          //Write over to the new file
          FileOutputStream fileOut;
          fileOut = new FileOutputStream(OUTPUT_FILE);            
          newWorkbook.write(fileOut);
  
          for(int i = 0; i &lt; excelSheets.length; i++) {
              workbook[i].close();
          }        
  
          newWorkbook.close();
          fileOut.close();
      }
  
      /*
       * Based on the implementation by Faraz Durrani on Stackoveflow
       * http://stackoverflow.com/questions/3333021/how-to-copy-one-workbook-sheet-to-another-workbook-sheet-using-apache-poi-and-ja
       */
  
      public static Workbook copy(Workbook oldWorkbook, Workbook newWorkbook, String bookName) throws IOException {
          // Need this to copy over styles from old sheet to new sheet. Next step will be processed below
          CellStyle newStyle = newWorkbook.createCellStyle(); 
          Row row;
          Cell cell;
          for (int i = 0; i &lt; oldWorkbook.getNumberOfSheets(); i++) {
              HSSFSheet sheetFromOldWorkbook = (HSSFSheet) oldWorkbook.getSheetAt(i);
              HSSFSheet sheetForNewWorkbook = (HSSFSheet) newWorkbook.createSheet(bookName + "-" + sheetFromOldWorkbook.getSheetName());
              for (int rowIndex = 0; rowIndex &lt; sheetFromOldWorkbook.getPhysicalNumberOfRows(); rowIndex++) {
                  row = sheetForNewWorkbook.createRow(rowIndex); //create row in this new sheet
                  for (int colIndex = 0; colIndex &lt; sheetFromOldWorkbook.getRow(rowIndex).getPhysicalNumberOfCells(); colIndex++) {
                      cell = row.createCell(colIndex); //create cell in this row of this new sheet
                      //get cell from old/original Workbook's sheet and when cell is null, return it as blank cells. 
                      //And Blank cell will be returned as Blank cells. That will not change.
                      Cell c = sheetFromOldWorkbook.getRow(rowIndex).getCell(colIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK ); 
                          if (c.getCellTypeEnum() == CellType.BLANK){
                              //System.out.println("This is BLANK " +  ((XSSFCell) c).getReference());                            
                          }
                          else {  
                          //Below is where all the copying is happening. 
                          //First it copies the styles of each cell and then it copies the content.              
                          CellStyle origStyle = c.getCellStyle();
                          newStyle.cloneStyleFrom(origStyle);
                          cell.setCellStyle(newStyle);            
  
                           switch (c.getCellTypeEnum()) {
                              case STRING:                            
                                  cell.setCellValue(c.getRichStringCellValue().getString());
                                  break;
                              case NUMERIC:
                                  if (DateUtil.isCellDateFormatted(cell)) {                             
                                      cell.setCellValue(c.getDateCellValue());
                                  } else {                              
                                      cell.setCellValue(c.getNumericCellValue());
                                  }
                                  break;
                              case BOOLEAN:
                                  cell.setCellValue(c.getBooleanCellValue());
                                  break;
                              case FORMULA:
                                  cell.setCellValue(c.getCellFormula());
                                  break;
                              case BLANK:
                                  cell.setCellValue("");
                                  break;
                              default:
                                  System.out.println();
                              }
                          }
                      }
                  }
  
              }    
  
          return newWorkbook;   
      }
  }
  </code></pre>