反射泛型导入excel

最新推荐文章于 2022-08-08 15:18:48 发布

原创最新推荐文章于 2022-08-08 15:18:48 发布 · 340 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#导入excel

该方法用于从Excel文件中获取数据并将其封装到指定的实体类中，利用反射解析实体类的set方法，同时支持字段校验和错误记录。通过对不同类型的单元格进行处理，将数据转换为字符串并调用实体类的setter方法进行赋值。

/**
* 导入
* @throws IllegalAccessException
* @throws IOException
*/
public void excelImport() throws IllegalAccessException, IOException{
Department d = (Department) getSession().getAttribute("login_dep");
// 定义返回代码
String html = "<!DOCTYPE html><head><meta charset='utf-8' /></head><body><script>alert('数据上传完毕，总共${count}条数据，其中成功${success}条，失败${error}条。');location.href='qyjbxx_getList.do';</script></body></html>";
// 定义数据列
List<String> ywColumns = Arrays.asList("qyfl","qymc","shxydm","fddbrxm","fddbrsfz","jycs","qylx","sshy",
"zczb","jyfw","ybjyxm","slrq","hzrq","employeeCity");
// 定义错误接受数组 key=行号 value=实体类
List<Entry<Integer, String>> errorLog = new ArrayList<Map.Entry<Integer,String>>();
// 读取Excel内容并封装到实体类，key=行号 value=实体类
List<Entry<Integer, Object>> entryList =
ExcelUtils.importDataByExcel(excelFile, Qyjbxx.class, ywColumns, new IExcelHandle() {
@Override
public Object handle(String name, Object object) throws Exception {
String value = (String)object;
if ("qyfl".equals(name)){
if ("先证后核类".equals(value)){
return "0";
}else if("暂时保留类".equals(value)){
return "1";
}else if("暂停行使类".equals(value)){
return "2";
}else if("取消类".equals(value)){
return "3";
}else if("其他".equals(value)){
return "4";
}else{
throw new Exception("企业分类错误！只能是先证后核类、暂时保留类、暂停行使类其中之一！");
}
}else if("shxydm".equals(name)){
Qyjbxx qyjbxx = commonService.getQyjbxxByShxydm(value, null);
if (qyjbxx != null){
throw new Exception("该社会信用代码在数据库中已存在！");
}
return value;
}else if("slrq".equals(name)){
if (StringUtils.isBlank(value)){
return null;
}else{
Date date = ActionUtils.string2Date(value, "yyyy-MM-dd");
if (date == null){
throw new Exception("设立日期格式化失败！时间格式应该是yyyy-MM-dd形式！");
}
return date;
}
}else if("hzrq".equals(name)){
if (StringUtils.isBlank(value)){
return null;
}else{
Date date = ActionUtils.string2Date(value, "yyyy-MM-dd");
if (date == null){
throw new Exception("核准日期格式化失败！时间格式应该是yyyy-MM-dd形式！");
}
return date;
}
}
return StringUtils.isBlank(value)?null:object.toString();
}
} , errorLog);
int count = errorLog.size() + entryList.size();
// 处理实体类里的ID等字段，并检测社会信用代码是否有重复，重复则删除数据
Set<String> shxydmSet = new HashSet<String>();
for (int i=entryList.size()-1;i>=0;i--){
Entry<Integer, Object> entry = entryList.get(i);
Qyjbxx qyjbxx = (Qyjbxx) entry.getValue();
if (shxydmSet.contains(qyjbxx.getShxydm())){
entryList.remove(i);
errorLog.add(new MyEntry<Integer, String>(i+1, "社会信用代码重复！"));
continue;
}
shxydmSet.add(qyjbxx.getShxydm());
qyjbxx.setId(UuidGenerator.generate32UUID());
qyjbxx.setLrsj(new Date());
qyjbxx.setLrjg(d.getDepartmentGuid());
}
// 保存实体类、日志到数据库，并返回成功数量
int success = commonService.saveEntryList(entryList, errorLog, "t_credit_qyjbxx", "企业基本信息", (Employee) getSession().getAttribute("publisher"));
int error = count - success;
ActionUtils.writeHtml(getResponse(), html.replace("${count}", count+"").replace("${success}", success+"").replace("${error}", error+""));

}

