This discussion is archived
0 Replies Latest reply: Apr 9, 2013 1:39 AM by 1001916 RSS

import data ftom excel to database using JDBC

1001916 Newbie
Currently Being Moderated
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?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points