解析excel是一个比较常见的功能，多数情况下我们会使用如下两种方式进行处理
- 写一个excel解析工具类，将数据解析到一个List<Object[]>集合中，但这种方式需要我们对数据进行二次处理，将其转换为我们需要的类型；
- 使用easypoi包对excel进行处理，省去了我们自己对excel繁琐解析，同时easypoi解析后的数据就是我们想要的那个实体类，但如果我们想对某些数据做特殊处理，就显得的有点力不存心了。【关于easypoi的用法，有兴趣的童鞋可以自行百度】
下面介绍一下我平日里使用较多的解析方式，这里称之为『模板方式』，就是为第一种解析方式配入模板，使其在解析数据的同时，满足对数据进行特殊处理的要求，并将最终结果返回的我们所需要的实体类中。
poi版本：4.1.2
这里使用的式springboot，模板式配置在yml文件中
1. 模板配置
sys:
# 全局属性
read_row_begin: 2 # 从第几行开始读取
read_row_end: -1 # 到第几行结束【-1 标识所有行】
read_cell_begin: A # 从第几列开始读取
read_cell_end: -1 # 到第几列结束【-1 表示所有列】
user:
# 私有属性
# 私有属性和全局属性中，会优先读取私有属性
# read_row_begin: 2 # 从第几行开始读取
# read_row_end: -1 # 到第几行结束【-1 标识所有行】
# read_cell_begin: A # 从第几列开始读取
# read_cell_end: -1 # 到第几列结束【-1 表示所有列】
property:
name: A
age: B
sex: C
address: D
2. user对象
@data
public class User {
@ApiModelProperty(value = "用户名", required = true, position = 1)
private String name;
@ApiModelProperty(value = "年龄", required = true, position = 2)
@StringValidator(maxLength = 3, minLength = 1, regexp = "^[+-]?[0-9]+$", message = "年龄格式错误")
private String age;
@ApiModelProperty(value = "性别", required = true, position = 3)
private Integer sex;
@ApiModelProperty(value = "地址", position = 4)
private String address;
}
3. 解析工具类
package com.wangfz.test.utils.poi.excel;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.wangfz.test.common.validator.StringValidator;
import com.wangfz.test.constant.ErrorCodes;
import com.wangfz.test.constant.HttpStatus;
import com.wangfz.test.exception.ApiException;
import com.wangfz.test.utils.*;
import com.wangfz.test.utils.date.DateUtil;
import com.wangfz.test.utils.poi.excel.imports.ImportTemplate;
import io.swagger.annotations.ApiModelProperty;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.regex.Pattern;
import static java.util.regex.Pattern.compile;
@Slf4j
public class ExcelUtils<T> {
/**
* @param className 类名，带有路径【如："com.wangfz.user.vo.sys.user.User"】
* @param isVerify 是否校验
* @param path 模板路径 【如："template/import/sysImport.yml"】
* @param node 读取的节点【如："sys.user.property"】
* @param isRecordRowNum 是否记录行号【行号属性必须为rowNum】
* @param precision 小数位数
* @param multipartFile 文件
* @return
* @Author wangfz
* @Description 读取excel
* @date 2019/11/13 18:49
**/
public static <T> List<T> readExcel(String className, boolean isVerify, String path, String node, boolean isRecordRowNum, int precision, MultipartFile multipartFile) throws IOException, NoSuchFieldException {
if (!Optional.ofNullable(multipartFile).isPresent()) {
throw new ApiException(HttpStatus.INTERNAL_SERVER_ERROR, ErrorCodes.MISSING_INFO, "请选择有效的文件地址");
}
List<T> list = Lists.newArrayList();
Workbook workbook = null;
if (multipartFile.getOriginalFilename().endsWith("xlsx")) {
workbook = new XSSFWorkbook(multipartFile.getInputStream());
} else if (multipartFile.getOriginalFilename().endsWith("xls")) {
workbook = new HSSFWorkbook(multipartFile.getInputStream());
}
if (workbook == null) {
throw new ApiException(HttpStatus.INTERNAL_SERVER_ERROR, ErrorCodes.INVALID_DATA, "无效的文件，请选择正确格式的文件");
}
// 读取模板
ImportTemplate template = PropertyYmlUtils.findImpTemplate(path, node);
Integer readRowBegin = Integer.parseInt(template.getReadRowBegin().trim());
Integer readRowEnd = Integer.parseInt(template.getReadRowEnd().trim());
Integer readCellBegin = letterToNumber(template.getReadCellBegin().trim(), true);
Integer readCellEnd = letterToNumber(template.getReadCellEnd().trim(), true);
Map<String, String> propertyMap = template.getProperty();
// 遍历Sheet
for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
Sheet sheet = workbook.getSheetAt(sheetNum);
System.out.println(sheet.getLastRowNum());
if (sheet == null || sheet.getLastRowNum() < readRowBegin - 1) {
continue;
}
// 遍历sheet中数据
for (int rowNum = readRowBegin - 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
if (readRowEnd > -1 && rowNum == readRowEnd) {
break;
}
Row row = sheet.getRow(rowNum);
T t = FormatObject.createClass(className);
int lineNum = 0;
if (isRecordRowNum) {
lineNum = row.getRowNum() + 1;
FormatObject.setProperty(t, "rowNum", lineNum);
}
// 遍历所有列
int maxCellNum = readCellEnd == -1 ? row.getLastCellNum() : readCellEnd;
for (int cellNum = readCellBegin - 1; cellNum < maxCellNum; cellNum++) {
Cell cell = row.getCell(cellNum);
String key = numberToLetter(cellNum + 1);
String property = propertyMap.get(key);
String cellValue = analysisData(t, property, cell, isVerify, lineNum, precision);
}
list.add(t);
}
}
return list;
}
/***********************************************导入EXCEL BEGIN*****************************************************/
/***
* 解析表格数据
* @param obj
* @param attribute 当前列对应实体类的属性
* @param isVerify 时间校验数据格式
* @param lineNum 行号
* @param cell
*/
private static String analysisData(Object obj, String attribute, Cell cell, boolean isVerify, int lineNum, int precision) throws NoSuchFieldException {
if (!Optional.ofNullable(obj).isPresent() || StringUtils.isBlank(attribute)) {
return null;
}
// TODO 获取数据格式后期需优化为通用方法
String cellValue = getCellValue(cell, precision, false, false, null);
setObjectVlue(attribute, obj, cellValue, isVerify, lineNum);
return cellValue;
}
/**
* 读取单元格值
*
* @param cell
* @param precision 精度【若为数字时保留的小数位数】
* @param isAccountant 是否使用会计记数法
* @param isDiscernFormula 是否识别公式
* @param formulaEvaluator 公式识别器
* @return
*/
public static String getCellValue(Cell cell, int precision, boolean isAccountant, boolean isDiscernFormula, FormulaEvaluator formulaEvaluator) {
String cellValue = "";
if (Optional.ofNullable(cell).isPresent()) {
switch (cell.getCellType()) {
case NUMERIC: // 数字
if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
SimpleDateFormat sdf = null;
if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
sdf = new SimpleDateFormat("HH:mm");
} else {// 日期
sdf = new SimpleDateFormat("yyyy-MM-dd");
}
Date date = cell.getDateCellValue();
cellValue = sdf.format(date);
} else if (cell.getCellStyle().getDataFormat() == 58) {
// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
double value = cell.getNumericCellValue();
Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
cellValue = sdf.format(date);
} else {
StringBuilder builder = new StringBuilder("");
builder.append("0");
if (precision > 0) {
builder.append(".");
for (int i = 0; i < precision; i++) {
builder.append("0");
}
}
DecimalFormat df = new DecimalFormat(builder.toString());
cellValue = df.format(cell.getNumericCellValue());
if (isAccountant) {
DecimalFormat accountantDf = new DecimalFormat("##,##");
cellValue = accountantDf.format(Double.parseDouble(cellValue));
}
}
break;
case STRING: // 字符串
cellValue = cell.getStringCellValue();
break;
case FORMULA: // 表达式
if (isDiscernFormula && Optional.ofNullable(formulaEvaluator).isPresent()) {
cellValue = getCellValueFormula(cell, formulaEvaluator);
} else {
cellValue = cell.getCellFormula();
}
break;
case BOOLEAN: // 字符串
cellValue = StringUtils.valueOf(cell.getBooleanCellValue());
break;
case BLANK: // 空
break;
case ERROR: // 异常
break;
case _NONE: // 未知类型
break;
default:
break;
}
}
return cellValue;
}
private static String getCellValueFormula(Cell cell, FormulaEvaluator formulaEvaluator) {
if (cell == null || formulaEvaluator == null) {
return null;
}
// 判断是否为公式
// TODO 无法处理拥有外部链接的表格
if (cell.getCellType() == CellType.FORMULA) {
DataFormatter formatter = new DataFormatter();
// excel显示值
// String showValue = formatter.formatCellValue(cell, formulaEvaluator);
String showValue = "";
try {
if (cell.getCellStyle().getDataFormatString().indexOf("%") != -1) {
DecimalFormat df = new DecimalFormat("0.00%");
showValue = df.format(formulaEvaluator.evaluate(cell).getNumberValue());
} else {
DecimalFormat df = new DecimalFormat("0.00");
showValue = df.format(formulaEvaluator.evaluate(cell).getNumberValue());
}
} catch (Exception e) {
showValue = formatter.formatCellValue(cell, formulaEvaluator);
}
return showValue;
}
return null;
}
/***
* 赋值
* @param fieldName 属性
* @param obj 指定对象
* @param cellValue 表格中的数据
* @param isVerify 是否对excel数据校验
* @param lineNum 行号
*/
private static void setObjectVlue(String fieldName, Object obj, String cellValue, boolean isVerify, int lineNum) throws NoSuchFieldException {
Map<String, Object> map = Maps.newHashMap();
Field field = obj.getClass().getDeclaredField(fieldName);
// 校验数据的必填项和格式
if (isVerify) {
if (field.isAnnotationPresent(ApiModelProperty.class)) {
ApiModelProperty amp = field.getAnnotation(ApiModelProperty.class);
if (amp.required() && StringUtils.isBlank(cellValue)) {
String propName = amp.value();
if (StringUtils.isEmpty(propName)) {
propName = amp.name();
}
throw new ApiException(HttpStatus.INTERNAL_SERVER_ERROR, ErrorCodes.MISSING_INFO, propName + "不能为空");
}
}
if (field.isAnnotationPresent(StringValidator.class)) {
StringValidator sv = field.getAnnotation(StringValidator.class);
String regexp = sv.regexp();
int multiple = sv.multiple();
int maxLength = sv.maxLength();
int minLength = sv.minLength();
if (StringUtils.isNotBlank(cellValue) && (StringUtils.isNotBlank(regexp) || multiple > 0 || maxLength > 0 || minLength > 0)) {
String message = sv.message();
if (lineNum > 0) {
message = message.replace("{rowNum}", String.valueOf(lineNum));
}
if (!cellValue.matches(regexp) || (multiple > 0 && cellValue.length() % multiple > 0)
|| (maxLength > 0 && cellValue.length() > maxLength) || (minLength > 0 && cellValue.length() < minLength)) {
throw new ApiException(HttpStatus.INTERNAL_SERVER_ERROR, ErrorCodes.INVALID_DATA, message);
}
}
}
}
if (StringUtils.isBlank(cellValue)) {
return;
}
String typeName = field.getGenericType().getTypeName();
// 获取属性的 JsonFormat 注解，如果有则返回 pattern 属性值；否则返回空
String pattern = "";
if (field.isAnnotationPresent(JsonFormat.class)) {
JsonFormat jsonFormat = field.getAnnotation(JsonFormat.class);
pattern = jsonFormat.pattern();
}
map.put("typeName", typeName);
map.put("pattern", pattern);
Object value = disposeValue(map, cellValue);
FormatObject.setProperty(obj, fieldName, value);
}
/***
* 处理cell中的数据，转换为指定的类型
* @param map
* @param cellValue
* @return
*/
private static Object disposeValue(Map<String, Object> map, String cellValue) {
Object value = null;
String typeName = String.valueOf(map.get("typeName"));
if (Objects.equals(typeName, "java.math.BigDecimal")) {
value = BigDecimalUtils.getBigDecimal(value).doubleValue();
} else if (Objects.equals(typeName, "java.util.Date") || Objects.equals(typeName, "java.time.LocalDate")
|| Objects.equals(typeName, "java.time.LocalDateTime")) {
String pattern = StringUtils.valueOf(map.get("pattern"));
pattern = StringUtils.isBlank(pattern) ? DateUtil.YEAR_MONTH_DAY : pattern;
if (Objects.equals(typeName, "java.util.Date")) {
// 将时间转为字符串
value = DateUtil.converDateTimeToString(cellValue, pattern);
} else if (Objects.equals(typeName, "java.time.LocalDate")) {
value = LocalDate.parse(cellValue, DateTimeFormatter.ofPattern(pattern));
} else if (Objects.equals(typeName, "java.time.LocalDateTime")) {
value = LocalDateTime.parse(cellValue, DateTimeFormatter.ofPattern(pattern));
}
} else if (Objects.equals(typeName, "java.lang.Integer")) {
value = Integer.parseInt(cellValue);
} else {
value = cellValue;
}
return value;
}
/***********************************************导入EXCEL END*****************************************************/
/**
* @param letter 字母
* @param isAllowNumber 是否允许字母为数字
* @return
* @Author wangbt
* @Description 将字母转为数字
* @date 2019/11/19 18:28
**/
public static int letterToNumber(String letter, boolean isAllowNumber) {
// 检查字符串是否为空
if (letter == null || letter.isEmpty()) {
return -1;
}
// 判断是否为数字类型字符串，如果是则直接返回该数字
if (isAllowNumber) {
Pattern pattern = compile("^[-\\+]?[\\d]*$");
if (pattern.matcher(letter).matches()) {
return Integer.parseInt(letter);
}
}
String upperLetter = letter.toUpperCase(); // 转为大写字符串
if (!upperLetter.matches("[A-Z]+")) { // 检查是否符合，不能包含非字母字符
return -1;
}
long num = 0; // 存放结果数值
long base = 1;
// 从字符串尾部开始向头部转换
for (int i = upperLetter.length() - 1; i >= 0; i--) {
char ch = upperLetter.charAt(i);
num += (ch - 'A' + 1) * base;
base *= 26;
if (num > Integer.MAX_VALUE) { // 防止内存溢出
return -1;
}
}
return (int) num;
}
/**
* @param num
* @return
* @Author wangbt
* @Description 将数字转为字母
* @date 2019/11/19 18:30
**/
public static String numberToLetter(int num) {
if (num <= 0) { // 检测列数是否正确
return null;
}
StringBuffer letter = new StringBuffer();
do {
--num;
int mod = num % 26; // 取余
letter.append((char) (mod + 'A')); // 组装字符串
num = (num - mod) / 26; // 计算剩下值
} while (num > 0);
return letter.reverse().toString(); // 返回反转后的字符串
}
public static void main(String[] args) {
// 伪代码
// List<User> users = ExcelUtils.readExcel(User.class.getName, true, "模板路径", "test.user.property", false, 2, multipartFile);
}
}
说明：
- ErrorCodes、HttpStatus、ApiException：自定义异常处理类，实际使用中，如果没有自定义的异常处理，可以直接return异常结果；
- DateUtil：自定义日期时间处理工具类
- StringUtils：继承了org.apache.commons.lang3.StringUtils的自定义字符串处理工具类
- PropertyYmlUtils：yml解析工具类
- StringValidator：自定义字符串校验注解
- FormatObject：格式化对象工具类
4. 使用到的工具类及其相关类如下
4.1 StringUtils
public static String valueOf(Object obj) {
return obj == null ? "" : obj.toString();
}
/**
* @param line 带有下划线的字符串
* @param firstIsUpperCase 首字符是否大写【默认：false】
* @return
* @Author wangfz
* @Description 下划线转换为驼峰
* @date 2019/11/8 17:33
**/
public static String underlineCamel(String line, boolean... firstIsUpperCase) {
String str = "";
if (StringUtils.isBlank(line)) {
return str;
} else {
StringBuilder sb = new StringBuilder();
String[] strArr;
// 不包含下划线，且第二个参数是空的
if (!line.contains("_") && firstIsUpperCase.length == 0) {
sb.append(line.substring(0, 1).toLowerCase()).append(line.substring(1));
str = sb.toString();
} else if (!line.contains("_") && firstIsUpperCase.length != 0) {
if (!firstIsUpperCase[0]) {
sb.append(line.substring(0, 1).toLowerCase()).append(line.substring(1));
str = sb.toString();
} else {
sb.append(line.substring(0, 1).toUpperCase()).append(line.substring(1));
str = sb.toString();
}
} else if (line.contains("_") && firstIsUpperCase.length == 0) {
strArr = line.split("_");
for (String s : strArr) {
sb.append(s.substring(0, 1).toUpperCase()).append(s.substring(1));
}
str = sb.toString();
str = str.substring(0, 1).toLowerCase() + str.substring(1);
} else if (line.contains("_") && firstIsUpperCase.length != 0) {
strArr = line.split("_");
for (String s : strArr) {
sb.append(s.substring(0, 1).toUpperCase()).append(s.substring(1));
}
if (!firstIsUpperCase[0]) {
str = sb.toString();
str = str.substring(0, 1).toLowerCase() + str.substring(1);
} else {
str = sb.toString();
}
}
}
return str;
}
4.2 PropertyYmlUtils
/**
* @Author wangfz
* @Description 获取导入EXCEL模板中指定节点的数据
* @date 2019/12/30 15:42
* @param path 模板路径
* @param node 指定的节点
* @return
**/
public static ImportTemplate findImpTemplate(String path, String node) {
List<String> nodes = StringUtils.getStringSplit(node, ".");
Map<String, Object> map = PropertyYmlUtils.loadYaml(path);
ImportTemplate template = new ImportTemplate();
recursionImpMap(map, nodes, template, "");
return template;
}
/***
* 解析导入模板
* @param map yml中数据
* @param nodes 获取的数据节点
* @param template
* @param previousKey 前一个key【之前所有符合条件的key的拼接】
*/
private static void recursionImpMap(Map<String, Object> map, List<String> nodes, ImportTemplate template, String previousKey) {
Map<String, String> propertyMap = null;
if (Objects.equals(previousKey, nodes.get(nodes.size() - 1))) {
propertyMap = Maps.newHashMap();
template.setProperty(propertyMap);
}
for (Map.Entry<String, Object> entry : map.entrySet()) {
String key = entry.getKey();
Object value = entry.getValue();
// 第一次或者后续的递归
if (nodes.indexOf(key) != -1 || nodes.indexOf(previousKey) != -1) {
if (key.startsWith("read_row_") || key.startsWith("read_cell_")) {
String property = StringUtils.underlineCamel(key);
FormatObject.setProperty(template, property, StringUtils.valueOf(value));
}
if (value instanceof Map) {
String previous = StringUtils.isBlank(previousKey) ? key : previousKey + "." + key;
if (nodes.indexOf(previous) != -1) {
recursionImpMap(FormatObject.objToMap(value), nodes, template, previous);
}
}
}
if (Objects.equals(previousKey, nodes.get(nodes.size() - 1))) {
String property = StringUtils.underlineCamel(key);
propertyMap.put(StringUtils.valueOf(value), property);
}
}
}
ImportTemplate
@ApiModel("导入模板请求信息")
@Data
public class ImportTemplate {
@ApiModelProperty(name = "开始行", position = 1)
private String readRowBegin;
@ApiModelProperty(name = "结束行", example = "-1：所有行", position = 2)
private String readRowEnd;
@ApiModelProperty(name = "开始列", position = 3)
private String readCellBegin;
@ApiModelProperty(name = "结束列", example = "-1：所有列", position = 4)
private String readCellEnd;
@ApiModelProperty(name = "导入的数据属性", example = "包含对象属性和数据位置；key：位置；value：属性", position = 5)
private Map<String, String> property;
}
4.3 StringValidator
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.METHOD, ElementType.FIELD})
public @interface StringValidator {
// 提示信息
String message() default "";
// 校验规则【正则】
String regexp() default "";
// 最大长度
int maxLength() default 0;
// 最小长度
int minLength() default 0;
// 长度为某个数的倍数【0：不限制】
int multiple() default 0;
}
4.4 FormatObject
/**
* @Author wangfz
* @Description 给对象赋值【无法给继承类赋值】
* @date 2019/5/27 17:46
* @param obj
* @param propertyName 指定属性
* @param value 值
* @return
**/
public static void setProperty(Object obj, String propertyName, Object value) {
try {
Field field = obj.getClass().getDeclaredField(propertyName);
field.setAccessible(true);
// 给变量赋值
field.set(obj, value);
} catch (NoSuchFieldException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
/***
* 通过类名创建对象
* @param className
* @param <T>
* @return
*/
public static <T> T createClass(String className) {
try {
T t = (T) Class.forName(className).newInstance();
return t;
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return null;
}
-
到此为止，模板解析excel基本完成，上述方法基本能满足我们大部分需求，如有特殊需求可对ExcelUtils进行改造。没有完美的工具类，只有适合我们解决当下需求的工具类。
-
在实际使用过程中，模板中的配置可以在项目启动时就缓存到redis中，防止频繁去读取yml文件。如果excel的字段特别多或者数据量特别大【5W以上】建议使用最原始的方式，使用模板解析和使用原始解析方式相比，在性能上还是有差距的。
在开发过程中，如果遇到需要大量读取或写入excel，同时对性能又有较高的要求时，在条件允许的情况下，可以使用POI的SAX模式进行读取或使用SXSSF模式进行写入，但这两种模式都仅支持 .xlsx 格式的excel。关于SAX和SXSSF模式可以自行百度哈
907
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
