测试java导出excel

package com.iflytek.sgy.zljd.web.qpcz;

import com.iflytek.sgy.zljd.domain.TZljdXkzDzzz;
import com.iflytek.sgy.zljd.persistence.xkz.XkzDzzzdDao;
import org.apache.log4j.Logger;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import java.io.IOException;
import java.util.List;



@Controller
@RequestMapping("test")
public class ExportToExcelController {

	@Autowired
	private XkzDzzzdDao xkzDzzzdDao;

	private Logger logger = Logger.getLogger(this.getClass());

	@RequestMapping("/testExportDataToExcel.do")
	@ResponseBody
	public void exportToExcel(HttpServletResponse response, HttpServletRequest request) {
		logger.info("开始导出.........................................");
		long startTime = System.currentTimeMillis();
		//内存中保存10000行，超过的实时写入磁盘，保证内存消耗不会过大
		final int rowAccessWindowSize = 50000;
		try {
			SXSSFWorkbook workbook = new SXSSFWorkbook(rowAccessWindowSize);
			export(workbook,response);
			//writeAndClose(response, workbook);
			long endTime = System.currentTimeMillis();
			logger.info("导出完成，用时：" + (endTime - startTime) / 1000 + "秒");
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("导出失败: ", e);
		}
	}
	public void export(SXSSFWorkbook workbook,HttpServletResponse response) throws IOException {
		FileOutputStream output = null;
		// 标题
		final String[] titleArr = {"主键Id", "上报原因", "电子证照url", "制证状态", "是否删除"};
		// 结束标识。由于大量数据时count()很慢，所以不查询总数，一直往下查，直到查询的数据量小于每页数据
		boolean end = true;
		// 查询的次数
		int i = 1;
		// 每次查询数据库的条数
		final int pageSize = 50000;
		// 每个sheet的总行数,超过这个行数就会新建一个sheet
		final int sheetTotalRow = 50000;
		// 行号计数器
		int currentRow = 0;
		// sheet的数量
		int sheetNum = 0;
		// 从数据库中查询的数据
		List users = null;
		// sheet对象
		Sheet sheet = null;
		while (end) {
			logger.info("第【" + i + "】次查询数据库.............");
			Integer startData = (i - 1) * pageSize;
			Integer endData = i * pageSize;
			long startTime = System.currentTimeMillis();
			users = xkzDzzzdDao.findAll(startData, endData);
			if (users != null && users.size() > 0) {
				logger.info("第【" + i + "】次查询数据库的数据量为" + users.size());
				long endTime = System.currentTimeMillis();
				logger.info("第【" + i + "】次查询" + users.size() +"条数据共用:"+ (endTime - startTime) / 1000 + "秒");
			} else {
				end = false;
			}
			int size = users.size();
			for (int j = 0; j < size; j++) {
				//
				if (j == 0) {
					currentRow = 0;
					sheetNum++;
					//创建一个sheet
					sheet = workbook.createSheet("用户清单" + sheetNum);
					logger.info("第【" + sheetNum + "】次创建sheet页.............");
					//创建第一行单元格
					Row titleRow = sheet.createRow(currentRow);
					for (int v = 0; v < titleArr.length; v++) {
						//获取第一行的每个单元格
						Cell cell = titleRow.createCell(v);
						//给一行每个单元格赋值
						cell.setCellValue(titleArr[v]);
					}
				}
				currentRow++;
				TZljdXkzDzzz user = users.get(j);
				//获取第二行单元格,放数据
				Row dataRow = sheet.createRow(currentRow);
				for (int q = 0; q < titleArr.length; q++) {
					//获取每行的单元格
					Cell cell = dataRow.createCell(q);
					switch (q) {
						case 0:
							cell.setCellValue(user.getId());
							break;
						case 1:
							cell.setCellValue(user.getSbyy());
							break;
						case 2:
							cell.setCellValue(user.getDzzzurl());
							break;
						case 3:
							cell.setCellValue(user.getZzzt());
							break;
						case 4:
							cell.setCellValue(user.getSfsc());
							break;
					}
				}
				// 弱化引用,为了让对象尽快被回收,参考ArrayList的clear()方法
				user = null;
			}
			String fileName = "用户"+(i)+".xlsx";
				setResponseHeader(response, fileName);
				BufferedOutputStream bos = new BufferedOutputStream(new FileOutputStream("D:\" + fileName));
				//FileOutputStream output = new FileOutputStream("D:\" + fileName);
				workbook.write(bos);
				bos.flush();
				bos.close();
			logger.info("第【" + i + "】次数据导入到excel里面.............");
			i++;
		}
	}
//	public void export(SXSSFWorkbook workbook) throws IOException {
//		FileOutputStream output = null;
//		// 标题
//		final String[] titleArr = {"主键Id", "上报原因", "电子证照url", "制证状态", "是否删除"};
//		// 结束标识。由于大量数据时count()很慢，所以不查询总数，一直往下查，直到查询的数据量小于每页数据
//		boolean end = true;
//		// 查询的次数
//		int i = 1;
//		// 每次查询数据库的条数
//		final int pageSize = 50000;
//		// 每个sheet的总行数,超过这个行数就会新建一个sheet
//		final int sheetTotalRow = 50000;
//		// 行号计数器
//		int currentRow = 0;
//		// sheet的数量
//		int sheetNum = 0;
//		// 从数据库中查询的数据
//		List users = null;
//		// sheet对象
//		Sheet sheet = null;
//		while (end) {
//			logger.info("第【" + i + "】次查询数据库.............");
//			Integer startData = (i - 1) * pageSize;
//			Integer endData = i * pageSize;
//			long startTime = System.currentTimeMillis();
//			users = xkzDzzzdDao.findAll(startData, endData);
//			if (users != null && users.size() > 0) {
//				logger.info("第【" + i + "】次查询数据库的数据量为" + users.size());
//				long endTime = System.currentTimeMillis();
//				logger.info("第【" + i + "】次查询" + users.size() + (endTime - startTime) / 1000 + "秒");
//			} else {
//				end = false;
//			}
//			i++;
//			int size = users.size();
//			for (int j = 0; j < size; j++) {
//				// 每五万行,创建一个sheet currentRow=50000的时候创建一下新的sheet存入数据
//				if (sheet == null || currentRow % sheetTotalRow == 0) {
//					currentRow = 0;
//					sheetNum++;
//					//创建一个sheet
//					sheet = workbook.createSheet("用户清单" + sheetNum);
//					logger.info("第【" + sheetNum + "】次创建sheet页.............");
//					//创建第一行单元格
//					Row titleRow = sheet.createRow(currentRow);
//					for (int v = 0; v < titleArr.length; v++) {
//						//获取第一行的每个单元格
//						Cell cell = titleRow.createCell(v);
//						//给一行每个单元格赋值
//						cell.setCellValue(titleArr[v]);
//					}
//				}
//				currentRow++;
//				TZljdXkzDzzz user = users.get(j);
//				//获取第二行单元格,放数据
//				Row dataRow = sheet.createRow(currentRow);
//				for (int q = 0; q < titleArr.length; q++) {
//					//获取每行的单元格
//					Cell cell = dataRow.createCell(q);
//					switch (q) {
//						case 0:
//							cell.setCellValue(user.getId());
//							break;
//						case 1:
//							cell.setCellValue(user.getSbyy());
//							break;
//						case 2:
//							cell.setCellValue(user.getDzzzurl());
//							break;
//						case 3:
//							cell.setCellValue(user.getZzzt());
//							break;
//						case 4:
//							cell.setCellValue(user.getSfsc());
//							break;
//					}
//				}
//				// 弱化引用,为了让对象尽快被回收,参考ArrayList的clear()方法
//				user = null;
//			}
//		}
//	}

	private void writeAndClose(HttpServletResponse response, SXSSFWorkbook wb) {
		int i = 1;
		String fileName = "用户"+(i)+".xlsx";
		BufferedOutputStream bos = null;
		try {
			setResponseHeader(response, fileName);
			 bos = new BufferedOutputStream(new FileOutputStream("D:\" + fileName));
			//FileOutputStream output = new FileOutputStream("D:\" + fileName);
			wb.write(bos);
			//output.flush();
			//output.close();
			// 此方法能够删除导出过程中生成的xml临时文件
		//	wb.dispose();
		} catch (Exception e) {
			logger.error("导出失败:", e);
		}finally {
			if (bos!=null){
				try {
					bos.flush();
					bos.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public void setResponseHeader(HttpServletResponse response, String fileName) {
		try {
			response.setContentType("application/octet-stream;charset=utf-8");
			response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1"));
			response.addHeader("Pargam", "no-cache");
			response.addHeader("Cache-Control", "no-cache");
		} catch (Exception e) {
			logger.error("导出失败:", e);
		}
	}
}