Java POI读取Excel数据到实体类

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)]