POI读取文件,实现批量加数据
1、首先导入相关依赖:
<!--poi框架的两个依赖-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.0</version>
</dependency>
2、写在controller层的代码
FileInputStream fis =new FileInputStream("C:\\Users\\Administrator\\Desktop\\test.xls");
Workbook workbook = WorkbookFactory.create(fis);
Sheet sheet1 = workbook.getSheetAt(0);
int rowNum = sheet1.getLastRowNum(); //总行数
ArrayList<User> list = new ArrayList<>();
for (int i = 1; i < rowNum+1; i++) { //遍历每一行
Row row = sheet1.getRow(i);
Cell c1 = row.getCell(0);
c1.setCellType(CellType.STRING);
Cell c2 = row.getCell(1);
c1.setCellType(CellType.STRING);
Cell c3 = row.getCell(2);
c1.setCellType(CellType.STRING);
Cell c4 = row.getCell(3);
c1.setCellType(CellType.STRING);
Cell c5 = row.getCell(4);
c1.setCellType(CellType.STRING);
/*
c1是张三 ==>i行1列
c2是332898432edc466c9dd759b833c830a3 ==>i行2列
c3是男 ==>i行3列
c4是18 ==>i行4列
c5是篮球 ==>i行5列
* */
user re = new User();
re.setUserName(c1.getStringCellValue());
re.setUserId(c2.getStringCellValue());
re.setUserSex(c3.getStringCellValue());
re.setUserAge(c4.getStringCellValue());
re.setUserHobby(c5.getStringCellValue());
list.add(re);
}
for (User user: list) {
userRepository.save(user);
}
3、前端发送来一个请求时会调这个方法,最坑的是在调试时经常会出现类型转换错误,是excel单元格格式没设置好,比如这里要求的单元格格式是文本类型的,在wps里进行设置,如下图
一般这样设置亲测无效,在项目运行时还是会报错,
下面介绍正确的打开方式:
1、选中单元格所在列—>数据—>分列 —> 一直下一步,直到步骤3,选择文本,再点击完成,这样在取数据的时候就不会报错了