java-EasyExcelFactory实现Excel复杂表头导出数据

导出的目标格式 包括合并和多级表头排列

具体实现

1、pom配置参考

<!-- easyExcel -->
<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>easyexcel</artifactId>
  <version>3.2.1</version>
  <!-- 3+以上版本的easyExcel，使用poi 5+以上版本时，需要手动排除：poi-ooxml-schemas -->
  <exclusions>
     <exclusion>
         <artifactId>poi-ooxml-schemas</artifactId>
         <groupId>org.apache.poi</groupId>
     </exclusion>
  </exclusions>
</dependency>

2、返回的数据格式

private static HttpServletResponse putResponseInfo(HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
        String filename = URLEncoder.encode(fileName+".xlsx", "UTF-8");
        response.reset();
        response.setCharacterEncoding("utf-8");
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Access-Control-Allow-Origin","*");
        response.setHeader("Content-disposition", "attachment;filename=" + filename);
        return response;
 }

3、导出：设置返回数据格式-设置表头格式-放入数据-写出excel

public static void exportExcel(HttpServletResponse response, List<youDate> youDateList,List<TypeVO> typeList,String fileName) {
        try {
            response = putResponseInfo(response,fileName);

            //获取表头名称
            List<List<String>> sheetHead = getFirstSheetHead(typeList);
            //存放数据
            List<List<Object>> sheetData = getFirstSheetData(youDateList);
            ServletOutputStream out = response.getOutputStream();
            ExcelWriter writer = write(out).build();

            WriteSheet firstSheet = new WriteSheet();
            firstSheet.setSheetName("sheet表名称");
            firstSheet.setSheetNo(0);
            WriteTable firstTable = new WriteTable();
            firstTable.setTableNo(1);
            firstTable.setHead(sheetHead);
            writer.write(sheetData, firstSheet, firstTable);
            writer.finish();
        } catch (Exception var8) {
            var8.printStackTrace();
            throw new RuntimeException("导出xx失败");
        }
    }

private static List<List<String>> getFirstSheetHead(List<TypeVO> typeList) {
        //表头
        List<List<String>> headTitles = Lists.newArrayList();
        //固定title
        String emptyHead = "";
        headTitles.add(Lists.newArrayList( "回路名称"));
        headTitles.add(Lists.newArrayList( "日期"));
        //二级表头
        List<String> peakTitles = Lists.newArrayList("最大值", "最小值");
        //三级表头
        List<String> valueTitles = Lists.newArrayList("数值", "发生时间");
        //子项电力类别
        for (TypeVO powerType : typeList) {
            for (String peakTitle : peakTitles) {
                for (String valueTitle : valueTitles){
                    headTitles.add(Lists.newArrayList(powerType.getTableName(), peakTitle, valueTitle));
                }
            }
            headTitles.add(Lists.newArrayList(powerType.getTableName(), "平均值"));
        }
        return headTitles;
    }

private static List<List<Object>> getFirstSheetData(List<YouDataVO> dataList) {
            List<List<Object>> sheetDataList = Lists.newArrayList();
            for (YouDataVO resultVO:dataList) {
                List bean = Lists.newArrayList(resultVO.getFCircuitName());
                bean.add(resultVO.getFCollectDate());
                bean = peakListInfo(resultVO.getPeakInfoList(),bean);
                sheetDataList.add(bean);
            }
            return sheetDataList;
}

private static List peakListInfo(List<PeakPowerInfoVO> peakInfoList, List bean) {
        for (PeakPowerInfoVO peakPowerInfoVO:peakInfoList) {
            bean.add(peakPowerInfoVO.getMaxValue() != null ? peakPowerInfoVO.getMaxValue():0);
            bean.add(peakPowerInfoVO.getMaxTime());
            bean.add(peakPowerInfoVO.getMinValue()!= null ? peakPowerInfoVO.getMinValue():0);
            bean.add(peakPowerInfoVO.getMinTime());
            bean.add(peakPowerInfoVO.getAvgValue()!= null ? peakPowerInfoVO.getAvgValue():0);
        }
        return bean;
    }