八云家的橙猫猫·a year ago
累了，不解释，直接上代码
依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
annotation
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelColumn {
String value();
}
pojo
@Data
@NoArgsConstructor
public class YJZGradeExcelBusinessPojo {
@ExcelColumn("申请单位（必填）")
private String deptName;
@ExcelColumn("系统名称（必填）")
private String businessName;
@ExcelColumn("统一社会信用代码")
private String socialCreditCode;
@ExcelColumn("行政区划")
private String region;
}
Service
public interface ExcelService {
/**
* 将上传的Excel第一个窗口转化为java对象
* @param file 上传的文件对象
* @param mClass 转换后的java对象类型
* @return PO集合
* @param <T> 转换后的java对象类型
*/
<T> List<T> readFirstSheetExcel(MultipartFile file, Class<T> mClass);
/**
* 读取Excel为Java对象
* @param file 上传的文件对象
* @return Excel的java对象
*/
Workbook openExcel(MultipartFile file);
/**
* 将第sheetIndex个窗口的数据，读取为java集合
* 如果行数据为空，默认忽略
* @see this#readExcelWork(Workbook, int, Class, boolean)
* @param workbook Excel的java对象
* @param sheetIndex 窗口序号，从0开始
* @param mClass 读取后的java对象类型
* @return java集合
* @param <T> java对象类型
*/
<T> List<T> readExcelWork(Workbook workbook, int sheetIndex, Class<T> mClass);
/**
* 将第sheetIndex个窗口的数据，读取为java集合
* @param workbook Excel的java对象
* @param sheetIndex 窗口序号，从0开始
* @param mClass 读取后的java对象类型
* @param ignoreEmptyData 是否忽略空数据
* @return java集合
* @param <T> java对象类型
*/
<T> List<T> readExcelWork(Workbook workbook, int sheetIndex, Class<T> mClass, boolean ignoreEmptyData);
}
serviceImpl
@Slf4j
@Service
public class ExcelServiceImpl implements ExcelService {
@Override
public <T> List<T> readFirstSheetExcel(MultipartFile file, Class<T> mClass) {
Workbook sheets = openExcel(file);
return readExcelWork(sheets, 0, mClass);
}
@Override
public Workbook openExcel(MultipartFile file) {
String originalFilename = file.getOriginalFilename();
// excel 表不能通过文件头获取文件类型
String typeName = FileUtil.extName(originalFilename);
Predicate<String> isExcel = str -> "xls".equalsIgnoreCase(typeName)
// xlsx的文件头是zip格式
|| "xlsx".equalsIgnoreCase(typeName);
if (!isExcel.test(typeName)){
log.error("文件不是excel, typeName={}", typeName);
throw new BussJsonException(ErrorEnum.YJZ_FILE_NOT_EXCEL, typeName);
}
log.info("读取文件:{}", originalFilename);
try (InputStream in = file.getInputStream()){
Workbook workbook = null;
try {
// 根据后名创建2003或2007的实例
if ("xls".equalsIgnoreCase(typeName)) {
log.info("create excel 2003");
workbook = new HSSFWorkbook(in);
} else if ("xlsx".equalsIgnoreCase(typeName)) {
log.info("create excel 2007");
workbook = new XSSFWorkbook(in);
} else {
log.error("Excel既不是2003也不是2007");
throw new BussJsonException(ErrorEnum.YJZ_FILE_NOT_EXCEL, typeName);
}
return workbook;
} catch (Exception e) {
log.error("解析Excel数据失败", e);
throw new BussJsonException(ErrorEnum.YJZ_READ_EXCEL_FAILED, originalFilename);
} finally {
IOUtils.closeQuietly(workbook);
}
} catch (IOException e) {
log.error("读取Excel文件失败", e);
throw new BussJsonException(ErrorEnum.YJZ_READ_EXCEL_FAILED, originalFilename);
}
}
/**
* 将Excel对象转换成java集合
* @param workbook 2003和2007的ExcelWork接口类
* @param sheetIndex 窗口序号，从0开始
* @param mClass 转换后的java对象类型
* @return PO集合
* @param <T> 转换后的java对象类型
*/
@Override
public <T> List<T> readExcelWork(Workbook workbook, int sheetIndex, Class<T> mClass) {
return readExcelWork(workbook, sheetIndex, mClass, true);
}
@Override
public <T> List<T> readExcelWork(Workbook workbook, int sheetIndex, Class<T> mClass, boolean ignoreEmptyData) {
int numberOfSheets = workbook.getNumberOfSheets();
sheetIndex = Math.max(0, Math.min(numberOfSheets-1, sheetIndex));
log.info("read sheet={}", sheetIndex);
List<T> list = new ArrayList<>();
// 取第一个窗口
Sheet sheet = workbook.getSheetAt(sheetIndex);
Field[] declaredFields = mClass.getDeclaredFields();
Method[] methods = mClass.getMethods();
// 从Excel第一行中读取属性字段，并和java对象中的注解进行匹配
Field[] fields = sortFieldByHeader(sheet.getRow(0), declaredFields);
int rCount = sheet.getLastRowNum() + 1;
for (int i = 1; i < rCount; i++) {
T t = row2Bean(mClass, sheet, methods, fields, i, ignoreEmptyData);
if (Objects.nonNull(t)){
list.add(t);
}
}
return list;
}
/**
* 根据表头排序 bean 对象属性
* 按Excel表头对齐对象属性列表
* @param row Excel行对象
* @param fields java对象属性
* @return 排序后的属性列表
*/
private Field[] sortFieldByHeader(Row row, Field[] fields) {
short cellLength = row.getLastCellNum();
// 创建和excel表头等长的field列表
Field[] result = new Field[Math.max(cellLength, fields.length)];
Cell cell;
for (int i = 0; i < cellLength; i++) {
cell = row.getCell(i);
String value = getCellValue(cell);
if (ObjectUtil.isNullOrEmpty(value)) {
// 不设置表头，不进入匹配循环
continue;
}
// 循环对象的属性
for (Field field : fields) {
boolean hasAnnotation = field.isAnnotationPresent(ExcelColumn.class);
if (hasAnnotation) {
ExcelColumn column = field.getDeclaredAnnotation(ExcelColumn.class);
if (value.equals(column.value())) {
// 对象注解与表头一致，有序放置在结果集中
result[i] = field;
}
}
}
}
return result;
}
/**
* 读取单元格的内容
* @param cell 单元格对象
* @return 单元格的值
*/
private String getCellValue(Cell cell) {
if (cell == null) {
return null;
}
switch (cell.getCellType()) {
case NUMERIC:
DecimalFormat df = new DecimalFormat("0");
return df.format(cell.getNumericCellValue());
case STRING:
return cell.getStringCellValue();
case FORMULA:
return String.valueOf(cell.getCellFormula());
case BOOLEAN:
return String.valueOf(cell.getBooleanCellValue());
case BLANK:
return "";
case ERROR:
return String.valueOf(cell.getErrorCellValue());
case _NONE:
return null;
default:
return null;
}
}
/**
* 将行数据转换成 java bean 对象
*
* @param mClass bean对象类型
* @param sheet Excel sheet 对象
* @param methods bean对象方法列表[set方法]
* @param fields 序列化的 bean 对象属性列表
* @param i 行号
* @param <T> bean对象类型
* @param ignoreEmptyData 是否忽略空数据，true: 遇到空数据时返回null
* @return java Bean对象
*/
private <T> T row2Bean(Class<T> mClass, Sheet sheet, Method[] methods, Field[] fields, int i, boolean ignoreEmptyData) {
Cell cell;
Row row = sheet.getRow(i);
T t;
try {
t = mClass.newInstance();
} catch (Exception e) {
log.error("create class {} failed .", mClass.getSimpleName());
return null;
}
int cCount = Math.min(row.getLastCellNum(), fields.length);
// 按Excel表头的顺序，给对象属性赋值
for (int j = 0; j < cCount; j++) {
cell = row.getCell(j);
String value = getCellValue(cell);
if (ObjectUtil.isNullOrEmpty(value)) {
continue;
}
if (!ObjectUtil.isNullOrEmpty(fields[j])) {
// 对象属性赋值，位置为j
objSetMethod(t, fields[j], methods, value);
ignoreEmptyData = false;
}
}
if (ignoreEmptyData){
t = null;
}
return t;
}
/**
* 调用bean对象的set方法
*
* @param obj bean对象
* @param declaredField 属性
* @param methods 方法
*/
private void objSetMethod(Object obj, Field declaredField, Method[] methods, Object value) {
for (Method method : methods) {
if ("set".concat(declaredField.getName())
.equalsIgnoreCase(method.getName())) {
try {
method.invoke(obj, value);
} catch (Exception e) {
log.error("{}, {}", method.getName(), e.getMessage());
}
}
}
}
}
Drag and drop your files here
Loading comments...
Source