复制sheet模板

48 阅读 0 评论 32 点赞

我是靠谱客的博主乐观黑夜，最近开发中收集的这篇文章主要介绍复制sheet模板，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

public class CopySheetUtil {
public CopySheetUtil() {
     }

     public static void copySheets(Sheet newSheet, Sheet sheet) {
         copySheets(newSheet, sheet, true);
     }

     public static void copySheets(Sheet newSheet, Sheet sheet,
             boolean copyStyle) {
         int maxColumnNum = 0;
         Map<Integer, CellStyle> styleMap = (copyStyle) ? new HashMap<Integer, CellStyle>()
                 : null;
         for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
         Row srcRow = sheet.getRow(i);
         Row destRow = newSheet.createRow(i);
             if (srcRow != null) {
                 CopySheetUtil.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 newSheet
      * @param sheet
      * @param copyStyle
      /
     public static void copyRow(Sheet srcSheet, Sheet destSheet,
             Row srcRow, Row destRow,
             Map<Integer, CellStyle> styleMap) {
         Set mergedRegions = new TreeSet();
         destRow.setHeight(srcRow.getHeight());
         int deltaRows = destRow.getRowNum() - srcRow.getRowNum(); //如果copy到另一个sheet的起始行数不同
         for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum()+1; j++) {
         if(j>=0) {
             Cell oldCell = srcRow.getCell(j); // old cell
               Cell newCell = destRow.getCell(j); // new cell
                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() + deltaRows,
                                mergedRegion.getLastRow() + deltaRows, mergedRegion
                                        .getFirstColumn(), mergedRegion
                                        .getLastColumn());
                        CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion);
                        if (isNewMergedRegion(wrapper, mergedRegions)) {
                            mergedRegions.add(wrapper);
                            destSheet.addMergedRegion(wrapper.range);
                        }
                    }
                }
         }
         }
     }

     /*
      * 把原来的Sheet中cell（列）的样式和数据类型复制到新的sheet的cell（列）中
      *
      * @param oldCell
      * @param newCell
      * @param styleMap
      */
     public static void copyCell(Cell oldCell, Cell newCell,
             Map<Integer, CellStyle> styleMap) {
         if (styleMap != null) {
             if (oldCell.getSheet().getWorkbook() == newCell.getSheet()
                     .getWorkbook()) {
                 newCell.setCellStyle(oldCell.getCellStyle());
             } else {
                 int stHashCode = oldCell.getCellStyle().hashCode();
                 CellStyle 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 Cell.CELL_TYPE_STRING:
             newCell.setCellValue(oldCell.getStringCellValue());
             break;
         case Cell.CELL_TYPE_NUMERIC:
             newCell.setCellValue(oldCell.getNumericCellValue());
             break;
         case Cell.CELL_TYPE_BLANK:
             newCell.setCellType(Cell.CELL_TYPE_BLANK);
             break;
         case Cell.CELL_TYPE_BOOLEAN:
             newCell.setCellValue(oldCell.getBooleanCellValue());
             break;
         case Cell.CELL_TYPE_ERROR:
             newCell.setCellErrorValue(oldCell.getErrorCellValue());
             break;
         case Cell.CELL_TYPE_FORMULA:
             newCell.setCellFormula(oldCell.getCellFormula());
             break;
         default:
             break;
         }

     }

     // 获取merge对象
     public static CellRangeAddress getMergedRegion(Sheet 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(
             CellRangeAddressWrapper newMergedRegion,
             Set mergedRegions) {
         boolean bool = mergedRegions.contains(newMergedRegion);
         return !bool;
     }
}