poi将Excel表格中数据遍历出来并插入数据库

需要引用架包

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.8</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.8</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.8</version>
        </dependency>

controller层

@RequestMapping("/uploadHead")//路径
@ResponseBody//将返回值处理为json
//注意方法传的参数名字一定要与前台对应不然会报空值
public int UserUploadHead(MultipartFile headimg, HttpServletRequest request) throws IOException {
//        文件上传
//        1.得到文件名字
//        2.给文件重命名
//        3.指定保存路径
//        4.上传

    String fileName=headimg.getOriginalFilename();
    UUID uuid=UUID.randomUUID();
    String newFileName=uuid+fileName;
    String path=request.getServletContext().getRealPath("/avatar");
    File file1=new File(path);
    if (!file1.exists()){
        file1.mkdirs();
    }
    // 把文件写到指定路径
    String savePath=path+File.separator+newFileName;
    File finalPach=new File(savePath);
    headimg.transferTo(finalPach);
    System.out.println(finalPach);
    //将文件上传后的到文件路径便于寻找

    File f = new File(String.valueOf(finalPach));
    FileInputStream is = new FileInputStream(f);
    XSSFWorkbook wbs = new XSSFWorkbook(is);
    XSSFSheet childSheet = wbs.getSheetAt(0);
    System.out.println("有行数"+ childSheet.getLastRowNum());
    for (int j = 1; j < childSheet.getLastRowNum(); j++) {
        Map<String,Object> map=new HashMap<>();
        XSSFRow row = childSheet.getRow(j);//行
        // System.out.println(row.getPhysicalNumberOfCells());
        // System.out.println("有列数" + row.getLastCellNum());
        if (null!= row) {
            for (int k = 0; k < row.getLastCellNum(); k++) {
                XSSFCell cell = row.getCell(k);//列
                if (null!= cell) {
                    switch (cell.getCellType()) {
                        case HSSFCell.CELL_TYPE_NUMERIC:// 数字
                            System.out.print(cell.getNumericCellValue()
                                    + "   ");
                            map.put(childSheet.getRow(0).getCell(k).getStringCellValue(),cell.getNumericCellValue());
                            break;
                        case HSSFCell.CELL_TYPE_STRING:// 字符串
                            System.out.print(cell.getStringCellValue()
                                    + "   ");
                            map.put(childSheet.getRow(0).getCell(k).getStringCellValue(),cell.getStringCellValue());
                            break;
                        case HSSFCell.CELL_TYPE_BOOLEAN:// Boolean
                            System.out.println(cell.getBooleanCellValue()
                                    + "   ");
                            map.put(childSheet.getRow(0).getCell(k).getStringCellValue(),cell.getBooleanCellValue());
                            break;
                        case HSSFCell.CELL_TYPE_FORMULA:// 公式
                            System.out.print(cell.getCellFormula() + "   ");
                            map.put(childSheet.getRow(0).getCell(k).getStringCellValue(),cell.getCellFormula());
                            break;
                        case HSSFCell.CELL_TYPE_BLANK:// 空值
                            System.out.println(" ");
                            map.put(childSheet.getRow(0).getCell(k).getStringCellValue(),null);
                            break;
                        case HSSFCell.CELL_TYPE_ERROR:// 故障
                            System.out.println(" ");
                            map.put(childSheet.getRow(0).getCell(k).getStringCellValue(),null);
                            break;
                        default:
                            System.out.print("未知类型   ");
                            map.put(childSheet.getRow(0).getCell(k).getStringCellValue(),null);
                            break;
                    }

                } else {
                    System.out.print("-   ");
                }
            }
        }
//        System.out.println(map);
        addInfoService.add(map);//存入数据库
    }


    return 0;
    }

暂时代码只支持.xlsx格式
文件上传前台部分参考ajax与文件上传
代码参考于召唤思维