2019-03-25 17:41:38 Giggle1994 阅读数 314
  • 基于SSM的POI导入导出Excel实战

    本课程将给大家分享如何基于SSM实现POI导入导出Excel,并讲解目前企业级JavaWeb应用mvc三层模式的开发流程,可让初学者或者职场萌新掌握如何基于SSM整合第三方框架并采用mvc三层开发模式实现自己的业务模块!

    1265 人正在学习 去看看 钟林森

一:简介

          SXSSFWorkbook是用来生成海量excel数据文件,主要原理是借助临时存储空间生成excel,
          SXSSFWorkbook专门处理大数据,对于大型excel的创建且不会内存溢出的,就只有SXSSFWorkbook了。
          它的原理很简单,用硬盘空间换内存(就像hashmap用空间换时间一样)。 SXSSFWorkbook是streaming
          版本的XSSFWorkbook,它只会保存最新的excel rows在内存里供查看,在此之前的excel rows都会被写入到
          硬盘里(Windows电脑的话,是写入到C盘根目录下的temp文件夹)。被写入到硬盘里的rows是不可见的/不
          可访问的。只有还保存在内存里的才可以被访问到。 
          注:HSSFWorkbook和XSSFWorkbook的Excel Sheet导出条数上限(<=2003版)是65535行、256列,(>=2007版)
               是1048576行,16384列,如果数据量超过了此上限,那么可以使用SXSSFWorkbook来导出。实际上上万条数据,
               甚至上千条数据就可以考虑使用SXSSFWorkbook了。
        注意:首先需要引入依赖:注意:4.0.0版本的JDK需要1.8以上,如果JDK是1.7的,那么就使用3.9版本的依赖

  1. <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
  2. <dependency>
  3. <groupId>org.apache.poi</groupId>
  4. <artifactId>poi-ooxml-schemas</artifactId>
  5. <version>4.0.0</version>
  6. </dependency>
  7. <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
  8. <dependency>
  9. <groupId>org.apache.poi</groupId>
  10. <artifactId>poi-ooxml</artifactId>
  11. <version>4.0.0</version>
  12. </dependency>
  13. <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
  14. <dependency>
  15. <groupId>org.apache.poi</groupId>
  16. <artifactId>poi</artifactId>
  17. <version>4.0.0</version>
  18. </dependency>

二:实例一,我们使用SXSSFWorkbook向Excel中写入50万条数据,只需要         34秒左右,内存占用率最多在700M左右,CPU使用率在25%左右           
        


           代码如下:

  1. package com.test.POI;
  2. import java.io.BufferedOutputStream;
  3. import java.io.File;
  4. import java.io.FileNotFoundException;
  5. import java.io.FileOutputStream;
  6. import java.io.IOException;
  7. import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
  8. import org.apache.poi.xssf.streaming.SXSSFRow;
  9. import org.apache.poi.xssf.streaming.SXSSFSheet;
  10. import org.apache.poi.xssf.streaming.SXSSFWorkbook;
  11. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  12. public class SXSSFWORKBookUtils {
  13. @SuppressWarnings("resource")
  14. public static void main(String[] args) throws FileNotFoundException, InvalidFormatException {
  15. long startTime = System.currentTimeMillis();
  16. String filePath = "E:\\txt\\111.xlsx";
  17. SXSSFWorkbook sxssfWorkbook = null;
  18. BufferedOutputStream outputStream = null;
  19. try {
  20. //这样表示SXSSFWorkbook只会保留100条数据在内存中,其它的数据都会写到磁盘里,这样的话占用的内存就会很少
  21. sxssfWorkbook = new SXSSFWorkbook(getXSSFWorkbook(filePath),100);
  22. //获取第一个Sheet页
  23. SXSSFSheet sheet = sxssfWorkbook.getSheetAt(0);
  24. for (int i = 0; i < 50; i++) {
  25. for (int z = 0; z < 10000; z++) {
  26. SXSSFRow row = sheet.createRow(i*10000+z);
  27. for (int j = 0; j < 10; j++) {
  28. row.createCell(j).setCellValue("你好:"+j);
  29. }
  30. }
  31. }
  32. outputStream = new BufferedOutputStream(new FileOutputStream(filePath));
  33. sxssfWorkbook.write(outputStream);
  34. outputStream.flush();
  35. sxssfWorkbook.dispose();// 释放workbook所占用的所有windows资源
  36. } catch (IOException e) {
  37. e.printStackTrace();
  38. }finally {
  39. if(outputStream!=null) {
  40. try {
  41. outputStream.close();
  42. } catch (IOException e) {
  43. e.printStackTrace();
  44. }
  45. }
  46. }
  47. long endTime = System.currentTimeMillis();
  48. System.out.println(endTime-startTime);
  49. }
  50. /**
  51. * 先创建一个XSSFWorkbook对象
  52. * @param filePath
  53. * @return
  54. */
  55. public static XSSFWorkbook getXSSFWorkbook(String filePath) {
  56. XSSFWorkbook workbook = null;
  57. BufferedOutputStream outputStream = null;
  58. try {
  59. File fileXlsxPath = new File(filePath);
  60. outputStream = new BufferedOutputStream(new FileOutputStream(fileXlsxPath));
  61. workbook = new XSSFWorkbook();
  62. workbook.createSheet("测试Sheet");
  63. workbook.write(outputStream);
  64. } catch (Exception e) {
  65. e.printStackTrace();
  66. }finally {
  67. if(outputStream!=null) {
  68. try {
  69. outputStream.close();
  70. } catch (IOException e) {
  71. e.printStackTrace();
  72. }
  73. }
  74. }
  75. return workbook;
  76. }
  77. }

  效果:
      

