Spring Boot Excel文件导出下载实现代码

spring boot excel 文件导出

目标：

实现excel文件的直接导出下载，后续开发不需要开发很多代码，直接继承已经写好的代码，增加一个xml配置就可以直接导出。

实现：

1、抽象类 baseexcelview 继承 webmvc 的  abstractxlsxstreamingview 抽象类， abstractxlsxstreamingview 是webmvc继承了最顶层view接口，是可以直接大量数据导出的不会造成内存泄漏问题，即 sxssfworkbook 解决了内存问题， 导出只支持xlsx类型文件。

抽象类代码 baseexcelview ：

public abstract class baseexcelview extends abstractxlsxstreamingview {
  private static final logger logger = loggerfactory.getlogger(baseexcelview.class);
  /**
   * 获取导出文件名
   *
   * @return
   */
  abstract protected string getfilename();
  /**
   * 获取表单名称
   *
   * @return
   */
  abstract protected string getsheetname();
  /**
   * 获取标题栏名称
   *
   * @return
   */
  abstract protected string[] gettitles();
  /**
   * 获取列宽
   *
   * @return
   */
  abstract protected short[] getcolumnwidths();
  /**
   * 构造内容单元格
   *
   * @param sheet
   */
  abstract protected void buildcontentcells(sheet sheet);
  @override
  protected void buildexceldocument(
      map<string, object> model, workbook workbook, httpservletrequest request, httpservletresponse response)
      throws exception {
    // 构造标题单元格 sxssfworkbook
    sheet sheet = buildtitlecells(workbook);
    // 构造内容单元格
    buildcontentcells(sheet);
    // 设置响应头
    setresponsehead(request, response);
  }
  /**
   * 设置响应头
   *
   * @param response
   * @throws ioexception
   */
  protected void setresponsehead(httpservletrequest request,
                  httpservletresponse response) throws ioexception {
    // 文件名
    string filename = getfilename();
    string useragent = request.getheader("user-agent").tolowercase();
    logger.info("客户端请求头内容:");
    logger.info("user-agent\t值: {}", useragent);
    if (useragent != null) {
      if (useragent.contains("firefox")) {
        // firefox有默认的备用字符集是西欧字符集
        filename = new string(filename.getbytes("utf-8"), "iso8859-1");
      } else if (useragent.contains("webkit") && (useragent.contains("chrome") || useragent.contains("safari"))) {
        // webkit核心的浏览器,主流的有chrome,safari,360
        filename = new string(filename.getbytes("utf-8"), "iso8859-1");
      } else {
        // 新老版本的ie都可直接用url编码工具编码后输出正确的名称,无乱码
        filename = urlencoder.encode(filename, "utf-8");
      }
    }
    //响应头信息
    response.setcharacterencoding("utf-8");
    response.setcontenttype("application/ms-excel; charset=utf-8");
    response.setheader("content-disposition", "attachment; filename=" + filename + ".xlsx");
  }
  /**
   * 构造标题单元格
   *
   * @param
   * @return
   */
  protected sheet buildtitlecells(workbook workbook) {
    // 表单名称
    string sheetname = getsheetname();
    // 标题名称
    string[] titles = gettitles();
    // 列宽
    short[] colwidths = getcolumnwidths();
    // 创建表格
    sheet sheet = workbook.createsheet(sheetname);
    // 标题单元格样式
    cellstyle titlestyle = getheadstyle(workbook);
    // 默认内容单元格样式
    cellstyle contentstyle = getbodystyle(workbook);
    // 标题行
    row titlerow = sheet.createrow(0);
    // 创建标题行单元格
    for (int i = 0; i < titles.length; i++) {
      // 标题单元格
      cell cell = titlerow.createcell((short) i);
      cell.setcelltype(celltype.string);
      cell.setcellvalue(new xssfrichtextstring(titles[i]));
      cell.setcellstyle(titlestyle);
      // 设置列宽
      sheet.setcolumnwidth((short) i, (short) (colwidths[i] * 256));
      // 设置列默认样式
      sheet.setdefaultcolumnstyle((short) i, contentstyle);
    }
    return sheet;
  }
  /**
   * 设置表头的单元格样式
   */
  public cellstyle getheadstyle(workbook workbook) {
    // 创建单元格样式
    cellstyle cellstyle = workbook.createcellstyle();
    // 设置单元格的背景颜色为淡蓝色
    cellstyle.setfillforegroundcolor(indexedcolors.pale_blue.index);
    // 设置填充字体的样式
    cellstyle.setfillpattern(fillpatterntype.solid_foreground);
    // 设置单元格居中对齐
    cellstyle.setalignment(horizontalalignment.center);
    // 设置单元格垂直居中对齐
    cellstyle.setverticalalignment(verticalalignment.center);
    // 创建单元格内容显示不下时自动换行
    cellstyle.setwraptext(true);
    // 设置单元格字体样式
    font font = workbook.createfont();
    // 字号
    font.setfontheightinpoints((short) 12);
    // 加粗
    font.setbold(true);
    // 将字体填充到表格中去
    cellstyle.setfont(font);
    // 设置单元格边框为细线条（上下左右）
    cellstyle.setborderleft(borderstyle.thin);
    cellstyle.setborderbottom(borderstyle.thin);
    cellstyle.setborderright(borderstyle.thin);
    cellstyle.setbordertop(borderstyle.thin);
    return cellstyle;
  }
  /**
   * 设置表体的单元格样式
   */
  public cellstyle getbodystyle(workbook workbook) {
    // 创建单元格样式
    cellstyle cellstyle = workbook.createcellstyle();
    // 设置单元格居中对齐
    cellstyle.setalignment(horizontalalignment.center);
    // 设置单元格居中对齐
    cellstyle.setverticalalignment(verticalalignment.center);
    // 创建单元格内容不显示自动换行
    cellstyle.setwraptext(true);
    //设置单元格字体样式字体
    font font = workbook.createfont();
    // 字号
    font.setfontheightinpoints((short) 10);
    // 将字体添加到表格中去
    cellstyle.setfont(font);
    // 设置单元格边框为细线条
    cellstyle.setborderleft(borderstyle.thin);
    cellstyle.setborderbottom(borderstyle.thin);
    cellstyle.setborderright(borderstyle.thin);
    cellstyle.setbordertop(borderstyle.thin);
    return cellstyle;
  }
}

