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）模板二：

posted @ 2022-08-17 10:42 [奮斗] 閱讀(1534) 評論(3) 收藏舉報

刷新頁面返回頂部

奮斗

青春由磨礪而出彩，人生因奮斗而升華

SpingBoot解析Excel數據

前言

一、數據模板數據展示：

（1）模板一

二、處理思路

三、編程準備

1、實體類創建：

2、maven依賴的引入

3、文件解析工具類的編寫

(1) ExcelHelper

(2) ExcelConverter

(3) ExcelTreeUtils

4、測試類的編寫

5、日志輸出展示

（1）模板一：

（2）模板二：

公告

奮 斗

青春由磨礪而出彩，人生因奮斗而升華

SpingBoot解析Excel數據

前言

一、數據模板數據展示：

（1）模板一

二、處理思路

三、編程準備

1、實體類創建：

2、maven依賴的引入

3、文件解析工具類的編寫

(1) ExcelHelper

(2) ExcelConverter

(3) ExcelTreeUtils

4、測試類的編寫

5、日志輸出展示

（1）模板一：

（2）模板二：

公告

奮斗

青春由磨礪而出彩，人生因奮斗而升華

三、編程準備

1、實體類創建：

3、文件解析工具類的編寫

4、測試類的編寫