三:我们使用XSSFWorkbook常规的方法分批向excel中写入50万条数据,内         存占用率最多在  2.1个G左右(占用了很大的内存),CPU使用率在90%           左右 ,最后内存 溢出了

          
          代码如下:
          

  1. package com.test;
  2. import java.io.BufferedOutputStream;
  3. import java.io.File;
  4. import java.io.FileInputStream;
  5. import java.io.FileNotFoundException;
  6. import java.io.FileOutputStream;
  7. import java.io.IOException;
  8. import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
  9. import org.apache.poi.xssf.usermodel.XSSFRow;
  10. import org.apache.poi.xssf.usermodel.XSSFSheet;
  11. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  12. public class POIController {
  13. /**
  14. * 这种方式效率比较低并且特别占用内存,数据量越大越明显
  15. * @param args
  16. * @throws FileNotFoundException
  17. * @throws InvalidFormatException
  18. */
  19. public static void main(String[] args) throws FileNotFoundException, InvalidFormatException {
  20. long startTime = System.currentTimeMillis();
  21. BufferedOutputStream outPutStream = null;
  22. XSSFWorkbook workbook = null;
  23. FileInputStream inputStream = null;
  24. String filePath = "E:\\txt\\666.xlsx";
  25. try {
  26. workbook = getWorkBook(filePath);
  27. XSSFSheet sheet = workbook.getSheetAt(0);
  28. for (int i = 0; i < 50; i++) {
  29. for (int z = 0; z < 10000; z++) {
  30. XSSFRow row = sheet.createRow(i*10000+z);
  31. for (int j = 0; j < 10; j++) {
  32. row.createCell(j).setCellValue("你好:"+j);
  33. }
  34. }
  35. //每次要获取新的文件流对象,避免将之前写入的数据覆盖掉
  36. outPutStream = new BufferedOutputStream(new FileOutputStream(filePath));
  37. workbook.write(outPutStream);
  38. }
  39. } catch (IOException e) {
  40. e.printStackTrace();
  41. }finally {
  42. if(outPutStream!=null) {
  43. try {
  44. outPutStream.close();
  45. } catch (IOException e) {
  46. e.printStackTrace();
  47. }
  48. }
  49. if(inputStream!=null) {
  50. try {
  51. inputStream.close();
  52. } catch (IOException e) {
  53. e.printStackTrace();
  54. }
  55. }
  56. if(workbook!=null) {
  57. try {
  58. workbook.close();
  59. } catch (IOException e) {
  60. e.printStackTrace();
  61. }
  62. }
  63. }
  64. long endTime = System.currentTimeMillis();
  65. System.out.println(endTime-startTime);
  66. }
  67. /**
  68. * 先创建一个XSSFWorkbook对象
  69. * @param filePath
  70. * @return
  71. */
  72. public static XSSFWorkbook getWorkBook(String filePath) {
  73. XSSFWorkbook workbook = null;
  74. try {
  75. File fileXlsxPath = new File(filePath);
  76. BufferedOutputStream outPutStream = new BufferedOutputStream(new FileOutputStream(fileXlsxPath));
  77. workbook = new XSSFWorkbook();
  78. workbook.createSheet("测试");
  79. workbook.write(outPutStream);
  80. } catch (Exception e) {
  81. e.printStackTrace();
  82. }
  83. return workbook;
  84. }
  85. }

效果:
   
        

