使用easyexcel读取excel中的数据内容

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());
    }
发表新评论