java - 如何将唯一数据从 excel 存储到数据库（使用 apache poi）？

Question

如果工作表中存在多个重复值（使用apache poi），如何将excel中单元格中的唯一数据（例如电话号码和电子邮件ID应该是唯一的）存储到数据库中？

下面是读取excel数据并将其发送到mysql数据库的代码

公共类 ExcelToDatabase {

public static void main(String[] args) throws SQLException, IOException {
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/dbName","root","root");
    Statement statement = con.createStatement();
    
    
    FileInputStream fis = new FileInputStream("/home/ist/Proj/ApachePOI/datafiles/TestData.xlsx");
    XSSFWorkbook workbook = new XSSFWorkbook(fis);
    XSSFSheet sheet = workbook.getSheetAt(0);
    
    int rows = sheet.getLastRowNum();
    
    for(int r=1; r<=rows; r++) {
        
        XSSFRow row = sheet.getRow(r);
        double EmpId = row.getCell(0).getNumericCellValue();
        String fName = row.getCell(1).getStringCellValue();
        String lName = row.getCell(2).getStringCellValue();
        int Age = (int) row.getCell(3).getNumericCellValue();
        String Email = row.getCell(4).getStringCellValue();
        String Phone = row.getCell(5).getStringCellValue();
        
        
        String sql = "INSERT INTO table_name values('"+EmpId+"','"+fName+"','"+lName+"', '"+Age+"','"+Email+"', '"+Phone+"')";
        statement.execute(sql);
        statement.execute("commit");
        
    }
    workbook.close();
    fis.close();
    con.close();
    
    System.out.println("data has been exported to the Database");
}

标签： javadatabaseapache-poi

Answer 1

public static void main(String[] args) throws SQLException, IOException {
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/dbName","root","root");
    Statement statement = con.createStatement();
    
    
    FileInputStream fis = new FileInputStream("/home/ist/Proj/ApachePOI/datafiles/TestData.xlsx");
    XSSFWorkbook workbook = new XSSFWorkbook(fis);
    XSSFSheet sheet = workbook.getSheetAt(0);
    
    int rows = sheet.getLastRowNum();
}