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!

Recommended Answers

All 12 Replies

you declare "row" to be null, but never create any instance before you try to call methods on it.

HSSFRow HSSFRow = sheet.getRow(i);
HSSFRow row=null;
int id = (int) row.getCell(0).getNumericCellValue();

My guess is what you actually want to do is the following:

HSSFRow row = sheet.getRow(i);
int id = (int) row.getCell(0).getNumericCellValue();

haven't checked the rest of your code, but that's the obvious problem you're running into here.

I made the change you told me but now I get this:

Exception in thread "main" java.lang.IllegalStateException: Cannot get a numeric value from a text cell


at NewExcel2DB.main(NewExcel2DB.java:39

then don't use the 'getNumericCellValue()' method. there's bound to be an alternative.

And how i will get the value on the database?

Could you be more specific?

click the link I've provided, there you'll find every method that class provides, including all the methods to retrieve data from it. just see which ones you can't use, and then use the one you'll be able to use. my guess: you're looking for getCellStringValue().

But the excel file tha i read has both numeric and string cells! That's why i use cases..

The content of the excel file i am reading is like the above:

ID Name Salary
12 Sam 1254.30
45 Tim 1256.00
23 Nick 8962.00

which has both numeric and string values!

ehm .. yah.
just because the value stored in a cell is 1254 doesn't make it a numerical field.
since you are, as you claim to be, a 'newbie' in Java, why do you start with this kind of applications? it would do you better to start slowly and learn the basics and how to work with documentation first.

I need help because of a project i am doing. I have deadline for this and i have to hurry..

and? have you tried with the other get..Value() method(s)?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.