SpingBoot解析Excel数据

前言

在最近的工作中，由于导入模板除了前三列（姓名、手机号、实发工资）固定；其余的列不固定，并且可以做到两个模板的数据都能够正常入库进行对应业务处理。

一、数据模板数据展示：

（1）模板一

 （2）模板二

 二、处理思路

观察两个模板的数据；结合面向对象的编程思想设计；我可以将两模板需要的获取的字段属性归纳为5个属性点：
注意：标题头在以下中不算！!!

索引（index）-- 相当于excel中所在列的下标列索引
列名称（name）-- 相当于excel中所在列的名称
列值（value）-- 相当于excel中所在列的值
是否存在合并单元格（isGrossField）-- 相当于excel中所在列的是否有合并单元格
合并的子元素（childs）-- 相当于excel中所在列的是否有合并单元格下所包含的列

三、编程准备

1、实体类创建：

/**
 * @project 
 * @Description
 * @Author songwp
 * @Date 2022/8/17 9:04
 * @Version 1.0.0
 **/
@Data
public class TreeExcel implements Serializable {

    private int index = 0;

    private String name;

    private String value;

    private boolean isGrossField;

    private List<TreeExcel> childs;
}

2、maven依赖的引入

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.0-beta2</version>
 </dependency>

3、文件解析工具类的编写

(1) ExcelHelper

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.enums.CellExtraTypeEnum;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.CellExtra;import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.stream.Collectors;
/**
 * @project
 * @Description 
 * @Author songwp
 * @Date 2022/9/15 9:03
 * @Version 1.0.0
 **/
public class ExcelHelper extends AnalysisEventListener<Map<Integer, String>> {
    protected Logger log = LoggerFactory.getLogger(getClass());
    List<Map<Integer, String>> list = new ArrayList<>();
    ArrayList<String> msg = new ArrayList<>();
    private Integer rowNumber = 0;
    private Integer startDataLine = 1;

    @Override
    public void invoke(Map<Integer, String> data, AnalysisContext context) {
        context.readWorkbookHolder().setIgnoreEmptyRow(false);
        //把数据存储到list中
        if (!context.readRowHolder().getRowType().name().equals("EMPTY")) {
            list.add(data);
            rowNumber++;
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }

    //获取总行数
    public Integer getRowNumber() {
        return rowNumber;
    }

    //判断是否有报错消息，如有报错消息显示报错消息，没有报错消息压入success
    public ArrayList<String> getMsg() {
        return msg;
    }

    public Integer getStartDataLine() {
        return startDataLine;
    }

    /**
     * 检查上传的文件前三列是不是 姓名、手机号、实发工资
     * @param columOneName
     * @param columTwoName
     * @param columThreeName
     */
    public void validateExcelHead(String columOneName,String columTwoName,String columThreeName){
        if (!columOneName.equals("姓名"))
            throw new RuntimeException("第一列不是姓名！");
        if(!columTwoName.equals("手机号"))
            throw new RuntimeException("第二列不是手机号！");
        if(!columThreeName.equals("实发工资"))
            throw new RuntimeException("第三列不是实发工资！");
    }

    /**
     * 获取文件的标题头信息
     * @return
     */
    public List<String> getExcelHead() {
        List<String> headList = new ArrayList<>();
        for (int i = 3; i < list.get(0).size(); i++) {
            headList.add(list.get(0).get(i));
        }
        return headList;
    }

    /**
     * 检查上传文件的标题头信息是否存在空表头
     * @return
     */
    public void checkExcelHeadInfo(int rowNumber) {
        if (rowNumber > 3){
                throw new RuntimeException("上传的文件存在空表头！");
             }
        if (rowNumber == 3){
            for (int i = 0; i < list.get(rowNumber -1).size(); i++) {
                if (StringUtils.isEmpty(list.get(rowNumber -1).get(i)) && StringUtils.isEmpty(list.get(rowNumber -2).get(i)) && StringUtils.isEmpty(list.get(rowNumber -3).get(i))){
                    throw new RuntimeException("上传的文件表头中存在空白单元格！");
                }
            }
        }
        if (rowNumber == 2){
            for (int i = 0; i < list.get(rowNumber -1).size(); i++) {
                if (StringUtils.isEmpty(list.get(rowNumber -1).get(i)) && StringUtils.isEmpty(list.get(rowNumber -2).get(i))){
                    throw new RuntimeException("上传的文件表头中存在空白单元格！");
                }
            }
        }
        if (rowNumber == 1){
            for (int i = 0; i < list.get(0).size(); i++) {
                if (StringUtils.isEmpty(list.get(0).get(i))){
                    throw new RuntimeException("上传的文件表头中存在空白单元格！");
                }
            }
        }
    }

    /**
     * 获取解析后的数据中存在空值的位置信息
     * @param rowNum
     * @return
     */
    public List<String> getExcelContent(int rowNum) {
        List<String> content = new ArrayList<>();
        for (int i = 0; i < list.get(0).size(); i++) {
            if (list.get(rowNum).get(i) == null || list.get(rowNum).get(i).equals("")) {
                msg.add(String.format(list.get(rowNum).get(1)+":第%d行，第%d列为空", rowNum, i));
                log.warn(list.get(rowNum).get(1)+":第%d行，第%d列为空", rowNum, i);
                content.add("");
            }
            else {
                content.add(list.get(rowNum).get(i));
            }
        }
        return content;
    }

    public Map<Integer, String> getExcelRowContent(int rowNum) {
        return list.get(rowNum);
    }

    /**
     * 检查上传的文件中存在重复的手机号
     * @param column
     * @return
     */
    public List<String> checkCertainListRepeat(int column) {
        List<String> certainList = list.stream().map(item->item.get(column)).collect(Collectors.toList());
        List<String> phoneList = certainList.stream().filter(Objects::nonNull).collect(Collectors.toList());
        return StrUtils.getListDuplicateElements(phoneList);
    }

    /**
     * 检查excel中存在空值的位置
     * @return
     */
    public List<String> checkPaseErrorList(boolean flag) {
        List<String> parseErrorLog = new ArrayList<>();
        if (flag) {
            if (list.get(2).get(0) != null){
                for (int i = 2; i < list.size(); i++) {
                    for (int j = 0; j < list.get(i).size(); j++) {
                        if (StringUtils.isEmpty(list.get(i).get(j))) {
                            parseErrorLog.add(String.format("第%d行，第%d列为空", i, j + 1));
                        }
                    }
                }
            }else {
                for (int i = 3; i < list.size(); i++) {
                    for (int j = 0; j < list.get(i).size(); j++) {
                        if (StringUtils.isEmpty(list.get(i).get(j))) {
                            parseErrorLog.add(String.format("第%d行，第%d列为空", i, j + 1));
                        }
                    }
                }
            }
        } else {
            for (int i = 1; i < list.size(); i++) {
                for (int j = 0; j < list.get(i).size(); j++) {
                    if (StringUtils.isEmpty(list.get(i).get(j))) {
                        parseErrorLog.add(String.format("第%d行，第%d列为空", i, j + 1));
                    }
                }
            }
        }
        return parseErrorLog;
    }

    @Override
    public void extra(CellExtra extra, AnalysisContext context) {
        if (extra.getType() == CellExtraTypeEnum.MERGE) {
            if (extra.getRowIndex()==0 && extra.getColumnIndex()==0) {
                startDataLine = extra.getLastRowIndex()+1;
            }
        }
    }
}

(2) ExcelConverter

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;

import java.text.DecimalFormat;

public class ExcelConverter implements Converter<String> {
    @Override
    public Class<?> supportJavaTypeKey() {
        return String.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.NUMBER;
    }

