Spring Boot 入门（十二）：报表导出，对比poi、jxl和esayExcel的效率

<!--begin poi-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>${poi.version}</version>
</dependency> <dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>${poi.version}</version>
</dependency>
<!--end poi-->
<!--begin jxl-->
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.10</version>
</dependency>
<!--end jxl-->
<!--begin esayExcel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>1.1.2-beat1</version>
</dependency>
<!--end esayExcel-->

<form class="form-horizontal">
<div class="form-group clearfix">
<button type="button" onclick="report_poi();" class="btn btn-sm btn-warning">poi导出</button>
<button type="button" onclick="report_jxl();" class="btn btn-sm btn-danger">jxl导出</button>
<button type="button" onclick="report_esay_excel();" class="btn btn-sm btn-primary">esayExcel导出</button>
</div>
</form>

function report_poi() {
window.location.href = "/conf/report/reportPoi";
} function report_jxl() {
window.location.href = "/conf/report/reportJxl";
} function report_esay_excel() {
window.location.href = "/conf/report/reportEsayExcel";
}

// 报表的title
private static final String[] title = {"id", "报表id"
, "col1", "col2", "col3", "col4", "col5", "col6", "col7", "col8", "col9", "col10"
, "col11", "col12", "col13", "col14", "col15", "col16", "col17", "col18", "col19", "col20"
, "col21", "col22", "col23", "col24", "col25", "col26", "col27", "col28", "col29", "col30"};

@Log("poi导出报表")
@RequestMapping(value = "/reportPoi", method = RequestMethod.GET)
@ResponseBody
public String reportPoi(HttpServletResponse response) throws Exception {
//excel文件名
log.info("poi方式开始导出数据");
response.reset();// 清空输出流
response.setHeader("Content-Disposition", "attachment;filename=poi.xlsx");
response.setContentType("application/octet-stream;charset=UTF-8");
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
//sheet页中的行数,行数数据；
List<Report> list = reportService.getAllDate();
long start = System.currentTimeMillis();
// 开始导出excel
SXSSFWorkbook wb = new SXSSFWorkbook(1000);
SXSSFSheet sheet = wb.createSheet("poi");
CellStyle style = wb.createCellStyle();
style.setWrapText(true);
Row row = sheet.createRow(0);
Cell cell = null;
for (int i = 0; i < title.length; i++) {
cell = row.createCell(i);
cell.setCellValue(title[i]);
cell.setCellStyle(style);
}
for (int i = 0; i < list.size(); i++) {
Report report = list.get(i);
row = sheet.createRow(i + 1);
row.createCell(0).setCellValue(report.getId());
row.createCell(1).setCellValue(report.getReportId());
row.createCell(2).setCellValue(report.getCol1());
row.createCell(3).setCellValue(report.getCol2());
row.createCell(4).setCellValue(report.getCol3());
row.createCell(5).setCellValue(report.getCol4());
row.createCell(6).setCellValue(report.getCol5());
row.createCell(7).setCellValue(report.getCol6());
row.createCell(8).setCellValue(report.getCol7());
row.createCell(9).setCellValue(report.getCol8());
row.createCell(10).setCellValue(report.getCol9());
row.createCell(11).setCellValue(report.getCol10());
row.createCell(12).setCellValue(report.getCol11());
row.createCell(13).setCellValue(report.getCol12());
row.createCell(14).setCellValue(report.getCol13());
row.createCell(15).setCellValue(report.getCol14());
row.createCell(16).setCellValue(report.getCol15());
row.createCell(17).setCellValue(report.getCol16());
row.createCell(18).setCellValue(report.getCol17());
row.createCell(19).setCellValue(report.getCol18());
row.createCell(20).setCellValue(report.getCol19());
row.createCell(21).setCellValue(report.getCol20());
row.createCell(22).setCellValue(report.getCol21());
row.createCell(23).setCellValue(report.getCol22());
row.createCell(24).setCellValue(report.getCol23());
row.createCell(25).setCellValue(report.getCol24());
row.createCell(26).setCellValue(report.getCol25());
row.createCell(27).setCellValue(report.getCol26());
row.createCell(28).setCellValue(report.getCol27());
row.createCell(29).setCellValue(report.getCol28());
row.createCell(30).setCellValue(report.getCol29());
row.createCell(31).setCellValue(report.getCol30()); }
long millis = System.currentTimeMillis() - start;
OutputStream os = response.getOutputStream();
wb.write(os);
os.flush();
os.close();
wb.dispose();
log.info("POI导出报表，数据量：{},时间：{}ms", list.size(), millis);
return "";
}

