EXCEL工具注解方式读取写入封装

package xzy.sample.excel.util; import cn.hutool.core.codec.Base64; import cn.hutool.core.util.PageUtil; import cn.hutool.poi.excel.ExcelReader; import cn.hutool.poi.excel.ExcelUtil; import cn.hutool.poi.excel.ExcelWriter; import cn.hutool.poi.excel.StyleSet; import org.apache.poi.ss.usermodel.*; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import xzy.sample.excel.domain.bo.ExcelHeaderInfoBo; import xzy.sample.excel.domain.bo.ExcelInfoBo; import xzy.sample.excel.domain.bo.PageResBo; import xzy.sample.excel.exception.ExcelReadException; import java.io.ByteArrayInputStream; import java.io.ByteArrayOutputStream; import java.io.InputStream; import java.util.*; import java.util.stream.Collectors; /** * @author ext.xuzhengyang5 * @date 2024/1/4 * @Description excel工具类 */ public class ComExcelUtil { private static final Logger logger = LoggerFactory.getLogger(ComExcelUtil.class); /** * 读取excel base64字符串，依赖传递class的别名配置 * * @param base64Str * @param tClass * @param <T> * @return * @throws Exception */ public static <T> List<T> readAllByClass(String base64Str, Class<T> tClass) throws Exception { byte[] decode = Base64.decode(base64Str); ByteArrayInputStream in = new ByteArrayInputStream(decode); return readAllByClass(in, tClass); } /** * 读取excel文件流，依赖传递class的别名配置 * * @param in * @param tClass * @param <T> * @return * @throws Exception */ public static <T> List<T> readAllByClass(InputStream in, Class<T> tClass) throws Exception { ExcelInfoBo excelInfoBo = ExcelAnnoUtil.getAliasInfoRead(tClass); return readAllByClass(excelInfoBo, in, tClass); } /** * 读取excel文件流 * * @param excelInfoBo * @param in * @param tClass * @param <T> * @return */ public static <T> List<T> readAllByClass(ExcelInfoBo excelInfoBo, InputStream in, Class<T> tClass) { try (ExcelReader reader = ExcelUtil.getReader(in, excelInfoBo.getSheetName());) { reader.setHeaderAlias(excelInfoBo.getFieldAlias()); checkHead(excelInfoBo.getHeaderRowIndex(), excelInfoBo.getForceFieldSet(), reader); return reader.read(excelInfoBo.getHeaderRowIndex(), 1, tClass); } } public static <T> PageResBo<T> readerPageByClass(ExcelReader reader, int startRow, int rowCount, Class<T> tClass) { ExcelInfoBo excelInfoBo = ExcelAnnoUtil.getAliasInfoRead(tClass); return readerPageByClass(excelInfoBo, tClass, startRow, rowCount, reader); } /** * 分页读取excel文件流 * 调用此方法记得关闭ExcelReader * * @param excelInfoBo * @param tClass * @param pageNum * @param pageSize * @param reader * @return * @param <T> */ public static <T> PageResBo<T> readerPageByClass(ExcelInfoBo excelInfoBo, Class<T> tClass, int pageNum, int pageSize, ExcelReader reader) { Map<String, String> fieldAlias = excelInfoBo.getFieldAlias(); int headerRowIndex = excelInfoBo.getHeaderRowIndex(); reader.setHeaderAlias(fieldAlias); // 校验表头 checkHead(headerRowIndex, excelInfoBo.getForceFieldSet(), reader); // 分页读取数据 int dataStart = headerRowIndex + 1; int start = PageUtil.getStart(pageNum - 1, pageSize); int end = PageUtil.getEnd(pageNum - 1, pageSize); int startRowIndex = start + dataStart; int endRowIndex = end + dataStart - 1; List<T> pageData = reader.read(headerRowIndex, startRowIndex, endRowIndex, tClass); // 获取Excel的总行数（不包括表头） int totalRows = reader.getRowCount() - headerRowIndex; // 计算总页数 int totalPages = (totalRows + pageSize - 1) / pageSize; // 封装结果 PageResBo<T> pageResBo = new PageResBo<>(); pageResBo.setPageNum(pageNum); pageResBo.setPageSize(pageSize); pageResBo.setTotalPages(totalPages); pageResBo.setTotalCount(totalRows); pageResBo.setData(pageData); return pageResBo; } private static void checkHead(int headerRowIndex, Set<String> forceFieldSet, ExcelReader reader) { List<Object> headList = reader.readRow(headerRowIndex); boolean bool = forceFieldSet.stream().allMatch(headList::contains); if (Boolean.FALSE.equals(bool)) { logger.warn("读取excel文件流 文件缺少必要的表头"); throw new ExcelReadException("文件缺少必要的表头"); } } /** * 根据模板流生成文件,注解形式 * * @param tClass * @param templateStream 模板文件流，注意此方法不会关闭templateStream * @param rows * @param <T> * @return */ public static <T> ByteArrayOutputStream writeByTemplate(InputStream templateStream, List<T> rows, Class<T> tClass) { ExcelInfoBo excelInfoBo = ExcelAnnoUtil.getAliasInfoTemplateWrite(tClass); return writeByTemplate(excelInfoBo, templateStream, rows); } /** * 根据模板流生成文件 * * @param templateStream 模板文件流，注意此方法不会关闭templateStream * @param rows * @param <T> * @return */ public static <T> ByteArrayOutputStream writeByTemplate(ExcelInfoBo excelInfoBo, InputStream templateStream, List<T> rows) { String sheetName = excelInfoBo.getSheetName(); ExcelReader reader = ExcelUtil.getReader(templateStream, sheetName); // 从ExcelReader获取Workbook对象 Workbook workbook = reader.getWorkbook(); // 创建ExcelWriter，基于获取到的Workbook对象 ExcelWriter writer = new ExcelWriter(workbook, sheetName); // 设置为写入模式 writer.setOnlyAlias(true); //设置默认样式 CellStyle cellStyle = writer.getCellStyle(); cellStyle.setAlignment(HorizontalAlignment.LEFT); writer.setCurrentRow(excelInfoBo.getHeaderRowIndex()); writer.setHeaderAlias(excelInfoBo.getFieldAlias()); //重新设置输出起始行 头部起始行+1为数据起始行 writer.setCurrentRow(excelInfoBo.getHeaderRowIndex() + 1); // 写入数据到Excel，数据将填充到模板的单元格中，保留原有样式 writer.write(rows); // 写出到文件或输出流 ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream(); writer.flush(byteArrayOutputStream); // 关闭ExcelWriter writer.close(); // 关闭ExcelReader reader.close(); return byteArrayOutputStream; } /** * 基于class注解信息写入OutputStream * * @param explainText * @param explainHeight * @param rows * @param tClass * @param <T> * @return */ public static <T> ByteArrayOutputStream writeAllByClass(String explainText, Integer explainHeight, List<T> rows, Class<T> tClass) { ExcelInfoBo aliasInfo = ExcelAnnoUtil.getAliasInfoWrite(tClass); aliasInfo.setExplainText(explainText); aliasInfo.setExplainHeight(explainHeight); return writeAllByClass(aliasInfo, rows); } /** * 基于excel信息写入OutputStream * * @param aliasInfo * @param rows * @param <T> * @return */ public static <T> ByteArrayOutputStream writeAllByClass(ExcelInfoBo aliasInfo, List<T> rows) { // 创建一个ExcelWriter对象，通过构造方法指定写出到的文件路径和Excel类型（是否为xlsx格式） ExcelWriter