Java poi Excel导出文件，Java poi 分批次导出大批量数据

 

Java poi Excel导出文件，Java poi 分批次导出大批量数据

 

 

================================

©Copyright 蕃薯耀 2018年6月26日

http://fanshuyao.iteye.com/

 

一、问题描述：

1、当使用WorkbookFactory.create 创建Excel时，其实创建的文档为Excel2003版的，最大容纳65536行数据，超出这个行数就是发生溢出。

WorkbookFactory.create(...);

 

2、当数据量时，直接从数据库查询出来，内存不够也会溢出。

 

二、解决方案：

 1、解决Excel2003行数溢出，使用XSSFWorkbook对象创建2007版的Excel，能容纳104万行数据。

具体如下：

Excel2003版最大行数是65536，Excel2007最大行数是1048576

Excel2003版最大列数是256，Excel2007最大列数是16384

 

XSSFWorkbook xSSFWorkbook = new XSSFWorkbook(new FileInputStream(excelTemplatePath));//使用模板文件

 

2、直接从数据库查询大批量数据导致内存溢出，可以使用分批次的方式查询数据，避免内存一下子暴掉。如要导出数据库100万条数据，每次取1万条，这样分100次取，每次取的数量自己可以定义，保证内存不溢出。

 

具体例子和代码：

 

