表格内容如下

分析

1. 该文档总共9个Sheet页
2. 以Sheet0(客户交易结算月报)为例：表格总共分为4个部分(图中红色部分)。一、二部分数据为横向数据(字段名：字段值)，三四部分为纵向集合。第四部分每一行数据下有合计栏(合计栏数据不读取)。

实现逻辑

1. 前期准备

1） Pom文件引入Easy Excel

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

2）实现AnalysisEventListener

public class EasyExcelListener extends AnalysisEventListener<Object> {

    // 创建list集合封装最终的数据
    private List<Object> list = new ArrayList<>();
	// sheet页索引
    private int sheetNo = 0;

    @Override
    public void invoke(Object t, AnalysisContext context) {
        // 读取excle内容
        int currentSheetNo = context.readSheetHolder().getSheetNo();
        if (currentSheetNo != sheetNo) {
        	// 如果不根据sheet页索引更新状态重新创建list，list会反复添加前面的sheet页对象值
            list = new ArrayList<>();
            sheetNo = currentSheetNo;
        }
        list.add(t);
    }

    // 读取excel表头信息
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
    }

    // 读取完成后执行
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
    }

    /**
     * 获取表格内容(简单excel读取可用该方法)
     *
     * @param obj 需要转化的实体
     * @param <T>
     * @return
     */
    public <T> List<T> getList(Class<T> obj) {
        String jsonObj = JSONUtil.toJsonStr(list);
        return JSONUtil.toList(jsonObj, obj);
    }

    /**
     * 将表格转化为map集合（复杂excel读取用此方法）
     *
     * @return map集合
     */
    public List<LinkedHashMap> getListMap() {
        String jsonObj = JSONUtil.toJsonStr(list);
        return JSONUtil.toList(jsonObj, LinkedHashMap.class);
    }

}

ps: 为什么用LinkHashMap接收excel表格内容？

读取文件内容时，在不确定接收实体的情况下，对象都是以LinkedHashMap进行存储的。图中可以看到，每个map的key，value是以列索引以及对应的行值进行存储。

3）Excel工具类

@Slf4j
public class EasyExcelUtil {
    private EasyExcelUtil() {
    }

    /**
     * 根据easyexcel注解给指定实体赋值
     *
     * @param objects 读取的表格内容
     * @param clazz   需转化的实体
     * @param <T>     实体
     * @return 需转化的提示集合
     */
    public static <T> List<T> convertList(List<LinkedHashMap> objects, Class<T> clazz) {
        List<T> results = new ArrayList<>(objects.size());
        try {
            Map<String, Field> objIndex = new HashMap<>();
            // 获取转化实体字段信息集合
            Field[] fields = clazz.getDeclaredFields();
            for (Field field : fields) {
            	// 根据实体上Easy Excel的ExcelProperty注解中的索引值对应excel读取数据的值
                int index = field.getAnnotation(ExcelProperty.class).index();
                // 设置字段可编辑
                field.setAccessible(true);
                objIndex.put(String.valueOf(index), field);
            }

            T obj = null;
            for (LinkedHashMap o : objects) {
                obj = clazz.newInstance();
                for (Object key : o.keySet()) {
                    // 如果表格索引与字段注解指定索引一样则赋值
                    if (objIndex.containsKey(key)) {
                        Object object = o.get(key);
                        Object value = null;
                        Field field = objIndex.get(key);
                        if (ObjectUtil.isEmpty(object)) {
                            continue;
                        }
                        Class<?> type = field.getType();
                        String replace = object.toString();
                        // 有特殊需要处理的字段类型则在此进行处理
                        if (type == BigDecimal.class) {
                            value = "--".equals(replace) ? null : new BigDecimal(replace.replace(",", ""));
                        } else if (type == Integer.class) {
                        	// String强转Integer会报错，所以需要单独进行转化
                            value = "--".equals(replace) ? null : Integer.valueOf(replace.replace(",", ""));
                        } else {
                            value = object;
                        }
                        field.set(obj, value);
                    }
                }
                results.add(obj);
            }
        } catch (Exception e) {
            log.error("字段解析失败", e);
            Asserts.fail("字段解析失败:" + e.getMessage());
        }
        return results;
    }
}

