POI批量导入到数据库详解

第一步在pom文件中导入需要的jar包

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.10-FINAL</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>3.10-FINAL</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.10-FINAL</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.8</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.10-FINAL</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-examples</artifactId>
            <version>3.10-FINAL</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-excelant</artifactId>
            <version>3.10-FINAL</version>
        </dependency>

第二步从web获取文件file

    @ResponseBody
    @RequestMapping(value = "uploadRegionExl", method = RequestMethod.POST)
    public BaseResult<String> uploadSeatExl(HttpSession session, MultipartHttpServletRequest request) {
        try {
            String result = "";
            String fileName = "";
            MultipartFile mpf = null;
            Iterator<String> itr = request.getFileNames();
            while (itr.hasNext()) {
                 mpf = request.getFile(itr.next());
                fileName = mpf.getOriginalFilename();
                String format = fileName.substring(fileName.lastIndexOf("."));
                String saveExlPath = this.getClass().getResource("/").getPath();
                String uuid = Identities.uuid2();
                String newFileName = uuid + format;
//               saveExlPath = filePath + bookingExlPrefix;
                saveExlPath = "Z:/testexcel/";
                //
                File diagramDirFile = new File(saveExlPath);
                if (!diagramDirFile.exists()) {
                    // 如果文件夹不存在，则创建它
                    diagramDirFile.mkdirs();
                }
                saveExlPath = saveExlPath + File.separator + newFileName;
                FileCopyUtils.copy(mpf.getBytes(), new FileOutputStream(saveExlPath));
                result = saveExlPath;
                if(!result.endsWith(".xls")&&!result.endsWith(".xlsx"))
                {
                    return BaseResult.err("请选择以.xls和.xlsx结尾的文件");
                }
                List<AddSupplierPo> lists = AnalysisExlUtils.setFilePath(result);
                System.out.println(lists);
            }
            
            return BaseResult.ok(result);
        } catch (Exception e) {
            log.error("exception:(uploadSeatExl)" + e.getMessage(), e);
            return BaseResult.err(e.getMessage());
        }
    }

第三步写读取sheet数据方法

public class AnalysisExlUtils {
    private static final String SUFFIX_2003 = ".xls";
    private static final String SUFFIX_2007 = ".xlsx";

    // 保存源文件内容
    public static List<AddSupplierPo> setFilePath(String filePath) {
        List<AddSupplierPo> datas = new ArrayList<>();
        // 总的数据条数
        File file = new File(filePath);
        InputStream is;
        Workbook workbook = null;
        try {
            is = new FileInputStream(file);
            // 根据后缀，得到不同的Workbook子类，即HSSFWorkbook或XSSFWorkbook
            if (filePath.endsWith(SUFFIX_2003)) {
                workbook = new HSSFWorkbook(is);
            }
            else if (filePath.endsWith(SUFFIX_2007)) {
                workbook = new XSSFWorkbook(is);
            }
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        // 获取到所有的工作表
        int numberOfSheets = workbook.getNumberOfSheets();
        for (int i = 0; i < numberOfSheets; i++) {
            Sheet sheet = workbook.getSheetAt(i);
            Row row;
            Iterator<Row> iterator = sheet.iterator();
            while (iterator.hasNext()) {
                row = iterator.next();
                // 利用parseRow处理每一行，得到每个cell中的String
                AddSupplierPo rst = parseRow(row);
                datas.add(rst);
            }
        }

        return datas;
    }

    // 这里是解析每一行数据放入对象中
    private static AddSupplierPo parseRow(Row row) {
        AddSupplierPo supplierPo = new AddSupplierPo();

        // 判断这行记录是否存在
        if (row != null && row.getLastCellNum() >= 1 && !"".equals(getValue(row.getCell(0)))) {
            // 获取每一行
            if (ObjectUtils.isNotEmpty(row.getCell(0))) {
                supplierPo.setSupplierName(getValue(row.getCell(0)));
            }
            if (ObjectUtils.isNotEmpty(row.getCell(1))) {
                supplierPo.setUserName(getValue(row.getCell(1)));
            }
        }
        return supplierPo;
    }

    //转成String
    private static String getValue(Cell cell){  
       cell.setCellType((Cell.CELL_TYPE_STRING));
       return cell.getStringCellValue();   
    }  
}

第四，数据库中的实体为

public class AddSupplierPo {

 
    private String supplierName ;
    
    private String userName ;
}