导出
生成依赖
父工程中导入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.4</version>
</dependency>
设置实体类标签
- @ExcelProperty : 配置属性和生成Excel中列的关系(并设置表头为自定义的内容)
- @ContentRowHeight(参数) : 配置数据行的行高
- @HeadRowHeight(参数): 配置表头行的行高
- @ColumnWidth(参数) :配置列宽
@Data
@ContentRowHeight(20)
@HeadRowHeight(20)
@ColumnWidth(15)
public class ContractProductVo implements Serializable {
@ExcelProperty("客户名称")
private String customName; //客户名称
@ExcelProperty("合同号")
private String contractNo; //合同号,订单号
@ExcelProperty("货号")
private String productNo; //货号
@ExcelProperty("数量")
private Integer cnumber; //数量
}
不依赖模板的简单导出
@RequestMapping("/printEasyExcel")
public void printEasyExcel(String inputDate) throws Exception {
List<ContractProductVo> contractProductVoList = contractService.findContractProductVoListByShipTime(inputDate, getCompanyId());
String returnName = "出货表.xlsx";
returnName = response.encodeURL(new String(returnName.getBytes(),"iso8859-1"));
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition","attachment;filename="+returnName);
EasyExcel.write(response.getOutputStream()).head(ContractProductVo.class).sheet("出货表").doWrite(contractProductVoList);
}
使用已有模板导出
@RequestMapping("/printEasyExcelTemplate")
public void printEasyExcelTemplate(String inputDate) throws Exception {
List<ContractProductVo> contractProductVoList = contractService.findContractProductVoListByShipTime(inputDate, getCompanyId());
Map map = new HashMap();
map.put("time",inputDate.replaceAll("-0","年").replaceAll("-","年")+"月份出货表");
String realPath = session.getServletContext().getRealPath("make/xlsprint/tOUTPRODUCT2.xlsx");
String resultName = "出货表.xlsx";
resultName = response.encodeURL(new String(resultName.getBytes(),"iso8859-1"));
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-Disposition","attachment;filename="+resultName);
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).head(ContractProductVo.class).withTemplate(realPath).build();
WriteSheet writeSheet = EasyExcel.writerSheet().build();
excelWriter.fill(map,writeSheet);
excelWriter.fill(contractProductVoList,writeSheet);
excelWriter.finish();
}
EasyExcel支持两种模板占位符的写法
{属性名} :针对单一对象的数据详情(传入参数为对象或者map)
属性名 = 对象的属性/map中的key
{.属性名} :针对数组中循环迭代,配置数组中对象元素的属性名称
导入
设置实体类
@ExcelIgnoreUnannotated
public class ContractProduct extends BaseEntity implements Serializable {
@ExcelProperty("货号")
private String productNo; //货号
@ExcelProperty("货物描述")
private String productDesc; //货描
@ExcelProperty("装率")
private String loadingRate; //报运:装率 1/3
@ExcelProperty("箱数")
private Integer boxNum; //报运:箱数 100
@ExcelProperty("包装单位")
private String packingUnit; //包装单位:PCS/SETS 支/箱
@ExcelProperty("数量")
private Integer cnumber; //数量 300
@ExcelProperty("要求")
private String productRequest; //要求
@ExcelProperty("单价")
private Double price; //单价
@ExcelProperty("生产厂家")
private String factoryName; //厂家名称,冗余字段
//省略未加注解属性和setget方法
}
- 通过@ExcelProperty注解的形式指定解析Excel中列的位置
- @ExcelIgnoreUnannotated: 默认不加
ExcelProperty
的注解的属性都会参与读写,在类上配置此注解剩余属性不会参与读写。
实现导入
@RequestMapping("/import")
public String importFile(String contractId, MultipartFile file) throws Exception{
List<ContractProduct> contractProductList = EasyExcel.read(file.getInputStream()).head(ContractProduct.class).sheet(0).doReadSync();
for (ContractProduct contractProduct : contractProductList) {
contractProduct.setId(UUID.randomUUID().toString());
contractProduct.setContractId(getCompanyId());
contractProduct.setCompanyName(getCompanyName());
contractProduct.setContractId(contractId);
contractProduct.setCreateTime(new Date());
}
contractProductService.saveList(contractProductList);
return "redirect:/cargo/contractProduct/list.do?contractId="+contractId;
}