Wednesday, 19 September 2012

Reading Data from excel file

How to read data from Excel file using Java

 

 

import java.io.*;
import java.sql.*;
public class ExcelReadTest{
public static void main(String[] args){ 
 Connection connection = null;
try{
 Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
 Connection con = DriverManager.getConnection( "jdbc:odbc:exceltest" );
 Statement st = con.createStatement();
 ResultSet rs = st.executeQuery( "Select * from [Sheet1$]" );
 
 ResultSetMetaData rsmd = rs.getMetaData();
 int numberOfColumns = rsmd.getColumnCount();
 
 
 while (rs.next()) {
 for (int i = 1; i <= numberOfColumns; i++) {
 if (i > 1) System.out.print(", ");
 String columnValue = rs.getString(i);
 System.out.print(columnValue);
 } 
 
System.out.println(""); 
}
 
st.close();
con.close();
}
catch(Exception ex) {
System.err.print("Exception: ");
System.err.println(ex.getMessage());
}
}
}
 
 

OR

WorkbookSettings ws = new WorkbookSettings();   

ws.setLocale(new Locale("en","EN"));   

Workbook workbook = Workbook.getWorkbook(file);              

Sheet s = workbook.getSheet(0);         

LabelCell lcdate = s.findLabelCell("Date");          

LabelCell lcitem = s.findLabelCell("Item");                                  

LabelCell lcamount = s.findLabelCell("Amount");                                 

LabelCell lctype = s.findLabelCell("Type");                 

int columns = s.getColumns();          

   int rows = s.getRows();         

      System.out.println("col -->>> "+columns+"  rows -->>> "+rows);  


To print the data


String data;     

  for(int col = 0;col < columns;col++)         {             for(int row = 0;row < rows;row++)             {                data = s.getCell(col, row).getContents();   

       // Code to display ....   

       }  

 

 

(OR)

 

 

Navigate to this url's

http://poi.apache.org/index.html 

 http://www.coderanch.com/t/465901/JDBC/databases/insert-data-excel-file-java

http://www.ibm.com/developerworks/library/x-jxmlexl/index.html 

http://www.avajava.com/tutorials/lessons/how-do-i-read-from-an-excel-file-using-poi.html?page=1 

http://mrbool.com/reading-excel-file-with-java/24562 

4 comments:

  1. I simply want to say I’m very new to blogs and actually loved you’re blog site. Almost certainly I’m going to bookmark your blog post . You absolutely come with great well written articles. Thanks a lot for sharing your blog.

    Online Training in Chennai

    ReplyDelete
  2. Wonderful blog.. Thanks for sharing informative blog.. its very useful to me..

    iOS Training in Chennai

    ReplyDelete