侧边栏壁纸
博主头像
MDZZW博主等级

曾经也是帅哥，如今只是肉多

  • 累计撰写 28 篇文章
  • 累计创建 26 个标签
  • 累计收到 7 条评论
标签搜索
JAVA 服务器

JAVA百万数据导出成Excel

MDZZW
MDZZW
2021-12-31 / 0 评论 / 0 点赞 / 717 阅读 / 1,728 字
温馨提示：
本文最后更新于 2022-10-26，若内容或图片失效，请留言反馈。部分素材来自网络，若不小心影响到您的利益，请联系我们删除。

原理

  1. 数据分片放入内存 （分页，游标逐行便利） 解决OOM FullGC导致的CPU爆表
  2. SAX 读写Excel 解决OOM FullGC导致的CPU爆表
  3. 异步邮件发送或者FTP 文件服务器获取推送 解决HTTP 超时

源码

POM

<dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.17</version>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.17</version>
    </dependency>
注意点： 日期类型和Object 转化会发生时分秒丢失的问题

SXSSFWorkbook 官方专门优化过的一个类 SAX读写 有兴趣的自己研究
如果想要速度再快点可以引入 Fork/join框架

package com.sinosoft.reins.util;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.util.Date;
import java.util.List;

public class PoiSXSSFWorkbookExcel {
    private static final Logger logger = LoggerFactory.getLogger(PoiSXSSFWorkbookExcel.class);

    /**
     * 默认单个工作簿导出方法 超过最大行数 1048576 新增工作簿  =  sheetTitle_1 以此类推
     * @param sheetTitle sheet名字
     * @param title 第一行表格名
     * @param result 数据列表
     */
    public static SXSSFWorkbook getSxssfwbManySameSheets(String sheetTitle, String[] title, List<List> result)  throws Exception{
        SXSSFWorkbook wb = new SXSSFWorkbook();
        if(title.length == 0 || result.size()==0){
            throw new Exception("导出数据不能为空");
        }
        int sheetNum = 0;// 记录额外创建的sheet数量
        Sheet sheet = wb.createSheet(sheetTitle);
        // wb.setSheetName(sheetNum, sheetTitle+sheetNum);
        int rownum = 0;
        Row row = sheet.createRow(rownum);

        // 设置并获取到需要的样式
        XSSFCellStyle xssfCellStyleHeader = getAndSetXSSFCellStyleHeader(wb);

        Cell cell;
        // 创建标题,此时row=0,即第一行
        for (int j = 0; j < title.length; j++) {
            cell = row.createCell(j);
            cell.setCellValue(title[j]);
            cell.setCellStyle(xssfCellStyleHeader);
        }

        // 遍历集合数据，创建excel内容,产生数据行
        if (result != null) {
            List m = null;
            for (int i = 0; i < result.size(); i++) {
                if ((i + 1) % 1048576 == 0) {
                    sheetNum++;
                    sheet = wb.createSheet(sheetTitle +"_"+ sheetNum);
                    row = sheet.createRow(0);
                    // 声明列对象，参数为列索引，可以是0～255之间的任何一个
                    // 创建标题,此时row=0,即第一行
                    for (int j = 0; j < title.length; j++) {
                        cell = row.createCell(j);
                        cell.setCellValue(title[j]);
                        cell.setCellStyle(xssfCellStyleHeader);
                    }
                }
                row = sheet.createRow((i + 1) - (sheetNum * 1048576)+sheetNum);
                int cellIndex = 0;
                m = result.get(i);
                for (Object o : m) {
                    if(o instanceof Byte){
                        row.createCell((short) cellIndex).setCellValue((Byte) o);
                    }
                    if(o instanceof Short){
                        row.createCell((short) cellIndex).setCellValue((Short) o);
                    }
                    if(o instanceof Integer){
                        row.createCell((short) cellIndex).setCellValue((Integer) o);
                    }
                    if(o instanceof Long){
                        row.createCell((short) cellIndex).setCellValue((Long) o);
                    }
                    if(o instanceof Character){
                        row.createCell((short) cellIndex).setCellValue((Character) o);
                    }
                    if(o instanceof Float){
                        row.createCell((short) cellIndex).setCellValue((Float) o);
                    }
                    if(o instanceof Double){
                        row.createCell((short) cellIndex).setCellValue((Double) o);
                    }
                    if(o instanceof Boolean){
                        row.createCell((short) cellIndex).setCellValue((Boolean) o);
                    }
                    if(o instanceof BigDecimal){
                        row.createCell((short) cellIndex).setCellValue(((BigDecimal) o).doubleValue());
                    }
                    if(o instanceof String){
                        row.createCell((short) cellIndex).setCellValue((String) o);
                    }
                    if(o instanceof Date){
                        row.createCell((short) cellIndex).setCellValue(DateUtis.toStr((Date)o,DateUtis.CLASSICAL));
                    }
                    cellIndex++;
                }
            }
        }


        return wb;
    }