2018-05-22 00:33:37 sinat_30314715 阅读数 10198
  • 基于SSM的POI导入导出Excel实战

    本课程将给大家分享如何基于SSM实现POI导入导出Excel,并讲解目前企业级JavaWeb应用mvc三层模式的开发流程,可让初学者或者职场萌新掌握如何基于SSM整合第三方框架并采用mvc三层开发模式实现自己的业务模块!

    1265 人正在学习 去看看 钟林森

java代码使用poi的API解决在读取大数据量的Excel数据时候内存溢出的问题:首先我需要声明下面的工具类是在老袁博客(https://laoyuan.me/posts/java-read-big-excel-with-poi.html)基础上做了稍微的改造,我将老袁的的工具类需要2个参数改成只需要一个参数就可以完成调用,当然你可以根据你自己的情况使用。
下面是一个工具类,复制到自己的项目中直接调用即可:

1、工具类

package com.xxx.xxx.xxx;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler.SheetContentsHandler;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.XMLReaderFactory;
/**
 * 解析大数据量Excel工具类
 * @author RobinTime
 *
 */
@Component
public class ExcelParser {
    private static final Logger logger = LoggerFactory.getLogger(ExcelParser.class);
    /**
     * 表格默认处理器
     */
    private ISheetContentHandler contentHandler = new DefaultSheetHandler();
    /**
     * 读取数据
     */
    private List<String[]> datas = new ArrayList<String[]>();

    /**
     * 转换表格,默认为转换第一个表格
     * @param stream
     * @return
     * @throws InvalidFormatException
     * @throws IOException
     * @throws ParseException
     */
    public ExcelParser parse(InputStream stream)
            throws InvalidFormatException, IOException, ParseException {
        return parse(stream, 1);
    }


    /**
     * 
     * @param stream
     * @param sheetId:为要遍历的sheet索引,从1开始
     * @return
     * @throws InvalidFormatException
     * @throws IOException
     * @throws ParseException
     */
    public synchronized ExcelParser parse(InputStream stream, int sheetId)
            throws InvalidFormatException, IOException, ParseException {
        // 每次转换前都清空数据
        datas.clear();
        // 打开表格文件输入流
        OPCPackage pkg = OPCPackage.open(stream);
        try {
            // 创建表阅读器
            XSSFReader reader;
            try {
                reader = new XSSFReader(pkg);
            } catch (OpenXML4JException e) {
                logger.error("读取表格出错");
                throw new ParseException(e.fillInStackTrace());
            }

            // 转换指定单元表
            InputStream shellStream = reader.getSheet("rId" + sheetId);
            try {
                InputSource sheetSource = new InputSource(shellStream);
                StylesTable styles = reader.getStylesTable();
                ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(pkg);
                getContentHandler().init(datas);// 设置读取出的数据
                // 获取转换器
                XMLReader parser = getSheetParser(styles, strings);
                parser.parse(sheetSource);
            } catch (SAXException e) {
                logger.error("读取表格出错");
                throw new ParseException(e.fillInStackTrace());
            } finally {
                shellStream.close();
            }
        } finally {
            pkg.close();

        }
        return this;

    }

    /**
     * 获取表格读取数据,获取数据前,需要先转换数据<br>
     * 此方法不会获取第一行数据
     * 
     * @return 表格读取数据
     */
    public List<String[]> getDatas() {
        return getDatas(true);

    }

    /**
     * 获取表格读取数据,获取数据前,需要先转换数据
     * 
     * @param dropFirstRow
     *            删除第一行表头记录
     * @return 表格读取数据
     */
    public List<String[]> getDatas(boolean dropFirstRow) {
        if (dropFirstRow && datas.size() > 0) {
            datas.remove(0);// 删除表头
        }
        return datas;

    }

    /**
     * 获取读取表格的转换器
     * 
     * @return 读取表格的转换器
     * @throws SAXException
     *             SAX错误
     */
    protected XMLReader getSheetParser(StylesTable styles, ReadOnlySharedStringsTable strings) throws SAXException {
        XMLReader parser = XMLReaderFactory.createXMLReader();
        parser.setContentHandler(new XSSFSheetXMLHandler(styles, strings, getContentHandler(), false));
        return parser;
    }

    public ISheetContentHandler getContentHandler() {
        return contentHandler;
    }

    public void setContentHandler(ISheetContentHandler contentHandler) {
        this.contentHandler = contentHandler;
    }

    /**
     * 表格转换错误
     */
    public class ParseException extends Exception {
        private static final long serialVersionUID = -2451526411018517607L;

        public ParseException(Throwable t) {
            super("表格转换错误", t);
        }

    }

    public interface ISheetContentHandler extends SheetContentsHandler {

        /**
         * 设置转换后的数据集,用于存放转换结果
         * 
         * @param datas
         *            转换结果
         */
        void init(List<String[]> datas);
    }

    /**
     * 默认表格解析handder
     */
    class DefaultSheetHandler implements ISheetContentHandler {
        /**
         * 读取数据
         */
        private List<String[]> datas;
        private int columsLength;
        // 读取行信息
        private String[] readRow;
        private ArrayList<String> fristRow = new ArrayList<String>();

        @Override
        public void init(List<String[]> datas) {
            this.datas = datas;
//          this.columsLength = columsLength;
        }

        @Override
        public void startRow(int rowNum) {
            if (rowNum != 0) {
                readRow = new String[columsLength];
            }
        }

        @Override
        public void endRow(int rowNum) {
        //将Excel第一行表头的列数当做数组的长度,要保证后续的行的列数不能超过这个长度,这是个约定。
            if (rowNum == 0) {
                columsLength = fristRow.size();
                readRow = fristRow.toArray(new String[fristRow.size()]);
            }else {
                readRow = fristRow.toArray(new String[columsLength]);
            }
            datas.add(readRow.clone());
            readRow = null;
            fristRow.clear();
        }

        @Override
        public void cell(String cellReference, String formattedValue, XSSFComment comment) {
            int index = getCellIndex(cellReference);//转换A1,B1,C1等表格位置为真实索引位置
            try {
                fristRow.set(index, formattedValue);
            } catch (IndexOutOfBoundsException e) {
                int size = fristRow.size();
                for (int i = index - size+1;i>0;i--){
                    fristRow.add(null);
                }
                fristRow.set(index,formattedValue);
            }
        }

        @Override
        public void headerFooter(String text, boolean isHeader, String tagName) {
        }

        /**
         * 转换表格引用为列编号
         * 
         * @param cellReference
         *            列引用
         * @return 表格列位置,从0开始算
         */
        public int getCellIndex(String cellReference) {
            String ref = cellReference.replaceAll("\\d+", "");
            int num = 0;
            int result = 0;
            for (int i = 0; i < ref.length(); i++) {
                char ch = cellReference.charAt(ref.length() - i - 1);
                num = (int) (ch - 'A' + 1);
                num *= Math.pow(26, i);
                result += num;
            }
            return result - 1;
        }
    }
}

2、调用

File tempFile = new File(this.getClass().getClassLoader().getResource("").getPath() + "tempFile\\" + (new Date()).getTime() + ".xlsx");
//传入一个路径产生流再将流传入工具类,返回解析对象,Excel的所有数据就被解析到List<String[]> 里面,遍历list任由你处置。
FileInputStream inputStream = new FileInputStream(tempFile);
ExcelParser parse = excelParser.parse(inputStream);
List<String[]> datas = parse.getDatas();
2019-08-01 15:59:18 weixin_40049583 阅读数 36
  • 基于SSM的POI导入导出Excel实战

    本课程将给大家分享如何基于SSM实现POI导入导出Excel,并讲解目前企业级JavaWeb应用mvc三层模式的开发流程,可让初学者或者职场萌新掌握如何基于SSM整合第三方框架并采用mvc三层开发模式实现自己的业务模块!

    1265 人正在学习 去看看 钟林森

百万数据POI操作(一)

概述

​ 我们都知道Excel可以分为早期的Excel2003版本(使用POI的HSSF对象操作)和Excel2007版本(使用POI的XSSF操作),两者对百万数据的支持如下:

  • Excel 2003:在POI中使用HSSF对象时,excel 2003最多只允许存储65536条数据,一般用来处理较少的数据量。这时对于百万级别数据,Excel肯定容纳不了。
  • Excel 2007:当POI升级到XSSF对象时,它可以直接支持excel2007以上版本,因为它采用ooxml格式。这时excel可以支持1048576条数据,单个sheet表就支持近百万条数据。但实际运行时还可能存在问题,原因是执行POI报表所产生的行对象,单元格对象,字体对象,他们都不会销毁,这就导致OOM的风险

JDK性能监控工具

没有性能监控工具一切推论都只能停留在理论阶段,我们可以使用Java的性能监控工具来监视程序的运行情况,包括CUP,垃圾回收,内存的分配和使用情况,这让程序的运行阶段变得更加可控,也可以用来证明我们的推测。这里我们使用JDK提供的性能工具Jvisualvm来监控程序运行。

​ VisualVM 是Netbeans的profile子项目,已在JDK中自带,能够监控线程,内存情况,查看方法的CPU时间和内存中的对 象,已被GC的对象,反向查看分配的堆栈。

​ Jvisualvm位于JAVA_HOME/bin目录下,直接双击就可以打开该程序。如果只是监控本地的java进程,是不需要配置参数的,直接打开就能够进行监控。首先我们需要在本地打开一个Java程序,例如我打开员工后台管理系统进程,这时在jvisualvm界面就可以看到与IDEA相关的Java进程了

Jvisualvm的使用

Jvisualvm使用起来比较简单,双击点击当前运行的进程即可进入到程序的监控界面:

在这里插入图片描述

  • 双击Jvisualvm.exe

在这里插入图片描述

  • 概述:可以看到进程的启动参数。
  • 监视:左上:cpu利用率,gc状态的监控,右上:堆利用率,永久内存区的利用率,左下:类的监控,右下:线程的监控
  • 线程:能够显示线程的名称和运行的状态,在调试多线程时必不可少,而且可以点进一个线程查看这个线程的详细运行情况

百万数据POI操作(二)SXSSFWorkBook处理百万数据报表打印

需求

使用Apache POI完成百万数据量的Excel报表导出。

分析

​ 基于XSSFWork导出Excel报表,是通过将所有单元格对象保存到内存中,当所有的Excel单元格全部创建完成之后一次性写入到Excel并导出。当百万数据级别的Excel导出时,随着表格的不断创建,内存中对象越来越多,直至内存溢出。ApachePoi提供了SXSSFWork对象,专门用于处理大数据量Excel报表导出。

​ 在实例化SXSSFWork这个对象时,可以指定在内存中所产生的POI导出相关对象的数量(默认100),一旦内存中的对象的个数达到这个指定值时,就将内存中的这些对象的内容写入到磁盘中(XML的文件格式),就可以将这些对象从内存中销毁,以后只要达到这个值,就会以类似的处理方式处理,直至Excel导出完成。

Api

SXSSFWorkbook

  • 处理大数据量excel报表生成的:将已经使用过的内存元素,即使删除(poi4采用)或者保存到本地磁盘(poi3)

  • 使用条件:

    (1) 不支持模板打印

    (2)不支持太多的样式对象

实现

步骤1:模拟百万数据的导出

package cn.zonhar.web.controller.cargo;

import cn.zonhar.entity.cargo.Contract;
import cn.zonhar.entity.cargo.ContractExample;
import cn.zonhar.entity.system.User;
import cn.zonhar.entity.utils.DeleteStatus;
import cn.zonhar.entity.vo.ContractProductVo;
import cn.zonhar.service.cargo.ContractProductService;
import cn.zonhar.service.cargo.ContractService;
import cn.zonhar.web.controller.BaseController;
import cn.zonhar.web.utils.DownloadUtil;
import com.alibaba.dubbo.config.annotation.Reference;
import com.github.pagehelper.PageInfo;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.List;

/**
 * @author zonhar
 * @date 2019/7/27
 * 购销合同
 */
@Controller
@RequestMapping("/cargo/contract")
public class ContractController extends BaseController {

    @Reference
    private ContractService contractService;
    @Reference
    private ContractProductService contractProductService;


    @RequestMapping(value = "/print", name = "去出货表页面")
    public String toPrint() {
        return "/cargo/print/contract-print";
    }

  

    /**
     * 处理百万级导出
     *
     * @param inputDate 出货时间
     * @return
     */
    @RequestMapping(value = "/printExcel", name = "打印出货表")
    public void printExcel(String inputDate) throws IOException {
        Workbook workbook = new SXSSFWorkbook();

        Sheet sheet = workbook.createSheet("出货表");


        //出货表.xls ,设置每列列宽
        sheet.setColumnWidth(0, 0 * 256);
        sheet.setColumnWidth(1, 26 * 256);
        sheet.setColumnWidth(2, 12 * 256);
        sheet.setColumnWidth(3, 30 * 256);
        sheet.setColumnWidth(4, 12 * 256);
        sheet.setColumnWidth(5, 15 * 256);
        sheet.setColumnWidth(6, 10 * 256);
        sheet.setColumnWidth(7, 10 * 256);
        sheet.setColumnWidth(8, 8 * 256);

        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 8));
        /**
         * 创建第一行
         */
        Row row = sheet.createRow(0);
        row.setHeightInPoints(36);
        Cell cell = row.createCell(1);
        cell.setCellStyle(this.bigTitle(workbook));
        String value = inputDate.replace("-0", "-").replace("-", "年") + "月份出货表";
        cell.setCellValue(value);
        /**
         * 创建第二行
         */
        row = sheet.createRow(1);
        row.setHeightInPoints(26);
        String titles[] = new String[]{"客户", "订单号", "货号", "数量", "工厂",
                "工厂交期", "船期", "贸易条款"};
        for (int i = 0; i < titles.length; i++) {
            cell = row.createCell(i + 1);
            cell.setCellValue(titles[i]);
            cell.setCellStyle(this.title(workbook));
        }

        /**
         * 导出数据行
         */
        String companyId = getLoginCompanyId();
        List<ContractProductVo> list =
                contractProductService.findByShipTime(companyId, inputDate);

        if (list != null && list.size() > 0) {
            int index = 2;
            for (ContractProductVo cp : list) {
                for (int i = 0; i < 6000; i++) {


                    row = sheet.createRow(index++);
                    row.setHeightInPoints(24);

                    cell = row.createCell(1);
                    cell.setCellValue(cp.getCustomName());
                    //  cell.setCellStyle(this.text((workbook)));

                    cell = row.createCell(2);
                    cell.setCellValue(cp.getContractNo());
                    //  cell.setCellStyle(this.text(workbook));

                    cell = row.createCell(3);
                    cell.setCellValue(cp.getProductNo());
                    //  cell.setCellStyle(this.text(workbook));

                    cell = row.createCell(4);
                    cell.setCellValue(cp.getCnumber());
                    // cell.setCellStyle(this.text(workbook));

                    cell = row.createCell(5);
                    cell.setCellValue(cp.getFactoryName());
                    //  cell.setCellStyle(this.text(workbook));

                    cell = row.createCell(6);
                    cell.setCellValue(cp.getDeliveryPeriod());
                    //  cell.setCellStyle(this.text(workbook));

                    cell = row.createCell(7);
                    cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(cp.getShipTime()));
                    // cell.setCellStyle(this.text(workbook));

                    cell = row.createCell(8);
                    cell.setCellValue(cp.getTradeTerms());
                    //  cell.setCellStyle(this.text(workbook));
                }
            }
        }

        //导出下载
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        workbook.write(bos);
        new DownloadUtil().download(bos, response, "出货表.xlsx");


    }
  
}

