POI通用导出Excel(.xls,.xlsx)的方法

2023-08-23 19:07:04 146

POI操作EXCEL对象
HSSF：操作Excel97(.xls)格式
XSSF：操作Excel2007OOXML(.xlsx)格式，操作EXCEL内存占用高于HSSF
SXSSF:从POI3.8beta3开始支持，基于XSSF，低内存占用。

使用POI的HSSF对象，生成Excel97(.xls)格式，生成的EXCEL不经过压缩直接导出。
线上问题：负载服务器转发请求到应用服务器阻塞,以及内存溢出。
如果系统存在大数据量报表导出，则考虑使用POI的SXSSF进行EXCEL操作。

HSSF生成的Excel97(.xls)格式本身就有每个sheet页不能超过65536条的限制。
XSSF生成Excel2007OOXML(.xlsx)格式，条数增加了，但是导出过程中，内存占用率却高于HSSF.
SXSSF是自3.8-beta3版本后，基于XSSF提供的低内存占用的操作EXCEL对象。其原理是可以设置或者手动将内存中的EXCEL行写到硬盘中，这样内存中只保存了少量的EXCEL行进行操作。

EXCEL的压缩率特别高，能达到80%，12M的文件压缩后才2M左右。如果未经过压缩、不仅会占用用户带宽，且会导致负载服务器（apache）和应用服务器之间，长时间占用连接(二进制流转发)，导致负载服务器请求阻塞，不能提供服务。

一定要注意文件流的关闭

防止前台（页面）连续触发导出EXCEL

1.通用核心导出工具类ExcelUtil.java

packagesy.util;

importjava.io.FileOutputStream;
importjava.io.IOException;
importjava.io.InputStream;
importjava.io.OutputStream;
importjava.lang.reflect.Method;
importjava.text.SimpleDateFormat;
importjava.util.ArrayList;
importjava.util.Date;
importjava.util.Iterator;
importjava.util.LinkedHashMap;
importjava.util.List;
importjava.util.Map;

importorg.apache.poi.hpsf.SummaryInformation;
importorg.apache.poi.hssf.usermodel.HSSFCell;
importorg.apache.poi.hssf.usermodel.HSSFCellStyle;
importorg.apache.poi.hssf.usermodel.HSSFClientAnchor;
importorg.apache.poi.hssf.usermodel.HSSFComment;
importorg.apache.poi.hssf.usermodel.HSSFFont;
importorg.apache.poi.hssf.usermodel.HSSFPatriarch;
importorg.apache.poi.hssf.usermodel.HSSFRichTextString;
importorg.apache.poi.hssf.usermodel.HSSFRow;
importorg.apache.poi.hssf.usermodel.HSSFSheet;
importorg.apache.poi.hssf.usermodel.HSSFWorkbook;
importorg.apache.poi.ss.formula.functions.T;
importorg.apache.poi.ss.usermodel.CellStyle;
importorg.apache.poi.ss.usermodel.DateUtil;
importorg.apache.poi.ss.usermodel.Font;
importorg.apache.poi.ss.util.CellRangeAddress;
importorg.apache.poi.xssf.streaming.SXSSFCell;
importorg.apache.poi.xssf.streaming.SXSSFRow;
importorg.apache.poi.xssf.streaming.SXSSFSheet;
importorg.apache.poi.xssf.streaming.SXSSFWorkbook;

importcom.alibaba.fastjson.JSONArray;
importcom.alibaba.fastjson.JSONObject;

