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

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

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

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

1.pom引入

org.apache.poi

poi

3.17

org.apache.poi

poi-ooxml

3.17

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 {

private static final Logger log = LoggerFactory.getLogger(ExcelImhttp://portUtil.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 title_to_index = new HashMap<>();

/**

* 所有带有ExcelIn注解的字段

*/

private final List 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 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 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.getErrorCellVahttp://lue();

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 reader = new ExcelImportUtil<>();

List 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;

}

7.测试

（1）两种文件类型的excel

（2）excel中格式如下，注意红色箭头所指的地方 对应user对象中的字段以及sheet名

（3）swagger测试

（4）成功打印

总结

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。