poi之SXSSFWorkbook大量导出excel

本次的poi导出是用的SXSSFWorkbook，不同于之前写的HSSFWorkbook，HSSFWorkbook导出时，超过65536条数据就会出错，当然前面说的分sheet可以解决该问题，但是SXSSFWorkbook就能导出大量的数据。如下：

1、引用jar包：

我用的版本是3.9的，SXSSFWorkbook的版本要3.8以上的。

poi-3.9.jar、poi-examples-3.0.jar、poi-ooxml-3.9.jar、poi-scratchpad-3.9.jar。我是写过导入，所以要这四个包，其实不需要这么多包，只是我不记得导出只需要哪些包了，所有的包都要同一个版本，不然会冲突。

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.struts2.ServletActionContext;

public class XSSFPoiExcelExport {

	Logger logger = Logger.getLogger(XSSFPoiExcelExport.class);

	HttpServletResponse response;
	// 文件名
	private String fileName;
	// 文件保存路径
	private String fileDir;
	// sheet名
	private String sheetName;
	// 表头字体
	private String titleFontType = "Arial Unicode MS";
	// 表头背景色
	private String titleBackColor = "C1FBEE";
	// 表头字号
	private short titleFontSize = 12;
	// 添加自动筛选的列 如 A:M
	private String address = "";
	// 正文字体
	private String contentFontType = "Arial Unicode MS";
	// 正文字号
	private short contentFontSize = 12;
	// 设置列的公式
	private String colFormula[] = null;

	public XSSFPoiExcelExport(HttpServletResponse response, String fileName, String sheetName) {
		this.response = response;
		this.fileName = fileName;
		this.sheetName = sheetName;
	}

