java poi 动态多表头导出数据

1、maven依赖

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.1.2</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>

2、数据集合类

public class Demo1 {

    private int id;

    private String name;

    private int age;

    private List<Demo2> demo2; // List<?> 也可以

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public List<Demo2> getDemo2() {
        return demo2;
    }

    public void setDemo2(List<Demo2> demo2) {
        this.demo2 = demo2;
    }
}

3、表格样式工具类

public class SheetStyle {
    
    /**
     * 水平居中、垂直居中
     * 字体：宋体
     * 字体大小：16号
     * 加粗
     * @param workbook
     * @return
     */
    public static CellStyle getStyle(XSSFWorkbook workbook) {
        CellStyle cellstyle=workbook.createCellStyle();
        cellstyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellstyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        Font font=workbook.createFont();//字体
        font.setFontName("宋体");//字体
        font.setFontHeightInPoints((short)18);//字号
        font.setBold(true);//加粗
        cellstyle.setFont(font);
        setBorderStyle(cellstyle);
        return cellstyle;
    }

    /**
     * 获取默认的cell表格样式，加边框，水平居中，垂直居中
     * @param workbook
     * @return
     */
    public static CellStyle getTextCellStyle(XSSFWorkbook workbook) {
        CellStyle style=workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        setBorderStyle(style);
        return style;
    }
    /**
     * 获取默认的cell表格样式，加边框，水平居中，垂直居中
     * @param workbook
     * @return
     */
    public static CellStyle getNumCellStyle(XSSFWorkbook workbook) {
        CellStyle style=workbook.createCellStyle();
        XSSFDataFormat df = new XSSFWorkbook().createDataFormat();
        style.setAlignment(HorizontalAlignment.RIGHT);//水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        style.setDataFormat(df.getFormat("0.00"));
        Font font=workbook.createFont();//字体
        font.setFontName("宋体");//字体
        font.setFontHeightInPoints((short)11);//字号
        style.setFont(font);
        setBorderStyle(style);
        return style;
    }

    /**
     * 边框样式
     * @param style
     */
    public static void setBorderStyle(CellStyle style) {
        style.setBorderBottom(BorderStyle.THIN); //下边框
        style.setBorderLeft(BorderStyle.THIN);//左边框
        style.setBorderTop(BorderStyle.THIN);//上边框
        style.setBorderRight(BorderStyle.THIN);//右边框
    }

