Skip to Content

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.

image

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:

image
image

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.

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Shane McCarron
    Your solution is very close to one that I have written myself for a project but I am having difficulty reading fields containing URLs. I am using an ODBC connection pointing to a local Excel file. The fields in question are showing up as NULLs when I extract them from the ResultSet. (I don’t need to see the address of the URL as the text in question is all I am concerned with)  All other data comes through to java without any issues.  Do you know if this is a limitation of java.sql or MicroSoft?  Have you ever tried this?
    (0) 
    1. Naga Devisetty Post author
      Can you please explain your issue more clearly!
      1.Wt do u mean by ‘I don’t need to see the address of the URL as the text in question is all I am concerned with’, if you don’t want to display the text of URL what do u want to display?

      2.”The fields in question are showing up as NULLs when I extract them from the ResultSet.” Wt do u mean by ‘fields in question are showing up as NULLs’?

      Well I’ve tried to add some urls to my excel sheet and try to print them, its working fine there are no issues in displaying the URLs.

      Munna_SAP

      (0) 

Leave a Reply