Poi导入导出百万Excel java

前言

       项目中做报表，导入导出Excel,是常见也是不可或缺的功能，本文讲解导入多
  Excel的写法，以及导出百万的优化方案

图解代码思路

导出多Excel工具类

package com.XXX.XXX.utils;
/**
 * @Author  windwardbird
 * @packageName  windwardbird     exportMulitExcelUtil
 * @describeOfFunction: 数据大于50万分Excel
 */
@Slf4j
@Component
public class ExportMultiExcelUtils {
  
    private static Environment environment;

    @Resource
    private Environment env;

    @PostConstruct
    public void init() {
        environment = env;   // 注入bean (自己new 出来的)
    }

    private final SimpleDateFormat format = new SimpleDateFormat("MM-dd-HH");
    private final SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
    private final SimpleDateFormat df1 = new SimpleDateFormat("yyyy-MM-dd-HH");

    private static final String NEW_EXCEL_SUFFIX = ".xlsx";

    private static final String OLD_EXCEL_SUFFIX = ".xls";


    private static final Integer CURRENT_MAX_NUMBER = null==environment.getProperty("max.export.number") ?500000:env.getProperty("max.export.number"); //每个默认Excel50万


    public void ExportExcel(Map<String, Object> tempMap, Object exportExcelBase, String tableName, String model,String part) throws Exception {
        log.info("进入Excel工具类");
        List<ExportExcelBase> exportExcelBaseList = (List<ExportExcelBase>) exportExcelBase;

        List<List<Object>> excelDataResult = new ArrayList<>(); //Excel 的对应数

        List<Object> headExcelValue = new ArrayList<>();  //Excel表头

        List<Object> headExcelKey = new ArrayList<>();   //连接key

        GetExcelHeadInfo(tempMap, headExcelValue, headExcelKey);//获取Excel 表头以及连接key
        //处理数据
        ProcessingInputData(exportExcelBaseList, excelDataResult, headExcelKey);
        log.info("Excel 数据处理完毕");
        //TODO: 数据多余50万处理成多个Excel
        Integer excelTotal = excelDataResult.size(); //处理过Excel总条数
        if (excelTotal <= CURRENT_MAX_NUMBER) {
            List<List<Object>> excelDataResultTemp = new ArrayList<>();
            XSSFWorkbook wb = new XSSFWorkbook();//新版的Excel
            XSSFSheet sheet = wb.createSheet(tableName);
            excelDataResultTemp.add(headExcelValue);
            for (int j = 0; j < excelTotal; j++) {
                excelDataResultTemp.add(excelDataResult.get(j));
            }
            InsertDataToExcel(excelDataResultTemp, excelDataResultTemp.size(), sheet);
            log.info("查询数据小于50万");
            //导出Excel
            ExportExcelOfResponseStream(tableName, wb, model,part);
        } else {
            log.info("查询数据大于50万");
            int excelNumber = 0;  //分EXCEl导出 默认生成一个Excel
            int flagNum = excelTotal % CURRENT_MAX_NUMBER; // 取模看是否需要多分Excel
            if (flagNum == 0) {
                excelNumber = excelTotal / CURRENT_MAX_NUMBER;
            } else {
                excelNumber = excelTotal / CURRENT_MAX_NUMBER + 1;
            }
            for (int i = 1; i <= excelNumber; i++) {
                //构造当前Excel数据
                List<List<Object>> excelDataResultTemp = new ArrayList<>();
                XSSFWorkbook wb = new XSSFWorkbook();//新版的Excel
                XSSFSheet sheet = wb.createSheet(tableName);
                if (i == 1) {
                    //ToDo: 取50万整数据，单独加表头
                    excelDataResultTemp.add(headExcelValue);
                    for (int j = 0; j < CURRENT_MAX_NUMBER; j++) {
                        excelDataResultTemp.add(excelDataResult.get(j));
                    }
                } else {
                    excelDataResultTemp.add(headExcelValue); //添加每个Excel的表头
                    for (int j = CURRENT_MAX_NUMBER * (i - 1); j < CURRENT_MAX_NUMBER * i; j++) {

                        if (j > excelTotal) {
                            break;
                        } else if (j < excelTotal) {
                            excelDataResultTemp.add(excelDataResult.get(j));
                        }
                    }
                }
                InsertDataToExcel(excelDataResultTemp, excelDataResultTemp.size(), sheet);
                //导出Excel
                ExportExcelOfResponseStream(tableName, wb, model,part);
            }
        }
        log.info("数据转成excel处理完毕，进入生成压缩包阶段");
        //获取excel,打成zip包，根据当前的时间，放入相应的服务器路径
        String zipPath = environment.getProperty("base.path") + File.separator + model + File.separator + part + File.separator + df.format(new Date()) + File.separator;
        CompressUtil.generateFile(zipPath,"zip",tableName);

        File file = new File(zipPath);
        File[] files = file.listFiles();
        // System.gc();
        //删除excel
        for (int i = 0; i < files.length; i++) {
            if (!files[i].getName().endsWith(".zip")) {
                log.info("文件名称：{}" , files[i].getName());
                files[i].delete();
            }
        }

 
    private void ExportExcelOfResponseStream(String tableName, XSSFWorkbook wb, String model,String part) throws IOException {

        // 设置文件名(根据当前时间)
        String fileName = "" + tableName + "-" + format.format(new Date()) + "-" + SnowflakeIdUtil.nextId() + NEW_EXCEL_SUFFIX;

        //获取文件夹，如果没有则创建
        String format = df.format(new Date());
        String path = environment.getProperty("base.path") + File.separator + model + File.separator + part + File.separator  + format;
        File f = new File(path);
        if (!f.exists()) {
            f.mkdirs();
        }

        //写入excel
        FileOutputStream fileOutputStream = new FileOutputStream(path + File.separator + fileName);
        wb.write(fileOutputStream);
        fileOutputStream.flush();
        fileOutputStream.close();
        wb.close();

    }


    private void InsertDataToExcel(List<List<Object>> excelDataResult, int excelTotal, XSSFSheet
            sheet) {
        for (int i = 0; i < excelTotal; i++) {
            XSSFRow row = sheet.createRow(i);// 创建行数
            List<Object> rowData = excelDataResult.get(i);
            for (int j = 0; j < rowData.size(); j++) {
                Object cellValue = null == rowData.get(j) ? "" : rowData.get(j);
                row.createCell(j).setCellValue(String.valueOf(cellValue));
                //row.setRowStyle(cellStyle);
            }
        }
    }

    // 格式不加走默认（根据需要自己添加）
    private XSSFSheet GETExcelTypeAndName(String tableName) {
        XSSFWorkbook wb = new XSSFWorkbook();//新版的Excel
        XSSFCellStyle cellStyle = wb.createCellStyle(); //创建单元格式
        //创建单元格格式
        XSSFFont ztFont = (XSSFFont) wb.createFont();
        ztFont.setFontHeightInPoints((short) 10);    // 将字体大小设置为10px
        ztFont.setFontName("Arial");                // 将“华文行楷”字体应用到当前单元格上
       ztFont.setBoldweight( XSSFFont.BOLDWEIGHT_BOLD);//字体加粗
        cellStyle.setFont(ztFont);                    // 设置字体格式
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //居中显示
       cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        XSSFSheet sheet = wb.createSheet(tableName);
        return sheet;
    }


    private void ProcessingInputData(List<ExportExcelBase> exportExcelBase, List<List<Object>> excelDataResult, List<Object> headExcelKey) {
        exportExcelBase.forEach(frontPartDetail -> {
                    try {
                        List<Object> headExcelData = new ArrayList<>(); //
                        Map<String, Object> frontPartMap = BeanToMap.javaBeanToMap(frontPartDetail); //当行数超过三千，就不要BeantoMap ,直接在内存根据表头构造好List<Map<String,Object>> 传入 ，效率提升50倍加
                        for (int i = 0; i < headExcelKey.size(); i++) {
                            Object tempData = frontPartMap.get(headExcelKey.get(i));
                            headExcelData.add(tempData);
                        }
                        excelDataResult.add(headExcelData);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
        );
    }


    private void GetExcelHeadInfo(Map<String, Object> tempMap, List<Object> headExcelValue, List<Object> headExcelKey) {
        for (Map.Entry<String, Object> entry : tempMap.entrySet()) {
            String key = entry.getKey(); //取数据
            Object value = entry.getValue();//表头
            headExcelValue.add(value);
            headExcelKey.add(key);
        }
    }
}

优化

导出3千可以直接用上面的工具类，倘若要导出上万或者百万的Excel?
1.上面的逻辑代码仍可以用（前提是吃硬件，时间慢）
2. 需要做的仅仅是换个接收的工具类
a .阿里的EasyExcel
b.阿帕奇的 SXSSF 替换 （XSSF）/(HSSF)

3. pom文件的引用

阿里

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>1.1.2-beta5</version>
</dependency>

阿帕奇

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

生成zip工具可以自己写，也可以参考
https://blog.youkuaiyun.com/WindwirdBird/article/details/109273548

javaBean 转换map
https://blog.youkuaiyun.com/windwirdbird/article/details/109258678

单个SXSSFWorkbook Excel
https://blog.youkuaiyun.com/WindwirdBird/article/details/111177847

大数据量分多Excel 导出 直接用 SXSSFWorkbook
https://blog.youkuaiyun.com/WindwirdBird/article/details/111215256