Solved

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

Posted on 2012-12-25
9
6,215 Views
Last Modified: 2013-05-19
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

0
Comment
Question by:Rose_Taylor
  • 5
  • 4
9 Comments
 
LVL 86

Assisted Solution

by:CEHJ
CEHJ earned 500 total points
ID: 38721057
0
 

Author Comment

by:Rose_Taylor
ID: 38721255
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);
                        }
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 38721265
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?
0
 

Author Comment

by:Rose_Taylor
ID: 38722362
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.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:Rose_Taylor
ID: 38722538
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

0
 
LVL 86

Expert Comment

by:CEHJ
ID: 38723983
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'
0
 

Author Comment

by:Rose_Taylor
ID: 38725910
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...
0
 
LVL 86

Accepted Solution

by:
CEHJ earned 500 total points
ID: 38726725
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

0
 
LVL 86

Expert Comment

by:CEHJ
ID: 39178486
:)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
create a gui in perl 3 46
solarwind tftp server 2 32
Error with Java/Cache JDBC Classpath 2 21
eclipse formatting 6 43
By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now