upload the excel data into the database using jsp/servlet

Question

1)How to develop an software which transfer all the data in excel to oracle database?

2) Cells in the excel sheet may vary according to the input given by the user so we have to read the excel dynamically and create a staging table according to the column values?

kindly suggest me an idea how to do it and if possible suggest me a sample link

thanks in advance

Read the filename using the browse button

             fileName = new File(item.getName()).getName();
                    String filePath = uploadPath + File.separator + fileName;
                    //System.out.println("File Name : "+fileName);
                    //System.out.println("File Path : "+filePath);
                    File storeFile = new File(filePath);
                    // saves the file on disk
                    if(fileName != null && fileName.length()>0){
                        System.out.println("Store File : "+fileName);
                        item.write(storeFile);
                        fileInp=item.getInputStream();

Reading the given excel file

        System.out.println("Started reading Excel File");
        String[] title = new String[6];
        String[] val = new String[6];

        workbook = new HSSFWorkbook(fileInp);

        Sheet worksheet = workbook.getSheetAt(0);
        rowCount = worksheet.getLastRowNum();
        Row row = worksheet.getRow(0);
        Cell col1 = row.getCell((short) 0);
        title[0] = col1.getStringCellValue();
        Cell col2 = row.getCell((short) 1);
        title[1] = col2.getStringCellValue();
        Cell col3 = row.getCell((short) 2);
        title[2] = col3.getStringCellValue();
        Cell col4 = row.getCell((short) 3);
        title[3] = col4.getStringCellValue();
        Cell col5 = row.getCell((short) 4);
        title[4] = col5.getStringCellValue();
        Cell col6 = row.getCell((short) 5);
        title[5] = col6.getStringCellValue();

        for (int i = 1; i <= rowCount; i++) {

            row = worksheet.getRow(i);
            Cell col;
            for (int j = 0; j < 6; j++) {
                col = row.getCell((short) j);
                if (col != null) {
                    col.setCellType(HSSFCell.CELL_TYPE_STRING);
                    val[j] = col.getStringCellValue();
                    /*
                     * switch (col.getCellType()) { case
                     * HSSFCell.CELL_TYPE_STRING: val[j] =
                     * col.getStringCellValue(); break; case
                     * HSSFCell.CELL_TYPE_NUMERIC: val[j] =
                     * col.getNumericCellValue() + ""; break; }
                     */
                }
            }

apart from this i need to do the same process in a dynamic way where in the above case i have pre-defined the column but i need to write a code where the i should generate a table according to the no of column that is der in the database

Apache POI library is a good place to start

BretC
– BretC

2015-04-13 13:50:31 +00:00
Commented Apr 13, 2015 at 13:50 — BretC
– BretC, Commented Apr 13, 2015 at 13:50

FatherMathew · Accepted Answer · 2015-04-13 13:52:11Z

1

One possible way is to use Apache Poi to read the excel sheet row by row.

Once you get the data, you can use populate the data into the database using JDBC drivers.

answered Apr 13, 2015 at 13:52

FatherMathew

1,02013 silver badges15 bronze badges

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

Collectives™ on Stack Overflow

upload the excel data into the database using jsp/servlet

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related