参考实体：
@ExcelProperty注解必须有，且对应到字段值对应的列数。如果不确定列数可以先读取linkedlist 看看对应的索引值

@Data
@ApiModel("其他资金明细excel实体")
public class OtherFundDetailsExcelDTO {

    @ApiModelProperty(value = "发生日期")
    @ExcelProperty(value = "发生日期", index = 0)
    private String happenDate;

    @ApiModelProperty(value = "交易所")
    @ExcelProperty(value = "交易所", index = 2)
    private String exchange;

    @ApiModelProperty(value = "类型")
    @ExcelProperty(value = "类型", index = 4)
    private String type;

    @ApiModelProperty(value = "金额")
    @ExcelProperty(value = "金额", index = 6)
    private BigDecimal money;

    @ApiModelProperty(value = "备注")
    @ExcelProperty(value = "备注", index = 8)
    private String remark;
}

2. 编码

1）Controller获取文件内容

   	@ApiOperation("导入")
    @PostMapping(value = "/excel/upload")
    public void upload(@ApiParam(name = "file", value = "file", required = true) @RequestParam(value = "file") MultipartFile file, HttpServletResponse response) throws IOException {
    	// step1. 读取excel内容
        EasyExcelListener easyExcelListener = new EasyExcelListener();
        ExcelReaderBuilder read = EasyExcelFactory.read(file.getInputStream(), easyExcelListener);
        ExcelReader excelReader = read.build();
        // step2. 获取各个sheet页信息
        List<ReadSheet> sheets = excelReader.excelExecutor().sheetList();
        // step3. 获取各个Shhet页表格内容存于map
        Map<Integer, List<LinkedHashMap>> sheetInfos = new HashMap<>(sheets.size());
        for (ReadSheet sheet : sheets) {
            Integer sheetNo = sheet.getSheetNo();
            excelReader.read(sheet);
            sheetInfos.put(sheetNo, easyExcelListener.getListMap());
        }
        iSettlementMothReportService.saveExcelInfo(sheetInfos);
    }

2） Service将读取内容转化为实体类（此代码仅提供两种excel表格读取方式，横向数据读取以及纵向集合读取）

public void saveExcelInfo(Map<Integer, List<LinkedHashMap>> sheetInfos) {
        SettlementMothReportSaveDTO settlementMothReportSaveDTO = new SettlementMothReportSaveDTO();
        for (Integer sheetNo : sheetInfos.keySet()) {
            List<LinkedHashMap> maps = sheetInfos.get(sheetNo);
            // 不同sheet页数据处理方式不同
            switch (sheetNo) {
                case 0:
                    settlementMonthlyReport(maps, settlementMothReportSaveDTO);
                    break;
                case 1:
                    varietyCollects(maps, settlementMothReportSaveDTO);
                    break;
                case 2:
                    holdingsSubsidiary(maps, settlementMothReportSaveDTO);
                    break;
                case 3:
                    dealDetail(maps, settlementMothReportSaveDTO);
                    break;
                default:
                    break;
            }
        }
        iBzjBaseInfoService.saveSettlementMothReport(settlementMothReportSaveDTO);
    }

private void settlementMonthlyReport(List<LinkedHashMap> maps, SettlementMothReportSaveDTO settlementMothReportSaveDTO) {
        // 基本资料
        BzjBaseInfo baseInfo = new BzjBaseInfo();
        // 期货期权账户出入金明细
        List<LinkedHashMap> optionAccountsDetails = new ArrayList<>();

        // 是否基本资料
        boolean whetherBaseInfo = true;
        // 是否期货期权账户出入金明细
        boolean whetherOptionAccountsDetails = false;
      
        for (LinkedHashMap map : maps) {
            if (whetherBaseInfo) {
                // 基本资料
                whetherBaseInfo = dealBaseInfo(map, baseInfo);
                if (!whetherBaseInfo) {
                    continue;
                }
            }
    
            if (map.containsValue(OptionAccountsDetailsConstant.OPTION_ACCOUNTS_DETAILS)) {
                whetherOptionAccountsDetails = true;
            }
            if (!whetherBaseInfo && whetherOptionAccountsDetails) {
                // 期货期权账户出入金明细
                whetherOptionAccountsDetails = dealOptionAccountsDetails(map, optionAccountsDetails);
                if (!whetherOptionsFuturesFunds) {
                    continue;
                }
            }
        }
        if (!optionAccountsDetails.isEmpty()) {
            List<FuturesOptionAccountsDetailsExcelDTO> dtos = EasyExcelUtil.convertList(optionAccountsDetails, FuturesOptionAccountsDetailsExcelDTO.class);
            settlementMothReportSaveDTO.setOptionAccountsDetails(dtos);
        }
        settlementMothReportSaveDTO.setBzjBaseInfo(baseInfo);
    }

