title: Excel导入改善 tags:
- easy-poi
- excel
- 导入
- 批量 categories: 日常记录 date: 2017-08-18 18:18:52
最近由于上线的需要，对于excel导入导出的效率提出了要求。
那么一方面我们对于db落库的业务尽量走批量,
其次对于导入数据的校验以及对应成实体也做出了改善。
- db落库走批量可以参考上述两篇日志[批量查询DB的实现][DB][redis限制请求频率及资源隔离][redis]
- excel对应成实体对象的简化在本次介绍（类似于传统jdbc到ORM框架的转换）
本次开发中选择了POI作为基础框架（可以类比为jdbc）easy-poi作为脚手架（类比为mybatis）
一个简单的导入如下（员工导入作为样例）
package com.air.tqb.model;
import com.air.tqb.po.PO;
import com.air.tqb.utils.ImportExcelGroup;
import org.jeecgframework.poi.excel.annotation.Excel;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Size;
import java.util.Date;
/**
* @Title: TmEmployee.java
* @Package com.air.tqb.model
* @Description: 员工PO
* @author Alex
* @date 2015年4月27日 上午9:56:30
*/
@SuppressWarnings("serial")
public class TmEmployee extends PO {
private static final long serialVersionUID = -5771140355588903785L;
private String pkId;//主键
@Size(max = 100)
@NotNull(groups = {ImportExcelGroup.class})
@Excel(name="员工姓名", isImportField = "true")
private String name;//名称
@Size(max = 20)
@NotNull(groups = {ImportExcelGroup.class})
@Excel(name="手机号码", isImportField = "true")
private String cellPhone;//手机
@NotNull(groups = {ImportExcelGroup.class})
@Excel(name="服务技师", isImportField = "true")
private Integer isRepair;//是否维修工,1代表是维修工,0代表不是维修工
@Excel(name="出生日期", isImportField = "true")
private String birthdate;//出生日期
@Excel(name="入职日期", isImportField = "true")
private String entrydate;//入职日期
@Excel(name="身份证号", isImportField = "true")
private String coCard;//身份证号
@Excel(name="家庭住址", isImportField = "true")
private String homeAddress;//家庭住址
@Excel(name="电子邮件", isImportField = "true")
private String email;//电子邮件
private Date creationtime;//创建时间
private Date modifiedtime;//修改时间
private String creator;//创建人
private String modifier;//修改人
private String idOwnOrg;//组织机构
private Integer isDel;//是否删除,1表示删除,0表示未删除
@NotNull(groups = {ImportExcelGroup.class})
@Excel(name="性别", isImportField = "true")
private Integer sex;//性别,1表示男,0表示女
@Excel(name="微信号", isImportField = "true")
private String wechart;//微信号
@Excel(name="基本工资", isImportField = "true")
private Double baseSalary;//基本工资
private String idWxbEmployee;//员工ID
private Double maxPointFavourable;//能使用积分抵扣消费的最大值（负数表示无限制）
private Double maxFavourable;//能使用的结清优惠最大值（负数表示无限制）
private Double maxGatheringFavourable;//能使用的收银优惠最大值（负数表示无限制）
public String getPkId() {
return pkId;
}
public void setPkId(String pkId) {
this.pkId = pkId;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getCellPhone() {
return cellPhone;
}
public void setCellPhone(String cellPhone) {
this.cellPhone = cellPhone;
}
public Integer getIsRepair() {
return isRepair;
}
public void setIsRepair(Integer isRepair) {
this.isRepair = isRepair;
}
public String getBirthdate() {
return birthdate;
}
public void setBirthdate(String birthdate) {
this.birthdate = birthdate;
}
public String getEntrydate() {
return entrydate;
}
public void setEntrydate(String entrydate) {
this.entrydate = entrydate;
}
public String getCoCard() {
return coCard;
}
public void setCoCard(String coCard) {
this.coCard = coCard;
}
public String getHomeAddress() {
return homeAddress;
}
public void setHomeAddress(String homeAddress) {
this.homeAddress = homeAddress;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getCreationtime() {
return creationtime;
}
public void setCreationtime(Date creationtime) {
this.creationtime = creationtime;
}
public Date getModifiedtime() {
return modifiedtime;
}
public void setModifiedtime(Date modifiedtime) {
this.modifiedtime = modifiedtime;
}
public String getCreator() {
return creator;
}
public void setCreator(String creator) {
this.creator = creator;
}
public String getModifier() {
return modifier;
}
public void setModifier(String modifier) {
this.modifier = modifier;
}
public String getIdOwnOrg() {
return idOwnOrg;
}
public void setIdOwnOrg(String idOwnOrg) {
this.idOwnOrg = idOwnOrg;
}
public Integer getIsDel() {
return isDel;
}
public void setIsDel(Integer isDel) {
this.isDel = isDel;
}
public Integer getSex() {
return sex;
}
public void setSex(Integer sex) {
this.sex = sex;
}
public String getWechart() {
return wechart;
}
public void setWechart(String wechart) {
this.wechart = wechart;
}
public Double getBaseSalary() {
return baseSalary;
}
public void setBaseSalary(Double baseSalary) {
this.baseSalary = baseSalary;
}
public String getIdWxbEmployee() {
return idWxbEmployee;
}
public void setIdWxbEmployee(String idWxbEmployee) {
this.idWxbEmployee = idWxbEmployee;
}
public Double getMaxPointFavourable() {
return maxPointFavourable;
}
public void setMaxPointFavourable(Double maxPointFavourable) {
this.maxPointFavourable = maxPointFavourable;
}
public Double getMaxFavourable() {
return maxFavourable;
}
public void setMaxFavourable(Double maxFavourable) {
this.maxFavourable = maxFavourable;
}
public Double getMaxGatheringFavourable() {
return maxGatheringFavourable;
}
public void setMaxGatheringFavourable(Double maxGatheringFavourable) {
this.maxGatheringFavourable = maxGatheringFavourable;
}
}
复制代码如上我们使用了一些注解
注意isImportField标记位导入字段
| name | String | null | 列名,支持name_id |
| needMerge | boolean | fasle | 纵向合并单元格 |
| orderNum | String | "0" | 列的排序,支持name_id |
| replace | String[] | {} | 值得替换 导出是{a_id,b_id} 导入反过来 |
| savePath | String | "upload" | 导入文件保存路径,如果是图片可以填写,默认是upload/className/ IconEntity这个类对应的就是upload/Icon/ |
| type | int | 1 | 导出类型 1 是文本 2 是图片,3 是函数,10 是数字 默认是文本 |
| width | double | 10 | 列宽 |
| height | double | 10 | 列高,后期打算统一使用@ExcelTarget的height,这个会被废弃,注意 |
| isStatistics | boolean | fasle | 自动统计数据,在追加一行统计,把所有数据都和输出 这个处理会吞没异常,请注意这一点 |
| isHyperlink | boolean | false | 超链接,如果是需要实现接口返回对象 |
| isImportField | boolean | true | 校验字段,看看这个字段是不是导入的Excel中有,如果没有说明是错误的Excel,读取失败,支持name_id |
| exportFormat | String | "" | 导出的时间格式,以这个是否为空来判断是否需要格式化日期 |
| importFormat | String | "" | 导入的时间格式,以这个是否为空来判断是否需要格式化日期 |
| format | String | "" | 时间格式,相当于同时设置了exportFormat 和 importFormat |
| databaseFormat | String | "yyyyMMddHHmmss" | 导出时间设置,如果字段是Date类型则不需要设置 数据库如果是string 类型,这个需要设置这个数据库格式,用以转换时间格式输出 |
| numFormat | String | "" | 数字格式化,参数是Pattern,使用的对象是DecimalFormat |
| imageType | int | 1 | 导出类型 1 从file读取 2 是从数据库中读取 默认是文件 同样导入也是一样的 |
| suffix | String | "" | 文字后缀,如% 90 变成90% |
| isWrap | boolean | true | 是否换行 即支持\n |
| mergeRely | int[] | {} | 合并单元格依赖关系,比如第二列合并是基于第一列 则{1}就可以了 |
| mergeVertical | boolean | fasle | 纵向合并内容相同的单元格 |
| 属性 | 类型 | 默认值 | 功能 |
|---|
其实这样看整体的导入就比较清晰了
一个导入的具体操作如下
@Override
@DataSource(DataSourceType.SLOW)
@OperationLimit
public ImportResultInfo<String> fastImportBatchStaff(InputStream inputStream) throws Exception {
ImportResultInfo<String> resultInfo = new ImportResultInfo<>();
ImportParams params = new ImportParams();
//params.setKeyIndex(null);
params.setDataHanlder(new TmEmployeeDataHandler());
List<TmEmployee> list = ExcelImportUtil.importExcel(inputStream, TmEmployee.class, params);
Map<TmEmployee,Integer> lineNumberMap = generateLineNumberMap(list);
Map errorMap = validateList(list,lineNumberMap);
int successCount = staffMapper.addStaffBatch(list);
resultInfo.setStatus("success");
resultInfo.setStr("成功导入" + successCount + "条数据</br>");
if (!errorMap.isEmpty()) {
resultInfo.setStr(resultInfo.getStr() + mapJoiner.join(errorMap));
}
return resultInfo;
}
复制代码从Excel映射到具体的对象List的方法如下一句
ImportParams params = new ImportParams();
//params.setKeyIndex(null);
params.setDataHanlder(new TmEmployeeDataHandler());
List<TmEmployee> list =ExcelImportUtil.importExcel(inputStream, TmEmployee.class, params);
复制代码DataHandler可以做一些粗浅的转换（类比于ResultHandler）
public class TmEmployeeDataHandler extends F6DataHandlerDefaultImpl<TmEmployee> {
private static final String 性别 = "性别";
private static final String 服务技师 = "服务技师";
private static final String 出生日期 = "出生日期";
private static final String 入职日期 = "入职日期";
private static final String[] HANDLE_NAMES = new String[]{性别, 服务技师, 出生日期, 入职日期};
public TmEmployeeDataHandler() {
super();
setNeedHandlerFields(HANDLE_NAMES);
}
@Override
public Object importHandler(TmEmployee obj, String name, Object value) {
String str = value != null ? value.toString().trim() : null;
switch (name) {
case 性别:
return "女".equals(value) ? 0: 1;
case 出生日期:
case 入职日期:
return CheckUtil.dateConvertString(parseDate(str),DEFAULT_DAY_FORMAT_PATTERN);
case 服务技师:
return "是".equals(value) ? 1 : 0;
default:
return super.importHandler(obj, name, value);
}
}
}
复制代码这样就可以完成具体的转换了
为了记录错误的行号，我做了如下的改造
@Autowired
private Validator validator;
protected Joiner.MapJoiner mapJoiner = Joiner.on("<br/>").withKeyValueSeparator("=");
protected Map<Integer,String> validateList(List<Object> list,Map<?,Integer> lineNumberMap) {
return validateList(list,lineNumberMap, ImportExcelGroup.class, Default.class);
}
protected Map<Integer, String> validateList(List<Object> list, Map<?, Integer> lineNumberMap, Class... group) {
Map<Integer, String> errorMap = Maps.newTreeMap();
int errorAllowSize = list.size() / 5;
for (int i = list.size() - 1; i >= 0; i--) {
Object object = list.get(i);
object = trimStringAttributeOfObject(object);
Set<ConstraintViolation<Object>> validate = validator.validate(object, group);
if (!validate.isEmpty()) {
StringBuilder stringBuilder = new StringBuilder(128);
for (ConstraintViolation<Object> va : validate) {
stringBuilder.append(va.getMessage()).append("-").append(va.getPropertyPath()).append("-").append(va.getInvalidValue()).append("|");
}
errorMap.put(lineNumberMap.get(object), stringBuilder.toString());
if (errorMap.size() >= errorAllowSize) {
throw new DataException("错误数已超过最多允许数！" + errorAllowSize + "<br/>" + mapJoiner.join(errorMap));
}
list.remove(i);
}
}
return errorMap;
}
protected Map<Object, Integer> generateLineNumberMap(List list) {
Map<Object,Integer> lineNumberMap = new IdentityHashMap<>(list.size());
int lineNumber = 2;
for (Object vo : list) {
lineNumberMap.put(vo, lineNumber++);
}
return lineNumberMap;
}
复制代码同时为了避免校验成其他组，定义了自己的接口方便validate
public interface ImportExcelGroup {
}
复制代码这样我们就可以按照组别定义我们的校验。而不会干扰到默认组（Default）
如上我们就可以将不符合我们格式的数据check出来
这样配合批量导入db就可以完成高效的导入了。
改完之后可以check一下需要时间
2017-08-16 17:15:27,726 [INFO] [fastImportBatchCustomerCar-9] c.a.t.s.b.i.CustomerCarServiceImpl:? validate List,874ms
2017-08-16 17:15:28,076 [INFO] [fastImportBatchCustomerCar-9] c.a.t.a.BatchAspect:? getUUidInBatch size:20462 cost：349ms
2017-08-16 17:15:28,470 [INFO] [fastImportBatchCustomerCar-9] c.a.t.a.BatchAspect:? getCustomerByCellPhone size:18315 cost：331ms
2017-08-16 17:15:29,190 [INFO] [fastImportBatchCustomerCar-9] c.a.t.a.BatchAspect:? getExistCarByCarNo size:20462 cost：719ms
2017-08-16 17:15:29,364 [INFO] [fastImportBatchCustomerCar-9] c.a.t.a.BatchAspect:? getExistCarByVin size:9872 cost：173ms
2017-08-16 17:15:29,404 [INFO] [fastImportBatchCustomerCar-9] c.a.t.s.b.i.CustomerCarServiceImpl:? removeLoginDuplicate List,2552ms
2017-08-16 17:15:29,721 [INFO] [fastImportBatchCustomerCar-9] c.a.t.a.BatchAspect:? getUUidInBatch size:18315 cost：267ms
2017-08-16 17:15:33,487 [INFO] [fastImportBatchCustomerCar-9] c.a.t.a.BatchAspect:? addCustomerInBatch size:18315 cost：2975ms
2017-08-16 17:15:41,173 [INFO] [fastImportBatchCustomerCar-9] c.a.t.a.BatchAspect:? addCarInBatch size:18315 cost：7686ms
2017-08-16 17:15:42,523 [INFO] [fastImportBatchCustomerCar-9] c.a.t.a.BatchAspect:? addCustomerCarInBatch size:18315 cost：1349ms
2017-08-16 17:15:46,397 [INFO] [fastImportBatchCustomerCar-9] c.a.t.a.BatchAspect:? addBasSearchInBatch size:18315 cost：3856ms
2017-08-16 17:15:46,420 [INFO] [fastImportBatchCustomerCar-9] c.a.t.a.BatchAspect:? getUUidInBatch size:2147 cost：22ms
2017-08-16 17:15:47,578 [INFO] [fastImportBatchCustomerCar-9] c.a.t.a.BatchAspect:? addCarInBatch size:2147 cost：1156ms
2017-08-16 17:15:47,765 [INFO] [fastImportBatchCustomerCar-9] c.a.t.a.BatchAspect:? addCustomerCarInBatch size:2147 cost：158ms
2017-08-16 17:15:48,016 [INFO] [fastImportBatchCustomerCar-9] c.a.t.a.BatchAspect:? addBasSearchInBatch size:2147 cost：249ms
2017-08-16 17:15:48,340 [INFO] [http-nio-8080-exec-9] c.a.t.c.DataInitController:? 花费: 29 秒<br/>成功导入20462条数据</br>
```
[DB]: https://my.oschina.net/qixiaobo025/blog/1501633
[redis]: https://my.oschina.net/qixiaobo025/blog/1503394
复制代码
2464
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