	@SuppressWarnings("unchecked")
	public void wirteExcel(String titleColumn[], String titleName[], int titleSize[],
			List<?> dataList) throws Exception {
//	    //excel文件名
//	    String fileName = System.currentTimeMillis() + ".xlsx";

//		FileOutputStream fos = null;
		/// -> excel导出逻辑
		long startTime = System.currentTimeMillis();
		SXSSFWorkbook sxssfWorkbook = null;
		try {
			HttpServletRequest request = ServletActionContext.getRequest();
			// 新建文件
			OutputStream out = null;
			String finalFileName = null;
			if (fileDir != null) {
				deleteExcel(fileDir);
				// 有文件路径
				out = new FileOutputStream(fileDir);
			} else {
				// 否则，直接写到输出流中
				out = response.getOutputStream();
				fileName = fileName + ".xlsx";
				logger.info("导出excel，文件为：" + fileName);
				response.setContentType("application/x-msdownload");
				final String userAgent = request.getHeader("USER-AGENT"); // 获取浏览器的代理
				// 下面主要是让文件名适应不同浏览器的编码格式
				if (StringUtils.contains(userAgent, "MSIE")) {
					finalFileName = URLEncoder.encode(fileName, "UTF8");
				} else if (StringUtils.contains(userAgent, "Mozilla")) {// google,火狐浏览器
					finalFileName = new String(fileName.getBytes(), "ISO8859-1");
				} else {
					finalFileName = URLEncoder.encode(fileName, "UTF8");// 其他浏览器
				}
				response.setHeader("Content-Disposition", "attachment; filename=\"" + finalFileName + "\"");
			}

			// 创建sheet并命名
			sxssfWorkbook = new SXSSFWorkbook(100);
			sxssfWorkbook.setCompressTempFiles(true);
			// 设置并获取到需要的样式
			XSSFCellStyle xssfCellStyleHeader = getAndSetXSSFCellStyleHeader(sxssfWorkbook);
			XSSFCellStyle xssfCellStyleOne = getAndSetXSSFCellStyleOne(sxssfWorkbook);
			XSSFCellStyle xssfCellStyleTwo = getAndSetXSSFCellStyleTwo(sxssfWorkbook);
			XSSFCellStyle xssfCellStyleThree = getAndSetXSSFCellStyleThree(sxssfWorkbook);
			
			if(dataList!=null) {
				// 求总sheet数量，大于pageCount设置的数据就新建一个sheet
				int pageCount = 10000;
				final int sheetNum = (int) Math.ceil((float) dataList.size() / pageCount);
				logger.info("excel中sheet数量："+sheetNum);
				for (int n = 1; n <= sheetNum; n++) {
					// 添加Worksheet（不添加sheet时生成的xls文件打开时会报错)
					String sheetNames = this.sheetName + "_" + n;
					Sheet sheet = sxssfWorkbook.createSheet(sheetNames);
					// 冻结最左边的两列、冻结最上面的一行
					// 即：滚动横向滚动条时，左边的第一、二列固定不动;滚动纵向滚动条时，上面的第一行固定不动。
					// sheet.createFreezePane(2, 1);
					sheet.createFreezePane(0, 1);
					// 创建第一行,作为header表头
					Row header = sheet.createRow(0);
					for (int i = 0; i < titleName.length; i++) {
						Cell cell = header.createCell(i);
						cell.setCellStyle(xssfCellStyleHeader);
						cell.setCellValue(titleName[i].toString());
					}
					
					if(dataList.size() > 0) {
						if (titleColumn.length > 0) {
							int list_num_start = pageCount*(n-1); // 开始下标
							int list_num_end = (sheetNum == n?(n-1):n)*pageCount+(sheetNum == n?dataList.size()%pageCount:0); // 结束下标
							// 将本sheet数据加入到集合中
							List<Object> useList = new ArrayList<>();
							for (int j = list_num_start; j < list_num_end; j++) {
								Object object = dataList.get(j);
								useList.add(object);
							}
							
							// 遍历创建行，导出数据
							int rows = 0;
							for (Iterator iterator = useList.iterator(); iterator.hasNext();) {
								Object obj = (Object) iterator.next();
								Class clsss = obj.getClass(); // 获得该对对象的class实例
								rows++;
								Row row = sheet.createRow(rows); // 将数据写入行中

								// 将数据写入对应列中
								int a = 0;
								for (int columnIndex = 0; columnIndex < titleColumn.length; columnIndex++) {
									String title = titleColumn[columnIndex].toString().trim();
									if (!"".equals(title)) { // 字段不为空
										// 使首字母大写
										String UTitle = Character.toUpperCase(title.charAt(0)) + title.substring(1, title.length()); // 使其首字母大写;
										String methodName = "get" + UTitle;
										// 设置要执行的方法
										Method method = clsss.getDeclaredMethod(methodName);

										// 获取返回类型
										Class<?> returnType2 = method.getReturnType();
										String returnType = returnType2.getName();
										String data = method.invoke(obj) == null ? "" : method.invoke(obj).toString();

										int x = a++; // 定义列
										Cell cell = row.createCell(x); // 将数据写入行中
										if (data != null && !"".equals(data)) {
											if ("int".equals(returnType)) {
												cell.setCellStyle(xssfCellStyleTwo);
												cell.setCellValue(Integer.parseInt(data));
											} else if ("long".equals(returnType)) {
												cell.setCellStyle(xssfCellStyleTwo);
												cell.setCellValue(Long.parseLong(data));
											} else if ("float".equals(returnType)) {
												cell.setCellStyle(xssfCellStyleOne);
												cell.setCellValue(Float.parseFloat(data));
											} else if ("double".equals(returnType)) {
												cell.setCellStyle(xssfCellStyleOne);
												cell.setCellValue(Double.parseDouble(data));
											} else {
												cell.setCellStyle(xssfCellStyleThree);
												String str = data.substring(0, 1);
												if (data.matches("\\d+") == true && GlobalFunctions.isEquals(str, "0")) {// 判断能否转成数字
													cell.setCellValue("'" + data);// 转换成文本形式 避免 0001导出变成1
												} else {
													cell.setCellValue(data);
												}
											}
										} else {
											cell.setCellStyle(xssfCellStyleThree);
										}
									}
								}
							}
						}
					}
					setSheet(sheet, titleSize);
				}
			}

//			fos = new FileOutputStream(fileName);
			sxssfWorkbook.write(out);
			out.close();
			long endTime = System.currentTimeMillis();
			logger.info("数据全部导出至excel总耗时:" + (endTime - startTime) + "ms；数据长度：" + dataList.size());
			// 当前JVM占用的内存总数(M)
			double total = (Runtime.getRuntime().totalMemory()) / (1024.0 * 1024);
			logger.info("当前JVM占用的内存总数(M)："+ total);
			// JVM最大可用内存总数(M)
			double max = (Runtime.getRuntime().maxMemory()) / (1024.0 * 1024);  
			logger.info("JVM最大可用内存总数(M)："+ max);
			// JVM空闲内存(M)
			double free = (Runtime.getRuntime().freeMemory()) / (1024.0 * 1024);  
			logger.info("JVM空闲内存(M)："+ free);
			// 可用内存内存(M)
			double mayuse=(max - total + free);
			logger.info("可用内存内存(M)："+ mayuse);
			// 已经使用内存(M)
    		double used=(total-free);
    		logger.info("已经使用内存(M)："+ used);
		} catch (Exception e) {
			// TODO: handle exception
			logger.error("发生异常！", e);
		} finally {
			if (sxssfWorkbook != null) {
				// dispose of temporary files backing this workbook on disk -> 处
				// 理SXSSFWorkbook导出excel时，产生的临时文件
				sxssfWorkbook.dispose();
			}
//                if(fos != null) {
//                    fos.close();
//                }
		}

		// sheet名
//	    if (Objects.isNull(num)) {
//	        num = 65536;
//	    }
//	    String[][] content = buildContent(num);
//	    long start = System.currentTimeMillis();
	    SXSSFWorkbook wb = ExcelUtil.getSXSSFWorkbookByPageThread(TITLE, content);
//	    long millis = System.currentTimeMillis() - start;
//	    long second = millis / 1000;
//	    System.out.println("SXSSF Page Thread 导出" + num + "条数据，花费：" + second + "s/ " + millis + "ms");
//	    writeAndClose(response, fileName, wb);
//	    wb.dispose();
	}

