基于POI的SXSSFWorkbook海量数据导出

背景

在导出Excel数据时，HSSFWorkbook和XSSFWorkbook的Excel Sheet导出条数上限(<=2003版)是65535行、256列,(>=2007版)是1048576行,16384列,如果数据量超过了此上限,就只能分sheet或者分多个Excel导出，但是还有一种方式，是没有数量限制的，那就是本文介绍的 SXSSFWorkbook了。

原理

SXSSFWorkbook是用来生成海量excel数据文件，主要原理是借助临时存储空间生成excel，SXSSFWorkbook专门处理大数据，对于大型excel的创建且不会内存溢出的，就只有SXSSFWorkbook了。它的原理很简单，用硬盘空间换内存（就像hashmap用空间换时间一样）。 SXSSFWorkbook是streaming版本的XSSFWorkbook,它只会保存最新的excel rows在内存里供查看，在此之前的excel rows都会被写入到硬盘里（Windows电脑的话，是写入到C盘根目录下的temp文件夹）。被写入到硬盘里的rows是不可见的/不可访问的。只有还保存在内存里的才可以被访问到。

具体实现

引入依赖

4.0.0版本需要jdk1.8及以上，3.9版本可以用jdk1.7，更多版本可以去官网查询

		<dependency>
		    <groupId>org.apache.poi</groupId>
		    <artifactId>poi-ooxml-schemas</artifactId>
		    <version>4.0.0</version>
		</dependency>
		<dependency>
		    <groupId>org.apache.poi</groupId>
		    <artifactId>poi-ooxml</artifactId>
		    <version>4.0.0</version>
		</dependency>
		<dependency>
		    <groupId>org.apache.poi</groupId>
		    <artifactId>poi</artifactId>
		    <version>4.0.0</version>
		</dependency>

代码

	public static void main() {
		long startTime = System.currentTimeMillis();
		SXSSFWorkbook sxssfWorkbook = null;
		BufferedOutputStream outputStream = null;
		String filePath = "d:\\aaa.xls";
		String sheetName = "导出";
		// 这样表示SXSSFWorkbook只会保留100条数据在内存中，其它的数据都会写到磁盘里，这样的话占用的内存就会很少
		sxssfWorkbook = new SXSSFWorkbook(ExcelThreadUtils.getXSSFWorkbook(filePath, sheetName), 100);
		// 获取第一个Sheet页
		SXSSFSheet sheet = (SXSSFSheet) sxssfWorkbook.getSheetAt(0);

	    String []headers = {"题目","答对次数","答错次数","错误率"};
	    //产生表格标题行
		SXSSFRow row = (SXSSFRow) sheet.createRow(0);
	    for (int i = 0; i < headers.length; i++) {
			SXSSFCell cell = (SXSSFCell) row.createCell(i);
			HSSFRichTextString text = new HSSFRichTextString(headers[i]);
			cell.setCellValue(text);
	    }
	    AsyncTask asyncTask = this.getAsyncTask();
	    try {
	    	Sort sort =  new Sort(Direction.DESC, "wrongRate");
			List<MindQuestion> mindQuestions = mindQuestionRepository.findAll(getSpecification(model), sort);
		    int i = 1;
		    for (MindQuestion mindQuestion : mindQuestions) {
				SXSSFRow rowTemp = (SXSSFRow) sheet.createRow(i);
		    	// 题目
				SXSSFCell cell0 = (SXSSFCell) rowTemp.createCell(0);
		    	cell0.setCellValue(mindQuestion.getName());
		    	// 答对次数
				SXSSFCell cell1 = (SXSSFCell) rowTemp.createCell(1);
		    	cell1.setCellValue(mindQuestion.getRightTime() == null ? 0L : mindQuestion.getRightTime());	    	
		    	// 答错次数
				SXSSFCell cell2 = (SXSSFCell) rowTemp.createCell(2);
		    	cell2.setCellValue(mindQuestion.getWrongTime() == null ? 0L : mindQuestion.getWrongTime());
		    	// 错误率
				SXSSFCell cell3 = (SXSSFCell) rowTemp.createCell(3);
		    	cell3.setCellValue(mindQuestion.getWrongRate() == null ? "0%" : mindQuestion.getWrongRate() / 10 +"%");
		    	i++;
		    }
			outputStream = new BufferedOutputStream(new FileOutputStream(filePath));
			sxssfWorkbook.write(outputStream);
			outputStream.flush();
			sxssfWorkbook.dispose();// 释放workbook所占用的所有windows资源
		} catch (Exception e) {
 			e.printStackTrace();
 			log.error("导出失败", e);
 		} finally {
			if (outputStream != null) {
				try {
					outputStream.close();
				} catch (IOException e) {
					log.error("导出失败", e);
				}
			}
 		}
		log.info("导出题目错误率结束" + params + " " + (System.currentTimeMillis() - startTime) / 1000);
	}

	/**
	 * 创建XSSFWorkbook对象
	 * 
	 * @param filePath
	 * @return
	 */
	public static XSSFWorkbook getXSSFWorkbook(String filePath, String sheetName) {
		XSSFWorkbook workbook = null;
		BufferedOutputStream outputStream = null;
		try {
			File fileXlsxPath = new File(filePath);
			outputStream = new BufferedOutputStream(new FileOutputStream(fileXlsxPath));
			workbook = new XSSFWorkbook();
			workbook.createSheet(sheetName);
			workbook.write(outputStream);
		} catch (Exception e) {
			log.error(sheetName + "出错", e.getMessage());
		} finally {
			if (outputStream != null) {
				try {
					outputStream.close();
				} catch (IOException e) {
					log.error(sheetName + "出错", e.getMessage());
				}
			}
		}
		return workbook;
	}