Copying a sheet from an excel file to another excel file using Apache POI

[Srikanth Kumar]

Hey,
I hava a requirement to copy from an excel file to another excel file where i need to copy an excel files sheet to another excel sheet. i'm able to achieve this using cell-to-cell copying but its consuming time. Is there any way to copy directly from sheet to sheet?

[Ulf Dittmer]

No, POI does not have such convenience methods.

Note that -if you end up writing code that copies a complete sheet, including the cell styles- this would make a good addition to POI, so you might think about donating that code to the project (or at least posting it here so that people can find it).

[Martijn Verburg]

Hi Srikanth,

Welcome to Javaranch! Just a quick admin matter, can you please change your display name to fit the Naming guidelines. Thanks!

[Srikanth Kumar]

Thanks for reply Ulf.
I have taken your word into consideration and tried to implement that. somehow i could able to achieve this(copying sheet-to-sheet). However it works with sheets that contains integers and decimals. If any cell contains varchar then its replacing it with #VALUE!. And while opening the merged file i'm getting an alert "File error: data may have been lost."

I'm posting the classes involved in this.

package com.utilities.excel;
 
import java.io.IOException;
import java.io.InputStream;
 
import org.apache.poi.hssf.model.Workbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.DirectoryNode;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
 
public class AdvancedWorkbook extends HSSFWorkbook {
 
public AdvancedWorkbook() {
System.out.println("_sheets initial size ==> " + super._sheets.size());
// TODO Auto-generated constructor stub
}
 
public AdvancedWorkbook(InputStream s) throws IOException {
super(s);
// TODO Auto-generated constructor stub
}
 
public void addSheet(HSSFSheet sheet){
System.out.println(" b4 add _sheets size ==> " + super._sheets.size());
_sheets.add(sheet);
System.out.println(" after add _sheets size ==> " + super._sheets.size());
}
}

 
 
package com.utilities.excel;
 
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
 
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 
public class MergeXSLFiles {
 
public static void mergeExcelFiles() {
HSSFWorkbook book1 = null;
HSSFWorkbook book2 = null;
HSSFSheet sheet1 = null;
HSSFSheet sheet2 = null;
 
try {
book1 = new HSSFWorkbook(new FileInputStream(
"..\\com\\utilities\\excel\\Book1.xls"));
    sheet1 = book1.getSheetAt(0);
 
    book2 = new HSSFWorkbook(new FileInputStream(
                       "..\\com\\utilities\\excel\\Book2.xls"));
    sheet2 = book2.getSheetAt(0);
   
        try {
        AdvancedWorkbook mergedBook = new AdvancedWorkbook();
        
        mergedBook.addSheet(sheet1);
        mergedBook.setSheetName(0, book1.getSheetName(0));
 
        mergedBook.addSheet(sheet2);
       mergedBook.setSheetName(1, book2.getSheetName(0));
 
        FileOutputStream fileOut = new FileOutputStream(
        "..\\src\\com\\utilities\\excel\\Merged.xls"        ,false);
        mergedBook.write(fileOut);
        fileOut.close();
 
        System.out.println("Merged file has been created succesfully");
         } catch ( Exception ex ) {
         ex.printStackTrace();
         }
} catch (FileNotFoundException e) {
System.out.println("==> File Not found");
} catch (IOException e) {
System.out.println("==> IO Exception");
}
}
/**
 * @param args
 */
public static void main(String[] args) {
mergeExcelFiles();
}
}

Here, book1 contains 1 sheet with integers and decimals and book2 contains varchar. i need create a merged file with two tabs numbers and varchars.

Anybody help on this.
Thanks in advance.
[ October 23, 2008: Message edited by: Martijn Verburg ]

[Ulf Dittmer]

I'm surprised it works like that at all (well, it seems it doesn't :-). I would have thought that one would need to create cells, cell contents and cell styles for each cell in each sheet. That's what I suggested.

[Martijn Verburg]

Hi Srikanth,

Thanks for changing the display name, much appreciated! I just thought I'd point out a handy tip for you since you are new to Javaranch. You can UseCodeTags to make your code examples stand out more. I've done this for you in this instance.

Happy questioning!

[Alex Krenvalk]

