Working With Microsoft Excel Using JAVA Program

I came across the problem of reading from an excel sheet while competing for MUPHORIA organized by Mu Sigma Inc. . I used following procedure to do so. Although this jdbc code reads data efficiently, it has a limitation. The ResultSet object can read data upto 256 fields irrespective of number of rows as I experienced .

Follow these steps :

Step 1: create an excel sheet, rename it as per your choice( I named it student ) and save it at any location of your choice.

Step 2:  Now create a DSN (Data Source Name) for the concerned excel sheet.


Start->Control Panel->Administrative Tools->Data Sources (ODBC)>”System Dsn” tab->

Click on Add Button–>Select “Microsoft Excel Driver (*.xls,*.xlsx,*.xlsm,*.xlsb)”–>
Enter "Data Source Name" >finish

Step 3: Now compile following jdbc code. (change the name "vikku" in line 13 with your Data Source Name and replace "student" in line 20 with your own excel table name.

 1 //ConnTest.java
 2 
 3 /**
 4  *
 5  * @author VIK
 6  */
 7 import java.sql.*;
 8 
 9 public class ConnTest {
10 
11     public static void main(String... args) throws Exception {
12         Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
13         Connection con = DriverManager.getConnection("jdbc:odbc:vikku", "", "");
14         if (con == null) {
15             System.out.println("Access denied...");
16         } else {
17             System.out.println("Connection Successful...");
18         }
19         Statement st = con.createStatement();
20         ResultSet rs = st.executeQuery("select * from [student$]");
21         ResultSetMetaData rsmd = rs.getMetaData();
22         int colcount = rsmd.getColumnCount();           
23         while (rs.next()) {
24             System.out.println("");
25             for (int i = 1; i < colcount; i++) {
26                 System.out.print("\t" + rs.getString(i));
27             }
28         }
29         rs.close();
30         st.close();
31         con.close();
32     }
33 }
34 

I didn't use the above jdbc code for reading data from excel. I saved the document as .csv( comma delimited) and then read that CSV file which I will explain in another post.