Results 1 to 4 of 4
Like Tree2Likes
  • 1 Post By efoikonom
  • 1 Post By masijade

Thread: import data ftom excel to database using JDBC

  1. #1
    efoikonom is offline Member
    Join Date
    Apr 2013
    Posts
    10
    Rep Power
    0

    Question import data ftom excel to database using JDBC

    Hi to all,
    i am newbie in java. I have created a program to read data from excel files with APACHE POI and show them in my console. I am using Eclipse as editor. Now I an trying to import these data in database so I can store them. The purpose for that is i would like to be able to access them from different locations. I am using JDBC connection!
    Can someone help me of how I would do this? How i will connect these two? When i ran tha first code i get this: Exception in thread "main" java.lang.NullPointerException

    at NewExcel2DB.main(NewExcel2DB.java:40)
    How I will solve this?

    **Here is my code for import data from Eclipse console to DATABASE: **

    import java.io.*;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    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 NewExcel2DB {
    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");
    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=null;
    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 row = (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(); //we can execute the statement before
    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);
    }
    }
    }



    And here is my code to read excel file using APACHE POI:


    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 ExcelRead{
    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();
    }
    }
    showExelData(sheetData);
    }
    private static void showExelData(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("");
    }
    }
    }
    Thank you in advance!
    sara777 likes this.

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,086
    Rep Power
    20

    Default Re: import data ftom excel to database using JDBC

    Moved this to the proper JDBC forum (I hope).

    Please wrap code in [code] tags [/code] so it retains its formatting.

    If you get an exception you need to not only give us the exception and stack trace, but also highlight the line on which it occurs.

    A quick scan of your db code shows you are nt using the PreparedStatement correctly.
    There are no binding placeholders (these would be '?' in the query).
    You don't concatenate the variables into the query, you use '?' instead and then use the setXXX calls to set the values for a particular call of the statement. That has the advantage of handling any quotes for Strings and data conversions for things like dates.

    Apart from that, presumably one Excel row represents one table row, so you'll need to either batch the calls or do one call per row.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  3. #3
    noobplus's Avatar
    noobplus is offline Senior Member
    Join Date
    Feb 2012
    Location
    chennai,south of India(south east of asia)
    Posts
    232
    Rep Power
    3

    Default Re: import data ftom excel to database using JDBC

    Hi,

    Java Code:
    import org.apache.poi.hssf.usermodel.HSSFWorkBook;
    I'm getting class error..
    Java Code:
    The import org.apache cannot be resolved
    May I connect Java with Excel using standard java classes?

    regards
    dhilip

  4. #4
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default Re: import data ftom excel to database using JDBC

    That error means you are not including the POI jars on your classpath.

    But, other than that, on Windows systems, yes you can, using the JDBC-ODBC Bridge and, preferably, a DSNless connection url. Google those terms, along with JDBC Excel and you will be rewarded. It is NOT perfect, however, POI and HSSFWorkBook (or better XSSFWorkBook) is much better (although more involved, as well).
    noobplus likes this.

Similar Threads

  1. updating data in SQL SEVER database using JDBC
    By madhav.dubey9 in forum New To Java
    Replies: 5
    Last Post: 03-27-2013, 03:47 PM
  2. How to get data from 2nd row in Excel using JDBC
    By siddu in forum Advanced Java
    Replies: 5
    Last Post: 01-03-2013, 11:21 AM
  3. Upload excel data to access database
    By ravikumar.achi in forum New To Java
    Replies: 20
    Last Post: 01-19-2012, 11:30 AM
  4. Replies: 0
    Last Post: 03-23-2011, 03:46 AM
  5. Reading data form Excel using Jdbc (example)
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 02-13-2008, 11:23 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •