复制带表头的excel sheet页(poi的HSSFWorkbook)

HSSFWorkbook导出excel时会有65535行的限制，当我们数据量大的时候可以分多个sheet页的方案来解决，

但是每个sheet页都需要读取下载模版文件的表头，而poi库对一个excel之间的多sheet页都读取模版excel文件不支持，

本文的思路是先生成多个excel文件到内存，然后再将这些excel复制到一个文件的多个sheet页中。

一 . 首先查出来数据总数，按6万条一个excel进行生成。

　　//生成excel
    @GetMapping("/generate")
    public void generate(String fileName){
        List<Map<String,Object>> dataList=new ArrayList();
        //测试数据
        for (int i=0;i<200000;i++){
            Map m1 = new HashMap();
            m1.put("Zara", "8bbb");
            m1.put("Mahnaz", "31cc");
            m1.put("Ayan", "12");
            m1.put("Daisy", "14111aa");
            dataList.add(m1);
        }
        //单个sheet处理65536条最多
        final int sheetNum= new Double(Math.ceil((float)dataList.size()/60000)).intValue();
　　　　　
        HSSFWorkbook sumWorkBook =new HSSFWorkbook();

        for (int i=0;i<sheetNum;i++){
            HSSFWorkbook workbook1=exportExcelByPostFix(dataList.subList(60000*i,
                    (i!=(sheetNum-1)?(60000*(i+1)):dataList.size())));
            HSSFSheet sheet1=workbook1.getSheetAt(0);
            HSSFSheet newsheet =sumWorkBook.createSheet("sheet"+(i+1));
            ExcelUtil.copySheet(sumWorkBook,sheet1,newsheet);
        }

        //生成文件
        exportFile(sumWorkBook,fileName);
        return Result.succ("copy");
    }

 二 . 将每6万条数据生成一个excel到内存中，即excel的第一个sheet页

public HSSFWorkbook exportExcelByPostFix(List<Map<String,Object>> dataList){
        //获取模版表头
        Resource resourceData=new DefaultResourceLoader().getResource("classpath:doctemp/temp.xls");
        InputStream inputStream=null;
        HSSFWorkbook wb=null;
        try {
            //获取输入流
            inputStream= resourceData.getInputStream();
            //创建带表头的工作表
            wb=new HSSFWorkbook(inputStream);

            //设置单元格基础样式，加边框，加基础黑框
            CellStyle hssfCellStyle=wb.createCellStyle();
            hssfCellStyle.setBorderBottom(BorderStyle.THIN);
            hssfCellStyle.setBorderLeft(BorderStyle.THIN);
            hssfCellStyle.setBorderRight(BorderStyle.THIN);
            hssfCellStyle.setBorderTop(BorderStyle.THIN);
            //字居中
            hssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
            //获取第一个sheet页
            HSSFSheet sheet=wb.getSheetAt(0);
            //前五行表头
            int count=5;
            ExcelUtil.exportForData(dataList,sheet,hssfCellStyle,count);
        }
        catch (IOException e){

        }
        finally {

        }
        return wb;
    }

三.生成excel工具类，以及复制sheet页的方法

