poi导入大数据量excel

2020-01-11 19:09:31 qq_31142553 阅读数 334

昨天,产品提了一个紧急需求,让把十一月份已发货的商品数据导出来,写好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-01-07 17:04:00 weixin_42614447 阅读数 5221

POI 导入导出功能,引用jar包是关键,maven依赖支持3.17版.

介绍:

首先,理解一下一个Excel的文件的组织形式,一个Excel文件对应于一个workbook(HSSFWorkbook),一个workbook可以有多个sheet(页/表)(HSSFSheet)组成,一个sheet是由多个row(行)(HSSFRow)组成,一个row是由多个cell(单元格)(HSSFCell)组成。
1、用HSSFWorkbook打开或者创建“Excel文件对象
2、用HSSFWorkbook对象返回或者创建Sheet对象
3、用Sheet对象返回行对象,用行对象得到Cell对象
4、对Cell对象读写

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>

mvc 依赖:

poi-3.7-20101029.jar
poi-3.9.jar
poi-ooxml-3.9.jar
poi-ooxml-schemas-3.9.jar

ExcelUtil.java (导入导出工能封装)

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFHyperlink;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by cdw on 2018/04/19.
 *
 * Apache POI操作Excel对象 HSSF:操作Excel 2007之前版本(.xls)格式,生成的EXCEL不经过压缩直接导出
 * XSSF:操作Excel 2007及之后版本(.xlsx)格式,内存占用高于HSSF SXSSF:从POI3.8
 * beta3开始支持,基于XSSF,低内存占用,专门处理大数据量(建议)。
 *
 * 注意: 值得注意的是SXSSFWorkbook只能写(导出)不能读(导入)
 *
 * 说明: .xls格式的excel(最大行数65536行,最大列数256列) .xlsx格式的excel(最大行数1048576行,最大列数16384列)
 * 这里引用的是阿里的json包,也可以自行转换成net.sf.json.JSONArray net.sf.json.JSONObject
 */
public class ExcelUtil {

	private final static String Excel_2003 = ".xls"; // 2003 版本的excel
	private final static String Excel_2007 = ".xlsx"; // 2007 版本的excel

	public static final String DEFAULT_DATE_PATTERN = "yyyy-MM-dd HH:mm:ss"; // 默认日期格式(类型为Date即可转换)
	public static final int DEFAULT_COLUMN_WIDTH = 17; // 默认列宽

	/**
	 * 导入Excel
	 *
	 * @param file
	 *            输入文件流
	 */
	public static List<List<Object>> importExcel(@RequestParam(value = "file", required = false) MultipartFile file)
            throws Exception {
		String fileName = file.getOriginalFilename();
		String xls = fileName.substring(fileName.lastIndexOf('.'));
		if (Excel_2003.equals(xls) || Excel_2007.equals(xls)) {
			return ExcelUtil.getImportExcel(file);
		} else {
			// 导入格式不正确
			System.out.println("导入格式不正确:导入失败!");
		}
		return null;
	}

	/**
	 * 导出Excel
	 *
	 * @param titleList
	 *            表格头信息集合
	 * @param dataArray
	 *            数据数组
	 * @param os
	 *            文件输出流
	 */
	public static void exportExcel(ArrayList<LinkedHashMap> titleList, JSONArray dataArray, OutputStream os)
			throws Exception {
		ExcelUtil.getExportExcel(titleList, dataArray, os);
	}

	/**
	 * 导入Excel
	 *
	 * @param file
	 *            导入文件流对象
	 */
	private static List<List<Object>> getImportExcel(MultipartFile file) throws Exception {
		ImportExcelUtil util = new ImportExcelUtil();
		String fileName = file.getOriginalFilename();
		InputStream inputStream = file.getInputStream();
		// 将导入的Excel数据转换成list集合
		List<List<Object>> excelLists = util.getBankListByExcel(inputStream, fileName);
		// 获取工作模板行数据对象
//        HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(inputStream));
        // 或
//        Workbook workbook = util.getWorkbook(inputStream, fileName);
//
//		for (int i = 0; i < excelLists.size(); i++) { // 循环行
//			List<Object> list = excelLists.get(i); // 获取行级列集合
//			for (int j = 0; j < list.size(); j++) {
//				System.out.println("获取" + i + "行级" + j + "列的值:" + list.get(j));
//			}
//		}
//
//		for (List<Object> excelList : excelLists) { // 循环行
//			for (Object obj : excelList) { // 循环列
//				// 获取行级列的值
//			}
//		}
//
//        for (Sheet rows : workbook) { // 循环行
//            for (Row row : rows) { // 循环列
//                // 获取行级列的值
//            }
//        }
		return excelLists;
	}