堆内存的消耗:

在这里插入图片描述

导出后的大小:

在这里插入图片描述

在这里插入图片描述

2018-05-10 14:47:47 whandgdh 阅读数 10177
  • 基于SSM的POI导入导出Excel实战

    本课程将给大家分享如何基于SSM实现POI导入导出Excel,并讲解目前企业级JavaWeb应用mvc三层模式的开发流程,可让初学者或者职场萌新掌握如何基于SSM整合第三方框架并采用mvc三层开发模式实现自己的业务模块!

    1265 人正在学习 去看看 钟林森

今天在做excel文件上传时,发现数据量超过10万条后,系统会出现内存溢出。
跟踪代码发现程序卡在如下代码这里:

Workbook workbook=WorkbookFactory.create(new File(path));

通过poi的api发现此方法属于能对excel读写,但是内存消耗大,速度较慢。
在poi的官方文档中找到了读取大量数据的方法,但只能读数据,不能进行写操作。
代码整理如下


import java.io.InputStream;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.junit.Test;
import org.xml.sax.Attributes;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;

/**
 * 数据量比较大(8万条以上)的excel文件解析,将excel文件解析为 行列坐标-值的形式存入map中,此方式速度快,内存耗损小 但只能读取excle文件
 * 提供处理单个sheet方法 processOneSheet(String  filename) 以及处理多个sheet方法 processAllSheets(String  filename)
 * 只需传入文件路径+文件名即可  调用处理方法结束后,只需 接收LargeExcelFileReadUtil.getRowContents()返回值即可获得解析后的数据
 *
 */
