- excel的导出功能,将数据从数据库中查询出来,生成对应的excel文件,封装内容,以IO流的形式传递给前端
- easyexcel源码:https://github.com/alibaba/easyexcel
- 需要的工具类 ExcelException.java ,ExcelListener.java ,ExcelUtil.java,ExcelWriterFactory.java
- git上源码都有可以去copy下来,懒人的话我下面会有工具类的源码
工具用法
**
1.导入依赖
**
<!--alibaba,easyExecl工具依赖-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>1.1.2-beta4</version>
</dependency>
2.下载源码工具类
package com.bjzhianjia.scp.cgp.util.easyExeclUtil;
/**
* Created with IntelliJ IDEA
*
* @Author yuanhaoyue swithaoy@gmail.com
* @Description Excel 解析 Exception
* @Date 2018-06-06
* @Time 15:56
*/
public class ExcelException extends RuntimeException{
public ExcelException(String message) {
super(message);
}
}
package com.bjzhianjia.scp.cgp.util.easyExeclUtil;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import java.util.ArrayList;
import java.util.List;
/**
* Created with IntelliJ IDEA
*
* @Author yuanhaoyue swithaoy@gmail.com
* @Description 监听类,可以自定义
* @Date 2018-06-05
* @Time 16:58
*/
public class ExcelListener extends AnalysisEventListener {
//自定义用于暂时存储data。
//可以通过实例获取该值
private List<Object> datas = new ArrayList<>();
/**
* 通过 AnalysisContext 对象还可以获取当前 sheet,当前行等数据
*/
@Override
public void invoke(Object object, AnalysisContext context) {
//数据存储到list,供批量处理,或后续自己业务逻辑处理。
datas.add(object);
//根据业务自行 do something
doSomething();
/*
如数据过大,可以进行定量分批处理
if(datas.size()<=100){
datas.add(object);
}else {
doSomething();
datas = new ArrayList<Object>();
}
*/
}
/**
* 根据业务自行实现该方法
*/
private void doSomething() {
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
/*
datas.clear();
解析结束销毁不用的资源
*/
}
public List<Object> getDatas() {
return datas;
}
public void setDatas(List<Object> datas) {
this.datas = datas;
}
}
package com.bjzhianjia.scp.cgp.util.easyExeclUtil;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.List;
/**
* Created with IntelliJ IDEA
*
* @Author yuanhaoyue swithaoy@gmail.com
* @Description 工具类
* @Date 2018-06-06
* @Time 14:07
*/
public class ExcelUtil {
/**
* 读取 Excel(多个 sheet)
*
* @param excel 文件
* @param rowModel 实体类映射,继承 BaseRowModel 类
* @return Excel 数据 list
*/
public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel) {
ExcelListener excelListener = new ExcelListener();
ExcelReader reader = getReader(excel, excelListener);
if (reader == null) {
return null;
}
for (Sheet sheet : reader.getSheets()) {
if (rowModel != null) {
sheet.setClazz(rowModel.getClass());
}
reader.read(sheet);
}
return excelListener.getDatas();
}
/**
* 读取某个 sheet 的 Excel
*
* @param excel 文件
* @param rowModel 实体类映射,继承 BaseRowModel 类
* @param sheetNo sheet 的序号 从1开始
* @return Excel 数据 list
*/
public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo) {
return readExcel(excel, rowModel, sheetNo, 1);
}
/**
* 读取某个 sheet 的 Excel
*
* @param excel 文件
* @param rowModel 实体类映射,继承 BaseRowModel 类
* @param sheetNo sheet 的序号 从1开始
* @param headLineNum 表头行数,默认为1
* @return Excel 数据 list
*/
public static List<Object> readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo,
int headLineNum) {
ExcelListener excelListener = new ExcelListener();
ExcelReader reader = getReader(excel, excelListener);
if (reader == null) {
return null;
}
reader.read(new Sheet(sheetNo, headLineNum, rowModel.getClass()));
return excelListener.getDatas();
}
/**
* 导出 Excel :一个 sheet,带表头
* @param response HttpServletResponse
* @param list 数据 list,每个元素为一个 BaseRowModel
* @param fileName 导出的文件名
* @param sheetName 导入文件的 sheet 名
* @param object 映射实体类,Excel 模型
*/
public static void writeExcel(HttpServletResponse response, List<? extends BaseRowModel> list,
String fileName, String sheetName, BaseRowModel object) {
ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
Sheet sheet = new Sheet(1, 0, object.getClass());
sheet.setSheetName(sheetName);
writer.write(list, sheet);
writer.finish();
}
/**
* 导出 Excel :多个 sheet,带表头
*
* @param response HttpServletResponse
* @param list 数据 list,每个元素为一个 BaseRowModel
* @param fileName 导出的文件名
* @param sheetName 导入文件的 sheet 名
* @param object 映射实体类,Excel 模型
*/
public static ExcelWriterFactroy writeExcelWithSheets(HttpServletResponse response, List<? extends BaseRowModel> list,
String fileName, String sheetName, BaseRowModel object) {
ExcelWriterFactroy writer = new ExcelWriterFactroy(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
Sheet sheet = new Sheet(1, 0, object.getClass());
sheet.setSheetName(sheetName);
writer.write(list, sheet);
return writer;
}
/**
* 导出文件时为Writer生成OutputStream
*/
private static OutputStream getOutputStream(String fileName, HttpServletResponse response) {
//创建本地文件
String filePath = fileName + ".xlsx";
File dbfFile = new File(filePath);
try {
if (!dbfFile.exists() || dbfFile.isDirectory()) {
dbfFile.createNewFile();
}
fileName = new String(filePath.getBytes(), "ISO-8859-1");
response.addHeader("Content-Disposition", "filename=" + fileName);
return response.getOutputStream();
} catch (IOException e) {
throw new ExcelException("创建文件失败!");
}
}
/**
* 返回 ExcelReader
*
* @param excel 需要解析的 Excel 文件
* @param excelListener new ExcelListener()
*/
private static ExcelReader getReader(MultipartFile excel,
ExcelListener excelListener) {
String filename = excel.getOriginalFilename();
if (filename == null || (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {
throw new ExcelException("文件格式错误!");
}
InputStream inputStream;
try {
inputStream = new BufferedInputStream(excel.getInputStream());
return new ExcelReader(inputStream, null, excelListener, false);
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
}
package com.bjzhianjia.scp.cgp.util.easyExeclUtil;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
/**
* Created with IntelliJ IDEA
*
* @Author yuanhaoyue swithaoy@gmail.com
* @Description
* @Date 2018-06-07
* @Time 16:47
*/
public class ExcelWriterFactroy extends ExcelWriter {
private OutputStream outputStream;
private int sheetNo = 1;
public ExcelWriterFactroy(OutputStream outputStream, ExcelTypeEnum typeEnum) {
super(outputStream, typeEnum);
this.outputStream = outputStream;
}
public ExcelWriterFactroy write(List<? extends BaseRowModel> list, String sheetName,
BaseRowModel object) {
this.sheetNo++;
try {
Sheet sheet = new Sheet(sheetNo, 0, object.getClass());
sheet.setSheetName(sheetName);
this.write(list, sheet);
} catch (Exception ex) {
ex.printStackTrace();
try {
outputStream.flush();
} catch (IOException e) {
e.printStackTrace();
}
}
return this;
}
@Override
public void finish() {
super.finish();
try {
outputStream.flush();
} catch (IOException e) {
e.printStackTrace();
}
}
}
3.设置表头:
我以一个复杂的表头做示例
先创建一个实体类:这个实体类可以自己定义 @Data 注解可以省去getter setter 构造方法 (不包括final修饰过的)
package com.bjzhianjia.scp.cgp.entity;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;
@Data
public class GridAssessmentemplate extends BaseRowModel {
@ExcelProperty(value={"序号","序号","序号"},index = 0)
private Integer id;
@ExcelProperty(value={"网格","网格","网格"},index = 1)
private String gridName;
@ExcelProperty(value={"姓名","姓名","姓名"},index = 2)
private String userName;
@ExcelProperty(value={"巡查情况","巡查次数","巡查次数"},index = 3)
private String numberPatrols;
@ExcelProperty(value={"巡查情况","巡查时长(单位:小时)","总时长"},index = 4)
private String patrolTimeLength;
@ExcelProperty(value={"巡查情况","巡查时长(单位:小时)","夜间时长"},index = 5)
private String nightPatrol;
@ExcelProperty(value={"巡查上报情况","发现问题","上报数"},index = 6)
private String yReported;
@ExcelProperty(value={"巡查上报情况","发现问题","终止数"},index = 7)
private String findTermination;
@ExcelProperty(value={"巡查上报情况","发现问题","上报有效率"},index = 8)
private String reportEfficiency;
@ExcelProperty(value={"巡查上报情况","未发现问题","上报数"},index = 9)
private String nReported;
@ExcelProperty(value={"巡查上报情况","上报总量","上报总量"},index = 10)
private String reportsTotal;
@ExcelProperty(value={"工单办理情况","任务处理率","直接处理"},index = 11)
private String processingWorkOrder;
@ExcelProperty(value={"工单办理情况","任务处理率","交办任务处理"},index = 12)
private String getThouth;
@ExcelProperty(value={"工单办理情况","退回数","退回数"},index = 13)
private String reportsBack;
@ExcelProperty(value={"工单办理情况","任务处理率","任务处理率"},index = 14)
private String taskEfficiency;
@ExcelProperty(value={"工单办理情况","事件处理总量","事件处理总量"},index = 15)
private String eventTotal;
public String getyReported() {
return yReported;
}
public void setyReported(String yReported) {
this.yReported = yReported;
}
public String getnReported() {
return nReported;
}
public void setnReported(String nReported) {
this.nReported = nReported;
}
}
**
4.数据的封装,可以把数据也封装到上面的实体类中
**
@GetMapping("/gridAssessmentemplate/easyExecl")
public void exportIndexBatchDetailResultConfirm(
HttpServletRequest request, HttpServletResponse response,
@RequestParam(value = "month", defaultValue = "") @ApiParam("月度,yyyy-MM") String month,
@RequestParam(value = "gridId", required = false) @ApiParam("行政村ID") Integer gridId,
@RequestParam(value = "gridMember", defaultValue = "") @ApiParam("姓名") String gridMember,
@RequestParam(value = "gridRole", defaultValue = "") @ApiParam("人员角色") String gridRole,
@RequestParam(value = "isPartyMember", defaultValue = "") @ApiParam("是否党员") String isPartyMember,
@RequestParam(value = "page", defaultValue = "1") Integer page,
@RequestParam(value = "limit", defaultValue = "10") Integer limit
){
//数据导出,不分页,全部数据导出
boolean paging = false;
TableResultResponse<JSONObject> assessment = this.baseBiz.getAssessment(month, gridId, gridMember, isPartyMember, gridRole, page, limit,paging);
List<JSONObject> rows = assessment.getData().getRows();
//封装数据
List<GridAssessmentemplate> list = new ArrayList<>();
for (int i = 0; i < rows.size(); i++) {
GridAssessmentemplate wgyTem = new GridAssessmentemplate();
wgyTem.setId(i);
wgyTem.setGridName(rows.get(i).getString("gridName"));
wgyTem.setUserName(rows.get(i).getString("userName"));
wgyTem.setNumberPatrols(rows.get(i).getString("numberPatrols"));
wgyTem.setPatrolTimeLength(rows.get(i).getString("patrolTimeLength"));
wgyTem.setNightPatrol(rows.get(i).getString("nightPatrol"));
wgyTem.setyReported(rows.get(i).getString("yReported"));
wgyTem.setFindTermination(rows.get(i).getString("findTermination"));
wgyTem.setReportEfficiency(rows.get(i).getString("reportEfficiency"));
wgyTem.setnReported(rows.get(i).getString("nReported"));
wgyTem.setReportsTotal(rows.get(i).getString("reportsTotal"));
wgyTem.setProcessingWorkOrder(rows.get(i).getString("processingWorkOrder"));
wgyTem.setGetThouth(rows.get(i).getString("getThouth"));
wgyTem.setReportsBack(rows.get(i).getString("reportsBack"));
wgyTem.setTaskEfficiency(rows.get(i).getString("taskEfficiency"));
wgyTem.setEventTotal(rows.get(i).getString("eventTotal"));
list.add(wgyTem);
}
// 设置导出参数
String fileName = "网格员考核";
String sheetName = "网格员考核";
ExcelUtil.writeExcel(response, list, fileName, sheetName, new GridAssessmentemplate());
}
完后用浏览器访问自己的接口 就可以下载了
- 下面附上生成的内容