基于Hutool的Excel处理工具类

maven

		<!-- excel解析包 -->
        <!--处理2003 excel-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.0</version>
        </dependency>
        <!--处理2007 excel-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.0</version>
        </dependency>
        
		<dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.6.5</version>
        </dependency>

工具类代码

package com.demo.util;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.List;
import java.util.Objects;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.io.IoUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.demo.annotation.ExcelColumn;
import com.google.common.base.Joiner;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.CellRangeAddressList;

/**
 * excel处理工具类
 *
 * @author matao
 * @version ExcelUtils.java, v 0.1 2022年10月27日 11:37 matao
 */
public class ExcelUtils<T> {

    private static final int ZERO = 0;
    private static final String COMMA = ",";

    /**
     * 导出excel模板，excel表格设置下拉列表
     *
     * @param inputStream          文件流
     * @param fileName             文件名称
     * @param response             HttpServletResponse
     * @param cellRangeAddressList poi表格处理
     * @param selectList           下拉列表
     * @throws IOException
     */
    public void downloadFile(InputStream inputStream, String fileName, HttpServletResponse response,
        CellRangeAddressList cellRangeAddressList, List<String> selectList) throws IOException {
        if (Objects.isNull(inputStream) || StrUtil.isEmpty(fileName)) {
            return;
        }

        // 通过文件流获取excel写入器
        ExcelReader reader = ExcelUtil.getReader(inputStream);

        // 获取Excel写出器
        ExcelWriter writer = reader.getWriter();
        if (!Objects.isNull(cellRangeAddressList) && CollUtil.isNotEmpty(selectList)) {
            writer.addSelect(cellRangeAddressList, Joiner.on(COMMA).join(selectList));
        }
        responseWrite(fileName, response, writer);
    }

    /**
     * 导出写入数据的excel
     *
     * @param inputStream 本地文件流
     * @param fileName    文件名称
     * @param response    HttpServletResponse
     * @param data        写入到excel的数据集
     * @param passRowNum  跳到制定行数
     * @throws IOException
     */
    public void downloadFile(InputStream inputStream, String fileName, HttpServletResponse response, List<T> data,
        Integer passRowNum) throws IOException {
        if (CollUtil.isEmpty(data)) {
            return;
        }

        // 通过文件流获取excel写入器
        ExcelReader reader = ExcelUtil.getReader(inputStream);

        // 获取Excel写出器
        ExcelWriter writer = reader.getWriter();

        // 设置单元格样式无边框
        CellStyle cellStyle = writer.getCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        cellStyle.setBorderBottom(BorderStyle.NONE);
        cellStyle.setBorderLeft(BorderStyle.NONE);
        cellStyle.setBorderRight(BorderStyle.NONE);
        cellStyle.setBorderTop(BorderStyle.NONE);

        // 通过反射增加标题别名，对应数据，方便写入
        Object o = data.get(ZERO);
        Class<?> clazz = o.getClass();
        for (Field field : clazz.getDeclaredFields()) {
            field.setAccessible(true);
            ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
            if (!Objects.isNull(excelColumn)) {
                writer.addHeaderAlias(excelColumn.fieldName(), excelColumn.fieldDesc());
            }
        }

        // 设置跳过制定行数
        if (!Objects.isNull(passRowNum)) {
            writer.passRows(passRowNum);
        }

        // 写入数据到excel
        writer.write(data, false);
        responseWrite(fileName, response, writer);
    }

    /**
     * 读取excel
     *
     * @param inputStream  文件流
     * @param bean         excel数据对应到实体
     * @param headRow      表头所在行
     * @param startReadRow 开始读取到行
     * @return
     */
    public List<T> uploadFile(InputStream inputStream, Class<T> bean, Integer headRow, Integer startReadRow) {
        // 通过文件流获取excel写入器
        ExcelReader reader = ExcelUtil.getReader(inputStream);

        // 通过反射增加标题别名，对应数据，方便读取
        for (Field field : bean.getDeclaredFields()) {
            field.setAccessible(true);
            ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
            if (!Objects.isNull(excelColumn)) {
                reader.addHeaderAlias(excelColumn.fieldDesc(), excelColumn.fieldName());
            }
        }

        return reader.read(headRow, startReadRow, bean);
    }

    /**
     * 返回文件到客户端
     *
     * @param fileName 文件名称
     * @param response HttpServletResponse
     * @param writer   Excel 写入器
     * @throws IOException
     */
    private void responseWrite(String fileName, HttpServletResponse response, ExcelWriter writer) throws IOException {
        // response为HttpServletResponse对象
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        // fileName是弹出下载对话框的文件名，如果是中文需要自行编码
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        response.setHeader("Pragma", URLEncoder.encode(fileName, "UTF-8"));
        ServletOutputStream out = response.getOutputStream();

        writer.flush(out, true);
        // 关闭writer，释放内存
        writer.close();
        // 此处记得关闭输出Servlet流
        IoUtil.close(out);
    }
}