POI进行ExcelSheet的拷贝

POI进行ExcelSheet的拷贝

学习了：http://www.360doc.com/content/17/0508/20/42823223_652205632.shtml，这个也需要改改

这个：http://blog.csdn.net/wutbiao/article/details/8696446#有些问题

目前格式还是无法拷贝，如果拷贝格式会导致wookbook为空；

 

package com.srie.excel.controller;
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.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
public class POIUtils {
    /**
     * 拷贝Excel行
     * 
     * @param fromsheet
     * @param newsheet
     * @param firstrow
     * @param lastrow
     */
    @SuppressWarnings("deprecation")
    public void copyRows(  HSSFSheet fromsheet, HSSFSheet newsheet ) {
        int firstrow = fromsheet.getFirstRowNum(); 
        int lastrow = fromsheet.getLastRowNum();
        
        if ((firstrow == -1) || (lastrow == -1) || lastrow < firstrow) {
            return;
        }
        // 拷贝合并的单元格
        for (int i = 0; i < fromsheet.getNumMergedRegions(); i++) {
            CellRangeAddress mergedRegion = fromsheet.getMergedRegion(i);
            newsheet.addMergedRegion(mergedRegion);
        }
        HSSFRow fromRow = null;
        HSSFRow newRow = null;
        HSSFCell newCell = null;
        HSSFCell fromCell = null;
        // 设置列宽
        for (int i = firstrow; i <= lastrow; i++) {
            fromRow = fromsheet.getRow(i);
            if (fromRow != null) {
                for (int j = fromRow.getLastCellNum(); j >= fromRow.getFirstCellNum(); j--) {
                    int colnum = fromsheet.getColumnWidth((short) j);
                    if (colnum > 100) {
                        newsheet.setColumnWidth((short) j, (short) colnum);
                    }
                    if (colnum == 0) {
                        newsheet.setColumnHidden((short) j, true);
                    } else {
                        newsheet.setColumnHidden((short) j, false);
                    }
                }
                break;
            }
        }
        // 拷贝行并填充数据
        for (int i = 0; i <= lastrow; i++) {
            fromRow = fromsheet.getRow(i);
            if (fromRow == null) {
                continue;
            }
            newRow = newsheet.createRow(i - firstrow);
            newRow.setHeight(fromRow.getHeight());
            for (int j = fromRow.getFirstCellNum(); j < fromRow.getPhysicalNumberOfCells(); j++) {
                fromCell = fromRow.getCell((short) j);
                if (fromCell == null) {
                    continue;
                }
                newCell = newRow.createCell((short) j);
                HSSFCellStyle cellStyle = fromCell.getCellStyle();
                
//                HSSFCellStyle newStyle = newsheet.getWorkbook().createCellStyle();
//                newStyle.cloneStyleFrom(cellStyle);
//                newCell.setCellStyle(newStyle);
                
//                HSSFCellStyle cellStyle2 = newCell.getCellStyle();
//                cellStyle2.setFillForegroundColor(cellStyle.getFillForegroundColor());
//                cellStyle2.setFillPattern(cellStyle.getFillPattern());
//                cellStyle2.setAlignment(cellStyle.getAlignment());
//                cellStyle2.setVerticalAlignment(cellStyle.getVerticalAlignment());
                
                int cType = fromCell.getCellType();
                newCell.setCellType(cType);
                switch (cType) {
                case HSSFCell.CELL_TYPE_STRING:
                    newCell.setCellValue(fromCell.getRichStringCellValue());
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    newCell.setCellValue(fromCell.getNumericCellValue());
                    break;
                case HSSFCell.CELL_TYPE_FORMULA:
                    newCell.setCellFormula(fromCell.getCellFormula());
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    newCell.setCellValue(fromCell.getBooleanCellValue());
                    break;
                case HSSFCell.CELL_TYPE_ERROR:
                    newCell.setCellValue(fromCell.getErrorCellValue());
                    break;
                default:
                    newCell.setCellValue(fromCell.getRichStringCellValue());
                    break;
                }
            }
        }
    }
    public static void main(String[] args) {}
}