POI通用导出Excel(.xls,.xlsx)的方法
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)的方法详解整合,希望对大家有所帮助