ps：为什么要用模块标题作为标识而不是索引？
因为以模块标题作为标识，如果上一个模块出现增删数据时，不影响本模块的读取。如果直接用表格行索引，那么表格内容出现变更时就无法灵活读取数据。

 /**
     * 处理基础资料
     *
     * @param map      excel表格信息
     * @param baseInfo 基础资料信息
     * @return 是否基本资料
     */
    private boolean dealBaseInfo(LinkedHashMap map, BzjBaseInfo baseInfo) {
        // 是否基础信息内容
        if (map.containsValue(BaseInfoConstant.BASE_INFO)) {
            return true;
        }
		// 根据表格内容分析，对象值在2,7列。合并单元格在读取之后会将值存在合并的第一列索引下，如合并A-C列，那A列会存储字段值，B、C列值为空。具体内容可见上面读取excel截图
        Object obj = map.get("2");
        String str = null;
        if (ObjectUtil.isNotEmpty(obj)) {
            str = obj.toString();
        }
        Object object = map.get("7");
        String value = null;
        if (ObjectUtil.isNotEmpty(object)) {
            value = object.toString();
        }
        // 通过本行的第一个字段名，获取本行的所有字段信息(后面的判断均是如此，常量类对应了各个字段名)
        if (map.containsValue("客户期货期权内部资金账户")) {
            baseInfo.setCusFuturesInnerFundAccount(str);
            baseInfo.setTransactionMonth(value);
        }
        if (map.containsValue(BaseInfoConstant.CUSTOMER_NAME)) {
            baseInfo.setCustomerName(str);
            DateTimeFormatter fmt = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            baseInfo.setQueryTime(LocalDateTime.parse(value, fmt));
        }
        if (map.containsValue(BaseInfoConstant.FUTURES_COMPANY_NAME)) {
            baseInfo.setFuturesCompanyName(str);
            baseInfo.setCusSecuritiesSpotInnnerFundAccount(value);
            return false;
        }
        return true;
    }

    /**
     * 处理期货期权账户出入金明细
     *
     * @param map                   excel表格信息
     * @param optionAccountsDetails 账户出入金明细Excel集合
     * @return 是否期货期权账户出入金明细
     */
    private Boolean dealOptionAccountsDetails(LinkedHashMap map, List<LinkedHashMap> optionAccountsDetails) {
    	// 确定模块结束标识
        if (map.containsValue("合计")) {
            return false;
        }
        // 模块开始标识为模块标题以及模块第一行字段名，所以读取数据时要排除这两行
        if (!map.containsValue("发生日期") && !map.containsValue("期货期权账户出入金明细")) {
            optionAccountsDetails.add(map);
        }
        return true;
    }

PS:为什么确定模块开始标识和结束标识？
确定了开始和结束标识相当于直接将读取数据锁死，只读取开始和结束中间的数据，避免数据误读！

以上是Easy Excel读取复杂表格的代码分析，请多指教！
晚安，玛卡巴卡~

前言

使用spring boot 对excel 进行操作在平时项目中要经常使用。常见通过jxl和poi 的方式进行操作。但他们都存在一个严重的问题就是非常的耗内存。这里介绍一种 Easy Excel 工具来对excel进行操作。

一、Easy Excel是什么？

EasyExcel是阿里巴巴开源的一个excel处理框架，以使用简单、节省内存著称。easyExcel能大大减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中，而是从磁盘上一行行读取数据，逐个解析。

二、使用EasyExcel 实现读操作

从excel 中读取数据，常用的场景就是读取excel的数据，将相应的数据保存到数据库中。需要实现一定的逻辑处理。

1.导入依赖

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

