POI通⽤导出Excel（.xls,.xlsx）的⽅法POI操作EXCEL对象HSSF：操作Excel 97(.xls)格式XSSF：操作Excel 2007 OOXML (.xlsx)格式，操作EXCEL内存占⽤⾼于HSSFSXSSF:从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）和应⽤服务器之间，长时间占⽤连接(⼆进制流转发)，导致负载服务器请求阻塞，不能提供服务。
⼀定要注意⽂件流的关闭防⽌前台（页⾯）连续触发导出EXCEL1.通⽤核⼼导出⼯具类 ExcelUtil.javapackage sy.util;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.io.OutputStream;import ng.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 ermodel.HSSFCell;import ermodel.HSSFCellStyle;import ermodel.HSSFClientAnchor;import ermodel.HSSFComment;import ermodel.HSSFFont;import ermodel.HSSFPatriarch;import ermodel.HSSFRichTextString;import ermodel.HSSFRow;import ermodel.HSSFSheet;import ermodel.HSSFWorkbook;import org.apache.poi.ss.formula.functions.T;import ermodel.CellStyle;import ermodel.DateUtil;import ermodel.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=0titleRow.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 =1for(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=0titleRow.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 =1for(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 导出excelpublic 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) { = 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")@ResponseBodypublic 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对象的字段匹配。
