EXCEL导出大量数据的处理方式

使用poi-ooxml-3.17.jar

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

@Override
	public void createExcel(String name, String time, String id, Map<String, Object> model, Map<String, Object> sdModel) throws Exception {
		String filePath = PathUtil.getClasspath() + Const.REVENUESTATISTICS + id + name + time + ".xlsx";
		XSSFWorkbook workbook = new XSSFWorkbook();
		File fileXlsxPath = new File(filePath);
		BufferedOutputStream outputStream = new BufferedOutputStream(new FileOutputStream(fileXlsxPath));
		if("实验室收入统计".equals(name)) {
			workbook.createSheet("临床检验收入统计");
			workbook.createSheet("山东大学齐鲁医院（外送检测）收入统计");
		}else {
			workbook.createSheet("Sheet1");
		}
		workbook.write(outputStream);
		outputStream.close();
		//这样表示SXSSFWorkbook只会保留100条数据在内存中，其它的数据都会写到磁盘里，这样的话占用的内存就会很少
		SXSSFWorkbook sxssfWorkbook =  new SXSSFWorkbook(workbook, 100);
		//获取第一个Sheet页
		SXSSFSheet sheet = sxssfWorkbook.getSheetAt(0);
		CellStyle cellstyle = sxssfWorkbook.createCellStyle();
		cellstyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);
		Cell cell;
		Row row;
		List<String> titles = (List<String>) model.get("titles");
		row = sheet.createRow(0);
		for(int i = 0; i < titles.size(); i++) {
			cell = row.createCell(i);
			cell.setCellValue(titles.get(i));
			cell.setCellStyle(cellstyle);
		}
		List<PageData> varList = (List<PageData>) model.get("varList");
		for(int i = 0;i < varList.size();i++) {
			row = sheet.createRow(i+1);
			PageData vpd = varList.get(i);
			for(int j = 0;j < titles.size();j++) {
				cell = row.createCell(j);
				cell.setCellValue(vpd.getString("var"+(j+1)));
				cell.setCellStyle(cellstyle);
			}
		}
		//自动调整列宽
		sheet.trackAllColumnsForAutoSizing();
		for(int i = 0; i < titles.size(); i++) {
		    sheet.autoSizeColumn(i);
		    //手动调整列宽，解决中文不能自适应问题
		    //单元格单行最长支持255*256宽度（每个单元格样式已经设置自动换行，超出即换行）
		    //设置最低列宽度，列宽约六个中文字符
		    int width = Math.max(15 * 256, Math.min(255 * 256, sheet.getColumnWidth(i) * 17 / 10));
		    sheet.setColumnWidth(i, width);
		}
		
		if("实验室收入统计".equals(name)) {
			sheet = sxssfWorkbook.getSheetAt(1);
			row = sheet.createRow(0);
			cell = null;
			cellstyle = sxssfWorkbook.createCellStyle();
			cellstyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);
			titles = (List<String>) sdModel.get("titles");
			for(int i = 0; i < titles.size(); i++) {
				cell = row.createCell(i);
				cell.setCellValue(titles.get(i));
				cell.setCellStyle(cellstyle);
			}
			varList = (List<PageData>) sdModel.get("varList");
			for(int i = 0;i < varList.size();i++) {
				row = sheet.createRow(i+1);
				PageData vpd = varList.get(i);
				for(int j = 0;j < titles.size();j++) {
					cell = row.createCell(j);
					cell.setCellValue(vpd.getString("var"+(j+1)));
					cell.setCellStyle(cellstyle);
				}
			}
			//自动调整列宽
			sheet.trackAllColumnsForAutoSizing();
			for(int i = 0; i < titles.size(); i++) {
				sheet.autoSizeColumn(i);
			    //手动调整列宽，解决中文不能自适应问题
			    //单元格单行最长支持255*256宽度（每个单元格样式已经设置自动换行，超出即换行）
			    //设置最低列宽度，列宽约六个中文字符
			    int width = Math.max(15 * 256, Math.min(255 * 256, sheet.getColumnWidth(i) * 17 / 10));
			    sheet.setColumnWidth(i, width);
			}
		}
		outputStream = new BufferedOutputStream(new FileOutputStream(filePath));
		sxssfWorkbook.write(outputStream);
		outputStream.flush();
		sxssfWorkbook.dispose();//释放workbook所占用的所有windows资源
		outputStream.close();
	}