hutools 导出excel

hutools 导出excel 工具类HuExcelUtils

• 添加依赖

<dependency>
	<groupId>cn.hutool</groupId>
	<artifactId>hutool-all</artifactId>
	<version>5.8.16</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>5.2.2</version>
</dependency>

• 主要代码

@Slf4j
public class HuExcelUtils {

    /**
     * 导出多个 Sheet 页
     *
     * @param sheetList 页数据
     * @param fileName  文件名
     */
    public static void exportSheetExcel(HttpServletResponse response, String fileName, List<SheetDTO> sheetList) {
        MyExcelWriter bigWriter = null;
        ServletOutputStream out = null;
        try {
            bigWriter = MyExcelWriter.getBigWriter();
            // 重命名第一个Sheet的名称，不然会默认多出一个Sheet1的页
            bigWriter.renameSheet(0, sheetList.get(0).getSheetName());
            for (SheetDTO sheet : sheetList) {
                // 指定要写出的 Sheet 页
                bigWriter.setSheet(sheet.getSheetName());
                // 设置字段和别名
                bigWriter.setHeaderAlias(sheet.getFieldAndAlias());

                Sheet bigWriterSheet = bigWriter.getSheet();

                // false 输出key,value到excel中， true 只输出value值到excel中
                bigWriter.setOnlyAlias(true);

                // 设置冻结行
                bigWriter.setFreezePane(1);
                // 一次性写出内容，使用默认样式，强制输出标题
                bigWriter.write(sheet.getCollection(), true);
                //设置所有列为自动宽度，不考虑合并单元格
                bigWriter.autoSizeColumnAll();
            }

            //response为HttpServletResponse对象
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" +
                            URLEncoder.encode(fileName + DateUtil.today() + ".xlsx", StandardCharsets.UTF_8));
            out = response.getOutputStream();
            bigWriter.flush(out, true);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (null != bigWriter) {
                // 关闭writer，释放内存
                bigWriter.close();
            }

            if (out != null) {
                try {
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        //此处记得关闭输出Servlet流
        IoUtil.close(out);
    }

    /**
     *
     * @param response res
     * @param fileName 文件名
     * @param sheetDTO 导出的文件
     */
    public static void exportExcel(HttpServletResponse response,
                                   String fileName,
                                   @NotNull SheetDTO sheetDTO) {
        long sEpochSecond = Instant.now().getEpochSecond();
        //通过工具类创建writer
        try (MyExcelWriter bigWriter = MyExcelWriter.getBigWriter()) {
            //数据量特别大时，使用BigExcelWriter对象，可以避免内存溢出
            //设置sheet的名称
            bigWriter.renameSheet(sheetDTO.getSheetName());

            // 设置字段和别名
            bigWriter.setHeaderAlias(sheetDTO.getFieldAndAlias());

            // false 输出key,value到excel中， true 只输出value值到excel中
            bigWriter.setOnlyAlias(true);
            bigWriter.write(sheetDTO.getCollection(), true);

            //设置所有列为自动宽度，不考虑合并单元格
            bigWriter.autoSizeColumnAll();
            response.reset();
            response.setContentType("application/vnd.ms-excel;charset=utf-8");

            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, StandardCharsets.UTF_8) + ".xlsx");
            bigWriter.flush(response.getOutputStream());
            long eEpochSecond = Instant.now().getEpochSecond();
            log.info("写入记录耗时: " + (eEpochSecond - sEpochSecond) / 1000 + "秒");
        } catch (Exception e) {
            //如果导出异常，则生成一个空的文件
            e.printStackTrace();
        }
        // 关闭writer，释放内存

    }
}

//参考 https://www.ufcn.cn/it/892263.html 解决自动列宽问题
public class MyExcelWriter extends BigExcelWriter {