    @Override
    public String convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
        String numStr = cellData.getNumberValue().toPlainString();
        if (numStr.indexOf(".") == -1) {
            return numStr;
        }
        return new DecimalFormat("#0.00").format(cellData.getNumberValue());
    }
}

(3) ExcelTreeUtils

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.enums.CellExtraTypeEnum;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;import java.io.InputStream;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Queue;
import java.util.concurrent.LinkedBlockingQueue;

/**
 * @project
 * @Description excel数据解析成树状结构
 * @Author songwp
 * @Date 2022/9/15 9:03
 * @Version 1.0.0
 **/
public class ExcelTreeUtils implements Serializable {

    private String key;

    private String value = "empty";

    private int excelColumnIndex = -1;

    private List<ExcelTreeUtils> nodes = null;

    public int getExcelColumnIndex() {
        return excelColumnIndex;
    }

    public void setExcelColumnIndex(int excelColumnIndex) {
        this.excelColumnIndex = excelColumnIndex;
    }

    public ExcelTreeUtils() { };

    public ExcelTreeUtils(String key, int excelColumnIndex) {
        this.key = key;
        this.excelColumnIndex = excelColumnIndex;
    }

    public String getKey() {
        return key;
    }

    public String getValue() {
        return value;
    }

    public void setValue(String value) {
        this.value = value;
    }

    public List<ExcelTreeUtils> getNodes() {
        return nodes;
    }

    public void setNodes(List<ExcelTreeUtils> nodes) {
        this.nodes = nodes;
    }

    public void createStructeddJson(JSONArray jsonArray, int valueQueueSize, Queue<String> linkedValue, boolean firstStorey) {
        if (null != nodes && !firstStorey) {
            JSONObject jsonObjectElem = new JSONObject();
            jsonObjectElem.put("isGrossField", true);
            jsonObjectElem.put("name", key);

            JSONArray jsonArrayElem = new JSONArray();
            jsonObjectElem.put("child", jsonArrayElem);
            for (ExcelTreeUtils tree : nodes) {
                tree.createStructeddJson(jsonArrayElem, valueQueueSize, linkedValue, false);
            }
            jsonArray.add(jsonObjectElem);
        } else {
            if (firstStorey) {
                for (ExcelTreeUtils tree : nodes) {
                    tree.createStructeddJson(jsonArray, valueQueueSize, linkedValue, false);
                }
                return;
            }
            JSONObject jsonObjectElem = new JSONObject();
            jsonObjectElem.put("index", valueQueueSize - linkedValue.size());
            jsonObjectElem.put("isGrossField", false);
            jsonObjectElem.put("name", key);
            jsonObjectElem.put("value", linkedValue.poll());
            jsonArray.add(jsonObjectElem);
        }
    }

    /**
     * 获取树状数据结构
     * @param inputStream
     * @return
     */
    public static JSONArray getExcelParseJSON(InputStream inputStream){
        JSONArray resultJsonArray = null;
        try {
            ExcelHelper excelHelper = new ExcelHelper();
            ExcelConverter excelConverter = new ExcelConverter();
            EasyExcel.read(inputStream, excelHelper)
                    .registerConverter(excelConverter)
                    .extraRead(CellExtraTypeEnum.MERGE)
                    .sheet().headRowNumber(0).doRead();
            int startDataLine = excelHelper.getStartDataLine();
            excelHelper.checkExcelHeadInfo(startDataLine);
            ExcelTreeUtils tree = new ExcelTreeUtils();
            Queue<ExcelTreeUtils> queue = new LinkedBlockingQueue<>();
            for (int i = 0; i < startDataLine; i++) {
                Map<Integer, String> lineList = excelHelper.getExcelRowContent(i);
                Queue<ExcelTreeUtils> nextQueue = new LinkedBlockingQueue<>();
                for (int j = 0; j < lineList.size(); j++) {
                    String elem = lineList.get(j);
                    if (null != elem) {
                        if (i == 0) {
                            List<ExcelTreeUtils> nodes = tree.getNodes();
                            if (null == nodes) {
                                nodes = new ArrayList<>();
                                tree.setNodes(nodes);
                            }
                            ExcelTreeUtils currentTree = new ExcelTreeUtils(elem, j);
                            nodes.add(currentTree);
                            queue.add(currentTree);
                        } else {
                            ExcelTreeUtils currentQueueTree = queue.peek();
                            while (j >= queue.peek().getExcelColumnIndex()) {
                                nextQueue.add(queue.poll());
                                currentQueueTree = queue.peek();
                            }
                            // 子标题与父标题相同，则跳过添加嵌套
                            if (elem.equals(currentQueueTree.getKey())) {
                                continue;
                            }
                            List<ExcelTreeUtils> nodes = currentQueueTree.getNodes();
                            if (null == nodes) {
                                nodes = new ArrayList<>();
                                currentQueueTree.setNodes(nodes);
                            }
                            ExcelTreeUtils currentTree = new ExcelTreeUtils(elem, j);
                            nodes.add(currentTree);
                            nextQueue.add(currentTree);
                        }
                    }
                }
                if (i > 0) {
                    queue = nextQueue;
                }
                Queue<ExcelTreeUtils> newQueue = new LinkedBlockingQueue<>();
                while (null != queue.peek()) {
                    ExcelTreeUtils poll = queue.poll();
                    if (null != queue.peek()) {
                        poll.setExcelColumnIndex(queue.peek().getExcelColumnIndex());
                    } else {
                        poll.setExcelColumnIndex(lineList.size());
                    }
                    newQueue.add(poll);
                }
                queue = newQueue;
            }
            Queue<String> valueQueue = new LinkedBlockingQueue<>();
            resultJsonArray = new JSONArray();
            for (int i = startDataLine; i < excelHelper.getRowNumber(); i++) {
                Map<Integer, String>  lineList = excelHelper.getExcelRowContent(i);
                for (int j = 0; j < lineList.size(); j++) {
                    String elem = lineList.get(j);
                    if (null == elem) elem = "";
                    valueQueue.add(elem);
                }
                JSONArray jsonArray = new JSONArray();
                tree.createStructeddJson(jsonArray, valueQueue.size(), valueQueue, true);
                resultJsonArray.add(jsonArray);
            }
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage());
        }
        return resultJsonArray;
    }
}

4、测试类的编写

 public static void main(String[] args) throws Exception {
        InputStream fis = new FileInputStream("C:\\Users\\Lenovo\\Desktop\\工作簿1.xlsx");
        JSONArray jsonArray = getExcelParseJSON(fis);
        System.out.println(jsonArray);
        TreeExcel treeExcel = new TreeExcel();
        for (Object salaryObject : jsonArray) {
            JSONArray salaryInfo = (JSONArray) salaryObject;
            treeExcel.setIndex(Integer.parseInt(((JSONObject) salaryInfo.get(0)).getString("index")));
            treeExcel.setName(((JSONObject) salaryInfo.get(0)).getString("name"));
            treeExcel.setValue(((JSONObject) salaryInfo.get(0)).getString("value"));
            treeExcel.setGrossField(Boolean.getBoolean(((JSONObject) salaryInfo.get(0)).getString("isGrossField")));
        }
        System.out.println(treeExcel);
    }

5、日志输出展示

 注意：因为自己的实体在循环外面创建的；所以循环里面赋值存在覆盖；所以获取到的是最后一条数据对应信息

（1）模板一：

（2）模板二：