基于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);}// 写入数据到excelwriter.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);}
}

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！