com.iceolive.util.ExcelUtil Maven / Gradle / Ivy
The newest version!
package com.iceolive.util;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.iceolive.util.annotation.ExcelColumn;
import com.iceolive.util.constants.ValidationConsts;
import com.iceolive.util.enums.ColumnType;
import com.iceolive.util.enums.RuleType;
import com.iceolive.util.exception.ImageOutOfBoundsException;
import com.iceolive.util.model.*;
import com.iceolive.xpathmapper.XPathMapper;
import com.monitorjbl.xlsx.StreamingReader;
import com.networknt.schema.JsonSchema;
import com.networknt.schema.JsonSchemaFactory;
import com.networknt.schema.SpecVersionDetector;
import com.networknt.schema.ValidationMessage;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.builder.CompareToBuilder;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.PackagePart;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.*;
import org.apache.xmlbeans.XmlException;
import org.apache.xmlbeans.XmlObject;
import javax.validation.ConstraintViolation;
import javax.validation.Valid;
import javax.validation.Validation;
import javax.validation.Validator;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.math.BigDecimal;
import java.sql.Time;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.*;
import java.util.function.Function;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* @author wangmianzhe
*/
@SuppressWarnings("unchecked")
public class ExcelUtil {
private static Validator validator = null;
private static Validator getValidatorInstance() {
if (validator == null) {
createValidatorInstance();
}
return validator;
}
private static synchronized Validator createValidatorInstance() {
if (validator == null) {
validator = Validation.buildDefaultValidatorFactory()
.getValidator();
}
return validator;
}
/**
* 导入excel
*
* @param filepath excel文件路径
* @param clazz 中间类类型
* @param faultTolerant 是否容错，验证是所有数据先验证后在一条条导入。true表示不需要全部数据都符合验证，false则表示必须全部数据符合验证才执行导入。
* @param
* @return
*/
public static ImportResult importExcel(
String filepath, Class clazz,
boolean faultTolerant) {
return importExcel(filepath, clazz, faultTolerant, 1, null, null);
}
/**
* 导入excel
*
* @param filepath excel文件路径
* @param clazz 中间类类型
* @param faultTolerant 是否容错，验证是所有数据先验证后在一条条导入。true表示不需要全部数据都符合验证，false则表示必须全部数据符合验证才执行导入。
* @param startRow 开始行数，从1开始，当第一行是标题，则传1，当第二行是标题则传2。
* @param
* @return
*/
public static ImportResult importExcel(
String filepath, Class clazz,
boolean faultTolerant, int startRow) {
return importExcel(filepath, clazz, faultTolerant, startRow, null, null);
}
/**
* 导入excel
*
* @param filepath excel文件路径
* @param clazz 中间类类型
* @param faultTolerant 是否容错，验证是所有数据先验证后在一条条导入。true表示不需要全部数据都符合验证，false则表示必须全部数据符合验证才执行导入。
* @param importFunc 一条条入库的方法,只有验证通过的数据才会进入此方法。如果你是批量入库，请自行获取结果的成功列表,此参数传null。返回true表示入库成功，入库失败提示请抛一个带message的Exception。
* @param
* @return
*/
public static ImportResult importExcel(
String filepath, Class clazz,
boolean faultTolerant,
Function importFunc) {
return importExcel(filepath, clazz, faultTolerant, 1, null, importFunc);
}
/**
* 导入excel
*
* @param filepath excel文件路径
* @param clazz 中间类类型
* @param faultTolerant 是否容错，验证是所有数据先验证后在一条条导入。true表示不需要全部数据都符合验证，false则表示必须全部数据符合验证才执行导入。
* @param startRow 开始行数，从1开始，当第一行是标题，则传1，当第二行是标题则传2。
* @param importFunc 一条条入库的方法,只有验证通过的数据才会进入此方法。如果你是批量入库，请自行获取结果的成功列表,此参数传null。返回true表示入库成功，入库失败提示请抛一个带message的Exception。
* @param
* @return
*/
public static ImportResult importExcel(
String filepath, Class clazz,
boolean faultTolerant, int startRow,
Function importFunc) {
return importExcel(filepath, clazz, faultTolerant, startRow, null, importFunc);
}
/**
* 导入excel
*
* @param filepath excel文件路径
* @param clazz 中间类类型
* @param faultTolerant 是否容错，验证是所有数据先验证后在一条条导入。true表示不需要全部数据都符合验证，false则表示必须全部数据符合验证才执行导入。
* @param customValidateFunc {@code 自定义验证的方法，一般简单验证写在字段注解中，这里处理复杂验证，如身份证格式等，不需要请传null。如果验证错误,则返回List,由于一行数据可能有多个错误，所以用List。如果验证通过返回null或空list即可}
* @param importFunc 一条条入库的方法,只有验证通过的数据才会进入此方法。如果你是批量入库，请自行获取结果的成功列表,此参数传null。返回true表示入库成功，入库失败提示请抛一个带message的Exception。
* @param
* @return
*/
public static ImportResult importExcel(
String filepath, Class clazz,
boolean faultTolerant,
Function> customValidateFunc,
Function importFunc) {
return importExcel(filepath, clazz, faultTolerant, 1, customValidateFunc, importFunc);
}
/**
* 导入excel
*
* @param filepath excel文件路径
* @param clazz 中间类类型
* @param startRow 开始行数，从1开始，当第一行是标题，则传1，当第二行是标题则传2。
* @param faultTolerant 是否容错，验证是所有数据先验证后在一条条导入。true表示不需要全部数据都符合验证，false则表示必须全部数据符合验证才执行导入。
* @param customValidateFunc {@code 自定义验证的方法，一般简单验证写在字段注解中，这里处理复杂验证，如身份证格式等，不需要请传null。如果验证错误,则返回List,由于一行数据可能有多个错误，所以用List。如果验证通过返回null或空list即可}
* @param importFunc 一条条入库的方法,只有验证通过的数据才会进入此方法。如果你是批量入库，请自行获取结果的成功列表,此参数传null。返回true表示入库成功，入库失败提示请抛一个带message的Exception。
* @param 中间类
* @return 返回导入结果
*/
public static ImportResult importExcel(
String filepath, Class clazz,
boolean faultTolerant,
int startRow,
Function> customValidateFunc,
Function importFunc) {
FileInputStream inputStream;
try {
inputStream = new FileInputStream(filepath);
} catch (Exception e) {
throw new RuntimeException(e);
}
return importExcel(inputStream, clazz, faultTolerant, startRow, customValidateFunc, importFunc);
}
/**
* 导入excel
*
* @param inputStream excel文件的字节数组
* @param clazz 中间类类型
* @param faultTolerant 是否容错，验证是所有数据先验证后在一条条导入。true表示不需要全部数据都符合验证，false则表示必须全部数据符合验证才执行导入。
* @param
* @return
*/
public static ImportResult importExcel(
InputStream inputStream, Class clazz,
boolean faultTolerant) {
return importExcel(inputStream, clazz, faultTolerant, 0, null, null);
}
/**
* 导入excel
*
* @param inputStream excel文件的字节数组
* @param clazz 中间类类型
* @param faultTolerant 是否容错，验证是所有数据先验证后在一条条导入。true表示不需要全部数据都符合验证，false则表示必须全部数据符合验证才执行导入。
* @param startRow 开始行数，从1开始，当第一行是标题，则传1，当第二行是标题则传2。
* @param
* @return
*/
public static ImportResult importExcel(
InputStream inputStream, Class clazz,
boolean faultTolerant, int startRow) {
return importExcel(inputStream, clazz, faultTolerant, startRow, null, null);
}
/**
* 导入excel
*
* @param inputStream excel文件的字节数组
* @param clazz 中间类类型
* @param faultTolerant 是否容错，验证是所有数据先验证后在一条条导入。true表示不需要全部数据都符合验证，false则表示必须全部数据符合验证才执行导入。
* @param importFunc 一条条入库的方法,只有验证通过的数据才会进入此方法。如果你是批量入库，请自行获取结果的成功列表,此参数传null。返回true表示入库成功，入库失败提示请抛一个带message的Exception。
* @param
* @return
*/
public static ImportResult importExcel(
InputStream inputStream, Class clazz,
boolean faultTolerant,
Function importFunc) {
return importExcel(inputStream, clazz, faultTolerant, 0, null, importFunc);
}
/**
* 导入excel
*
* @param inputStream excel文件的字节数组
* @param clazz 中间类类型
* @param faultTolerant 是否容错，验证是所有数据先验证后在一条条导入。true表示不需要全部数据都符合验证，false则表示必须全部数据符合验证才执行导入。
* @param startRow 开始行数，从1开始，当第一行是标题，则传1，当第二行是标题则传2。
* @param importFunc 一条条入库的方法,只有验证通过的数据才会进入此方法。如果你是批量入库，请自行获取结果的成功列表,此参数传null。返回true表示入库成功，入库失败提示请抛一个带message的Exception。
* @param
* @return
*/
public static ImportResult importExcel(
InputStream inputStream, Class clazz,
boolean faultTolerant, int startRow,
Function importFunc) {
return importExcel(inputStream, clazz, faultTolerant, startRow, null, importFunc);
}
/**
* 导入excel
*
* @param inputStream excel文件的字节数组
* @param clazz 中间类类型
* @param faultTolerant 是否容错，验证是所有数据先验证后在一条条导入。true表示不需要全部数据都符合验证，false则表示必须全部数据符合验证才执行导入。
* @param customValidateFunc {@code 自定义验证的方法，一般简单验证写在字段注解中，这里处理复杂验证，如身份证格式等，不需要请传null。如果验证错误,则返回List,由于一行数据可能有多个错误，所以用List。如果验证通过返回null或空list即可}
* @param importFunc 一条条入库的方法,只有验证通过的数据才会进入此方法。如果你是批量入库，请自行获取结果的成功列表,此参数传null。返回true表示入库成功，入库失败提示请抛一个带message的Exception。
* @param
* @return
*/
public static ImportResult importExcel(
InputStream inputStream, Class clazz,
boolean faultTolerant,
Function> customValidateFunc,
Function importFunc) {
return importExcel(inputStream, clazz, faultTolerant, 0, customValidateFunc, importFunc);
}
/**
* 导入excel
*
* @param inputStream excel文件的字节数组
* @param clazz 中间类类型
* @param faultTolerant 是否容错，验证是所有数据先验证后在一条条导入。true表示不需要全部数据都符合验证，false则表示必须全部数据符合验证才执行导入。
* @param startRow 开始行数，从1开始，当第一行是标题，则传1，当第二行是标题则传2。
* @param customValidateFunc {@code 自定义验证的方法，一般简单验证写在字段注解中，这里处理复杂验证，如身份证格式等，不需要请传null。如果验证错误,则返回List,由于一行数据可能有多个错误，所以用List。如果验证通过返回null或空list即可}
* @param importFunc 一条条入库的方法,只有验证通过的数据才会进入此方法。如果你是批量入库，请自行获取结果的成功列表,此参数传null。返回true表示入库成功，入库失败提示请抛一个带message的Exception。
* @param 中间类
* @return 返回导入结果
*/
public static ImportResult importExcel(
InputStream inputStream, Class clazz,
boolean faultTolerant,
int startRow,
Function> customValidateFunc,
Function importFunc) {
ImportResult result = new ImportResult();
result.setErrors(new ArrayList<>());
Workbook workbook = null;
try {
if (hasCellImageField(clazz)) {
//如果有图片字段，则不使用StreamingWorkbook
workbook = new XSSFWorkbook(inputStream);
} else {
workbook = StreamingReader.builder()
//缓存到内存中的行数，默认是10
.rowCacheSize(100)
//读取资源时，缓存到内存的字节大小，默认是1024
.bufferSize(4096)
//打开资源，必须，可以是InputStream或者是File，注意：只能打开XLSX格式的文件
.open(inputStream);
}
} catch (Exception e1) {
try {
workbook = new HSSFWorkbook(inputStream);
} catch (Exception e2) {
throw new RuntimeException(e2);
}
}
Sheet sheet = workbook.getSheetAt(0);
//列序号和字段的map
Map> headMap = null;
Map list = new LinkedHashMap<>();
int totalCount = 0;
int titleRowNum = startRow - 1;
for (Row row : sheet) {
if (row.getRowNum() < titleRowNum) {
//小于标题行的抛弃
} else if (row.getRowNum() == titleRowNum) {
headMap = getHeadMap(clazz, row);
} else {
totalCount++;
if (null != row) {
T obj = null;
try {
obj = clazz.newInstance();
} catch (IllegalAccessException | InstantiationException e) {
throw new RuntimeException(e);
}
boolean validate = true;
for (Integer c : headMap.keySet()) {
Cell cell = row.getCell(c);
List fields = headMap.get(c);
//是否日期单元格
boolean isDateCell = SheetUtil.isDateCell(cell);
String dateFormat = "yyyy-MM-dd HH:mm:ss";
try {
if (null != cell) {
String str = SheetUtil.getCellStringValue(cell);
for (Field field : fields) {
Object value = null;
if (isDateCell && (field.getType().isAssignableFrom(Date.class) || field.getType().isAssignableFrom(LocalDateTime.class) || field.getType().isAssignableFrom(LocalDate.class))) {
//特殊处理日期格式
if (!StringUtil.isBlank(str)) {
value = StringUtil.parse(str, dateFormat, field.getType());
}
} else if (isDateCell && (field.getType().isAssignableFrom(LocalTime.class) || field.getType().isAssignableFrom(Time.class))) {
//特殊处理日期格式
if (!StringUtil.isBlank(str)) {
value = StringUtil.parse(str, dateFormat, field.getType());
}
} else if (field.getType().isAssignableFrom(boolean.class) || field.getType().isAssignableFrom(Boolean.class)) {
ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
value = StringUtil.parseBoolean(str, excelColumn.trueString(), excelColumn.falseString(), field.getType());
} else if (field.getType().isArray() && field.getType().getComponentType().equals(byte.class)) {
value = SheetUtil.getCellImageBytes((XSSFWorkbook) workbook, cell);
} else if (field.getType().isAssignableFrom(BufferedImage.class)) {
value = ImageUtil.Bytes2Image(SheetUtil.getCellImageBytes((XSSFWorkbook) workbook, cell));
} else {
value = StringUtil.parse(str, field.getType());
}
field.setAccessible(true);
field.set(obj, value);
}
} else {
//单元格为null，处理图片
for (Field field : fields) {
Object value = null;
if (field.getType().isArray() && field.getType().getComponentType().equals(byte.class)) {
List floatImages = getFloatImagesBytes(sheet, row.getRowNum(), c);
if (!CollectionUtils.isEmpty(floatImages)) {
value = floatImages.get(0);
}
} else if (field.getType().isAssignableFrom(BufferedImage.class)) {
List floatImages = getFloatImagesBytes(sheet, row.getRowNum(), c);
if (!CollectionUtils.isEmpty(floatImages)) {
value = ImageUtil.Bytes2Image(floatImages.get(0));
}
} else if (field.getType().isAssignableFrom(List.class)) {
ParameterizedType genericType = (ParameterizedType) field.getGenericType();
if (genericType.getActualTypeArguments()[0] == BufferedImage.class) {
List floatImages = getFloatImagesBytes(sheet, row.getRowNum(), c);
value = new ArrayList<>();
for (byte[] floatImage : floatImages) {
((List) value).add(ImageUtil.Bytes2Image(floatImage));
}
} else if (genericType.getActualTypeArguments()[0] == byte[].class) {
List floatImages = getFloatImagesBytes(sheet, row.getRowNum(), c);
value = floatImages;
}
}
field.setAccessible(true);
field.set(obj, value);
}
}
} catch (Exception e) {
validate = false;
ImportResult.ErrorMessage errorMessage = new ImportResult.ErrorMessage();
errorMessage.setRow(row.getRowNum());
errorMessage.setCol(CellReference.convertNumToColString(c));
errorMessage.setCell(new CellAddress(row.getRowNum(), c).formatAsString());
if (e instanceof ImageOutOfBoundsException) {
errorMessage.setMessage(e.getMessage());
} else {
errorMessage.setMessage("类型转换错误");
}
result.getErrors().add(errorMessage);
}
}
List validateResults = validate(obj);
validate = isValidate(result, headMap, row, validate, validateResults, clazz);
if (customValidateFunc != null) {
List customValidateResults = customValidateFunc.apply(obj);
validate = isValidate(result, headMap, row, validate, customValidateResults, clazz);
}
if (validate) {
list.put(row.getRowNum(), obj);
}
}
}
}
//设置总记录数
result.setTotalCount(totalCount);
if (list.size() > 0) {
if (faultTolerant || result.getErrors().size() == 0) {
//如果容错模式或是验证全部通过
if (importFunc != null) {
//如果有导入函数
for (Map.Entry m : list.entrySet()) {
try {
if (Boolean.TRUE.equals(importFunc.apply(m.getValue()))) {
result.getSuccesses().put(m.getKey(), m.getValue());
} else {
ImportResult.ErrorMessage errorMessage = new ImportResult.ErrorMessage();
errorMessage.setRow(m.getKey());
errorMessage.setMessage("未抛异常的错误");
result.getErrors().add(errorMessage);
//非容错模式，退出循环
if (!faultTolerant) {
break;
}
}
} catch (Exception e) {
ImportResult.ErrorMessage errorMessage = new ImportResult.ErrorMessage();
errorMessage.setRow(m.getKey());
errorMessage.setMessage(e.getMessage());
result.getErrors().add(errorMessage);
//非容错模式，退出循环
if (!faultTolerant) {
break;
}
}
}
} else {
//没有导入函数
result.setSuccesses(list);
}
}
}
return result;
}
/**
* 获取列序号和字段的对应关系
*
* @param clazz
* @param row 标题行
* @param
* @return
*/
private static Map> getHeadMap(Class clazz, Row row) {
//列序号和字段的map
Map> headMap = new HashMap<>();
for (int c = 0; c < row.getLastCellNum(); c++) {
Cell cell = row.getCell(c);
if (null != cell) {
String title = cell.getStringCellValue();
for (Field field : clazz.getDeclaredFields()) {
ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
if (excelColumn != null) {
if (StringUtil.isNotEmpty(excelColumn.value())) {
if (excelColumn.value().equals(title)) {
if (headMap.containsKey(c)) {
headMap.get(c).add(field);
} else {
List fieldList = new ArrayList<>();
fieldList.add(field);
headMap.put(c, fieldList);
}
}
} else {
//如果ExcelColumn不指定名称，则使用字段名匹配
if (field.getName().equals(title)) {
if (headMap.containsKey(c)) {
headMap.get(c).add(field);
} else {
List fieldList = new ArrayList<>();
fieldList.add(field);
headMap.put(c, fieldList);
}
}
}
}
}
}
}
return headMap;
}
/**
* 根据验证结果填充错误信息
*
* @param result
* @param headMap
* @param row
* @param validate
* @param validateResults
* @return
*/
private static boolean isValidate(ImportResult result, Map> headMap, Row row, boolean validate, List validateResults, Class clazz) {
if (validateResults != null && !validateResults.isEmpty()) {
validate = false;
for (ValidateResult v : validateResults) {
//错误是否在单元格内
boolean errorInCell = false;
for (Map.Entry> m : headMap.entrySet()) {
List fields = m.getValue();
boolean stop = false;
for (Field field : fields) {
if (field.getName().equals(v.getFieldName())) {
ImportResult.ErrorMessage errorMessage = new ImportResult.ErrorMessage();
errorMessage.setRow(row.getRowNum());
errorMessage.setCol(CellReference.convertNumToColString(m.getKey()));
errorMessage.setCell(new CellAddress(row.getRowNum(), m.getKey()).toString());
errorMessage.setMessage(v.getMessage());
result.getErrors().add(errorMessage);
stop = true;
errorInCell = true;
break;
}
}
if (stop) {
break;
}
}
if (!errorInCell) {
String fieldName = v.getFieldName();
String columnName = fieldName;
Field field = Arrays.stream(clazz.getDeclaredFields()).filter(m -> m.getName().equals(fieldName)).findFirst().orElse(null);
if (field != null) {
String title = field.getAnnotation(ExcelColumn.class).value();
if (StringUtil.isNotEmpty(title)) {
columnName = title;
}
}
//如果错误不在单元格内，不
ImportResult.ErrorMessage errorMessage = new ImportResult.ErrorMessage();
errorMessage.setRow(row.getRowNum());
errorMessage.setMessage(v.getMessage() + "\n请检查[" + columnName + "]列是否存在");
result.getErrors().add(errorMessage);
}
}
}
return validate;
}
/**
* 根据注解验证对象
*
* @param obj 验证的对象
* @return 返回验证列表
*/
public static List validate(@Valid Object obj) {
List result = new ArrayList<>();
Set> validateSet = getValidatorInstance()
.validate(obj, new Class[0]);
if (!CollectionUtils.isEmpty(validateSet)) {
validateSet.stream().forEach((v) -> {
String msg = v.getMessage();
if (StringUtil.isEmpty(msg)) {
msg = "参数输入有误";
}
result.add(new ValidateResult(v.getPropertyPath().toString(), msg));
});
}
return result;
}
/**
* json-schema验证
*
* @param schemaJson
* @param obj
* @return
*/
public static List jsonSchemaValidate(String schemaJson, Object obj) {
List result = new ArrayList<>();
try {
ObjectMapper mapper = new ObjectMapper();
JsonNode schemaNode = mapper.readTree(schemaJson);
JsonSchema schema = JsonSchemaFactory.getInstance(SpecVersionDetector.detect(schemaNode)).getSchema(schemaNode);
JsonNode node = mapper.readTree(mapper.writeValueAsString(obj));
Set errors = schema.validate(node);
for (ValidationMessage error : errors) {
String msg = error.getMessage();
String fieldName = error.getPath();
int split = msg.indexOf(":");
if (split > -1) {
fieldName = msg.substring(2, split);
msg = msg.substring(split + 1);
}
result.add(new ValidateResult(fieldName, msg));
}
} catch (JsonProcessingException e) {
throw new RuntimeException(e);
}
return result;
}
/**
* 简单的excel转list
*
* @param filepath
* @return
*/
public static List> excel2List(String filepath) {
List> list = new ArrayList<>();
FileInputStream inputStream = null;
Workbook workbook = null;
try {
inputStream = new FileInputStream(filepath);
workbook = StreamingReader.builder()
//缓存到内存中的行数，默认是10
.rowCacheSize(100)
//读取资源时，缓存到内存的字节大小，默认是1024
.bufferSize(4096)
//打开资源，必须，可以是InputStream或者是File，注意：只能打开XLSX格式的文件
.open(inputStream);
} catch (Exception e1) {
try {
workbook = new HSSFWorkbook(inputStream);
} catch (Exception e2) {
throw new RuntimeException(e2);
}
}
Sheet sheet = workbook.getSheetAt(0);
Map headMap = new HashMap<>();
for (Row row : sheet) {
if (row.getRowNum() == 0) {
for (int c = 0; c < row.getLastCellNum(); c++) {
Cell cell = row.getCell(c);
if (null != cell) {
if (cell.getStringCellValue().length() > 0) {
headMap.put(c, cell.getStringCellValue());
}
}
}
} else {
if (null != row) {
Map obj = new HashMap<>();
for (Integer i : headMap.keySet()) {
Cell cell = row.getCell(i);
//是否日期单元格
String dateFormat = "yyyy-MM-dd HH:mm:ss";
if (null != cell) {
String str = null;
CellType cellType = cell.getCellTypeEnum();
//支持公式单元格
if (cellType == CellType.FORMULA) {
cellType = cell.getCachedFormulaResultTypeEnum();
}
switch (cellType) {
case NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
str = StringUtil.format(cell.getDateCellValue(), dateFormat);
} else {
BigDecimal bd = new BigDecimal(String.valueOf(cell.getNumericCellValue()));
str = bd.stripTrailingZeros().toPlainString();
}
break;
case BOOLEAN:
str = String.valueOf(cell.getBooleanCellValue());
break;
case ERROR:
str = null;
break;
case STRING:
default:
str = cell.getStringCellValue();
break;
}
obj.put(headMap.get(i), str);
}
}
list.add(obj);
}
}
}
return list;
}
/**
* 生成导出excel模板
*
* @param clazz
* @param
* @return
*/
public static byte[] createImportExcelTemplate(Class clazz) {
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet("Sheet1");
int i = 0;
XSSFRow row = sheet.createRow(0);
for (Field field : clazz.getDeclaredFields()) {
ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
if (excelColumn != null) {
XSSFCell cell = row.createCell(i);
cell.setCellValue(excelColumn.value());
i++;
}
}
if (wb != null) {
ByteArrayOutputStream baos = new ByteArrayOutputStream();
try {
wb.write(baos);
baos.close();
} catch (IOException e) {
e.printStackTrace();
}
return baos.toByteArray();
} else {
return null;
}
}
public static List getFloatImagesBytes(Sheet sheet, Integer rowIndex, Integer columnIndex) {
List list = new ArrayList<>();
for (Shape shape : sheet.getDrawingPatriarch()) {
XSSFPicture picture = (XSSFPicture) shape;
XSSFClientAnchor anchor = picture.getClientAnchor();
if (anchor.getRow1() == rowIndex && anchor.getCol1() == columnIndex) {
if (anchor.getRow1() != anchor.getRow2()) {
throw new ImageOutOfBoundsException();
} else if (anchor.getCol1() != anchor.getCol2()) {
throw new ImageOutOfBoundsException();
} else {
list.add(((XSSFPicture) shape).getPictureData().getData());
}
}
}
return list;
}
/**
* 是否有图片字段
*
* @param clazz
* @return
*/
private static boolean hasCellImageField(Class clazz) {
for (Field field : clazz.getDeclaredFields()) {
ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class);
if (excelColumn != null) {
if (field.getType().isAssignableFrom(BufferedImage.class)) {
return true;
} else if (field.getType().isArray() && field.getType().getComponentType().equals(byte.class)) {
return true;
}
}
}
return false;
}
/**
* 导入excel
*
* @param filepath excel文件路径
* @param columnInfos 列信息
* @param faultTolerant 是否容错，验证是所有数据先验证后在一条条导入。true表示不需要全部数据都符合验证，false则表示必须全部数据符合验证才执行导入。
* @return
*/
public static ImportResult importExcel(
String filepath, List columnInfos,
boolean faultTolerant) {
return importExcel(filepath, columnInfos, faultTolerant, 1, null, null);
}
/**
* 导入excel
*
* @param filepath excel文件路径
* @param columnInfos 列信息
* @param faultTolerant 是否容错，验证是所有数据先验证后在一条条导入。true表示不需要全部数据都符合验证，false则表示必须全部数据符合验证才执行导入。
* @param startRow 开始行数，从1开始，当第一行是标题，则传1，当第二行是标题则传2。
* @return
*/
public static ImportResult importExcel(
String filepath, List columnInfos,
boolean faultTolerant,
int startRow) {
return importExcel(filepath, columnInfos, faultTolerant, startRow, null, null);
}
/**
* 导入excel
*
* @param filepath excel文件路径
* @param columnInfos 列信息
* @param faultTolerant 是否容错，验证是所有数据先验证后在一条条导入。true表示不需要全部数据都符合验证，false则表示必须全部数据符合验证才执行导入。
* @param importFunc 一条条入库的方法,只有验证通过的数据才会进入此方法。如果你是批量入库，请自行获取结果的成功列表,此参数传null。返回true表示入库成功，入库失败提示请抛一个带message的Exception。
* @return
*/
public static ImportResult importExcel(
String filepath, List columnInfos,
boolean faultTolerant,
Function, Boolean> importFunc) {
return importExcel(filepath, columnInfos, faultTolerant, 1, null, importFunc);
}
/**
* 导入excel
*
* @param filepath excel文件路径
* @param columnInfos 列信息
* @param faultTolerant 是否容错，验证是所有数据先验证后在一条条导入。true表示不需要全部数据都符合验证，false则表示必须全部数据符合验证才执行导入。
* @param startRow 开始行数，从1开始，当第一行是标题，则传1，当第二行是标题则传2。
* @param importFunc 一条条入库的方法,只有验证通过的数据才会进入此方法。如果你是批量入库，请自行获取结果的成功列表,此参数传null。返回true表示入库成功，入库失败提示请抛一个带message的Exception。
* @return
*/
public static ImportResult importExcel(
String filepath, List columnInfos,
boolean faultTolerant, int startRow,
Function, Boolean> importFunc) {
return importExcel(filepath, columnInfos, faultTolerant, startRow, null, importFunc);
}
/**
* 导入excel
*
* @param filepath excel文件路径
* @param columnInfos 列信息
* @param faultTolerant 是否容错，验证是所有数据先验证后在一条条导入。true表示不需要全部数据都符合验证，false则表示必须全部数据符合验证才执行导入。
* @param customValidateFunc {@code 自定义验证的方法，一般简单验证写在字段注解中，这里处理复杂验证，如身份证格式等，不需要请传null。如果验证错误,则返回List,由于一行数据可能有多个错误，所以用List。如果验证通过返回null或空list即可}
* @param importFunc 一条条入库的方法,只有验证通过的数据才会进入此方法。如果你是批量入库，请自行获取结果的成功列表,此参数传null。返回true表示入库成功，入库失败提示请抛一个带message的Exception。
* @return
*/
public static ImportResult importExcel(
String filepath, List columnInfos,
boolean faultTolerant,
Function, List> customValidateFunc,
Function, Boolean> importFunc) {
return importExcel(filepath, columnInfos, faultTolerant, 1, customValidateFunc, importFunc);
}
/**
* 导入excel
*
* @param filepath excel文件路径
* @param columnInfos 列信息
* @param faultTolerant 是否容错，验证是所有数据先验证后在一条条导入。true表示不需要全部数据都符合验证，false则表示必须全部数据符合验证才执行导入。
* @param startRow 开始行数，从1开始，当第一行是标题，则传1，当第二行是标题则传2。
* @param customValidateFunc {@code 自定义验证的方法，一般简单验证写在字段注解中，这里处理复杂验证，如身份证格式等，不需要请传null。如果验证错误,则返回List,由于一行数据可能有多个错误，所以用List。如果验证通过返回null或空list即可}
* @param importFunc 一条条入库的方法,只有验证通过的数据才会进入此方法。如果你是批量入库，请自行获取结果的成功列表,此参数传null。返回true表示入库成功，入库失败提示请抛一个带message的Exception。
* @return
*/
public static ImportResult importExcel(
String filepath, List columnInfos,
boolean faultTolerant,
int startRow,
Function, List> customValidateFunc,
Function, Boolean> importFunc) {
FileInputStream inputStream;
try {
inputStream = new FileInputStream(filepath);
} catch (Exception e) {
throw new RuntimeException(e);
}
return importExcel(inputStream, columnInfos, faultTolerant, startRow, customValidateFunc, importFunc);
}
/**
* 导入excel
*
* @param inputStream excel文件的字节数组
* @param columnInfos 列信息
* @param faultTolerant 是否容错，验证是所有数据先验证后在一条条导入。true表示不需要全部数据都符合验证，false则表示必须全部数据符合验证才执行导入。
* @return
*/
public static ImportResult importExcel(
InputStream inputStream, List columnInfos,
boolean faultTolerant) {
return importExcel(inputStream, columnInfos, faultTolerant, 1, null, null);
}
/**
* 导入excel
*
* @param inputStream excel文件的字节数组
* @param columnInfos 列信息
* @param faultTolerant 是否容错，验证是所有数据先验证后在一条条导入。true表示不需要全部数据都符合验证，false则表示必须全部数据符合验证才执行导入。
* @param startRow 开始行数，从1开始，当第一行是标题，则传1，当第二行是标题则传2。
* @return
*/
public static ImportResult importExcel(
InputStream inputStream, List columnInfos,
boolean faultTolerant,
int startRow) {
return importExcel(inputStream, columnInfos, faultTolerant, startRow, null, null);
}
/**
* 导入excel
*
* @param inputStream excel文件的字节数组
* @param columnInfos 列信息
* @param faultTolerant 是否容错，验证是所有数据先验证后在一条条导入。true表示不需要全部数据都符合验证，false则表示必须全部数据符合验证才执行导入。
* @param importFunc 一条条入库的方法,只有验证通过的数据才会进入此方法。如果你是批量入库，请自行获取结果的成功列表,此参数传null。返回true表示入库成功，入库失败提示请抛一个带message的Exception。
* @return
*/
public static ImportResult importExcel(
InputStream inputStream, List columnInfos,
boolean faultTolerant,
Function, Boolean> importFunc) {
return importExcel(inputStream, columnInfos, faultTolerant, 1, null, importFunc);
}
/**
* 导入excel
*
* @param inputStream excel文件的字节数组
* @param columnInfos 列信息
* @param faultTolerant 是否容错，验证是所有数据先验证后在一条条导入。true表示不需要全部数据都符合验证，false则表示必须全部数据符合验证才执行导入。
* @param startRow 开始行数，从1开始，当第一行是标题，则传1，当第二行是标题则传2。
* @param importFunc 一条条入库的方法,只有验证通过的数据才会进入此方法。如果你是批量入库，请自行获取结果的成功列表,此参数传null。返回true表示入库成功，入库失败提示请抛一个带message的Exception。
* @return
*/
public static ImportResult importExcel(
InputStream inputStream, List columnInfos,
boolean faultTolerant, int startRow,
Function, Boolean> importFunc) {
return importExcel(inputStream, columnInfos, faultTolerant, startRow, null, importFunc);
}
/**
* 导入excel
*
* @param inputStream excel文件的字节数组
* @param columnInfos 列信息
* @param faultTolerant 是否容错，验证是所有数据先验证后在一条条导入。true表示不需要全部数据都符合验证，false则表示必须全部数据符合验证才执行导入。
* @param customValidateFunc {@code 自定义验证的方法，一般简单验证写在字段注解中，这里处理复杂验证，如身份证格式等，不需要请传null。如果验证错误,则返回List,由于一行数据可能有多个错误，所以用List。如果验证通过返回null或空list即可}
* @param importFunc 一条条入库的方法,只有验证通过的数据才会进入此方法。如果你是批量入库，请自行获取结果的成功列表,此参数传null。返回true表示入库成功，入库失败提示请抛一个带message的Exception。
* @return
*/
public static ImportResult importExcel(
InputStream inputStream, List columnInfos,
boolean faultTolerant,
Function, List> customValidateFunc,
Function, Boolean> importFunc) {
return importExcel(inputStream, columnInfos, faultTolerant, 1, customValidateFunc, importFunc);
}
/**
* 导入excel
*
* @param inputStream excel文件的字节数组
* @param columnInfos 列信息
* @param faultTolerant 是否容错，验证是所有数据先验证后在一条条导入。true表示不需要全部数据都符合验证，false则表示必须全部数据符合验证才执行导入。
* @param startRow 开始行数，从1开始，当第一行是标题，则传1，当第二行是标题则传2。
* @param customValidateFunc {@code 自定义验证的方法，一般简单验证写在字段注解中，这里处理复杂验证，如身份证格式等，不需要请传null。如果验证错误,则返回List,由于一行数据可能有多个错误，所以用List。如果验证通过返回null或空list即可}
* @param importFunc 一条条入库的方法,只有验证通过的数据才会进入此方法。如果你是批量入库，请自行获取结果的成功列表,此参数传null。返回true表示入库成功，入库失败提示请抛一个带message的Exception。
* @return
*/
public static ImportResult importExcel(
InputStream inputStream, List columnInfos,
boolean faultTolerant,
int startRow,
Function, List> customValidateFunc,
Function, Boolean> importFunc) {
ImportResult> result = new ImportResult<>();
result.setErrors(new ArrayList<>());
Workbook workbook = null;
try {
if (columnInfos.stream().anyMatch(m -> m.getType() == ColumnType.IMAGE.getValue() || m.getType() == ColumnType.IMAGES.getValue())) {
//如果有图片字段，则不使用StreamingWorkbook
workbook = new XSSFWorkbook(inputStream);
} else {
workbook = StreamingReader.builder()
//缓存到内存中的行数，默认是10
.rowCacheSize(100)
//读取资源时，缓存到内存的字节大小，默认是1024
.bufferSize(4096)
//打开资源，必须，可以是InputStream或者是File，注意：只能打开XLSX格式的文件
.open(inputStream);
}
} catch (Exception e1) {
try {
workbook = new HSSFWorkbook(inputStream);
} catch (Exception e2) {
throw new RuntimeException(e2);
}
}
Sheet sheet = workbook.getSheetAt(0);
//列序号和字段的map
Map headMap = new HashMap<>();
Map> list = new LinkedHashMap<>();
int totalCount = 0;
int titleRowNum = startRow - 1;
for (Row row : sheet) {
if (row.getRowNum() < titleRowNum) {
//小于标题行的抛弃
} else if (row.getRowNum() == titleRowNum) {
for (int c = 0; c < row.getLastCellNum(); c++) {
Cell cell = row.getCell(c);
if (null != cell) {
String title = cell.getStringCellValue();
ColumnInfo columnInfo = columnInfos.stream().filter(m -> StringUtil.isNotEmpty(m.getTitle()) && m.getTitle().equals(title)).findFirst().orElse(null);
if (columnInfo != null) {
headMap.put(c, columnInfo);
}
}
}
columnInfos.stream().filter(m -> StringUtil.isNotEmpty(m.getColString())).forEach(columnInfo ->
{
int i = CellReference.convertColStringToIndex(columnInfo.getColString());
headMap.put(i, columnInfo);
});
} else {
totalCount++;
if (null != row) {
boolean isEmptyRow = true;
Map obj = new HashMap<>();
boolean validate = true;
for (Integer c : headMap.keySet()) {
ColumnInfo columnInfo = headMap.get(c);
//处理图片
if (columnInfo.getType() == ColumnType.IMAGE.getValue()) {
List floatImages = getFloatImagesBytes(sheet, row.getRowNum(), c);
if (!CollectionUtils.isEmpty(floatImages)) {
obj.put(columnInfo.getName(), floatImages.get(0));
isEmptyRow = false;
}
continue;
} else if (columnInfo.getType() == ColumnType.IMAGES.getValue()) {
List floatImages = getFloatImagesBytes(sheet, row.getRowNum(), c);
obj.put(columnInfo.getName(), floatImages);
if (!CollectionUtils.isEmpty(floatImages)) {
isEmptyRow = false;
}
continue;
}
Cell cell = row.getCell(c);
//是否日期单元格
boolean isDateCell = SheetUtil.isDateCell(cell);
String dateFormat = "yyyy-MM-dd HH:mm:ss";
try {
if (null != cell) {
String str = SheetUtil.getCellStringValue(cell);
if (!StringUtil.isBlank(str)) {
isEmptyRow = false;
}
Object value = null;
if (isDateCell && (columnInfo.getType() == ColumnType.DATETIME.getValue() || columnInfo.getType() == ColumnType.DATE.getValue())) {
//特殊处理日期格式
if (!StringUtil.isBlank(str)) {
value = StringUtil.parse(str, dateFormat, Date.class);
}
} else if (isDateCell && columnInfo.getType() == ColumnType.TIME.getValue()) {
if (!StringUtil.isBlank(str)) {
value = StringUtil.parse(str, dateFormat, Time.class);
}
} else if (columnInfo.getType() == ColumnType.IMAGE.getValue()) {
value = SheetUtil.getCellImageBytes((XSSFWorkbook) workbook, cell);
} else if (columnInfo.getType() == ColumnType.LONG.getValue()) {
value = StringUtil.parse(str, Long.class);
} else if (columnInfo.getType() == ColumnType.DOUBLE.getValue()) {
value = StringUtil.parse(str, Double.class);
} else {
value = str;
}
obj.put(columnInfo.getName(), value);
}
} catch (Exception e) {
validate = false;
ImportResult.ErrorMessage errorMessage = new ImportResult.ErrorMessage();
errorMessage.setRow(row.getRowNum());
errorMessage.setCol(CellReference.convertNumToColString(c));
errorMessage.setCell(new CellAddress(row.getRowNum(), c).formatAsString());
if (e instanceof ImageOutOfBoundsException) {
errorMessage.setMessage(e.getMessage());
} else {
errorMessage.setMessage("类型转换错误");
}
result.getErrors().add(errorMessage);
}
}
if (isEmptyRow) {
//空行时总数-1，且不校验
totalCount--;
continue;
}
List validateResults = ValidateUtil.validate(obj, columnInfos);
validate = ValidateUtil.isValidate(result, headMap, row, validate, validateResults, columnInfos);
if (customValidateFunc != null) {
List customValidateResults = customValidateFunc.apply(obj);
validate = ValidateUtil.isValidate(result, headMap, row, validate, customValidateResults, columnInfos);
}
if (validate) {
list.put(row.getRowNum(), obj);
}
}
}
}
//设置总记录数
result.setTotalCount(totalCount);
if (list.size() > 0) {
if (faultTolerant || result.getErrors().size() == 0) {
//如果容错模式或是验证全部通过
if (importFunc != null) {
//如果有导入函数
for (Map.Entry> m : list.entrySet()) {
try {
if (Boolean.TRUE.equals(importFunc.apply(m.getValue()))) {
result.getSuccesses().put(m.getKey(), m.getValue());
} else {
ImportResult.ErrorMessage errorMessage = new ImportResult.ErrorMessage();
errorMessage.setRow(m.getKey());
errorMessage.setMessage("未抛异常的错误");
result.getErrors().add(errorMessage);
//非容错模式，退出循环
if (!faultTolerant) {
break;
}
}
} catch (Exception e) {
ImportResult.ErrorMessage errorMessage = new ImportResult.ErrorMessage();
errorMessage.setRow(m.getKey());
errorMessage.setMessage(e.getMessage());
result.getErrors().add(errorMessage);
//非容错模式，退出循环
if (!faultTolerant) {
break;
}
}
}
} else {
//没有导入函数
result.setSuccesses(list);
}
}
}
return result;
}
}
© 2015 - 2024 Weber Informatics LLC | Privacy Policy