	/**
	 * 删除文件，当文件名出现重复的时候调用
	 * 
	 * @param fileDir
	 * @return
	 */
	public boolean deleteExcel(String path) {
		boolean flag = false;
		File file = new File(path);
		// 判断目录或文件是否存在
		if (!file.exists()) { // 不存在返回 false
			return flag;
		} else {
			// 判断是否为文件
			if (file.isFile()) { // 为文件时调用删除文件方法
				file.delete();
				flag = true;
			}
		}
		return flag;
	}

	/**
	 * 设置sheet
	 */
	private void setSheet(Sheet sheet, int titleSize[]) {
		// 设置各列宽度(单位为:字符宽度的1/256)
		for (int i = 0; i < titleSize.length; i++) {
			sheet.setColumnWidth(i, titleSize[i] * 256);
		}
	}

	/**
	 * 获取并设置header样式
	 */
	private XSSFCellStyle getAndSetXSSFCellStyleHeader(SXSSFWorkbook sxssfWorkbook) {
		XSSFCellStyle xssfCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
		Font font = sxssfWorkbook.createFont();
		// 字体大小
		font.setFontHeightInPoints((short) 10);
		// 字体粗细
		font.setBoldweight((short) 20);
		// 将字体应用到样式上面
		xssfCellStyle.setFont(font);
		// 前景颜色
		xssfCellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
		xssfCellStyle.setFillForegroundColor(IndexedColors.AQUA.getIndex());
		// 是否自动换行
		xssfCellStyle.setWrapText(false);
		// 水平居中
		xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
		// 垂直居中
		xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		return xssfCellStyle;
	}

