一、步骤
1、导入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>1.1.1</version>
</dependency>
2、创建工具
/**
* @author wyy
* @version 1.0
* @date 2019/7/30 16:27
* @description
**/
public class ExcelUtil {
/**
* 导出 Excel :一个 sheet,带表头.
*
* @param response HttpServletResponse
* @param list 数据 list,每个元素为一个 BaseRowModel
* @param fileName 导出的文件名
* @param sheetName 导入文件的 sheet 名
* @param model 映射实体类,Excel 模型
* @throws Exception 异常
*/
public static void writeExcel(
HttpServletResponse response, List<? extends BaseRowModel> list,
String fileName, String sheetName, BaseRowModel model) throws Exception {
ExcelWriter writer =
new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX);
Sheet sheet = new Sheet(1, 0, model.getClass());
sheet.setSheetName(sheetName);
writer.write(list, sheet);
writer.finish();
}
/**
* 导出文件时为Writer生成OutputStream.
*
* @param fileName 文件名
* @param response response
* @return ""
*/
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);
}
}
}
3、创建表模型
/**
* @author wyy
* @version 1.0
* @date 2019/8/6 8:47
* @description
**/
@Data(记得导入 import lombok.Data;)
public class ExcelProperty extends BaseRowModel {
/**
* value: 表头名称
* index: 列的号, 0表示第一列
*/
/**
* 供应商
*/
@ExcelProperty(value = "供应商", index = 1)
private String supplierName;
/**
* 商品
*/
@ExcelProperty(value = "商品名", index = 1)
private String productName;
/**
* 入库件数
*/
@ExcelProperty(value = "入库件数", index = 4)
private Integer inNum;
/**
* 入库重量
*/
@ExcelProperty(value = "入库重量(Kg)", index = 5)
private BigDecimal inWeight;
/**
* 入库长度
*/
@ExcelProperty(value = "入库长度(m)", index = 6)
private BigDecimal inLength;
/**
* 入库面积
*/
@ExcelProperty(value = "入库面积(m²)", index = 7)
private BigDecimal inArea;
/**
* 出库件数
*/
@ExcelProperty(value = "出库件数", index = 8)
private Integer outNum;
/**
* 出库重量
*/
@ExcelProperty(value = "出库重量(Kg)", index = 9)
private BigDecimal outWeight;
/**
* 出库长度
*/
@ExcelProperty(value = "出库长度(m)", index = 10)
private BigDecimal outLength;
/**
* 出库面积
*/
@ExcelProperty(value = "出库面积(m²)", index = 11)
private BigDecimal outArea;
}
4、导入数据
public void printSummary(Query query, HttpServletResponse response) {
ExcelProperty property = new ExcelProperty();
//这里持久实体和报表实体不建议用同一个 所以分开并转化
List<ModelPO> list = reportDao.listallSummary(query);
List<ExcelProperty> data = null;
if (!CollectionUtils.isEmpty(list)) {
data = list.stream()
.map(x -> new ExcelProperty (x.getSupplierName(), x.productName(), x.getInNum(), x.getInWeight(),
x.getInLength(), x.getInArea(), x.getOutNum(), x.getOutWeight(), x.getOutLength(), x.getOutArea()))
.collect(Collectors.toList());
}
try {
ExcelUtil.writeExcel(response, data, "summaryReport", "汇总表", property );
} catch (Exception e) {
e.printStackTrace();
}
}