public class LargeExcelFileReadUtil  {

    private  LinkedHashMap<String, String>rowContents=new LinkedHashMap<String, String>(); 
    private  SheetHandler sheetHandler;

public LinkedHashMap<String, String> getRowContents() {
        return rowContents;
    }
    public void setRowContents(LinkedHashMap<String, String> rowContents) {
        this.rowContents = rowContents;
    }

    public SheetHandler getSheetHandler() {
        return sheetHandler;
    }
    public void setSheetHandler(SheetHandler sheetHandler) {
        this.sheetHandler = sheetHandler;
    }
    //处理一个sheet
    public void processOneSheet(String filename) throws Exception {
        InputStream sheet2=null;
        OPCPackage pkg =null;
        try {
                pkg = OPCPackage.open(filename);
                XSSFReader r = new XSSFReader(pkg);
                SharedStringsTable sst = r.getSharedStringsTable();
                XMLReader parser = fetchSheetParser(sst);
                sheet2 = r.getSheet("rId1");
                InputSource sheetSource = new InputSource(sheet2);
                parser.parse(sheetSource);
                setRowContents(sheetHandler.getRowContents());
                }catch (Exception e) {
                    e.printStackTrace();
                    throw e;
                    }finally{
                        if(pkg!=null){
                            pkg.close();
                                     }
                        if(sheet2!=null){
                            sheet2.close();
                                        }
                }
    }
//处理多个sheet
    public void processAllSheets(String filename) throws Exception {
        OPCPackage pkg =null;
        InputStream sheet=null;
        try{
                pkg=OPCPackage.open(filename);
                XSSFReader r = new XSSFReader( pkg );
                SharedStringsTable sst = r.getSharedStringsTable();
                XMLReader parser = fetchSheetParser(sst);
                Iterator<InputStream> sheets = r.getSheetsData();
                while(sheets.hasNext()) {
                    System.out.println("Processing new sheet:\n");
                    sheet = sheets.next();
                    InputSource sheetSource = new InputSource(sheet);
                    parser.parse(sheetSource);
                                        }
            }catch (Exception e) {
                    e.printStackTrace();
                    throw e;
                   }finally{
                       if(pkg!=null){
                           pkg.close();
                                 }
                       if(sheet!=null){
                           sheet.close();
                                    }
                            }
    }

