前言：
本工具类提供了Excel导入功能，通过反射机制将Excel中数据映射到实体类中，从而获取Excel数据，工具类依赖org.apache.poi包。支持RESTful API，支持Spring MVC中使用。
一.本工具类支持功能（2021-08-04更新）：
- 支持File类型导入
- 支持MultipartFile类型Excel（通过Form表单上传的格式）导入
- 支持Excel2003及以上版本
- 支持空行跳过
- 支持过滤肉眼看上去为空的单元格（通过
正则表达式过滤空格、制表符、换行符） - 支持合并单元格（v2021.08.04）
- 支持各种数据类型：文本、数值、布尔、空值、公式（若为公式，则读取其真实数值，而不是计算公式）
- 支持实体类中的java数据类型：
int、float、double、byte、boolean、Date（yyyy-MM-dd HH:mm:ss）、LocalDateTime（yyyy-MM-dd HH:mm:ss）、LocalDate（yyyy-MM-dd）、BigDecimal、String，Excel内容可自动转换为以上类型。 - 导入成功后，Excel文件自动删除，不占用服务器空间
示例：以下效果的Excel，可以很好的支持：
二.工具类
1.所需jar包依赖org.apache.poi版本：
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.0</version>
</dependency>
2.工具类源码
本工具类，可直接在项目中使用，放心CV！
// TODO 修改为你的包名
package com.excel.util;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.web.multipart.MultipartFile;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* Excel导入工具类
*/
public class ImportExcelUtils {
// 正则表达式 ,用于判断是get还是set方法
private static final String REGEX = "[a-zA-Z]";
//public static void main(String[] args) throws Exception {
// File file = new File("d:\\a.xlsx");
// List<List<Object>> lists = importExcelFile(file, 1, 0, ExcelImportInput.class);
// System.out.println();
//}
/**
* @param multipartFile
* @param startRow 开始行
* @param endRow 结束行（0表示到最后一行结束）
* @param clazz
* @return
* @throws Exception
*/
public static List<List<Object>> importExcelMultipartFile(MultipartFile multipartFile, int startRow, int endRow,
Class<?> clazz) throws Exception {
// 判断文件是否存在
if (multipartFile == null || multipartFile.isEmpty()) {
throw new IOException("Excel文件内容为空或不存在！");
}
String name = "Excel" + System.nanoTime();
File file = File.createTempFile(name, null);
multipartFile.transferTo(file);
if (!file.exists()) {
throw new IOException("文件名为" + file.getName() + "Excel文件不存在！");
}
return importExcelFile(file, startRow, endRow, clazz);
}
/**
* 根据文件导入Excel，仅导入数据，去除校验
*
* @param file 文件
* @param startRow 开始行，从0开始
* @param endRow 结束行，0表示所有行; 正数表示到第几行结束; 负数表示到倒数第几行结束
* @param clazz sheet需要映射的对象类型
* @return List<List < Object>>
* @throws Exception
*/
public static List<List<Object>> importExcelFile(File file, int startRow, int endRow, Class<?> clazz)
throws Exception {
List<List<Object>> sheetsData = new ArrayList<>();
// 判断文件是否存在
if (!file.exists()) {
throw new IOException("文件名为" + file.getName() + "Excel文件不存在！");
}
Workbook wb = null;
FileInputStream inputStream = null;
try {
inputStream = new FileInputStream(file);
// 工厂模式，根据Excel版本（2003版以前版本，或其他版本），创建对应的工作薄处理类
wb = WorkbookFactory.create(inputStream);
for (int sheetNumber = 0; sheetNumber < wb.getNumberOfSheets(); sheetNumber++) {
List<Row> rowList = new ArrayList<Row>();
Sheet sheet = wb.getSheetAt(sheetNumber);
// 获取最后行号
int lastRowNum = sheet.getLastRowNum();
Row row = null;
Pattern p = Pattern.compile("\\s*|\t|\r|\n");
// 循环读取
for (int i = startRow; i <= lastRowNum + endRow; i++) {
// 是否全部单元格都为空
boolean isEmptyRow = true;
row = sheet.getRow(i);
if (row != null) {
// 判断是否为空行
for (int j = 0; j < row.getLastCellNum(); j++) {
String value = p.matcher(getCellValue(row.getCell(j))).replaceAll("");
if (!value.trim().equals("")) {
isEmptyRow = false;
break;
}
}
// 校验规则：如果是空白行，没有数据，仅有空格符、制表符等字符（用户无意间输入的字符），则应该过滤掉。
if (!isEmptyRow) {
// 该行数据中存在非空单元格，则返回该行
rowList.add(row);
}
}
}
sheetsData.add(returnObjectList(rowList, clazz));
}
} catch (IOException e) {
e.printStackTrace();
} finally {
if (wb != null) {
wb.close();
}
if (file != null) {
file.delete();
}
}
return sheetsData;
}
/**
* 功能:返回指定的对象集合
*/
private static List<Object> returnObjectList(List<Row> rowList, Class<?> clazz) {
List<Object> objectList = null;
Object obj = null;
String attribute = null;
String value = null;
int j = 0;
try {
objectList = new ArrayList<Object>();
Field[] declaredFields = clazz.getDeclaredFields();
for (Row row : rowList) {
j = 0;
obj = clazz.newInstance();
for (Field field : declaredFields) {
try {
attribute = field.getName().toString();
Cell cell = row.getCell(j);
value = getCellValue(cell);
if (isMergedRegion(row.getSheet(), row.getRowNum(), cell.getColumnIndex())) {
value = getMergedRegionValue(row.getSheet(), row.getRowNum(), cell.getColumnIndex());
}
setAttributeValue(obj, attribute, value.trim());
j++;
} catch (Exception e) {
System.out.println("属性映射出错，属性名：" + attribute + "属性值：" + value);
//log.info("属性映射出错，属性名：" + attribute + "属性值：" + value);
e.printStackTrace();
}
}
// 仅提取没有非空字段的对象
objectList.add(obj);
}
} catch (Exception e) {
e.printStackTrace();
}
return objectList;
}
/**
* 功能:获取单元格的值
*/
private static String getCellValue(Cell cell) {
Object result = "";
if (cell != null) {
switch (cell.getCellType()) {
case STRING:
result = cell.getStringCellValue();
break;
case NUMERIC:
result = cell.getNumericCellValue();
break;
case BOOLEAN:
result = cell.getBooleanCellValue();
break;
case FORMULA:
//result = cell.getCellFormula();
// 如果是公式单元格，返回其数值
result = cell.getNumericCellValue();
break;
case ERROR:
result = cell.getErrorCellValue();
break;
case BLANK:
break;
default:
break;
}
}
return result.toString();
}
/**
* 功能:给指定对象的指定属性赋值
*/
private static void setAttributeValue(Object obj, String attribute, String value) {
if (value == null || value.trim().equals("")) {
return;
}
// 得到该属性的set方法名
String method_name = convertToMethodName(attribute, obj.getClass(), true);
Method[] methods = obj.getClass().getMethods();
for (Method method : methods) {
/**
* 因为这里只是调用bean中属性的set方法，属性名称不能重复 所以set方法也不会重复，所以就直接用方法名称去锁定一个方法 （注：在java中，锁定一个方法的条件是方法名及参数）
*/
if (method.getName().equals(method_name)) {
Class<?>[] parameterC = method.getParameterTypes();
try {
/**
* 如果是(整型,浮点型,布尔型,字节型,时间类型), 按照各自的规则把value值转换成各自的类型 否则一律按类型强制转换(比如:String类型)
*/
if (parameterC[0] == int.class || parameterC[0] == Integer.class) {
int index = value.lastIndexOf(".");
if (index != -1) {
value = value.substring(0, index);
}
try {
// 去除整数末位的.0
method.invoke(obj,
Integer.valueOf(new BigDecimal(value).stripTrailingZeros().toPlainString()));
} catch (Exception e) {
e.printStackTrace();
}
break;
} else if (parameterC[0] == float.class || parameterC[0] == Float.class) {
method.invoke(obj, Float.valueOf(value));
break;
} else if (parameterC[0] == double.class || parameterC[0] == Double.class) {
method.invoke(obj, Double.valueOf(value));
break;
} else if (parameterC[0] == byte.class || parameterC[0] == Byte.class) {
method.invoke(obj, Byte.valueOf(value));
break;
} else if (parameterC[0] == boolean.class || parameterC[0] == Boolean.class) {
method.invoke(obj, Boolean.valueOf(value));
break;
} else if (parameterC[0] == Date.class) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date date = null;
try {
date = sdf.parse(value);
} catch (Exception e) {
e.printStackTrace();
}
method.invoke(obj, date);
break;
} else if (parameterC[0] == LocalDateTime.class) {
DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
try {
method.invoke(obj, LocalDateTime.parse(value, df));
} catch (Exception e) {
}
break;
} else if (parameterC[0] == LocalDate.class) {
DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd");
try {
method.invoke(obj, LocalDate.parse(value, df));
} catch (Exception e) {
e.printStackTrace();
}
break;
} else if (parameterC[0] == BigDecimal.class) {
method.invoke(obj, new BigDecimal(value).stripTrailingZeros());
break;
} else {
method.invoke(obj, parameterC[0].cast(value));
break;
}
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} catch (SecurityException e) {
e.printStackTrace();
}
}
}
}
/**
* 功能:根据属性生成对应的set/get方法
*/
private static String convertToMethodName(String attribute, Class<?> objClass, boolean isSet) {
/** 通过正则表达式来匹配第一个字符 **/
Pattern p = Pattern.compile(REGEX);
Matcher m = p.matcher(attribute);
StringBuilder sb = new StringBuilder();
/** 如果是set方法名称 **/
if (isSet) {
sb.append("set");
} else {
/** get方法名称 **/
try {
Field attributeField = objClass.getDeclaredField(attribute);
/** 如果类型为boolean **/
if (attributeField.getType() == boolean.class || attributeField.getType() == Boolean.class) {
sb.append("is");
} else {
sb.append("get");
}
} catch (SecurityException e) {
e.printStackTrace();
} catch (NoSuchFieldException e) {
e.printStackTrace();
}
}
/** 针对以下划线开头的属性 **/
if (attribute.charAt(0) != '_' && m.find()) {
sb.append(m.replaceFirst(m.group().toUpperCase()));
} else {
sb.append(attribute);
}
return sb.toString();
}
/**
* 判断指定的单元格是否是合并单元格
*
* @param sheet
* @param row 行下标
* @param column 列下标
* @return
*/
private static boolean isMergedRegion(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if (row >= firstRow && row <= lastRow) {
if (column >= firstColumn && column <= lastColumn) {
return true;
}
}
}
return false;
}
/**
* 获取合并单元格的值
*
* @param sheet
* @param row
* @param column
* @return
*/
private static String getMergedRegionValue(Sheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress ca = sheet.getMergedRegion(i);
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();
if (row >= firstRow && row <= lastRow) {
if (column >= firstColumn && column <= lastColumn) {
Row fRow = sheet.getRow(firstRow);
Cell fCell = fRow.getCell(firstColumn);
return getCellValue(fCell);
}
}
}
return null;
}
}
三.如何使用：
1.如何定义本工具类支持的Excel实体类
实体类属性的顺序，一定要与Excel首行列名顺序一致！！！
@Data
public class ExcelImportInput implements Serializable {
/**
* 年份
*/
private Integer year;
/**
* 公司
*/
private String companyName;
/**
* 治理项目
*/
private String projectName;
// 其他属性以此类推，省略了……
}
实体类属性如果是时间类型，格式必须保持以下格式：
| 类型 | 工具类支持的格式 |
|---|---|
| Date | yyyy-MM-dd HH:mm:ss |
| LocalDateTime | yyyy-MM-dd HH:mm:ss |
| LocalDate | yyyy-MM-dd |
如果不是以上格式，那就直接用String类型接收时间数据吧，然后业务处理时，再做时间转换。
保持顺序的原因解释：
本工具类实现中，通过反射来将数据绑定到对应字段上，如果属性顺序和列名顺序不一致，会出现属性的类型不同导入出错。
2.导入MultipartFile类型(为前端提供接口时常用)
应用场景： Web前端页面调用后端接口，上传的是MultipartFile格式的Excel文件，通过本工具类，可直接解析为List<List<Object>>，再通过强制类型转换为你自己定义的实体类列表List<List<EventDTO>> eventList。
使用示例：
@PostMapping(value = "/project/import")
public ResultObject importAlarmEvents(@RequestParam MultipartFile file) {
ResultObject result = new ResultObject();
try {
// 从Excel第二行(第一行是表头跳过)起到最后一行结束
List<List<Object>> excelData = ImportExcelUtil.importExcelMultipartFile(file, 1, 0, EventDTO.class);
if (excelData == null || excelData.isEmpty()) {
result.setMessage("导入失败,Excel内容为空");
return result;
}
//将Excel中数据，转为你的实体类
List<List<EventDTO>> eventList= new ArrayList<>();
for (List<?> list : excelData) {
eventList.add((List<EventDTO>) list);
}
Map<String, Object> res = eventService.importEvent(eventList);
} catch (Exception e) {
result.setMessage(e.getMessage());
}
return result;
}
注意：
如果需要实现导入Excel成功后，再将Excel上传到服务器上保存，不建议使用此方式，因为MultipartFile类型通过multipartFile.transferTo(file)转为File类型时，文件流就会关闭，如果再次通过multipartFile.transferTo(file)转为File可能会（window没出现，linux服务器必现）出现以下错误：
java.io.FileNotFoundException: /tmp/tomcat.9039068360389.8081/work/Tomcat/localhost/ROOT/upload_f359ed4e_cd65_080.tmp (No such file or directory)
如何完成上述需求：导入Excel成功后，再将Excel上传到服务器上保存
①.上传文件，将其由MultipartFile类型通过multipartFile.transferTo(file)转为File类型
②.通过工具类读取excel文件
③.将Excel上传服务器
示例：
public Integer importExcel( @RequestParam Integer year,@RequestParam MultipartFile file) {
if (!ObjectUtils.isEmpty(file)) {
log.warn("Excel文件为空");
}
try {
// 将MultipartFile转为File，并重命名文件，防止并发
String[] originalName = file.getOriginalFilename().split("\\.");
String fileName = originalName[0] + "_" + System.nanoTime() + "." + originalName[1];
File excelFile = new File(path + fileName);
FileUtils.forceMkdirParent(excelFile);
if (!excelFile.exists()) {
excelFile.createNewFile();
}
file.transferTo(excelFile);
// 由于Excel工具类会自动删除使用后的Excel文件，先创建一个临时副本，然后把正式的Excel保留在服务器上
File copyFile = File.createTempFile("copyFile", String.valueOf(System.nanoTime()));
FileUtils.copyFile(excelFile, copyFile);
List<List<Object>> lists = ImportExcelUtils.importExcelFile(copyFile, 1, 0, ExcelImportInput.class);
if (ObjectUtils.isEmpty(lists) || ObjectUtils.isEmpty(lists.get(0))) {
log.error("Excel导入时失败，Excel内容可能为空");
}
// 将Excel数据强转为实体类数据
List<ExcelImportInput> sheetDataList = new ArrayList<>();
for (List<?> list : lists) {
sheetDataList.addAll((List<ExcelImportInput>) list);
}
// 业务逻辑处理……
return importExcelService.importExcel(year, sheetDataList, excelFile);
} catch (Exception e) {
e.printStackTrace();
}
return 0;
}
3.导入文件格式为File
应用场景： 如果通过File上传文件，可选择此方式，其他效果和上述一致。
使用示例：
@PostMapping(value = "/project/import")
public ResultObject importAlarmEvents(@RequestParam File file) {
ResultObject result = new ResultObject();
try {
// 从Excel第一行起到最后一行结束,
List<List<Object>> excelData = ImportExcelUtil.importExcelFile(file, 1, 0, EventDTO.class);
if (excelData == null || excelData.isEmpty()) {
result.setMessage("导入失败,Excel内容为空");
return result;
}
//将Excel中数据，转为你的实体类
List<List<EventDTO>> alarmList = new ArrayList<>();
for (List<?> list : excelData) {
alarmList.addAll((List<EventDTO>) list);
}
Map<String, Object> res = eventService.importEvent(alarmList);
} catch (Exception e) {
result.setMessage(e.getMessage());
}
return result;
}
Excel实用教程集锦
以下是我写的关于Java操作Excel的所有教程，基本包含了所有场景。
1.如果简单导出推荐使用工具类的方式，这种配置最简单。
2.如果对导出样式要求极高的还原度，推荐使用Freemarker方式，FreeMarker模板引擎可以通吃所有Excel的导出，属于一劳永逸的方式，项目经常导出推荐使用这种方式。
3.Freemarker导出的Excel为xml格式，是通过重命名为xls后，每次会打开弹框问题，我在《Freemarker整合poi导出带有图片的Excel教程》也已经完美解决，本教程将直接导出真正的xls格式，完美适配新版office和wps。Freemarker是无法导出带有图片的Excel，通过其他技术手段，也在本教程中完美导出带有图片的Excel。
4.下列教程中的代码都经本人和网友多次验证，真实有效！
958
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