	/**
	 * 导出Excel
	 *
	 * @param titleList
	 *            表格头信息集合
	 * @param dataArray
	 *            数据数组
	 * @param os
	 *            文件输出流
	 */
	private static void getExportExcel(ArrayList<LinkedHashMap> titleList, JSONArray dataArray, OutputStream os)
			throws Exception {
		String datePattern = DEFAULT_DATE_PATTERN;
		int minBytes = DEFAULT_COLUMN_WIDTH;

		/**
		 * 声明一个工作薄
		 */
		SXSSFWorkbook workbook = new SXSSFWorkbook(1000);// 大于1000行时会把之前的行写入硬盘
		workbook.setCompressTempFiles(true);

		// 表头1样式
		CellStyle title1Style = workbook.createCellStyle();
		title1Style.setAlignment(HorizontalAlignment.CENTER);// 水平居中
		title1Style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
		Font titleFont = workbook.createFont();// 字体
		titleFont.setFontHeightInPoints((short) 20);
		titleFont.setBold(true);
		titleFont.setFontHeight((short) 700);
		title1Style.setFont(titleFont);

		// 表头2样式
		CellStyle title2Style = workbook.createCellStyle();
		title2Style.setAlignment(HorizontalAlignment.CENTER);
		title2Style.setVerticalAlignment(VerticalAlignment.CENTER);
		title2Style.setBorderTop(BorderStyle.THIN);// 上边框
		title2Style.setBorderRight(BorderStyle.THIN);// 右
		title2Style.setBorderBottom(BorderStyle.THIN);// 下
		title2Style.setBorderLeft(BorderStyle.THIN);// 左
		Font title2Font = workbook.createFont();
		title2Font.setUnderline((byte) 1);
		title2Font.setColor(HSSFColor.BLUE.index);
		title2Style.setFont(title2Font);

		// head样式
		CellStyle headerStyle = workbook.createCellStyle();
		headerStyle.setAlignment(HorizontalAlignment.CENTER);
		headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		headerStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);// 设置颜色
		headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 前景色纯色填充
		headerStyle.setBorderTop(BorderStyle.THIN);
		headerStyle.setBorderRight(BorderStyle.THIN);
		headerStyle.setBorderBottom(BorderStyle.THIN);
		headerStyle.setBorderLeft(BorderStyle.THIN);
		Font headerFont = workbook.createFont();
		headerFont.setFontHeightInPoints((short) 12);
		headerFont.setBold(true); // 是否加粗
		headerFont.setFontHeight((short) 500); // 字体大小
		headerStyle.setFont(headerFont);

		// 单元格样式
		CellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		cellStyle.setBorderTop(BorderStyle.THIN);
		cellStyle.setBorderRight(BorderStyle.THIN);
		cellStyle.setBorderBottom(BorderStyle.THIN);
		cellStyle.setBorderLeft(BorderStyle.THIN);
		Font cellFont = workbook.createFont();
		cellFont.setBold(false); // 是否加粗
		cellFont.setFontHeight((short) 300); // 字体大小
		cellStyle.setFont(cellFont);

		String title1 = (String) titleList.get(0).get("title1");
		String title2 = (String) titleList.get(0).get("title2");
		LinkedHashMap<String, String> headMap = titleList.get(1);

		/**
		 * 生成一个(带名称)表格
		 */
		SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet(title1);
		sheet.createFreezePane(0, 3, 0, 3);// (单独)冻结前三行

