java springboot poi 从controller 接收不同类型excel 文件处理

作者：爱睡懒觉的派大星时间：2023-10-17 14:26:05　

根据poi接收controller层的excel文件导入

可使用后缀名xls或xlsx格式的excel。

1.pom引入

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>

2.ExcelImportUtil 工具类创建

import com.guard.biz.common.util.excel.ExcelIn;
import org.apache.commons.beanutils.BeanUtilsBean;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.reflect.FieldUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.ss.usermodel.WorkbookFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @author Wei
* @time 2019/10/29
* @Description excel 导入工具类
*/
public class ExcelImportUtil<T> {
private static final Logger log = LoggerFactory.getLogger(ExcelImportUtil.class);
private static BeanUtilsBean beanUtilsBean = new BeanUtilsBean();
static {
beanUtilsBean.getConvertUtils().register(new org.apache.commons.beanutils.converters.DateConverter(null), java.util.Date.class);
}
/**
* 表头名字和对应所在第几列的下标，用于根据title取到对应的值
*/
private final Map<String, Integer> title_to_index = new HashMap<>();
/**
* 所有带有ExcelIn注解的字段
*/
private final List<Field> fields = new ArrayList<>();
/**
* 统计表格的行和列数量用来遍历表格
*/
private int firstCellNum = 0;
private int lastCellNum = 0;
private int firstRowNum = 0;
private int lastRowNum = 0;
private String sheetName;
private Sheet sheet;
public List<T> read(InputStream in, Class clazz) throws Exception {
gatherAnnotationFields(clazz);
configSheet(in);
configHeader();
List rList = null;
try {
rList = readContent(clazz);
} catch (IllegalAccessException e) {
throw new Exception(e);
} catch (InstantiationException e) {
throw new Exception(e);
} catch (InvocationTargetException e) {
throw new Exception(e);
}
return rList;
}
private List readContent(Class clazz) throws IllegalAccessException, InstantiationException, InvocationTargetException {
Object o = null;
Row row = null;
List<Object> rsList = new ArrayList<>();
Object value = null;
for (int i = (firstRowNum + 1); i <= lastRowNum; i++) {
o = clazz.newInstance();
row = sheet.getRow(i);
Cell cell = null;
for (Field field : fields) {
//根据注解中的title，取到表格中该列所对应的的值
Integer column = title_to_index.get(field.getAnnotation(ExcelIn.class).title());
if (column == null) {
continue;
}
cell = row.getCell(column);
value = getCellValue(cell);
if (null != value && StringUtils.isNotBlank(value.toString())) {
beanUtilsBean.setProperty(o, field.getName(), value);
}
}
rsList.add(o);
}
return rsList;
}
private void configSheet(InputStream in) throws Exception {
// 根据文件类型来分别创建合适的Workbook对象
try (Workbook wb = WorkbookFactory.create(in)) {
getSheetByName(wb);
} catch (FileNotFoundException e) {
throw new Exception(e);
} catch (IOException e) {
throw new Exception(e);
}
}
/**
* 根据sheet获取对应的行列值，和表头对应的列值映射
*/
private void configHeader() {
this.firstRowNum = sheet.getFirstRowNum();
this.lastRowNum = sheet.getLastRowNum();
//第一行为表头，拿到表头对应的列值
Row row = sheet.getRow(firstRowNum);
this.firstCellNum = row.getFirstCellNum();
this.lastCellNum = row.getLastCellNum();
for (int i = firstCellNum; i < lastCellNum; i++) {
title_to_index.put(row.getCell(i).getStringCellValue(), i);
}
}
/**
* 根据sheet名称获取sheet
*
* @param workbook
* @return
* @throws Exception
*/
private void getSheetByName(Workbook workbook) throws Exception {
int sheetNumber = workbook.getNumberOfSheets();
for (int i = 0; i < sheetNumber; i++) {
String name = workbook.getSheetName(i);
if (StringUtils.equals(this.sheetName, name)) {
this.sheet = workbook.getSheetAt(i);
return;
}
}
throw new Exception("excel中未找到名称为" + this.sheetName + "的sheet");
}
/**
* 根据自定义注解，获取所要导入表格的sheet名称和需要导入的字段名称
*
* @param clazz
* @throws Exception
*/
private void gatherAnnotationFields(Class clazz) throws Exception {
if (!clazz.isAnnotationPresent(ExcelIn.class)) {
throw new Exception(clazz.getName() + "类上没有ExcelIn注解");
}
ExcelIn excelIn = (ExcelIn) clazz.getAnnotation(ExcelIn.class);
this.sheetName = excelIn.sheetName();
// 得到所有定义字段
Field[] allFields = FieldUtils.getAllFields(clazz);
// 得到所有field并存放到一个list中
for (Field field : allFields) {
if (field.isAnnotationPresent(ExcelIn.class)) {
fields.add(field);
}
}
if (fields.isEmpty()) {
throw new Exception(clazz.getName() + "中没有ExcelIn注解字段");
}
}
private Object getCellValue(Cell cell) {
if (cell == null) {
return "";
}
Object obj = null;
switch (cell.getCellTypeEnum()) {
case BOOLEAN:
obj = cell.getBooleanCellValue();
break;
case ERROR:
obj = cell.getErrorCellValue();
break;
case FORMULA:
try {
obj = String.valueOf(cell.getStringCellValue());
} catch (IllegalStateException e) {
obj = numericToBigDecimal(cell);
}
break;
case NUMERIC:
obj = getNumericValue(cell);
break;
case STRING:
String value = String.valueOf(cell.getStringCellValue());
value = value.replace(" ", "");
value = value.replace("\n", "");
value = value.replace("\t", "");
obj = value;
break;
default:
break;
}
return obj;
}
private Object getNumericValue(Cell cell) {
// 处理日期格式、时间格式
if (HSSFDateUtil.isCellDateFormatted(cell)) {
return cell.getDateCellValue();
} else if (cell.getCellStyle().getDataFormat() == 58) {
// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
double value = cell.getNumericCellValue();
return org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
} else {
return numericToBigDecimal(cell);
}
}
private Object numericToBigDecimal(Cell cell) {
String valueOf = String.valueOf(cell.getNumericCellValue());
BigDecimal bd = new BigDecimal(valueOf);
return bd;
}
}

