JAVA 大数据Excel POI生成导出

JAVA 大数据Excel POI生成导出一、背景在工作中经常会将List导出Excel,但是有时数据量很大,需要一次性导出。为防止各个系统重复造轮子,本文通过注解方式来实现Excel的普通、分片生成。二、直接上代码1、导入依赖<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId>…

大家好,又见面了,我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全系列IDE稳定放心使用

一、背景

在工作中经常会将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);
        }
    }

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

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/184751.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号