CODEWITHSUNDEEP
javadatabase
SandyKrish
SandyKrish

Reputation: 232

How can i create a single database table in java data from 4 excel sheets?

In my project I have uploaded the excel sheets and I retrieve by using java, after receiving excel files i just want to create a database table. These excel sheets contains mark list (each excel sheet contain single subject mark list) like student name, internal,external,result. if it is one excel sheet then i can easily create a table in db. But i have to create a table from 4 sheets.

i just tried to list the each excel files using for loop and get the data from one by one but its not my solution.

STUDENT NAME    CIA  ESE      TOTAL

 AJAY G         46   31         77
 AJITH V        41   27         68
AJITH KUMAR     40   26         66

And My Java code is here : 

 String dirs[] = file.list();
        for(String i:dirs) {
            // here i can get the file one by one 
            fis = new FileInputStream(fullpath+"/"+i);
            wb = WorkbookFactory.create(fis);
            System.out.println(wb.getNumberOfSheets());
            sh = wb.getSheet("Sheet1");
            int noOfRows = sh.getLastRowNum();
            int noOfCols = sh.getRow(7).getLastCellNum();
            System.out.println("Rows : "+ noOfRows);
            System.out.println("Cols : "+ noOfCols);
        }

The above is my excel sheet data and all sheets like the same with different subject (Subject name is the file name so i can take it).

What I expect is to create a single table in db like :

Table name : StudentResult

Stuname | Subject1 | Sub1CIA | Sub1ESE | Subject2 | Sub2CIA | Sub2ESE ...

and so on (get remaining excel sheet column like this.)

Upvotes: 0

Views: 95

Answers (1)

Hari Prasad
Hari Prasad

Reputation: 304

Assuming each of the sheets contain the column heading, collect the column headings from all the sheets and make a unique set of them. Then you can use this set to create the database table dynamically executing a DDL using JDBC.

If you want to create the table after the for loop, then declare a Set variable above for- loop and keep adding the column headings from each Sheet to it. Once the control is out of for loop you will have the set with column headings. Here is the pseudo code for this 

    // Declare a set here
    Set<String> dbColumns = new HashSet<String>();

    String dirs[] = file.list();
    for(String i:dirs) {

        // For each sheet
        // Get the heading row ( may be col 1 depending on the work sheet)

        // Get the cells from this row

        // Add the values to the dbColumns set
    }

    // Now dbColumns contains the list for columns to be created

    // Iterate through this set and form the DDL Statement

    // Execute the DDL using jdbc

Upvotes: 1

Related Questions