java - Copying Excel Worksheets in POI

Question

Does anyone know of a means to copy a worksheet from one workbook to another using POI? The Workbook class has a cloneSheet method, but there doesn't seem to be able to insert a cloned sheet into a new workbook?

If there isn't an API to do this easily, does anyone have the code to copy all of the data (styles, column widths, data, etc) from one sheet to another?

The jxls has methods to copy sheets, but they don't work when copying between workbooks.

Answer 1

i have implemented some functionality with poi. please see the code for your reference.

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.FileInputStream;
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 ExcelReadAndWrite {

    public static void main(String[] args) throws IOException {
        ExcelReadAndWrite excel = new ExcelReadAndWrite();
        excel.process("D:/LNN/My Workspace/POI/src/tables.xls");
    }

    public void process(String fileName) throws IOException {
        BufferedInputStream bis = new BufferedInputStream(new FileInputStream(fileName));
        HSSFWorkbook workbook = new HSSFWorkbook(bis);
        HSSFWorkbook myWorkBook = new HSSFWorkbook();
        HSSFSheet sheet = null;
        HSSFRow row = null;
        HSSFCell cell = null;
        HSSFSheet mySheet = null;
        HSSFRow myRow = null;
        HSSFCell myCell = null;
        int sheets = workbook.getNumberOfSheets();
        int fCell = 0;
        int lCell = 0;
        int fRow = 0;
        int lRow = 0;
        for (int iSheet = 0; iSheet < sheets; iSheet++) {
            sheet = workbook.getSheetAt(iSheet);
            if (sheet != null) {
                mySheet = myWorkBook.createSheet(sheet.getSheetName());
                fRow = sheet.getFirstRowNum();
                lRow = sheet.getLastRowNum();
                for (int iRow = fRow; iRow <= lRow; iRow++) {
                    row = sheet.getRow(iRow);
                    myRow = mySheet.createRow(iRow);
                    if (row != null) {
                        fCell = row.getFirstCellNum();
                        lCell = row.getLastCellNum();
                        for (int iCell = fCell; iCell < lCell; iCell++) {
                            cell = row.getCell(iCell);
                            myCell = myRow.createCell(iCell);
                            if (cell != null) {
                                myCell.setCellType(cell.getCellType());
                                switch (cell.getCellType()) {
                                case HSSFCell.CELL_TYPE_BLANK:
                                    myCell.setCellValue("");
                                    break;

                                case HSSFCell.CELL_TYPE_BOOLEAN:
                                    myCell.setCellValue(cell.getBooleanCellValue());
                                    break;

                                case HSSFCell.CELL_TYPE_ERROR:
                                    myCell.setCellErrorValue(cell.getErrorCellValue());
                                    break;

                                case HSSFCell.CELL_TYPE_FORMULA:
                                    myCell.setCellFormula(cell.getCellFormula());
                                    break;

                                case HSSFCell.CELL_TYPE_NUMERIC:
                                    myCell.setCellValue(cell.getNumericCellValue());
                                    break;

                                case HSSFCell.CELL_TYPE_STRING:
                                    myCell.setCellValue(cell.getStringCellValue());
                                    break;
                                default:
                                    myCell.setCellFormula(cell.getCellFormula());
                                }
                            }
                        }
                    }
                }
            }
        }
        bis.close();
        BufferedOutputStream bos = new BufferedOutputStream(
                new FileOutputStream("workbook.xls", true));
        myWorkBook.write(bos);
        bos.close();
    }
}