一、模板样式
本工具类使用于简单的模板导出,如下图:
引入的maven:
<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>1.1.2-beat1</version> </dependency>
二、工具类型实现
使用方式
list:导出的数据列表
ExportModel:导出的表头信息
response:HttpServletResponse
EasyExcelUtils.baseExportExcel("导出列表",list , ExportModel.class , response);
实现方式
ExportModel类:
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;
import java.io.Serializable;
import java.time.LocalDateTime;
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
public class ExportModel extends BaseRowModel implements Serializable {
@ExcelProperty(index = 0 , value ="id")
private String id;
@ExcelProperty(index = 1 , value ="动作")
private String action;
@ExcelProperty(index = 2 , value ="用户名")
private String userName;
@ExcelProperty(index = 3 , value ="脱网时间")
private LocalDateTime outDate;
@ExcelProperty(index = 4 , value ="操作时间")
private LocalDateTime operateDate;
@ExcelProperty(index = 5 , value ="描述")
private String description;
}
EasyExcelUtils类:
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.hdvon.common.lang.StringUtils;
import org.apache.commons.lang3.time.FastDateFormat;
import org.springframework.beans.BeanUtils;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* @Author allen小哥 2019/7/5 18:03
* 阿里组件 easyexcel
**/
public class EasyExcelUtils {
protected static FastDateFormat fastDateFormat = FastDateFormat.getInstance("yyyyMMddHH:mm:ss");
/**
*
* @param fileName 文件名
* @param cameraReportStopList 普通vo类
* @param entityModel 继承BaseRowModel的模型class
* @param response
* @param <T>
*/
public static <T extends BaseRowModel> void baseExportExcel(String fileName , List cameraReportStopList , Class<T> entityModel , HttpServletResponse response) {
List<BaseRowModel> modelList = new ArrayList<>();
if (StringUtils.isEmpty(cameraReportStopList)) return;
cameraReportStopList.forEach(cameraReportStopVo -> {
try{
BaseRowModel cameraReportStopModel = entityModel.newInstance();
BeanUtils.copyProperties(cameraReportStopVo , cameraReportStopModel);
modelList.add(cameraReportStopModel);
} catch (Exception e){
}
});
try{
writeExcel(response , modelList , fileName+fastDateFormat.format(new Date()) , "第一页");
} catch (Exception e){
e.printStackTrace();
}
}
/**
* 导出 Excel :一个 sheet,带表头
*
* @param response HttpServletResponse
* @param list 数据 list,每个元素为一个 BaseRowModel
* @param fileName 导出的文件名
* @param sheetName 导入文件的 sheet 名
*/
public static void writeExcel(HttpServletResponse response, List<? extends BaseRowModel> list,
String fileName, String sheetName) throws Exception {
ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
Class clazz = null;
if (list.size() > 0) {
clazz = list.get(0).getClass();
} else {
clazz = BaseRowModel.class;
}
Sheet sheet = new Sheet(1, 0, clazz);
sheet.setSheetName(sheetName);
writer.write(list, sheet);
writer.finish();
}
/**
* 导出文件时为Writer生成OutputStream
*
*/
private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
try {
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf8");
response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "no-store");
response.addHeader("Cache-Control", "max-age=0");
return response.getOutputStream();
} catch (IOException e) {
throw new Exception("导出excel表格失败!", e);
}
}
}
三、总结
这篇文章简单,侧重代码实现,只是方便自己的基础代码实现,为以后直接使用。