java - 从Excel工作表中读取多行并存储到数据库

Question

我正在尝试从 Excel 工作表中读取多行并将它们复制到 java 对象，然后使用 hibernate 将该对象存储到数据库中。

示例-

• 我有 userTravelBO java 对象。对于这个对象属性，我正在从 Excel 工作表中读取值。
• 如果 Excel 工作表只有一行，那么它工作正常。
• 如果 Excel 工作表包含多行，则从第二行开始，值不会设置为 userTravelBO 对象。

下面是我尝试过的代码。

Getting sheets from Excel file.

@Override
public Response addingUserFromExcel(String filename) {
    // TODO Auto-generated method stub
    File myFile = new File(filename);
    XSSFWorkbook myWorkBook = null;
    XSSFSheet mySheet = null;
    FileInputStream fis = null;
    int i = 0;
    try {
        fis = new FileInputStream(myFile);
        myWorkBook = new XSSFWorkbook(fis);
        while (i < myWorkBook.getNumberOfSheets()) {
            mySheet = myWorkBook.getSheetAt(i);
            switch(i){
            case 0 : addindUserToTable(mySheet);
                     break;
            case 1:  addVehicleToTable(mySheet);
                     break;
            case 2:  addTripToTable(mySheet);
                     break;
            case 3: addPackageToTable(mySheet);
                     break;
            }
            i++;

        }
        HashMap praf = new HashMap();
        praf.put("message", "uploaded successfully");
        return Response.ok(praf).build();
    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
        return Response.ok("file uploaded but not added to table").build();
    }


}

Setting values to the properties of UserTravelBO object and sending it store to DB.

    public Response addindUserToTable(XSSFSheet mySheet) {
    UserTravelBo userBO = null;
    Iterator<Row> rowIterator = mySheet.iterator();
    int j = 0;
    while (rowIterator.hasNext()) {
        userBO = new UserTravelBo();//creating a new object for each row.

        Row row = rowIterator.next();
        if(row.getRowNum()==0){
               continue; //just skip the rows if row number is 0 because it is heading
         }
        else{
        Iterator<Cell> cellIterator = row.cellIterator();
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();

            switch (j) {
            case 0:
                userBO.setUsername(cell.getStringCellValue());
                System.out.println("Excel"+cell.getStringCellValue());
                break;
            case 1:
                userBO.setPassword(cell.getStringCellValue());
                break;
            case 2:
                userBO.setHint(cell.getStringCellValue());
                break;
            case 3:
                userBO.setFirstname(cell.getStringCellValue());
                break;
            case 4:
                userBO.setMiddlename(cell.getStringCellValue());
                break;
            case 5:
                userBO.setLastname(cell.getStringCellValue());
                break;
            case 6:
                userBO.setStreet(cell.getStringCellValue());
                break;
            case 7:
                userBO.setArea(cell.getStringCellValue());
                break;
            case 8:
                userBO.setCity(cell.getStringCellValue());
                break;
            case 9:
                userBO.setState(cell.getStringCellValue());
                break;
            case 10:
                userBO.setCountry(cell.getStringCellValue());
                break;
            case 11:
                userBO.setPostalcode(cell.getStringCellValue());
                break;
            case 12:
                userBO.setTelephone(cell.getStringCellValue());
                break;
            case 13:
                userBO.setEmail(cell.getStringCellValue());
                break;
            case 14:
                userBO.setRolename(cell.getStringCellValue());
                break;
            case 15:
                userBO.setStatus(cell.getBooleanCellValue());
                break;

            default:
                break;
            }
            j++;
        }
    }
        System.out.println(userBO.getUsername());//for first row getting valid output but for the second row i am getting null.
        addUser(userBO);
    }
    return Response.ok("file uploaded and added to table").build();
}

Here I am creating new UserTravelBO object for each row iteration then why it is happening like this. I am not getting this.

One thing is coming in my mind that user has one to one relation with address table because of this only this thing happening.I don't know I am correct or wrong.

Answer 1

这是我的错误。我在每行的 while 循环之外初始化变量 j = 0。当一行完成时，“j”变量应该再次初始化为零，但我没有这样做。

Below is the correct code.

public Response addindUserToTable(XSSFSheet mySheet) {
UserTravelBo userBO = null;
Iterator<Row> rowIterator = mySheet.iterator();
//int j = 0; Here i was doing wrong
while (rowIterator.hasNext()) {
    userBO = new UserTravelBo();//creating a new object for each row.
    Row row = rowIterator.next();
    if(row.getRowNum()==0){
           continue; //just skip the rows if row number is 0 because it is   heading
     }
    else{
    int j = 0;//Here j should be initiated
    Iterator<Cell> cellIterator = row.cellIterator();
    while (cellIterator.hasNext()) {
        Cell cell = cellIterator.next();

        switch (j) {
        case 0:
            userBO.setUsername(cell.getStringCellValue());
            System.out.println("Excel"+cell.getStringCellValue());
            break;
        case 1:
            userBO.setPassword(cell.getStringCellValue());
            break;
        case 2:
            userBO.setHint(cell.getStringCellValue());
            break;
        case 3:
            userBO.setFirstname(cell.getStringCellValue());
            break;
        case 4:
            userBO.setMiddlename(cell.getStringCellValue());
            break;
        case 5:
            userBO.setLastname(cell.getStringCellValue());
            break;
        case 6:
            userBO.setStreet(cell.getStringCellValue());
            break;
        case 7:
            userBO.setArea(cell.getStringCellValue());
            break;
        case 8:
            userBO.setCity(cell.getStringCellValue());
            break;
        case 9:
            userBO.setState(cell.getStringCellValue());
            break;
        case 10:
            userBO.setCountry(cell.getStringCellValue());
            break;
        case 11:
            userBO.setPostalcode(cell.getStringCellValue());
            break;
        case 12:
            userBO.setTelephone(cell.getStringCellValue());
            break;
        case 13:
            userBO.setEmail(cell.getStringCellValue());
            break;
        case 14:
            userBO.setRolename(cell.getStringCellValue());
            break;
        case 15:
            userBO.setStatus(cell.getBooleanCellValue());
            break;

        default:
            break;
        }
        j++;
    }
}
    System.out.println(userBO.getUsername());//for first row getting valid    output but for the second row i am getting null.
    addUser(userBO);
}
return Response.ok("file uploaded and added to table").build();
}

谢谢。