一、序言
本人现在从事工作也有两年的时间了，excel的操作是每个开发的人员都会用到的技术；而不同的公司对于excel的实现方式都不同。而导入数据的规范又不是我们能掌握的。简单一点会对数据校验没有那么多的要求，但是复杂的就会对数据校验有很严格的校验，并且要求对错误的数据有十分友好的用户体验。这就对我们的开发提出了一定的难度。我毕业进的第一家第一个任务就是让我自己写一个excel导入、导出功能。并且要进行十分严格的数据校验和错误数据的反馈。对于当时还是菜鸟的我来说着实难受。进而下面几个问题自然成了我们必须克服的问题：
1、数据校验
2、错误数据如何返回
3、性能如何优化
后面在工作中也遇到了各种各样的excel相关的功能开发。慢慢的自己也总结了一种比较完善的解决方式；以excel错误数据导出+单元格标注描述错误原因来作为错误数据的解决方案。至于性能则采用的是多线程的方式。
二、效果展示
1 、导入模板图
活动信息导入模板.PNG
其中姓名、证件类型、证件号码是必填项！
2 、错误数据返回
活动信息导入错误信息.PNG.jpg
用户可以很清晰的知道自己输入的数据问题在哪。用户体验会好很多。
三、代码实现
1 、接收数据的实体类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class ActivityExcelVO implements Serializable {
private static final long serialVersionUID = 1171618220553691362L;
private Integer index;
private String name;
private String startTime ;
private String endTime ;
private String address;
private String venueName;
private String activityTopic;
private String activityTypeStr ;
private String organizer;
private String responsiblePersonName;
private String responsiblePersonMobile;
private String activityScale ;
private String remark ;
}
2、Excel工具类
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Map.Entry;
public class ExcelUtil {
static int sheetsize = 5000;
/**
* @author yucheng9
* @param data 导入到excel中的数据
* @param fields 需要注意的是这个方法中的map中：
* 每一列对应的实体类的英文名为键，excel表格中每一列名为值
*
*/
public static <T> Workbook List2Execl(List<T> data, Map<String, String> fields, Map<String,String> failCommentMap) throws Exception {
HSSFWorkbook workbook = new HSSFWorkbook();
// 如果导入数据为空，则抛出异常。
if (data == null || data.size() == 0) {
workbook.close();
throw new Exception("导入的数据为空");
}
// 根据data计算有多少页sheet
int pages = data.size() / sheetsize;
if (data.size() % sheetsize > 0) {
pages += 1;
}
// 提取表格的字段名（英文字段名是为了对照中文字段名的）
String[] egtitles = new String[fields.size()];
String[] cntitles = new String[fields.size()];
Iterator<String> it = fields.keySet().iterator();
int count = 0;
while (it.hasNext()) {
String cntitle = (String) it.next();
String egtitle = fields.get(cntitle);
egtitles[count] = egtitle;
cntitles[count] = cntitle;
count++;
}
// 添加数据
for (int i = 0; i < pages; i++) {
int rownum = 0;
// 计算每页的起始数据和结束数据
int startIndex = i * sheetsize;
int endIndex = (i + 1) * sheetsize - 1 > data.size() ? data.size()
: (i + 1) * sheetsize - 1;
// 创建每页，并创建第一行
HSSFSheet sheet = workbook.createSheet();
HSSFRow row = sheet.createRow(rownum);
// 在每页sheet的第一行中，添加字段名
for (int f = 0; f < cntitles.length; f++) {
HSSFCell cell = row.createCell(f);
cell.setCellValue(cntitles[f]);
}
rownum++;
// 将数据添加进表格
for (int j = startIndex; j < endIndex; j++) {
row = sheet.createRow(rownum);
T item = data.get(j);
// 首先获取序号
Field fdIndex = item.getClass().getDeclaredField("index");
fdIndex.setAccessible(true);
Object objIndex = fdIndex.get(item);
String index = objIndex == null ? "" : objIndex.toString();
for (int h = 0; h < cntitles.length; h++) {
String egtitle = egtitles[h];
String commentKey = index + ":" + egtitle;
Field fd = item.getClass().getDeclaredField(egtitle);
fd.setAccessible(true);
Object o = fd.get(item);
String value = o == null ? "" : o.toString();
HSSFCell cell = row.createCell(h);
// 判断备注项是否有
String failReason = failCommentMap.get(commentKey);
if (!StringUtils.isEmpty(failReason)){
// 设置标注
HSSFPatriarch p = sheet.createDrawingPatriarch();
//前四个参数是坐标点,后四个参数是编辑和显示批注时的大小.
HSSFComment comment=p.createComment(new HSSFClientAnchor(0,0,0,0,(short)3,3,(short)5,6));
//输入批注信息
comment.setString(new HSSFRichTextString(failReason));
//将批注添加到单元格对象中
cell.setCellComment(comment);
}
cell.setCellValue(value);
}
rownum++;
}
}
return workbook;
}
/**
* @title excel数据导入
* @param in ：文件输入流
* @param entityClass ：返回实体类
* @param fields ：字段名、字段描述映射表
*/
public static <T> List<T> excel2List(InputStream in, Class<T> entityClass,
Map<String, String> fields,String fileExtensionType) throws Exception {
Workbook workbook = null;
if (Objects.equals(fileExtensionType,".xls")) {
workbook = new HSSFWorkbook(in);
} else if (Objects.equals(fileExtensionType,".xlsx")){
workbook = new XSSFWorkbook(in);
} else {
throw new RuntimeException("excel文件格式错误！");
}
List<T> resultList = new ArrayList<T>();
// excel中字段的中英文名字数组
String[] egTitles = new String[fields.size()];
String[] cnTitles = new String[fields.size()];
Iterator<String> it = fields.keySet().iterator();
int count = 0;
while (it.hasNext()) {
String cntitle = (String) it.next();
String egtitle = fields.get(cntitle);
egTitles[count] = egtitle;
cnTitles[count] = cntitle;
count++;
}
// 得到excel中sheet总数
int sheetcount = workbook.getNumberOfSheets();
if (sheetcount == 0) {
workbook.close();
throw new Exception("Excel文件中没有任何数据");
}
// 数据的导出
for (int i = 0; i < sheetcount; i++) {
Sheet sheet = workbook.getSheetAt(i);
if (sheet == null) {
continue;
}
// 每页中的第一行为标题行，对标题行的特殊处理
Row firstRow = sheet.getRow(0);
int celllength = firstRow.getLastCellNum();
String[] excelFieldNames = new String[celllength];
LinkedHashMap<String, Integer> colMap = new LinkedHashMap<String, Integer>();
// 获取Excel中的列名
for (int f = 0; f < celllength; f++) {
Cell cell = firstRow.getCell(f);
excelFieldNames[f] = cell.getStringCellValue().trim();
// 将列名和列号放入Map中,这样通过列名就可以拿到列号
for (int g = 0; g < excelFieldNames.length; g++) {
colMap.put(excelFieldNames[g], g);
}
}
// 由于数组是根据长度创建的，所以值是空值，这里对列名map做了去空键的处理
colMap.remove(null);
// 判断需要的字段在Excel中是否都存在
// 需要注意的是这个方法中的map中：中文名为键，英文名为值
boolean isExist = true;
List<String> excelFieldList = Arrays.asList(excelFieldNames);
for (String cnName : fields.keySet()) {
if (!excelFieldList.contains(cnName)) {
isExist = false;
break;
}
}
// 如果有列名不存在，则抛出异常，提示错误
if (!isExist) {
workbook.close();
throw new Exception("Excel中缺少必要的字段，或字段名称有误");
}
// 将sheet转换为list
for (int j = 1; j <= sheet.getLastRowNum(); j++) {
Row row = sheet.getRow(j);
// 根据泛型创建实体类
T entity = entityClass.newInstance();
// 给对象中的字段赋值
for (Entry<String, String> entry : fields.entrySet()) {
// 获取中文字段名
String cnNormalName = entry.getKey();
// 获取英文字段名
String enNormalName = entry.getValue();
// 根据中文字段名获取列号
int col = colMap.get(cnNormalName);
// 获取当前单元格中的内容
Cell cell = row.getCell(col);
if (cell != null){
String content = "";
// String content = new BigDecimal(cell.toString()).toPlainString();
CellType cellType = cell.getCellType();
// 以下是判断数据的类型
if (Objects.equals(cellType,CellType.NUMERIC)){
DecimalFormat df = new DecimalFormat("0");
content = df.format(cell.getNumericCellValue());
} else if (Objects.equals(cellType,CellType.STRING)){
content = cell.getStringCellValue().trim();
} else if (Objects.equals(cellType,CellType.BOOLEAN)){
content = cell.getBooleanCellValue() + "";
} else if (Objects.equals(cellType,CellType.FORMULA)){
content = cell.getCellFormula() + "";
} else if (Objects.equals(cellType,CellType.BLANK)){
content = "";
} else if (Objects.equals(cellType,CellType._NONE)){
content = "";
} else if (Objects.equals(cellType,CellType.ERROR)){
content = "非法字符";
} else {
content = "未知类型";
}
// 给对象赋值
setFieldValueByName(enNormalName, content, entity);
}
}
resultList.add(entity);
}
}
workbook.close();
return resultList;
}
/**
* @Description : 根据字段名给对象的字段赋值
* @param fieldName 字段名
* @param fieldValue 字段值
* @param o 对象
*
*/
private static void setFieldValueByName(String fieldName, Object fieldValue, Object o) throws Exception {
Field field = getFieldByName(fieldName, o.getClass());
if (field != null) {
field.setAccessible(true);
// 获取字段类型
Class<?> fieldType = field.getType();
// 根据字段类型给字段赋值
if (String.class == fieldType) {
field.set(o, String.valueOf(fieldValue));
} else if ((Integer.TYPE == fieldType)
|| (Integer.class == fieldType)) {
// 去掉小数点
String stringValue = StringUtil.subZeroAndDot(fieldValue.toString());
field.set(o, Integer.parseInt(stringValue));
} else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) {
// 去掉小数点
String stringValue = StringUtil.subZeroAndDot(fieldValue.toString());
field.set(o, Long.valueOf(stringValue));
} else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) {
field.set(o, Float.valueOf(fieldValue.toString()));
} else if ((Short.TYPE == fieldType) || (Short.class == fieldType)) {
field.set(o, Short.valueOf(fieldValue.toString()));
} else if ((Double.TYPE == fieldType)
|| (Double.class == fieldType)) {
field.set(o, Double.valueOf(fieldValue.toString()));
} else if (Character.TYPE == fieldType) {
if ((fieldValue != null)
&& (fieldValue.toString().length() > 0)) {
field.set(o,
Character.valueOf(fieldValue.toString().charAt(0)));
}
} else if (Date.class == fieldType) {
field.set(o, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
.parse(fieldValue.toString()));
} else {
field.set(o, fieldValue);
}
} else {
throw new Exception(o.getClass().getSimpleName() + "类不存在字段名 " + fieldName);
}
}
/**
* @Description : 根据字段名获取字段
* @param fieldName 字段名
* @param clazz 包含该字段的类
*
*/
private static Field getFieldByName(String fieldName, Class<?> clazz) {
// 拿到本类的所有字段
Field[] selfFields = clazz.getDeclaredFields();
// 如果本类中存在该字段，则返回
for (Field field : selfFields) {
if (field.getName().equals(fieldName)) {
return field;
}
}
// 否则，查看父类中是否存在此字段，如果有则返回
Class<?> superClazz = clazz.getSuperclass();
if (superClazz != null && superClazz != Object.class) {
return getFieldByName(fieldName, superClazz);
}
// 如果本类和父类都没有，则返回空
return null;
}
}
3、应用实例
@PostMapping("/excelImport")
public ResultData excelImport(@RequestParam(value = "file",required = true) MultipartFile file, HttpServletResponse response) {
ExcelResult excelResult = new ExcelResult();
if (file.isEmpty()){
throw new RuntimeException("文件不能为空！");
}
try {
String fileName = file.getOriginalFilename();
String fileExtensionType = fileName.indexOf(".") != -1 ? fileName.substring(fileName.lastIndexOf("."), fileName.length()) : null;
Map<String, String> fieldsMap = new LinkedHashMap<>();
fieldsMap.put("序号", "index");
fieldsMap.put("活动名称", "name");
fieldsMap.put("开始时间（2020-04-18 08:30）", "startTime");
fieldsMap.put("结束时间（2020-04-19 18:00）", "endTime");
fieldsMap.put("活动详细地址", "address");
fieldsMap.put("活动场馆名称", "venueName");
fieldsMap.put("活动主题", "activityTopic");
fieldsMap.put("活动类型", "activityTypeStr");
fieldsMap.put("举办单位", "organizer");
fieldsMap.put("负责人姓名", "responsiblePersonName");
fieldsMap.put("负责人电话", "responsiblePersonMobile");
fieldsMap.put("活动规模", "activityScale");
fieldsMap.put("备注信息", "remark");
List<ActivityExcelVO> resultList = ExcelUtil.excel2List(file.getInputStream(), ActivityExcelVO.class, fieldsMap, fileExtensionType);
// 解析以后进行数据校验：符合要求的作为记录插入到数据库，不符合要求的作为错误数据输出到页面
List<ActivityExcelVO> successList = new ArrayList<>();
List<ActivityExcelVO> failList = new ArrayList<>();
Map<String,String> failCommentMap = new HashMap<>();
if (!CollectionUtils.isEmpty(resultList)){
for (ActivityExcelVO activityExcelVO : resultList) {
StringBuffer sb = new StringBuffer("");
String activityName = activityExcelVO.getName();
String venueName = activityExcelVO.getVenueName();
if (StringUtils.isEmpty(activityName)){
sb.append("活动名称不能为空！");
failCommentMap.put(activityExcelVO.getIndex() + ":" + "name" ,"活动名称不能为空!");
}
if (StringUtils.isEmpty(venueName)){
sb.append("场馆名称不能为空！");
failCommentMap.put(activityExcelVO.getIndex() + ":" + "venueName" ,"场馆名称不能为空!");
}
if (StringUtils.isEmpty(sb.toString())){
successList.add(activityExcelVO);
} else {
failList.add(activityExcelVO);
}
}
}
excelResult.setFailCount(failList.size());
excelResult.setSuccessCount(successList.size());
// 处理成功的记录
if (!CollectionUtils.isEmpty(successList)){
String url = SHSCALPER_SERVICE_IP + SHSCALPER_SERVICE_CONTEXT_PATH + "/v1/syscfg/activity/importActivity";
ResponseEntity<String> responseResult = RestTemplateUtils.post(url, successList,String.class);
if (responseResult != null && Objects.equals(responseResult.getStatusCodeValue(), HttpStatus.SC_OK)){
excelResult.setStatus(1);
}
}
// 处理失败的记录
if (!CollectionUtils.isEmpty(failList)){
// 将错误数据以excel的格式导出
Workbook workbook = ExcelUtil.List2Execl(failList,fieldsMap,failCommentMap);
OutputStream outputStream = response.getOutputStream();
response.reset();
response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes(),"iso-8859-1") + ".xls");
response.setContentType("application/msexcel;charset=utf-8");
workbook.write(outputStream);
outputStream.close();
}
} catch (IOException e) {
logger.error(e.getMessage(), e);
} catch (Exception e) {
logger.error(e.getMessage(), e);
}
return new ResultData(CommonCodeEnum.SUCCESS.getCode(),CommonCodeEnum.SUCCESS.getMsg(),excelResult);
}
4、总结
这样就能很好的解决excel导入的问题了。