For work with excel files advise use-Excel file error: data may have been lost,because as far as i know tool is freeware,it save important information,such as graphics,statistic and mathematics,program will help you to recover valuable information and avoid its losses,tool scans your broken worksheet,then gets the data from this document,will help you to repair damaged files in Microsoft Excel sheet recognizable format,repair file Excel this file is not in a recognizable format, Excel showing this file does not in a recognizable format, or Microsoft Excel worksheet this file is not in a recognizeable format, it is an Excel file error: data may have been lost: Microsoft
Excel impossible read file,tool performs a scan of your corrupt excel files not recognizable format and attempts to recover all available data.

[Ulf Dittmer]

In the interest of full disclosure it should be noted that Alex Krenvalk seems somehow involved with the commercial tool he recommends.

[Sivaraman Lakshmanan]

Hi,
Hope this code can help you in copying the sheets.
Copy Sheets

[Evgeniy Bulanov]

Thanks 4 all replays in the topic. (Personally to Srikanth Kumar and Sivaraman Lakshmanan)
I modify a bit code posted by Sivaraman Lakshmanan. Now it supports copying cellstyles.

package org.mit.svodi.excel;

import java.util.Collection;
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.hssf.util.Region;

/**
 *
 * @author jk
 * getted from http://jxls.cvs.sourceforge.net/jxls/jxls/src/java/org/jxls/util/Util.java?revision=1.8&view=markup
 * by Leonid Vysochyn 
 * and modified (adding styles copying)
 */
public class Util {
    public static void copySheets(HSSFSheet newSheet, HSSFSheet sheet){
        copySheets(newSheet, sheet, true);
    }
    public static void copySheets(HSSFSheet newSheet, HSSFSheet sheet, boolean copyStyle){
        int maxColumnNum = 0;
        Map<Integer, HSSFCellStyle> styleMap = (copyStyle)
                ? new HashMap<Integer, HSSFCellStyle>() : null;

        for (int i = sheet.getFirstRowNum(); i <= 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() > maxColumnNum) {
                    maxColumnNum = srcRow.getLastCellNum();
                }
            }
        }
        for (int i = 0; i <= maxColumnNum; i++) {
            newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
        }
    }

    public static void copyRow(HSSFSheet srcSheet, HSSFSheet destSheet, HSSFRow srcRow, HSSFRow destRow, Map<Integer, HSSFCellStyle> styleMap) {
        Set mergedRegions = new TreeSet();
        destRow.setHeight(srcRow.getHeight());
        for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
            HSSFCell oldCell = srcRow.getCell(j);
            HSSFCell newCell = destRow.getCell(j);
            if (oldCell != null) {
                if (newCell == null) {
                    newCell = destRow.createCell(j);
                }
                copyCell(oldCell, newCell, styleMap);
                Region mergedRegion = getMergedRegion(srcSheet, srcRow.getRowNum(), oldCell.getCellNum());
                if (mergedRegion != null) {
//                    Region newMergedRegion = new Region( destRow.getRowNum(), mergedRegion.getColumnFrom(),
//                            destRow.getRowNum() + mergedRegion.getRowTo() - mergedRegion.getRowFrom(), mergedRegion.getColumnTo() );
                    Region newMergedRegion = new Region(mergedRegion.getRowFrom(), mergedRegion.getColumnFrom(),
                            mergedRegion.getRowTo(), mergedRegion.getColumnTo());
                    if (isNewMergedRegion(newMergedRegion, mergedRegions)) {
                        mergedRegions.add(newMergedRegion);
                        destSheet.addMergedRegion(newMergedRegion);
                    }
                }
            }
        }
        
    }
    public static void copyCell(HSSFCell oldCell, HSSFCell newCell, Map<Integer, HSSFCellStyle> 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;
        }
        
    }
    public static Region getMergedRegion(HSSFSheet sheet, int rowNum, short cellNum) {
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            Region merged = sheet.getMergedRegionAt(i);
            if (merged.contains(rowNum, cellNum)) {
                return merged;
            }
        }
        return null;
    }

    private static boolean isNewMergedRegion(Region region, Collection mergedRegions) {
        return !mergedRegions.contains(region);
    }
}

I hope it will be helpful for somebody ;)

[Ph. Loep]

Hi,

I found this old thread via Google and I am happy that the code posted by Evgeniy Bulanov works well.
But I modified the code a little bit to get rid of the deprecated classes and methods and changed the raw-types to generic-types. Now there are no warnings left and still it works fine!

import java.util.Collection;
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&view=markup
 * by Leonid Vysochyn 
 * and modified (adding styles copying)
 * modified by Philipp Löpmeier (replacing deprecated classes and methods, using generic types)
 */
public class Util {
	
