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

Question

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.)

Hari Prasad · Accepted Answer · 2019-02-09 07:34:58Z

1

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

edited Feb 9, 2019 at 7:34

answered Feb 8, 2019 at 5:31

Hari Prasad

3041 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SandyKrish Over a year ago

Hi, I think you cant understant what's my problem, When my program enter in the above for loop (above code) it select single excel sheet and process to extract data in it then only it go to next sheet, here how can i store the single excel sheet data ? i want the multiple sheets data ? how can i store these data ?

Hari Prasad Over a year ago

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. I have edited my answer to include psuedo code

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related