一、功能概述
- 可以校验表头
- 以sheet维度,读取数据
- 可以根据反射,自动把excel中的数据封装到bean
- 主要使用了OPCPackage、XSSFReader、XSSFSheetXMLHandler、XMLReader 读取数据
- 具体的执行demo,下载绑定的代码资源即可
二、BigExcelAnalysisUtil类
package org.example.ljj.util;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.util.SAXHelper;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.example.ljj.util.model.*;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.*;
public class BigExcelAnalysisUtil {
private OPCPackage xlsxPackage;
private class SimpleSheetContentsHandler implements XSSFSheetXMLHandler.SheetContentsHandler {
HashMap<String, String> rowData = null;
List<HashMap<String, String>> datas = null;
public SimpleSheetContentsHandler(List<HashMap<String, String>> datas) {
this.datas = datas;
}
@Override
public void startRow(int rowNum) {
if (datas.size() < rowNum) {
for (int i = 0; i < rowNum; i++) {
datas.add(null);
}
}
rowData = new HashMap<String, String>(16);
}
@Override
public void cell(String cellReference, String formattedValue, XSSFComment comment) {
int thisCol = (new CellReference(cellReference)).getCol();
if (!StringUtils.isEmpty(cellReference)) {
formattedValue = formattedValue.trim();
}
rowData.put(String.valueOf(thisCol), formattedValue);
}
@Override
public void endRow(int rowNum) {
datas.add(rowData);
}
@Override
public void headerFooter(String text, boolean isHeader, String tagName) {
}
}
public WorkBookDataResult process(String filePath, List<SheetRule> sheetrules) throws Exception {
WorkBookDataResult workBookData = new WorkBookDataResult();
workBookData.setSuccess(true);
this.xlsxPackage = OPCPackage.open(filePath, PackageAccess.READ);
ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(this.xlsxPackage);
XSSFReader xssfReader = new XSSFReader(this.xlsxPackage);
StylesTable styles = xssfReader.getStylesTable();
XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
XMLReader parser = SAXHelper.newXMLReader();
int sheetIndex = 0;
List<SheetDataResult> sheetDatas = new ArrayList<>();
while (sheets.hasNext()) {
InputStream sheet = sheets.next();
SheetRule sheetRule = this.getSheetRule(sheetrules, sheetIndex);
if (sheetRule != null) {
String sheetName = sheets.getSheetName();
List<HashMap<String, String>> datas = new ArrayList<>();
XSSFSheetXMLHandler.SheetContentsHandler sheetHandler = new SimpleSheetContentsHandler(datas);
this.processSheet(parser, styles, strings, sheetHandler, sheet);
sheet.close();
System.out.println(sheetName + " rows=" + datas.size());
String sheetAnalysisResult = this.processData(sheetDatas, sheetName, datas, sheetRule);
if (!"SUCCESS".equals(sheetAnalysisResult)) {
workBookData.setErrMsg(sheetAnalysisResult);
workBookData.setSuccess(false);
sheetDatas.clear();
break;
}
}
sheetIndex++;
}
workBookData.setWorkDatas(sheetDatas);
return workBookData;
}
private void processSheet(XMLReader parser, StylesTable styles, ReadOnlySharedStringsTable strings,
XSSFSheetXMLHandler.SheetContentsHandler sheetHandler, InputStream sheetInputStream) throws IOException, SAXException {
ContentHandler handler = new XSSFSheetXMLHandler(styles, strings, sheetHandler, false);
InputSource sheetSource = new InputSource(sheetInputStream);
parser.setContentHandler(handler);
parser.parse(sheetSource);
}
private String processData(List<SheetDataResult> sheetDatas, String sheetName, List<HashMap<String, String>> datas, SheetRule sheetRule) throws Exception {
List<CellRule> cellRules = sheetRule.getCellRules();
if (!checkSheetTitles(sheetRule, datas)) {
return "导入文件的表头和模板的表头不一致!";
}
int startRow = sheetRule.getStartRow();
String mapBeanName = sheetRule.getMapBean();
boolean mapBean = StringUtils.isEmpty(mapBeanName) ? false : true;
HashMap<String, HashMap<String, String>> dictionary = sheetRule.getDictionary();
List<Map<String, Object>> sheetMapDatas = new ArrayList<Map<String, Object>>();
List<Object> sheetBeanDatas = new ArrayList<Object>();
for (int rowIndex = 0 + startRow; rowIndex < datas.size(); rowIndex++) {
if (datas.get(rowIndex) == null || datas.get(rowIndex).isEmpty()) {
continue;
}
HashMap<String, Object> mapData = null;
Object beanData = null;
Class beanClass = null;
if (!mapBean) {
mapData = new HashMap<>(16);
} else {
beanClass = Class.forName(mapBeanName);
beanData = beanClass.newInstance();
}
for (int ls = 0; ls < cellRules.size(); ls++) {
CellRule cellRule = cellRules.get(ls);
String cellData = datas.get(rowIndex).get(String.valueOf(cellRule.getColumnIndex()));
CheckCellInfo checkCellInfo = checkCelldata(cellRule, cellData);
if (!checkCellInfo.isSuccess()) {
return sheetName + "第" + (rowIndex + 1) + "行" + checkCellInfo.getMeg();
} else {
String transformDicName = cellRule.getTransformDicName();
if (!StringUtils.isEmpty(transformDicName)) {
cellData = dictionary.get(transformDicName).get(cellData);
}
if (!mapBean && mapData != null) {
String key = cellRule.getMapColumn();
mapData.put(key, cellData);
} else if (beanClass != null) {
String beanField = cellRule.getBeanFiled();
Field field = beanClass.getDeclaredField(beanField);
field.setAccessible(true);
field.set(beanData, transFiledData(field, cellData));
}
}
}
if (!mapBean) {
sheetMapDatas.add(mapData);
} else {
sheetBeanDatas.add(beanData);
}
}
SheetDataResult sheetDataResult = new SheetDataResult();
sheetDataResult.setSheetIndex(sheetRule.getSheetIndex());
sheetDataResult.setSheetName(sheetName);
if (!mapBean) {
sheetDataResult.setSheetDatas(sheetMapDatas);
sheetDataResult.setRowNum(sheetMapDatas.size());
} else {
sheetDataResult.setSheetBeanDatas(sheetBeanDatas);
sheetDataResult.setRowNum(sheetBeanDatas.size());
}
sheetDatas.add(sheetDataResult);
return "SUCCESS";
}
private boolean checkSheetTitles(SheetRule sheetRule, List<HashMap<String, String>> datas) {
List<List<SheetTitle>> sheetTitles = sheetRule.getSheetTitles();
List<CellRule> cellRules = sheetRule.getCellRules();
if (!CollectionUtils.isEmpty(sheetTitles)) {
List<CellRule> dynamicCellRule = new ArrayList<>();
for (int cl = 0; cl < sheetTitles.get(0).size(); cl++) {
SheetTitle sheetTitle = sheetTitles.get(0).get(cl);
HashMap<String, String> titleRow = datas.get(0);
boolean findTitle = false;
int columnIndex = 0;
if (titleRow != null) {
Iterator<String> titleKye = titleRow.keySet().iterator();
while (titleKye.hasNext()) {
String titleColumn = titleKye.next();
String titleValue = titleRow.get(titleColumn);
if (sheetTitle.getTitleValue().equals(titleValue)) {
sheetTitle.setColumnIndex(Integer.parseInt(titleColumn));
findTitle = true;
columnIndex = Integer.parseInt(titleColumn);
for (int i = 1; i < sheetTitles.size(); i++) {
if (!sheetTitles.get(i).get(cl).getTitleValue().equals(datas.get(i).get(titleColumn))) {
return false;
}
}
break;
}
}
}
if (sheetTitle.isRequire() && !findTitle) {
return false;
}
if (findTitle) {
if (cl < cellRules.size()) {
CellRule cellRule = cellRules.get(cl);
cellRule.setColumnIndex(columnIndex);
dynamicCellRule.add(cellRule);
}
}
}
cellRules.clear();
cellRules.addAll(dynamicCellRule);
}
return true;
}
private static CheckCellInfo checkCelldata(CellRule cellRule, String value) {
CheckCellInfo checkCellInfo = null;
if (cellRule != null) {
checkCellInfo = cellRule.checkData(value);
} else {
checkCellInfo = new CheckCellInfo();
checkCellInfo.setSuccess(true);
}
return checkCellInfo;
}
private static Object transFiledData(Field field, Object data) {
Object value = null;
String fileType = field.getType().getName();
fileType = fileType.substring(fileType.lastIndexOf(".") + 1);
if (data != null && !StringUtils.isEmpty(String.valueOf(data))) {
try {
switch (fileType) {
case "String":
value = String.valueOf(data);
break;
case "int":
value = Integer.parseInt(String.valueOf(data));
break;
case "Short":
value = Short.parseShort(String.valueOf(data));
break;
case "Integer":
value = Integer.parseInt(String.valueOf(data));
break;
case "double":
value = Double.parseDouble(String.valueOf(data));
break;
case "float":
value = Float.parseFloat(String.valueOf(data));
break;
case "Date":
String format = "yyyy-MM-dd HH:mm:ss";
if (!String.valueOf(data).contains(":")) {
if (String.valueOf(data).contains("/")) {
format = "yyyy/MM/dd";
} else {
format = "yyyy-MM-dd";
}
} else {
if (String.valueOf(data).contains("/")) {
format = "yyyy/MM/dd HH:mm:ss";
}
}
SimpleDateFormat sdf = new SimpleDateFormat(format);
;
value = sdf.parse(String.valueOf(data));
break;
case "boolean":
value = Boolean.parseBoolean(String.valueOf(data));
break;
case "char":
value = String.valueOf(data).charAt(0);
break;
case "long":
value = Long.parseLong(String.valueOf(data));
break;
case "Long":
value = Long.parseLong(String.valueOf(data));
break;
default:
value = data;
break;
}
} catch (Exception e) {
System.out.println("数据转换异常!");
e.printStackTrace();
}
}
return value;
}
private SheetRule getSheetRule(List<SheetRule> sheetrules, int sheetIndex) {
if (sheetrules != null && sheetrules.size() > 0) {
for (SheetRule sheetRule : sheetrules) {
if (sheetRule.getSheetIndex() == sheetIndex) {
return sheetRule;
}
}
}
return null;
}
}
三、SheetRuleUtil 类
package org.example.ljj.util;
import org.dom4j.Document;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import org.example.ljj.util.model.*;
import org.example.ljj.util.enums.DataType;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.*;
import java.util.*;
public class SheetRuleUtil {
private static final Logger logger = LoggerFactory.getLogger(SheetRuleUtil.class);
public static List<SheetRule> analysiXml(InputStream in) {
SAXReader saxReader = new SAXReader();
List<SheetRule> sheetRules = new ArrayList<SheetRule>();
try {
if (in != null) {
Document document = saxReader.read(in);
Element rootElement = document.getRootElement();
Iterator<Element> sheets = rootElement.element("sheets").elements().iterator();
while (sheets.hasNext()) {
Element sheet = sheets.next();
SheetRule sheetRule = analysisSheetRule(sheet);
int startColumn = sheetRule.getStartColumn();
List<CellRule> cellRules = analysisCellRules(sheet, startColumn);
sheetRule.setCellRules(cellRules);
HashMap<String, HashMap<String, String>> dictionary = analysisDictionary(sheet);
sheetRule.setDictionary(dictionary);
List<List<SheetTitle>> sheetTitles = analysisSheetTitle(sheet);
sheetRule.setSheetTitles(sheetTitles);
sheetRules.add(sheetRule);
}
}
} catch (Exception e) {
logger.error("解析xml配置异常", e);
} finally {
if (in != null) {
try {
in.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return sheetRules;
}
private static SheetRule analysisSheetRule(Element sheet) {
Boolean isCommon = Boolean.valueOf(sheet.attributeValue("isCommon"));
int sheetIndex = Integer.parseInt(sheet.attributeValue("sheetIndex"));
int startRow = Integer.parseInt(sheet.attributeValue("startRow"));
int startColumn = Integer.parseInt(sheet.attributeValue("startColumn"));
int columnSize = Integer.parseInt(sheet.attributeValue("columnSize"));
String sheetName = sheet.attributeValue("sheetName");
String mapBean = sheet.attributeValue("mapBean");
SheetRule sheetRule = new SheetRule(sheetIndex, startRow, startColumn, columnSize, null, isCommon);
sheetRule.setSheetName(sheetName);
sheetRule.setMapBean(mapBean);
return sheetRule;
}
private static List<CellRule> analysisCellRules(Element sheet, int startColumn) {
Iterator<Element> cells = sheet.element("cells").elements().iterator();
List<CellRule> cellRules = new ArrayList<CellRule>();
int colIndex = 0;
while (cells.hasNext()) {
Element cell = cells.next();
String cellName = cell.attributeValue("cellName");
boolean notNull = "true".equals(cell.attributeValue("notNull")) ? true : false;
String mapColumn = cell.attributeValue("mapColumn");
String beanFiled = cell.attributeValue("beanFiled");
String dataType = cell.attributeValue("dataType");
String transformDicName = cell.attributeValue("transformDicName");
boolean isMulti = "true".equals(cell.attributeValue("isMulti")) ? true : false;
String multiSplit = cell.attributeValue("multiSplit");
String reJoinSplit = cell.attributeValue("reJoinSplit");
String labelTypeCode = cell.attributeValue("labelTypeCode");
CellRule cellRule = null;
switch (DataType.getCodeValue(dataType)) {
case 1:
try {
int sLeg = Integer.parseInt(cell.attributeValue("maxLength"));
boolean checkIllegalChar = "false".equals(cell.attributeValue("checkIllegalChar")) ? false : true;
cellRule = new StringCellRule(cellName, notNull, mapColumn, beanFiled, sLeg, checkIllegalChar);
} catch (Exception e) {
e.printStackTrace();
}
break;
case 2:
int maxLength = Integer.parseInt(cell.attributeValue("maxLength"));
cellRule = new IntegerCellRule(cellName, notNull, mapColumn, beanFiled, maxLength);
break;
case 3:
int maxLg = Integer.parseInt(cell.attributeValue("maxLength"));
int decimalLength = Integer.parseInt(cell.attributeValue("decimalLength"));
cellRule = new DoubleCellRule(cellName, notNull, mapColumn, beanFiled, maxLg, decimalLength);
break;
case 4:
cellRule = new DateCellRule(cellName, notNull, mapColumn, beanFiled);
break;
case 5:
cellRule = new DateTimeCellRule(cellName, notNull, mapColumn, beanFiled);
break;
case 6:
String expression = cell.attributeValue("expression");
cellRule = new RegularCellRule(cellName, notNull, mapColumn, beanFiled, expression);
cellRule.setIsMulti(isMulti);
cellRule.setMultiSplit(multiSplit);
cellRule.setLabelTypeCode(labelTypeCode);
cellRule.setReJoinSplit(reJoinSplit);
break;
}
;
if (cell != null) {
cellRule.setTransformDicName(transformDicName);
}
cellRule.setColumnIndex(startColumn + colIndex);
cellRules.add(cellRule);
colIndex++;
}
return cellRules;
}
private static HashMap<String, HashMap<String, String>> analysisDictionary(Element sheet) {
HashMap<String, HashMap<String, String>> dicsMap = new HashMap<String, HashMap<String, String>>();
Element dicsElement = sheet.element("dics");
if (dicsElement != null) {
Iterator<Element> dics = dicsElement.elements().iterator();
while (dics.hasNext()) {
Element dic = dics.next();
String dicName = dic.attributeValue("name");
Iterator<Element> kv = dic.elements().iterator();
HashMap<String, String> dicMap = new HashMap<String, String>();
while (kv.hasNext()) {
Element dicElementData = kv.next();
String key = dicElementData.attributeValue("key");
String value = dicElementData.getTextTrim();
dicMap.put(key, value);
}
dicsMap.put(dicName, dicMap);
}
}
return dicsMap;
}
private static List<List<SheetTitle>> analysisSheetTitle(Element sheet) {
List<List<SheetTitle>> sheetTitles = new LinkedList<>();
Element titlesElements = sheet.element("titles");
if (titlesElements != null) {
Iterator<Element> titles = titlesElements.elements().iterator();
while (titles.hasNext()) {
List<SheetTitle> list = new LinkedList<>();
Element title = titles.next();
int rowIndex = Integer.parseInt(title.attributeValue("rowIndex"));
Iterator<Element> texts = title.elements().iterator();
while (texts.hasNext()) {
Element value = texts.next();
int colIndex = Integer.parseInt(value.attributeValue("columnIndex"));
String titleValue = value.getTextTrim();
SheetTitle sheetTitle = new SheetTitle(rowIndex, colIndex, titleValue);
boolean require = "false".equals(value.attributeValue("require")) ? false : true;
sheetTitle.setRequire(require);
list.add(sheetTitle);
}
sheetTitles.add(list);
}
}
return sheetTitles;
}
}
其他
SheetContentsHandler 使用讲解