验证中...
ExcelUtilTest.java
Raw Copy
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.util.List;
import java.util.stream.Collectors;
import lombok.extern.slf4j.Slf4j;
@Slf4j
public class ExcelUtilTest {
public static void main(String[] args) throws FileNotFoundException {
List<ExcelRowBean> list = ExcelUtil.readExcel(new FileInputStream(new File("D:测试.xls")), ExcelRowBean.class);
log.debug("{}", list);
}
}
ExcelRowBean.java
Raw Copy
import java.util.Date;
import lombok.Data;
import lombok.experimental.Accessors;
@Accessors(chain = true)
@Data
public class ExcelRowBean {
@ExcelProperty("编号")
private Long id; // 编号
@ExcelProperty("用户名")
private String username; // 用户名
@ExcelProperty(index = 2)
private String password; // 密码
@ExcelProperty("创建时间", format = "M/d/yyyy HH:mm:ss")
private Date createTime; // 创建时间
@ExcelProperty("余额")
private Double balance; // 余额
}
ExcelUtil.java
Raw Copy
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
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.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import lombok.extern.slf4j.Slf4j;
/**
* Excel工具类.
*/
@Slf4j
public class ExcelUtil {
/** 列标题名称位置 */
private static final ThreadLocal<Map<String, Integer>> CELL_TITLE_INDEX = new ThreadLocal<>();
private ExcelUtil() {
throw new IllegalStateException("Utility class");
}
public static <T> List<T> readExcel(String excelFilePath, Class<T> clazz) {
return readExcel(new File(excelFilePath), clazz);
}
/**
* 读取Excel,转换为泛型集合
*
* @param <T> 泛型
* @param excelFile excel文件
* @param clazz 目标类
* @return
*/
public static <T> List<T> readExcel(File excelFile, Class<T> clazz) {
try (Workbook workbook = excelFile.getName().endsWith(".xlsx") ? new XSSFWorkbook(new FileInputStream(excelFile)) : new HSSFWorkbook(new FileInputStream(excelFile))) {
return getListBySheet(workbook.getSheetAt(0), clazz);
} catch (Exception e) {
log.error("读取excel文件异常!", e);
}
return Collections.emptyList();
}
/**
* 读取Excel,转换为泛型集合
*
* @param <T> 泛型
* @param inputStream excel输入流
* @param clazz 目标类
* @return
*/
public static <T> List<T> readExcel(InputStream inputStream, Class<T> clazz) {
try (Workbook workbook = WorkbookFactory.create(inputStream)) {
return getListBySheet(workbook.getSheetAt(0), clazz);
} catch (Exception e) {
log.error("读取excel文件异常!", e);
}
return Collections.emptyList();
}
/**
* 检查Excel映射对象字段配置
*
* @param <T> 泛型
* @param clazz
*/
private static <T> void checkExcelFieldConfig(Class<T> clazz) {
Field[] fields = clazz.getDeclaredFields();
for (Field field : fields) {
ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
if (excelProperty == null) {
log.warn("{}字段缺少ExcelProperty配置", field.getName());
}
}
}
private static <T> List<T> getListBySheet(Sheet sheet, Class<T> clazz) throws IllegalAccessException,
InvocationTargetException, ParseException, NoSuchMethodException, InstantiationException {
checkExcelFieldConfig(clazz);
int firstRowNum = sheet.getFirstRowNum() + 1; // XXX 从第2行开始读取
int lastRowNum = sheet.getLastRowNum();
List<T> list = new ArrayList<>(lastRowNum);
CELL_TITLE_INDEX.set(getCellTitleIndexMap(sheet.getRow(0))); // 获取行标题index
for (int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++) {
if (!ExcelUtil.isAllCellEmpty(sheet.getRow(rowNum))) {
list.add(getRowData(sheet.getRow(rowNum), clazz));
}
}
CELL_TITLE_INDEX.remove();
return list;
}
/**
* 获取列标题单元格index
*
* @param titleRow 列标题行
* @return
*/
private static Map<String, Integer> getCellTitleIndexMap(Row titleRow) {
int firstCellNumm = titleRow.getFirstCellNum();
int lastCellNum = titleRow.getLastCellNum();
Map<String, Integer> cellTitleIndexMap = new HashMap<>(lastCellNum);
for (int cellNum = firstCellNumm; cellNum < lastCellNum; cellNum++) {
if (titleRow.getCell(cellNum) != null) {
cellTitleIndexMap.put(titleRow.getCell(cellNum).getStringCellValue(), cellNum);
}
}
return cellTitleIndexMap;
}
/**
* 获取转换后的excel行数据
*
* @param <T> 泛型
* @param row excel行
* @param clazz 目标类
* @return
* @throws IllegalAccessException
* @throws InvocationTargetException
* @throws ParseException
* @throws NoSuchMethodException
* @throws InstantiationException
*/
private static <T> T getRowData(Row row, Class<T> clazz) throws IllegalAccessException, InvocationTargetException,
ParseException, NoSuchMethodException, InstantiationException {
T obj = clazz.newInstance();
Field[] fields = clazz.getDeclaredFields();
for (Field field : fields) {
ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
if (excelProperty != null) {
Integer cellIndex = !excelProperty.value().isEmpty() ? CELL_TITLE_INDEX.get().get(excelProperty.value()) : Integer.valueOf(excelProperty.index());
if (cellIndex != null) {
Cell cell = row.getCell(cellIndex);
String setMethodName = "set".concat(field.getName().substring(0, 1).toUpperCase().concat(field.getName().substring(1)));
Method method = clazz.getMethod(setMethodName, field.getType());
dealCellFieldMethodInvoke(cell, obj, field, method);
} else {
log.warn("“{}-{}”在excel表头中未找到!", field.getName(), excelProperty.value());
}
}
}
return obj;
}
/**
* Excel单元格转泛型对象
*
* @param <T> 泛型
* @param cell excel单元格
* @param obj 目标对象
* @param field 目标字段
* @param method 目标方法
* @throws IllegalAccessException
* @throws InvocationTargetException
* @throws ParseException
*/
private static <T> void dealCellFieldMethodInvoke(Cell cell, T obj, Field field, Method method)
throws IllegalAccessException, InvocationTargetException, ParseException {
String cellValue = getCellValue(cell);
if (cellValue == null) {
return;
}
if (field.getType().isAssignableFrom(String.class)) {
method.invoke(obj, cellValue);
} else if (field.getType().isAssignableFrom(Long.class)) {
method.invoke(obj, Long.parseLong(cellValue));
} else if (field.getType().isAssignableFrom(Integer.class)) {
method.invoke(obj, Integer.parseInt(cellValue));
} else if (field.getType().isAssignableFrom(java.util.Date.class)) {
method.invoke(obj, new SimpleDateFormat(field.getAnnotation(ExcelProperty.class).format()).parse(cellValue));
} else if (field.getType().isAssignableFrom(java.sql.Date.class)) {
method.invoke(obj, new SimpleDateFormat(field.getAnnotation(ExcelProperty.class).format()).parse(cellValue));
} else if (field.getType().isAssignableFrom(Double.class)) {
method.invoke(obj, Double.parseDouble(cellValue));
} else if (field.getType().isAssignableFrom(Float.class)) {
method.invoke(obj, Float.parseFloat(cellValue));
} else if (field.getType().isAssignableFrom(BigDecimal.class)) {
method.invoke(obj, new BigDecimal(cellValue));
} else if (field.getType().isAssignableFrom(Timestamp.class)) {
method.invoke(obj, new Timestamp(new SimpleDateFormat(field.getAnnotation(ExcelProperty.class).format()).parse(cellValue).getTime()));
} else if (field.getType().isAssignableFrom(LocalDateTime.class)) {
method.invoke(obj, LocalDateTime.parse(cellValue, DateTimeFormatter.ofPattern(field.getAnnotation(ExcelProperty.class).format())));
} else if (field.getType().isAssignableFrom(LocalDate.class)) {
method.invoke(obj, LocalDate.parse(cellValue, DateTimeFormatter.ofPattern(field.getAnnotation(ExcelProperty.class).format())));
} else if (field.getType().isAssignableFrom(LocalTime.class)) {
method.invoke(obj, LocalTime.parse(cellValue, DateTimeFormatter.ofPattern(field.getAnnotation(ExcelProperty.class).format())));
} else {
log.warn("不支持的数据类型:{},请在ExcalUtil#dealCellFieldMethodInvoke(Cell, T, Field, Method)增加相关类型支持", field.getType());
}
}
private static boolean isEmpty(String text) {
return text == null || text.trim().isEmpty();
}
/**
* 判断是否为空行
*
* @param row excel行
* @return
*/
public static boolean isAllCellEmpty(Row row) {
if (row == null) {
return true;
}
int count = 0;
// 单元格数量
int rowCount = row.getLastCellNum() - row.getFirstCellNum();
// 判断多少个单元格为空
for (int c = 0; c < rowCount; c++) {
Cell cell = row.getCell(c);
if (cell == null || getCellValue(cell) == null) {
count += 1;
}
}
return count == rowCount;
}
/**
* 获取单元格内容.
*
* @param cell Excel单元格
* @return
*/
public static String getCellValue(Cell cell) {
if (cell == null) {
return null;
}
cell.setCellType(CellType.STRING);
return isEmpty(cell.getStringCellValue()) ? null : cell.getStringCellValue();
}
}
ExcelProperty.java
Raw Copy
import java.lang.annotation.ElementType;
import java.lang.annotation.Inherited;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* Excel字段配置
*
* @see com.alibaba.excel.annotation.ExcelProperty
*/
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ExcelProperty {
/**
* excel列标题名称
*
* @return
*/
String value() default "";
/**
* 列标题位置(从0开始)
*
* @return
*/
int index() default 99999;
/**
* 列格式
*
* @return
*/
String format() default "";
}

Comment list( 0 )

Sign in for post a comment

Help Search