POI操作EXCEL对象
HSSF：操作Excel 97(.xls)格式
XSSF：操作Excel 2007 OOXML (.xlsx)格式，操作EXCEL内存占用高于HSSF
SXSSF:从POI3.8 beta3开始支持，基于XSSF，低内存占用。
使用POI的HSSF对象，生成Excel 97(.xls)格式，生成的EXCEL不经过压缩直接导出。
线上问题：负载服务器转发请求到应用服务器阻塞,以及内存溢出 。
如果系统存在大数据量报表导出，则考虑使用POI的SXSSF进行EXCEL操作。
(相关资料图)
HSSF生成的Excel 97(.xls)格式本身就有每个sheet页不能超过65536条的限制。
XSSF生成Excel 2007 OOXML (.xlsx)格式，条数增加了，但是导出过程中，内存占用率却高于HSSF.
SXSSF是自3.8-beta3版本后，基于XSSF提供的低内存占用的操作EXCEL对象。其原理是可以设置或者手动将内存中的EXCEL行写到硬盘中，这样内存中只保存了少量的EXCEL行进行操作。
EXCEL的压缩率特别高，能达到80%，12M的文件压缩后才2M左右。 如果未经过压缩、不仅会占用用户带宽，且会导致负载服务器（apache）和应用服务器之间，长时间占用连接(二进制流转发)，导致负载服务器请求阻塞，不能提供服务。
一定要注意文件流的关闭
防止前台（页面）连续触发导出EXCEL
1.通用核心导出工具类 ExcelUtil.java
package sy.util;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.io.OutputStream;import java.lang.reflect.Method;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.Iterator;import java.util.LinkedHashMap;import java.util.List;import java.util.Map;import org.apache.poi.hpsf.SummaryInformation;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFClientAnchor;import org.apache.poi.hssf.usermodel.HSSFComment;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.hssf.usermodel.HSSFPatriarch;import org.apache.poi.hssf.usermodel.HSSFRichTextString;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.formula.functions.T;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.DateUtil;import org.apache.poi.ss.usermodel.Font;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.xssf.streaming.SXSSFCell;import org.apache.poi.xssf.streaming.SXSSFRow;import org.apache.poi.xssf.streaming.SXSSFSheet;import org.apache.poi.xssf.streaming.SXSSFWorkbook;import com.alibaba.fastjson.JSONArray;import com.alibaba.fastjson.JSONObject;public class ExcelUtil{ public static String NO_DEFINE = "no_define";//未定义的字段 public static String DEFAULT_DATE_PATTERN="yyyy年MM月dd日";//默认日期格式 public static int DEFAULT_COLOUMN_WIDTH = 17; /** * 导出Excel 97(.xls)格式 ，少量数据 * @param title 标题行 * @param headMap 属性-列名 * @param jsonArray 数据集 * @param datePattern 日期格式，null则用默认日期格式 * @param colWidth 列宽 默认 至少17个字节 * @param out 输出流 */ public static void exportExcel(String title,Map<String, String> headMap,JSONArray jsonArray,String datePattern,int colWidth, OutputStream out) { if(datePattern==null) datePattern = DEFAULT_DATE_PATTERN; // 声明一个工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); workbook.createInformationProperties(); workbook.getDocumentSummaryInformation().setCompany("*****公司"); SummaryInformation si = workbook.getSummaryInformation(); si.setAuthor("JACK"); //填加xls文件作者信息 si.setApplicationName("导出程序"); //填加xls文件创建程序信息 si.setLastAuthor("最后保存者信息"); //填加xls文件最后保存者信息 si.setComments("JACK is a programmer!"); //填加xls文件作者信息 si.setTitle("POI导出Excel"); //填加xls文件标题信息 si.setSubject("POI导出Excel");//填加文件主题信息 si.setCreateDateTime(new Date()); //表头样式 HSSFCellStyle titleStyle = workbook.createCellStyle(); titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont titleFont = workbook.createFont(); titleFont.setFontHeightInPoints((short) 20); titleFont.setBoldweight((short) 700); titleStyle.setFont(titleFont); // 列头样式 HSSFCellStyle headerStyle = workbook.createCellStyle(); headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont headerFont = workbook.createFont(); headerFont.setFontHeightInPoints((short) 12); headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerStyle.setFont(headerFont); // 单元格样式 HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont cellFont = workbook.createFont(); cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); cellStyle.setFont(cellFont); // 生成一个(带标题)表格 HSSFSheet sheet = workbook.createSheet(); // 声明一个画图的顶级管理器 HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); // 定义注释的大小和位置,详见文档 HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5)); // 设置注释内容 comment.setString(new HSSFRichTextString("可以在POI中添加注释！")); // 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容. comment.setAuthor("JACK"); //设置列宽 int minBytes = colWidth<DEFAULT_COLOUMN_WIDTH?DEFAULT_COLOUMN_WIDTH:colWidth;//至少字节数 int[] arrColWidth = new int[headMap.size()]; // 产生表格标题行,以及设置列宽 String[] properties = new String[headMap.size()]; String[] headers = new String[headMap.size()]; int ii = 0; for (Iterator<String> iter = headMap.keySet().iterator(); iter .hasNext();) { String fieldName = iter.next(); properties[ii] = fieldName; headers[ii] = fieldName; int bytes = fieldName.getBytes().length; arrColWidth[ii] = bytes < minBytes ? minBytes : bytes; sheet.setColumnWidth(ii,arrColWidth[ii]*256); ii++; } // 遍历集合数据，产生数据行 int rowIndex = 0; for (Object obj : jsonArray) { if(rowIndex == 65535 || rowIndex == 0){ if ( rowIndex != 0 ) sheet = workbook.createSheet();//如果数据超过了，则在第二页显示 HSSFRow titleRow = sheet.createRow(0);//表头 rowIndex=0 titleRow.createCell(0).setCellValue(title); titleRow.getCell(0).setCellStyle(titleStyle); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1)); HSSFRow headerRow = sheet.createRow(1); //列头 rowIndex =1 for(int i=0;i<headers.length;i++) { headerRow.createCell(i).setCellValue(headers[i]); headerRow.getCell(i).setCellStyle(headerStyle); } rowIndex = 2;//数据内容从 rowIndex=2开始 } JSONObject jo = (JSONObject) JSONObject.toJSON(obj); HSSFRow dataRow = sheet.createRow(rowIndex); for (int i = 0; i < properties.length; i++) { HSSFCell newCell = dataRow.createCell(i); Object o = jo.get(properties[i]); String cellValue = ""; if(o==null) cellValue = ""; else if(o instanceof Date) cellValue = new SimpleDateFormat(datePattern).format(o); else cellValue = o.toString(); newCell.setCellValue(cellValue); newCell.setCellStyle(cellStyle); } rowIndex++; } // 自动调整宽度 /*for (int i = 0; i < headers.length; i++) { sheet.autoSizeColumn(i); }*/ try { workbook.write(out); workbook.close(); } catch (IOException e) { e.printStackTrace(); } } /** * 导出Excel 2007 OOXML (.xlsx)格式 * @param title 标题行 * @param headMap 属性-列头 * @param jsonArray 数据集 * @param datePattern 日期格式，传null值则默认 年月日 * @param colWidth 列宽 默认 至少17个字节 * @param out 输出流 */ public static void exportExcelX(String title,Map<String, String> headMap,JSONArray jsonArray,String datePattern,int colWidth, OutputStream out) { if(datePattern==null) datePattern = DEFAULT_DATE_PATTERN; // 声明一个工作薄 SXSSFWorkbook workbook = new SXSSFWorkbook(1000);//缓存 workbook.setCompressTempFiles(true); //表头样式 CellStyle titleStyle = workbook.createCellStyle(); titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); Font titleFont = workbook.createFont(); titleFont.setFontHeightInPoints((short) 20); titleFont.setBoldweight((short) 700); titleStyle.setFont(titleFont); // 列头样式 CellStyle headerStyle = workbook.createCellStyle(); headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); Font headerFont = workbook.createFont(); headerFont.setFontHeightInPoints((short) 12); headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerStyle.setFont(headerFont); // 单元格样式 CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); Font cellFont = workbook.createFont(); cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); cellStyle.setFont(cellFont); // 生成一个(带标题)表格 SXSSFSheet sheet = workbook.createSheet(); //设置列宽 int minBytes = colWidth<DEFAULT_COLOUMN_WIDTH?DEFAULT_COLOUMN_WIDTH:colWidth;//至少字节数 int[] arrColWidth = new int[headMap.size()]; // 产生表格标题行,以及设置列宽 String[] properties = new String[headMap.size()]; String[] headers = new String[headMap.size()]; int ii = 0; for (Iterator<String> iter = headMap.keySet().iterator(); iter .hasNext();) { String fieldName = iter.next(); properties[ii] = fieldName; headers[ii] = headMap.get(fieldName); int bytes = fieldName.getBytes().length; arrColWidth[ii] = bytes < minBytes ? minBytes : bytes; sheet.setColumnWidth(ii,arrColWidth[ii]*256); ii++; } // 遍历集合数据，产生数据行 int rowIndex = 0; for (Object obj : jsonArray) { if(rowIndex == 65535 || rowIndex == 0){ if ( rowIndex != 0 ) sheet = workbook.createSheet();//如果数据超过了，则在第二页显示 SXSSFRow titleRow = sheet.createRow(0);//表头 rowIndex=0 titleRow.createCell(0).setCellValue(title); titleRow.getCell(0).setCellStyle(titleStyle); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1)); SXSSFRow headerRow = sheet.createRow(1); //列头 rowIndex =1 for(int i=0;i<headers.length;i++) { headerRow.createCell(i).setCellValue(headers[i]); headerRow.getCell(i).setCellStyle(headerStyle); } rowIndex = 2;//数据内容从 rowIndex=2开始 } JSONObject jo = (JSONObject) JSONObject.toJSON(obj); SXSSFRow dataRow = sheet.createRow(rowIndex); for (int i = 0; i < properties.length; i++) { SXSSFCell newCell = dataRow.createCell(i); Object o = jo.get(properties[i]); String cellValue = ""; if(o==null) cellValue = ""; else if(o instanceof Date) cellValue = new SimpleDateFormat(datePattern).format(o); else if(o instanceof Float || o instanceof Double) cellValue= new BigDecimal(o.toString()).setScale(2,BigDecimal.ROUND_HALF_UP).toString(); else cellValue = o.toString(); newCell.setCellValue(cellValue); newCell.setCellStyle(cellStyle); } rowIndex++; } // 自动调整宽度 /*for (int i = 0; i < headers.length; i++) { sheet.autoSizeColumn(i); }*/ try { workbook.write(out); workbook.close(); workbook.dispose(); } catch (IOException e) { e.printStackTrace(); } } //Web 导出excel public static void downloadExcelFile(String title,Map<String,String> headMap,JSONArray ja,HttpServletResponse response){ try { ByteArrayOutputStream os = new ByteArrayOutputStream(); ExcelUtil.exportExcelX(title,headMap,ja,null,0,os); byte[] content = os.toByteArray(); InputStream is = new ByteArrayInputStream(content); // 设置response参数，可以打开下载页面 response.reset(); response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8"); response.setHeader("Content-Disposition", "attachment;filename="+ new String((title + ".xlsx").getBytes(), "iso-8859-1")); response.setContentLength(content.length); ServletOutputStream outputStream = response.getOutputStream(); BufferedInputStream bis = new BufferedInputStream(is); BufferedOutputStream bos = new BufferedOutputStream(outputStream); byte[] buff = new byte[8192]; int bytesRead; while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) { bos.write(buff, 0, bytesRead); } bis.close(); bos.close(); outputStream.flush(); outputStream.close(); }catch (Exception e) { e.printStackTrace(); } } public static void main(String[] args) throws IOException { int count = 100000; JSONArray ja = new JSONArray(); for(int i=0;i<100000;i++){ Student s = new Student(); s.setName("POI"+i); s.setAge(i); s.setBirthday(new Date()); s.setHeight(i); s.setWeight(i); s.setSex(i/2==0?false:true); ja.add(s); } Map<String,String> headMap = new LinkedHashMap<String,String>(); headMap.put("name","姓名"); headMap.put("age","年龄"); headMap.put("birthday","生日"); headMap.put("height","身高"); headMap.put("weight","体重"); headMap.put("sex","性别"); String title = "测试"; /* OutputStream outXls = new FileOutputStream("E://a.xls"); System.out.println("正在导出xls...."); Date d = new Date(); ExcelUtil.exportExcel(title,headMap,ja,null,outXls); System.out.println("共"+count+"条数据,执行"+(new Date().getTime()-d.getTime())+"ms"); outXls.close();*/ // OutputStream outXlsx = new FileOutputStream("E://b.xlsx"); System.out.println("正在导出xlsx...."); Date d2 = new Date(); ExcelUtil.exportExcelX(title,headMap,ja,null,0,outXlsx); System.out.println("共"+count+"条数据,执行"+(new Date().getTime()-d2.getTime())+"ms"); outXlsx.close(); }}class Student { private String name; private int age; private Date birthday; private float height; private double weight; private boolean sex; public String getName() { return name; } public void setName(String name) { this.name = name; } public Integer getAge() { return age; } public Date getBirthday() { return birthday; } public void setBirthday(Date birthday) { this.birthday = birthday; } public float getHeight() { return height; } public void setHeight(float height) { this.height = height; } public double getWeight() { return weight; } public void setWeight(double weight) { this.weight = weight; } public boolean isSex() { return sex; } public void setSex(boolean sex) { this.sex = sex; } public void setAge(Integer age) { this.age = age; }}2. 控制器Controller 的写法
//导出配件列表 @RequestMapping(value = "partExport") @ResponseBody public void partExportHttpServletResponse response){ JSONArray ja = ptmpOrderService.selectStatExport();//获取业务数据集 Map<String,String> headMap = ptmpOrderService.getPartStatHeadMap();//获取属性-列头 String title = "配件统计表"; ExcelUtil.downloadExcelFile(title,headMap,ja,response); }3.前端页面的写法（不要用异步方式请求，如$.post）
//可以点击一个按钮事件触发下面的代码进行导出window.open("partExport","_blank");//或者可以提交表单$("#form").attr("action","partExport");$("#form").attr("target","_blank");$("#form").submit();4.POI依赖的jar包（maven pom)
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.14</version></dependency>
5.本地测试
将10w条数据导出到本地硬盘中，HSSF方式用时14s左右，SXSSF方式用时24s左右，尽管如此，但建议使用SXSSF导出.xlsx的excel.
之所以使用JSONArray作为数据集，而没有采用java的集合类，是因为JSONObject 在获取数据的时候是采用 get(key)的方式，正好与属性列对应，这样灵活性高，属性列不必与java对象的字段匹配。而若使用java类，则要应用反射，拼凑get方法，这样更复杂点。
以上所述是小编给大家介绍的POI通用导出Excel(.xls,.xlsx)的方法详解整合，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对网站的支持！
关键词： POI导出Excel POI导出xls POI导出xlsx