    /**
     * 默认单个工作簿导出方法 超过最大行数 1048576 新增工作簿  =  sheetTitle_1 以此类推
     * @param sheetTitle sheet名字
     * @param title 第一行表格名
     * @param result 数据列表
     */
    public static SXSSFWorkbook getSxssfwbManySameSheets(String sheetTitle, String[] title, ResultSet rs,Date dateBefore)  throws Exception{
        SXSSFWorkbook wb = new SXSSFWorkbook();
        if(title.length == 0 || null==rs){
            throw new Exception("导出数据不能为空");
        }
        int sheetNum = 0;// 记录额外创建的sheet数量
        Sheet sheet = wb.createSheet(sheetTitle);
        // wb.setSheetName(sheetNum, sheetTitle+sheetNum);
        int rownum = 0;
        Row row = sheet.createRow(rownum);

        // 设置并获取到需要的样式
        XSSFCellStyle xssfCellStyleHeader = getAndSetXSSFCellStyleHeader(wb);

        Cell cell;
        // 创建标题,此时row=0,即第一行
        for (int j = 0; j < title.length; j++) {
            cell = row.createCell(j);
            cell.setCellValue(title[j]);
            cell.setCellStyle(xssfCellStyleHeader);
        }

        int i =0;
        while (rs.next()){
            if ((i + 1) % 1048576 == 0) {
                sheetNum++;
                sheet = wb.createSheet(sheetTitle +"_"+ sheetNum);
                row = sheet.createRow(0);
                // 声明列对象，参数为列索引，可以是0～255之间的任何一个
                // 创建标题,此时row=0,即第一行
                for (int j = 0; j < title.length; j++) {
                    cell = row.createCell(j);
                    cell.setCellValue(title[j]);
                    cell.setCellStyle(xssfCellStyleHeader);
                }
            }
            row = sheet.createRow((i + 1) - (sheetNum * 1048576)+sheetNum);

            int cellIndex = 0;
            for (String column: title){
                if(column.indexOf("日期")!=-1){
                    row.createCell((short) cellIndex).setCellValue((String) rs.getString(column));
                }else {
                    Object o = rs.getObject(column);
                    if(o instanceof Byte){
                        row.createCell((short) cellIndex).setCellValue((Byte) o);
                    }
                    if(o instanceof Short){
                        row.createCell((short) cellIndex).setCellValue((Short) o);
                    }
                    if(o instanceof Integer){
                        row.createCell((short) cellIndex).setCellValue((Integer) o);
                    }
                    if(o instanceof Long){
                        row.createCell((short) cellIndex).setCellValue((Long) o);
                    }
                    if(o instanceof Character){
                        row.createCell((short) cellIndex).setCellValue((Character) o);
                    }
                    if(o instanceof Float){
                        row.createCell((short) cellIndex).setCellValue((Float) o);
                    }
                    if(o instanceof Double){
                        row.createCell((short) cellIndex).setCellValue((Double) o);
                    }
                    if(o instanceof Boolean){
                        row.createCell((short) cellIndex).setCellValue((Boolean) o);
                    }
                    if(o instanceof BigDecimal){
                        row.createCell((short) cellIndex).setCellValue(((BigDecimal) o).doubleValue());
                    }
                    if(o instanceof String){
                        row.createCell((short) cellIndex).setCellValue((String) o);
                    }
                    if(o instanceof Date){
                        row.createCell((short) cellIndex).setCellValue(DateUtis.toStr((Date)o,DateUtis.CLASSICAL));
                    }
                }
                cellIndex++;
            }

            i++;
        }
        Date dateAfter = new Date();
        logger.info("共"+i+"条数据,导出列表共执行"+(dateAfter.getTime()-dateBefore.getTime())+"ms");

        return wb;
    }

    /**
     * 多工作簿导出方法 超过最大行数 1048576 新增工作簿  =  sheetTitle_1 以此类推
     * @param sheetTitles 多清单sheet名字
     * @param titles 多清单第一行表格名
     * @param results 多清单数据列表
     */