    public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
        XMLReader parser =
            XMLReaderFactory.createXMLReader(
                    "com.sun.org.apache.xerces.internal.parsers.SAXParser"
            );
        setSheetHandler(new SheetHandler(sst));
        ContentHandler handler = (ContentHandler) sheetHandler;
        parser.setContentHandler(handler);
        return parser;
    }

    /** 
     * See org.xml.sax.helpers.DefaultHandler javadocs 
     */
    //测试
    @Test
    public  void test ()throws Exception {
       Long time=System.currentTimeMillis();
        LargeExcelFileReadUtil example = new LargeExcelFileReadUtil();

        example.processOneSheet("C:/Users/Desktop/2018041310024112.xlsx");
        Long endtime=System.currentTimeMillis();
        LinkedHashMap<String, String>  map=example.getRowContents();
        Iterator<Entry<String, String>> it= map.entrySet().iterator();
        int count=0;
        String prePos="";
        while (it.hasNext()){
            Map.Entry<String, String> entry=(Map.Entry<String, String>)it.next();
            String pos=entry.getKey();
            if(!pos.substring(1).equals(prePos)){
                prePos=pos.substring(1);
                count++;
            }
            System.out.println(pos+";"+entry.getValue());
        }
        System.out.println("解析数据"+count+"条;耗时"+(endtime-time)/1000+"秒");
    }
}


