2012-09-06 11:58:43 kjkhi 阅读数 181
  • 基于SSM的POI导入导出Excel实战

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

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

POI读取Excel文件有两种方式,一种是使用usermodel方式读取,这种方式的优势是统一接口开发,读取.xls文件的HSSFWorkbook与读取.xlsx文件的XSSFWorkbook两个类都实现了Workbook接口。另外一种是eventusermodel方式读取,这种方式比前面一种方式读取复杂很多,并且对与2003版本和2007版本的Excel处理没有统一接口,需要了解Excel的内部文件组织原理,但是效率却比第一种方式快得多,并却能轻松读取大量数据的Excel文件,而不会把内存溢出。本文也是主要介绍使用eventusermodel方式读取2007版本的Excel文件。

eventusermodel其实是使用了XML的文件格式读取Excel的,因为Excel内部组织也是通过XML实现了(可以把后缀名改为.zip)。

xl\worksheets\sheet1.xml  - 第一个sheet的内容

 

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"><dimension ref="A1:D1"/>
<sheetViews><sheetView workbookViewId="0"><selection sqref="A1:XFD1"/></sheetView></sheetViews>
<sheetFormatPr defaultRowHeight="13.5" x14ac:dyDescent="0.15"/>
	<sheetData>
		<row r="1" spans="1:4" x14ac:dyDescent="0.15">
			<c r="A1" t="s"><v>0</v></c>
			<c r="B1" t="s"><v>1</v></c>
			<c r="C1" t="s"><v>2</v></c>
			<c r="D1" t="s"><v>15</v></c>
		</row>
	</sheetData>
<phoneticPr fontId="1" type="noConversion"/><pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/><pageSetup paperSize="0" orientation="portrait" horizontalDpi="0" verticalDpi="0" copies="0"/></worksheet>

<c />标签表示单元格,t=“s”,说明当前的单元格类型是字符串,那此时<v>0</v>,0则是在sharedStrings.xml的一个索引值。是<si />标签序号。 

 

xl\sharedStrings.xml - Excel文件中字符串的值,如其内容片段

 

<si>
	<t>col1</t><phoneticPr fontId="1" type="noConversion"/>
</si>
<si>
	<t>col2</t><phoneticPr fontId="1" type="noConversion"/>
</si>

 POI的eventusermodel也是通过这样的原理读取Excel文件的。

首先读取Excel文件,取得XSSFReader实例:

 

XSSFReader reader =  new XSSFReader(OPCPackage.open(file));
XMLReader xmlReader = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");

// sharedStrings.xml实体
SharedStringsTable table = reader.getSharedStringsTable();

