This sample code was an outcome of a friend's requirement for reading an excel file dynamically. He had an EJB layer for a flight management system for read/writes to database. It so happened that the data also started coming from various departments in a spread sheet format. So either he had to import the data to his oracle database manually or re-design his EJB's for accomadating this new data input. So what i designed for him was a simple facade pattern classes run by a daemon process which makes use of the existing enterprise java beans which enabled him to treat the spreadsheet data as no different. The scope of the design is beyond this article.

So what i will illustrate in this article is a simple way of accessing spreadsheets as if they were a database. This article holds good for java running on windows-servers. The access itself is through a jdbc-odbc bridge.

okie..so here we go...

Open the odbc data administrator console and click on system dsn. select add and add the Microsoft Excel driver from the list of drivers and give a name to the dsn(say exceltest) and select the workbook.

Then all one needs is to connect through this dsn just like connecting to the database and accessing records.

Here's the sample code

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());
                        		}
                        	}
                    }

                    

                    Select column_name1,column_name2 from [Sheet1$] where column_name3 like '%bob%';
                    

                        			for (int i = 1; i <= numberOfColumns; i++) {
                        				if (i > 1) System.out.print(", ");
                        				String columnName = rsmd.getColumnName(i);
                        				System.out.print(columnName);
                        			}
                        			System.out.println("");