依赖 <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-base</artifactId> <version>3.2.0</version> </dependency> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-web</artifactId> <version>3.2.0</version> </dependency> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-annotation</artifactId> <version>3.2.0</version> </dependency>
public void (MultipartFile file){
String fileName = file.getOriginalFilename();
//后续判断文件名是否符合要求
valitFileNameHeng(fileName);
ImportParams params = new ImportParams();
params.setTitleRows(0);
params.setHeadRows(1);
//设置检验数据是否合格
params.setNeedVerfiy(true);
//设置不读取最后一行
params.setLastOfInvalidRow(1);
//设置开始读取的sheet位置
//params.setStartSheetIndex(1);
//设置开始时间
long startTime = System.currentTimeMillis();
log.debug("开始解析excel数据 文件名:{} 文件大小:{}b", file.getName(), file.getSize());
ExcelImportResult<ElemeRetailExcelModel> result = null;
try {
result = ExcelImportUtil.importExcelMore(
file.getInputStream(),
ElemeRetailExcelModel.class, params);
} catch (Exception e) {
e.printStackTrace();
}
//获取解析成功的数据
List<ElemeRetailExcelModel> successList = result.getList();
//获取解析失败的数据
List<ElemeRetailExcelModel> failList = result.getFailList();
log.debug("解析完成 此次一共解析成功{}条数据,解析失败{}条数据 耗时:{}毫秒",
successList.size(), failList.size(), System.currentTimeMillis() - startTime);
}
@Data
@ExcelTarget("ElemeRetailExcelModel")
public class ElemeRetailExcelModel implements IExcelModel, IExcelDataModel {
@Excel(name = "时间", importFormat = "yyyy-MM-dd")
@NotNull(message = "时间不能为空")
private String orderTime;
@Excel(name = "门店名称", orderNum = "1")
@NotNull(message = "门店名称不能为空")
private String storeName;
@Excel(name = "菜品销售", orderNum = "3")
@NotNull(message = "菜品销售不能为空")
private String dishesSaleIncome;
@Excel(name = "餐盒收入", orderNum = "4")
@NotNull(message = "餐盒收入不能为空")
private String lunchBoxIncome;
@Excel(name = "商户补贴", orderNum = "8")
@NotNull(message = "商户补贴不能为空")
private String businessSubsidy;
@Excel(name = "有效订单数", orderNum = "10")
@NotNull(message = "有效订单数不能为空")
private String orderNum;
private String errorMsg;
private int rowNum;
@Override
public String getErrorMsg() {
return errorMsg;
}
@Override
public void setErrorMsg(String errorMsg) {
this.errorMsg = errorMsg;
}
@Override
public int getRowNum() {
return rowNum;
}
@Override
public void setRowNum(int rowNum) {
this.rowNum = rowNum + 1;
}
}
导出例子
package com.ziku.mall.model.salereport.export; import cn.afterturn.easypoi.excel.annotation.Excel; import lombok.Data; import java.io.Serializable; import java.math.BigDecimal; @Data public class SaleFlowExportBO implements Serializable { private static final long serialVersionUID = 5097467920454191406L; /** * 商品名称 */ @Excel(name = "商品名称") private String productTitle; /** * 规格名称 */ @Excel(name = "规格名称") private String skuName; /** * 商品编号 */ @Excel(name = "商品编号") private String skuCode; /** * 销售数量 */ @Excel(name = "销售数量") private Integer quantity; /** * 销售金额 */ @Excel(name = "销售金额(元)") private BigDecimal paymentAmount; /** * 订单号 */ @Excel(name = "订单号") private String orderNo; /** * 订单日期 */ @Excel(name = "订单日期") private String orderDate; /** * 订单时间 */ @Excel(name = "订单时间") private String orderTime; }
List<SaleFlowExportBO> flowExportBOS
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(名称, 名称, ExcelType.HSSF), SaleFlowExportBO.class, flowExportBOS);