xmlReader.setContentHandler(new ContentHandler()//实现该接口的一个实例);

InputStream sheet = reader.getSheet("rId"+sheetId);

InputSourcesheetSource  =  new InputSource(sheet )

xmlReader.parse(sheetSource);
 
package net.bingosoft.import4excel.common;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;

public class TestContentHandler extends DefaultHandler{

	private SharedStringsTable table;
	
	private boolean isString;
	
	private String value;
	
	private FileWriter writer;
	
	public TestContentHandler(SharedStringsTable table){
		this.table = table;
	}

	/* (non-Javadoc)
	 * @see org.xml.sax.helpers.DefaultHandler#characters(char[], int, int)
	 */
	@Override
	public void characters(char[] ch, int start, int length)
			throws SAXException {
		value = new String(ch,start,length);
	}

	/* (non-Javadoc)
	 * @see org.xml.sax.helpers.DefaultHandler#endDocument()
	 */
	@Override
	public void endDocument() throws SAXException {
		try {
			writer.flush();
			writer.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/* (non-Javadoc)
	 * @see org.xml.sax.helpers.DefaultHandler#endElement(java.lang.String, java.lang.String, java.lang.String)
	 */
	@Override
	public void endElement(String uri, String localName, String qName)
			throws SAXException {
		try {
			if(qName.equals("v")){
				if(isString) value = table.getEntryAt(Integer.valueOf(value.trim())).getT();
				writer.write(value+",");
			}
			if(qName.equals("row")){
				writer.write("\r\n");
			}
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/* (non-Javadoc)
	 * @see org.xml.sax.helpers.DefaultHandler#startDocument()
	 */
	@Override
	public void startDocument() throws SAXException {
		try {
			File file = new File("D:/test.txt");
			if(file.exists()) file.delete();
			writer = new FileWriter(file);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	/* (non-Javadoc)
	 * @see org.xml.sax.helpers.DefaultHandler#startElement(java.lang.String, java.lang.String, java.lang.String, org.xml.sax.Attributes)
	 */
	@Override
	public void startElement(String uri, String localName, String qName,
			Attributes attributes) throws SAXException {
		if(qName.equals("c")){
			String type = attributes.getValue("t");
			if(StringUtils.isNotBlank(type) && type.equals("s")){
				isString = true;
			}else{
				isString = false;
			}
		}
		value = "";
	}
	
	
}

 

2020-01-11 19:09:31 qq_31142553 阅读数 33
  • 基于SSM的POI导入导出Excel实战

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

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

昨天,产品提了一个紧急需求,让把十一月份已发货的商品数据导出来,写好SQL发给DBA执行之后,得到了三十多个100W数据的Excel文件。有一个属性是以JSON格式存在表中一个字段里面的,需要加工Excel文件将其单独取出来(如图的第四列)。

处理程序也在数据导出的过程中写好了,大概思路就是读入Excel构建Workbook对象,然后对指定列的值进行转换,最后写回原文件。想法很奈斯,结果很悲哀,OOM了。即使把Xmx和Xms调到最大,还是无济于事。

 

系统平时的导入导出不会遇到这种问题,导入的话限制了上传文件大小,导出的话应用里面设置了最大导出数量校验。直接从数据库的导出是写好SQL交给DBA处理的,他们有自己的工具。

本着遇到困难就解决的优秀品质,所以总结了一下POI中专门处理大数据量的SXSSF。

一、HSSF、XSSF与SXSSF

首先是HSSF,支持2003及以下的版本,即.xls结尾的Excel文件,最多只允许存储65536条数据。

然后是XSSF,支持2007及以上的版本,即.xlsx结尾的Excel文件,虽然单个Sheet就支持1048576条数据,但是性能不好,而且那么多的数据存在内存中也容易OOM。

最后是SXSSF,它使用了不一样的存储方式,具体就不说了,只要知道大数量用它就OK了。呃,它只支持.xlsx文件,看它名字就知道了:"S" + "XSSF",S表示SAX事件驱动模式。

二、SXSSF导出数据

这个比较简单,SXSSF也是实现了Workbook接口,所以就跟HSSF和XSSF用起来差不多,只是构建实例的方式不一样而已。

它的基本思路是:当内存中的数据够了一定行数(构造函数可以设置)之后就先刷到硬盘中。但是,你也别就真把100W数据一次性读到内存中,应该根据总数分批加载到内存(比如从数据库读需要分页一样)。

直接上代码吧

/**
 * POI 导出
 *
 * @author Zhou Huanghua
 * @date 2020/1/11 14:03
 */
public class PoiExport {

    private static final Logger LOGGER = Logger.getLogger(MethodHandles.lookup().lookupClass().getName());

    public static void main(String[] args) throws Exception {
        long begin = System.currentTimeMillis();
        // keep 100 rows in memory, exceeding rows will be flushed to disk
        try (SXSSFWorkbook wb = new SXSSFWorkbook(100);
             OutputStream os = new FileOutputStream("C:/Users/dell/Desktop/tmp/demo.xlsx")) {
            Sheet sh = wb.createSheet();
            String val = "第%s行第%s列";
            for (int rowNum = 0; rowNum < 100_0000; rowNum++) {
                Row row = sh.createRow(rowNum);
                int realRowNum = rowNum + 1;
                Cell cell1 = row.createCell(0);
                cell1.setCellValue(format(val, realRowNum, 1));
                Cell cell2 = row.createCell(1);
                cell2.setCellValue(format(val, realRowNum, 2));
                Cell cell3 = row.createCell(2);
                cell3.setCellValue(format(val, realRowNum, 3));
                Cell cell4 = row.createCell(3);
                cell4.setCellValue(format(val, realRowNum, 4));
            }
            wb.write(os);
        }
        LOGGER.info("导出100W行数据耗时(秒):" + (System.currentTimeMillis() - begin)/1000);
    }
}

测试得到一个17M的文件,耗时17秒。

三、SXSSF导入数据

这个比较复杂一些,不过使用步骤也还统一,区别就是需要实现自己的SheetContentsHandler,我管它叫内容处理器。

直接上代码

/**
 * POI 导入
 *
 * @author Zhou Huanghua
 * @date 2020/1/11 14:02
 */
public class PoiImport {

    private static final Logger LOGGER = Logger.getLogger(MethodHandles.lookup().lookupClass().getName());

    public static void main(String[] args) throws Exception {
        String filePath = "C:/Users/dell/Desktop/tmp/demo.xlsx";
        // OPCPackage.open(...)有多个重载方法,比如String path、File file、InputStream in等
        try (OPCPackage opcPackage = OPCPackage.open(filePath);) {
            // 创建XSSFReader读取StylesTable和ReadOnlySharedStringsTable
            XSSFReader xssfReader = new XSSFReader(opcPackage);
            StylesTable stylesTable = xssfReader.getStylesTable();
            ReadOnlySharedStringsTable sharedStringsTable = new ReadOnlySharedStringsTable(opcPackage);
            // 创建XMLReader,设置ContentHandler
            XMLReader xmlReader = SAXHelper.newXMLReader();
            xmlReader.setContentHandler(new XSSFSheetXMLHandler(stylesTable, sharedStringsTable, new SimpleSheetContentsHandler(), false));
            // 解析每个Sheet数据
            Iterator<InputStream> sheetsData = xssfReader.getSheetsData();
            while (sheetsData.hasNext()) {
                try (InputStream inputStream = sheetsData.next();) {
                    xmlReader.parse(new InputSource(inputStream));
                }
            }
        }
    }

    /**
     * 内容处理器
     */
    public static class SimpleSheetContentsHandler implements XSSFSheetXMLHandler.SheetContentsHandler {

        protected List<String> row;

        /**
         * A row with the (zero based) row number has started
         *
         * @param rowNum
         */
        @Override
        public void startRow(int rowNum) {
            row = new ArrayList<>();
        }

        /**
         * A row with the (zero based) row number has ended
         *
         * @param rowNum
         */
        @Override
        public void endRow(int rowNum) {
            if (row.isEmpty()) {
                return;
            }
            // 处理数据
            LOGGER.info(row.stream().collect(Collectors.joining("   ")));
        }

        /**
         * A cell, with the given formatted value (may be null),
         * and possibly a comment (may be null), was encountered
         *
         * @param cellReference
         * @param formattedValue
         * @param comment
         */
        @Override
        public void cell(String cellReference, String formattedValue, XSSFComment comment) {
            row.add(formattedValue);
        }

        /**
         * A header or footer has been encountered
         *
         * @param text
         * @param isHeader
         * @param tagName
         */
        @Override
        public void headerFooter(String text, boolean isHeader, String tagName) {
        }
    }
}

目前是解析一行数据就处理,这个看你使用场景是否能够接受,异步的话还OK。

如果你觉得数据量和你JVM的堆内存还OK的话,可以在SimpleSheetContentsHandler的构造函数传一个集合进来收集每行数据,等把数据全部解析完再统一处理。BUT,既然你用了SXSSF,那么这么做有OOM风险。

比较好的做法,还是先将每行数据逐一收集,不过需要达到一定行数之后批量处理,然后清空集合重新收集,这个还没想好怎么把代码写得优雅,就先不献丑了。

对了,我使用的maven依赖是

        <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>

demo代码地址:https://github.com/zhouhuanghua/poi-demo

2019-03-19 14:57:48 wanglizheng825034277 阅读数 2828
  • 基于SSM的POI导入导出Excel实战

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

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

xls和xlsx

  1. xls是旧版Excel格式文件,xlsx是新版Excel格式文件;而xlsx新版格式其实是一系列文件压缩包,
    如图:
    在这里插入图片描述
  2. xls是以二进制的方式存储,这种格式不易被其他软件读取使用;而xlsx采用了基于XML的ooxml开放文档标准,ooxml使用XML和ZIP技术结合进行文件存储,XML是一个基于文本的格式,而且ZIP容器支持内容的压缩,所以其一大优势是可以大大减小文件的尺寸;
  3. 使用POI来读写Excel文件有两种方式,一种用户模式(UserModel),读取时消耗大量内存,造成OOM问题;一种事件模式(SAX模式),仅仅关注文件内部数据,内存消耗很低;
  4. 导出文件同样如此,使用Workbook普通导出,数据量小的时候可以正常使用,但时间等待仍然很长,这时推荐使用POI提供的SXXFWorkbook处理,其使用时间窗口原理(具体可以查询)限制访问,刷出内存,降低内存消耗,提升效率。
    另外还需要注意,根据你使用的功能,仍然可能消耗大量内存,例如合并区域,超链接,注释……,这些内容只存储在内存中。
    在这里插入图片描述

导入数据(大量)

大量数据导入在网络上搜寻到的相关代码大部分通过集成POI原生的DefaultHandler重写其startElement, endElement, characters方法进行相关的解析,而POI已经将相关逻辑封装在XSSFSheetXMLHandler,只要实现暴露的接口SheetContentsHandler即可。
使用SheetContentsHandler的例子可以参考官方的XLSX2CVS
本例实现该接口:

package cn.skio.venus.api;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.util.SAXHelper;
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.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;

import javax.xml.parsers.ParserConfigurationException;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

/**
 * @autor jasmine
 */
public class ExcelEventParser {
    private String fileName;
    private SimpleSheetContentsHandler handler;
    // 测试使用对比使用SAX和UserModel模式选择(实际使用不需要)
    private Integer saxInterupt;
	private void setHandler(SimpleSheetContentsHandler handler) {
		this.handler = handler;
	}

	// 放置读取数据
    protected List<List<String>> table = new ArrayList<>();

    public ExcelEventParser(String filename, Integer saxInterupt){
        this.fileName = filename;
        this.saxInterupt = saxInterupt;
    }

    public List<List<String>> parse() {
        OPCPackage opcPackage = null;
        InputStream inputStream = null;

        try {
            FileInputStream fileStream = new FileInputStream(fileName);
            opcPackage = OPCPackage.open(fileStream);
            XSSFReader xssfReader = new XSSFReader(opcPackage);

            StylesTable styles = xssfReader.getStylesTable();
            ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(opcPackage);
            inputStream = xssfReader.getSheetsData().next();

            processSheet(styles, strings, inputStream);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (opcPackage != null) {
                try {
                    opcPackage.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return table;
    }

	// 确定XMLReader解析器,使用SAX模式解析xml文件
    private void processSheet(StylesTable styles, ReadOnlySharedStringsTable strings, InputStream sheetInputStream) throws SAXException, ParserConfigurationException, IOException {
        XMLReader sheetParser = SAXHelper.newXMLReader();

        if (handler == null) {
            setHandler(new SimpleSheetContentsHandler());
        }
        sheetParser.setContentHandler(new XSSFSheetXMLHandler(styles, strings, handler, false));

        try {
            sheetParser.parse(new InputSource(sheetInputStream));
        } catch (RuntimeException e) {
            System.out.println("---> 遇到空行读取文件结束!");
        }
    }

	// 实现SheetContentsHandler
    public class SimpleSheetContentsHandler implements SheetContentsHandler{
        protected List<String> row;
        @Override
        public void startRow(int rowNum) {
            row = new LinkedList<>();
        }

        @Override
        public void endRow(int rowNum) {
        	// 判断是否使用异常作为文件读取结束(有些Excel文件格式特殊,导致很多空行,浪费内存)
            if (saxInterupt == 1) {
                if (row.isEmpty()) {
                    throw new RuntimeException("Excel文件读取完毕");
                }
            }
			// 添加数据到list集合
            table.add(row);
        }

        /**
         * 所有单元格数据转换为string类型,需要自己做数据类型处理
         * @param cellReference 单元格索引
         * @param formattedValue 单元格内容(全部被POI格式化为字符串)
         * @param comment
         */
        @Override
        public void cell(String cellReference, String formattedValue, XSSFComment comment) {
            row.add(formattedValue);
        }

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

经测试结果,发现使用SAX模式(抛弃了样式等,只关注数据)仅仅消耗很少内存,效率高;而普通Workbook读取数据(测试文件为5.2MB的有大量空行文件)内存消耗 > 1GB(此时线上系统OOM概率非常大);
在这里插入图片描述

导出数据(大量)

导出数据的话瓶颈主要在于数据写入Excel文件,代码(同样的74273条数据导出)如下:

	// 使用SXSSFwrokbook,大量数据处理快速
	@GetMapping("/outExcel")
    public void outPutExcel(HttpServletResponse response) throws Exception {
        // 每次写100行数据,就刷新数据出缓存
        SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
        Sheet sh = wb.createSheet();
        List<Tmp> tmps = tmpDao.findAll();
        log.info("---> 数据量:{}", tmps.size());

        for(int rowNum = 0; rowNum < tmps.size(); rowNum++){
            Row row = sh.createRow(rowNum);
            Tmp tmp = tmps.get(rowNum);
            Cell cell1 = row.createCell(0);
            cell1.setCellValue(tmp.getSource());

            Cell cell2 = row.createCell(1);
            cell2.setCellValue(tmp.getName());
            Cell cell3 = row.createCell(2);
            cell3.setCellValue(tmp.getPhone());
            Cell cell4 = row.createCell(3);
            cell4.setCellValue(tmp.getCity());
        }

        String fileName = "sxssf.xlsx";
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
        wb.write(response.getOutputStream());
        wb.close();
    }
	// XSSFWorkbook, 效率低下
	@GetMapping("/outExcel2")
    public void outPutExcel2(HttpServletResponse response) throws Exception {
        XSSFWorkbook wb = new XSSFWorkbook();
        Sheet sh = wb.createSheet();
        List<Tmp> tmps = tmpDao.findAll();
        log.info("---> 数据量:{}", tmps.size());

        for(int rowNum = 0; rowNum < tmps.size(); rowNum++){
            Row row = sh.createRow(rowNum);
            Tmp tmp = tmps.get(rowNum);
            Cell cell1 = row.createCell(0);
            cell1.setCellValue(tmp.getSource());

            Cell cell2 = row.createCell(1);
            cell2.setCellValue(tmp.getName());
            Cell cell3 = row.createCell(2);
            cell3.setCellValue(tmp.getPhone());
            Cell cell4 = row.createCell(3);
            cell4.setCellValue(tmp.getCity());
        }

        String fileName = "sxssf.xlsx";
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "GBK"));
        wb.write(response.getOutputStream());
        wb.close();
    }

效率对比:

对象 耗时
SXSSFWorkbook 在这里插入图片描述
XSSFWorkbook 在这里插入图片描述

CPU和内存消耗对比:
在这里插入图片描述

总结

  1. 大文件读取使用SAX
  2. 大文件写入使用SXSSFWorkbook

参考链接:
[1]: https://blog.csdn.net/Holmofy/article/details/82532311
[2]: https://blog.csdn.net/daiyutage/article/details/53010491
[3]: https://www.cnblogs.com/yfrs/p/5689347.html
[4]: easyexcel

2019-04-09 15:55:24 yuwy691 阅读数 215
  • 基于SSM的POI导入导出Excel实战

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

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

实际项目中经常会遇到excel导入、导出操作,数据量过大会导致内存溢出,自己封装了一个导入导出的工具类,包括用户模式、事件驱动模式两种导入导出,数据量较小是可以使用用户模式,数据量比较大使用事件驱动模式,实测事件驱动模式下导入导出百万数据量毫无压力。

下面介绍下事件驱动模式导入导出的用法:

一、事件驱动模式导入

1、定义导入实体类,对应excel表格每一行数据,需要与表格字段顺序一致(使用lombok插件,省去getter、setter方法)。

@Data
public class ImportVo {
  private String name;

  private String sex;

  private String email;

  private String age;
}

2、导入方法调用,解析本地文件,同时映射为定义的实体类(文件中单元格类型设置为文本格式)。

public void importExcel() throws Exception {
    ExcelHandler handler = new ExcelHandlerImpl();

    String path = "D:/test.xlsx";
    File file = new File(path);
    List<ImportVo> list = handler.saxImport(file, "sheet", ImportVo.class, true);

    List<User> userList = new ArrayList<>();
    Iterator<ImportVo> iterator = list.iterator();
    while (iterator.hasNext()) {
      ImportVo importVo = iterator.next();
      User user = new User();
      user.setName(importVo.getName());
      user.setSex(Integer.parseInt(importVo.getSex()));
      user.setEmail(importVo.getEmail());
      user.setAge(Double.parseDouble(importVo.getAge()));
      user.setCreateTime(new Date());

      userList.add(user);

      if (userList.size() == 10000) {
        userMapper.batchInsert(userList);

        userList.clear();
      }
    }
  }

二、事件驱动模式导出

1、定义导出实体类,@Header为导出列头显示的内容(使用lombok插件,省去getter、setter方法)。

@Data
public class ExportVo {
  @Header("姓名")
  private String name;

  @Header("性别")
  private int sex;

  @Header("邮箱")
  private String email;

  @Header("年龄")
  private double age;
}

2、分页填充数据,每次填充10000条数据,添加完所有数据后,写入本地文件。

public void export() throws Exception {
    // 导出到本地磁盘
    String path = "D:/test.xlsx";

    Workbook wb = new SXSSFWorkbook(1000);
    Sheet sheet = wb.createSheet("export_sheet");
    ExcelHandler handler = new ExcelHandlerImpl();

    List<ExportVo> dtoList = new ArrayList<>();

    PageInfo<User> pageInfo = findAll(1, 10000);

    fillSheet(sheet, handler, dtoList, pageInfo);

    while (pageInfo.isHasNextPage()) {
      pageInfo = findAll(pageInfo.getNextPage(), 10000);

      fillSheet(sheet, handler, dtoList, pageInfo);
    }

    File file = new File(path);
    OutputStream os = new FileOutputStream(file);
    wb.write(os);
  }


private void fillSheet(
      Sheet sheet, ExcelHandler handler, List<ExportVo> dtoList, PageInfo<User> pageInfo)
      throws Exception {
    dtoList.clear();
    for (User user : pageInfo.getList()) {
      ExportVo vo = new ExportVo();
      vo.setName(user.getName());
      vo.setSex(user.getSex());
      vo.setEmail(user.getEmail());
      vo.setAge(user.getAge());

      dtoList.add(vo);
    }
    handler.fillSheet(sheet, true, dtoList);
  }

3、不分页导出

public void exportNoPage() throws Exception {
    // 导出到本地磁盘
    String path = "D:/test.xlsx";

    ExcelHandler handler = new ExcelHandlerImpl();

    List<ExportVo> dtoList = new ArrayList<>();

    PageInfo<User> pageInfo = findAll(1, 1000000);
    for (User user:pageInfo.getList()) {
      ExportVo vo = new ExportVo();
      vo.setName(user.getName());
      vo.setSex(user.getSex());
      vo.setEmail(user.getEmail());
      vo.setAge(user.getAge());

      dtoList.add(vo);
    }

    Workbook wb = handler.exportXLSX("no_page",true,true,dtoList);

    File file = new File(path);
    OutputStream os = new FileOutputStream(file);
    wb.write(os);
  }

总结:这个是以POI为基础开发的一个工具集,难点在于事件驱动模式对Excel的解析,Excel2007以后都是用XML格式存储,所以解析就是对XML文件的处理,要了解XML的存储结构然后对不同的标签做不同的处理。

源码地址:https://github.com/yuwy691/PoiExcelHandler.git

如何查看Excel2007 XML存储结构:https://blog.csdn.net/yuwy691/article/details/86161227

2018-09-24 21:17:41 lichunericli 阅读数 3854
  • 基于SSM的POI导入导出Excel实战

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

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

  1. 大数据量的导入

当Excel中的数据量超过10万行时,在用POI读取文件流时很容易引起失败,需要引入xlsx-streamer来进行资源的打开,剩下的处理同POI处理上百行数据量类似:filePath=>FileInputStream=>Workbook=>Sheet=>Row=>Cell=>Java反射获取值。

 <dependency>
       <groupId>com.monitorjbl</groupId>
       <artifactId>xlsx-streamer</artifactId>
       <version>1.2.1</version>
</dependency>  

使用StreamingReader来进行对Workbook的获取。需要注意主要针对超过10万行要解析的Excel的Workbook获取,缓存到内存中的行数默认是10行,读取资源时缓存到内存的字节大小默认是1024,资源必须打开,File或者InputStream都可以,但是只能打开XLSX格式的文件。
public static Workbook obtainWorkbookByStream(String filePath) throws Exception {
      // StreamingReader用于读取Excel的内容,不能写入,不能随机读取Excel的内容
      FileInputStream in = new FileInputStream(filePath);
      Workbook workbook = StreamingReader.builder().rowCacheSize(100).bufferSize(4096).open(in);
      return workbook;
}

2. 大数据量的导出

对于大数据量的导出通常采用分而治之的思想,将大数据量分批次的导出到多个Excel文件或者单个Excel文件的多个sheet,也可导出到多个Excel文件后合并从单独的文件,其实现方式可参考:https://github.com/chunericli/xpt-excel-extension

总结:大数据量的处理需要注意对内存使用的影响和对业务的影响。除非必要,要不然最好使用异步的方式进行处理,即首先保存文件元数据信息,然后分批次保存数据和分批次读取数据。

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