SpringBoot本地读取Excel文件

一、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);			// 执行数据库得插入操作
    }
}