【Hutool】：使用hutool实现复杂excel导出功能

【Hutool】：使用hutool实现复杂excel导出功能

 

 转自：https://blog.csdn.net/weixin_45511500/article/details/118884656

引入依赖

<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.7.2</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

<!-- lombok -->
 <dependency>
     <groupId>org.projectlombok</groupId>
     <artifactId>lombok</artifactId>
     <version>1.18.20</version>
     <scope>provided</scope>
 </dependency>

　　

编写pojo类

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student {

    /**
     * 学号
     */
    private String sno;

    /**
     * 姓名
     */
    private String name;

    /**
     * 年龄
     */
    private Integer age;

    /**
     * 性别
     */
    private String gender;

    /**
     * 籍贯
     */
    private String nativePlace;

    /**
     * 入学时间
     */
    @JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
    private Date enrollmentTime;

    private BigDecimal money;

}

　　

编写实现类

	@PostMapping("user_export_excel")
    @ResponseBody
    public void exportExcel(HttpServletResponse response) throws UnsupportedEncodingException, ParseException {
         // 设置响应类型
        response.setContentType("application/vnd.ms-excel");
        // 设置字符编码
        response.setCharacterEncoding("utf-8");
        // 设置响应头信息
        response.setHeader("Content-disposition",
                "attachment;filename*=utf-8''" + URLEncoder.encode("学生花名册", "UTF-8") + ".xlsx");


        List<Student> studentList = new ArrayList<Student>() {
            {
                SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
                add(new Student("1001", "张三", 23, "男", "陕西西安", dateFormat.parse("2020-09-01"), BigDecimal.valueOf(22.33)));
                add(new Student("1002", "李四", 22, "女", "陕西渭南", dateFormat.parse("2020-09-01"),BigDecimal.valueOf(11.99)));
            }
        };

        // 写入文件
        ExcelWriter writer = ExcelUtil.getWriter();
        // 设置合并单元格
//        writer.merge(11, "BOM报价单", false);
//        writer.merge(3,"您上传的BOM", false);
//        writer.merge(1, 1 ,4,9,"商城报价单", false);
        // 设置标题一样式
        CellStyle cellStyle1 = writer.createCellStyle();
        cellStyle1.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle1.setAlignment(HorizontalAlignment.CENTER);
        cellStyle1.setBorderLeft(writer.getCellStyle().getBorderLeft());
        cellStyle1.setBorderRight(writer.getCellStyle().getBorderRight());
        cellStyle1.setBorderBottom(writer.getCellStyle().getBorderBottom());
        Font font = writer.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 12);
        font.setFontName("微软雅黑");
        cellStyle1.setFont(font);

        // 设置“总价” 单元格样式
        CellStyle cellStyle = writer.createCellStyle();
        cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GOLD.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //设置垂直居中和水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        //设置边框
        cellStyle.setBorderRight(writer.getCellStyle().getBorderRight());
        cellStyle.setBorderBottom(writer.getCellStyle().getBorderBottom());


        // 标题一
        writer.merge(0,0,0,11, "BOM报价单", cellStyle1);
        // 标题二
        writer.merge(1,1,0,3,"您上传的BOM", cellStyle1);
        writer.merge(1, 1 ,4,9,"商城报价单", cellStyle1);
        writer.merge(1,1,10,11,"总价（含税）: "+ " 99.99",cellStyle);
        // 跳过前面两行（为了设置标题样式）原始的设置标题方法，不可以，例：merge(3, "标题名", false);
        writer.passRows(2);

        // 设置高度（行row）
        writer.setRowHeight(0, 50);
        writer.setRowHeight(1, 30);


        // 设置导出信息的表头
        writer.addHeaderAlias("sno", "学号");
        writer.addHeaderAlias("name", "姓名");
        writer.addHeaderAlias("age", "年龄");
        writer.addHeaderAlias("gender", "性别");
        writer.addHeaderAlias("nativePlace", "籍贯");
        writer.addHeaderAlias("enrollmentTime", "入学时间");
        writer.addHeaderAlias("money", "金钱");

        // 设置列宽（Colum）
        writer.setColumnWidth(5, 20);
        writer.setColumnWidth(10, 10);
        writer.setColumnWidth(11, 10);

        // 设置导出表头样式，但不包括合并单元格
        CellStyle headCellStyle = writer.getHeadCellStyle();
        headCellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIME.getIndex());
        headCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 写入
        writer.write(studentList, true);

        // 主要针对自定义表头设置 行高（必须要写入完成后才可以设置）
        for (int i = 2; i < studentList.size(); i++) {
            writer.setRowHeight(i,20);
        }

        try {
            writer.flush(response.getOutputStream(), true);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            writer.close();
        }
    }

　　

效果图

 

 

 

参考资料

参考文章一
参考文章二
hutool官方文档