一、EasyExcel阿里开源excel导出(SXSSF)

参考链接:EasyExcel阿里开源excel导出

 1、建立映射到excel文件的映射类,然后get和set

import com.alibaba.excel.annotation.ExcelProperty;

public class DownloadAjgl {

    @HeadStyle(horizontalAlignment = HorizontalAlignment.CENTER)//表头样式
@ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER)//内容样式
    @ColumnWidth(20)//表头宽度
@ExcelProperty(value
= "账套编码", index = 0)//可以只写列名,不写index会默认按照这个类的属性顺序来写到excel private String ztCode; @ExcelProperty(value = {"账套名称(一级表头)","二级表头","三级表头"}, index = 1) private String ztName; @ExcelProperty("年份") private int year; @ExcelProperty("部门编码") private String bmCode; @ExcelProperty("部门名称") private String bmName; public String getZtCode() { return ztCode; } public void setZtCode(String ztCode) { this.ztCode = ztCode; } public String getZtName() { return ztName; } public void setZtName(String ztName) { this.ztName = ztName; } public int getYear() { return year; } public void setYear(int year) { this.year = year; } public String getBmCode() { return bmCode; } public void setBmCode(String bmCode) { this.bmCode = bmCode; } public String getBmName() { return bmName; } public void setBmName(String bmName) { this.bmName = bmName; } }

2、controller里面

@GetMapping("/exportExcel")
    public void exportExcel(@RequestParam(value = "ztId") int ztId, HttpServletResponse response) throws IOException {

        List<Ajgl> ajglList =ajglRepository.findAllByZtId(ztId);
       
      
            response.setHeader("Content-Disposition", "attachment; filename=Ajgl.xlsx");
            // 响应类型,编码
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

 EasyExcel.write(response.getOutputStream(), DownloadAjgl.class).sheet("案卷导出模板").doWrite(data(ajglList));//data是把数据封装成映射类的自定义方法
            

        
    }

3、前端跟下面  “二、XSSF导出。”一样

4、这点比较重要,如果不做的话会报错
Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is com.alibaba.excel.exception.ExcelGenerateException: Can not close IO.] with root
cause
java.net.SocketTimeoutException: null

(我公司用window10的子系统ubuntu编译运行的)

需要在ubuntu运行几个命令

$ sudo apt-get update
$ sudo apt-get upgrade
$ sudo apt-get install build-essential git unzip zip curl wget vim fontconfig ttf-dejavu fonts-dejavu

主要是第三个命令,只要第三个命令运行成功之后就不会报错了。

二、XSSF导出。

java代码

@GetMapping("/exportExcel")
    public void exportExcel(@RequestParam(value = "ztId") int ztId,
                            HttpServletResponse response) throws IOException {
   
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
    XSSFSheet sheet = xssfWorkbook.createSheet("测试");
    XSSFRow row0 = sheet.createRow(0);
    row0.createCell(0).setCellValue("编码");
    row0.createCell(1).setCellValue("名称");
    List<Ce> ces = ceRepository.findAllByZtId(ztId);
    int num=0;
    for(var it : ces){
      num++;
      XSSFRow row = sheet.createRow(num);
      row.createCell(0).setCellValue(it.getCode());
      row.createCell(1).setCellValue(it.getName());
    }
   response.setHeader("Content-Disposition", "attachment; filename=Cs.xlsx");
     // 响应类型,编码
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    // 将指定的字节写入此输出流
    xssfWorkbook.write(response.getOutputStream());
    response.flushBuffer();
    xssfWorkbook.close();
}

在自定义的request.js中导出download

export const download = (path, name) => {
  const data = options('GET');
  fetch(backend(path), data)
    .then(response => response.blob())
    .then(blob => {
      var url = window.URL.createObjectURL(blob);
      var a = document.createElement('a');
      a.href = url;
      a.download = name;
      document.body.appendChild(a); // for firefox
      a.click();
      a.remove();
    });
};

 

前端代码:

<el-button type="warning" size="small" @click="exportExcel">导出</el-button>

.........

<script>
import {
  download as httpDownload,//封装好的请求
  upload as uploadHeaders,
  get as httpGet,
  delete_ as httpDelete,
  PAGE_SIZE
} from "@/request";
.............

  methods: {
    exportExcel() {
       httpDownload(`/exportExcel/?ztId=${this.ztId}`, "Ajgl.xlsx");
    }
}
.....

 三、HSSFworkbook、XSSFworkbook、SXSSFworkbook区别总结