2.创建要读取数据的封装类

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

@Data
public class SubjectDate {
    @ExcelProperty(index = 0)
    // 一级目录
    private  String oneSubjectName;

    @ExcelProperty(index = 1)
    // 二级目录
    private String twoSubjectName;

}

比如我们要读取两列的数据，就写两个属性。@ExcelProperty(index = 0)来设置要读取的列,index=0表示读取第一列。

3.创建读取excel 监听器

监听器继承 AnalysisEventListener 类

package com.boshrong.edu.entity.excel;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.boshrong.edu.entity.EduSubject;
import com.boshrong.edu.service.EduSubjectService;
import com.boshrong.utils.globalException.myException;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;


public class SubjectListener extends AnalysisEventListener<SubjectDate> {
    // 创建list集合封装最终的数据
    //List<SubjectDate> list=new ArrayList<SubjectDate>();
    public EduSubjectService eduSubjectService;

    public SubjectListener(EduSubjectService eduSubjectService) {
        this.eduSubjectService = eduSubjectService;
    }
    public SubjectListener(){}

    @Override
    /**
 * 这个每一条数据解析都会来调用
 */
    //一行一行去读取excle内容 subjectDate 为每一行的数据，第一个数据为每行的数据
    public void invoke(SubjectDate subjectDate, AnalysisContext analysisContext) {
        if(subjectDate==null)
            throw  new myException(20001,"文件数据为空");
        // 一行一行的读取数据
        EduSubject eduSubject=existOneSubject(subjectDate.getOneSubjectName(),eduSubjectService);
        if(eduSubject==null){
             eduSubject=new EduSubject();
             eduSubject.setTitle(subjectDate.getOneSubjectName());
             eduSubject.setParentId("0");
             eduSubjectService.save(eduSubject);
        }
        //获取一级分类id值 作为二级分类的父id
        String pid=eduSubject.getId();
        EduSubject eduSubject1=existTwoSubject(eduSubjectService,subjectDate.getTwoSubjectName(),pid);
        if (eduSubject1==null){
            eduSubject1=new EduSubject();
            eduSubject1.setTitle(subjectDate.getTwoSubjectName());
            eduSubject1.setParentId(pid);
            eduSubjectService.save(eduSubject1);
        }



    }
    // 判断一级目录不为空
    //select * from edu_subject where title=? and parent_id=0
    public EduSubject  existOneSubject(String name,EduSubjectService eduSubjectService){
        QueryWrapper<EduSubject> wrapper=new QueryWrapper<>();
        wrapper.eq("title",name);
        wrapper.eq("parent_id","0");
        EduSubject eduSubject=eduSubjectService.getOne(wrapper);
        return eduSubject;
    }
    // 判断二级目录不为空
    private EduSubject existTwoSubject(EduSubjectService subjectService,String name,String pid) {
        QueryWrapper<EduSubject> wrapper = new QueryWrapper<>();
        wrapper.eq("title",name);
        wrapper.eq("parent_id",pid);
        EduSubject twoSubject = subjectService.getOne(wrapper);
        return twoSubject;
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

当解析每一条数据时都会调用invoke方法，当所有数据都解析完毕时最后会调用doAfterAllAnalysed方法。上述代码将读取的excel数据经过判断处理后，放入数据。可以不看existOneSubject， existTwoSubject 方法。

调用监听器

通常在上传功能时，我们会传来一个excel文件。我们会读取excel中的文件加入数据库，这个时候需要调用监听器。

  // 获取上传过来的文件，把文件中的内容读取出来
    @PostMapping("addSubject")
    // MutipartFile 表示当前上传文件
    public Result addSubject(MultipartFile file){
        //得到上传过来的文件,将数据传到数据库
        eduSubjectService.saveSubject(file,eduSubjectService);
        return Result.sucess().message("文件上传成功");

    }
    

eduService 中的逻辑

  public void saveSubject(MultipartFile file,EduSubjectService eduSubjectService) {
        try {
            InputStream in=file.getInputStream();
            EasyExcel.read(in, SubjectDate.class,new SubjectListener(eduSubjectService)).sheet().doRead();
        }catch (Exception e){
            System.out.println(e);
        }

    }

关键代码
InputStream in=file.getInputStream()
EasyExcel.read(in,SubjectDate.class,new SubjectListener(eduSubjectService)).sheet().doRead();
获取文件的输入流，执行EasyExcel read方法参数为 输入流，封装数据类，调用监听器（使用构造器注入相关数据库service）。

三 使用EasyExcel 实现写操作

写操作有两种写法，一种是不创建对象的写入，另一种是根据对象写入。这里主要介绍创建对象写入

创建对象写入

创建excel对象类

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

@Data
public class DemoData {
    @ExcelProperty("学生编号")
    private int sno;
    @ExcelProperty("学生姓名11")
    private String stu_name;

}

注意@ExcelProperty(“学生编号”) 会生成相应的列名为 学生编号

import com.alibaba.excel.EasyExcel;

import java.util.ArrayList;
import java.util.List;

public class TestExcel {
    private static List<DemoData> data(){
        List<DemoData> list=new ArrayList<DemoData>();
        for(int i=0;i<10;i++){
            DemoData data=new DemoData();
            data.setSno(i);
            data.setStu_name("张三"+i);
            list.add(data);
        }
        return list;
    }
    public static void main(String args[]) throws Exception{
        String filename="F:\\学生信息.xlsx";
        EasyExcel.write(filename, DemoData.class).sheet("写入方法一").doWrite(data());
    }

}

放入数据，并写入excel
运行结果

总结

提示：这里对文章进行总结：
这就简单的利用easyExcel 对Excel 进行操作步骤。

使用easy excel导出复杂表头的excel

今天想写一个双层表头的excel导出,一开始使用的是poi来画发现太麻烦,
于是就想到了使用easy-excel的模板填充来实现,将导出写成了一个简单的工具类,
供参考

最终是要实现为这样的一个效果 , 红色为表头,绿色为表体,于是我先做出了一个模板 ,
如果不会写模板的可以去看官方文档, 十分简单 https://www.yuque.com/easyexcel/doc/fill

然后将表头数据封装成一个map,表体数据为一个list , 去调用工具类就实现了

以下为工具类代码,导入了easy-excel就可以使用

package com.org.inventory.util;

import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import org.springframework.core.io.ClassPathResource;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;

/**
 * Description: easy excel 工具类
 *
 * @ProjectName: inventory_task
 * @ProduceName: IntelliJ IDEA
 * @author: li ji hong
 * @date: 2021/12/15 9:51
 */
public class EasyExcelUtils {
    private static final String CONTENT_TYPE = "application/vnd.ms-excel";
    private static final String CHARACTER_ENCODING = "utf-8";
    private static final String HEADER_S1 = "Content-disposition";
    private static final String HEADER_S2 = "attachment; filename=";


    /**
     * 创建excel
     *
     * 此方法为封装简单excel的导出 ,
     * 表头或者日期等信息写在map ,
     * list数据单独传可以实现简单导出
     *
     * @param response 响应
     * @param fileName 文件名称
     * @param map      map
     * @param list     列表
     * @throws IOException ioexception
     */
    public static void createExcel(HttpServletResponse response, String fileName, Map<String, Object> map, List<?> list) throws IOException {
        // 设置公共头信息
        OutputStream out = null;
        BufferedOutputStream bos = null;
        response.setContentType(CONTENT_TYPE);
        response.setCharacterEncoding(CHARACTER_ENCODING);
        response.setHeader(HEADER_S1, HEADER_S2 + fileName);

        out = response.getOutputStream();
        bos = new BufferedOutputStream(out);
        // 此处可以修改 为 classpath:/file
        ClassPathResource cpr = new ClassPathResource("file" + File.separator + fileName);
        ExcelWriter excelWriter = com.alibaba.excel.EasyExcel.write(bos)
                .withTemplate(cpr.getInputStream()).excelType(ExcelTypeEnum.XLS).build();
        WriteSheet writeSheet = com.alibaba.excel.EasyExcel.writerSheet().build();
        FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
        // 填充 list 数据
        excelWriter.fill(list, fillConfig, writeSheet);
        // 填充 map 数据
        excelWriter.fill(map, writeSheet);
        excelWriter.finish();
        bos.flush();
        bos.close();


    }
}

直接调用即可