		/**
		 * 生成head相关信息+设置每列宽度
		 */
		int[] colWidthArr = new int[headMap.size()];// 列宽数组
		String[] headKeyArr = new String[headMap.size()];// headKey数组
		String[] headValArr = new String[headMap.size()];// headVal数组
		int i = 0;
		for (Map.Entry<String, String> entry : headMap.entrySet()) {
			headKeyArr[i] = entry.getKey();
			headValArr[i] = entry.getValue();

			int bytes = headKeyArr[i].getBytes().length;
			colWidthArr[i] = bytes < minBytes ? minBytes : bytes;
			sheet.setColumnWidth(i, colWidthArr[i] * 256);// 设置列宽
			i++;
		}

		/**
		 * 遍历数据集合,产生Excel行数据,除去 title + head 数据起始行为0,赋值为3(即第四行起)
		 */
		int rowIndex = 0;
		for (Object obj : dataArray) {
			// 生成title+head信息
			if (rowIndex == 0) {
				SXSSFRow title1Row = (SXSSFRow) sheet.createRow(0);// title1行
				title1Row.createCell(0).setCellValue(title1);
				title1Row.getCell(0).setCellStyle(title1Style);
				sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));// 合并单元格

				SXSSFRow title2Row = (SXSSFRow) sheet.createRow(1);// title2行
				title2Row.createCell(0).setCellValue(title2);

				CreationHelper createHelper = workbook.getCreationHelper();
				XSSFHyperlink hyperLink = (XSSFHyperlink) createHelper.createHyperlink(HyperlinkType.URL);
				hyperLink.setAddress(title2);
				title2Row.getCell(0).setHyperlink(hyperLink);// 添加超链接

				title2Row.getCell(0).setCellStyle(title2Style);
				sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, headMap.size() - 1));// 合并单元格

				SXSSFRow headerRow = (SXSSFRow) sheet.createRow(2);// head行
				for (int j = 0; j < headValArr.length; j++) {
					headerRow.createCell(j).setCellValue(headValArr[j]);
					headerRow.getCell(j).setCellStyle(headerStyle);
				}
				rowIndex = 3;
			}

			JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
			// 生成数据
			SXSSFRow dataRow = (SXSSFRow) sheet.createRow(rowIndex);// 创建行
			for (int k = 0; k < headKeyArr.length; k++) {
				SXSSFCell cell = (SXSSFCell) dataRow.createCell(k);// 创建单元格
				Object o = jo.get(headKeyArr[k]);
				String cellValue = "";

				if (o == null) {
					cellValue = "";
				} else if (o instanceof Date) {
					cellValue = new SimpleDateFormat(datePattern).format(o);
				} else if (o instanceof Float || o instanceof Double) {
					cellValue = new BigDecimal(o.toString()).setScale(2, BigDecimal.ROUND_HALF_UP).toString();
				} else {
					cellValue = o.toString();
				}

				cell.setCellValue(cellValue);
				cell.setCellStyle(cellStyle);
			}
			rowIndex++;
		}

//      // 另外一种导出方式
//		HSSFWorkbook workbook = new HSSFWorkbook();
//		HSSFSheet sheet = workbook.createSheet("测试表");
//		HSSFRow titleRow = sheet.createRow(0);
//		sheet.setColumnWidth(titleRow.createCell(0).getColumnIndex(), 256 * 20);
//		titleRow.createCell(0).setCellValue("名称");
//		sheet.setColumnWidth(titleRow.createCell(1).getColumnIndex(), 256 * 20);
//		titleRow.createCell(1).setCellValue("状态(0-已生成,1-待生成)");
//		// 设置应用类型,以及编码
//		response.setContentType("application/msexcel;charset=utf-8");
//		response.setHeader("Content-Disposition", "filename=" + new String("测试表.xls".getBytes("gb2312"), "iso8859-1"));
//		workbook.write(output);
//		output.flush();
//		output.close();

		try {
			workbook.write(os);
			os.flush();// 刷新此输出流并强制将所有缓冲的输出字节写出
			os.close();// 关闭流
			workbook.dispose();// 释放workbook所占用的所有windows资源
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

}