参考连接:HSSFworkbook,XSSFworkbook,SXSSFworkbook区别总结

1、HSSFWorkbook:是操作Excel2003以前(包括2003)的版本,扩展名是.xls;

poi导出excel最常用的方式;但是此种方式的局限就是导出的行数至多为65535行,超出65536条后系统就会报错。此方式因为行数不足七万行所以一般不会发生内存不足的情况(OOM:OutOfMemoryError)。

2、XSSFWorkbook:是操作Excel2007后的版本,扩展名是.xlsx;

这种形式的出现是为了突破HSSFWorkbook的65535行局限。其对应的是excel2007(1048576行,16384列)扩展名为“.xlsx”,最多可以导出104万行,不过这样就伴随着一个问题---OOM内存溢出,原因是你所创建的book sheet row cell等此时是存在内存的并没有持久化。

3、SXSSFWorkbook:是操作Excel2007后的版本,扩展名是.xlsx;

从POI 3.8版本开始,提供了一种基于XSSF的低内存占用的SXSSF方式。对于大型excel文件的创建,一个关键问题就是,要确保不会内存溢出。其实,就算生成很小的excel(比如几Mb),它用掉的内存是远大于excel文件实际的size的。如果单元格还有各种格式(比如,加粗,背景标红之类的),那它占用的内存就更多了。对于大型excel的创建且不会内存溢出的,就只有SXSSFWorkbook了。它的原理很简单,用硬盘空间换内存(就像hash map用空间换时间一样)。

 

SXSSFWorkbook是streaming版本的XSSFWorkbook,它只会保存最新的excel rows在内存里供查看,在此之前的excel rows都会被写入到硬盘里(Windows电脑的话,是写入到C盘根目录下的temp文件夹)。被写入到硬盘里的rows是不可见的/不可访问的。只有还保存在内存里的才可以被访问到。

SXSSF与XSSF的对比:

a. 在一个时间点上,只可以访问一定数量的数据

b. 不再支持Sheet.clone()

c. 不再支持公式的求值

d. 在使用Excel模板下载数据时将不能动态改变表头,因为这种方式已经提前把excel写到硬盘的了就不能再改了

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

(无关)(额外)请求导出示例:

import {
  get as getToken
} from './token';

export const PAGE_SIZE = 20;

export const backend = (u) => `${process.env.VUE_APP_BASE_API}${u}`;

export const upload = () => {
  return {
    'Authorization': `Bearer ${getToken()}`
  }
}

export const options = (method) => {
  return {
    credentials: 'include',
    headers: {
      'Authorization': `Bearer ${getToken()}`,
      'Content-Type': 'application/json; charset=utf-8'
    },
    method
  };
};

export const get = (path) => fetch(backend(path), options('GET')).then((res) => res.status === 200 ?
  res.json() :
  res.json().then(err => {
    throw err;
  }));

export const delete_ = (path) => fetch(backend(path), options('DELETE')).then((res) => res.status === 200 ?
  res.json() :
  res.json().then(err => {
    throw err;
  }));

// https://github.github.io/fetch/#options
export const post = (path, body) => {
  const data = options('POST');
  data.body = JSON.stringify(body);
  return fetch(backend(path), data).then((res) => res.status === 200 ?
    res.json() :
    res.json().then(err => {
      throw err;
    }));
};

export const patch = (path, body) => {
  const data = options('PATCH');
  data.body = JSON.stringify(body);
  return fetch(backend(path), data).then((res) => res.status === 200 ?
    res.json() :
    res.json().then(err => {
      throw err;
    }));
};

export const put = (path, body) => {
  const data = options('PUT');
  data.body = JSON.stringify(body);
  return fetch(backend(path), data).then((res) => res.status === 200 ?
    res.json() :
    res.json().then(err => {
      throw err;
    }));
};


export const download = (path, name) => {
  const data = options('GET');
  fetch(backend(path), data)
    .then(response => response.blob())
    .then(blob => {
      var url = window.URL.createObjectURL(blob);
      var a = document.createElement('a');
      a.href = url;
      a.download = name;
      document.body.appendChild(a); // for firefox
      a.click();
      a.remove();
    });
};

使用例子:

.........
<script>
import {
  download as httpDownload,
  upload as uploadHeaders,
  get as httpGet,
  delete_ as httpDelete,
  PAGE_SIZE
} from "@/request";
...................