    /**
     * 奇数行
     * 背景颜色为黄色
     * @param style
     */
    public static void setCellStyleYellow(CellStyle style) {
        style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    }
    /**
     * 偶数行
     * 背景颜色为LIME
     * @param style
     */
    public static void setCellStyleLime(CellStyle style) {
        style.setFillForegroundColor(IndexedColors.LIME.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    }
    /**
     * 字体设置红色
     * @param workbook
     * @param style
     */
    public static void setFontRedColor(XSSFWorkbook workbook,CellStyle style) {
        Font font=workbook.createFont();//字体
        font.setColor(IndexedColors.RED.getIndex());
        style.setFont(font);
    }

4、表格生成工具类

public class ExcelExport {
    /**
     * 一对多模式子类行转列导出数据（指定子类某一项作为子类一级标题） 子项为数值类型带汇总
     * @param fatTitle 父类显示列
     * @param sonTitle 子类一级标题
     * @param sonTitleTow 子类二级标题
     * @param dataList 数据行 数据格式 a,b,c,List<d>
     * @throws IOException
     */

    public static void outExcel(
            Map<String,Object> fatTitle,
            List<String> sonTitle,
            Map<String,Object> sonTitleTow,
            List<Map<String,Object>> dataList
    ) throws IOException {
        //创建工作薄对象
        XSSFWorkbook workbook = new XSSFWorkbook();//这里也可以设置sheet的Name
        //创建工作表对象
        XSSFSheet sheet = workbook.createSheet();
        //创建单元格空对象
        XSSFCell cell = null;
        //创建合并空对象
        CellRangeAddress region = null;
        //设置标题
        XSSFRow title = sheet.createRow(0);//设置第一行，从零开始
        //设置标题行高
        title.setHeightInPoints(40);
        //统计合并列(父类属性+子类显示属性*子类标题)
        int titleColumn = fatTitle.size()+(sonTitle.size()+1)*sonTitleTow.size()-1;
        for (int i = 0; i <= titleColumn; i++) {
            cell = title.createCell(i);
            cell.setCellValue("测试数据标题");
            cell.setCellStyle(SheetStyle.getStyle(workbook));
        }
        //合并单元格
        region = new CellRangeAddress(0, 0, 0, titleColumn);
        sheet.addMergedRegion(region);
        sheet.setColumnWidth(0,3000);
        //先创建父类标题
        XSSFRow row = sheet.createRow(1);//设置第一行
        //设置第二行
        XSSFRow row1 = sheet.createRow(2);
        row.setHeightInPoints(20);//设置行高
        row1.setHeightInPoints(18);//设置行高
        int i = 0;  // 列数
        //循环添加父类标题
        for (String s : fatTitle.keySet()) {
            //绘制单元格
            cell=row.createCell(i);
            //添加数据
            cell.setCellValue(fatTitle.get(s).toString());
            //添加样式
            cell.setCellStyle(SheetStyle.getTextCellStyle(workbook));
            //设置要合并的单元格样式
            cell = row1.createCell(i);
            cell.setCellStyle(SheetStyle.getTextCellStyle(workbook));
            //合并 (4个参数，分别为起始行，结束行，起始列，结束列)
            region = new CellRangeAddress(1, 2, i, i++);
            sheet.addMergedRegion(region);
            sheet.setColumnWidth(i,3000);
        }
        //循环添加子类一级标题
        int i_1=i;  // 子类一级标题起始列
        for (String s : sonTitle){
            //绘制单元格
            cell=row.createCell(i_1);
            //添加数据
            cell.setCellValue(s);
            //添加样式
            cell.setCellStyle(SheetStyle.getTextCellStyle(workbook));
            //设置要合并的单元格样式
            cell = row.createCell(i_1+1);
            cell.setCellStyle(SheetStyle.getTextCellStyle(workbook));
            //合并单元格
            region = new CellRangeAddress(1, 1,i_1, i_1+sonTitleTow.size()-1);
            sheet.addMergedRegion(region);
            //循环添加子类二级表头
            int i_2=i_1;  // 子类二级标题起始列
            for (String s1 : sonTitleTow.keySet()){
                cell = row1.createCell(i_2);
                cell.setCellValue(sonTitleTow.get(s1).toString());
                cell.setCellStyle(SheetStyle.getTextCellStyle(workbook));
                sheet.setColumnWidth(i_2,2500);
                i_2++;
            }
            i_1 = i_1+sonTitleTow.size();
        }
        //添加汇总标题
        int i_3 = i_1;
        for (String s1 : sonTitleTow.keySet()){
            cell = row.createCell(i_3);
            cell.setCellValue(sonTitleTow.get(s1).toString()+"汇总");
            cell.setCellStyle(SheetStyle.getTextCellStyle(workbook));
            //设置要合并的单元格样式
            cell = row1.createCell(i_3);
            cell.setCellStyle(SheetStyle.getTextCellStyle(workbook));
            //合并单元格
            region = new CellRangeAddress(1, 2,i_3, i_3++);
            sheet.addMergedRegion(region);
        }
        //循环添加数据
        for (int j = 0; j < dataList.size(); j++) {
            //创建单元格对象
            //设置数据第一行
            XSSFRow row2 = sheet.createRow(j+3);
            row2.setHeightInPoints(18);//设置行高
            //添加父类数据
            int dataCol = 0;
            for (String s : fatTitle.keySet()) {
                cell= row2.createCell(dataCol);
                cell.setCellValue(dataList.get(j).get(s).toString());
                cell.setCellStyle(SheetStyle.getTextCellStyle(workbook));
                dataCol++;
            }
            //添加子类数据
            List<Map> lis = (List<Map>) dataList.get(j).get("porList");
            //根据二级子标题创建汇总集合
            Map<String,Object> sumMap = sonTitleTow;
            for (Map map : lis) {
                for (String s : sonTitle) {
                    //查找和标题行对应的数据
                    if(map.get(s)!=null){
                        for (String str : sonTitleTow.keySet()) {
                            cell = row2.createCell(dataCol);
                            cell.setCellValue( Double.valueOf(map.get(str).toString()));
                            cell.setCellStyle(SheetStyle.getNumCellStyle(workbook));
                            try{
                                sumMap.put(str,Double.valueOf(sumMap.get(str).toString())+Double.valueOf(map.get(str).toString()));
                            }catch (NumberFormatException e){
                                sumMap.put(str,Double.valueOf(map.get(str).toString()));
                            }
                            dataCol++;
                        }
                        break;
                    }
                }
            }
            /**
             * 添加汇总数据
             */
            for (String str : sonTitleTow.keySet()) {
                cell = row2.createCell(dataCol);
                cell.setCellValue(Double.valueOf(sumMap.get(str).toString()));
                cell.setCellStyle(SheetStyle.getNumCellStyle(workbook));
                dataCol++;
            }
        }
        workbook.setSheetName(0, "测试导出");//设置sheet的Name
        //文档输出
        FileOutputStream out = new FileOutputStream("D:/excel/原生poi.xlsx");
        workbook.write(out);
        out.close();
    }

5、测试导出

 List<Demo1> demo1List = new ArrayList<>();
    //父类标题
    Map<String,Object> fatTitle = new HashMap<>();
    //子类一级标题
    List<String> sonTitle = new ArrayList<>();
    //子类二级标题
    Map<String,Object> sonTitleTow = new HashMap<>();
    //数据行
    List<Map<String,Object>> dataList = new ArrayList<>();
    @Before
    public  void testBefore() {
        fatTitle.put("name","姓名");
        fatTitle.put("age","年龄");
        sonTitleTow.put("money","金额");
        sonTitleTow.put("number","数量");
        for (int i = 0; i < 10; i++) {
            Demo1 demo = new Demo1();
            List<Demo2> lis = new ArrayList<>();
            for (int j = 0; j < 10; j++) {
                Demo2 demo2 = new Demo2();
                demo2.setName("项目" + j);
                demo2.setMoney((float) (i + j * 3.5));
                demo2.setNumber((i + 1) * 15);
                lis.add(demo2);
            }
            demo.setDemo2(lis);
            demo.setId(i);
            demo.setName("医院" + i);
            demo.setAge(100 - i);
            demo1List.add(demo);
        }
        demo1List.forEach(demo1 -> {
            Map<String, Object> map = new HashMap<>();
            demo1.getDemo2().forEach(demo2 -> {
                sonTitle.remove(demo2.getName());
                sonTitle.add(demo2.getName());
            });
        });
        demo1List.forEach(demo1 -> {
            Map<String, Object> map = new HashMap<>();
            map.put("name", demo1.getName());
            map.put("age", demo1.getAge());
            List<Map> porList = new ArrayList<>();
            for (String s : sonTitle) {
                Map<String, Object> porMap = new HashMap<>();
                porMap.put(s,s);
                //判断是否找到的标志
                boolean isFind = false;
                for (Demo2 demo2 : demo1.getDemo2()) {
                    if (demo2.getName().equals(s)) {
                        porMap.put("money", demo2.getMoney());
                        porMap.put("number", demo2.getNumber());
                        isFind = true;
                        break;
                    }
                }
                //找不到给其添加0进行补位
                if (!isFind) {
                    porMap.put("money", 0);
                    porMap.put("number", 0);
                }
                porList.add(porMap);
                map.put("porList", porList);
            }
            dataList.add(map);
        });
    }

    /**
     * 1、创建工作薄对象
     * 2、创建工作表（sheet）对象
     * 3、创建行
     * 4、创建单元格
     * 5、填充数据
     * 6、设置格式
     * 7、合并单元格
     * @throws IOException
     */

    @Test
    public void testExcel() throws IOException {
        ExcelExport.outExcel(fatTitle,sonTitle,sonTitleTow,dataList);
    }

6、导出效果

 

 7、前端点击下载时

public void dataExport(@RequestBody Map<String,Object> params, HttpServletResponse response){
        XSSFWorkbook workbook = service.dataExport(params);
        SXSSFWorkbook workBook = new SXSSFWorkbook(1000);//内存中最多存放一千条数据，防止内存溢出
        try {
            Date day=new Date();
            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String name = params.get("title").toString()+ df.format(day);
            String codedFileName = new String(name.getBytes("gbk"), "iso-8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + codedFileName + ".xlsx");
            // 响应类型,编码
            response.setContentType("application/octet-stream;charset=UTF-8");
            // 形成输出流
            OutputStream osOut = response.getOutputStream();
            // 将指定的字节写入此输出流
            workbook.write(osOut);
            // 刷新此输出流并强制将所有缓冲的输出字节被写出
            osOut.flush();
            // 关闭流
            osOut.close();
            /*
             * dispose of temporary files backing this workbook on disk 处理在磁盘上备份此工作簿的临时文件
             * SXSSF分配临时文件，您必须始终清除显式，通过调用dispose方法
             */
            workBook.dispose();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }