Excel导入
第一步、第二步工具类相互引用
一、创建...UploadUtil数据识别工具类
import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.Row; import org.springframework.util.StringUtils; import tv.zhongchi.common.util.ObjFieldIsNullUtil; import tv.zhongchi.common.util.ExcelImportUtil; import java.text.DecimalFormat; import java.util.ArrayList; import java.util.Arrays; import java.util.List; import java.util.stream.Collectors; import java.util.stream.Stream; /** * @ClassName CustomerInfoUploadUtil * @Author ZhangRF * @CreateDate 2021/01/18 * @Decription 客户信息导入工具类 */ public class ...UploadUtil { public static <T> List<T> returnObjectList(List<Row> rowList, boolean showInfo, Class<T> clazz, Row sheetRow) { List<T> objectList = null; T obj; String attribute; String value; Boolean flag = false; int physicalNumberOfCells = sheetRow.getPhysicalNumberOfCells(); try { objectList = new ArrayList<>(); for (int i = 0; i < rowList.size(); i++) { Row row = rowList.get(i); ExcelImportUtil.out("第" + (i + 1) + "行：", showInfo, false); obj = clazz.newInstance(); String remark = "";//备注拼接 String doctorUserName = "";//医生拼接 String consultUserName = "";//咨询师保留一个 for (int j = 0; j < physicalNumberOfCells; j++) { //表头 String curCell = sheetRow.getCell(j).toString(); Cell cell = row.getCell(j); if (StringUtils.isEmpty(cell)) { continue; } if (StringUtils.isEmpty(curCell)) { continue; } else if (curCell.equals("病历号")) { attribute = "customerNo"; //病历号强转string if (!StringUtils.isEmpty(cell) && cell.getCellTypeEnum() == CellType.NUMERIC) { value = String.valueOf((int) cell.getNumericCellValue()); } else { value = ExcelImportUtil.getCellValue(row.getCell(j)); } //没有档案号不录入 if (value.contains("*")) { flag = true; } } else if (curCell.equals("姓名")) { attribute = "customerName"; value = ExcelImportUtil.getCellValue(row.getCell(j)); } else if (curCell.equals("昵称")) { attribute = "customerNameInitial"; value = ExcelImportUtil.getCellValue(row.getCell(j)); } else if (curCell.equals("性别")) { attribute = "sex"; value = ExcelImportUtil.getCellValue(row.getCell(j)); if (value.equals("男")) { value = "1"; } else if (value.equals("女")) { value = "2"; } else { value = "0"; } } else if (curCell.equals("生日")) { attribute = "birthday"; value = ExcelImportUtil.getCellValue(row.getCell(j)); if (!StringUtils.isEmpty(value)) { value = value.substring(0, 10); } } else if (curCell.equals("手机号")) { attribute = "customerMobile"; if (!StringUtils.isEmpty(cell) && cell.getCellTypeEnum() == CellType.NUMERIC) { DecimalFormat format = new DecimalFormat("#"); double numericCellValue = cell.getNumericCellValue(); value = format.format(numericCellValue); } else { value = ExcelImportUtil.getCellValue(row.getCell(j)); } } else if (curCell.equals("电话")) { attribute = "phone"; if (!StringUtils.isEmpty(cell) && cell.getCellTypeEnum() == CellType.NUMERIC) { DecimalFormat format = new DecimalFormat("#"); double numericCellValue = cell.getNumericCellValue(); value = format.format(numericCellValue); } else { value = ExcelImportUtil.getCellValue(row.getCell(j)); } value = value.replace(" ", ""); String[] split = value.split("："); if (split.length < 2) { split = value.split(":"); if (split.length < 2) { value = ""; } else { remark = remark + value + "；"; } } else { remark = remark + value + "；"; } } else if (curCell.equals("地址")) { attribute = "address"; value = ExcelImportUtil.getCellValue(row.getCell(j)); } else if (curCell.equals("患者一级来源")) { attribute = "oneAcceptsChannels"; value = ExcelImportUtil.getCellValue(row.getCell(j)); } else if (curCell.equals("患者二级来源")) { attribute = "twoAcceptsChannels"; value = ExcelImportUtil.getCellValue(row.getCell(j)); } else if (curCell.equals("患者三级来源")) { attribute = "threeAcceptsChannels"; value = ExcelImportUtil.getCellValue(row.getCell(j)); } else if (curCell.equals("备注")) { //备注拼接 attribute = "remark"; value = ExcelImportUtil.getCellValue(row.getCell(j)); if (StringUtils.isEmpty(value)) { value = remark; } else { value = remark + value + ";"; if (StringUtils.isEmpty(remark)) { remark = value; } else { remark = remark + value + ";"; } } value = remark; } else if (curCell.equals("初诊医生")) { attribute = "doctorUserName"; value = ExcelImportUtil.getCellValue(row.getCell(j)); if (!StringUtils.isEmpty(value)) { doctorUserName = doctorUserName + value + ";"; String[] split = doctorUserName.split(";"); Stream<String> stream = Arrays.stream(split).distinct(); //将Stream转化为List集合 List<String> collect = stream.collect(Collectors.toList()); doctorUserName = ""; for (String doctor : collect) { doctorUserName = doctorUserName + doctor + ";"; } } value = doctorUserName; } else if (curCell.equals("责任医生")) { attribute = "doctorUserName"; value = ExcelImportUtil.getCellValue(row.getCell(j)); if (!StringUtils.isEmpty(value)) { doctorUserName = doctorUserName + value + ";"; String[] split = doctorUserName.split(";"); Stream<String> stream = Arrays.stream(split).distinct(); //将Stream转化为List集合 List<String> collect = stream.collect(Collectors.toList()); doctorUserName = ""; for (String doctor : collect) { doctorUserName = doctorUserName + doctor + ";"; } } value = doctorUserName; } else if (curCell.equals("咨询师")) { attribute = "consultUserName"; if (StringUtils.isEmpty(consultUserName)) { value = ExcelImportUtil.getCellValue(row.getCell(j)); consultUserName = value; } else { value = consultUserName; } } else if (curCell.equals("网电咨询师")) { attribute = "consultUserName"; if (StringUtils.isEmpty(consultUserName)) { value = ExcelImportUtil.getCellValue(row.getCell(j)); consultUserName = value; } else { value = consultUserName; } } else if (curCell.equals("创建时间")) { attribute = "gmtCreate"; value = ExcelImportUtil.getCellValue(row.getCell(j)); } else if (curCell.equals("创建人")) { attribute = "createUser"; value = ExcelImportUtil.getCellValue(row.getCell(j)); } else if (curCell.equals("患者标签")) { attribute = "remark"; value = ExcelImportUtil.getCellValue(row.getCell(j)); if (StringUtils.isEmpty(value)) { value = remark; } else { if (StringUtils.isEmpty(remark)) { remark = value; } else { remark = remark + value + ";"; } } value = remark; } else { continue; } ExcelImportUtil.setAttributeValue(obj, attribute, value); if (!value.equals("")) { ExcelImportUtil.out(value + " | ", showInfo, false); } } if (flag) { //continue掉不需要的当条数据 flag = false; continue; } if (ObjFieldIsNullUtil.isAllNull(obj)){ continue; } ExcelImportUtil.out("", showInfo); objectList.add(obj); } } catch (Exception e) { e.printStackTrace(); } return objectList; }
二、创建导入解析工具类
import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.util.IOUtils; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.mock.web.MockMultipartFile; import org.springframework.web.multipart.MultipartFile; import tv.zhongchi.common.util.upload.dto.eky.*; import tv.zhongchi.common.util.upload.dto.yygj.YYGJPayProjectUploadDTO; import tv.zhongchi.common.util.upload.eky.*; import tv.zhongchi.common.util.upload.yygj.YYGJPayProjectUploadUtil; import java.io.*; import java.lang.reflect.Field; import java.lang.reflect.InvocationTargetException; import java.lang.reflect.Method; import java.math.BigDecimal; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import java.util.regex.Matcher; import java.util.regex.Pattern; /** * @ClassName ExcelImportUtil * @Author ZhangRF * @CreateDate 2020/6/12 * @Decription Excel导入工具 */ public class ExcelImportUtil { /** * 07版Excel后缀 */ public static final String EXCEL_07 = "xlsx"; /** * 03版Excel后缀 */ public static final String EXCEL_03 = "xls"; /** * 正则表达式 用于匹配属性的第一个字母 */ private static final String REGEX = "[a-zA-Z]"; /** * 获取上传Excel列表 * * @param file 上传文件 * @param clazz class * @return 对应列表 */ public static <T> List<T> getImportExcelList(MultipartFile file, Class<T> clazz) throws IOException { //文件后缀 String suffix = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1); List<T> importList = new ArrayList<>(); //07版本的excel if (EXCEL_07.equalsIgnoreCase(suffix)) { importList = import07Excel(file.getInputStream(), 1, 0, clazz); } else if (EXCEL_03.equalsIgnoreCase(suffix)) { importList = import03Excel(file.getInputStream(), 1, 0, clazz); } return importList; } /** * Excel数据导入到数据库 * * @param is 数据流 * @param startRow 从第几行开始 * @param endRow 到第几行结束 (0表示所有行;正数表示到第几行结束;负数表示到倒数第几行结束) * @param clazz 要返回的对象集合的类型 * @return 对应List * @throws IOException */ public static <T> List<T> import03Excel(InputStream is, int startRow, int endRow, Class<T> clazz) throws IOException { //是否打印提示信息 boolean showInfo = true; return doImportExcel(is, startRow, endRow, showInfo, clazz); } /** * Excel数据导入到数据库 * * @param is 数据流 * @param startRow 从第几行开始 * @param endRow 到第几行结束 (0表示所有行;正数表示到第几行结束;负数表示到倒数第几行结束) * @param clazz 要返回的对象集合的类型 * @return 对应List * @throws IOException */ public static <T> List<T> import07Excel(InputStream is, int startRow, int endRow, Class<T> clazz) throws IOException { //是否打印提示信息 boolean showInfo = true; return doImport07Excel(is, startRow, endRow, showInfo, clazz); } /** * 功能:真正实现导入 */ private static <T> List<T> doImport07Excel(InputStream is, int startRow, int endRow, boolean showInfo, Class<T> clazz) throws IOException { // 去读Excel XSSFWorkbook workbook = new XSSFWorkbook(is); XSSFSheet sheet = workbook.getSheetAt(0); Row sheetRow = sheet.getRow(0); List<Row> rowList = getRowData(startRow, endRow, showInfo, sheet); if (clazz.equals(....class)) { //返回信息的数据集 return ...UploadUtil.returnObjectList(rowList, showInfo, clazz, sheetRow); } return returnObjectList(rowList, clazz); } /** * 功能:真正实现导入 */ private static <T> List<T> doImportExcel(InputStream is, int startRow, int endRow, boolean showInfo, Class< T> clazz) throws IOException { // 去读Excel HSSFWorkbook wb = new HSSFWorkbook(is); Sheet sheet = wb.getSheetAt(0); Row sheetRow = sheet.getRow(0); List<Row> rowList = getRowData(startRow, endRow, showInfo, sheet); if (clazz.equals(.....class)) { //返回信息的数据集 return ...UploadUtil.returnObjectList(rowList, showInfo, clazz, sheetRow); } return returnObjectList(rowList, clazz); } private static List<Row> getRowData(int startRow, int endRow, boolean showInfo, Sheet sheet) { List<Row> rowList = new ArrayList<>(); // 获取最后行号 int lastRowNum = sheet.getLastRowNum(); // 如果>0，表示有数据 if (lastRowNum > 0) { out("\n开始读取名为【" + sheet.getSheetName() + "】的内容：", showInfo); } // 循环读取 for (int i = startRow; i <= lastRowNum + endRow; i++) { Row row = sheet.getRow(i); if (row != null) { rowList.add(row); // out("第" + (i + 1) + "行：", showInfo, false); // // 获取每一单元格的值 // for (int j = 0; j < row.getLastCellNum(); j++) { // String value = getCellValue(row.getCell(j)); // if (!value.equals("")) { // out(value + " | ", showInfo, false); // } // } // out("", showInfo); } } return rowList; } /** * 功能:返回指定的对象集合 */ private static <T> List<T> returnObjectList(List<Row> rowList, Class<T> clazz) { List<T> objectList = null; T obj; String attribute; String value; int j; try { objectList = new ArrayList<>(); Field[] declaredFields = clazz.getDeclaredFields(); for (Row row : rowList) { j = 0; obj = clazz.newInstance(); for (Field field : declaredFields) { attribute = field.getName(); value = getCellValue(row.getCell(j)); setAttributeValue(obj, attribute, value); j++; } objectList.add(obj); } } catch (Exception e) { e.printStackTrace(); } return objectList; } /** * 功能:获取单元格的值 */ public static String getCellValue(Cell cell) { Object result = ""; if (cell != null) { switch (cell.getCellTypeEnum()) { case STRING: result = cell.getStringCellValue(); break; case NUMERIC: short format = cell.getCellStyle().getDataFormat(); if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) { SimpleDateFormat sdf = null; //System.out.println("cell.getCellStyle().getDataFormat()="+cell.getCellStyle().getDataFormat()); if (format == 20 || format == 32) { sdf = new SimpleDateFormat("HH:mm"); } else if (format == 14 || format == 31 || format == 57 || format == 58) { // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58) sdf = new SimpleDateFormat("yyyy-MM-dd"); double value = cell.getNumericCellValue(); Date date = org.apache.poi.ss.usermodel.DateUtil .getJavaDate(value); result = sdf.format(date); } else {// 日期 sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); } try { result = sdf.format(cell.getDateCellValue());// 日期 } catch (Exception e) { try { throw new Exception("exception on get date data !".concat(e.toString())); } catch (Exception e1) { e1.printStackTrace(); } } finally { sdf = null; } } else { result = BigDecimal.valueOf(cell.getNumericCellValue());// 数值 这种用BigDecimal包装再获取plainString，可以防止获取到科学计数值 } // result = cell.getNumericCellValue(); break; case BOOLEAN: result = cell.getBooleanCellValue(); break; case FORMULA: result = cell.getStringCellValue(); break; case ERROR: result = cell.getErrorCellValue(); break; case BLANK: break; default: break; } } return result.toString(); } /** * 功能:给指定对象的指定属性赋值 */ public static void setAttributeValue(Object obj, String attribute, String value) { //得到该属性的set方法名 String method_name = convertToMethodName(attribute, obj.getClass(), true); Method[] methods = obj.getClass().getMethods(); for (Method method : methods) { /** * 因为这里只是调用bean中属性的set方法，属性名称不能重复 * 所以set方法也不会重复，所以就直接用方法名称去锁定一个方法 * （注：在java中，锁定一个方法的条件是方法名及参数） */ if (method.getName().equals(method_name)) { Class<?>[] parameterC = method.getParameterTypes(); try { /**如果是(整型,浮点型,布尔型,字节型,时间类型), * 按照各自的规则把value值转换成各自的类型 * 否则一律按类型强制转换(比如:String类型) */ if (parameterC[0] == int.class || parameterC[0] == Integer.class) { if (value.lastIndexOf(".") != -1) value = value.substring(0, value.lastIndexOf(".")); method.invoke(obj, Integer.valueOf(value)); break; } else if (parameterC[0] == float.class || parameterC[0] == Float.class) { method.invoke(obj, Float.valueOf(value)); break; } else if (parameterC[0] == double.class || parameterC[0] == Double.class) { method.invoke(obj, Double.valueOf(value)); break; } else if (parameterC[0] == byte.class || parameterC[0] == Byte.class) { method.invoke(obj, Byte.valueOf(value)); break; } else if (parameterC[0] == BigDecimal.class || parameterC[0] == BigDecimal.class) { method.invoke(obj, new BigDecimal(value)); break; } else if (parameterC[0] == boolean.class || parameterC[0] == Boolean.class) { method.invoke(obj, Boolean.valueOf(value)); break; } else if (parameterC[0] == char.class || parameterC[0] == Character.class) { if (value != null && value != "") { method.invoke(obj, value.charAt(0)); break; } } else if (parameterC[0] == Date.class) { Date date = DateUtil.formatUnknownString2Date(value); method.invoke(obj, DateUtil.formatUnknownString2Date(value)); break; } else { if (value != null && value != "") { // if(value.lastIndexOf(".")!=-1) // value = value.substring(0, value.lastIndexOf(".")); method.invoke(obj, parameterC[0].cast(value)); break; } } } catch (IllegalArgumentException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } catch (SecurityException e) { e.printStackTrace(); } } } } /** * 功能:根据属性生成对应的set/get方法 */ private static String convertToMethodName(String attribute, Class<?> objClass, boolean isSet) { /** 通过正则表达式来匹配第一个字符 **/ Pattern p = Pattern.compile(REGEX); Matcher m = p.matcher(attribute); StringBuilder sb = new StringBuilder(); /** 如果是set方法名称 **/ if (isSet) { sb.append("set"); } else { /** get方法名称 **/ try { Field attributeField = objClass.getDeclaredField(attribute); /** 如果类型为boolean **/ if (attributeField.getType() == boolean.class || attributeField.getType() == Boolean.class) { sb.append("is"); } else { sb.append("get"); } } catch (SecurityException e) { e.printStackTrace(); } catch (NoSuchFieldException e) { e.printStackTrace(); } } /** 针对以下划线开头的属性 **/ if (attribute.charAt(0) != '_' && m.find()) { sb.append(m.replaceFirst(m.group().toUpperCase())); } else { sb.append(attribute); } return sb.toString(); } /** * 功能:输出提示信息(普通信息打印) */ public static void out(String info, boolean showInfo) { if (showInfo) { System.out.print(info + (showInfo ? "\n" : "")); } } /** * 功能:输出提示信息(同一行的不同单元格信息打印) */ public static void out(String info, boolean showInfo, boolean nextLine) { if (showInfo) { if (nextLine) { System.out.print(info + (showInfo ? "\n" : "")); } else { System.out.print(info); } } }