/**
*
* 获取Excel数据内容并封装到对应实体类
* @param excelFile Excel文件
* @param clazz 实体类的Class，通过类似ExcelUtils.class的方式可以获得
* @param ywColumns 英文字段名称
* @param handle Excel字段校验接口，用于校验字段合法性，不合法则不封装到实体类并输出错误记录
* @param errLog 错误日志，key=错误行号（从1开始，和Excel显示完全一致），value=错误原因（由handle决定，如果为null则不会出现错误数据）
* @return 返回封装好的实体类列表
* @throws IllegalAccessException
*/
public static List<Entry<Integer, Object>> importDataByExcel(File excelFile, Class clazz, List<String> ywColumns, IExcelHandle handle, List<Entry<Integer, String>> errLog) throws IllegalAccessException{
List<Entry<Integer, Object>> list = new ArrayList<Map.Entry<Integer,Object>>();
if (excelFile == null || !excelFile.exists()){
return list;
}
// 解析字段的set方法
Map<String, Method> setMethodMap = new HashMap<String, Method>();
for(String column : ywColumns){
try{
PropertyDescriptor pd = new PropertyDescriptor(column, clazz);
Method method = pd.getWriteMethod();
if (method == null){
throw new IllegalAccessException(column + "字段无法于实体类映射，请检查参数！");
}
setMethodMap.put(column, method);
}catch(Exception e){
throw new IllegalAccessException("实体类解析出现异常[" + e.getMessage() + "]，请检查参数！");
}
}
// 读取Excel，格式化并创建实体类，如果整行为空自动报错
try{
Workbook wb = null;
try{
wb = new HSSFWorkbook(new FileInputStream(excelFile));
} catch(Exception e){}
if (wb == null){
wb = new XSSFWorkbook(new FileInputStream(excelFile));
// wb = new SXSSFWorkbook(); //海量数据处理
}
Sheet sheet = wb.getSheetAt(0);
int count = sheet.getLastRowNum()+1;
for (int i=1;i<count;i++){
// 读取行数据并构造实体类
try{
Row row = sheet.getRow(i);
Object o = clazz.newInstance();
StringBuffer blankTest = new StringBuffer();
for (int j=0;j<ywColumns.size();j++){
Object value = readCellString(row.getCell(j));
if (handle != null){
value = handle.handle(ywColumns.get(j), value);
}
blankTest.append(value==null?"":value.toString());
setMethodMap.get(ywColumns.get(j)).invoke(o, value);
}
if (blankTest.length() == 0){
throw new Exception("空行");
}
list.add(new MyEntry<Integer, Object>(i+1, o));
}catch(SQLException e){
e.printStackTrace();
errLog.add(new MyEntry<Integer, String>(i+1, e.getMessage() + ";ErrorCode:" + e.getErrorCode()));
}catch(Exception e){
e.printStackTrace();
errLog.add(new MyEntry<Integer, String>(i+1, e.getMessage()));
}
}
}catch(Exception e){
e.printStackTrace();
}
return list;
}

private static String readCellString(Cell cell){
if (cell == null){
return "";
}
String ret;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BLANK:
ret = "";
break;
case Cell.CELL_TYPE_BOOLEAN:
ret = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR:
ret = null;
break;
case Cell.CELL_TYPE_FORMULA:
Workbook wb = cell.getSheet().getWorkbook();
CreationHelper crateHelper = wb.getCreationHelper();
FormulaEvaluator evaluator = crateHelper.createFormulaEvaluator();
ret = readCellString(evaluator.evaluateInCell(cell));
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
Date theDate = cell.getDateCellValue();
ret = ActionUtils.date2String(theDate, "yyyy-MM-dd");
} else {
ret = NumberToTextConverter.toText(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_STRING:
ret = cell.getRichStringCellValue().getString();
break;
default:
ret = null;
}
return ret==null?"":ret.trim(); //有必要自行trim
}

反射 泛型导入excel

反射泛型导入excel