    public static SXSSFWorkbook getSxssfwbManySameSheets(List<String> sheetTitles, List<String[]>titles, List<List<List>> results) throws Exception{
        SXSSFWorkbook wb = new SXSSFWorkbook();
        if(sheetTitles.size() == 0 || titles.size()!=sheetTitles.size() || sheetTitles.size()!=results.size()){
            throw new Exception("参数错误");
        }
        for (int i=0;i< titles.size();i++){
            if(StringUtils.isBlank(sheetTitles.get(i)) || titles.get(i).length == 0 || results.get(i).size()==0){
                throw new Exception("导出数据不能为空");
            }
        }
        for (int i=0;i< titles.size();i++){

            int sheetNum = 0;// 记录额外创建的sheet数量
            Sheet sheet = wb.createSheet(sheetTitles.get(i));
            int rownum = 0;
            Row row = sheet.createRow(rownum);

            // 设置并获取到需要的样式
            XSSFCellStyle xssfCellStyleHeader = getAndSetXSSFCellStyleHeader(wb);

            Cell cell;
            // 创建标题,此时row=0,即第一行
            for (int j = 0; j < titles.get(i).length; j++) {
                cell = row.createCell(j);
                cell.setCellValue(titles.get(i)[j]);
                cell.setCellStyle(xssfCellStyleHeader);
            }

            // 遍历集合数据，创建excel内容,产生数据行
            if (results != null) {
                List m = null;
                for (int k = 0; k < results.get(i).size(); k++) {
                    if ((k + 1) % 1048576 == 0) {
                        sheetNum++;
                        sheet = wb.createSheet(sheetTitles.get(i) +"_"+ sheetNum);
                        row = sheet.createRow(0);
                        // 声明列对象，参数为列索引，可以是0～255之间的任何一个
                        // 创建标题,此时row=0,即第一行
                        for (int j = 0; j < titles.get(i).length; j++) {
                            cell = row.createCell(j);
                            cell.setCellValue(titles.get(i)[j]);
                            cell.setCellStyle(xssfCellStyleHeader);
                        }
                    }
                    row = sheet.createRow((k + 1) - (sheetNum * 1048576)+sheetNum);
                    int cellIndex = 0;
                    m = results.get(i).get(k);
                    for (Object o : m) {
                        if(o instanceof Byte){
                            row.createCell((short) cellIndex).setCellValue((Byte) o);
                        }
                        if(o instanceof Short){
                            row.createCell((short) cellIndex).setCellValue((Short) o);
                        }
                        if(o instanceof Integer){
                            row.createCell((short) cellIndex).setCellValue((Integer) o);
                        }
                        if(o instanceof Long){
                            row.createCell((short) cellIndex).setCellValue((Long) o);
                        }
                        if(o instanceof Character){
                            row.createCell((short) cellIndex).setCellValue((Character) o);
                        }
                        if(o instanceof Float){
                            row.createCell((short) cellIndex).setCellValue((Float) o);
                        }
                        if(o instanceof Double){
                            row.createCell((short) cellIndex).setCellValue((Double) o);
                        }
                        if(o instanceof Boolean){
                            row.createCell((short) cellIndex).setCellValue((Boolean) o);
                        }
                        if(o instanceof BigDecimal){
                            row.createCell((short) cellIndex).setCellValue(((BigDecimal) o).doubleValue());
                        }
                        if(o instanceof String){
                            row.createCell((short) cellIndex).setCellValue((String) o);
                        }
                        if(o instanceof Date){
                            row.createCell((short) cellIndex).setCellValue(DateUtis.toStr((Date)o,DateUtis.CLASSICAL));
                        }
                        cellIndex++;
                    }
                }
            }
        }
        return wb;
    }


    /**
     * 获取并设置header样式
     */
    private static XSSFCellStyle getAndSetXSSFCellStyleHeader(SXSSFWorkbook sxssfWorkbook) {
        XSSFCellStyle xssfCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
        Font font = sxssfWorkbook.createFont();
        // 字体大小
        font.setFontHeightInPoints((short) 11);
        // 字体粗细
        font.setBold(true);
        font.setFontName("宋体");
        // 将字体应用到样式上面
        xssfCellStyle.setFont(font);
        // 是否自动换行
        xssfCellStyle.setWrapText(false);
        // 水平居中
        xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return xssfCellStyle;
    }
}

结果展示 电脑太垃圾半天打不开

image.png

0
数据导出 POI
版权归属： MDZZW
本文链接： https://mdzzw.cn/archives/exportexcel
许可协议： 本文使用《署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)》协议授权

评论区