    public static void copySheets(HSSFSheet newSheet, HSSFSheet sheet){
        copySheets(newSheet, sheet, true);
    }
    
    public static void copySheets(HSSFSheet newSheet, HSSFSheet sheet, boolean copyStyle){
        int maxColumnNum = 0;
        Map<Integer, HSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, HSSFCellStyle>() : null;
        for (int i = sheet.getFirstRowNum(); i <= 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() > maxColumnNum) {
                    maxColumnNum = srcRow.getLastCellNum();
                }
            }
        }
        for (int i = 0; i <= maxColumnNum; i++) {
            newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
        }
    }

    public static void copyRow(HSSFSheet srcSheet, HSSFSheet destSheet, HSSFRow srcRow, HSSFRow destRow, Map<Integer, HSSFCellStyle> styleMap) {
        Set<CellRangeAddress> mergedRegions = new TreeSet<CellRangeAddress>();
        destRow.setHeight(srcRow.getHeight());
        for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
            HSSFCell oldCell = srcRow.getCell(j);
            HSSFCell newCell = destRow.getCell(j);
            if (oldCell != null) {
                if (newCell == null) {
                    newCell = destRow.createCell(j);
                }
                copyCell(oldCell, newCell, styleMap);
                CellRangeAddress mergedRegion = getMergedRegion(srcSheet, srcRow.getRowNum(), (short)oldCell.getColumnIndex());
                if (mergedRegion != null) {
                	CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow(), mergedRegion.getFirstColumn(), mergedRegion.getLastRow(), mergedRegion.getLastColumn());
                    if (isNewMergedRegion(newMergedRegion, mergedRegions)) {
                        mergedRegions.add(newMergedRegion);
                        destSheet.addMergedRegion(newMergedRegion);
                    }
                }
            }
        }
        
    }
    
    public static void copyCell(HSSFCell oldCell, HSSFCell newCell, Map<Integer, HSSFCellStyle> 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;
        }
        
    }
    
    public static CellRangeAddress getMergedRegion(HSSFSheet sheet, int rowNum, short cellNum) {
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress merged = sheet.getMergedRegion(i);
            if (merged.isInRange(rowNum, cellNum)) {
                return merged;
            }
        }
        return null;
    }

    private static boolean isNewMergedRegion(CellRangeAddress newMergedRegion, Collection<CellRangeAddress> mergedRegions) {
        return !mergedRegions.contains(newMergedRegion);
    }
    
}

The "Add a way to copy sheets"-feature ist still on the Todo List of the Apache POI project, may we send them our code as a proposition?

[Lester Burnham]

I'm sure they welcome code contributions.

[Evgeniy Bulanov]

Hi Ph. Loep,
I am very glad that my code was helpful for you. Thank you for code contributions ;)

[Srikanth Kumar]

Thanks a lot for all your replies.

[Martijn Verburg]

And welcome to Javaranch Ph. Loep!

[jesper johnsen]

Hi all

I would like to copy a sheet as well, and I have done this with 1 VBA line, can it really be true that I need 100+ lines of JAVA to do this

Here is my VBA line:
Workbooks("source.xls").Sheets(1).Move Before:=Workbooks("Destination.xls").Sheets(2)

both files must be open, so that makes it 3 lines of code...
I started out using JACOB, but I could not understand the dispatch syntax, so I started looking at POI, so can any of you convert this single line of code, into POI, or JACOB?
the good thing about the VBA code, is that it takes everything including graphs...

Regards
Jesper Johnsen

[Martijn Verburg]

Hi Jesper and welcome to Javaranch!

It does seem strange that so much code is needed in the POI library - hopefully someone more knowledgeable than I can come up with an answer!

[amanda jones]

Hi Ph. Loep

when you change the deprecated method you forget that CellRangeAddress does not implement Comparable so the method isNewMergedRegion does not work...
Thanks

[Pierre Guilbert]

Hi,

As Amanda Jones pointed out, there is a problem. I spent some time this morning to fix some of it.

