How to read Excel in java

				


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



Answer to Comments:

Hey john ur comment is right and well recieved. The reason for the first row not being printed is that the jdbc-bridge assumes the first row to be akin to column names in the database. Hence the first available row is the name of the column...which explains why the third row is printed if the first row is missing.


Hope this helps and thanks for the comment




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

can be used on the spreadsheet.


You can use the following snippet to print the column names(which is the first row of the spread sheet).

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