ImportExcelUtil.java (Excel数据转换类)

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * Created by cdw on 2018/04/19.
 *
 * 转换类
 *
 * 说明: .xls格式的excel(最大行数65536行,最大列数256列) .xlsx格式的excel(最大行数1048576行,最大列数16384列)
 */
public class ImportExcelUtil {
	
	private final static String Excel_2003 = ".xls"; //2003 版本的excel
	private final static String Excel_2007 = ".xlsx"; //2007 版本的excel

	/**
	 * @param in
	 * @param fileName
	 *
	 * @return
	 */
	public List<List<Object>> getBankListByExcel(InputStream in, String fileName) throws Exception {
		List<List<Object>> list = null;

		//创建Excel工作簿
		Workbook work = this.getWorkbook(in, fileName);
		if (work == null) {
			throw new Exception("创建Excel工作簿为空!");
		}
		Sheet sheet = null;
		Row row = null;
		Cell cell = null;
		list = new ArrayList<List<Object>>();
		//遍历Excel中的所有sheet
		for (int i = 0; i < work.getNumberOfSheets(); i++) {
			sheet = work.getSheetAt(i);
			if (sheet == null) {
				continue;
			}
			//遍历当前sheet中的所有行
			//int totalRow = sheet.getPhysicalNumberOfRows();//如果excel有格式,这种方式取值不准确
			int totalRow = sheet.getPhysicalNumberOfRows();
			for (int j = sheet.getFirstRowNum(); j < totalRow; j++) {
				row = sheet.getRow(j);
				if (!isRowEmpty(row)) {
					//if(row != null && !"".equals(row)) {
					//获取第一个单元格的数据是否存在
					Cell fristCell = row.getCell(0);
					if (fristCell != null) {
						//遍历所有的列
						List<Object> li = new ArrayList<Object>();
						//int totalColum = row.getLastCellNum();
						for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
							cell = row.getCell(y);
							String callCal = this.getCellValue(cell) + "";
							li.add(callCal);
						}
						list.add(li);
					}

				} else if (isRowEmpty(row)) {
					continue;
				}

			}
		}
		in.close();
		return list;
	}

	/**
	 * 判断行是否为空
	 *
	 * @param row
	 *
	 * @return
	 */
	public static boolean isRowEmpty(Row row) {
		for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
			Cell cell = row.getCell(c);
			if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK)
				return false;
		}
		return true;
	}

	/**
	 * 描述:根据文件后缀,自动适应上传文件的版本
	 *
	 * @param inStr,fileName
	 *
	 * @return
	 *
	 * @throws Exception
	 */
	public Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
		Workbook work = null;
		String fileType = fileName.substring(fileName.lastIndexOf("."));
		if (Excel_2003.equals(fileType)) {
			work = new HSSFWorkbook(inStr);//2003 版本的excel
		} else if (Excel_2007.equals(fileType)) {
			work = new XSSFWorkbook(inStr);//2007 版本的excel
		} else {
			throw new Exception("解析文件格式有误!");
		}
		return work;
	}

	/**
	 * 描述:对表格中数值进行格式化
	 *
	 * @param cell
	 *
	 * @return
	 */
	public Object getCellValue(Cell cell) {
		/*Object value = null;
		DecimalFormat df1 = new DecimalFormat("0.00");//格式化number,string字符
		SimpleDateFormat sdf = new  SimpleDateFormat("yyy-MM-dd");//日期格式化
		DecimalFormat df2 = new DecimalFormat("0.00");//格式化数字
		if(cell !=null && !"".equals(cell)) {
			switch (cell.getCellType()) {
			case Cell.CELL_TYPE_STRING:
				value = cell.getRichStringCellValue().getString();
				break;
			case Cell.CELL_TYPE_NUMERIC:
				if("General".equals(cell.getCellStyle().getDataFormatString())) {
					value = df1.format(cell.getNumericCellValue());
				}else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
					value = sdf.format(cell.getDateCellValue());
				}else if(HSSFDateUtil.isCellDateFormatted(cell)){
					Date date = cell.getDateCellValue();
					value = sdf.format(date);				
				}
				else {
					value = df2.format(cell.getNumericCellValue());
				}
				break;
			case Cell.CELL_TYPE_BOOLEAN:
				value = cell.getBooleanCellValue();
				break;
			case Cell.CELL_TYPE_BLANK:
				value = "";
				break;
			default:
				break;
			}
		}		
		return value;*/
		String result = new String();  
        switch (cell.getCellType()) {  
        case HSSFCell.CELL_TYPE_FORMULA:  //Excel公式
            try {  
            	result = String.valueOf(cell.getNumericCellValue());  
            } catch (IllegalStateException e) {  
            	result = String.valueOf(cell.getRichStringCellValue());
            }  
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:// 数字类型  
            if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
                SimpleDateFormat sdf;
                if (cell.getCellStyle().getDataFormat() == HSSFDataFormat
                        .getBuiltinFormat("h:mm")) {  
                    sdf = new SimpleDateFormat("HH:mm");  
                } else {// 日期  
                    sdf = new SimpleDateFormat("yyyy-MM-dd");  
                }  
                Date date = cell.getDateCellValue();  
                result = sdf.format(date);  
            } else if (cell.getCellStyle().getDataFormat() == 58) {  
                // 处理自定义日期格式:m月d日(通过判断单元格的格式id解决,id的值是58)  
                SimpleDateFormat sdf = new SimpleDateFormat("M月d日");  
                double value = cell.getNumericCellValue();  
                Date date = org.apache.poi.ss.usermodel.DateUtil
                        .getJavaDate(value);  
                result = sdf.format(date);  
            } else {  
                double value = cell.getNumericCellValue();  
                CellStyle style = cell.getCellStyle();
                DecimalFormat format = new DecimalFormat();
                String temp = style.getDataFormatString();  
                // 单元格设置成常规  
                if (temp.equals("General")) {  
                    format.applyPattern("#.##");  
                }  
                result = format.format(value);  
            }  
            break;  
        case HSSFCell.CELL_TYPE_STRING:// String类型  
            result = cell.getRichStringCellValue().toString();  
            break;  
        case HSSFCell.CELL_TYPE_BLANK:  
            result = "";  
        default:  
            result = "";  
            break;  
        }  
        return result;  
	}
	
	public String getFormat(String str) {
		if(str.equals("null")) {
			str="";
			return str;
		}else{
			return str;
		}	
	}
	public Integer getFormats(Integer str) {
		if(str==null) {
			str=0;
			return str;
		}else{
			return str;
		}	
	}

	/**
	 * 获取字符串中的数字订单号、数字金额等,如从"USD 374.69"中获取到374.69、从“交易单号:123456789”获取到123456789
	 *
	 * @return
	 */
	public static String getFormatNumber(String str){
		str = str.trim();
		 Pattern p = Pattern.compile("[0-9]");
		 int indexNum = 0;
		 int lenght = str.length();
		 String num = "";
		 for(int i=0;i<lenght;i++){
			num += str.charAt(i);
		 	Matcher m = p.matcher(num);
		 	if(m.find()){
		 		indexNum = i;
		 		break;
		 	}
		  }
		 String formatNumber = str.substring(indexNum,lenght);
		 return formatNumber;
	}
}

