Read Excel values and store in to Database

[Rajendra Prakash]

The following code will read Excel sheet and print it to console, i want to read excel values and store in to Database.
Can you give me logic to read cells and store in to DB

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.Iterator;
import java.util.Vector;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;


public class ReadExcelDemo{

        public static void main(String[] args) {

             //   String fileName = "C:\\excelFile.xls";
        	//	String fileName ="C:/cyril/For Rajkumar//expense.xls";
        	String fileName ="d:/Ram.xls";
                Vector dataHolder = ReadCSV(fileName);
                printCellDataToConsole(dataHolder);
        }

        public static Vector ReadCSV(String fileName) {
                Vector cellVectorHolder = new Vector();

                try {
                        FileInputStream myInput = new FileInputStream(fileName);
                       // System.out.println("Input:"+myInput);
                        Workbook wb = WorkbookFactory.create(myInput);

                        Sheet sheet = wb.getSheetAt(0);

                        Iterator rowIter = sheet.rowIterator();

                        while (rowIter.hasNext()) {
                                Row myRow = (Row) rowIter.next();
                                Iterator cellIter = myRow.cellIterator();
                                Vector cellStoreVector = new Vector();
                                while (cellIter.hasNext()) {
                                        Cell myCell = (Cell) cellIter.next();
                                        cellStoreVector.addElement(myCell);
                                }
                                cellVectorHolder.addElement(cellStoreVector);
                        }
                } catch (Exception e) {
                        e.printStackTrace();
                }
                return cellVectorHolder;
        }

        private static void printCellDataToConsole(Vector dataHolder) {

                for (int i = 0; i < dataHolder.size(); i++) {
                        Vector cellStoreVector = (Vector) dataHolder.elementAt(i);
                        for (int j = 0; j < cellStoreVector.size(); j++) {
                                Cell myCell = (Cell) cellStoreVector.elementAt(j);
                                String stringCellValue = myCell.toString();
                                System.out.print(stringCellValue + "\t");
                        }
                        System.out.println();
                }
                
         try{
    		Class.forName("com.mysql.jdbc.Driver").newInstance();
    		Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root", "root");
    		Statement stat=con.createStatement();
    		//System.out.println("username:"+username);
    		//System.out.println("password :"+password);
    		
    		// int k=stat.executeUpdate("insert into deletetable values('"+uname+"','"+pwd+"')");
    		
    		//	System.out.println("Data is inserted");
    		stat.close();
    		con.close();
    			}
         catch(Exception e){}   
        }
}

[Wouter Oet]

Rajendra Prakash wrote:Can you give me logic to read cells and store in to DB

No it doesn't work that way. What have you tried? What went wrong? What happened and what did you expect to happen?

And moving to Java in General.

[Rajendra Prakash]

From Excel sheet i read all the fields as string . In that date field is there , i am getting "dd/mm/yyyy" format.
But i want to convert this to "yyyy/mm/dd". i have tried with SimpleDateFormat class. But i could not get.
can you help me

[amit punekar]

Hi,
Why did SimpleDateFormate not work? Did it throw any exception or did not convert the date in the format you needed.
Let us know.

regds,
Amit

[Rajendra Prakash]

with the following code i can read multiple excel files in a single workbook and store in to database. Now
i want to read multiple workbooks and store in to db. can you give me any logic for this.

package excel;


import java.io.*; 
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.CallableStatement;
import java.util.Locale;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.read.biff.BiffException;
import jxl.DateCell;
import java.util.Date; 
import jxl.*; 
import java.text.*;



public class ReadXLSheet1 {

