This tutorial will show you how to connect to and read data from an Excel spreadsheet using JDBC.
To start, we need to setup the local ODBC connection.
Navigate to your computers Control Panel and locate the Administrative Tools.
Once in the Administrative Tools section, locate Data Sources (ODBC)
The ODBC Data Source Administor menu will open
Select the System DSN tab and click Add
Find Driver do Microsoft Excel(*.xls) from the list and click Finish
Give the Data Source Name & Description
Next, click Select Workbook and locate the spreadsheet you wish to use
In this case, we are using worcester.xls. Select it and click OK.
Click OK again to exit the setup. The ODBC connection is now complete.
Now that the ODBC connection is setup, its time for the Java code.
Notice the bold worcester. This part of the code needs to match your ODBC Data Source Name.import java.sql.*; public class ExcelODBC { public static void main(String[] args) { try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Connection con = DriverManager.getConnection("jdbc:odbc:[B]worcester[/B]"); 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()); } } }
This part of the code allows you to query the excel spreadsheet with SQL. This is a simple SQL query to select everything from Sheet1 in the spreadsheet.ResultSet rs = st.executeQuery("Select * from [Sheet1$]");
If you are unfamiliar with SQL commands then there is a quick tutorial here:
A Gentle Introduction to SQL
Thats it! You can now connect to your Excel Spreadsheet and print its content to the console.