<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>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.7</version>
</dependency>
package com.spring.utils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
@Slf4j
public class ExcelUtil {
private static final Pattern P = Pattern.compile(".0$");
/**
* Excel表头对应Entity属性 解析封装javabean
*
* @param clazz 类
* @param filePath 文件路径
* @param excelHeads excel表头与entity属性对应关系
* @param <T>
* @return
* @throws Exception
*/
public static <T> List<T> readExcelToEntity(Class<T> clazz, String filePath, List<ExcelHead> excelHeads) {
try {
File file = new File(filePath);
// 是否EXCEL文件
checkFile(file.getName());
// 兼容新老版本
Workbook workbook = getWorkBoot(new FileInputStream(file), file.getName());
// 解析Excel
return readExcel(clazz, workbook, excelHeads);
} catch (Exception e) {
log.error("读取Excel异常：{}", e);
return null;
}
}
/**
* 解析Excel转换为Entity
*
* @param clazz 类
* @param filePath 文件名
* @param <T>
* @return
* @throws Exception
*/
public static <T> List<T> readExcelToEntity(Class<T> clazz, String filePath) {
return readExcelToEntity(clazz, filePath, null);
}
/**
* 校验是否是Excel文件
*
* @param fileName
* @throws Exception
*/
public static void checkFile(String fileName) throws Exception {
if (!StringUtils.isEmpty(fileName) && !(fileName.endsWith(".xlsx") || fileName.endsWith(".xls"))) {
throw new Exception("不是Excel文件！");
}
}
/**
* 兼容新老版Excel
*
* @param in
* @param fileName
* @return
* @throws IOException
*/
private static Workbook getWorkBoot(InputStream in, String fileName) throws IOException {
if (fileName.endsWith(".xlsx")) {
return new XSSFWorkbook(in);
} else {
return new HSSFWorkbook(in);
}
}
/**
* 解析Excel
*
* @param clazz 类
* @param workbook 工作簿对象
* @param excelHeads excel与entity对应关系实体
* @param <T>
* @return
* @throws Exception
*/
private static <T> List<T> readExcel(Class<T> clazz, Workbook workbook, List<ExcelHead> excelHeads) throws Exception {
List<T> beans = new ArrayList<T>();
int sheetNum = workbook.getNumberOfSheets();
for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++) {
Sheet sheet = workbook.getSheetAt(sheetIndex);
String sheetName = sheet.getSheetName();
int firstRowNum = sheet.getFirstRowNum();
int lastRowNum = sheet.getLastRowNum();
Row head = sheet.getRow(firstRowNum);
if (head == null) {
continue;
}
short firstCellNum = head.getFirstCellNum();
short lastCellNum = head.getLastCellNum();
Field[] fields = clazz.getDeclaredFields();
for (int rowIndex = firstRowNum + 1; rowIndex <= lastRowNum; rowIndex++) {
Row dataRow = sheet.getRow(rowIndex);
if (dataRow == null) {
continue;
}
T instance = clazz.newInstance();
// 非头部映射方式，默认不校验是否为空，提高效率
if (CollectionUtils.isEmpty(excelHeads)) {
firstCellNum = dataRow.getFirstCellNum();
lastCellNum = dataRow.getLastCellNum();
}
for (int cellIndex = firstCellNum; cellIndex < lastCellNum; cellIndex++) {
Cell headCell = head.getCell(cellIndex);
if (headCell == null) {
continue;
}
Cell cell = dataRow.getCell(cellIndex);
headCell.setCellType(Cell.CELL_TYPE_STRING);
String headName = headCell.getStringCellValue().trim();
if (StringUtils.isEmpty(headName)) {
continue;
}
// 下划线转驼峰
headName = StringUtil.lineToHump(headName);
ExcelHead eHead = null;
if (!CollectionUtils.isEmpty(excelHeads)) {
for (ExcelHead excelHead : excelHeads) {
if (headName.equals(excelHead.getExcelName())) {
eHead = excelHead;
headName = eHead.getEntityName();
break;
}
}
}
for (Field field : fields) {
if (headName.equalsIgnoreCase(field.getName())) {
String methodName = MethodUtils.setMethodName(field.getName());
Method method = clazz.getMethod(methodName, field.getType());
if (isDateFied(field)) {
Date date = null;
if (cell != null) {
date = cell.getDateCellValue();
}
if (date == null) {
volidateValueRequired(eHead, sheetName, rowIndex);
break;
}
method.invoke(instance, cell.getDateCellValue());
} else {
String value = null;
if (cell != null) {
value = getCellStringValue(cell);
}
if (StringUtils.isEmpty(value)) {
volidateValueRequired(eHead, sheetName, rowIndex);
break;
}
method.invoke(instance, convertType(field.getType(), value.trim()));
}
break;
}
}
}
beans.add(instance);
}
}
return beans;
}
private static String getCellStringValue(Cell cell) {
if (cell == null) {
return "";
} else {
if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
return DateFormatUtils.format(date, "yyyy-MM-dd");
} else {
return getRealStringValueOfDouble(cell.getNumericCellValue());
}
}
cell.setCellType(1);
return cell.getStringCellValue().trim();
}
}
private static String getRealStringValueOfDouble(Double d) {
String doubleStr = d.toString();
boolean b = doubleStr.contains("E");
int indexOfPoint = doubleStr.indexOf('.');
if (b) {
int indexOfE = doubleStr.indexOf('E');
BigInteger xs = new BigInteger(doubleStr.substring(indexOfPoint
+ BigInteger.ONE.intValue(), indexOfE));
int pow = Integer.valueOf(doubleStr.substring(indexOfE
+ BigInteger.ONE.intValue()));
int xsLen = xs.toByteArray().length;
int scale = xsLen - pow > 0 ? xsLen - pow : 0;
doubleStr = String.format("%." + scale + "f", d);
} else {
Matcher m = P.matcher(doubleStr);
if (m.find()) {
doubleStr = doubleStr.replace(".0", "");
}
}
return doubleStr;
}
/**
* 是否日期字段
*
* @param field
* @return
*/
private static boolean isDateFied(Field field) {
return (Date.class == field.getType());
}
/**
* 空值校验
*
* @param excelHead
* @throws Exception
*/
private static void volidateValueRequired(ExcelHead excelHead, String sheetName, int rowIndex) throws Exception {
if (excelHead != null && excelHead.isRequired()) {
throw new Exception("《" + sheetName + "》第" + (rowIndex + 1) + "行:\"" + excelHead.getExcelName() + "\"不能为空！");
}
}
/**
* 类型转换
*
* @param clazz
* @param value
* @return
*/
private static Object convertType(Class clazz, String value) {
if (Integer.class == clazz || int.class == clazz) {
return Integer.valueOf(value);
}
if (Short.class == clazz || short.class == clazz) {
return Short.valueOf(value);
}
if (Byte.class == clazz || byte.class == clazz) {
return Byte.valueOf(value);
}
if (Character.class == clazz || char.class == clazz) {
return value.charAt(0);
}
if (Long.class == clazz || long.class == clazz) {
return Long.valueOf(value);
}
if (Float.class == clazz || float.class == clazz) {
return Float.valueOf(value);
}
if (Double.class == clazz || double.class == clazz) {
return Double.valueOf(value);
}
if (Boolean.class == clazz || boolean.class == clazz) {
return Boolean.valueOf(value.toLowerCase());
}
if (BigDecimal.class == clazz) {
return new BigDecimal(value);
}
return value;
}
/**
* 获取properties的set和get方法
*/
static class MethodUtils {
private static final String SET_PREFIX = "set";
private static final String GET_PREFIX = "get";
private static String capitalize(String name) {
if (name == null || name.length() == 0) {
return name;
}
return name.substring(0, 1).toUpperCase() + name.substring(1);
}
public static String setMethodName(String propertyName) {
return SET_PREFIX + capitalize(propertyName);
}
public static String getMethodName(String propertyName) {
return GET_PREFIX + capitalize(propertyName);
}
}
}
package com.spring.utils;
import lombok.Data;
@Data
public class ExcelHead {
/**
* Excel名
*/
private String excelName;
/**
* 实体类属性名
*/
private String entityName;
/**
* 值必填
*/
private boolean required=false;
}
List<PortraitDTO> portraitDTOList = ExcelUtil.readExcelToEntity(Bean.class, "D:\\excel.xlsx");
浙公网安备 33010602011771号