1、引入依赖
在pom.xml文件中添加POI相关依赖：
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.14</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.14</version> </dependency>
注：此处使用的3.14版本，如果使用4以上版本可能会不兼容。
2、ExcelUtil工具类
1 package com.payb.hsp.bjproj.common.util; 2 3 import lombok.extern.slf4j.Slf4j; 4 import org.apache.commons.lang3.time.DateFormatUtils; 5 import org.apache.poi.hssf.usermodel.HSSFDateUtil; 6 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 7 import org.apache.poi.ss.usermodel.Cell; 8 import org.apache.poi.ss.usermodel.Row; 9 import org.apache.poi.ss.usermodel.Sheet; 10 import org.apache.poi.ss.usermodel.Workbook; 11 import org.apache.poi.xssf.usermodel.XSSFWorkbook; 12 import org.springframework.util.CollectionUtils; 13 import org.springframework.util.StringUtils; 14 15 import java.io.File; 16 import java.io.FileInputStream; 17 import java.io.IOException; 18 import java.io.InputStream; 19 import java.lang.reflect.Field; 20 import java.lang.reflect.Method; 21 import java.math.BigDecimal; 22 import java.math.BigInteger; 23 import java.util.ArrayList; 24 import java.util.Date; 25 import java.util.List; 26 import java.util.regex.Matcher; 27 import java.util.regex.Pattern; 28 29 @Slf4j 30 public class ExcelUtil { 31 private static final Pattern P = Pattern.compile(".0$"); 32 33 /** 34 * Excel表头对应Entity属性 解析封装javabean 35 * 36 * @param clazz 类 37 * @param filePath 文件路径 38 * @param excelHeads excel表头与entity属性对应关系 39 * @param <T> 40 * @return 41 * @throws Exception 42 */ 43 public static <T> List<T> readExcelToEntity(Class<T> clazz, String filePath, List<ExcelHead> excelHeads) { 44 try { 45 File file = new File("src/main/resources/" + filePath); 46 // 是否EXCEL文件 47 checkFile(file.getName()); 48 // 兼容新老版本 49 Workbook workbook = getWorkBoot(new FileInputStream(file), file.getName()); 50 // 解析Excel 51 return readExcel(clazz, workbook, excelHeads); 52 } catch (Exception e) { 53 log.error("读取Excel异常：{}", e); 54 return null; 55 } 56 } 57 58 /** 59 * 解析Excel转换为Entity 60 * 61 * @param clazz 类 62 * @param filePath 文件名 63 * @param <T> 64 * @return 65 * @throws Exception 66 */ 67 public static <T> List<T> readExcelToEntity(Class<T> clazz, String filePath) { 68 return readExcelToEntity(clazz, filePath, null); 69 } 70 71 /** 72 * 校验是否是Excel文件 73 * 74 * @param fileName 75 * @throws Exception 76 */ 77 public static void checkFile(String fileName) throws Exception { 78 if (!StringUtils.isEmpty(fileName) && !(fileName.endsWith(".xlsx") || fileName.endsWith(".xls"))) { 79 throw new Exception("不是Excel文件！"); 80 } 81 } 82 83 /** 84 * 兼容新老版Excel 85 * 86 * @param in 87 * @param fileName 88 * @return 89 * @throws IOException 90 */ 91 private static Workbook getWorkBoot(InputStream in, String fileName) throws IOException { 92 if (fileName.endsWith(".xlsx")) { 93 return new XSSFWorkbook(in); 94 } else { 95 return new HSSFWorkbook(in); 96 } 97 } 98 99 /** 100 * 解析Excel 101 * 102 * @param clazz 类 103 * @param workbook 工作簿对象 104 * @param excelHeads excel与entity对应关系实体 105 * @param <T> 106 * @return 107 * @throws Exception 108 */ 109 private static <T> List<T> readExcel(Class<T> clazz, Workbook workbook, List<ExcelHead> excelHeads) throws Exception { 110 List<T> beans = new ArrayList<T>(); 111 int sheetNum = workbook.getNumberOfSheets(); 112 for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++) { 113 Sheet sheet = workbook.getSheetAt(sheetIndex); 114 String sheetName = sheet.getSheetName(); 115 int firstRowNum = sheet.getFirstRowNum(); 116 int lastRowNum = sheet.getLastRowNum(); 117 Row head = sheet.getRow(firstRowNum); 118 if (head == null) { 119 continue; 120 } 121 short firstCellNum = head.getFirstCellNum(); 122 short lastCellNum = head.getLastCellNum(); 123 Field[] fields = clazz.getDeclaredFields(); 124 for (int rowIndex = firstRowNum + 1; rowIndex <= lastRowNum; rowIndex++) { 125 Row dataRow = sheet.getRow(rowIndex); 126 if (dataRow == null) { 127 continue; 128 } 129 T instance = clazz.newInstance(); 130 // 非头部映射方式，默认不校验是否为空，提高效率 131 if (CollectionUtils.isEmpty(excelHeads)) { 132 firstCellNum = dataRow.getFirstCellNum(); 133 lastCellNum = dataRow.getLastCellNum(); 134 } 135 for (int cellIndex = firstCellNum; cellIndex < lastCellNum; cellIndex++) { 136 Cell headCell = head.getCell(cellIndex); 137 if (headCell == null) { 138 continue; 139 } 140 Cell cell = dataRow.getCell(cellIndex); 141 headCell.setCellType(Cell.CELL_TYPE_STRING); 142 String headName = headCell.getStringCellValue().trim(); 143 if (StringUtils.isEmpty(headName)) { 144 continue; 145 } 146 // 下划线转驼峰 147 headName = StringUtil.lineToHump(headName); 148 ExcelHead eHead = null; 149 if (!CollectionUtils.isEmpty(excelHeads)) { 150 for (ExcelHead excelHead : excelHeads) { 151 if (headName.equals(excelHead.getExcelName())) { 152 eHead = excelHead; 153 headName = eHead.getEntityName(); 154 break; 155 } 156 } 157 } 158 for (Field field : fields) { 159 if (headName.equalsIgnoreCase(field.getName())) { 160 String methodName = MethodUtils.setMethodName(field.getName()); 161 Method method = clazz.getMethod(methodName, field.getType()); 162 if (isDateFied(field)) { 163 Date date = null; 164 if (cell != null) { 165 date = cell.getDateCellValue(); 166 } 167 if (date == null) { 168 volidateValueRequired(eHead, sheetName, rowIndex); 169 break; 170 } 171 method.invoke(instance, cell.getDateCellValue()); 172 } else { 173 String value = null; 174 if (cell != null) { 175 value = getCellStringValue(cell); 176 } 177 if (StringUtils.isEmpty(value)) { 178 volidateValueRequired(eHead, sheetName, rowIndex); 179 break; 180 } 181 method.invoke(instance, convertType(field.getType(), value.trim())); 182 } 183 break; 184 } 185 } 186 } 187 beans.add(instance); 188 } 189 } 190 return beans; 191 } 192 193 private static String getCellStringValue(Cell cell) { 194 if (cell == null) { 195 return ""; 196 } else { 197 if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { 198 if (HSSFDateUtil.isCellDateFormatted(cell)) { 199 Date date = cell.getDateCellValue(); 200 return DateFormatUtils.format(date, "yyyy-MM-dd"); 201 } else { 202 return getRealStringValueOfDouble(cell.getNumericCellValue()); 203 } 204 } 205 cell.setCellType(1); 206 return cell.getStringCellValue().trim(); 207 } 208 } 209 210 private static String getRealStringValueOfDouble(Double d) { 211 String doubleStr = d.toString(); 212 boolean b = doubleStr.contains("E"); 213 int indexOfPoint = doubleStr.indexOf('.'); 214 if (b) { 215 int indexOfE = doubleStr.indexOf('E'); 216 BigInteger xs = new BigInteger(doubleStr.substring(indexOfPoint 217 + BigInteger.ONE.intValue(), indexOfE)); 218 int pow = Integer.valueOf(doubleStr.substring(indexOfE 219 + BigInteger.ONE.intValue())); 220 int xsLen = xs.toByteArray().length; 221 int scale = xsLen - pow > 0 ? xsLen - pow : 0; 222 doubleStr = String.format("%." + scale + "f", d); 223 } else { 224 Matcher m = P.matcher(doubleStr); 225 if (m.find()) { 226 doubleStr = doubleStr.replace(".0", ""); 227 } 228 } 229 return doubleStr; 230 } 231 232 /** 233 * 是否日期字段 234 * 235 * @param field 236 * @return 237 */ 238 private static boolean isDateFied(Field field) { 239 return (Date.class == field.getType()); 240 } 241 242 /** 243 * 空值校验 244 * 245 * @param excelHead 246 * @throws Exception 247 */ 248 private static void volidateValueRequired(ExcelHead excelHead, String sheetName, int rowIndex) throws Exception { 249 if (excelHead != null && excelHead.isRequired()) { 250 throw new Exception("《" + sheetName + "》第" + (rowIndex + 1) + "行:\"" + excelHead.getExcelName() + "\"不能为空！"); 251 } 252 } 253 254 /** 255 * 类型转换 256 * 257 * @param clazz 258 * @param value 259 * @return 260 */ 261 private static Object convertType(Class clazz, String value) { 262 if (Integer.class == clazz || int.class == clazz) { 263 return Integer.valueOf(value); 264 } 265 if (Short.class == clazz || short.class == clazz) { 266 return Short.valueOf(value); 267 } 268 if (Byte.class == clazz || byte.class == clazz) { 269 return Byte.valueOf(value); 270 } 271 if (Character.class == clazz || char.class == clazz) { 272 return value.charAt(0); 273 } 274 if (Long.class == clazz || long.class == clazz) { 275 return Long.valueOf(value); 276 } 277 if (Float.class == clazz || float.class == clazz) { 278 return Float.valueOf(value); 279 } 280 if (Double.class == clazz || double.class == clazz) { 281 return Double.valueOf(value); 282 } 283 if (Boolean.class == clazz || boolean.class == clazz) { 284 return Boolean.valueOf(value.toLowerCase()); 285 } 286 if (BigDecimal.class == clazz) { 287 return new BigDecimal(value); 288 } 289 return value; 290 } 291 292 /** 293 * 获取properties的set和get方法 294 */ 295 static class MethodUtils { 296 private static final String SET_PREFIX = "set"; 297 private static final String GET_PREFIX = "get"; 298 299 private static String capitalize(String name) { 300 if (name == null || name.length() == 0) { 301 return name; 302 } 303 return name.substring(0, 1).toUpperCase() + name.substring(1); 304 } 305 306 public static String setMethodName(String propertyName) { 307 return SET_PREFIX + capitalize(propertyName); 308 } 309 310 public static String getMethodName(String propertyName) { 311 return GET_PREFIX + capitalize(propertyName); 312 } 313 } 314 }
3、测试工具类
假如现在有一个Excel内容如下：
| name | highRiskNum |
| 医疗机构 | 12 |
| 参保单位 | 23 |
| 参保人 | 34 |
| 零售药店 | 45 |
首先创建对应的实体类：
1 package test; 2 3 import lombok.Data; 4 5 @Data 6 public class PortraitDTO { 7 private String name; 8 private Integer highRiskNum; 9 }
调用ExcelUtil的方法：
List<PortraitDTO> portraitDTOList = ExcelUtil.readExcelToEntity(PortraitDTO.class, "D:\\画像实体.xlsx");输出结果：
[PortraitDTO(name=医疗机构, highRiskNum=12), PortraitDTO(name=参保单位, highRiskNum=23), PortraitDTO(name=参保人, highRiskNum=34), PortraitDTO(name=零售药店, highRiskNum=45)]