@Log("jxl导出报表")
@RequestMapping(value = "/reportJxl")
@ResponseBody
public String reportJxl(HttpServletResponse response) throws Exception {
log.info("jxl方式开始导出数据");
try {
long start = System.currentTimeMillis();
OutputStream os = response.getOutputStream();// 取得输出流
response.reset();// 清空输出流
response.setHeader("Content-disposition", "attachment; filename=" + java.net.URLEncoder.encode("jxl", "UTF-8") + "Excel.xlsx");// 设定输出文件头
response.setContentType("application/msexcel");// 定义输出类型
WritableWorkbook workbook = jxl.Workbook.createWorkbook(os); // 建立excel文件
WritableSheet sheet1 = workbook.createSheet("jxl", 0);//第一个sheet名
// 通过函数WritableFont（）设置字体样式
// 第一个参数表示所选字体
// 第二个参数表示字体大小
// 第三个参数表示粗体样式，有BOLD和NORMAL两种样式
// 第四个参数表示是否斜体
// 第五个参数表示下划线样式
// 第六个参数表示颜色样式
WritableFont wf = new WritableFont(WritableFont.TIMES, 16, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
CellFormat cf = new WritableCellFormat(wf);
// 设置表头
for (int i = 0; i < title.length; i++) {
sheet1.addCell(new Label(i, 0, title[i], cf));
}
List<Report> list = reportService.getAllDate();
//根据内容自动设置列宽(内容为英文时)
// 生成主体内容
for (int i = 0; i < list.size(); i++) {
Report report = list.get(i);
sheet1.addCell(new Label(0, i + 1, report.getId().toString()));
sheet1.addCell(new Label(1, i + 1, report.getReportId()));
sheet1.addCell(new Label(2, i + 1, report.getCol1().toString()));
sheet1.addCell(new Label(3, i + 1, report.getCol2().toString()));
sheet1.addCell(new Label(4, i + 1, report.getCol3().toString()));
sheet1.addCell(new Label(5, i + 1, report.getCol4().toString()));
sheet1.addCell(new Label(6, i + 1, report.getCol5().toString()));
sheet1.addCell(new Label(7, i + 1, report.getCol6().toString()));
sheet1.addCell(new Label(8, i + 1, report.getCol7().toString()));
sheet1.addCell(new Label(9, i + 1, report.getCol8().toString()));
sheet1.addCell(new Label(10, i + 1, report.getCol9().toString()));
sheet1.addCell(new Label(11, i + 1, report.getCol10().toString()));
sheet1.addCell(new Label(12, i + 1, report.getCol11()));
sheet1.addCell(new Label(13, i + 1, report.getCol12()));
sheet1.addCell(new Label(14, i + 1, report.getCol13()));
sheet1.addCell(new Label(15, i + 1, report.getCol14()));
sheet1.addCell(new Label(16, i + 1, report.getCol15()));
sheet1.addCell(new Label(17, i + 1, report.getCol16()));
sheet1.addCell(new Label(18, i + 1, report.getCol17()));
sheet1.addCell(new Label(19, i + 1, report.getCol18()));
sheet1.addCell(new Label(20, i + 1, report.getCol19()));
sheet1.addCell(new Label(21, i + 1, report.getCol20()));
sheet1.addCell(new Label(22, i + 1, report.getCol21().toString()));
sheet1.addCell(new Label(23, i + 1, report.getCol22().toString()));
sheet1.addCell(new Label(24, i + 1, report.getCol23().toString()));
sheet1.addCell(new Label(25, i + 1, report.getCol24().toString()));
sheet1.addCell(new Label(26, i + 1, report.getCol25().toString()));
sheet1.addCell(new Label(27, i + 1, report.getCol26().toString()));
sheet1.addCell(new Label(28, i + 1, report.getCol27().toString()));
sheet1.addCell(new Label(29, i + 1, report.getCol28().toString()));
sheet1.addCell(new Label(30, i + 1, report.getCol29().toString()));
sheet1.addCell(new Label(31, i + 1, report.getCol30().toString()));
}
workbook.write(); // 写入文件
workbook.close();
os.close(); // 关闭流
long millis = System.currentTimeMillis() - start;
log.info("jxl导出报表，数据量：{},时间：{}ms", list.size(), millis);
} catch (Exception e) {
log.error("jxl导出报表报错", e);
}
return "";
}

@Log("esayExcel导出报表")
@RequestMapping(value = "/reportEsayExcel")
@ResponseBody
public String reportEsayExcel(HttpServletResponse response) throws Exception {
log.info("esayExcel方式开始导出数据");
long start = System.currentTimeMillis(); try {
ExcelWriter writer = null;
OutputStream outputStream = response.getOutputStream();
//添加响应头信息
response.setHeader("Content-disposition", "attachment; filename= esayExcel.xlsx");
response.setContentType("application/msexcel;charset=UTF-8");//设置类型
response.setHeader("Pragma", "No-cache");//设置头
response.setHeader("Cache-Control", "no-cache");//设置头
response.setDateHeader("Expires", 0);//设置日期头 //实例化 ExcelWriter
writer = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX, true); //实例化表单
Sheet sheet = new Sheet(1, 0, Report.class);
sheet.setSheetName("esayExcel"); //获取数据
List<Report> list = reportService.getAllDate(); //输出
writer.write(list, sheet);
writer.finish();
outputStream.flush();
long millis = System.currentTimeMillis() - start;
log.info("sayExcel导出报表，数据量：{},时间：{}ms", list.size(), millis);
} catch (IOException e) {
log.error("esayExcel导出excel报错", e);
} finally {
try {
response.getOutputStream().close();
} catch (IOException e) {
log.error("esayExcel关闭资源", e);
}
}
return "";
}

