poi之SXSSFWorkbook大量数据导出至excel

说明:
        前面我们介绍了使用xls或xlsx模板导出excel数据。但是当数据量比较大时,这样的方式就会特别慢。导出2万条(每条数据占11列)数据时,使用模板的方式,会大约耗时20几分钟。那要导出百万千万甚至更多条数据呢?这时我们可以使用poi的SXSSFWorkbook来导出。

      POI3.8之前的版本不支持大数据量处理,如果数据过多则经常报OOM错误,有时候调整JVM大小效果也不是太好。POI3.8版本新出来了SXSSFWorkbook,可以支持大数据量的操作。

     3.8版本的POI对excel的导出操作,一般只使用HSSFWorkbook以及SXSSFWorkbook,HSSFWorkbook用来处理较少的数据量,SXSSFWorkbook用来处理大数据量以及超大数据量的导出。

 

HSSFWorkbook、XSSFWorkbook、SXSSFWorkbook的区别:

     ◎HSSFWorkbook一般用于Excel2003版及更早版本(扩展名为.xls)的导出。

     ◎XSSFWorkbook一般用于Excel2007版(扩展名为.xlsx)的导出。

     ◎SXSSFWorkbook一般用于大数据量的导出。

注:HSSFWorkbook和XSSFWorkbook的Excel Sheet导出条数上限(<=2003版)是65535行、256列,(>=2007版)是
       1048576行,16384列,如果数据量超过了此上限,那么可以使用SXSSFWorkbook来导出。实际上上万条数据,
       甚至上千条数据就可以考虑使用SXSSFWorkbook了。

 

本人测试时软硬件环境:Windows10、idea、jdk1.8

准备工作:在pom.xml中引入依赖

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

注:因为本人还需要连库查询,所以本人还引入了MySQL的相关依赖,并在application.properties中进行了相关的数据库连接
     配置;这里就不再一一给出了。

SXSSFWorkbook导出excel示例

import com.mapper.DemoMapper;
import com.model.ExcelExportModel;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

@RunWith(SpringRunner.class)
@SpringBootTest
public class SxxfWorkbookApplicationTests {
    private final static Logger logger = LoggerFactory.getLogger(SxxfWorkbookApplicationTests.class);

    @Autowired
    private DemoMapper mapper;

