JeecgBoot通过EasyExcel导出自定义报表
一、作用
根据自己自定义模板,导出主附表数据的报表
二、示例图
1、目前只学习做过自定义excel格式的报表
2、后期学会其他格式的报表,再讲其他的
三、后台代码
1、实体类
@Data
public class OrderVo {
/**主键*/
@TableId(type = IdType.ASSIGN_ID)
@ApiModelProperty(value = "主键")
private String id;
/**客户*/
@ExcelProperty("客户")
@ApiModelProperty(value = "客户")
private String name;
@ExcelProperty("手机号")
@ApiModelProperty(value = "手机号")
private String phine;
@ExcelProperty("地址")
@ApiModelProperty(value = "地址")
private String address;
@ExcelProperty("年龄")
@ApiModelProperty(value = "年龄")
private int age;
@ExcelProperty("服务名称")
@ApiModelProperty(value = "服务名称")
private String serviceName;
@ExcelProperty("服务价格")
@ApiModelProperty(value = "服务价格")
private java.math.BigDecimal servicePrice;
@ExcelProperty(value = "章子图片")
@ApiModelProperty(value = "章子图片")
private File file;
@ExcelProperty("总计")
@ApiModelProperty(value = "总计")
private java.math.BigDecimal zongji;
}
2、服务项目明细实体类
public class OrderDetail implements Serializable {
private static final long serialVersionUID = 1L;
/**主键*/
@TableId(type = IdType.ASSIGN_ID)
@ApiModelProperty(value = "主键")
private String id;
/**订单id*/
@ApiModelProperty(value = "订单id")
private String orderId;
/**名称*/
@ExcelProperty("名称")
@Excel(name = "名称", width = 15)
@ApiModelProperty(value = "名称")
private String name;
/**时间*/
@ExcelProperty("时间")
@Excel(name = "时间", width = 15)
@ApiModelProperty(value = "时间")
private java.lang.String time;
/**数量*/
@ExcelProperty("数量")
@Excel(name = "数量", width = 15)
@ApiModelProperty(value = "数量")
private java.math.BigDecima num;
/**单价*/
@ExcelProperty("单价")
@Excel(name = "单价", width = 15)
@ApiModelProperty(value = "单价")
private java.math.BigDecimal price;
/**总价*/
@ExcelProperty("总价")
@Excel(name = "总价", width = 15)
@ApiModelProperty(value = "总价")
private java.math.BigDecimal sumPrice;
/**序号*/
@TableField(exist = false)
private Integer row;
}
3、controller方法
public void exportOrder(HttpServletResponse response, String orderId) throws IOException {
//赋值
OrderVo orderVo = new OrderVo();
orderVo.setName("张三");
orderVo.setPhone("181111111111");
//设置图片
exportOrder.setFile(new File(exportPath+"tc.png"));
//获取模板路径
String templateFile = exportPath+"模板名.xlsx";
//查询数据
List<OrderDetail> detailList = OrderDetailService.list(
new QueryWrapper<OrderDetail>().lambda().eq(OrderDetail::getOrderId, orderId));
//序号
int i = 0;
for(AtbOfferOrderDetail detail : orderDetailList){
i+=1;
detail.setRow(i);
}
try {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
String outFileName ="合同单【"+ orderVo .getId() +"】";
//填写合同单号和模板, getOutputStream浏览器下载方法写在下方
ExcelWriter excelWriter = EasyExcel.write(this.getOutputStream(outFileName, response)).withTemplate(templateFile).build();
WriteSheet writeSheet = EasyExcel.writerSheet(0,"第一个").build();
// 组合填充时,因为多组填充的数据量不确定,需要在多组填充完之后另起一行
FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.VERTICAL).forceNewRow(Boolean.TRUE).build();
excelWriter.fill(new FillWrapper(orderDetailList), fillConfig, writeSheet);
excelWriter.fill(exportOrder, writeSheet);
excelWriter.finish();
} catch (Exception e) {
System.out.println(e.toString());
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
Map<String, String> map = new HashMap<String, String>(16);
map.put("status", "failure");
map.put("message", "下载文件失败" + e.getMessage());
response.getWriter().println(JSON.toJSONString(map));
}
}
2、getOutputStream浏览器下载方法
/**
* 这是ExcelUtil.getOutputStream
* 这里就是将文件下载交给了浏览器
* @param
* @return
*/
public OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
// 这里文件名如果涉及中文一定要使用URL编码,否则会乱码
String exportFileName = URLEncoder.encode(fileName+ExcelTypeEnum.XLSX.getValue(), StandardCharsets.UTF_8.toString());
response.setContentType("application/force-download");
response.setHeader("Content-Disposition", "attachment;filename=" + exportFileName);
return response.getOutputStream();
}
一个在学习的开发者,勿喷,欢迎交流