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();

        }

    }

©著作权归作者所有,转载或内容合作请联系作者
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。

推荐阅读更多精彩内容