欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  IT编程

Poi导入导出百万Excel数据--java编程

程序员文章站 2024-01-20 16:26:28
前言 项目中做报表，导入导出Excel,是常见也是不可或缺的功能，本文讲解导入多 Excel的写法，以及导出百万的优化方案图解代码思路导出多Excel工具类package com.XXX.XXX.utils;import.XXX.ExportExcelBase;import lombok.extern.slf4j.Slf4j;import org.apache.poi.xssf.usermodel.*;import org.springframework.core.env...

前言

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

图解代码思路

Poi导入导出百万Excel数据--java编程

导出多Excel工具类

package com.XXX.XXX.utils;
import.XXX.ExportExcelBase;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.core.env.Environment;
import org.springframework.stereotype.Component;
import swiftsdk.SfOssClient;
import swiftsdk.SwiftConfiguration;
import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
/**
 * @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)

  1. 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.csdn.net/WindwirdBird/article/details/109273548

javaBean 转换map
https://blog.csdn.net/windwirdbird/article/details/109258678

本文地址：https://blog.csdn.net/WindwirdBird/article/details/109257876

相关标签： poi 1024程序员节

上一篇： Cause: org.apache.ibatis.type.TypeException: Could not resolve type alias ‘VARVHAR‘

下一篇： CDR X8任意两个节点之间怎么连接呢?

推荐阅读