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)的方法详解整合，希望对大家有所帮助