Maybe it's not pretty, but it's doing the job.

    private static boolean areAllTrue(boolean... values) {
        for (int i = 0; i < values.length; ++i) {
            if (values[i] != true) {
                return false;
            }
        }
        return true;
    }


    private static boolean isNewMergedRegion(CellRangeAddress newMergedRegion,
            Collection<CellRangeAddress> mergedRegions) {
       
        boolean isNew = true;

        // we want to check if newMergedRegion is contained inside our collection
        for (CellRangeAddress add : mergedRegions) {

            boolean r1 = (add.getFirstRow() == newMergedRegion.getFirstRow());
            boolean r2 = (add.getLastRow() == newMergedRegion.getLastRow());
            boolean c1 = (add.getFirstColumn() == newMergedRegion.getFirstColumn());
            boolean c2 = (add.getLastColumn() == newMergedRegion.getLastColumn());
            if (areAllTrue(r1,r2,c1,c2)) {
             isNew = false;
            }
        }

        return isNew;
    }

Plus you have to change this part in the copyRow method. CellRangeAddress constructor is called with the wrong arguments

              if (mergedRegion != null)
                {
                    //System.out.println(mergedRegion.toString());
                    CellRangeAddress newMergedRegion =
                            new CellRangeAddress(
                            mergedRegion.getFirstRow(),
                            mergedRegion.getLastRow(),
                            mergedRegion.getFirstColumn(),
                            mergedRegion.getLastColumn());

                    if (isNewMergedRegion(newMergedRegion, mergedRegions))
                    {
                        mergedRegions.add(newMergedRegion);
                        destSheet.addMergedRegion(newMergedRegion);
                    }

                }

Ps: It fixes some issues, but not all of them.

[Ernesto Esteban]

Hi all,

I spent some days trying to get have something working fine, thanks to all your post. Here is what I've done :

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&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<Integer, HSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, HSSFCellStyle>() : null;   
              for (int i = sheet.getFirstRowNum(); i <= 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() > maxColumnNum) {   
                          maxColumnNum = srcRow.getLastCellNum();   
                      }   
                  }   
              }   
              for (int i = 0; i <= 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<Integer, HSSFCellStyle> styleMap) {   
              // manage a list of merged zone in order to not insert two times a merged zone
            Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();   
              destRow.setHeight(srcRow.getHeight());   
              // pour chaque row
              for (int j = srcRow.getFirstCellNum(); j <= 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<Integer, HSSFCellStyle> 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 < 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<CellRangeAddressWrapper> mergedRegions) {
            return !mergedRegions.contains(newMergedRegion);   
          }   
             
      } 

and

import org.apache.poi.ss.util.CellRangeAddress;
 

 
public class CellRangeAddressWrapper implements Comparable<CellRangeAddressWrapper> {
 
      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() < o.range.getFirstColumn()
                              && range.getFirstRow() < o.range.getFirstRow()) {
                        return -1;
                  } else if (range.getFirstColumn() == o.range.getFirstColumn()
                              && range.getFirstRow() == o.range.getFirstRow()) {
                        return 0;
                  } else {
                        return 1;
                  }
 
      }
      
}

I created a wrapper for the CellRangeAddress class that implements Comparable. This allows the set to work.

++
kins

[hamza abd]

Ernesto Esteban wrote:Hi all,

I spent some days trying to get have something working fine, thanks to all your post. Here is what I've done :

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&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<Integer, HSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, HSSFCellStyle>() : null;   
              for (int i = sheet.getFirstRowNum(); i <= 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() > maxColumnNum) {   
                          maxColumnNum = srcRow.getLastCellNum();   
                      }   
                  }   
              }   
              for (int i = 0; i <= 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<Integer, HSSFCellStyle> styleMap) {   
              // manage a list of merged zone in order to not insert two times a merged zone
            Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();   
              destRow.setHeight(srcRow.getHeight());   
              // pour chaque row
              for (int j = srcRow.getFirstCellNum(); j <= 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<Integer, HSSFCellStyle> 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 < 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<CellRangeAddressWrapper> mergedRegions) {
            return !mergedRegions.contains(newMergedRegion);   
          }   
             
      } 

and

import org.apache.poi.ss.util.CellRangeAddress;
 

 
public class CellRangeAddressWrapper implements Comparable<CellRangeAddressWrapper> {
 
      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() < o.range.getFirstColumn()
                              && range.getFirstRow() < o.range.getFirstRow()) {
                        return -1;
                  } else if (range.getFirstColumn() == o.range.getFirstColumn()
                              && range.getFirstRow() == o.range.getFirstRow()) {
                        return 0;
                  } else {
                        return 1;
                  }
 
      }
      
}

I created a wrapper for the CellRangeAddress class that implements Comparable. This allows the set to work.

++
kins

