使用EasyExcel简单操作Excel

导出

生成依赖

父工程中导入依赖

<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;
    }