publicclassExcelUtil{
publicstaticStringNO_DEFINE="no_define";//未定义的字段
publicstaticStringDEFAULT_DATE_PATTERN="yyyy年MM月dd日";//默认日期格式
publicstaticintDEFAULT_COLOUMN_WIDTH=17;
/**
*导出Excel97(.xls)格式，少量数据
*@paramtitle标题行
*@paramheadMap属性-列名
*@paramjsonArray数据集
*@paramdatePattern日期格式，null则用默认日期格式
*@paramcolWidth列宽默认至少17个字节
*@paramout输出流
*/
publicstaticvoidexportExcel(Stringtitle,MapheadMap,JSONArrayjsonArray,StringdatePattern,intcolWidth,OutputStreamout){
if(datePattern==null)datePattern=DEFAULT_DATE_PATTERN;
//声明一个工作薄
HSSFWorkbookworkbook=newHSSFWorkbook();
workbook.createInformationProperties();
workbook.getDocumentSummaryInformation().setCompany("*****公司");
SummaryInformationsi=workbook.getSummaryInformation();
si.setAuthor("JACK");//填加xls文件作者信息
si.setApplicationName("导出程序");//填加xls文件创建程序信息
si.setLastAuthor("最后保存者信息");//填加xls文件最后保存者信息
si.setComments("JACKisaprogrammer!");//填加xls文件作者信息
si.setTitle("POI导出Excel");//填加xls文件标题信息
si.setSubject("POI导出Excel");//填加文件主题信息
si.setCreateDateTime(newDate());
//表头样式
HSSFCellStyletitleStyle=workbook.createCellStyle();
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFonttitleFont=workbook.createFont();
titleFont.setFontHeightInPoints((short)20);
titleFont.setBoldweight((short)700);
titleStyle.setFont(titleFont);
//列头样式
HSSFCellStyleheaderStyle=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);
HSSFFontheaderFont=workbook.createFont();
headerFont.setFontHeightInPoints((short)12);
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerStyle.setFont(headerFont);
//单元格样式
HSSFCellStylecellStyle=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);
HSSFFontcellFont=workbook.createFont();
cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
cellStyle.setFont(cellFont);
//生成一个(带标题)表格
HSSFSheetsheet=workbook.createSheet();
//声明一个画图的顶级管理器
HSSFPatriarchpatriarch=sheet.createDrawingPatriarch();
//定义注释的大小和位置,详见文档
HSSFCommentcomment=patriarch.createComment(newHSSFClientAnchor(0,
0,0,0,(short)4,2,(short)6,5));
//设置注释内容
comment.setString(newHSSFRichTextString("可以在POI中添加注释！"));
//设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
comment.setAuthor("JACK");
//设置列宽
intminBytes=colWidthiter=headMap.keySet().iterator();iter
.hasNext();){
StringfieldName=iter.next();

properties[ii]=fieldName;
headers[ii]=fieldName;

intbytes=fieldName.getBytes().length;
arrColWidth[ii]=bytesheadMap,JSONArrayjsonArray,StringdatePattern,intcolWidth,OutputStreamout){
if(datePattern==null)datePattern=DEFAULT_DATE_PATTERN;
//声明一个工作薄
SXSSFWorkbookworkbook=newSXSSFWorkbook(1000);//缓存
workbook.setCompressTempFiles(true);
//表头样式
CellStyletitleStyle=workbook.createCellStyle();
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
FonttitleFont=workbook.createFont();
titleFont.setFontHeightInPoints((short)20);
titleFont.setBoldweight((short)700);
titleStyle.setFont(titleFont);
//列头样式
CellStyleheaderStyle=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);
FontheaderFont=workbook.createFont();
headerFont.setFontHeightInPoints((short)12);
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerStyle.setFont(headerFont);
//单元格样式
CellStylecellStyle=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);
FontcellFont=workbook.createFont();
cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
cellStyle.setFont(cellFont);
//生成一个(带标题)表格
SXSSFSheetsheet=workbook.createSheet();
//设置列宽
intminBytes=colWidthiter=headMap.keySet().iterator();iter
.hasNext();){
StringfieldName=iter.next();

properties[ii]=fieldName;
headers[ii]=headMap.get(fieldName);

intbytes=fieldName.getBytes().length;
arrColWidth[ii]=bytesheadMap,JSONArrayja,HttpServletResponseresponse){
try{
ByteArrayOutputStreamos=newByteArrayOutputStream();
ExcelUtil.exportExcelX(title,headMap,ja,null,0,os);
byte[]content=os.toByteArray();
InputStreamis=newByteArrayInputStream(content);
//设置response参数，可以打开下载页面
response.reset();

response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
response.setHeader("Content-Disposition","attachment;filename="+newString((title+".xlsx").getBytes(),"iso-8859-1"));
response.setContentLength(content.length);
ServletOutputStreamoutputStream=response.getOutputStream();
BufferedInputStreambis=newBufferedInputStream(is);
BufferedOutputStreambos=newBufferedOutputStream(outputStream);
byte[]buff=newbyte[8192];
intbytesRead;
while(-1!=(bytesRead=bis.read(buff,0,buff.length))){
bos.write(buff,0,bytesRead);

}
bis.close();
bos.close();
outputStream.flush();
outputStream.close();
}catch(Exceptione){
e.printStackTrace();
}
}
publicstaticvoidmain(String[]args)throwsIOException{
intcount=100000;
JSONArrayja=newJSONArray();
for(inti=0;i<100000;i++){
Students=newStudent();
s.setName("POI"+i);
s.setAge(i);
s.setBirthday(newDate());
s.setHeight(i);
s.setWeight(i);
s.setSex(i/2==0?false:true);
ja.add(s);
}
MapheadMap=newLinkedHashMap();
headMap.put("name","姓名");
headMap.put("age","年龄");
headMap.put("birthday","生日");
headMap.put("height","身高");
headMap.put("weight","体重");
headMap.put("sex","性别");

Stringtitle="测试";
/*
OutputStreamoutXls=newFileOutputStream("E://a.xls");
System.out.println("正在导出xls....");
Dated=newDate();
ExcelUtil.exportExcel(title,headMap,ja,null,outXls);
System.out.println("共"+count+"条数据,执行"+(newDate().getTime()-d.getTime())+"ms");
outXls.close();*/
//
OutputStreamoutXlsx=newFileOutputStream("E://b.xlsx");
System.out.println("正在导出xlsx....");
Dated2=newDate();
ExcelUtil.exportExcelX(title,headMap,ja,null,0,outXlsx);
System.out.println("共"+count+"条数据,执行"+(newDate().getTime()-d2.getTime())+"ms");
outXlsx.close();

}
}
classStudent{
privateStringname;
privateintage;
privateDatebirthday;
privatefloatheight;
privatedoubleweight;
privatebooleansex;

publicStringgetName(){
returnname;
}

publicvoidsetName(Stringname){
this.name=name;
}

publicIntegergetAge(){
returnage;
}

publicDategetBirthday(){
returnbirthday;
}

publicvoidsetBirthday(Datebirthday){
this.birthday=birthday;
}

publicfloatgetHeight(){
returnheight;
}

publicvoidsetHeight(floatheight){
this.height=height;
}

publicdoublegetWeight(){
returnweight;
}

publicvoidsetWeight(doubleweight){
this.weight=weight;
}

publicbooleanisSex(){
returnsex;
}

publicvoidsetSex(booleansex){
this.sex=sex;
}

publicvoidsetAge(Integerage){
this.age=age;
}
}

2.控制器Controller的写法

//导出配件列表
@RequestMapping(value="partExport")
@ResponseBody
publicvoidpartExportHttpServletResponseresponse){

JSONArrayja=ptmpOrderService.selectStatExport();//获取业务数据集
MapheadMap=ptmpOrderService.getPartStatHeadMap();//获取属性-列头
Stringtitle="配件统计表";
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包（mavenpom)


org.apache.poi
poi-ooxml
3.14

5.本地测试

将10w条数据导出到本地硬盘中，HSSF方式用时14s左右，SXSSF方式用时24s左右，尽管如此，但建议使用SXSSF导出.xlsx的excel.

之所以使用JSONArray作为数据集，而没有采用java的集合类，是因为JSONObject在获取数据的时候是采用get(key)的方式，正好与属性列对应，这样灵活性高，属性列不必与java对象的字段匹配。而若使用java类，则要应用反射，拼凑get方法，这样更复杂点。

以上所述是小编给大家介绍的POI通用导出Excel(.xls,.xlsx)的方法详解整合，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持！

POI通用导出Excel(.xls,.xlsx)的方法

热门推荐

随机推荐