SpingBoot解析Excel数据

来源： https://www.cnblogs.com/songweipeng/p/16594235.html

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

一、数据模板数据展示：
（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 com.tencent.citybase.generated.extend.exceptions.BusinessException;
import com.tencent.citybase.generated.extend.exceptions.ResultStatus;
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;@Overridepublic void invoke(Map<Integer, String> data, AnalysisContext context) {context.readWorkbookHolder().setIgnoreEmptyRow(false);//把数据存储到list中if (!context.readRowHolder().getRowType().name().equals("EMPTY")) {list.add(data);rowNumber++;}}@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {}//获取总行数public Integer getRowNumber() {return rowNumber;}//判断是否有报错消息，如有报错消息显示报错消息，没有报错消息压入successpublic 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 BusinessException(ResultStatus.EXCEL_ONE_EXCEPTION.message());if(!columTwoName.equals("手机号"))throw new BusinessException(ResultStatus.EXCEL_TWO_EXCEPTION.message());if(!columThreeName.equals("实发工资"))throw new BusinessException(ResultStatus.EXCEL_THREE_EXCEPTION.message());}/*** 获取文件的标题头信息* @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 BusinessException(ResultStatus.UPLOAD_FILE_HEADER_ERR.message());}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 BusinessException(ResultStatus.UPLOAD_FILE_EMPTY_HEADER_EXIST.message());}}}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 BusinessException(ResultStatus.UPLOAD_FILE_EMPTY_HEADER_EXIST.message());}}}if (rowNumber == 1){for (int i = 0; i < list.get(0).size(); i++) {if (StringUtils.isEmpty(list.get(0).get(i))){throw new BusinessException(ResultStatus.UPLOAD_FILE_EMPTY_HEADER_EXIST.message());}}}}/*** 获取解析后的数据中存在空值的位置信息* @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;}@Overridepublic 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> {@Overridepublic Class<?> supportJavaTypeKey() {return String.class;}@Overridepublic CellDataTypeEnum supportExcelTypeKey() {return CellDataTypeEnum.NUMBER;}@Overridepublic 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 com.tencent.citybase.generated.extend.exceptions.BusinessException;
import com.tencent.citybase.generated.extend.exceptions.ResultStatus;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) {e.printStackTrace();throw new BusinessException(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）模板二：

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！