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 

10 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
  3. Whoa! I’m enjoying the template/theme of this website. It’s simple, yet effective. A lot of times it’s very hard to get that “perfect balance” between superb usability and visual appeal. I must say you’ve done a very good job with this.
    AWS Training in Chennai |Best Amazon Web Services Training in Chennai
    Learn Amazon Web Services Tutorial |AWS Tutorials For Beginners
    AWS Interview Questions And Answers
    AWS Training in Chennai | AWS Training Institute in Chennai Velachery, Tambaram, OMR

    ReplyDelete
  4. Nice blog..! I really loved reading through this article. Thanks for sharing such
    a amazing post with us and keep blogging...Well written article Thank You for Sharing with Us project management courses in chennai | pmp training class in chennai | pmp training fee | project management training certification | project management training in chennai

    ReplyDelete