JAVA 大数据Excel POI生成导出

一、背景

在工作中经常会将List导出Excel，但是有时数据量很大，需要一次性导出。为防止各个系统重复造轮子，本文通过注解方式来实现Excel的普通、分片生成。

二、直接上代码

1、导入依赖

<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency>

2、注解类

import java.lang.annotation.ElementType; import java.lang.annotation.Retention; import java.lang.annotation.RetentionPolicy; import java.lang.annotation.Target; /** * @Description 注解类 * @Date: 下午5:37 2022/4/12 */ @Target({ElementType.FIELD, ElementType.TYPE}) @Retention(RetentionPolicy.RUNTIME) public @interface XlsField { String xlsHeaderName() default ""; }

3、实体类

@Data public class Test { @XlsField(xlsHeaderName = "姓名") String name; @XlsField(xlsHeaderName = "年龄") String age; @XlsField(xlsHeaderName = "性别") String sex; public Test() { } public Test(String name, String age, String sex) { this.name = name; this.age = age; this.sex = sex; } }

4、工具类

import com.longfor.c2.expense.configration.XlsField; import lombok.Data; import org.apache.commons.lang3.ArrayUtils; import org.apache.poi.util.IOUtils; import org.apache.poi.xssf.streaming.SXSSFCell; import org.apache.poi.xssf.streaming.SXSSFRow; import org.apache.poi.xssf.streaming.SXSSFSheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import java.io.*; import java.lang.reflect.Field; import java.util.*; /** * 通过SXSSFWorkbook实现一个大数据excel生成工具类 * 版本要求excel2007之后版本 * 扩展名为.xlsx * * * @date 2022-04-12 */ public class ExcelUtil3 { /** * 用来做分片上传，以文件名称为key，已经生成过的workBook为value **/ private static Map<String, LocalWorkbook> FILE_BOOK_MAP = new HashMap<>(64); /** * 单个Sheet页最大行数 **/ private static final int MAX_ROW_NUM = 1048574; /** * 根据自定义注解获取excel表头 **/ private static <T> List<String> genHeader(Class<T> modelClazz) { Field[] fields = modelClazz.getDeclaredFields(); if (ArrayUtils.isEmpty(fields)) { return new ArrayList(0); } else { List<String> headers = new ArrayList(fields.length); Field[] arr$ = fields; int len$ = fields.length; for (int i$ = 0; i$ < len$; ++i$) { Field field = arr$[i$]; boolean isPresent = field.isAnnotationPresent(XlsField.class); if (isPresent) { String headerInfo = field.getAnnotation(XlsField.class).xlsHeaderName(); headers.add(headerInfo); } } return headers; } } /** * 创建一个excel文件（非分片） * * @param models 数据 * @param fileName 文件名称 * @return 文件 */ public static <T> File createExcel(List<T> models, String fileName) throws IllegalAccessException { SXSSFWorkbook workbook = createWorkBook(models, fileName); File file = new File(fileName); OutputStream out = null; try { if (!file.exists()) { file.createNewFile(); } out = new FileOutputStream(file); workbook.write(out); } catch (Exception e) { e.printStackTrace(); throw new RuntimeException(e); } finally { IOUtils.closeQuietly(out); } return file; } public static <T> File multipartCreateExcel(List<T> models, String fileName, boolean isFinish) throws IllegalAccessException { return multipartCreateExcel(models, fileName, MAX_ROW_NUM, isFinish); } /** * 分片生成excel * * @param models 数据 * @param fileName 文件名称 * @param sheetNum 每个Sheet页最大行数 * @param isFinish 是否生成完成(最后一片) * @return 流，可以直接上传S3 */ public static <T> File multipartCreateExcel(List<T> models, String fileName, int sheetNum, boolean isFinish) throws IllegalAccessException { if (sheetNum > MAX_ROW_NUM) { throw new IllegalAccessException("sheet rows num More than " + MAX_ROW_NUM + " rows "); } SXSSFWorkbook workbook = null; try { workbook = multipartCreateWorkBook(models, fileName, sheetNum); } catch (IllegalAccessException e) { FILE_BOOK_MAP.remove(fileName); throw e; } OutputStream out = null; File file = new File(fileName); if (isFinish) { try { out = new FileOutputStream(file); //临时缓冲区 //创建临时文件 workbook.write(out); } catch (Exception e) { e.printStackTrace(); } finally { FILE_BOOK_MAP.remove(fileName); } } return file; } /** * 分片写入SXSSFWorkbook * * @param models 数据 * @param fileName 文件名称 * @param sheetRowNum 一个sheet页多少行 * @return SXSSFWorkbook excel文件 */ public static <T> SXSSFWorkbook multipartCreateWorkBook(List<T> models, String fileName, int sheetRowNum) throws IllegalAccessException { List<String> header = genHeader(models.get(0).getClass()); Field[] fields = models.get(0).getClass().getDeclaredFields(); SXSSFWorkbook workbook; SXSSFSheet sheet; SXSSFRow row; int rowIndex = 0; if (!FILE_BOOK_MAP.containsKey(fileName)) { workbook = new SXSSFWorkbook(1000); sheet = workbook.createSheet(); row = sheet.createRow(0); for (int i = 0; i < header.size(); i++) { SXSSFCell cell = row.createCell(i); cell.setCellValue(header.get(i)); } FILE_BOOK_MAP.put(fileName, new LocalWorkbook(workbook, rowIndex)); } else { workbook = FILE_BOOK_MAP.get(fileName).getSxssfWorkbook(); sheet = workbook.getSheetAt(0); rowIndex = FILE_BOOK_MAP.get(fileName).getRowIndex(); } Iterator<T> it = models.iterator(); while (it.hasNext()) { if (rowIndex == sheetRowNum) { rowIndex = 0; sheet = workbook.createSheet(); row = sheet.createRow(0); for (int i = 0; i < header.size(); i++) { SXSSFCell cell = row.createCell(i); cell.setCellValue(header.get(i)); } FILE_BOOK_MAP.get(fileName).setRowIndex(rowIndex); } rowIndex++; row = sheet.createRow(rowIndex); T t = (T) it.next(); int cellIndex = 0; for (Field f : fields) { SXSSFCell cell = row.createCell(cellIndex); f.setAccessible(true); boolean isPresent = f.isAnnotationPresent(XlsField.class); if (!isPresent) { continue; } String value = Objects.toString(f.get(t)); cell.setCellValue(value); cellIndex++; } } FILE_BOOK_MAP.get(fileName).setRowIndex(rowIndex); return workbook; } private static <T> SXSSFWorkbook createWorkBook(List<T> models, String fileName) throws IllegalAccessException { List<String> header = genHeader(models.get(0).getClass()); Field[] fields = models.get(0).getClass().getDeclaredFields(); SXSSFWorkbook workbook = new SXSSFWorkbook(1000); SXSSFSheet sheet = workbook.createSheet(); SXSSFRow row = sheet.createRow(0); for (int i = 0; i < header.size(); i++) { SXSSFCell cell = row.createCell(i); cell.setCellValue(header.get(i)); } Iterator<T> it = models.iterator(); int index = 0; while (it.hasNext()) { index++; row = sheet.createRow(index); T t = (T) it.next(); int cellIndex = 0; for (Field f : fields) { SXSSFCell cell = row.createCell(cellIndex); f.setAccessible(true); boolean isPresent = f.isAnnotationPresent(XlsField.class); if (!isPresent) { continue; } String value = Objects.toString(f.get(t)); cell.setCellValue(value); cellIndex++; } } return workbook; } /** * 分片文件上传文件类 */ static class LocalWorkbook { private LocalWorkbook(SXSSFWorkbook sxssfWorkbook, int rowIndex) { this.sxssfWorkbook = sxssfWorkbook; this.rowIndex = rowIndex; this.totalRowNum = 0; } /** * 未完成的workBook **/ private SXSSFWorkbook sxssfWorkbook; /** * 当前sheet页row指针 **/ private int rowIndex; /** * 文件整体的行数 **/ private int totalRowNum; public SXSSFWorkbook getSxssfWorkbook() { return sxssfWorkbook; } public void setSxssfWorkbook(SXSSFWorkbook sxssfWorkbook) { this.sxssfWorkbook = sxssfWorkbook; } public int getRowIndex() { return rowIndex; } public void setRowIndex(int rowIndex) { this.rowIndex = rowIndex; } public int getTotalRowNum() { return totalRowNum; } public void setTotalRowNum(int totalRowNum) { this.totalRowNum = totalRowNum; } } }

5、测试

public static void main(String[] args) throws IllegalAccessException { String fileName = “测试导出.xlsx”; List<Test> list = new ArrayList<>(1234345); for (int i = 0; i < 1234345; i++) { list.add(new Test(String.valueOf(i), String.valueOf(i), String.valueOf(i))); } // 按照200000分片 List<List<Test>> ss = Lists.partition(list, 200000); File file = null; for (int i = 0; i < ss.size(); i++) { file = ExcelUtil3.multipartCreateExcel(ss.get(i), fileName, 100000, i == ss.size() – 1); } }

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/184751.html原文链接：https://javaforall.cn