Reading Excel Sheet from Java without using any Framework

[Former Member]

Reading Excel Sheets from Java using JDBC-ODBC Bridge:

There are couples of BLOGS present in SDN, but I didn’t find this way to read & write to/from any Spread Sheet (Ex. MS Excel Sheet) using Java. This is one of
easiest ways of achieving the results (read, write & update) while working on Excel Sheets through Java and thought of sharing.

The following java code will read data from Excel Sheet.

import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.Statement; import java.util.StringTokenizer; public class ConnectExcel{ public void static main(String s[]){ Connection connection = null; Statement statement = null; String fileName = "D:/TestSheet.xls"; try{ Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" ); connection = DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=" + fileName); //connection = DriverManager.getConnection("jdbc:odbc:ConnectExcelDSN"); statement = connection.createStatement(); String query = "Select [Name] from [Sheet1$]"; //String query1 = "Select [Name] from [Sheet1$] where [Name] like ‘%M%’"; ResultSet rs = statement.executeQuery( query ); while( rs.next() ) { System.out.println( rs.getString(1) +"\n"); } rs.close(); statement.close(); }catch( Exception e ){ System.out.println ("In Catch: "+ e ); } }//eof main() }//eof class

There following is the screen shot of TestSheet.xls with the values in it.

After executing the following code, you would get a list of all the values in column Name. You can execute any normal SQL query statements to fetch values
from the Excel sheet. This is the coolest way to achieve our customized results from Excel Sheet.

What exactly required:

We need to register the Excel Sheet as Database and connect it as we do generally for other databases like Oracle, MS SQL Server. There are 2 ways to do this,
either we can create a Data Source (in Windows) or just directly specifying the Driver Name as follows:

1#
DriverManager.getConnection("jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)};DBQ=" + fileName);
filename -> it the physical/network path to the excel file.

2#
Or if we create a DSN, use the following code.

connection = DriverManager.getConnection("jdbc:odbc:ConnectExcelDSN");

ConnectExcelDSN -> it is the System DSN created in ODBC Data Source Administrator for Microsoft Excel Driver (*.xls) as Driver

Creating DNS in Data Source:




Excel Sheet as Database:

The Excel file can be considered as a database, all the Sheets (Sheet1, Sheet2, Sheet3 etc) are tables in a database. By default, the First Row of any Sheet is the
name_of_the_column. In our example Name, Emp Code & Salary are the Column Names of table Sheet1.
There are few rules to build our query string, except that we can use any type of query on spread sheets based on the user permissions to the file.

Table Name (Work Sheet Name): When writing a query string the work sheet should be enclosed in [ ] braces and followed by a ‘$’ sign.

Select * from [Sheet1$];
Column Name: When writing the columns it is advisable to enclose them in [ ] braces.

Select [Name], [Salary] from [Sheet1$];
Using Cell Ranges: To use a cell range in a query string, add a cell range. Use a colon ‘:’ between the starting and ending cell positions.

Select * from [Sheet2$A1:D5] WHERE [Dept_No] > 1001;
In order to be used effectively, you will have to know the exact data range for the cells in the worksheet.