Data from Excel file storing in a database

[ef_mag]

Hi,
i have plenty of excel files i have to read them and store the data in a database. I am using eclipse as editor and mySQL. I am using APACHE POI to read the excel files and JDBC for the connection. The excel files have the structure as the above example:

1. ID NAME SALARY STREET
2. 321 TIM 1254 14 avenue
3. 121 PAUL 1265 28h oktovriou
4. 432 NICK 4521 papaflessa

I have off course plenty of such files which contains many more rows and columns. The purpose of my program is to read the data, create table named as the name of the excel file and the fields of each table to be the first rows of the excel file. Afterwards the values will be the rest data of the excel file.For this example specifically the table will take the name test5 and the fields of the table would be ID,NAME,SALARY,STREET. The values for the tables would be the rest of the contents.

The code below is to read the excel file and afterwards i am trying to create table as i described previous!

import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class excel {

    @SuppressWarnings({ "unchecked", "unchecked" })
    static void main (String[] args) throws Exception {

        String filename = "C:\\Users\\Efi\\Documents\\test5.xls";

        List sheetData = new ArrayList();
        FileInputStream fis = null;
        try {
            fis = new FileInputStream(filename);
            HSSFWorkbook workbook = new HSSFWorkbook(fis);
            HSSFSheet sheet = workbook.getSheetAt(0);

            Iterator rows = sheet.rowIterator();
            while (rows.hasNext()) {
                HSSFRow row = (HSSFRow) rows.next();
                Iterator cells = row.cellIterator();

                List data = new ArrayList();
                   while (cells.hasNext()) {
                   HSSFCell cell = (HSSFCell) cells.next();
                   data.add(cell);
                   }
                   sheetData.add(data);
            }

                   } catch (IOException e) {
                   e.printStackTrace();
                   } finally {
                   if (fis != null) {
                   fis.close();
                   }
                   }

    showExcelData(sheetData);


    @SuppressWarnings("unused")
    HashMap<String, String> tableFields = new HashMap();
          for (int i=0; i<sheetData.size();i++){
            List list = (List) sheetData.get(i);
              for (int j=0; j<list.size(); j++){
                Cell cell = (Cell) list.get(j);
                if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    System.out.print(cell.getNumericCellValue());
                    } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    System.out.print(cell.getRichStringCellValue());
                    } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                    System.out.print(cell.getBooleanCellValue());
                    }
                    if (j < list.size() - 1) {
                    System.out.print(", ");
                    }}}
                    }

    private static void showExcelData(List sheetData) {

    }

    @SuppressWarnings("unchecked")
    private HashMap parseExcelData (List sheetData){
        HashMap<String,Integer> tableFields = new HashMap();
        List list = (List) sheetData.get(0);
        for (int j=0; j<list.size(); j++){
            Cell cell=(Cell) list.get(j);
            tableFields.put(cell.getStringCellValue(),cell.getCellType());
    }

        return tableFields; 
    }

    @SuppressWarnings({ "unchecked", "unchecked", "unchecked", "unchecked" })
    private String getCreateTable(String tablename, HashMap<String, Integer> tableFields){
        Iterator iter = tableFields.keySet().iterator();
        String str="";
        String[] allFields = new String[tableFields.size()];
        int i = 0;
        while (iter.hasNext()){
            String fieldName = (String) iter.next();
            Integer fieldType = (Integer) tableFields.get(fieldName);

            switch (fieldType){
            case Cell.CELL_TYPE_NUMERIC:
                str=fieldName + "INTEGER";
                break;
            case Cell.CELL_TYPE_STRING:
                str= fieldName + "VARCHAR(255)";
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                str=fieldName + "INTEGER";
                break;
            }
            allFields[i++]= str;
        }


        {
            try
            {
            Class.forName( "com.mysql.jdbc.Driver" ).newInstance();

            Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/kainourgia","root", "root");

            Statement  stmt = con.createStatement();



            try
            {
            System.out.println( "Use the new database..." );
            stmt.executeUpdate( "USE kainourgia;" );
            }
            catch( SQLException e )
            {
            System.out.println( "SQLException: " + e.getMessage() );
            System.out.println( "SQLState:     " + e.getSQLState() );
            System.out.println( "VendorError:  " + e.getErrorCode() );
            }

            try
            {
                String createTableStr = "CREATE TABLE" + createTableStr
                   + "(" + org.apache.commons.lang3.StringUtils.join(allFields,
                   ",") + ")";

                System.out.println( "Create a new table in the database" );
                stmt.executeUpdate( createTableStr );
                }
                catch( SQLException e )
                {
                System.out.println( "SQLException: " + e.getMessage() );
                System.out.println( "SQLState:     " + e.getSQLState() );
                System.out.println( "VendorError:  " + e.getErrorCode() );
                }
                }
                catch( Exception e )
                {
                System.out.println( ((SQLException) e).getSQLState() );
                System.out.println( e.getMessage() );
                e.printStackTrace();
                }
                }
    }

I have tried to run this program but nothing happens. Could anyone help me? I describe the role of this program above.

[stultuske]

static void main (String[] args) throws Exception

have you tried by declaring your main method public?

also, it's bad design to allow your main method to throw Exceptions: you won't be able to prevent your application from crashing if something were to go wrong.

[ef_mag]

I change it but nothing comes up again..

[JamesCherrill]

If you are using Eclipse then ou could use its debugger to trace the execution of your program to see what is, and what is not, being executed. If you don't know how to do that yet, just add print statements at key points in the code so you can see what's been executed and what the values of the key variables are.