    public static MyExcelWriter getBigWriter() {
        try {
            return new MyExcelWriter();
        } catch (NoClassDefFoundError var1) {
            throw new DependencyException((Throwable) ObjectUtil.defaultIfNull(var1.getCause(), var1), "You need to add dependency of 'poi-ooxml' to your project, and version >= 4.1.2", new Object[0]);
        }
    }
    @Override
    public BigExcelWriter autoSizeColumnAll() {
        final SXSSFSheet sheet = (SXSSFSheet)this.sheet;
        sheet.trackAllColumnsForAutoSizing();
        super.autoSizeColumnAll();
        for (int i = 0; i <sheet.getRow(sheet.getLastRowNum()).getPhysicalNumberOfCells(); i++) {
            // 解决自动设置列宽中文失效的问题
            sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10);
        }
        sheet.untrackAllColumnsForAutoSizing();
        return this;
    }
}

• dto

@Data
@NoArgsConstructor
@AllArgsConstructor
public class SheetDTO implements Serializable {

    /** sheet页名称 */
    private String sheetName;

    /**
     * 字段和别名，如果使用这个，properties 和 titles可以不用处理
     * Map<字段, 别名>  如：Map<"name", "姓名">
     */
    private Map<String, String> fieldAndAlias;

//    /**
//     * 列宽<br/>
//     * 设置列宽时必须每个字段都设置才生效（columnWidth.size = fieldAndAlias.size）
//     */
//    private Map<Integer, Integer> columnWidth;


    private Integer rowHeight;

    /** 数据集 */
    private Collection<?> collection;

}

• entity

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Person implements Serializable {
    private String name;
    private Integer age;
}

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Book implements Serializable {
    private String name;
    private String author;
}

• controller

@RestController
@RequestMapping("excel")
public class ExcelControlller {

    @RequestMapping("export")
    public void exportExcel(HttpServletResponse response){
        String fileName = "测试导出excel";
        Map<String, String> headers = new HashMap<>();
        headers.put("name", "姓名");
        headers.put("age", "年龄");

        Map<Integer, Integer> columns = new HashMap<>();
        columns.put(0, 50);
        columns.put(1, 30);

        List<Person> persons = new ArrayList<>();
        Person person1 = new Person("张三", 18);
        Person person2 = new Person("李四", 20);
        persons.add(person1);
        persons.add(person2);

        SheetDTO sheetDTO = new SheetDTO(fileName, headers, 20, persons);

        HuExcelUtils.exportExcel(response, fileName, sheetDTO);
    }


    @RequestMapping("exportSheet")
    public void exportSheetExcel(HttpServletResponse response){
        String fileName = "测试导出excelSheet";
        String sheetName = "person";
        Map<String, String> headers = new HashMap<>();
        headers.put("name", "姓名");
        headers.put("age", "年龄");

//        Map<Integer, Integer> columns = new HashMap<>();
//        columns.put(0, 50 * 256);
//        columns.put(1, 30 * 256);

        List<Person> persons = new ArrayList<>();
        Person person1 = new Person("张三==============", 18);
        Person person2 = new Person("李四", 20);
        persons.add(person1);
        persons.add(person2);

        String bookSheetName = "book";
        Map<String, String> bookHeader = new HashMap<>();
        bookHeader.put("name", "书名");
        bookHeader.put("author", "作者");

//        Map<Integer, Integer> bookColumns = new HashMap<>();
//        columns.put(0, 50 * 256);
//        columns.put(1, 20 * 256);

        List<Book> bookList = new ArrayList<>();
        Book book1 = new Book("西游记=================", "吴承恩");
        Book book2 = new Book("红楼梦", "曹雪芹");
        bookList.add(book1);
        bookList.add(book2);

        SheetDTO sheetDTO = new SheetDTO(sheetName, headers, 20, persons);
        SheetDTO bookSheetDTO = new SheetDTO(bookSheetName, bookHeader, 18, bookList);
        List<SheetDTO> data = new ArrayList<>();
        data.add(sheetDTO);
        data.add(bookSheetDTO);

        HuExcelUtils.exportSheetExcel(response, fileName, data);
    }
}