使用easyexcel导入复杂的模板

雨点打透心脏的1/2处 2024-04-01 17:11 36阅读 0赞

## 文章目录 ##

一、使用easyexcel导入动态的模板

二、使用步骤

1.引入依赖

2.创建监听器

3.读取文件

总结

## 一、使用easyexcel导入动态的模板 ##

导入模板比较复杂，并且和实体类不匹配，没法直接将读到的内容存储到实体类中，可以使用这种方式

## 二、使用步骤 ##

### 1.引入依赖`` ###

<dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>3.0.5</version>
    </dependency>

### 2.创建监听器 ###

代码如下：``

package com.qingdao2world.source.api.tool.easyExcel;
    
    import com.alibaba.excel.context.AnalysisContext;
    import com.alibaba.excel.event.AnalysisEventListener;
    import com.alibaba.fastjson.JSON;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    /**
     * @author linaibo
     * @version 1.0
     * Create by 2022/8/11 9:51
     */
    
    public class DynamicHeaderListener extends AnalysisEventListener<HashMap<Integer, String>> {
    
        private static final Logger LOGGER = LoggerFactory.getLogger(DynamicHeaderListener.class);
        //顺丰标题项目位数
        private static final Integer NUMBER_14 = 14;
        //Excel数据缓存结构
        private List<Map<Integer, String>> list = new ArrayList<>();
    
        @Override
        public void invoke(HashMap<Integer, String> data, AnalysisContext analysisContext) {
            LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
            if(data.size()>=NUMBER_14){
                list.add(data);
            }
        }
    
        @Override
        public void doAfterAllAnalysed(AnalysisContext analysisContext) {
            LOGGER.info("所有数据解析完成！");
        }
    
        public List<Map<Integer, String>> getList() {
            return list;
        }
    }

--------------------

### 3.读取文件 ###

public int getSfFileDetail(MultipartFile file,Integer expressType) throws IOException {
            DynamicHeaderListener parseListener = new DynamicHeaderListener();
            EasyExcel.read(file.getInputStream(), parseListener).sheet().headRowNumber(1).doRead();
            List<Map<Integer, String>> listMap = parseListener.getList();
            List<SFExpressMonthDetailDto> sfExpressList = new ArrayList<>();
            listMap.stream().forEach(item -> {
                SFExpressMonthDetailDto expressMonthDetail = new SFExpressMonthDetailDto();
                if (!item.get(0).equals("序号")) {
                    setFileDetail(item, expressMonthDetail);
                    sfExpressList.add(expressMonthDetail);
                }
            });
            sfExpressList.stream().forEach(item -> setExpressType(item,expressType));
            return batchImportSf(sfExpressList);
        }

##  ##

public void setFileDetail(Map<Integer, String> item, SFExpressMonthDetailDto expressMonthDetail) {
            //运单号码
            expressMonthDetail.setLogisticsNo(item.get(2));
            //日期
            String[] str = item.get(1).split("-");
            Calendar date = Calendar.getInstance();
            String year = String.valueOf(date.get(Calendar.YEAR));
            String newDate;
            if (str[0].equals("12")) {
                newDate = Integer.valueOf(year) - 1 + "-" + str[0] + "-" + str[1];
            } else {
                newDate = year + "-" + str[0] + "-" + str[1];
            }
            expressMonthDetail.setExpenseDate(DateUtils.str2DateByFormat(newDate, "yyyy-MM-dd"));
            //寄件地区
            expressMonthDetail.setMailFrom(item.get(3));
            //到件地区
            expressMonthDetail.setMailTo(item.get(4));
            //计费重量
            expressMonthDetail.setCalcWeight(new BigDecimal(item.get(6)));
            //费用
            expressMonthDetail.setExpenseAmount(new BigDecimal(item.get(9)));
            //折扣/促销
            expressMonthDetail.setDiscount(new BigDecimal(item.get(10)));
            //应付金额
            expressMonthDetail.setPaidAmount(new BigDecimal(item.get(11)));
        }

## 总结 ##