/**
SheetHandler  类中处理从excle获取的数据,官方文档中 SheetHandler以内部类形式,为保证更新代码减少内部类class文件忘记打包,改为一般java类
*/
import java.util.LinkedHashMap;

import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;

public class SheetHandler  extends DefaultHandler{

    private SharedStringsTable sst;
    private String lastContents;
    private boolean nextIsString;
    private String  cellPosition;
    private  LinkedHashMap<String, String>rowContents=new LinkedHashMap<String, String>(); 

    public LinkedHashMap<String, String> getRowContents() {
        return rowContents;
    }

    public void setRowContents(LinkedHashMap<String, String> rowContents) {
        this.rowContents = rowContents;
    }

    public SheetHandler(SharedStringsTable sst) {
        this.sst = sst;
    }

    public void startElement(String uri, String localName, String name,
            Attributes attributes) throws SAXException {
        if(name.equals("c")) {
         //   System.out.print(attributes.getValue("r") + " - ");
            cellPosition=attributes.getValue("r");
            String cellType = attributes.getValue("t");
            if(cellType != null && cellType.equals("s")) {
                nextIsString = true;
            } else {
                nextIsString = false;
            }
        }
        // 清楚缓存内容
        lastContents = "";
    }

    public void endElement(String uri, String localName, String name)
            throws SAXException {
        if(nextIsString) {
            int idx = Integer.parseInt(lastContents);
            lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
            nextIsString = false;
        }

        if(name.equals("v")) {
//            System.out.println("lastContents:"+cellPosition+";"+lastContents);
            //数据读取结束后,将单元格坐标,内容存入map中
            if(!(cellPosition.length()==2)||(cellPosition.length()==2&&!"1".equals(cellPosition.substring(1)))){//不保存第一行数据
                rowContents.put(cellPosition, lastContents);
            }
        }
    }

    public void characters(char[] ch, int start, int length)
            throws SAXException {
        lastContents += new String(ch, start, length);
    }
}