导出功能引用方法:

// 导出
@ResponseBody
@RequestMapping(value = "/excelOut", method = RequestMethod.GET)
public String excelOut(HttpServletRequest request, HttpServletResponse response) {
    try {
        // 获得输出流
        OutputStream output = response.getOutputStream();

        ArrayList<LinkedHashMap> titleList = new ArrayList<LinkedHashMap>();
        LinkedHashMap<String, String> titleMap = new LinkedHashMap<String, String>();
        // title1设置标题,key固定
        titleMap.put("title1", "测试导出表");
        titleMap.put("title2", "测试导出表链接");

        LinkedHashMap<String, String> headMap = new LinkedHashMap<String, String>();
        headMap.put("ranking", "序号");
        headMap.put("posterName", "名称");
        headMap.put("status", "状态");
        titleList.add(titleMap);
        titleList.add(headMap);

        // 数据集合,下面的字段名必须和上面的map对象key或者数据实体类参数保持一致
        List<JSONObject> objects = new ArrayList<JSONObject>();
        for (int i = 0; i < 20; i++) {
            JSONObject result = new JSONObject();
            result.put("ranking", "value" + i);
            result.put("posterName", "value" + i);
            result.put("status", "value" + i);
            objects.add(result);
        }
        JSONArray objectsList = JSONArray.parseArray(objects.toString());
        // 设置应用类型,以及编码
        response.setContentType("application/msexcel;charset=utf-8");
        response.setHeader("Content-Disposition",
                "filename=" + new String("测试导出表.xlsx/测试导出表.xls".getBytes("gb2312"), "iso8859-1"));
        ExcelUtil.exportExcel(titleList, objectsList, output);
    } catch (Exception e) {
        e.printStackTrace();
    }
    return "success";
}

