import excel file to postgreSQL table using Apache POI

Question

I have the following Excel.

name    qualification   address
aaa     BE              Chennai
bbb     BCA             Cbe

My database table columnName contains same as above. (ie. name, qualification, address).

And I am able to import the values into PostgreSQL database table as they are in Excel using the following code :

XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(inputFile));

// Get first sheet from the workbook
XSSFSheet sheet = wb.getSheetAt(0);

Row row;
Cell cell;

// Iterate through each rows from first sheet
Iterator<Row> rowIterator = sheet.iterator();

while (rowIterator.hasNext()) 
{
    row = rowIterator.next();

    // System.out.println ("Row No.: " + row.getRowNum ());
    // For each row, iterate through each columns
    Iterator<Cell> cellIterator = row.cellIterator();

    while (cellIterator.hasNext()) 
    {
        cell = cellIterator.next();
        System.out.println(cell +"\t");

        switch (cell.getCellType()) 
        {
            case Cell.CELL_TYPE_BOOLEAN:
                 System.out.println(cell.getBooleanCellValue()  + "\t");
                 break;

            case Cell.CELL_TYPE_NUMERIC:
                 System.out.println(cell.getNumericCellValue()  + "\t");
                 break;

            case Cell.CELL_TYPE_STRING:
                 //System.out.println(cell.getStringCellValue()  + "\t");
                 list.add(cell.getStringCellValue());
                 break;

            case Cell.CELL_TYPE_BLANK:
                 System.out.println(" ");
                 break;

            default:
                 System.out.println(cell);
       }
 }

 String name = row.getCell(0).getStringCellValue();
 String add = row.getCell(1).getStringCellValue();
 String quali = row.getCell(2).getStringCellValue();

 System.out.println(name+add+quali);
 InsertRowInDB(name,add,quali);
 System.out.println("");


    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    return "success";

  }
    public void InsertRowInDB(String name,String add,String quali) throws SQLException, ClassNotFoundException{

        Class.forName("org.postgresql.Driver");
        System.out.println("connected");
        Connection conn=DriverManager.getConnection("jdbc:postgresql:postgres", "postgres","sridevi");
        if(!name.equals("name") && !add.equals("qualification") && !quali.equals("address")){
        String query = "Insert into excel(name,qualification,address) values(?,?,?)";
        PreparedStatement ps = conn.prepareStatement(query);



        ps.setString(1, name);
        ps.setString(2, add);
        ps.setString(3, quali);

    ps.executeUpdate();}
    System.out.println("Values Inserted Successfully");
    }

If my database contains the columnName like (name, address, qualification) means,

How to import the above excel file into database table?

How to mapping the spreadsheet column header into database column header?


Please help me to find this.

Answer 1

The order of the columns as they are defined in the PostgreSQL database doesn't matter if you are explicitly referencing them in the INSERT statement.

So, this:

 String query = "Insert into excel(name,qualification,address) values(?,?,?)";
 PreparedStatement ps=conn.prepareStatement(query);

 ps.setString(1, name);
 ps.setString(2, quali);
 ps.setString(3, add);

Is the same as this:

 String query = "Insert into excel(address, name, qualification) values(?,?,?)";
 PreparedStatement ps=conn.prepareStatement(query);

 ps.setString(1, add);
 ps.setString(2, name);
 ps.setString(3, qualifi);

As for mapping the Excel sheet 's header fields to be used as the field names for the SQL INSERT statement you can loop through the sheet' s rows, and use the first row as the column names and then only insert the following rows.

public String loadSheetInDatabase(String inputFile) {
    try {
        XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(inputFile));

        // Get first sheet from the workbook
        XSSFSheet sheet = wb.getSheetAt(0);

        Row row;
        Cell cell;

        // Iterate through each rows from first sheet
        Iterator < Row > rowIterator = sheet.iterator();

        Boolean parsedHeaders = false;
        List < String > headers = new ArrayList < String > ();
        List < String > rowValues = null;
        while (rowIterator.hasNext()) {
            row = rowIterator.next();
            if (parsedHeaders) {
                rowValues = new ArrayList <String> ();
            }
            // System.out.println ("Row No.: " + row.getRowNum ());
            // For each row, iterate through each columns
            Iterator < Cell > cellIterator = row.cellIterator();

            while (cellIterator.hasNext()) {
                cell = cellIterator.next();
                if (!parsedHeaders) {
                    headers.add(cell.getStringCellValue());
                } else {
                  rowValues.add(cell.getStringCellValue());
                }
            }
            if(!parsedHeaders){
              parsedHeaders = true;
            }else{
              String rowValuesString = "";
              for(String fieldValue : rowValues){
                rowValuesString += fieldValue + " ";
              }
              System.out.println(rowValuesString);
              InsertRowInDB(headers, rowValues);
              System.out.println("");
            }
        }
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    return "success";
}

public void InsertRowInDB(List<String> headers, List<String> values) throws SQLException, ClassNotFoundException {

    Class.forName("org.postgresql.Driver");
    System.out.println("connected");
    Connection conn = DriverManager.getConnection("jdbc:postgresql:postgres", "postgres", "sridevi");

    String insertColumns = "";
    String preparedStatementClause = "";
    for(int i = 0; i < headers.size(); i++){
      String fieldValue = headers[i];
      insertColumns += fieldValue;
      preparedStatementClause += "?";
      if(i <= (headers.size() - 1)){
        insertColumns += ", ";
        preparedStatementClause += ", ";
      }
    }


    String query = String.format( "Insert into excel(%s) values(%s)" , insertColumns, preparedStatementClause);
    PreparedStatement ps = conn.prepareStatement(query);
    for(int i = 0; i < values.size(); i++){
      ps.setString(i, values[i]);
    }
    ps.executeUpdate();
    System.out.println("Values Inserted Successfully");
}

However, for clarity you may consider using named parameters. They aren't available by default in JDBC. Check this thread for some details.

I would recommend getting familiar with Apache Commons DBUtils specifically and Apache Commons libraries generally.