亲测可用,20多万条数据,4秒解析完,执行结果
这里写图片描述
poi jar包如下:
这里写图片描述

2011-01-12 10:56:00 chiperfect 阅读数 8782
  • 基于SSM的POI导入导出Excel实战

    本课程将给大家分享如何基于SSM实现POI导入导出Excel,并讲解目前企业级JavaWeb应用mvc三层模式的开发流程,可让初学者或者职场萌新掌握如何基于SSM整合第三方框架并采用mvc三层开发模式实现自己的业务模块!

    1265 人正在学习 去看看 钟林森

使用poi生成excel,当遇到大数据量时,会有几个问题:

 

1. 目前office2003 excle每个sheet最大支持行数为65536,当数据超过65536行时,会报异常

2. 当列为17列左右,数据行数达到50w条左右时,会包OOM错误

Exception in thread "15124183@qtp-16902686-1" java.lang.OutOfMemoryError: Java heap space
Exception in thread "Thread-13" java.lang.OutOfMemoryError: Java heap space
        at org.apache.poi.hssf.usermodel.HSSFRow.createCell(HSSFRow.java:141)
        at org.apache.poi.hssf.usermodel.HSSFRow.createCell(HSSFRow.java:119)

 

问题1比较好解决,每次写入数据时,都获取sheet最后一行,在最后一行后继续写入数据,判断当最大行数大于65536或再小些,生成新的sheet,就能解决此问题。

具体代码片段

   int sheetLastSize = sheet.getLastRowNum();
            // 如果数据是最大写入数的整数倍(Constants.EXCEL_SHEET_DATASIZE为最大行数)
            if (sheetLastSize >= Constants.EXCEL_SHEET_DATASIZE) {
                sheet = workbook.createSheet();
                sheetLastSize = sheet.getLastRowNum();
                log.info("sheetLast Size:=========" + sheetLastSize);
                mapInfo.put("sheet", sheet);
                // 处理标题头
                processExcelTitle(dataset, workbook, sheet);
            }

 

问题2 目前没有较好的解决办法。由于每行都要生成多个cell对象,当数据量大时,java GC还没来得及回收,导致最后没有内存创建cell对象。现在正测试一种方法(只是暂时缓解,不是最好的办法),每次写入数据后,把row赋值null,定时调用System.GC();没有别的好办法,只能暂时测试下。

还是不行,仍然溢出!

 

 

 

/**
     * 传入已存在的workbook和数据源,向excel中追加数据,每个sheet大于指定行数时,创建新的sheet
     *
     * @param workbook
     *            已存在的工作薄
     * @param dataset
     *            写入数据集合
     * @param mapInfo
     *                 准备数据map
     */
    @SuppressWarnings("unchecked")
    private void dynamicExcel(HSSFWorkbook workbook, Map<String, Object> dataset, Map<Object, Object> mapInfo) {
        Map<String, String> headers = (Map<String, String>) dataset.get("1");
        Iterator<Entry<String, String>> titleIter = headers.entrySet().iterator();

        // 取得所有行的数据
        List<Map<String, String>> valueList = (List<Map<String, String>>) dataset.get("2");
        wroteRecodeSize += valueList.size();
        log.info("this times size is:------------------------------------------------------" + valueList.size());
        log.info("total wrote recode size is:------------------------------------------------" + wroteRecodeSize);
        HSSFSheet sheet = (HSSFSheet) mapInfo.get("sheet");
        for (int i = 0; i < valueList.size(); i++) {
            int sheetLastSize = sheet.getLastRowNum();
            // 如果数据是最大写入数的整数倍
            if (sheetLastSize >= Constants.EXCEL_SHEET_DATASIZE) {
                sheet = workbook.createSheet();
                sheetLastSize = sheet.getLastRowNum();
                log.info("sheetLast Size:=========" + sheetLastSize);
                mapInfo.put("sheet", sheet);
                // 处理标题头
                processExcelTitle(dataset, workbook, sheet);
            }
            // 追加行
            HSSFRow row = sheet.createRow(sheetLastSize + 1);
            sheet.setDefaultColumnWidth(25);
            // 取得一行所有记录的所有值
            int dataIndex = 0;
            Map<String, String> values = valueList.get(i);
            titleIter = headers.entrySet().iterator();
            while (titleIter.hasNext()) {
                Map.Entry<String, String> entry = (Map.Entry<String, String>) titleIter.next();
                String key = entry.getKey();
                HSSFCell cell = row.createCell(dataIndex++);
//                String v = values.get(key);
                cell.setCellValue(values.get(key));
            }
           
        }
    }

没有更多推荐了,返回首页