导入表格数据
![](https://img-blog.csdnimg.cn/direct/259149e80e9441108835377836eb7f35.png)
1、导入依赖
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-csv</artifactId>
<version>1.10.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.4.0</version>
</dependency>
2、自定义一个注解,目的是将excel文件中的数据,赋值给指定变量
org.apache.commons.csv.CSVRecord类底层也是通过注解的形式,将表格数据赋值给指定的变量,但我使用CSVRecord读取文件会乱码。
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelName {
//用来对应excel文件的表头名称
String name();
}
3、自定义工具类实现读取excel数据的功能
import com.way2.vo.ExcelName;
import org.apache.poi.ss.usermodel.*;
import java.io.FileInputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
public class ImportCsvFileUtils {
public static <T> List<T> readExcel(String filePath, Class<T> clazz) throws Exception {
FileInputStream fileInputStream = new FileInputStream(filePath);
List<T> dataList = new ArrayList<>();
Workbook workbook = null;
try {
workbook = generateWorkBook(fileInputStream);
Sheet sheet = workbook.getSheetAt(0);
// 获取第一行(表头)的数据
Row headerRow = sheet.getRow(0);
Field[] fields = clazz.getDeclaredFields();
// 遍历每一行数据(rowIndex = 1,表示排除表头)
for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
Row row = sheet.getRow(rowIndex);
T entity = clazz.newInstance();
for (Field field : fields) {
ExcelName annotation = field.getAnnotation(ExcelName.class);
if (annotation != null) {
String columnName = annotation.name();
//获取指定某一行具体的那一列单元格数据
Cell cell = row.getCell(findSameHeaderName(headerRow, columnName));
if (cell != null) {
setFieldValue(entity, field, cell);
}
}
}
dataList.add(entity);
}
return dataList;
} catch (Exception e) {
throw new IllegalArgumentException("解析文件失败");
} finally {
if (workbook != null) {
workbook.close();
}
if (fileInputStream != null) {
fileInputStream.close();
}
}
}
/**
* 提供文件流,调用Apache POI组建的创建方法,
* 由组件自己来确定应该创建 XSSFWorkbook类型的对象,还是 HSSFWorkbook类型的对象
*
* Excel 2007以下的版本,需要使用 HSSFWorkbook类型来创建 Workbook对象
* 否则会提示:You need to call a different part of POI to process this data (eg HSSF instead of XSSF)
*
* @param fileInputStream
* @return
*/
public static Workbook generateWorkBook(FileInputStream fileInputStream) {
try {
Workbook workbook = WorkbookFactory.create(fileInputStream);
return workbook;
} catch (Exception e) {
throw new IllegalArgumentException();
}
}
/**
* @param entity 通过反射创建的对象
* @param field 需要赋值的具体字段
* @param cell
* @throws IllegalAccessException
*/
private static void setFieldValue(Object entity, Field field, Cell cell) throws IllegalAccessException {
field.setAccessible(true);
Class<?> fieldType = field.getType();
Object value = getCellValue(cell, fieldType);
field.set(entity, value);
}
private static Object getCellValue(Cell cell, Class<?> fieldType) {
switch (cell.getCellType()) {
case STRING:
//防止出现制表符(\t),因此去掉首尾空格
return cell.getStringCellValue().trim();
case NUMERIC:
if (fieldType == Integer.class || fieldType == int.class) {
return (int) cell.getNumericCellValue();
} else if (fieldType == Double.class || fieldType == double.class) {
return cell.getNumericCellValue();
} else {
//可能会出现科学计数法(比如,2.3456789E10)展示的数据,需要转为数字
return BigDecimal.valueOf(cell.getNumericCellValue()).toPlainString();
}
// 其他类型转换...
// 处理其他类型的cell
default:
return null;
}
}
/**
* 找到相同的表头名称,取它对应的值
*
* @param header 表头
* @param columnName 列名
* @return
*/
private static int findSameHeaderName(Row header, String columnName) {
for (int i = 0; i < header.getLastCellNum(); i++) {
//防止出现制表符(\t),因此去掉首尾空格
String currentHeader = header.getCell(i).getStringCellValue().trim();
if (columnName.equals(currentHeader)) {
return i;
}
}
// 如果未找到列名,则返回-1,这样 row.getCell(-1) 会返回报错
return -1;
}
}
4、定义实体,使用自定义注解匹配表头,接收数据
public class OrgInfo {
@ExcelName(name = "企业名称")
private String orgName;
@ExcelName(name = "企业税号")
private String taxpayerId;
public String getOrgName() {
return orgName;
}
public void setOrgName(String orgName) {
this.orgName = orgName;
}
public String getTaxpayerId() {
return taxpayerId;
}
public void setTaxpayerId(String taxpayerId) {
this.taxpayerId = taxpayerId;
}
}
5、运行代码
public static void main(String[] args) throws Exception {
String path2 = "C:\\Users\\UMR\\Desktop\\测试导入文件.XLSX";
List<OrgInfo> result2 = ImportCsvFileUtils.readExcel(path2, OrgInfo.class);
System.out.println(result2);
}