Hello Ernesto,
Thank you very much for your code. I was able to copy one sheet to another. Do you know however how to preserve sheet aspect ?
my src sheet has 35% zoom and perfectly fits on an A4 page. Once copied into the new workbook it does not fit (zoom, print margins, row breaks all change)

Thanks for your help

[omar kamil]

Hi,

i 'm getting this error when i copy a sheet from workbook (XSSF) to another workbook using poi3.7 and this util class.

error :
java.lang.IllegalArgumentException: Cell index must be >= 0

Please any Help.

How to copy XSSF Sheet from workbook to another.

thanks in advance.

[omar kamil]

Any update on this issue.

Please Help.


Thanks in advance.

[Amit Raphael]

While setting something like color for a whole row by pressing on the row number,
the "firstCellNum" is -1.

I have changed line 70 on class Util from:
for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {

to:
int j = srcRow.getFirstCellNum();
if(j<0){j=0;}
for (; j <= srcRow.getLastCellNum(); j++) {

and the problem stoped.

[fabio biscaro]

I found also another improvement to the code: if you copy rows in a different row number merged regions are still copied to the original row numbers.

I modified slightly the code adding "deltaRows"

    public static void copyRow(HSSFSheet srcSheet, HSSFSheet destSheet, HSSFRow srcRow, HSSFRow destRow, Map<Integer, HSSFCellStyle> styleMap) {   
        // manage a list of merged zone in order to not insert two times a merged zone
      Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();   
        destRow.setHeight(srcRow.getHeight());   
        // reckoning delta rows
        int deltaRows = destRow.getRowNum()-srcRow.getRowNum();
        // pour chaque row
        for (int j = srcRow.getFirstCellNum(); j <= 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()+deltaRows, mergedRegion.getLastRow()+deltaRows, 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);   
                    }   
                }   
            }   
        }              
    }  

[Sartner huang]

There is some problems with the solution.

The Excel sheet render is with order;


when sheet1 finished rendering the sheet2 rendering is just started;
so
if sheet1 has a formula with relevance to sheet2. the formula will be #REF!

We need some extra code to refresh the formula when the whole excel is finished rendering
Here, I give a solution

We need a POJO to store the formula information,
like: sheet name , row index, cell index and the formula
and then
when we copy a cell to another sheet cell
if the cell type is HSSFCell.CELL_TYPE_FORMULA
we need to add the cell information to a collection as you like (of course you need to create a collection in the class)
OK~ excuse my poor English

I give the solution in the following

1. we need a POJO to save the formula infomation
I create a private inner static class named FormulaInfo

private static class FormulaInfo{


        private String sheetName;
        private Integer rowIndex;
        private Integer cellIndex;
        private String formula;

        private FormulaInfo(String sheetName, Integer rowIndex, Integer cellIndex, String formula) {
            this.sheetName = sheetName;
            this.rowIndex = rowIndex;
            this.cellIndex = cellIndex;
            this.formula = formula;
        }

        public String getSheetName() {
            return sheetName;
        }

        public void setSheetName(String sheetName) {
            this.sheetName = sheetName;
        }

        public Integer getRowIndex() {
            return rowIndex;
        }

        public void setRowIndex(Integer rowIndex) {
            this.rowIndex = rowIndex;
        }

        public Integer getCellIndex() {
            return cellIndex;
        }

        public void setCellIndex(Integer cellIndex) {
            this.cellIndex = cellIndex;
        }

        public String getFormula() {
            return formula;
        }

        public void setFormula(String formula) {
            this.formula = formula;
        }
    }

2. create a formulaInfo Collection in the class

private List<FormulaInfo> formulaInfoList = new ArrayList<FormulaInfo>();

3. in copyCell(), when you detected the cell is a FormulaType m you need to add informatiton to the formulaInfoList

            case HSSFCell.CELL_TYPE_FORMULA:
                newCell.setCellFormula(oldCell.getCellFormula());
                formulaInfoList.add(new FormulaInfo(oldCell.getSheet().getSheetName(),oldCell.getRowIndex(),oldCell.getColumnIndex(),oldCell.getCellFormula()));
                break;

4. after create the whole WorkBook, we need to refresh the formula

    public void refreshFormula(HSSFWorkbook workbook){
        for(FormulaInfo formulaInfo :formulaInfoList){
            workbook.getSheet(formulaInfo.getSheetName()).getRow(formulaInfo.getRowIndex()).getCell(formulaInfo.getCellIndex()).setCellFormula(formulaInfo.getFormula());
        }
        formulaInfoList.removeAll(formulaInfoList);
    }