package com.learn.hello.system.common.listener; import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.formula.functions.T; import java.util.ArrayList;
import java.util.List; /**
* @ClassName ExcelListener
* @Deccription 通过esayExcel的方式导出excel
* @Author DZ
* @Date 2020/1/20 22:28
**/
@Slf4j
public class ExcelListener extends AnalysisEventListener<T> {
//可以通过实例获取该值
private final List<T> rows = new ArrayList<>(); @Override
public void invoke(T object, AnalysisContext analysisContext) {
//数据存储到list，供批量处理，或后续自己业务逻辑处理。
rows.add(object);
} @Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
} public List<T> getRows() {
return rows;
}
}

package com.learn.hello.modules.entity; import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data; import javax.persistence.*;
import java.util.Date; @Data
@Table(name = "t_report")
public class Report extends BaseRowModel {
@ExcelProperty(value = "id", index = 0)
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id; /**
* 报表id
*/
@ExcelProperty(value = "报表id", index = 1)
@Column(name = "report_id")
private String reportId; @ExcelProperty(value = "col1", index = 2)
private Integer col1; @ExcelProperty(value = "col2", index = 3)
private Integer col2; @ExcelProperty(value = "col3", index = 4)
private Integer col3; @ExcelProperty(value = "col4", index = 5)
private Integer col4; @ExcelProperty(value = "col5", index = 6)
private Integer col5; @ExcelProperty(value = "col6", index = 7)
private Integer col6; @ExcelProperty(value = "col7", index = 8)
private Integer col7; @ExcelProperty(value = "col8", index = 9)
private Integer col8; @ExcelProperty(value = "col9", index = 10)
private Integer col9; @ExcelProperty(value = "col10", index = 11)
private Integer col10; @ExcelProperty(value = "col11", index = 12)
private String col11; @ExcelProperty(value = "col12", index = 13)
private String col12; @ExcelProperty(value = "col13", index = 14)
private String col13; @ExcelProperty(value = "col14", index = 15)
private String col14; @ExcelProperty(value = "col15", index = 16)
private String col15; @ExcelProperty(value = "col16", index = 17)
private String col16; @ExcelProperty(value = "col17", index = 18)
private String col17; @ExcelProperty(value = "col18", index = 19)
private String col18; @ExcelProperty(value = "col19", index = 20)
private String col19; @ExcelProperty(value = "col20", index = 21)
private String col20; @ExcelProperty(value = "col21", index = 22)
private Date col21; @ExcelProperty(value = "col22", index = 23)
private Date col22; @ExcelProperty(value = "col23", index = 24)
private Date col23; @ExcelProperty(value = "col24", index = 25)
private Date col24; @ExcelProperty(value = "col25", index = 26)
private Date col25; @ExcelProperty(value = "col26", index = 27)
private Date col26; @ExcelProperty(value = "col27", index = 28)
private Date col27; @ExcelProperty(value = "col28", index = 29)
private Date col28; @ExcelProperty(value = "col29", index = 30)
private Date col29; @ExcelProperty(value = "col30", index = 31)
private Date col30; }