欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2024-02-29 23:01:04
spring boot excel 文件导出 目标: 实现excel文件的直接导出下载,后续开发不需要开发很多代码,直接继承已经写好的代码,增加一个xml配置就可以直接...

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文件导出下载实现代码,希望对大家有所帮助