Excel文件导入数据库思路解析:
- 准备好需要import的Excel文件
- 准备Excel文件数据对应的Bean
- 引入POI相关的Jar依赖
- 做好.xls和 .xlsx 的兼容处理
- 读取sheet数量为N,进行N轮迭代处理
- 每一轮迭代处理,都是先获取Row对象,接着对Row中的每个单元格Cell进行数据类型判断匹配,按需处理
- 将第6步的数据初始化成一个个bean实例
- 将bean实例不断添加入List列表中
public class Country {
private String name;
private String code;
public Country() {
}
public Country(String name, String code) {
this.name = name;
this.code = code;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
Country country = (Country) o;
return Objects.equals(name, country.name) &&
Objects.equals(code, country.code);
}
@Override
public int hashCode() {
return Objects.hash(name, code);
}
}
public class ReadExcelFileToList {
private static final Logger logger = LoggerFactory.getLogger(ReadExcelFileToList.class);
/**
* @return java.util.List<com.example.utils.poi.Country>
* @throws
* @description 读取Excel文件中的数据
* @params [fileName]
*/
public static List<Country> readExcelData(String fileName) {
List<Country> countryList = new ArrayList<Country>();
try {
// 文件输入流实例的初始化
FileInputStream fis = new FileInputStream(fileName);
// 按文件类型,初始化workbook
Workbook workbook = initWorkbookByFileType(fileName, fis);
// 获取Excel文件中有效的sheet数量
int numberOfSheets = workbook.getNumberOfSheets();
for (int indexOfSheets = 0; indexOfSheets < numberOfSheets; indexOfSheets++) {
// 获取索引值对应的sheet
Sheet sheet = workbook.getSheetAt(indexOfSheets);
for (Row rowCells : sheet) {
String name = "";
String code = "";
// 获取行数据Row的迭代器
Iterator<Cell> cellIterator = rowCells.cellIterator();
while (cellIterator.hasNext()) {
// 获取单元格Cell实例
Cell cell = cellIterator.next();
// 校验Cell单元格数据类型,并依次做出相应的处理
switch (cell.getCellTypeEnum().getCode()) {
case Cell.CELL_TYPE_STRING:
if (code.equalsIgnoreCase("")) {
code = cell.getStringCellValue().trim();
} else if (name.equalsIgnoreCase("")) {
name = cell.getStringCellValue().trim();
} else {
// 异常数据,无需做任何处理
System.out.println("abnormal data::" + cell.getStringCellValue());
}
break;
case Cell.CELL_TYPE_NUMERIC:
System.out.println("abnormal data::" + cell.getNumericCellValue());
}
}
Country country = new Country(name, code);
countryList.add(country);
}
}
// 关闭文件输入流
fis.close();
} catch (IOException e) {
logger.error("============ 异常信息:[{}] ============ ", e.getMessage());
logger.error(" ============ [{}] ============ ", e);
}
return countryList;
}
/**
* @return org.apache.poi.ss.usermodel.Workbook
* @throws
* @description 初始化workbook
* @params [fileName, fis]
*/
private static Workbook initWorkbookByFileType(String fileName, FileInputStream fis) throws IOException {
Workbook workbook = null;
if (fileName.toLowerCase().endsWith("xlsx")) {
// 2007版本的Excel
workbook = new XSSFWorkbook(fis);
} else if (fileName.toLowerCase().endsWith("xls")) {
// 2003版本的Excel
workbook = new HSSFWorkbook(fis);
}
return workbook;
}
public static void main(String args[]) {
List<Country> list = readExcelData("countries.xls");
for (Country country : list) {
System.out.println("Record :" + country);
}
}
}
下附Excel文件测试数据展示图以及代码运行结果:
- Excel文件数据展示:
- 代码执行结果:
Record :Country{name='CN', code='中国'}
Record :Country{name='USA', code='美国'}