记录一次复杂的Excel导出（二）

上篇记录到生成Excel，详情看：https://blog.csdn.net/BinshaoNo_1/article/details/114411487

描述：把由html转换成的多个复杂Excel合并到一个Excel多sheet样式

先上代码，封装汇总数据并赋值，然后生成汇总表，这里主要是根据需求生成相应的Excel

public void getReportData(HttpServletResponse response, String startTime, String endTime) throws Exception {
        //忽略部分逻辑代码
        List<TempMonitor> tempMonitorList = baseMapper.getList(startTime, endTime);
        
        //合并后的Excel名称,总表sheet名称
        String filename = "汇总表";
        //操作粮温汇总表
        List<DailyReportVO> allReportList = new ArrayList<>();

        //sheetName集合
        StringJoiner sheetNames = new StringJoiner(",","","");
        sheetNames.add(filename);
        //初始顺序
        int intiValue = 2;
        StringJoiner sheetNamesSort = new StringJoiner(",","","");
        sheetNamesSort.add("1");

        //生成表格的地址集合
        StringJoiner result = new StringJoiner(",","","");
        //汇总表
        result.add(beetlProperties.getHtmlPathIn()+"\\"+filename+".xlsx");
        for (TempMonitor tempMonitor : tempMonitorList){
            //生成单个测试信息并生成表格，详见上一篇
            DailyReportVO dailyReport = htmlToExcel(response,tempMonitor);
            //加入到集合
            result.add(beetlProperties.getHtmlPathIn()+"\\"+tempMonitor.getDevId()+".xlsx");
            sheetNames.add(名称);
            sheetNamesSort.add(排序);
            intiValue++;
            //加入集合
            allReportList.add(dailyReport);
        }
        //汇总操作
        operateAllReport(response,allReportList);


        //合并
        generateManySheet(result.toString(),filename);
        //当天日期
        String format = LocalDateTime.now().format(DateTimeFormatter.ISO_LOCAL_DATE);
        String[] sheetNameArray = sheetNames.toString().split(",");
        String[] split = sheetNamesSort.toString().split(",");

        Integer[] integers = (Integer[]) ConvertUtils.convert(split, Integer.class);

        //下载
        downloadExcelByManySheet(response,beetlProperties.getHtmlPathIn()+"\\"+filename+".xlsx",filename+"_"+format+".xlsx",sheetNameArray,integers);

    }

汇总操作方法，主要生成汇总单个表格(依据需求生成的单表)：

/**
     * 汇总操作，并生成Excel
     * @param response
     * @param allReportList
     */
    private void operateAllReport(HttpServletResponse response,List<DailyReportVO> allReportList) throws Exception {
        //最大层
        int maxLayer = 0;
        //单元格初始大小
        int initValue = 15;
        for (DailyReportVO report : allReportList){
            int layerSize = report.getLayerTempList().size();
            //取最大层
            if (maxLayer < layerSize){
                maxLayer = layerSize;
            }
        }
        //赋值最大层
        allReportList.get(0).setStoreWay(String.valueOf(maxLayer));
        //合并单元格数量--总合并数量
        allReportList.get(0).setHum(String.valueOf((maxLayer-2)*3+initValue));
        //需要延伸的数量
        allReportList.get(0).setGrainOrigin(String.valueOf((maxLayer-2)*3));
        //数据导出
        String sheetName = "汇总表";
        String filePath = beetlProperties.getFilePathIn()+sheetName+".html";
//详见上一篇         ExportHtmlUtil().generateFile(null,allReportList,filePath,sheetName,"/html/all.html.btl");

        
        htmlToExcelToStr(response, filePath, sheetName, "all");
    }

合并生成的多个表格到同一个Excel：

/**
     * 把生成的多个Excel合同一个，并生成Excel
     * @param result
     */
    private void generateManySheet(String result,String filename) throws IOException {
        String[] inputFiles = result.toString().split(",");
        //创建一个新的Excel文档
        XSSFWorkbook newExcelCreat = new XSSFWorkbook();

        for (String file : inputFiles){
            InputStream in = new FileInputStream(file);
            XSSFWorkbook fromExcel = new XSSFWorkbook(in);
            int length = fromExcel.getNumberOfSheets();
            //长度为1时
            if(length<=1){
                XSSFSheet oldSheet = fromExcel.getSheetAt(0);
                XSSFSheet newSheet = newExcelCreat.createSheet(oldSheet.getSheetName());
                CopyWorkBookUtil.copySheet(newExcelCreat, oldSheet, newSheet);
            }else{
                // 遍历每个sheet
                for (int i = 0; i < length; i++) {
                    XSSFSheet oldSheet = fromExcel.getSheetAt(i);
                    XSSFSheet newSheet = newExcelCreat.createSheet(oldSheet.getSheetName());
                    CopyWorkBookUtil.copySheet(newExcelCreat, oldSheet, newSheet);
                }
            }

        }

        try{
            FileOutputStream fileOut = new FileOutputStream(beetlProperties.getHtmlPathIn()+"\\"+filename+".xlsx");
            newExcelCreat.write(fileOut);
            fileOut.close();
            newExcelCreat.close();
        }catch (Exception e){
            log.error("合并异常:{}",e.getMessage());
        }
    }