    @Test
    public void contextLoads() {

    // 导出的excel,全文件名
    final String excelExportDestfilepath = "C:/Users/JustryDeng/Desktop/abc.xlsx";

        FileOutputStream fos = null;
        SXSSFWorkbook sxssfWorkbook = null;
        try {
            /// -> 从数据库中查询出要进行excel导出的数据
            String timeLeft = "2018-10-10 00:00:00";
            String timeRight = "2018-10-11 00:00:00";
            long startTime0 = System.currentTimeMillis();
            List<ExcelExportModel> list = mapper.queryData(timeLeft, timeRight);
            long endTime0 = System.currentTimeMillis();
            logger.info("查询数据总耗时:{} 毫秒; list数量为 {}", endTime0 - startTime0, list.size());

            /// -> excel到处逻辑
            long startTime = System.currentTimeMillis();
            // 获取SXSSFWorkbook实例
            sxssfWorkbook = new SXSSFWorkbook();
            Sheet sheet = sxssfWorkbook.createSheet("我是Sheet");
            // 冻结最左边的两列、冻结最上面的一行
            // 即:滚动横向滚动条时,左边的第一、二列固定不动;滚动纵向滚动条时,上面的第一行固定不动。
            sheet.createFreezePane(2, 1);
            // 设置并获取到需要的样式
            XSSFCellStyle xssfCellStyleHeader = getAndSetXSSFCellStyleHeader(sxssfWorkbook);
            XSSFCellStyle xssfCellStyleOne = getAndSetXSSFCellStyleOne(sxssfWorkbook);
            XSSFCellStyle xssfCellStyleTwo = getAndSetXSSFCellStyleTwo(sxssfWorkbook);
            // 创建第一行,作为header表头
            Row header = sheet.createRow(0);
            // 循环创建header单元格(根据实际情况灵活创建即可)
            for (int cellnum = 0; cellnum < 11; cellnum++) {
                Cell cell = header.createCell(cellnum);
                cell.setCellStyle(xssfCellStyleHeader);
                // 判断单元格
                if (cellnum == 0) {
                    cell.setCellValue("通话ID");
                } else if (cellnum == 1) {
                    cell.setCellValue("绑定关系ID");
                } else if (cellnum == 2) {
                    cell.setCellValue("主叫号码");
                } else if (cellnum == 3) {
                    cell.setCellValue("被叫号码");
                } else if (cellnum == 4) {
                    cell.setCellValue("中间号码");
                } else if (cellnum == 5) {
                    cell.setCellValue("通话发生时间");
                } else if (cellnum == 6) {
                    cell.setCellValue("通话开始时间");
                } else if (cellnum == 7) {
                    cell.setCellValue("通话结束时间");
                } else if (cellnum == 8) {
                    cell.setCellValue("通话时长(秒)");
                } else if (cellnum == 9) {
                    cell.setCellValue("结束发起方");
                } else {
                    cell.setCellValue("结束状态(即挂断原因)");
                }
            }

            // 遍历创建行,导出数据
            for (int rownum = 1; rownum <= list.size(); rownum++) {
                Row row = sheet.createRow(rownum);
                // 循环创建单元格
                for (int cellnum = 0; cellnum < 11; cellnum++) {
                    Cell cell = row.createCell(cellnum);
                    // 根据行数,设置该行内的单元格样式
                    if (rownum % 2 == 1) { // 奇数
                        cell.setCellStyle(xssfCellStyleOne);
                    } else { // 偶数
                        cell.setCellStyle(xssfCellStyleTwo);
                    }
                    // 根据单元格所属,录入相应内容
                    // 将部分数字类型的字符串,转换为Long;以免导出excel后,单元格左上角有三
                    //    角形(这是excel检查到该单元格内的内容均为数字,但是单元格类型却不是
                    //    数字,给出的提示),转不转看自己需求灵活处理
                    if (cellnum == 0) {
                        cell.setCellValue((list.get(rownum - 1).getCallId()));
                    } else if (cellnum == 1) {
                        cell.setCellValue(list.get(rownum - 1).getBindId());
                    } else if (cellnum == 2) {
                        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                        cell.setCellValue(Long.parseLong(list.get(rownum - 1).getCallNo()));
                    } else if (cellnum == 3) {
                        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                        cell.setCellValue(Long.parseLong(list.get(rownum - 1).getPeerNo()));
                    } else if (cellnum == 4) {
                        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                        cell.setCellValue(Long.parseLong(list.get(rownum - 1).getTelX()));
                    } else if (cellnum == 5) {
                        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                        cell.setCellValue(Long.parseLong(list.get(rownum - 1).getCallTime()));
                    } else if (cellnum == 6) {
                        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                        cell.setCellValue(Long.parseLong(list.get(rownum - 1).getStartTime()));
                    } else if (cellnum == 7) {
                        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                        cell.setCellValue(Long.parseLong(list.get(rownum - 1).getFinishTime()));
                    } else if (cellnum == 8) {
                        cell.setCellValue(list.get(rownum - 1).getCallDuration());
                    } else if (cellnum == 9) {
                        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                        cell.setCellValue(Long.parseLong(list.get(rownum - 1).getFinishType()));
                    } else {
                        cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                        cell.setCellValue(Long.parseLong(list.get(rownum - 1).getFinishState()));

                    }
                }
            }
            // 在后面设置sheet
            setSheet(sheet);
            fos = new FileOutputStream(excelExportDestfilepath);
            sxssfWorkbook.write(fos);
            long endTime = System.currentTimeMillis();
            logger.info("数据全部导出至excel总耗时:{} 毫秒!", endTime - startTime, list.size());
        } catch (Exception e) {
            logger.error("发生异常咯!", e);
        } finally {
            try {
                if(sxssfWorkbook != null) {
                    // dispose of temporary files backing this workbook on disk -> 处
                    //     理SXSSFWorkbook导出excel时,产生的临时文件
                    sxssfWorkbook.dispose();
                }
                if(fos != null) {
                    fos.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }

        }
    }



    /**
     * 设置sheet
     */
    private void setSheet(Sheet sheet) {
        // 设置各列宽度(单位为:字符宽度的1/256)
        sheet.setColumnWidth(0, 32 * 256);
        sheet.setColumnWidth(1, 32 * 256);
        sheet.setColumnWidth(2, 20 * 256);
        sheet.setColumnWidth(3, 20 * 256);
        sheet.setColumnWidth(4, 20 * 256);
        sheet.setColumnWidth(5, 20 * 256);
        sheet.setColumnWidth(6, 20 * 256);
        sheet.setColumnWidth(7, 20 * 256);
        sheet.setColumnWidth(8, 20 * 256);
        sheet.setColumnWidth(9, 20 * 256);
        sheet.setColumnWidth(10, 32 * 256);
    }

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

    /**
     * 获取并设置样式一
     */
    private XSSFCellStyle getAndSetXSSFCellStyleOne(SXSSFWorkbook sxssfWorkbook) {
        XSSFCellStyle xssfCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
        XSSFDataFormat format = (XSSFDataFormat)sxssfWorkbook.createDataFormat();
        // 是否自动换行
        xssfCellStyle.setWrapText(false);
        // 水平居中
        xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 前景颜色
        xssfCellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        xssfCellStyle.setFillForegroundColor(IndexedColors.AQUA.getIndex());
        // 边框
        xssfCellStyle.setBorderBottom(BorderStyle.THIN);
        xssfCellStyle.setBorderRight(BorderStyle.THIN);
        xssfCellStyle.setBorderTop(BorderStyle.THIN);
        xssfCellStyle.setBorderLeft(BorderStyle.THIN);
        xssfCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        xssfCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        xssfCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        xssfCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        // 防止数字过长,excel导出后,显示为科学计数法,如:防止8615192053888被显示为8.61519E+12
        xssfCellStyle.setDataFormat(format.getFormat("0"));
        return xssfCellStyle;
    }

    /**
     * 获取并设置样式二
     */
    private XSSFCellStyle getAndSetXSSFCellStyleTwo(SXSSFWorkbook sxssfWorkbook) {
        XSSFCellStyle xssfCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
        XSSFDataFormat format = (XSSFDataFormat)sxssfWorkbook.createDataFormat();
        // 是否自动换行
        xssfCellStyle.setWrapText(false);
        // 水平居中
        xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 边框
        xssfCellStyle.setBorderBottom(BorderStyle.THIN);
        xssfCellStyle.setBorderRight(BorderStyle.THIN);
        xssfCellStyle.setBorderTop(BorderStyle.THIN);
        xssfCellStyle.setBorderLeft(BorderStyle.THIN);
        xssfCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        xssfCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        xssfCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        xssfCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        // 垂直居中
        xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 防止数字过长,excel导出后,显示为科学计数法,如:防止8615192053888被显示为8.61519E+12
        xssfCellStyle.setDataFormat(format.getFormat("0"));
        return xssfCellStyle;
    }

}

小知识:列宽自适应的设置(示例)

注:当设置宽度自适应时,设置时机需要放在设置完cell样式后面进行,如果放在开头设置,那么可能不起作用;
       设置宽度自适应时不能同时设置自动换行,否者也可能导致宽度自适应失效。

注:autoSizeColumn方法对中文支不不是很好,必要的话可自己写部分逻辑来实现宽度自适应,可参照着源码进行编写。

测试一下:运行测试方法,控制台输出一下信息

再去C:/Users/JustryDeng/Desktop/下,看见abc.xlsx文件确实生成了,打开该excel文件,比对数据,发现数据准确无误。

由此可见->成功!

 

注:如果目标位置已存在同名(含后缀名)文件,那么会覆盖原来的那个文件

注:导出同样数量的数据,使用poi的SXSSFWorkbook最快,其次是使用poi的HSSFWorkbook(或XSSFWorkbook),使用
       其他(对poi进行了封装的)框架,效率会比较低,如使用模板导出excel

 

^_^ 参考链接
            https://blog.csdn.net/qq_34869143/article/details/76512289
            https://zhidao.baidu.com/question/508958518.html

            http://happyqing.iteye.com/blog/2190225

^_^ 如有不当之处,欢迎指正

^_^ 本文已经被收录进《程序员成长笔记(三)》,笔者JustryDeng

©️2020 CSDN 皮肤主题: 书香水墨 设计师:CSDN官方博客 返回首页