public class ExcelUtil {
    /**
     * sheet复制
     */
    public static void copySheet(HSSFWorkbook wb, HSSFSheet fromSheet, HSSFSheet toSheet){

        //合并区域处理
        mergeSheetAllRegion(fromSheet, toSheet);
        // 列宽
        int length = fromSheet.getRow(fromSheet.getFirstRowNum()).getLastCellNum();
        for (int i = 0; i <= length; i++) {
            toSheet.setColumnWidth(i, fromSheet.getColumnWidth(i));
        }

        int flag=0;
        //设置单元格基础样式，加边框，加基础黑框
        CellStyle hssfCellStyle=wb.createCellStyle();
        hssfCellStyle.setBorderBottom(BorderStyle.THIN);
        hssfCellStyle.setBorderLeft(BorderStyle.THIN);
        hssfCellStyle.setBorderRight(BorderStyle.THIN);
        hssfCellStyle.setBorderTop(BorderStyle.THIN);
        //字居中
        hssfCellStyle.setAlignment(HorizontalAlignment.CENTER);

        for (Iterator rowIt = fromSheet.rowIterator(); rowIt.hasNext(); ) {
            HSSFRow oldRow = (HSSFRow) rowIt.next();
            HSSFRow newRow = toSheet.createRow(oldRow.getRowNum());
            flag++;
            copyRow(wb, oldRow, newRow,flag,hssfCellStyle);
        }
    }
    /**
     * 行复制
     */
    private static void copyRow(HSSFWorkbook wb, HSSFRow oldRow, HSSFRow toRow,int flag,
                                CellStyle hssfCellStyle) {
        toRow.setHeight(oldRow.getHeight());
        for (Iterator cellIt = oldRow.cellIterator(); cellIt.hasNext(); ) {
            HSSFCell tmpCell = (HSSFCell) cellIt.next();
            HSSFCell newCell = toRow.createCell(tmpCell.getColumnIndex());
            copyCell(wb, tmpCell, newCell,flag,hssfCellStyle);
        }
    }
    /**
     * 合并单元格处理
     */
    private static void mergeSheetAllRegion(HSSFSheet fromSheet, HSSFSheet toSheet) {
        int sheetMergeCount = fromSheet.getNumMergedRegions();
        CellRangeAddress cellR = null;
        for (int i = 0; i < sheetMergeCount; i++) {
            cellR = fromSheet.getMergedRegion(i);
            toSheet.addMergedRegion(cellR);
        }
    }
    /**
     * 复制单元格
     */
    private static void copyCell(HSSFWorkbook wb, HSSFCell fromCell, HSSFCell toCell, int flag,
                                 CellStyle hssfCellStyle) {
        //excel表头有5行
        if(flag<6){
            HSSFCellStyle newstyle = wb.createCellStyle();
            // 复制单元格样式
            newstyle.cloneStyleFrom(fromCell.getCellStyle());
            // 样式
            toCell.setCellStyle(newstyle);
        }
        else {
            //其它的单元格用标准格式
            //单元格格式过多会抛出异常，不支持创建太多
            toCell.setCellStyle(hssfCellStyle);
        }

        if (fromCell.getCellComment() != null) {
            toCell.setCellComment(fromCell.getCellComment());
        }
        // 不同数据类型处理
        CellType fromCellType = fromCell.getCellType();
        toCell.setCellType(fromCellType);
        if (fromCellType == CellType.NUMERIC) {
            if (DateUtil.isCellDateFormatted(fromCell)) {
                toCell.setCellValue(fromCell.getDateCellValue());
            } else {
                toCell.setCellValue(fromCell.getNumericCellValue());
            }
        } else if (fromCellType == CellType.STRING) {
            toCell.setCellValue(fromCell.getRichStringCellValue());
        } else if (fromCellType == CellType.BLANK) {
            // nothing21
        } else if (fromCellType == CellType.BOOLEAN) {
            toCell.setCellValue(fromCell.getBooleanCellValue());
        } else if (fromCellType == CellType.ERROR) {
            toCell.setCellErrorValue(fromCell.getErrorCellValue());
        } else if (fromCellType == CellType.FORMULA) {
            toCell.setCellFormula(fromCell.getCellFormula());
        } else {
            // nothing29
        }
    }

    /**
     * 创建单元格
     */
    public static void createCell(Row rows,int column,String value,CellStyle hssfCellStyle){
        Cell cell=rows.createCell(column);
        cell.setCellValue(value);
        cell.setCellStyle(hssfCellStyle);
    }
    /**
     * 根据数据组装cell数据
     * count=5表示从第6行开始生成单元格，前面5行是表头
     */
    public static void exportForData(List<Map<String,Object>> list, HSSFSheet hssfSheet,
                                     CellStyle hssfCellStyle,int count){
        if(list!=null&& list.size()!=0){
            for (int i=0;i<list.size();i++){
                //创建行
                Row rows=hssfSheet.createRow(count);
                Map<String,Object> map= list.get(i);
                //是否总计数据
                int j=0;
                Set<String> sets= map.keySet();
                if(sets==null||sets.size()<1){
                    continue;
                }
                for (String key:sets){
                    createCell(rows,j,String.valueOf(map.get(key)),hssfCellStyle);
                    //在for循环中。单元格纬度下调用该函数，会极大降低效率
                    //所以在最后一行数据时，才调用这个函数
                    if(i==list.size()-1){
                        hssfSheet.autoSizeColumn(j);//按内容撑开单元格宽度
                    }
                    j++;
                }
                count++;
            }
        }
    }
}

四,将生成的每个excel的sheet1合并到一个总的excel中，代码在第一步已经列出。然后生成文文件

private  void exportFile(HSSFWorkbook workbook,String fileName){
        String c="/workspace/java/";
        File file=new File(c);
        if(!file.exists()){
            file.mkdir();
        }

        String exportFilePath=file +"/"+fileName+".xls";
        FileOutputStream outputStream=null;
        try {
            outputStream =new FileOutputStream(exportFilePath);
            workbook.write(outputStream);
            outputStream.flush();
        }
        catch (Exception e){

        }
        finally {

        }
    }

五，结束。需要注意的是window开发环境和部署的linux环境的差别