数据导入
功能支持
- 读取excel、CSV
- 加入自定义注解校验数据防重
- 加入导入值跟实体类的类型检查
- 字典表支持(跟导出一个用法)
- 支持多种数据场景校验(Validation自带方法实体类校验)
使用方法
@ApiOperation("Excel导入")
@PostMapping("uploadExcel")
public void uploadExcel() throws Exception {
//1. 读取excel
List<Map<String, Object>> maps = ExcelCSVReadUtils.readBySax(new FileInputStream("C:\\Users\Admin\Desktop\t_sys_user.xls"), 0, 1);
//or 读取CSV
//List<Map<String, Object>> maps = ExcelCSVReadUtils.readCsv(new FileInputStream("C:\Users\Admin\Desktop\t_sys_user.csv"), 1, 1);
//2. 转换结果(支持表头下划线转驼峰)
Result result = MapUtil.map2Object(maps, User.class,1);
Object data = result.getData();
//3. 成功返回true
if(result.isCode()) {
//4. 获取对象
List<User> users = MapUtil.castList(data, User.class);
for (User user : users) {
//5. 脚手架自带 数据校验
ValidatorHelper.validateEntity(user);
}
}
}实体模板
实体注解介绍
@Unique为自定义注解校验数据防重，加在实体类的字段上
@ExcelProperty(converter = GenderConvert.class)字典表用法同导出
不同的是，导入重写的是BaseConverter类的convertToJavaData这个方法
@Override
public String convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
String dictType = getDictType();
//反向替换
if (!StringUtils.isEmpty(cellData.getData()) ){
//告诉我你的字典类型，先从缓存中获取
Map<String, String> dictItemMap = dictCache.get(dictType);
if (dictItemMap == null) {
//没拿到调用实现方法获取字典
GetDictMapping queryDictItem = ApplicationContextHolder.getBean(GetDictMapping.class);
if (queryDictItem != null){
dictItemMap = queryDictItem.getDictMappingReverse(dictType);
//放入缓存
dictCache.put(dictType,dictItemMap);
}
}
String v = dictItemMap.get(cellData.getData());
return (v == null ? Convert.toStr(cellData.getData()) : v);
}
return Convert.toStr(cellData.getData());
}
转换结果
成功返回true
失败返回错误条件提示
代码分析
在common-data-download分支下的upload包下，主要有两个工具类
ExcelCSVReadUtils封装了hutool工具类来读取Excel(03、07格式)、CSV
MapUtil支持表头下划线转驼峰为实体类对象，数据类型读取的值跟实体类的类型不匹配提醒、加入Unique唯一性校验不匹配提醒
附代码
/***
* @ClassName ExcelCSVReadUtils
* @Description: 读取大表的excel工具
* @Author suyuan
**/
public class ExcelCSVReadUtils {
private static Logger logger = LoggerFactory.getLogger(ExcelCSVReadUtils.class);
private List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();
private List<String> headers = new ArrayList<>();
private Map<String, String> aliasHeader;
private int headerRow = 1;
private boolean checkHeader = false;
public ExcelCSVReadUtils(Map<String, String> aliasHeader, int headerRow) {
this.aliasHeader = aliasHeader;
this.headerRow = headerRow;
}
public ExcelCSVReadUtils(Map<String, String> aliasHeader, int headerRow, boolean checkHeader) {
this.aliasHeader = aliasHeader;
this.headerRow = headerRow;
this.checkHeader = checkHeader;
}
/**
* 读取大表的实现方法
*
* @return
*/
private RowHandler createRowHandler() {
return new RowHandler() {
@Override
public void handle(int sheetIndex, int rowIndex, List<Object> rowlist) {
if (isEmpty(rowlist)) {
return;
}
if (headerRow > rowIndex + 1) {
return;
} else if (headerRow == rowIndex + 1) {
rowlist.forEach(e -> headers.add(e.toString()));
//比对表头是否正确;
if (checkHeader) {
boolean headerHandler = checkHeaderHandler();
if (!headerHandler) {
throw new POIException("表头格式不正确!");
}
}
} else {
if (aliasHeader == null || aliasHeader.isEmpty()) {
rows.add(IterUtil.toMap(headers, rowlist));
} else {
rows.add(IterUtil.toMap(aliasHeader(headers, aliasHeader), rowlist));
}
}
}
};
}
public boolean isEmpty(List<Object> list) {
if (list.isEmpty()) {
return true;
}
List<Object> collect = list.stream().filter(e -> e == null || StringUtils.isBlank(e.toString())).collect(Collectors.toList());
return collect.size() == list.size() ? true : false;
}
/**
* @param inputStream 文件流 获取方法推荐使用 new ByteArrayInputStream(multipartFile.getBytes())
* @param sheet 读取的sheet 第一个sheet为 0
* @param headerRow 表头行 第一行是 1
* @param beanType 定义的对象 属性有excel的注解
* @param <T>
* @return
*/
public static <T> List<T> readBySax(InputStream inputStream, int sheet, int headerRow, Class<T> beanType) {
Map<String, String> aliasHeader = getFiledMap(beanType);
List<Map<String, Object>> mapList = readBySax(inputStream, sheet, aliasHeader, headerRow, false);
if (Map.class.isAssignableFrom(beanType)) {
return (List<T>) mapList;
} else {
List<T> beanList = new ArrayList(mapList.size());
Iterator iterator = mapList.iterator();
while (iterator.hasNext()) {
Map<String, Object> map = (Map) iterator.next();
beanList.add(BeanUtil.mapToBean(map, beanType, false));
}
return beanList;
}
}
/**
* @param inputStream 文件流 获取方法推荐使用 new ByteArrayInputStream(multipartFile.getBytes())
* @param sheet 读取的sheet 第一个sheet为 0
* @param headerRow 表头行 第一行是 1
* @param beanType 定义的对象 属性有excel的注解
* @param <T>
* @param checkHeader
* @return
*/
public static <T> List<T> readBySax(InputStream inputStream, int sheet, int headerRow, Class<T> beanType, boolean checkHeader) {
Map<String, String> aliasHeader = getFiledMap(beanType);
List<Map<String, Object>> mapList = readBySax(inputStream, sheet, aliasHeader, headerRow, checkHeader);
if (Map.class.isAssignableFrom(beanType)) {
return (List<T>) mapList;
} else {
List<T> beanList = new ArrayList(mapList.size());
Iterator iterator = mapList.iterator();
while (iterator.hasNext()) {
Map<String, Object> map = (Map) iterator.next();
beanList.add(BeanUtil.mapToBean(map, beanType, false));
}
return beanList;
}
}
/**
* @param inputStream 文件流 获取方法推荐使用 new ByteArrayInputStream(multipartFile.getBytes())
* @param sheet 读取的sheet 第一个sheet为 0
* @param headerRow 表头行 第一行是 1
* @return
*/
public static List<Map<String, Object>> readBySax(InputStream inputStream, int sheet, int headerRow) {
return readBySax(inputStream, sheet, null, headerRow, false);
}
/**
* @param inputStream
* @param sheet 读取的sheet 第一个sheet为 0
* @param headerRow 表头行 第一行是 1
* @param checkHeader
* @return
*/
public static List<Map<String, Object>> readBySax(InputStream inputStream, int sheet, int headerRow, boolean checkHeader) {
return readBySax(inputStream, sheet, null, headerRow, checkHeader);
}
/**
* @param inputStream 文件流 获取方法推荐使用 new ByteArrayInputStream(multipartFile.getBytes())
* @param sheet 读取的sheet 第一个sheet为 0
* @param aliasHeader 定义的表头转换map {"姓名":name,"年龄":age}
* @param headerRow 表头行 第一行是 1
* @return
*/
public static List<Map<String, Object>> readBySax(InputStream inputStream, int sheet, Map<String, String> aliasHeader, int headerRow, boolean checkHeader) {
long start = System.currentTimeMillis();
ExcelCSVReadUtils excelUtils = new ExcelCSVReadUtils(aliasHeader, headerRow, checkHeader);
ExcelUtil.readBySax(inputStream, sheet, excelUtils.createRowHandler());
System.out.println("read used ：" + (System.currentTimeMillis() - start) + " ms");
return excelUtils.getRows();
}
/**
* 读取csv文件
*
* @param inputStream 文件流 获取方法推荐使用 new ByteArrayInputStream(multipartFile.getBytes())
* @param headerRowIndex 表头行
* @param startRowIndex 数据开始行
* @return
*/
public static List<Map<String, Object>> readCsv(InputStream inputStream, int headerRowIndex, int startRowIndex) {
return readCsv(inputStream, null, headerRowIndex, startRowIndex, "utf-8");
}
/**
* 读取csv文件
*
* @param inputStream 文件流 获取方法推荐使用 new ByteArrayInputStream(multipartFile.getBytes())
* @param headerRowIndex 表头行
* @param startRowIndex 数据开始行
* @param charset 字符集
* @return
*/
public static List<Map<String, Object>> readCsv(InputStream inputStream, int headerRowIndex, int startRowIndex, String charset) {
return readCsv(inputStream, null, headerRowIndex, startRowIndex, charset);
}
/**
* 读取csv文件
*
* @param inputStream 文件流 获取方法推荐使用 new ByteArrayInputStream(multipartFile.getBytes())
* @param aliasHeader 转换后的表头
* @param headerRowIndex 表头行
* @param startRowIndex 数据开始行
* @param charset 字符集
* @return
*/
public static List<Map<String, Object>> readCsv(InputStream inputStream, Map<String, String> aliasHeader, int headerRowIndex, int startRowIndex, String charset) {
long start = System.currentTimeMillis();
List<Map<String, Object>> resList = new ArrayList<>();
CsvReader reader = CsvUtil.getReader();
//从文件中读取CSV数据
InputStreamReader is = null;
try {
is = new InputStreamReader(inputStream, charset);
} catch (UnsupportedEncodingException e) {
throw new RuntimeException("get inputStreamReader failed");
}
CsvData data = reader.read(is);
List<CsvRow> rows = data.getRows();
//空表格；
if (rows.isEmpty() || rows.size() - headerRowIndex == 0) {
return null;
}
//获取表头；
CsvRow headerRow = rows.get(headerRowIndex - 1);
//遍历行
for (int i = startRowIndex - 1; i < rows.size(); i++) {
CsvRow csvRow = rows.get(i);
//getRawList返回一个List列表，列表的每一项为CSV中的一个单元格（既逗号分隔部分）
List<String> rawList = csvRow.getRawList();
//跳过表头
if(i<=startRowIndex - 1)
{
continue;
}
if (aliasHeader == null) {
Map map = IterUtil.toMap(headerRow, (Iterable) rawList);
resList.add(map);
} else {
Map map = IterUtil.toMap(aliasHeader(headerRow, aliasHeader), (Iterable) rawList);
resList.add(map);
}
}
long end = System.currentTimeMillis();
System.out.println("read used ：" + (end - start) + " ms");
return resList;
}
/**
* 读取csv文件 返回期望的beanType
*
* @param inputStream 文件流 获取方法推荐使用 new ByteArrayInputStream(multipartFile.getBytes())
* @param headerRowIndex 表头行
* @param startRowIndex 数据开始行
* @param beanType javaBean 属性包含@excel注解
* @param <T>
* @return
*/
public static <T> List<T> readCsv(InputStream inputStream, int headerRowIndex, int startRowIndex, Class<T> beanType) {
return readCsv(inputStream, headerRowIndex, startRowIndex, beanType, "utf-8");
}
/**
* 读取csv文件 返回期望的beanType
*
* @param inputStream 文件流 获取方法推荐使用 new ByteArrayInputStream(multipartFile.getBytes())
* @param headerRowIndex 表头行
* @param startRowIndex 数据开始行
* @param beanType javaBean 属性包含@excel注解
* @param charset 字符集
* @param <T>
* @return
*/
public static <T> List<T> readCsv(InputStream inputStream, int headerRowIndex, int startRowIndex, Class<T> beanType, String charset) {
Map<String, String> aliasHeader = getFiledMap(beanType);
List<Map<String, Object>> mapList = readCsv(inputStream, aliasHeader, headerRowIndex, startRowIndex, charset);
if (Map.class.isAssignableFrom(beanType)) {
return (List<T>) mapList;
} else {
List<T> beanList = new ArrayList(mapList.size());
Iterator i$ = mapList.iterator();
while (i$.hasNext()) {
Map<String, Object> map = (Map) i$.next();
beanList.add(BeanUtil.mapToBean(map, beanType, false));
}
return beanList;
}
}
/**
* 校验表头是否是我们需要的格式
*
* @return
*/
public boolean checkHeaderHandler() {
boolean flag = true;
List<String> aliasHeaders = CollUtil.newArrayList(aliasHeader.keySet());
if (headers.size() == 0 || aliasHeaders == null) {
return false;
}
//判断长度；
if (aliasHeaders.size() > headers.size()) {
return false;
}
//比较每个元素是否相等
List<String> collect = aliasHeaders.parallelStream().filter(e -> headers.contains(e)).collect(Collectors.toList());
return collect.size() == aliasHeaders.size();
}
/**
* 表头转换 将excel/csv的表头中文转换成可以入库的表字段
*
* @param headerList
* @param headerAlias
* @return
*/
private static List<String> aliasHeader(List<String> headerList, Map<String, String> headerAlias) {
ArrayList<String> result = new ArrayList();
if (CollUtil.isEmpty(headerList)) {
return result;
} else {
String alias = null;
for (Iterator iterator = headerList.iterator(); iterator.hasNext(); result.add(alias)) {
Object headerObj = iterator.next();
if (null != headerObj) {
String header = headerObj.toString();
alias = (String) headerAlias.get(header);
if (null == alias) {
alias = header;
}
}
}
return result;
}
}
/**
* 根据excel注解将属性名和注解name值转成map
* el：{"姓名":name,"年龄":age}
*
* @param clazz
* @return
*/
public static Map<String, String> getFiledMap(Class<?> clazz) {
BeanDesc beanDesc = BeanUtil.getBeanDesc(clazz);
Collection<BeanDesc.PropDesc> props = beanDesc.getProps();
Map<String, String> filedMap = new HashMap<>(props.size());
for (BeanDesc.PropDesc p : props) {
Field field = beanDesc.getField(p.getFieldName());
Excel annotation = field.getAnnotation(Excel.class);
if (annotation != null) {
filedMap.put(annotation.name(), p.getFieldName());
}
}
return filedMap;
}
public List<Map<String, Object>> getRows() {
return rows;
}
}
public class MapUtil {
/**
* map转对象
* 成功
* {
* data:成功list
* size:成功条数
* code:true
* }
* or
* 失败
* {
* data:失败list警告
* size:失败条数
* code:false
* }
* @param maps 读取的对象
* @param clazz 需要转换的对象
* @param headerRow 表头行 第一行是 1
* @author suyuan
* @date 2021/5/28 14:16
*/
public static <T> Result map2Object(List<Map<String, Object>> maps, Class<T> clazz,int headerRow) throws Exception {
// DO: 2021/5/28 封装一个消息返回成功或者错误信息
Result result = new Result();
ArrayList<T> listsuccess = new ArrayList<T>();
ArrayList<String> listfail = new ArrayList<>();
Map<Object, Integer> m=new HashMap<>();
int i=headerRow;
for(Map<String, Object> map : maps){
i++;
T t = clazz.newInstance();
// DO: 2021/5/28 下划线转驼峰
Iterator<Map.Entry<String, Object>> entries = map.entrySet().iterator();
while (entries.hasNext()) {
Map.Entry<String, Object> entry = entries.next();
Object fieldValue = entry.getValue();
// TODO: 2021/6/4 字典转换转换的不对要不要提醒？
Field newField = ReflectUtil.getField(clazz, StrUtil.toCamelCase(entry.getKey()));
if (newField != null) {
newField.setAccessible(true);
if (newField.isAnnotationPresent(ExcelProperty.class)) {
Class<? extends Converter> converter1 = newField.getAnnotation(ExcelProperty.class).converter();
if(!converter1.equals(AutoConverter.class)){
Converter converter = newField.getAnnotation(ExcelProperty.class).converter().newInstance();
CellData cellData = new CellData( converter.convertToJavaData(new CellData(fieldValue) , null, null));
if (cellData.getData() != null) {
fieldValue = cellData.getData();
}
}
}
Class<?> fieldType = newField.getType();
if (fieldType.equals(String.class) && String.valueOf(fieldValue).contains(".0")) {
fieldValue = convertRate2Decimal(fieldValue);
}
Object convert = Convert.convert(fieldType, fieldValue);
newField.set(t, Convert.convert(fieldType, fieldValue));
if (convert == null) {
listfail.add("第" + i + "行的" + entry.getKey() + "列数据类型有问题，请自行检查");
} else {
//唯一性校验
if (newField.isAnnotationPresent(Unique.class)) {
Integer count = m.get(convert);
m.put(convert, (count == null) ? 1 : count + 1);
if (m.get(convert) != null && m.get(convert) > 1) {
listfail.add("第" + i + "行的" + entry.getKey() + "列数据值有重复:" + convert + "，请自行检查");
}
}
}
}
}
listsuccess.add(t);
}
if(listfail.size()!=0){
result.setData(listfail);
result.setSize(listfail.size());
result.setCode(false);
}
else {
result.setData(listsuccess);
result.setSize(listsuccess.size());
result.setCode(true);
}
return result;
}
public static String convertRate2Decimal(Object rate) {
if (rate == null) {
return null;
}
BigDecimal bd = Convert.toBigDecimal(rate);
if (bd == null) {
return Convert.toStr(rate);
}
return bd.stripTrailingZeros().toPlainString();
}
/**
* Object转List
* @author suyuan
* @date 2021/6/8 9:32
*/
public static <T> List<T> castList(Object obj, Class<T> clazz)
{
List<T> result = new ArrayList<T>();
if(obj instanceof List<?>)
{
for (Object o : (List<?>) obj)
{
result.add(clazz.cast(o));
}
return result;
}
return null;
}
}