excel导出实现 1: 可以直接继承 baseexcelview  实现定义的方法 eg:

public class checkexcelview extends baseexcelview {
  private list<t> vo;
  public checkexcelview(list<t> vo) {
   this.vo= vo;
  }
  @override
  protected string getfilename() {
   string time = dateutils.getlocalfulldatetime14();
   return "导出文件" + time;
  }
  @override
  protected string getsheetname() {
    return "报表";
  }
  @override
  protected string[] gettitles() {
   return new string[] { "申请时间"};
  }
  @override
  protected short[] getcolumnwidths() {
   return new short[] { 20};
  }
  @override
  protected void buildcontentcells(sheet sheet) {
   decimalformat df = new decimalformat("0.00");
   int rownum = 1;
   for (t o : vo) {
     row crow = sheet.createrow(rownum++);
     crow.createcell(0).setcellvalue(o.getapplicationdate()));
   }
  }
}

导出实现 2： xml配置导出 

1、需要定义xml的配置 export-config.xml

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <table id="demo" name="测试">
    <columns>
      <column id="name" name="名称" width="40"></column>
    </columns>
  </table>
</configuration>

2、xml解析配置   

@root
public class export {
  @elementlist(entry = "table", inline = true)
  private list<table> table;
  public list<table> gettable() {
    return table;
  }
  public void settable(list<table> table) {
    this.table = table;
  }
  public static class table {
    @attribute
    private string id;
    @attribute
    private string name;
    @elementlist(entry = "column")
    private list<column> columns;
    public string getid() {
      return id;
    }
    public void setid(string id) {
      this.id = id;
    }
    public string getname() {
      return name;
    }
    public void setname(string name) {
      this.name = name;
    }
    public list<column> getcolumns() {
      return columns;
    }
    public void setcolumns(list<column> columns) {
      this.columns = columns;
    }
  }
  public static class column {
    @attribute
    private string id;
    @attribute
    private string name;
    @attribute
    private short width;
    @attribute(required = false)
    private string mapping;
    public string getid() {
      return id;
    }
    public void setid(string id) {
      this.id = id;
    }
    public string getname() {
      return name;
    }
    public void setname(string name) {
      this.name = name;
    }
    public string getmapping() {
      return mapping;
    }
    public void setmapping(string mapping) {
      this.mapping = mapping;
    }
    public short getwidth() {
      return width;
    }
    public void setwidth(short width) {
      this.width = width;
    }
  }
}

