一、SpringBoot本地读取Excel文件demo
提示:文件网络上传自行编写,大同小异。
1.导入依赖 :这边得依赖自行看着添加,我所用得到得就这些
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-contrib -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-contrib</artifactId>
<version>3.6</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-scratchpad -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.17</version>
</dependency>
2.建立工具类:
public class ExcelFormatUtil {
/**
* 读取excel表
*
* @param
* @param
* @return Workbook work = new XSSFWorkbook("D://GoolgeDownload//Summary.xlsx");
* @throws Exception
*/
public static List<List<Object>> getBankListByExcel() throws Exception {
List<List<Object>> list = new ArrayList<List<Object>>(); // 读取的数据放入该集合中
XSSFWorkbook book = new XSSFWorkbook("D://GoolgeDownload//医院.xlsx"); // 文件所在位置
XSSFSheet sheet = book.getSheetAt(0);
for (int i = 1; i < sheet.getLastRowNum()+1; i++) {
List<Object> list1 = new ArrayList<>();
XSSFRow row = sheet.getRow(i);
if (row != null) {
if(row.getCell(4) != null){ // 获取特殊格式的字段,如:改字段的属性为数字
row.getCell(4).setCellType(Cell.CELL_TYPE_STRING); // 将改字段的属性设置为string类型
}
String name = row.getCell(2).getStringCellValue(); // 医院名称 // 这边为读取的字段名称 ,写自己的就好
String level = row.getCell(3).getStringCellValue(); // 医院等级
String cityId = row.getCell(4).getStringCellValue(); // 城市id
list1.add(name); // 将读取的数据放入集合
list1.add(level);
list1.add(cityId);
list.add(list1);
}
}
return list;
}
3.再springboot得测试类中进行测试
@Test
public void test3() throws Exception{
List<List<Object>> bankListByExcel = ExcelFormatUtil.getBankListByExcel();
System.out.println("=========="+bankListByExcel.size()); // 打印集合得数量是否正确
for(int i = 0; i< bankListByExcel.size() ; i++){
List<Object> list = bankListByExcel.get(i); // 获取集合对象
HospitalDO hospitalDO = new HospitalDO(); // 开始封装对象
hospitalDO.setName(list.get(0).toString());
hospitalDO.setHospitalLevel(list.get(1).toString());
hospitalDO.setCityId(Long.valueOf(list.get(2).toString()));
hospitalDO.setGmtCreate(new Date());
hospitalDO.setGmtModified(new Date());
Integer insert = hospitalDOMapper.insert(hospitalDO); // 执行数据库得插入操作
}
}