	public void first(String filePath) {
		FileInputStream fs = null;
		try {
			fs = new FileInputStream(new File(filePath));
			contentReading(fs);
		} catch (IOException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				fs.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	//Returns the Headings used inside the excel sheet
	public void getHeadingFromXlsFile(Sheet sheet) {
		int columnCount = sheet.getColumns();
		for (int i = 0; i < columnCount; i++) {
			System.out.println(sheet.getCell(i, 0).getContents());
		}
	}

	public void contentReading(InputStream fileInputStream)  throws Exception {
		WorkbookSettings ws = null;
		Workbook workbook = null;
		Sheet s = null;
		Cell rowData[]=null;
		int rowCount = '0';
		int columnCount = '0';
		DateCell dc = null;
		int totalSheet = 0;

		String driver = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://localhost/test";
		String username = "root";
		String password = "root";

		try {
			Class.forName(driver);
			Connection con = (Connection) DriverManager.getConnection(url, username, password);
			//CallableStatement pstmt = null;			
			ws = new WorkbookSettings();
			ws.setLocale(new Locale("en", "EN"));
			workbook = Workbook.getWorkbook(fileInputStream, ws);

			totalSheet = workbook.getNumberOfSheets();
			if(totalSheet > 0) {
				System.out.println("Total Sheet Found:" + totalSheet);
				String sheetName="";
				for(int jj=0;jj<totalSheet ;jj++) {
					sheetName=workbook.getSheet(jj).getName();
					System.out.println("Sheet Name:" + sheetName);

					s = workbook.getSheet(jj);

					//Reading Individual Cell
					//getHeadingFromXlsFile(s);

					System.out.println("Total Rows inside Sheet:" + s.getRows());
					rowCount = s.getRows();

					System.out.println("Total Column inside Sheet:" + s.getColumns());
					columnCount = s.getColumns();

					//Reading Individual Row Content
					for (int i = 1; i < rowCount; i++) {
						//Get Individual Row
						rowData = s.getRow(i);						
						if (rowData[0].getContents().length() != 0) { // the first date column must not null
							int j=0;
							String empId="";
							String empName="";
							String empDate="";
							String client="";
							String project="";
							String task="";
							String jira="";
							String taskDesc="";
							String hrsSpent="";
							float Hours_Spent=0;
							String billHrs="";
							float Bill_Hrs=0;
							String assignBy="";
							String status="";
							String targetDate="1990-01-01";
							String remarks="";
							Date datec2 = null; 



							for ( j = 0; j < columnCount; j++) {					
								try{
									switch (j) {
									case 0:
										empId= rowData[j].getContents();
										System.out.println(empId);
										break;
									case 1:
										empName=rowData[j].getContents();									
										break;
									case 2:
										if(rowData[j].getType()==CellType.DATE)
										{
											dc = (DateCell) rowData[j]; 
											datec2 = dc.getDate(); 
											//System.out.println("datec2="+datec2);
											SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
											empDate=sdf.format(datec2);
											//System.out.println("date... ="+empDate);
										}
										break;
									case 3:
										client=rowData[j].getContents();									
										break;
									case 4:
										project=rowData[j].getContents();									
										break;
									case 5:
										task=rowData[j].getContents();									
										break;
									case 6:
										jira=rowData[j].getContents();									
										break;
									case 7:
										taskDesc=rowData[j].getContents();									
										break;									
									case 8:
										hrsSpent=rowData[j].getContents();									
										if(hrsSpent.trim().length()!=0)
										{
											Hours_Spent=Float.parseFloat(hrsSpent);
										}
										break;
									case 9:
										billHrs=rowData[j].getContents();								
										if(billHrs.trim().length()!=0)
										{
											Bill_Hrs=Float.parseFloat(billHrs);									
										}
										break;
									case 10:
										assignBy=rowData[j].getContents();									
										break;
									case 11:
										status=rowData[j].getContents().trim();									
										break;
									case 12:
										/*targetDate=rowData[j].getContents().trim();	*/
										if(rowData[j].getType()==CellType.DATE)
										{
											dc = (DateCell) rowData[j]; 
											datec2 = dc.getDate(); 
											//System.out.println("datec2="+datec2);
											SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
											targetDate=sdf.format(datec2);
											//System.out.println("date... ="+targetDate);
										}
										break;
									case 13:
										remarks=rowData[j].getContents().trim();									
										break;			
									default:
										break;
									}
								}catch (Exception e) {
									System.out.print(e);
								}
							}
							//System.out.println("Employee Id:" +empId);
							/*System.out.println("Employee Name:" +empName);
						System.out.println("empDate:" +empDate);
						System.out.println("client:" +client);
						System.out.println("project:" +project);
						System.out.println("task:" +task);
						System.out.println("jira:" +jira);
						System.out.println("taskDesc:" +taskDesc);
						System.out.println("hrsSpent:" +Hours_Spent);
						System.out.println("billHrs:" +Bill_Hrs);
						System.out.println("assignBy:" +assignBy);
						System.out.println("status:" +status);
						System.out.println("targetDate:" +targetDate);
						System.out.println("remarks:" +remarks);*/							
							//System.out.println();

							Statement st = con.createStatement();
					        ResultSet res = st.executeQuery("SELECT ROW_SNO FROM  sap_employee_ts");
					        				       
					        String str="";
					        while (res.next()) {
					                str = res.getString("ROW_SNO");
					         // System.out.println(str);
					        }
						try{	
							String query = "insert into sap_employee_ts values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
							PreparedStatement pstmt=con.prepareStatement(query);
						
							pstmt.setString(1, empId); // set input parameter 1
							pstmt.setString(2, empName); // set input parameter 2	
							pstmt.setString(3, empDate);
							pstmt.setString(4, client);
							pstmt.setString(5, project);
							pstmt.setString(6, task);
							pstmt.setString(7, jira);
							pstmt.setString(8, taskDesc);
							pstmt.setFloat(9, Hours_Spent);								
							pstmt.setFloat(10, Bill_Hrs);
							pstmt.setString(11, assignBy);
							pstmt.setString(12, status);
							pstmt.setString(13, targetDate);
							pstmt.setString(14, remarks);
							
							pstmt.executeUpdate(); // execute insert statement\
							
						}catch(Exception e){
							System.out.println("Record " + empId + "  Already Exists : " );
							
						}

						}		
						
					}
				//	pstmt.close();
				}
			}
			workbook.close();			
		} catch (IOException e) {
			e.printStackTrace();
		} catch (BiffException e) {
			e.printStackTrace();
		}
		catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) {
		try {
			ReadXLSheet1 xlReader = new ReadXLSheet1();
			xlReader.first("D:/Employee_SAP.xls");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
} 

[Ulf Dittmer]

Now i want to read multiple workbooks and store in to db. can you give me any logic for this.

There is a method that reads one file and stores something to the database - it sounds as if calling that method multiple times with different files would do the trick. Have you tried that? If so, what was the result? If not, tell us where you are stuck making progress.

[Rajendra Prakash]

No am not clear what you say. consider two folders , each consists one workbook . i want to read both workbooks and store in to db

[Ulf Dittmer]

Well, the "first" method reads one file and stores it to the DB, no? What is the problem with calling xlreader.first(..) repeatedly with different filenames?

[Rajendra Prakash]

yes , it is working fine. Now user can select workbook dynamically means , what i can do.

[Ulf Dittmer]

I don't follow - what difference does it make whether the user selects the file, or if the filename is hardcoded? The Excel/DB part would be the same, no?

If you're asking how a user can choose a file, start here: http://download.oracle.com/javase/tutorial/uiswing/components/filechooser.html

If you're asking something else, TellTheDetails. Your last two posts were very short, so it's hard to know what you have, and what problems you're encountering.

[Rajendra Prakash]

consider i have a folder prakash,inside that i would like to find all excel files . Once i found all excel files i have read the same and store in to db

[Ulf Dittmer]

OK, that's not hard. What do you have so far, and where are you stuck making progress? Are you familiar with the java.io package, particularly the File class and its methods? If not, that's where you should start.

[Rajendra Prakash]

with the following code i can read multiple excel files. but now i want to read multiple excel files from single folder in
a single line. i dont want to call "first" method again and again. which means i want call excel file dynamically

public static void main(String[] args) {
		try {
			ReadXLSheet1 xlReader = new ReadXLSheet1();
			xlReader.first("D:/Employee_SAP.xls");
			xlReader.first("D:/Employee1/Employee_SAP_Test1.xls");
			xlReader.first("D:/Employee2/Employee_SAP_Test2.xls");
			xlReader.first("D:/Employee3/Employee_SAP_Test3.xls");
			xlReader.first("D:/Employee4/Employee_SAP_Test4.xls");
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

[Ulf Dittmer]

but now i want to read multiple excel files from single folder in a single line. i dont want to call "first" method again and again. which means i want call excel file dynamically.

Create a method that takes the name of the directory as parameter, and in that method iterate through all files in that directory. For each xls file you'd then call the first method.

[Rajendra Prakash]

consider i have some 3 or 4 excel files.I dont know that files names , how can i read all files one by one

[Ulf Dittmer]

The java.io.File class has methods for enumerating all files in a directory. You can then check their names in a loop, and only process those with appropriate file names (like with an "xls" extension).