3、解析xml方法配置

@service
public class iexportservice {
  private export tables;
  private map<string, export.table> tablemap;
  @suppresswarnings("rawtypes")
  @postconstruct
  public void init() throws exception {
    inputstream inputstream = this.getclass().getclassloader().getresourceasstream("export-config.xml");
    serializer serializer = new persister();
    tables = serializer.read(export.class, inputstream);
    tablemap = new hashmap<>();
    for (export.table table : tables.gettable()) {
      tablemap.put(table.getid(), table);
    }
  }
  public export.table gettable(string key) {
    return tablemap.get(key);
  }
}

4、导出基础  excelexportview 代码实现

public class excelexportview extends baseexcelview {
  private string[] titles;
  private short[] columnwidths;
  list<map<string, object>> results;
  private export.table table;
  private iexportservice iexportservice;
  @override
  protected string getfilename() {
    return table.getname();
  }
  @override
  protected string getsheetname() {
    return table.getname();
  }
  @override
  protected string[] gettitles() {
    return this.titles;
  }
  @override
  protected short[] getcolumnwidths() {
    return this.columnwidths;
  }
  public excelexportview() {
    this.iexportservice = applicationcontextprovider.getbean(iexportservice.class);
  }
  @override
  protected void buildcontentcells(sheet sheet) {
    int dataindex = 1;
    if(collectionutils.isempty(results)){
      return;
    }
    for (map<string, object> data : results) {
      row row = sheet.createrow(dataindex++);
      for (int i = 0; i < table.getcolumns().size(); i++) {
        export.column column = table.getcolumns().get(i);
        cell cell = row.createcell(i);
        object value = data.get(column.getid());
        if (value == null) {
          value = "";
        }
        cell.setcellvalue(new xssfrichtextstring(value.tostring()));
      }
    }
  }
  public void exportexcel(string key, list<map<string, object>> results) {
    this.table = iexportservice.gettable(key);
    if (null == table) {
      return;
    }
    this.results = results;
    this.titles = new string[table.getcolumns().size()];
    this.columnwidths = new short[table.getcolumns().size()];
    for (int i = 0; i < table.getcolumns().size(); i++) {
      export.column column = table.getcolumns().get(i);
      titles[i] = column.getname();
      columnwidths[i] = column.getwidth();
    }
  }
}

最后：导出controller代码实现 

@requestmapping(path = "/export", method = requestmethod.get, produces = "application/octet-stream;charset=utf-8")
public @responsebody
modelandview export(){
  long logincomid = logincontext.getcompany().getid();
  list<t> list = new arraylist<>();
  excelexportview exportview = new excelexportview();
  exportview.exportexcel("xml中表的id", beanutils.objecttomaplist(list));
  return new modelandview(exportview);
<em id="__mcedel"><em id="__mcedel">}</em></em>

总结

以上所述是小编给大家介绍的spring boot  excel文件导出下载实现代码，希望对大家有所帮助