java后端Excel导出工具

借助hutool工具包快速导出数据到Excel文件中。官方文档

1、依赖

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

2、保存sheet相关信息的实体类

import lombok.Data;

import java.io.Serializable;
import java.util.Collection;
import java.util.LinkedHashMap;
import java.util.Map;

/**
 * SheetDTO
 *
 * @author ygr
 * @date 2021/12/1 12:00
 */
@Data
public class SheetDTO implements Serializable {
    private static final long serialVersionUID = 1L;
    /**
     * sheet页名称
     */
    private String sheetName;
    /**
     * 字段和别名
     */
    private LinkedHashMap<String, String> columns;
    /**
     * 列宽
     */
    private Map<String, Integer> widths;
    /**
     * 数据集
     */
    private Collection<?> dataList;
}

3、工具类

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.map.MapUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.example.datacollector.dto.SheetDTO;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * excel导出工具
 *
 * @author ygr
 * @date 2021/12/1 12:01
 */
public class ExcelExportUtil {

    /**
     * 导出数据到流
     * <p>
     * 适合数据量较大的场景
     *
     * @param sheetList    sheetList
     * @param outputStream outputStream
     */
    public static void exportExcel(List<SheetDTO> sheetList, OutputStream outputStream) {
        ExcelWriter bigWriter = ExcelUtil.getBigWriter();
        // 重命名第一个Sheet的名称，不然会默认多出一个Sheet1的页
        bigWriter.renameSheet(sheetList.get(0).getSheetName());
        doExport(bigWriter, sheetList, outputStream);
    }

    /**
     * 将sheet追加到文件中，如果文件不存在，则创建新的文件
     *
     * @param sheetList sheetList
     * @param destFile  destFile
     */
    public static void appendSheet(List<SheetDTO> sheetList, File destFile) {
        assert CollectionUtil.isNotEmpty(sheetList);
        ExcelWriter excelWriter = ExcelUtil.getWriter(destFile);
        excelWriter.renameSheet(sheetList.get(0).getSheetName());
        doExport(excelWriter, sheetList, null);
    }

    private static void doExport(ExcelWriter excelWriter, List<SheetDTO> sheetList, OutputStream outputStream) {
        for (SheetDTO sheet : sheetList) {
            // 指定要写出的 Sheet 页
            excelWriter.setSheet(sheet.getSheetName());
            Map<String, Integer> widths = sheet.getWidths();
            LinkedHashMap<String, String> columns = sheet.getColumns();
            if (MapUtil.isNotEmpty(widths)) {
                int index = 0;
                for (Map.Entry<String, String> entry : columns.entrySet()) {
                    Integer wid = widths.get(entry.getKey());
                    excelWriter.setColumnWidth(index++, wid != null ? wid : 25);
                }
            } else {
                for (int i = 0; i < columns.size(); i++) {
                    excelWriter.setColumnWidth(i, 25);
                }
            }

            // 设置字段和别名
            excelWriter.setHeaderAlias(sheet.getColumns());
            // 设置只导出有别名的字段
            excelWriter.setOnlyAlias(true);
            // 设置默认行高
            excelWriter.setDefaultRowHeight(18);
            // 设置冻结行
            excelWriter.setFreezePane(1);
            // 一次性写出内容，使用默认样式，强制输出标题
            excelWriter.write(sheet.getDataList(), true);
        }
        if (outputStream != null) {
            excelWriter.flush(outputStream, true);
        }
        excelWriter.close();
    }

    /**
     * 导出Excel文件到浏览器
     *
     * @param sheetList sheetList
     * @param fileName  fileName
     * @param response  response
     * @throws IOException IOException
     */
    public static void exportExcel(List<SheetDTO> sheetList, String fileName, HttpServletResponse response) throws IOException {
        assert CollectionUtil.isNotEmpty(sheetList);
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition",
                "attachment;filename=" +
                        URLEncoder.encode(fileName + DateUtil.today() + ".xlsx", "UTF-8"));
        exportExcel(sheetList, response.getOutputStream());
    }

}