导入功能引用方法:

// 导入
@ResponseBody
@RequestMapping(value = "/excelIn", method = RequestMethod.POST)
public String excelIn(@RequestParam(value = "file", required = false) MultipartFile file,
                      HttpServletResponse response) {
    try {
        List<List<Object>> lists = ExcelUtil.importExcel(file);
        System.out.println("导入结果:" + lists.toString());
    } catch (Exception e) {
        e.printStackTrace();
    }
    return "success";
}

导入功能前台引用文件:

文件引用
jquery.min.js
jquery.form.js

导入功能前台处理方法:

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<!DOCTYPE html>
<head>
    <title>导入导出-导入页面</title>
    <script src="${pageContext.request.contextPath}/js/jquery.min.js?v=2.1.4"></script>
    <script src="${pageContext.request.contextPath}/js/jquery.form.js?v=2.1.4"></script>
    <script src="${pageContext.request.contextPath}/js/bootstrap.min.js?v=3.3.6"></script>
</head>
<body>
<!--导入-->
<form action="/user/excelIn" enctype="multipart/form-data" method="post" id="fileForm" name="fileForm">
    <input type="file" id="file" name="file" accept="application/vnd.ms-excel" onchange="chooseFile(this.value)">
    <input type="button" value="选择文件" class="filebtn" id="filebtn"/>
    <input type="text" id="filetxt" class="filetxt" readonly>
    <input type="submit" value="上传Excel" id="importbtn" class="importbtn"/>
</form>
<script>
    function exportExcel() {
        // 导出接口地址
        window.location.href = "http://127.0.0.1:8081/user/excelIn";
    }

    //触发file change事件
    function chooseFile(path) {
        if (path) {
            $('#filetxt').val(path);
        } else {
            $('#filetxt').attr('readonly', true);
        }
    }

    $(function () {
        $("#fileForm").ajaxForm({
            beforeSubmit: function () {
                // 表单数据提交之前的操作
            },
            success: function (obj) {
                console.log("导入结果:" + obj);
            },
            error: function (msg) {
                console.log("导入错误:" + msg);
            }
        });
    });
</script>
</body>
</html>

示例:

导入结果:[[测试导出表], [测试导出表链接], [序号, 名称, 状态], [value0, value0, value0], [value1, value1, value1], [value2, value2, value2], [value3, value3, value3], [value4, value4, value4], [value5, value5, value5], [value6, value6, value6], [value7, value7, value7], [value8, value8, value8], [value9, value9, value9], [value10, value10, value10], [value11, value11, value11], [value12, value12, value12], [value13, value13, value13], [value14, value14, value14], [value15, value15, value15], [value16, value16, value16], [value17, value17, value17], [value18, value18, value18], [value19, value19, value19]]

导出结果:
导出结果展示

以上就是完整的导入导出功能,支持大数据量快速导出,导入.

转载请注明出处!

2019-04-09 15:55:24 yuwy691 阅读数 629

实际项目中经常会遇到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 阅读数 5053

  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

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

2018-05-22 00:33:37 sinat_30314715 阅读数 15043

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();