来源: 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;
@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 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;
}
@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 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)模板二: