1.pom引入依赖:
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<!--<version>1.1.2-beta5</version> -->
<version>2.0.5</version>
<exclusions>
<exclusion>
<groupId>javax.servlet</groupId>
<artifactId>servlet-api</artifactId>
</exclusion>
<exclusion>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
</exclusion>
<exclusion>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>cglib</groupId>
<artifactId>cglib</artifactId>
<version>3.1</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.26</version>
</dependency>
<dependency>
<groupId>org.ehcache</groupId>
<artifactId>ehcache</artifactId>
<version>3.7.1</version>
</dependency>
2.接收的实体类
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import com.fasterxml.jackson.annotation.JsonFormat;
import java.util.Date;
/**
* @Author: BillYu
* @Description:
* @Date: Created in 16:23 2019-09-12.
*/
public class LabelExcelData extends BaseRowModel {
@ExcelProperty(value = "材料编号(必填)", index = 0)
private String materialCode;
@ExcelProperty(value = "材料名称(必填)", index = 1)
private String materialName;
@ExcelProperty(value = "包装数量(必填)", index = 2)
private Double packageNumber;
@ExcelProperty(value = "计划号", index = 3)
private String purchasePlanCode;
@ExcelProperty(value = "供应厂商", index = 4)
private String manufacturerName;
@ExcelProperty(value = "制造日期", index = 5)
@JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
private Date produceDate;
@ExcelProperty(value = "有效日期", index = 6)
@JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
private Date validDate;
@ExcelProperty(value = "供应商批次号", index = 7)
private String batchCode;
public String getMaterialCode() {
return materialCode;
}
public void setMaterialCode(String materialCode) {
this.materialCode = materialCode;
}
public String getMaterialName() {
return materialName;
}
public void setMaterialName(String materialName) {
this.materialName = materialName;
}
public Double getPackageNumber() {
return packageNumber;
}
public void setPackageNumber(Double packageNumber) {
this.packageNumber = packageNumber;
}
public String getPurchasePlanCode() {
return purchasePlanCode;
}
public void setPurchasePlanCode(String purchasePlanCode) {
this.purchasePlanCode = purchasePlanCode;
}
public String getManufacturerName() {
return manufacturerName;
}
public void setManufacturerName(String manufacturerName) {
this.manufacturerName = manufacturerName;
}
public Date getProduceDate() {
return produceDate;
}
public void setProduceDate(Date produceDate) {
this.produceDate = produceDate;
}
public Date getValidDate() {
return validDate;
}
public void setValidDate(Date validDate) {
this.validDate = validDate;
}
public String getBatchCode() {
return batchCode;
}
public void setBatchCode(String batchCode) {
this.batchCode = batchCode;
}
}
3.数据处理加入list
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.BaseRowModel;
import com.kaicom.mes.util.GsonUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;
import java.util.ArrayList;
import java.util.List;
/**
* @Author: BillYu
* @Description:
* @Date: Created in 16:21 2019-09-12.
*/
public class LabelExcelListener<T extends BaseRowModel> extends AnalysisEventListener<LabelExcelData> {
private static final Logger LOGGER = LoggerFactory.getLogger(LabelExcelListener.class);
/**
* 每隔5条存储数据库,实际使用中可以3000条,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 3000;
List<LabelExcelData> list = new ArrayList<LabelExcelData>();
@Override
public void invoke(LabelExcelData data, AnalysisContext context) {
LOGGER.info("解析到一条数据:{}", GsonUtil.toJson(data));
//添加必填项
if(!StringUtils.isEmpty(data.getMaterialCode())&&!StringUtils.isEmpty(data.getMaterialName())&&data.getPackageNumber()!=null){
if(data.getMaterialCode().endsWith(".0")){
data.setMaterialCode(data.getMaterialCode().substring(0,data.getMaterialCode().length()-2));
}
if(data.getMaterialName().endsWith(".0")){
data.setMaterialName(data.getMaterialName().substring(0,data.getMaterialName().length()-2));
}
if(!StringUtils.isEmpty(data.getPurchasePlanCode())&&data.getPurchasePlanCode().endsWith(".0")){
data.setPurchasePlanCode(data.getPurchasePlanCode().substring(0,data.getPurchasePlanCode().length()-2));
}
if(!StringUtils.isEmpty(data.getManufacturerName())&&data.getManufacturerName().endsWith(".0")){
data.setManufacturerName(data.getManufacturerName().substring(0,data.getManufacturerName().length()-2));
}
if(!StringUtils.isEmpty(data.getBatchCode())&&data.getBatchCode().endsWith(".0")){
data.setBatchCode(data.getBatchCode().substring(0,data.getBatchCode().length()-2));
}
list.add(data);
if (list.size() >= BATCH_COUNT) {
saveData();
list.clear();
}
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
saveData();
LOGGER.info("所有数据解析完成!");
}
/**
* 加上存储数据库
*/
private void saveData() {
LOGGER.info("{}条数据,开始存储数据库!", list.size());
LOGGER.info("存储数据库成功!");
}
public List<LabelExcelData> getList(){
return this.list;
}
}
//xlsx解析没有问题,但是.xls解析字符串类型时,如果时纯数字的字符串会先变成double,所以拿到的String类型数据会在结尾多了.0 ,这里强行做了一下处理
4.controller
@ApiOperation("导入excel")
@PostMapping("/uploadExcel")
public RtData upload(@RequestParam("file") MultipartFile file) {
if (file.isEmpty()) {
return ResponseBuilder.fail("文件为空");
}
LabelExcelListener excelListener = new LabelExcelListener();
//读取xls 和 xlsx格式
ExcelReader reader = null;
try {
if(file.getOriginalFilename().endsWith(".xlsx")){
reader = new ExcelReader(file.getInputStream(), ExcelTypeEnum.XLSX, null, excelListener);
}else if(file.getOriginalFilename().endsWith(".xls")){
reader = new ExcelReader(file.getInputStream(), ExcelTypeEnum.XLS, null, excelListener);
}else{
return ResponseBuilder.fail("文件格式不支持");
}
reader.read(new com.alibaba.excel.metadata.Sheet(1,1),LabelExcelData.class);
} catch (IOException e) {
e.printStackTrace();
}catch (ExcelAnalysisException e){
return ResponseBuilder.fail("人为修改Excel文件后缀导致错误");
}
return ResponseBuilder.success(excelListener.getList());
}