/**
	 * 导出excel文件
	 * @param request
	 * @param response
	 * @throws Exception
	 */
	public void exportPlaceNameQueryExcelFile(HttpServletRequest request,HttpServletResponse response){
		Map<String, Object> map = new HashMap<String, Object>();;
		FileOutputStream fileOutputStream = null;
		try {
			Row row = parseRequestParametersToRow(request);
			
			String projectPath = getRealPath(request);
			String excelTemplatePath = projectPath + File.separator 
					+ "template" + File.separator + "name" + File.separator 
					+ "exportPlaceNameQueryExcelFile.xlsx";
			File excelTemplateFile = new File(excelTemplatePath);//模板文件
			
			File destFile = this.createDestExcelFile(request, excelTemplateFile);//目标文件
			
			fileOutputStream = new FileOutputStream(destFile);
			
			map.put("absoluteFilePath", destFile.getAbsolutePath());
			map.put("fileName", destFile.getName());
			int count = getPlaceNameQueryDelegate().getPnPlaceNameCount(row);//获取Excel导出的总数据量，根据数量判断是否进行分批次导出
			int pageSize = 1000 * 5;//每次查询的数据量
			int startRow = 2;//模板前2行是模板标题，excel行数是从0开始的，除去2行模板标题，此处设置成2
			XSSFWorkbook xSSFWorkbook = new XSSFWorkbook(new FileInputStream(excelTemplatePath));//使用模板文件
			//Excel2003版最大行数是65536，Excel2007最大行数是1048576
			//Excel2003版最大列数是256，Excel2007最大列数是16384
			SXSSFWorkbook workbook = new SXSSFWorkbook(xSSFWorkbook, 100);//使用SXSSFWorkbook避免Excel数据超出65536行时报错
			//Workbook b = WorkbookFactory.create(destFile);//超出65536行时报错
			Sheet sheet = workbook.getSheetAt(0);
			List<Row> rows = null;
			if(count > pageSize){//当数量大时，采用分批次获取数据
				int page = (count % pageSize == 0) ? (count / pageSize) : (count / pageSize + 1);
				for(int i=1; i<=page; i++){
					row.addColumn("pageNumber", i);
					row.addColumn("pageSize", pageSize);
					rows = getPlaceNameQueryDelegate().getAllPlaceNames(row);
					startRow = this.setSheet(sheet, startRow, rows);//此处要注意回写startRow供下次循环使用
				}
			}else{
				rows = getPlaceNameQueryDelegate().getAllPlaceNames(row);
				startRow = this.setSheet(sheet, startRow, rows);
			}
			workbook.write(fileOutputStream);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				if(fileOutputStream != null){
					fileOutputStream.close();
				}
				writeJson(response, JsonUtils.toJson(map));
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

 

 

 

private File createDestExcelFile(HttpServletRequest request, File excelTemplateFile){
		try {
			String destFileName = UUID.randomUUID().toString();
			Properties  prop = PropertiesUtils.read("placeName.properties");
			String destDir = prop.getProperty("destDirForPlaceNameQuery");
			
			File destFileDir = new File(destDir); 
			if(!destFileDir.exists()){
				destFileDir.mkdirs();
			}
			String absoluteFilePath =  excelTemplateFile.getAbsolutePath();
			String suffix = absoluteFilePath.substring(absoluteFilePath.lastIndexOf("."));
			File destFile = new File(destDir + destFileName + suffix);
			FileUtils.copyFile(excelTemplateFile, destFile);
			return destFile;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

 

 

 

private void writeToSheet(Sheet sheet, int startRow, int startCell, String value){
		org.apache.poi.ss.usermodel.Row poiRow = sheet.getRow(startRow);
		if(poiRow == null){
			poiRow = sheet.createRow(startRow);
		}
		Cell cell = poiRow.getCell(startCell);
		if(cell == null){
			cell = poiRow.createCell(startCell);
		}		
		cell.setCellValue(value);
	}

 

 

 

/**
	 * 根据Excel对象插入数据后，返回可以插入数据下一行的行数，startRow
	 * @param workbook
	 * @param startRow
	 * @param rows
	 * @return int
	 */
	private int setSheet(Sheet sheet, int startRow, List<Row> rows){
		if(rows != null && rows.size() > 0){
			
			if(startRow < 0){
				startRow = sheet.getLastRowNum();
			}
			for (Row row : rows) {
				int startCell = 0;
				this.writeToSheet(sheet, startRow, startCell++, row.getString("pn_code"));
				this.writeToSheet(sheet, startRow, startCell++, row.getString("standard_name"));//地名（standard_name）
				this.writeToSheet(sheet, startRow, startCell++, row.getString("pinyin"));//拼音（pinyin）
				this.writeToSheet(sheet, startRow, startCell++, row.getString("tree_path"));//地名类别（tree_path）
				String statusText = row.getString("status");
				if(statusText.equals("1")){
					statusText = "有效";
				}else if(statusText.equals("0")){
					statusText = "无效";
				}else if(statusText.equals("2")){
					statusText = "审批中";
				}
				this.writeToSheet(sheet, startRow, startCell++, statusText);//状态（status）
				
				//额外属性
				String type = row.getString("type");
				if(!StringUtils.isBlank(type)){
					type = type.trim();
					if("建筑物".equals(type)){
						//紧接着基础信息，startCell不用加
						this.setBuildingProperties(sheet, startRow, startCell, row);//建筑物
						
					}else if("道路".equals(type)){
						startCell += 10;//除去建筑物的10个属性
						this.setRoadProperties(sheet, startRow, startCell, row);
						
					}else if("桥梁".equals(type)){
						startCell += 18;//除去建筑物、道路的18个属性
						this.setBridgeProperties(sheet, startRow, startCell, row);
						
					}else if("公园".equals(type)){
						startCell += 28;//除去建筑物、道路、桥梁的28个属性
						this.setPartProperties(sheet, startRow, startCell, row);
						
					}else if("广场".equals(type)){
						startCell += 37;//除去建筑物、道路、桥梁、公园的37个属性
						this.setSquareProperties(sheet, startRow, startCell, row);
						
					}else if("隧道".equals(type)){
						startCell += 46;//除去建筑物、道路、桥梁、公园、广场的46个属性
						this.setTunnelProperties(sheet, startRow, startCell, row);
					}
				}
				
				startRow ++;//最后行号加1，必须有，且在最后
			}
		}
		return startRow;
	}

 

 

 

private void setBuildingProperties(Sheet sheet, int startRow, int startCell, Row row){
		try {
			Row dataRow = getPlaceNameQueryDelegate().getBuilding(row);
			if(dataRow != null){
				this.writeToSheet(sheet, startRow, startCell++, dataRow.getString("building_func"));
				this.writeToSheet(sheet, startRow, startCell++, dataRow.getString("start_date"));
                                 ……
                                 ……
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

 

private void setRoadProperties(Sheet sheet, int startRow, int startCell, Row row){
    try {
        Row dataRow = getPlaceNameQueryDelegate().getRoad(row);
            if(dataRow != null){
            this.writeToSheet(sheet, startRow, startCell++, dataRow.getString("starting_point"));
            ……
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}

 

Excel文件下载见：

http://fanshuyao.iteye.com/blog/2425604 

 

（如果你觉得文章对你有帮助，欢迎捐赠，^_^，谢谢！） 

    

================================

©Copyright 蕃薯耀 2018年6月26日

http://fanshuyao.iteye.com/

评论

1 楼 蕃薯耀 2018-07-03  

Java poi Excel导出文件，Java poi 导出Excel文件，Java poi 分批次导出数据，Java Poi内存溢出

===========
蕃薯耀