0 Replies Latest reply: Jan 23, 2015 2:06 AM by 1001916 RSS

    import data ftom excel to database using JDBC

    1001916

      Hi,
      i am trying to make a program to read data from couples of excel files and store them in a database. I am using APACHE POI to read the excel and JDBC for the connection to the base.
      The code below is for reading the excel files.

      import java.io.FileInputStream;
      import java.io.IOException;
      import java.util.ArrayList;
      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.poi.ss.usermodel.Cell;

      public class readexcel{

      public static void main(String[] args) throws Exception {
      //
      // An excel file name. You can create a file name with a full
      // path information.
      //
      String filename = "C:\\Users\\Efi\\Documents\\test5.xls";

      //
      // Create an ArrayList to store the data read from excel sheet.
      //
      List sheetData = new ArrayList();
      FileInputStream fis = null;
      try {
      //
      // Create a FileInputStream that will be use to read the
      // excel file.
      //
      fis = new FileInputStream(filename);

      //
      // Create an excel workbook from the file system.
      //
      HSSFWorkbook workbook = new HSSFWorkbook(fis);
      //
      // Get the first sheet on the workbook.
      //
      HSSFSheet sheet = workbook.getSheetAt(0);

      //
      // When we have a sheet object in hand we can iterator on
      // each sheet's rows and on each row's cells. We store the
      // data read on an ArrayList so that we can printed the
      // content of the excel to the console.
      //
      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);
      }

      private static void showExcelData(List sheetData) {
      //
      // Iterates the data and print it out to the console.
      //
      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(", ");
      }
      }
      System.out.println("");
      }
      }
      }

      Now i have made a code to store data in database.
      import java.io.*;
      import java.sql.DriverManager;
      import java.sql.SQLException;
      import java.sql.Statement;

      import org.apache.poi.hssf.usermodel.HSSFCell;
      import org.apache.poi.hssf.usermodel.HSSFRow;
      import org.apache.poi.hssf.usermodel.HSSFWorkbook;
      import org.apache.poi.hssf.usermodel.HSSFSheet;
      import org.apache.poi.poifs.filesystem.POIFSFileSystem;
      import org.apache.poi.ss.usermodel.*;
      import java.util.*;
      import java.sql.*;
      import com.mysql.jdbc.Connection;
      import com.mysql.jdbc.PreparedStatement;

      public class excel2DB { 

      public static void main(String[] args) throws Exception{ 
      try {
      Class.forName ("oracle.jdbc.OracleDriver");
      Connection con = (Connection)

      DriverManager.getConnection("jdbc:mysql://localhost:3306", "root", "root");

      Statement stmt = con.createStatement();

      try
      {
      System.out.println( "Create a new database..." );
      stmt.executeUpdate( "CREATE DATABASE tragik;" );
      }
      catch( SQLException e )
      {
      System.out.println( "SQLException: " + e.getMessage() );
      System.out.println( "SQLState: " + e.getSQLState() );
      System.out.println( "VendorError: " + e.getErrorCode() );
      }

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

      try
      {
      String table = "CREATE TABLE tragiko( "
      + "id INTEGER NOT NULL PRIMARY KEY,"
      + "Name Varchar(20),"
      + "Salary double (50)";
      System.out.println( "Create a new table in the new database..." );
      stmt.executeUpdate( table );
      }
      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();
      }
      }

      PreparedStatement sql_statement = null;
      String jdbc_insert_sql = "INSERT INTO XLS_POI"
      + "(ID, Name, Salary) VALUES"
      + "(?,?,?)";
      sql_statement = (PreparedStatement) con.prepareStatement(jdbc_insert_sql);
      FileInputStream input = new FileInputStream
      ("C:\\Users\\Efi\\Documents\\test5.xls");

      POIFSFileSystem fs = new POIFSFileSystem (input);
      HSSFWorkbook wb = new HSSFWorkbook(fs);
      HSSFSheet sheet = wb.getSheetAt(0);
      Iterator rows = sheet.rowIterator();
      for(int i=1; i<=sheet.getLastRowNum(); i++){
      HSSFRow HSSFRow = sheet.getRow(i);
      HSSFRow row;
      int id = (int) row.getCell(0).getNumericCellValue();
      String name = row.getCell(1).getStringCellValue();
      String salary = row.getCell(2).getStringCellValue();
      String sql = "INSERT INTO tablename VALUES('"+id+"','"+name+"','"+salary+"')";
      PreparedStatement pstm = (PreparedStatement)con.prepareStatement(sql);
      System.out.println("Import rows "+i);
      }
      while(rows.hasNext()) {       
      HSSFRow = (HSSFRow) rows.next();
      Iterator cells = row.cellIterator();
      while(cells.hasNext()) {
      HSSFCell cell = (HSSFCell) cells.next();
      switch(cell.getCellType()) {
      case Cell.CELL_TYPE_STRING: //handle string columns
      sql_statement.setString(1,
      cell.getStringCellValue());
      break;
      case Cell.CELL_TYPE_NUMERIC: //handle double data

      sql_statement.setDouble(2,cell.getNumericCellValue() );
      break;
      }
      }
      }

      sql_statement.executeUpdate();
      con.commit();
      con.close();
      input.close();
      System.out.println("Success import excel to mysql table");

      }catch (ClassNotFoundException e){
      System.out.println (e);
      }catch (SQLException ex){
      System.out.println(ex);
      }catch (IOException ioe){
      System.out.println(ioe);
      }

      }

      }
      But i have many mistakes.
      I would like the code to make one new database and the tables that will contain to have the names of the excel file name.
      How i would do this? Could anyone help me?