3.ExcelIn注解

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* @author Lei
* @time 2019/10/29
* @Description
*/
@Retention(value = RetentionPolicy.RUNTIME)
@Target(value = {ElementType.TYPE, ElementType.FIELD})
public @interface ExcelIn {
/**
* 导入sheet名称
*
* @return
*/
String sheetName() default "";
/**
* 字段对应的表头名称
*
* @return
*/
String title() default "";
}

4.创建excel中的对象

import lombok.Data;
import lombok.ToString;
import java.util.Date;
/**
* @author Lei
* @time 2019/10/29
* @Description
*/
@ToString
@Data
@ExcelIn(sheetName = "用户")
public class User {
private String id;
@ExcelIn(title = "姓名")
private String name;
@ExcelIn(title = "年龄")
private Integer age;
@ExcelIn(title = "出生日期")
private Date birthDate;
}

5.controller层接收

@PostMapping("/batch/excel")
@ApiOperation(value = "根据excel文件批量导入")
public ResponseVO batchAddDeviceByExcelImport(MultipartFile multipartFile) {
return new ResponseVO(deviceService.addDeviceByExcelImport(multipartFile));
}

6.service处理(此处仅打印)

public boolean addDeviceByExcelImport(MultipartFile multipartFile) {
File file = null;
try {
file = File.createTempFile("temp", null);
} catch (IOException e) {
e.printStackTrace();
}
try {
multipartFile.transferTo(file);
} catch (IOException e) {
e.printStackTrace();
}
file.deleteOnExit();
InputStream inputStream = null;
try {
inputStream = new FileInputStream(file);
} catch (FileNotFoundException e) {
e.printStackTrace();
}
ExcelImportUtil<User> reader = new ExcelImportUtil<>();
List<User> userList = null;
try {
userList = reader.read(inputStream, User.class);
} catch (Exception e) {
log.error(e.getMessage());
throw new CodeException("51302", e.getMessage());
}
userList.stream().forEach(e -> log.info(e.toString()));
return true;
}