文章末尾有示例 !
补充说明：
在实际开发中可能会用到字典，也就是excel表中的数据不一定是我们插入到数据库中的数据。
所以我这里解决的问题只是做一个通用的将Excel表转换为实体数据，因此设计过程中尽量的设计为字符串比较好，例如我在开发过程中有用到字典是否，而存进数据库的数据则是0,1这种数据。这个时候我们可以通过遍历更新一下这个字段为真正存入数据库中的数据，也就是0/1这类数据，然后通过MybatisPlus批量插入即可。
更新内容 时间：2021-05-27
- 增加正则截取功能（一个单元格里有多个字段数据的处理方式）
- 新增可空字段nullable，意思是某个单元格允许为空，存入数据库的这个字段可以为null
- 非空字段如果为空将excel的问题行列返回（带标题）异常消息
更新内容 时间：2021-05-29
- 新增excel导出出功能（使用方式见文章内容excel导出）
需求来源：如下的表单，时间这个单元格，设计数据库的时候是将年、季度分开存的，意思是一年有四个季度
设计的时候都把这两个字段设计为了整形（年、季度）。
正则的使用案例
加上注解，以及要截取的内容正则表达式进行截取
import lombok.Data;
import top.yumbo.excel.annotation.ExcelCellBindAnnotation;
import top.yumbo.excel.annotation.ExcelTableHeaderAnnotation;
import top.yumbo.excel.util.BigDecimalUtils;
import top.yumbo.excel.util.ExcelImportOutputUtils;
import java.math.BigDecimal;
/**
* @author jinhua
* @date 2021/5/20 14:18
*/
@Data
@ExcelTableHeaderAnnotation(height = 4, tableName = "区域季度数据")// 表头占4行
public class RegionQuarterETLSyncResponse {
/**
* 年份
*/
@ExcelCellBindAnnotation(title = "时间", exception = "年份格式不正确", pattern = "([0-9]{4})年",nullable = false)
private Integer year;
/**
* 季度，填写1到4的数字
*/
@ExcelCellBindAnnotation(title = "时间", exception = "季度只能是1，2，3，4", pattern = "([1-4]{1})季")
private Integer quarter;
}
使用方式一样，只需要一行代码即可完成转换
/**
* 核心方法，传入泛型（带注解信息），sheet待解析的数据
*/
// 加了注解信息的实体类
List<RegionQuarterETLSyncResponse > list = ExcelImportExportUtils.parseSheetToList(RegionQuarterETLSyncResponse .class, sheet);
将项目下载下来然后运行单元测试中的main即可看到效果
注解示例也在单元测试中，excel表有测试数据
文章内容
如下有一张表（表头合并了单元格）
看到项目中写的代码耦合性很高（硬编码），而且excel的导入导出似乎很多系统都需要用到。
硬编码的缺点很明显，一、不能复用。举个例子现在需要导入一个年度记录的表，那么通过硬编码我们可以很快的写好代码。但是下次要你写导入季度记录的表。那么只能从新写一套代码逻辑解决季度的excel导入
显然硬编码缺陷很多，开发效率低。
一、导入excel的设计思路
采用自定义注解给字段进行注解，这些注解信息理由有标题title表示我这个字段是和excel表中的那一列进行绑定的。
导入excel的作用是excel文件转换为实体，在设计的过程中，这里excel单元格对于类成员变量。
在我设计的框架中单元格和成员变量存在4种关系（实际上只有3种关系）
在框架中对于数值类型的成员变量还可以进行单位换算，只需要在注解上配置一下就行了，一些复杂的单位转换也进行了实现哦
- 一个单元格对于一个成员变量（可以正则获取用于剔除不需要的内容）
- 一个单元格对应多个成员变量（拆分单元格内容）
- 一个成员变量对应多个单元格（合并单元格内容）
- 多个成员变量对应多个单元格（可由上面3中关系构成）
导出的设计和导入的相反，成员属性和单元格的关系和导入类似，到处可以额外补充一些附加内容，例如成员属性值是 2021 导出的内容可以是 第2021年
合并单元格的处理
假设表头有3行，而有用的标题可能是这3行的任意一行，而且存在合并单元格的可能性。
对于合并的单元格我的处理是通过 index索引列 + width合并了多少个单元格（横向）两个维度共同确定了这个标题占据了那些列
例如 地区这个列是由 市+区共同确定的，index=0,width=2。考虑到了这个列的位置会变换所以这里的index是动态得到的
java实体字段-->title标题-->值 因此根据标题可以找到index的位置，根据标题还可以找到是那个字段的，也就是说这个index下的所有数据都是这个字段
使用方式
首先引入依赖，poi-tl、fastjson、spring-beans、lombok
<!-- 这里面只用到了StringUtils做字符串判空其它没啥用可以自己修改源码 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>5.3.7</version>
</dependency>
<!-- 操作excel的依赖工具包 -->
<dependency>
<groupId>com.deepoove</groupId>
<artifactId>poi-tl</artifactId>
<version>1.9.1</version>
</dependency>
<!-- fastjson工具包 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.76</version>
</dependency>
<!-- lombok 工具 -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.20</version>
<scope>provided</scope>
</dependency>
方式一、将后面源码中的3个类复制到你自己的项目即可
我们的需求就是将这个excel表中的记录导入到mysql数据库中，根据表设计好entity，然后加上表头注解，如下示例。
@Data
@ExcelTableHeaderAnnotation(height = 4,tableName = "区域年度数据")// 表头占4行
public class RegionYearETLSyncResponse {
/**
* 地区代码，存储最末一级的地区代码就可以
*/
@ExcelCellBindAnnotation(title = "地区",width = 2,exception = "地区不存在")
private String regionCode;
/**
* 年份
*/
@ExcelCellBindAnnotation(title = "年份",exception = "年份格式不正确")
private Integer year;
/**
* 对于不想返回的则不加注解即可,或者title为 ""
*/
private BigDecimal calGeneralIncomeDivOutcome;
}
然后只需要一行代码即可完成转换
/**
* 核心方法，传入泛型（带注解信息），sheet待解析的数据
*/
// 加了注解信息的实体类
List<RegionYearETLSyncResponse> regionYearETLSyncResponses = ExcelImportExportUtils.parseSheetToList(RegionYearETLSyncResponse.class, sheet);
方式二、直接引入maven依赖（已发布）
已发布
<dependency>
<groupId>top.yumbo.excel</groupId>
<artifactId>excel-import-export</artifactId>
<version>1.0</version>
</dependency>
加注解和使用按照方式一模仿即可
源码
自己设计的导入excel中使用到的3个类，两个注解一个反射工具类
package top.yumbo.excel.annotation;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* 表头注解，表示excel的表头占据多少行（后面都是数据）
*
* @author jinhua
* @date 2021/5/22 23:34
*/
@Target({ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelTableHeaderAnnotation {
/**
* 表头的高度，默认表头高1行
*/
int height() default 1;
/**
* 表名
*/
String tableName() default "Sheet1";
/**
* 模板Excel的在线访问路径，用于导出功能。
* 相当于获取到了模板数据后我们只需要往里面添加数据即可。
* http/https协议的以协议名开头，例如: https://top.yumbo/excel/template/1.xlsx
* 本地文件使用 path:// 开头即可。
* 绝对路径示例->例如：path:///D:/excel/template/1.xlsx
* 相对路径示例->例如：path://src/test/java/yumbo/test/excel/1.xlsx
*/
String resource() default "";
}
package top.yumbo.excel.annotation;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* @author jinhua
* @date 2021/5/21 15:53
*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelCellBindAnnotation {
/**
* 绑定的标题名称，
* 通过扫描单元格表头可以确定表头所在的索引列，然后在根据width就能确定单元格
*/
String title() default "";
/**
* 单元格宽度，对于合并单元格的处理
* 确定表格的位置采用： 下标（解析过程会得到下标） + 单元格的宽度
* 这样就可以确定单元格的位子和占据的宽度
*/
int width() default 1;
/**
* 注入的异常消息，为了校验单元格内容
* 校验失败应该返回的消息提升
*/
String exception() default "";
/**
* 规模，对于BigDecimal类型的需要进行转换
*/
String size() default "1";
/**
* 正则截取单元格内容
* 一个单元格中的部分内容，例如 2020年2季度，只想单独取出年、季度这两个数字
*/
String importPattern() default "";
/**
* 导出的字符串格式化填入，利用StringFormat.format进行字符串占位和替换
*/
String exportFormat() default "";
/**
* 导出功能，该字段可能是多个单元格的内容（连续单元格），按照split拆分填充
*/
String exportSplit() default "";
/**
* 合并多个字段的顺序，多个字段构成一个标题，例如时间 年+季度
*/
int exportPriority() default 1;
/**
* 默认不可以为空
*/
boolean nullable() default false;
/**
* 这行标题在第几行
*/
//int row() default 0;
/**
* 单元格索引位置
*/
int index() default -1;
}
package top.yumbo.excel.util;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.*;
import org.springframework.util.StringUtils;
import top.yumbo.excel.annotation.ExcelCellBindAnnotation;
import top.yumbo.excel.annotation.ExcelTableHeaderAnnotation;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.HashMap;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* @author jinhua
* @date 2021/5/21 21:51
*/
public class ExcelImportExportUtils {
public static final String INCORRECT_FORMAT_EXCEPTION = "格式不正确";
public static final String CONVERT_EXCEPTION = "转换异常";
public static final String NOT_BLANK_EXCEPTION = "不能为空";
public enum ExcelTable {
TABLE_NAME, TABLE_HEADER, TABLE_HEADER_HEIGHT, RESOURCE, TABLE_BODY;
}
public enum ExcelCell {
TITLE_NAME, FIELD_NAME, FIELD_TYPE, SIZE, PATTERN, NULLABLE, WIDTH, EXCEPTION, INDEX, ROW;
}
/**
* 批量保存（更新/插入）
*
* @param tList 传入的数据
* @param iService 默认的批量保存
* @param <T> 泛型
*/
// public static <T> boolean defaultBatchSave(List<T> tList, IService<T> iService) {
// return iService.saveBatch(tList);
// }
/**
* 将sheet解析成List类型的数据（注意这里只是将单元格内容转换为了实体，具体字段可能还不是正确的例如 区域码应该是是具体的编码而不是XX市XX区）
*
* @param tClass 传入的泛型
* @param sheet 表单数据（带表头的）
* @return 只是将单元格内容转化为List
*/
public static <T> List<T> parseSheetToList(Class<T> tClass, Sheet sheet) throws Exception {
JSONArray jsonArray = parseSheetToJSONArray(tClass, sheet);
return JSONArray.parseArray(jsonArray.toJSONString(), tClass);
}
/**
* 将excel表转换为JSONArray
*
* @param tClass 注解模板类
* @param sheet 传入的excel数据
*/
public static <T> JSONArray parseSheetToJSONArray(Class<T> tClass, Sheet sheet) throws Exception {
JSONObject fulledExcelDescData = getFulledExcelDescData(tClass, sheet);
// 根据所有已知信息将excel转换为JsonArray数据
return sheetToJSONArray(fulledExcelDescData, sheet);
}
/**
* 存在模板的情况下
* 将数据填充进入Excel表格
*/
private static Sheet filledListToSheet(List<T> list, Sheet sheet) {
// 得到完整的描述信息
JSONObject fulledExcelDescData = getFulledExcelDescData(T.class, sheet);
Workbook workbook = null;
Sheet cloneSheet = workbook.cloneSheet(workbook.getSheetIndex(sheet));
return null;
}
/**
* 生成简单Excel表
*
* @param list 数据集
* @param sheet excel表格
*/
public static Workbook generateSimpleExcel(List<T> list, Sheet sheet) {
// 根据反射获取表的所有信息
JSONObject excelDescData = getExcelPartDescData(T.class);
// 从所有数据中得到表头描述
JSONObject excelHeaderDescData = getExcelHeaderDescData(excelDescData);
// 从表头得到body数据，里面有title、index、field的绑定关系
JSONObject excelBodyDescData = getExcelBodyDescData(excelDescData);
Sheet filledSheet = filledListToSheet(list, sheet);
return null;
}
// private static Sheet getResource(String resource) {
// String proto = resource.split("://")[0];
// if ("http".equals(proto) || "https".equals(proto)) {
// // 网络下周好这个Excel模板文件
// } else {
// String path = getStringByPattern(resource, proto + "://(.*)");// 文件路径
// }
//
// return
// }
private static JSONArray sheetToJSONArray(JSONObject allExcelDescData, Sheet sheet) throws Exception {
JSONArray result = new JSONArray();
JSONObject tableHeader = allExcelDescData.getJSONObject(ExcelTable.TABLE_HEADER.name());// 表头描述信息
JSONObject tableBody = allExcelDescData.getJSONObject(ExcelTable.TABLE_BODY.name());// 表的身体描述
// 从表头描述信息得到表头的高
Integer headerHeight = tableHeader.getInteger(ExcelTable.TABLE_HEADER_HEIGHT.name());
final int lastRowNum = sheet.getLastRowNum();
// 按行扫描excel表
for (int i = headerHeight; i < lastRowNum; i++) {
Row row = sheet.getRow(i); // 得到第i行数据
JSONObject oneRow = new JSONObject();// 一行数据
int rowNum = i + 1;// 真正excel看到的行号
oneRow.put(ExcelCell.ROW.name(), rowNum);// 记录行号
int count = 0;// 记录空行次数
//将Row转换为JSONObject
for (Object entry : tableBody.values()) {
JSONObject rowDesc = (JSONObject) entry;
// 得到字段的索引位子
Integer index = rowDesc.getInteger(ExcelCell.INDEX.name());
if (index < 0) continue;
Integer width = rowDesc.getInteger(ExcelCell.WIDTH.name());// 得到宽度，如果宽度不为1则需要进行合并多个单元格的内容
String fieldName = rowDesc.getString(ExcelCell.FIELD_NAME.name());// 字段名称
String title = rowDesc.getString(ExcelCell.TITLE_NAME.name());// 标题名称
String fieldType = rowDesc.getString(ExcelCell.FIELD_TYPE.name());// 字段类型
String exception = rowDesc.getString(ExcelCell.EXCEPTION.name());// 转换异常返回的消息
String size = rowDesc.getString(ExcelCell.SIZE.name());// 得到规模
boolean nullable = rowDesc.getBoolean(ExcelCell.NULLABLE.name());
String positionMessage = "第" + rowNum + "行的,第" + (index + 1) + "列 ---标题：" + title + " -- ";
//String nullExceptionMessage = positionMessage + NOT_BLANK_EXCEPTION;
String castExceptionMessage = positionMessage + INCORRECT_FORMAT_EXCEPTION;
// 返回的异常消息
String message = positionMessage + exception;
// 获取合并的单元格值（合并后的结果，逗号分隔）
String value = getMergeString(row, index, width);
// 获取正则表达式，如果有正则，则进行正则截取value（相当于从单元格中取部分）
String pattern = rowDesc.getString(ExcelCell.PATTERN.name());
boolean hasText = StringUtils.hasText(value);
Object castValue = null;
// 默认字段不可以为空，如果注解过程设置为true则不抛异常
if (!nullable) {
// 说明字段不可以为空
if (!hasText) {
// 说明单元格内容为空需要抛异常
count++;
break;
} else {
try {
// 单元格有内容,要么正常、要么异常直接抛不能返回null 直接中止
value = patternConvert(pattern, value);
castValue = cast(value, fieldType, castExceptionMessage, size);
} catch (Exception e) {
throw new Exception(message);
}
}
} else {
// 字段可以为空 （要么正常 要么返回null不会抛异常）
try {
// 单元格内容无关紧要。要么正常转换，要么返回null
value = patternConvert(pattern, value);
castValue = cast(value, fieldType, castExceptionMessage, size);
} catch (Exception e) {
//castValue=null;// 本来初始值就是null
}
}
// 默认添加为null，只有正常才添加正常，否则中途抛异常直接中止
oneRow.put(fieldName, castValue);// 添加数据
}
if (count >= 1) {
break;// 遇到一行关键字段为null需要终止
}
result.add(oneRow);// 添加一条数据
}
return result;
}
/**
* 返回Excel主体数据Body的描述信息
*/
public static JSONObject getExcelBodyDescData(Class<T> clazz) {
JSONObject partDescData = getExcelPartDescData(clazz);
return getExcelBodyDescData(partDescData);
}
/**
* 从json中获取Excel身体部分数据
*/
private static JSONObject getExcelBodyDescData(JSONObject fulledExcelDescData) {
return fulledExcelDescData.getJSONObject(ExcelTable.TABLE_BODY.name());
}
/**
* 返回Excel头部Header的描述信息
*/
public static JSONObject getExcelHeaderDescData(Class<T> clazz) {
JSONObject partDescData = getExcelPartDescData(clazz);
return getExcelHeaderDescData(partDescData);
}
/**
* 从json中获取Excel表头部分数据
*/
private static JSONObject getExcelHeaderDescData(JSONObject fulledExcelDescData) {
return fulledExcelDescData.getJSONObject(ExcelTable.TABLE_HEADER.name());
}
/**
* 传入Sheet获取一个完整的表格描述信息，将INDEX更新
*
* @param excelDescData excel的描述信息
* @param sheet 待解析的excel表格
*/
private static JSONObject parseSheetAndFillTitleIndex(JSONObject excelDescData, Sheet sheet) {
JSONObject tableHeaderDesc = excelDescData.getJSONObject(ExcelTable.TABLE_HEADER.name());
JSONObject tableBodyDesc = excelDescData.getJSONObject(ExcelTable.TABLE_BODY.name());
// 补充table每一项的index信息
tableBodyDesc.forEach((fieldName, cellDesc) -> {
Integer height = tableHeaderDesc.getInteger(ExcelTable.TABLE_HEADER_HEIGHT.name());// 得到表头占据了那几行
// 扫描包含表头的那几行 记录需要记录的标题所在的索引列，填充INDEX
for (int i = 0; i < height; i++) {
Row row = sheet.getRow(i);// 得到第i行数据（在表头内）
// 遍历这行所有单元格，然后得到表头进行比较找到标题和注解上的titleName相同的单元格
row.forEach(cell -> {
// 得到单元格内容（统一为字符串类型）
String title = getStringCellValue(cell);
JSONObject cd = (JSONObject) cellDesc;
// 如果标题相同找到了这单元格，获取单元格下标存入
if (title.equals(cd.getString(ExcelCell.TITLE_NAME.name()))) {
int columnIndex = cell.getColumnIndex();// 找到了则取出索引存入jsonObject
cd.put(ExcelCell.INDEX.name(), columnIndex); // 补全描述信息
}
});
}
});
return excelDescData;
}
/**
* 获取完整的Excel描述信息
*
* @param tClass 模板类
* @param sheet Excel
* @param <T> 泛型
*/
private static <T> JSONObject getFulledExcelDescData(Class<T> tClass, Sheet sheet) {
// 获取表格部分描述信息（根据泛型得到的）
JSONObject partDescData = getExcelPartDescData(tClass);
// 根据相同标题填充index
return parseSheetAndFillTitleIndex(partDescData, sheet);
}
/**
* 获取excel表格的描述信息
* 根据 @ExcelTableHeaderAnnotation 得到表头占多少行，剩下的都是表的数据
* 根据 @ExcelCellBindAnnotation 得到字段和表格表头的映射关系以及宽度
*
* @param clazz 传入的泛型
* @return 所有加了注解需要映射 标题和字段的Map集合
*/
private static JSONObject getExcelPartDescData(Class<?> clazz) {
Field[] fields = clazz.getDeclaredFields();// 获取所有字段
JSONObject excelDescData = new JSONObject();// excel的描述数据
JSONObject tableBody = new JSONObject();// 表中主体数据信息
JSONObject tableHeader = new JSONObject();// 表中主体数据信息
// 1、先得到表头信息
Annotation clazzAnnotation = clazz.getAnnotation(ExcelTableHeaderAnnotation.class);
if (clazzAnnotation != null) {
ExcelTableHeaderAnnotation tableHeaderAnnotation = (ExcelTableHeaderAnnotation) clazzAnnotation;
tableHeader.put(ExcelTable.TABLE_NAME.name(), tableHeaderAnnotation.tableName());// 表的名称
tableHeader.put(ExcelTable.TABLE_HEADER_HEIGHT.name(), tableHeaderAnnotation.height());// 表头的高度
tableHeader.put(ExcelTable.RESOURCE.name(), tableHeaderAnnotation.resource());// 模板excel的访问路径
// 2、得到表的Body信息
for (Field field : fields) {
Annotation[] annotations = field.getDeclaredAnnotations();// 获取字段上所有注解
for (Annotation annotation : annotations) {
if (annotation instanceof ExcelCellBindAnnotation) {// 找到自定义的注解
ExcelCellBindAnnotation annotationTitle = (ExcelCellBindAnnotation) annotation;// 进行强转
JSONObject cellDesc = new JSONObject();// 单元格描述信息
String title = annotationTitle.title(); // 获取标题，如果标题不存在则不进行处理
if (StringUtils.hasText(title)) {
int width = annotationTitle.width();// 获取注解的单元格宽度
String exception = annotationTitle.exception();// 获取异常信息
int index = annotationTitle.index();// 得到默认索引位置（默认-1，对于导出功能有奇效，导入可以不传）
int row = annotationTitle.row();// 标题所在的位置
cellDesc.put(ExcelCell.TITLE_NAME.name(), title);// 标题名称
cellDesc.put(ExcelCell.FIELD_NAME.name(), field.getName());// 字段名称
cellDesc.put(ExcelCell.FIELD_TYPE.name(), field.getType().getTypeName());// 字段的类型
cellDesc.put(ExcelCell.WIDTH.name(), width);// 单元格的宽度（宽度为2代表合并了2格单元格）
cellDesc.put(ExcelCell.EXCEPTION.name(), exception);// 校验如果失败返回的异常消息
cellDesc.put(ExcelCell.INDEX.name(), index);// 默认的索引位置
cellDesc.put(ExcelCell.ROW.name(), row);// 表示这行标题在那一行
cellDesc.put(ExcelCell.SIZE.name(), annotationTitle.size());// 规模,记录规模(亿元/万元)
cellDesc.put(ExcelCell.PATTERN.name(), annotationTitle.pattern());// 正则表达式
cellDesc.put(ExcelCell.NULLABLE.name(), annotationTitle.nullable());// 是否可空
// 以字段名作为key
tableBody.put(field.getName(), cellDesc);// 存入这个标题名单元格的的描述信息，后面还需要补全INDEX
}
}
}
}
}
excelDescData.put(ExcelTable.TABLE_HEADER.name(), tableHeader);// 将表头记录信息注入
excelDescData.put(ExcelTable.TABLE_BODY.name(), tableBody);// 将表的body记录信息注入
return excelDescData;// 返回记录的所有信息
}
/**
* 单元格内容统一返回字符串类型的数据
*/
private static String getStringCellValue(Cell cell) {
String str = "";
if (cell.getCellType() == CellType.STRING) {
str = cell.getStringCellValue();
} else if (cell.getCellType() == CellType.NUMERIC) {
str += cell.getNumericCellValue();
} else if (cell.getCellType() == CellType.BOOLEAN) {
str += cell.getBooleanCellValue();
} else if (cell.getCellType() == CellType.BLANK) {
str = "";
}
return str;
}
/**
* 正则转换
*/
private static String patternConvert(String pattern, String value) {
if (StringUtils.hasText(pattern)) {
// 如果存在正则，则单元格内容根据正则进行截取
value = getStringByPattern(value, pattern);
}
return value;
}
/**
* 获取单元格内容（逗号分隔）
*
* @param row 被取出的行
* @param index 索引位置
* @param width 索引位置+width确定取那几列
* @return 返回合并单元格的内容（单个的则传width=1即可）
*/
private static String getMergeString(Row row, Integer index, Integer width) {
// 合并单元格的处理方式 开始
StringBuilder cellValue = new StringBuilder();
for (int j = 0; j < width; j++) {
String str = "";
// 根据index得到单元格内容
Cell cell = row.getCell(index + j);
// 返回字符串类型的数据
if (cell != null) {
str = getStringCellValue(cell);
if (str == null) {
str = "";
}
}
if (j % 2 == 1) {
cellValue.append(",");
}
cellValue.append(str);
}
// 合并单元格处理结束
return cellValue.toString();//得到单元格内容（合并后的）
}
/**
* 根据正则获取内容（处理嵌套的正则并且得到最内部的字符串）
*
* @param inputString 输入的字符串
* @param patternString 正则表达式字符串
*/
private static String getStringByPattern(String inputString, String patternString) {
String outputString = "";
Pattern pattern = Pattern.compile(patternString);
Matcher matcher = pattern.matcher(inputString);
while (matcher.find()) {
// 匹配最内部的那个正则匹配
outputString = matcher.group(matcher.groupCount());
}
return outputString;
}
/**
* 类型map，如果后续还添加了其他类型则继续往下面添加
*/
static HashMap<String, Class<?>> clazzMap = new HashMap<>();
static {
//如果新增了其他类型则继续put添加
clazzMap.put(BigDecimal.class.getName(), BigDecimal.class);
clazzMap.put(String.class.getName(), String.class);
clazzMap.put(Byte.class.getName(), Byte.class);
clazzMap.put(Short.class.getName(), Short.class);
clazzMap.put(Character.class.getName(), Character.class);
clazzMap.put(Integer.class.getName(), Integer.class);
clazzMap.put(Float.class.getName(), Float.class);
clazzMap.put(Double.class.getName(), Double.class);
clazzMap.put(Long.class.getName(), Long.class);
}
/**
* 根据类型的字符串得到返回类型
*/
private static Object cast(String inputValue, String aClass, String exception, String size) throws ClassCastException {
return cast(inputValue, clazzMap.get(aClass), exception, size);// 调用hashMap返回真正的类型
}
/**
* 类型转换（返回转换的类型如果转换异常则抛出异常消息）
*
* @param inputValue 输入的待转换的字符串
* @param aClass 转换成的类型
* @param exception 异常消息
* @throws ClassCastException 转换异常抛出的异常消息
*/
private static Object cast(String inputValue, Class<?> aClass, String exception, String size) throws ClassCastException {
Object obj = null;
String value;
if (StringUtils.hasText(inputValue)) {
value = inputValue.trim();
} else {
return null;
}
try {
if (aClass == BigDecimal.class) {
obj = new BigDecimal(value).multiply(new BigDecimal(size));// 乘以规模
} else if (aClass == String.class) {
obj = value;//直接返回字符串
} else if (aClass == Integer.class) {
obj = new Double(value).intValue();
} else if (aClass == Long.class) {
obj = Long.parseLong(value.split("\\.")[0]);// 小数点以后的不要
} else if (aClass == Double.class) {
obj = Double.parseDouble(value);
} else if (aClass == Short.class) {
obj = new Double(value).shortValue();
} else if (aClass == Character.class) {
obj = value.charAt(0);
} else if (aClass == Float.class) {
obj = Float.parseFloat(value);
}
} catch (Exception e) {
System.out.println(inputValue);
System.out.println(exception);
throw new ClassCastException("类型转换异常，输入的文本内容：" + inputValue + "\n");
//e.printStackTrace();
}
return obj;
}
}
二、导出excel的设计思路
仔细想了一下，不管是导入功能还是导出功能其实都需要有一个模板，也就是说我上面的导入设计实际上也是根据模板进行设计的。
导出的设计中也存在成员属性和单元格的关系。
导出也同样带了单位转换功能
- 一个成员属性对应一个单元格（自动单位转换，可以额外补充一些填充内容）
- 一个成员属性对应多个单元格（拆分成员属性内容）
- 多个成员属性对应一个单元格（合并成员属性内容）
方案一、注解实现导出
思考了一下注解可以这样设计，通过重复注解来实现多行表头的生成，大致注解是
@ExcelTableHeader(row = "第几行"，/*后面是每一个标题的定位和样式设置*/)
/**
* 细想了一下这种方式实际上在使用过程中是相当麻烦的，
* 本来注解是为了简化开发，结果这个注解需要配置很长的内容，相当的麻烦。
* 故，弃用这种方案。
*/
这种注解的方式对于复杂表头来说，使用起来是非常反人类的，故对于较复杂的excel表不应当这样设计。
思考
需求挖掘
可能会有这种需求，我原先没有excel表，我想要导出一个excel表，这样怎么办呢？
解决方案
细想了一下，要是没有模板，注解方式可以导出一个简单的excel表（标题只能在一行）
方案二、利用模板Excel实现导出（推荐的方案）
设计思路：
获取到模板Excel文件，（表头就不要通过注解配置了），然后我们实际上做的事情只是向这张表插入数据（不去重，去重在数据库层自己就应该处理，该工具只是做数据导出）
插入数据有一个问题，那就是实体中的数据和表达的数据不一致，例如 有一个 年和季度分别是2020、4，我希望这两个数据放在同一个单元格，并且生成的格式如：2020年第4季度
需求1，多个字段合成一个单元格，并且生成自定义文本（例如：2020、4 生成 2020年第4季度，并且放在同一个单元格）
如下方式，首先使用同一个标题值title=时间，然后使用exportFormat 设置导出字符串的格式，用{0}表示代替填充的内容，然后利用exportPriority设置拼串的顺序，这样就可以生成2020年第4季度
/**
* @author jinhua
* @date 2021/5/28 16:12
*/
@Data
@ExcelTableHeaderAnnotation(height = 4, tableName = "区域季度数据")// 表头占4行
public class ExcelImportExportTemplateForQuarter {
/**
* 年份
*/
@ExcelCellBindAnnotation(title = "时间",exportFormat = "{0}年", exportPriority = 1)
private Integer year;
/**
* 季度，填写1到4的数字
*/
@ExcelCellBindAnnotation(title = "时间", exportFormat = "第{0}季", exportPriority = 2)
private Integer quarter;
}
需求2，一个字段拆分成多个单元格内容（"贵阳市,南明区" 拆分成2个单元格内容，"贵阳市"、"南明区"）
如果有输出的格式，可以在{0}前后添加即可
/**
* @author jinhua
* @date 2021/5/19 16:39
*/
@Data
@ExcelTableHeaderAnnotation(height = 4, tableName = "区域年度数据")// 表头占4行
public class ExcelImportTemplateForYear {
/** 先拆分字段然后格式化，然后再拆依次。注意前后拆分的字符是同一个并且注意正则转移一下
* 地区，逻辑是"贵州市,南明区"先按照exportSplit拆分，
* 然后格式化成 "贵州省贵州市,南明区"
* 接着再按照exportSplit拆分成"贵州省贵州市","南明区" 然后按照index + width 填充
*/
@ExcelCellBindAnnotation(title = "地区", width = 2,exportSplit=",", exportFormat="贵州省{0},{1}")
private String regionCode;
}
这种情况下就很简单了，我们只需要填充数据即可了。
@ExcelTableHeaderAnnotation新增 resource，支持http和本地文件
http/https协议的需要提供完整的访问路径，例如 ：http://top.yumbo/excel/template/1、https://top.yumbo/excel/template/1
如果是本地文件则以path://开头，然后提供一个完整的访问路径。
例如path:///D:/excel/1.xlsx。注意path后多了一个/
如果是相对路径不要以/开头
excel导入的测试案例
可以下载源码，运行单元测试中的main方法即可运行得到结果
年度数据表单
这个表单项比较多，只截取部分
季度数据表单
季度部分表单
测试案例运行结果
=====年度数据======
=======
RegionYearETLSyncResponse(regionCode=贵阳市,贵阳市, year=2019, regionGdp=300000000.0, regionGdpPerCapita=1000000.0, regionGdpRank=50.0, regionGdpPerCapitaRank=0.300, regionGdpGrowth=0.500, industryContributeGdp=0.300, generalUrbanizationRate=0.800, enableIncomePerCapita=1000000000.0, financeTotalIncome=1000000000.0, comprehensiveFinance=900000000.0, generalBudgetIncome=100000000.0, taxIncome=100000000.0, nonTaxIncome=100000000.0, governmentFundIncome=100000000.0, superiorSubsidyIncome=100000000.0, returnIncome=100000000.0, generalTransferIncome=100000000.0, specialTransferIncome=100000000.0, financeTotalOutcome=100000000.0, generalBudgetOutcome=100000000.0, generalBudgetIncomeRank=0.600, totalIncomeRank=0.100, calTaxDivGeneralIncome=null, calGeneralDivFinanceOutcome=null, calGeneralIncomeDivOutcome=null, superiorGovernmentGdp=100000000.0, superiorGovernmentTotalIncome=1000000000.0, calRegionDivSuperiorGdp=null, calFinanceIncomeRegionDivSuperior=null)
RegionYearETLSyncResponse(regionCode=贵阳市,南明区, year=2019, regionGdp=400000000.0, regionGdpPerCapita=1010000.0, regionGdpRank=51.0, regionGdpPerCapitaRank=0.310, regionGdpGrowth=0.510, industryContributeGdp=0.310, generalUrbanizationRate=0.810, enableIncomePerCapita=1100000000.0, financeTotalIncome=1100000000.0, comprehensiveFinance=1000000000.0, generalBudgetIncome=200000000.0, taxIncome=200000000.0, nonTaxIncome=200000000.0, governmentFundIncome=200000000.0, superiorSubsidyIncome=200000000.0, returnIncome=200000000.0, generalTransferIncome=200000000.0, specialTransferIncome=200000000.0, financeTotalOutcome=200000000.0, generalBudgetOutcome=200000000.0, generalBudgetIncomeRank=0.610, totalIncomeRank=0.110, calTaxDivGeneralIncome=null, calGeneralDivFinanceOutcome=null, calGeneralIncomeDivOutcome=null, superiorGovernmentGdp=200000000.0, superiorGovernmentTotalIncome=1100000000.0, calRegionDivSuperiorGdp=null, calFinanceIncomeRegionDivSuperior=null)
RegionYearETLSyncResponse(regionCode=贵阳市,云岩区, year=2018, regionGdp=500000000.0, regionGdpPerCapita=1020000.0, regionGdpRank=52.0, regionGdpPerCapitaRank=0.320, regionGdpGrowth=0.520, industryContributeGdp=0.320, generalUrbanizationRate=0.820, enableIncomePerCapita=1200000000.0, financeTotalIncome=1200000000.0, comprehensiveFinance=1100000000.0, generalBudgetIncome=300000000.0, taxIncome=300000000.0, nonTaxIncome=300000000.0, governmentFundIncome=300000000.0, superiorSubsidyIncome=300000000.0, returnIncome=300000000.0, generalTransferIncome=300000000.0, specialTransferIncome=300000000.0, financeTotalOutcome=300000000.0, generalBudgetOutcome=300000000.0, generalBudgetIncomeRank=0.620, totalIncomeRank=0.120, calTaxDivGeneralIncome=null, calGeneralDivFinanceOutcome=null, calGeneralIncomeDivOutcome=null, superiorGovernmentGdp=300000000.0, superiorGovernmentTotalIncome=1200000000.0, calRegionDivSuperiorGdp=null, calFinanceIncomeRegionDivSuperior=null)
RegionYearETLSyncResponse(regionCode=贵阳市,花溪区, year=2017, regionGdp=600000000.0, regionGdpPerCapita=1030000.0, regionGdpRank=53.0, regionGdpPerCapitaRank=0.330, regionGdpGrowth=0.530, industryContributeGdp=0.330, generalUrbanizationRate=0.830, enableIncomePerCapita=1300000000.0, financeTotalIncome=1300000000.0, comprehensiveFinance=1200000000.0, generalBudgetIncome=400000000.0, taxIncome=400000000.0, nonTaxIncome=400000000.0, governmentFundIncome=400000000.0, superiorSubsidyIncome=400000000.0, returnIncome=400000000.0, generalTransferIncome=400000000.0, specialTransferIncome=400000000.0, financeTotalOutcome=400000000.0, generalBudgetOutcome=400000000.0, generalBudgetIncomeRank=0.630, totalIncomeRank=0.130, calTaxDivGeneralIncome=null, calGeneralDivFinanceOutcome=null, calGeneralIncomeDivOutcome=null, superiorGovernmentGdp=400000000.0, superiorGovernmentTotalIncome=1300000000.0, calRegionDivSuperiorGdp=null, calFinanceIncomeRegionDivSuperior=null)
RegionYearETLSyncResponse(regionCode=贵阳市,白云区, year=2019, regionGdp=700000000.0, regionGdpPerCapita=1040000.0, regionGdpRank=54.0, regionGdpPerCapitaRank=0.340, regionGdpGrowth=0.540, industryContributeGdp=0.340, generalUrbanizationRate=0.840, enableIncomePerCapita=1400000000.0, financeTotalIncome=1400000000.0, comprehensiveFinance=1300000000.0, generalBudgetIncome=500000000.0, taxIncome=500000000.0, nonTaxIncome=500000000.0, governmentFundIncome=500000000.0, superiorSubsidyIncome=500000000.0, returnIncome=500000000.0, generalTransferIncome=500000000.0, specialTransferIncome=500000000.0, financeTotalOutcome=500000000.0, generalBudgetOutcome=500000000.0, generalBudgetIncomeRank=0.640, totalIncomeRank=0.140, calTaxDivGeneralIncome=null, calGeneralDivFinanceOutcome=null, calGeneralIncomeDivOutcome=null, superiorGovernmentGdp=500000000.0, superiorGovernmentTotalIncome=1400000000.0, calRegionDivSuperiorGdp=null, calFinanceIncomeRegionDivSuperior=null)
RegionYearETLSyncResponse(regionCode=贵阳市,观山湖区, year=2021, regionGdp=800000000.0, regionGdpPerCapita=1050000.0, regionGdpRank=55.0, regionGdpPerCapitaRank=0.350, regionGdpGrowth=0.550, industryContributeGdp=0.350, generalUrbanizationRate=0.850, enableIncomePerCapita=1500000000.0, financeTotalIncome=1500000000.0, comprehensiveFinance=1400000000.0, generalBudgetIncome=600000000.0, taxIncome=600000000.0, nonTaxIncome=600000000.0, governmentFundIncome=600000000.0, superiorSubsidyIncome=600000000.0, returnIncome=600000000.0, generalTransferIncome=600000000.0, specialTransferIncome=600000000.0, financeTotalOutcome=600000000.0, generalBudgetOutcome=600000000.0, generalBudgetIncomeRank=0.650, totalIncomeRank=0.150, calTaxDivGeneralIncome=null, calGeneralDivFinanceOutcome=null, calGeneralIncomeDivOutcome=null, superiorGovernmentGdp=600000000.0, superiorGovernmentTotalIncome=1500000000.0, calRegionDivSuperiorGdp=null, calFinanceIncomeRegionDivSuperior=null)
=====季度数据======
=======
RegionQuarterETLSyncResponse(year=2021, quarter=4, regionCode=贵阳市,贵阳市, breachNumber=2, breachTotalScale=300000000.0, riskNature=管理失误违约, riskVarieties=标准债券, regionDebtManage=强, calBondsHistoryCredit=是, repayCoordinated=强, cooperationCoordinated=强, sctDeployStatus=无部署)
RegionQuarterETLSyncResponse(year=2021, quarter=3, regionCode=贵阳市,南明区, breachNumber=3, breachTotalScale=400000000.0, riskNature=技术违约, riskVarieties=非标集合产品, regionDebtManage=弱, calBondsHistoryCredit=否, repayCoordinated=弱, cooperationCoordinated=弱, sctDeployStatus=部署中)
RegionQuarterETLSyncResponse(year=2021, quarter=2, regionCode=贵阳市,云岩区, breachNumber=4, breachTotalScale=500000000.0, riskNature=实质违约, riskVarieties=银行贷款或单一产品, regionDebtManage=强, calBondsHistoryCredit=是, repayCoordinated=强, cooperationCoordinated=强, sctDeployStatus=无部署)
RegionQuarterETLSyncResponse(year=2021, quarter=1, regionCode=贵阳市,花溪区, breachNumber=5, breachTotalScale=600000000.0, riskNature=技术违约, riskVarieties=标准债券, regionDebtManage=强, calBondsHistoryCredit=是, repayCoordinated=强, cooperationCoordinated=强, sctDeployStatus=有效部署并应用)
RegionQuarterETLSyncResponse(year=2021, quarter=4, regionCode=贵阳市,白云区, breachNumber=6, breachTotalScale=700000000.0, riskNature=管理失误违约, riskVarieties=标准债券, regionDebtManage=强, calBondsHistoryCredit=是, repayCoordinated=强, cooperationCoordinated=强, sctDeployStatus=部署中)
RegionQuarterETLSyncResponse(year=2021, quarter=3, regionCode=贵阳市,观山湖区, breachNumber=7, breachTotalScale=800000000.0, riskNature=技术违约, riskVarieties=非标集合产品, regionDebtManage=弱, calBondsHistoryCredit=否, repayCoordinated=弱, cooperationCoordinated=弱, sctDeployStatus=无部署)
RegionQuarterETLSyncResponse(year=2020, quarter=3, regionCode=遵义市,遵义市, breachNumber=8, breachTotalScale=900000000.0, riskNature=实质违约, riskVarieties=银行贷款或单一产品, regionDebtManage=强, calBondsHistoryCredit=是, repayCoordinated=强, cooperationCoordinated=强, sctDeployStatus=有效部署并应用)
返回的是一个List集合，得到数据后更新一下部分内容。
因为excel表中的值不一定是存入数据库中的值(可能还需要进行计算)，可以使用java8的forEach更新一下即可
List<RegionYearETLSyncResponse> list = ExcelImportExportUtils.parseSheetToList(RegionYearETLSyncResponse.class, sheet);
// 一次性得到字典
HashMap<String, String> riskNatureMap = sysDictionaryService.getSubEntryDictPromptMap(Constans.RISK_NATURE_INT);
// 更新字段
list.forEach(one -> {
one.setRiskNature(/*处理后得到的值传入*/);
//...其他字段的处理
});
//经过上面set后这个list就是更新后的数据
list与数据库持久层框架的操作