	/**
	 * 获取并设置样式一
	 */
	private XSSFCellStyle getAndSetXSSFCellStyleOne(SXSSFWorkbook sxssfWorkbook) {
		XSSFCellStyle xssfCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
		XSSFDataFormat format = (XSSFDataFormat) sxssfWorkbook.createDataFormat();
		// 是否自动换行
		xssfCellStyle.setWrapText(false);
		// 水平居中
		xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
		// 垂直居中
		xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//		// 前景颜色
//		xssfCellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
//		xssfCellStyle.setFillForegroundColor(IndexedColors.AQUA.getIndex());
		// 边框
		xssfCellStyle.setBorderBottom(BorderStyle.THIN);
		xssfCellStyle.setBorderRight(BorderStyle.THIN);
		xssfCellStyle.setBorderTop(BorderStyle.THIN);
		xssfCellStyle.setBorderLeft(BorderStyle.THIN);
		xssfCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		xssfCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
		xssfCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
		xssfCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		// 防止数字过长,excel导出后,显示为科学计数法,如:防止8615192053888被显示为8.61519E+12
//		xssfCellStyle.setDataFormat(format.getFormat("0"));
		xssfCellStyle.setDataFormat(format.getFormat("0.00")); // 保留两位小数点
		return xssfCellStyle;
	}

	/**
	 * 获取并设置样式二
	 */
	private XSSFCellStyle getAndSetXSSFCellStyleTwo(SXSSFWorkbook sxssfWorkbook) {
		XSSFCellStyle xssfCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
		XSSFDataFormat format = (XSSFDataFormat) sxssfWorkbook.createDataFormat();
		// 是否自动换行
		xssfCellStyle.setWrapText(false);
		// 水平居中
		xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
		// 边框
		xssfCellStyle.setBorderBottom(BorderStyle.THIN);
		xssfCellStyle.setBorderRight(BorderStyle.THIN);
		xssfCellStyle.setBorderTop(BorderStyle.THIN);
		xssfCellStyle.setBorderLeft(BorderStyle.THIN);
		xssfCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		xssfCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
		xssfCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
		xssfCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		// 垂直居中
		xssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		// 防止数字过长,excel导出后,显示为科学计数法,如:防止8615192053888被显示为8.61519E+12
		xssfCellStyle.setDataFormat(format.getFormat("0"));
		return xssfCellStyle;
	}
	
	/**
	 * 获取并设置样式三
	 */
	private XSSFCellStyle getAndSetXSSFCellStyleThree(SXSSFWorkbook sxssfWorkbook) {
		XSSFCellStyle xssfCellStyle = (XSSFCellStyle) sxssfWorkbook.createCellStyle();
		// 是否自动换行
		xssfCellStyle.setWrapText(false);
		// 边框
		xssfCellStyle.setBorderBottom(BorderStyle.THIN);
		xssfCellStyle.setBorderRight(BorderStyle.THIN);
		xssfCellStyle.setBorderTop(BorderStyle.THIN);
		xssfCellStyle.setBorderLeft(BorderStyle.THIN);
		xssfCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		xssfCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
		xssfCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
		xssfCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		return xssfCellStyle;
	}

	public void setResponseHeader(HttpServletResponse response, String fileName) {
		try {
			try {
				fileName = new String(fileName.getBytes(), "UTF-8");
			} catch (UnsupportedEncodingException e) {
				e.printStackTrace();
			}
			response.setContentType("application/octet-stream;charset=ISO8859-1");
			response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
			response.addHeader("Pargam", "no-cache");
			response.addHeader("Cache-Control", "no-cache");
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

}

调用方法：

// 获取文件名
String statisticsTypeName = "导出";
// 拼接地址文件名并放入
XSSFPoiExcelExport pee = new XSSFPoiExcelExport(response,statisticsTypeName+System.currentTimeMillis(),statisticsTypeName);
// 获取要导出的数据(我这里的获取数据是打个比方，你获取要导出的数据就行)
List<Admin> adminList= new ArrayList<Admin>();
// 要导出的数据字段，按表格的顺序列出
String titleColumn[] = {"Name","Sex","Age","Love"};
// 要导出的表头
String titleName[] = {"姓名","性别","年龄","爱好"};
// 导出到excel中表头的宽度
String titleSize[] = {13,13,13,20};
// 调用导出方法
pee.wirteExcel(titleColumn, titleName, titleSize, adminList);

我这里的SXSSFWorkbook导出excel也分了sheet，不需要的可以自己去除，或者把pageCount改大就行。