合并多个表格用到的方法：

public class CopyWorkBookUtil {
    /**
     * Sheet复制
     * @param wb
     * @param fromSheet
     * @param toSheet
     */
    public static void copySheet(XSSFWorkbook wb, XSSFSheet fromSheet, XSSFSheet toSheet) {
        mergeSheetAllRegion(fromSheet, toSheet);
        // 设置列宽
        int length = fromSheet.getRow(fromSheet.getFirstRowNum()).getLastCellNum();
        for (int i = 0; i <= length; i++) {
            toSheet.setColumnWidth(i, fromSheet.getColumnWidth(i));
        }
        for (Iterator rowIt = fromSheet.rowIterator(); rowIt.hasNext();) {
            XSSFRow oldRow = (XSSFRow) rowIt.next();
            XSSFRow newRow = toSheet.createRow(oldRow.getRowNum());
            copyRow(wb, oldRow, newRow);
        }
    }
    /**
     * 行复制功能
     * @param wb
     * @param oldRow
     * @param toRow
     */
    public static void copyRow(XSSFWorkbook wb, XSSFRow oldRow, XSSFRow toRow) {
        toRow.setHeight(oldRow.getHeight());
        for (Iterator cellIt = oldRow.cellIterator(); cellIt.hasNext();) {
            XSSFCell tmpCell = (XSSFCell) cellIt.next();
            XSSFCell newCell = toRow.createCell(tmpCell.getColumnIndex());
            copyCell(wb, tmpCell, newCell);
        }
    }
    /**
     * 合并单元格
     * @param fromSheet
     * @param toSheet
     */
    public static void mergeSheetAllRegion(XSSFSheet fromSheet, XSSFSheet toSheet) {
        int num = fromSheet.getNumMergedRegions();
        CellRangeAddress cellR = null;
        for (int i = 0; i < num; i++) {
            cellR = fromSheet.getMergedRegion(i);
            toSheet.addMergedRegion(cellR);
        }
    }

    /**
     * 复制单元格
     * @param wb
     * @param fromCell
     * @param toCell
     */
    public static void copyCell(XSSFWorkbook wb, XSSFCell fromCell, XSSFCell toCell) {
        XSSFCellStyle newstyle = wb.createCellStyle();
        copyCellStyle(fromCell.getCellStyle(), newstyle);
        //  toCell.setEncoding(fromCell.getStringCelllValue());
        // 样式
        toCell.setCellStyle(newstyle);
        if (fromCell.getCellComment() != null) {
            toCell.setCellComment(fromCell.getCellComment());
        }
        // 不同数据类型处理
        int fromCellType = fromCell.getCellType();
        toCell.setCellType(fromCellType);
        if (fromCellType == XSSFCell.CELL_TYPE_NUMERIC) {
            if (XSSFDateUtil.isCellDateFormatted(fromCell)) {
                toCell.setCellValue(fromCell.getDateCellValue());
            } else {
                toCell.setCellValue(fromCell.getNumericCellValue());
            }
        } else if (fromCellType == XSSFCell.CELL_TYPE_STRING) {
            toCell.setCellValue(fromCell.getRichStringCellValue());
        } else if (fromCellType == XSSFCell.CELL_TYPE_BLANK) {
            // nothing21
        } else if (fromCellType == XSSFCell.CELL_TYPE_BOOLEAN) {
            toCell.setCellValue(fromCell.getBooleanCellValue());
        } else if (fromCellType == XSSFCell.CELL_TYPE_ERROR) {
            toCell.setCellErrorValue(fromCell.getErrorCellValue());
        } else if (fromCellType == XSSFCell.CELL_TYPE_FORMULA) {
            toCell.setCellFormula(fromCell.getCellFormula());
        } else { // nothing29
        }

    }
    public static void copyCellStyle(XSSFCellStyle fromStyle, XSSFCellStyle toStyle) {

        toStyle.cloneStyleFrom(fromStyle);
    }
}

public class XSSFDateUtil extends DateUtil {

}

 

到此，基本已经把复杂导出并把多个复杂导出合并到同一个表格完成，比较粗糙，目前只是实现了项目需求，并无优化，下篇记录一下导出数据的类型问题...