How to insert into database reading from excel sheet using java POI ?

Rose_Taylor used Ask the Experts™

on 2012-12-25

Java

Hi,

I'm reading the value from excel sheet using java POI and need to insert into database.I have string,numeric and date values from excel sheet as well as first fields are column header.

public class SimpleExcelReadExample {
	static Connection con1 = null;
	static Connection con3 = null;
	static PreparedStatement preparedstatement = null;
	static ResultSet resultset = null;
	int j = 0;

	public static void main(String[] args) {
		
		String fileName = "D:/Excel/Report.xls";
		Cleartables.table_daily_report();
		Vector dataHolder = read(fileName);
		saveToDatabase(dataHolder);
	}

	public static Vector read(String fileName) {
		Vector cellVectorHolder = new Vector();
		try {
			FileInputStream myInput = new FileInputStream(fileName);
			POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
			HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
			HSSFSheet mySheet = myWorkBook.getSheetAt(0);
			Iterator rowIter = mySheet.rowIterator();
			while (rowIter.hasNext()) {
				HSSFRow myRow = (HSSFRow) rowIter.next();
				Iterator cellIter = myRow.cellIterator();
				Vector cellStoreVector = new Vector();
				while (cellIter.hasNext()) {
					HSSFCell myCell = (HSSFCell) cellIter.next();
					//System.out.println("read method"+myCell);
					cellStoreVector.addElement(myCell);
				}
				cellVectorHolder.addElement(cellStoreVector);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return cellVectorHolder;
	}

	private static void saveToDatabase(Vector dataHolder)
	{
		
                       for (int i=0;i<dataHolder.size(); i++)
                        {
                           Vector cellStoreVector=(Vector)dataHolder.get(i);
                           issueid = ((HSSFCell)cellStoreVector.get(0)).toString();
                               System.out.println(issueid);
                        }

Open in new window

Comment

Watch Question

Do more with

EXPERT OFFICE^® is a registered trademark of EXPERTS EXCHANGE^®

View Solutions Only

CEHJ

Verified Expert

Top Expert 2016

Commented: 2012-12-26

See http://www.java2s.com/Code/Java/Database-SQL-JDBC/InsertRecordsUsingPreparedStatement.htm

Rose_Taylor

Author

Commented: 2012-12-26

Thanks for reply.

See the below code for retrive the value from excel sheet one by one.I have column header and values like database.But here there is no option to retrive value as header.
So what happening is getting all the values from excel sheet including header.But already header in data table.

How to avoid header while reading the excel sheet ?

private static void saveToDatabase(Vector dataHolder)
{

for (int i=0;i<dataHolder.size(); i++)
{
Vector cellStoreVector=(Vector)dataHolder.get(i);
issueid = ((HSSFCell)cellStoreVector.get(0)).toString();
System.out.println(issueid);
}

CEHJ

Verified Expert

Top Expert 2016

Commented: 2012-12-26

So what happening is getting all the values from excel sheet including header

If that's the case, then only the first row is a header, so why don't you simply ignore the first row?

Rose_Taylor

Author

Commented: 2012-12-26

Above program is reading the value as columwise but i want to read rowwise ..So that i can insert the values into database one by one.

Rose_Taylor

Author

Commented: 2012-12-26

Please find the below code...Values are not inserting into database.Can you please advise.

private static void saveToDatabase(Vector dataHolder)
	{
		try
        {
		String field1= "";
		String field2= "";
		String field3= "";
		String field4= "";
		String field5= "";
		String field6= "";
		String field7= "";
		String field8= "";
		String field9= "";
		String field10= "";
		String field11= "";
		String field12= "";
		String field13= "";
		String field14= "";
                        for (int i=1;i<dataHolder.size(); i++)
                        {
                           Vector cellStoreVector=(Vector)dataHolder.get(i);
                           field1= ((HSSFCell)cellStoreVector.get(0)).toString();
                           field2=((HSSFCell)cellStoreVector.get(1)).toString();
                           field3=((HSSFCell)cellStoreVector.get(2)).toString();
                           field4=((HSSFCell)cellStoreVector.get(3)).toString();
                           field5= ((HSSFCell)cellStoreVector.get(4)).toString();
                           field6= ((HSSFCell)cellStoreVector.get(5)).toString();
                   		field7= ((HSSFCell)cellStoreVector.get(6)).toString();
                   		field8= ((HSSFCell)cellStoreVector.get(7)).toString();
                   		field9= ((HSSFCell)cellStoreVector.get(8)).toString();
                   		field10= ((HSSFCell)cellStoreVector.get(9)).toString();
                   		field11= ((HSSFCell)cellStoreVector.get(10)).toString();
                   		field12= ((HSSFCell)cellStoreVector.get(12)).toString();
                   		field13= ((HSSFCell)cellStoreVector.get(13)).toString();
                   		field14= ((HSSFCell)cellStoreVector.get(14)).toString();
                                                                                 
                       
                        	String sql2 = "INSERT INTO Daily_Report (field1,field2,field3,field4,field5,field6,field7,field8,field9,field10,field11,field12,field13,field14) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                    		preparedstatement = con3.prepareStatement(sql2);
                    		//Statement stmt = con3.createStatement();
                    		System.out.println("1");
                    		preparedstatement.setString(1, field1);
                    		preparedstatement.setString(2, field2);
                    		preparedstatement.setString(3, field3);
                    		preparedstatement.setString(4, field4);
                    		preparedstatement.setString(5, field5);
                    		preparedstatement.setString(6, field6);
                    		preparedstatement.setString(7, field7);
                    		preparedstatement.setString(8, field8);
                    		preparedstatement.setString(9, field9);
                    		preparedstatement.setString(10, field10);
                    		preparedstatement.setString(11, field11);
                    		preparedstatement.setString(12, field12);
                    		preparedstatement.setString(13, field13);
                    		preparedstatement.setString(14, field14);
                    		preparedstatement.executeUpdate(); 
        }
        }                
        catch(Exception e){}
        }

Open in new window

CEHJ

Verified Expert

Top Expert 2016

Commented: 2012-12-27

Nothing wrong with that, although of course you can get rid of that massive amount of code and replace with a few lines in a loop. Make sure you close the Statement and Connection or the insert won't 'stick'

Rose_Taylor

Author

Commented: 2012-12-27

Please see the below code.

private void printToConsole(List cellDataList) {
    for (int i = 1; i < cellDataList.size(); i++) {
        List cellTempList = (List) cellDataList.get(i);
        System.out.println(cellTempList);
    }
}

Open in new window

How to get the data to insert into database from above code.

String sql2 = "INSERT INTO Daily_Report (field1,field2,field3,field4,field5,field6,field7,field8,field9,field10,field11,field12,field13,field14) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                        preparedstatement = con3.prepareStatement(sql2);
                        //Statement stmt = con3.createStatement();
                        System.out.println("1");
                        preparedstatement.setString(1, field1);
                        preparedstatement.setString(2, field2);
                        preparedstatement.setString(3, field3);
                        preparedstatement.setString(4, field4);
                        preparedstatement.setString(5, field5);
                        preparedstatement.setString(6, field6);
                        preparedstatement.setString(7, field7);
                        preparedstatement.setString(8, field8);
                        preparedstatement.setString(9, field9);
                        preparedstatement.setString(10, field10);
                        preparedstatement.setString(11, field11);
                        preparedstatement.setString(12, field12);
                        preparedstatement.setString(13, field13);
                        preparedstatement.setString(14, field14);
                        preparedstatement.executeUpdate();

Open in new window

Please advise...

CEHJ

Verified Expert

Top Expert 2016

Commented: 2012-12-28

This is a good place to use batch updates if the db supports it:

    private void sheetToDb(List cellDataList) {
        // Other db setup code
        con3.setAutoCommit(false);

        for (int i = 1; i < cellDataList.size(); i++) {
            List currentRow = (List) cellDataList.get(i);

            for (int col = 0; col < currentRow.size(); col++) {
                preparedstatement.setString(col + 1,
                    currentRow.get(col).toString());
            }

            preparedstatement.addBatch();
        }

        preparedstatement.executeBatch();
        con3.commit();

        // CLOSE EVERYTHING IN FINALLY BLOCK
    }

Open in new window

CEHJ

Verified Expert

Top Expert 2016

Commented: 2013-05-19

Do more with

Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial

How to insert into database reading from excel sheet using java POI ?

Premium Content

Premium Content

Explore our Suite of Services