Introduction

In this article, we will describe how a Java program connects to a Microsoft Excel sheet and retrieves the data from the sheet. And this connection is made with the JdbcOdbc bridge driver API provided by the SQL package in Java. You have to perform some steps to make the connection with an Excel sheet.
Step 1: First create the workbook (Excel sheet); if your Excel sheet is already created then there is no need to create it again.
img01.jpg
Step 2: To start, we need to setup the local ODBC connection. So select Administrative Tools in Control Panel.
img02.jpg
Step 3: Select Data Source (ODBC) option.
img3.jpg
Step 5: Select System DSN and click on the Add tab.
img4.jpg
Step 6: Select the driver named as Driver do Microsoft Excel(*.xls) from the list.
img5.jpg
Step 7: Give the data source name and select your workbook.
img6.jpg
Step 8: Click on the select workbook tab and then click ok.
img7.jpg
So now your DSN is created.
Java code
  1. import java.sql.*;
  2. public class ExcelConnection
  3. {
  4. public static void main(String[] args)
  5. {
  6. try
  7. {
  8. Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
  9. Connection con = DriverManager.getConnection("jdbc:odbc:abhishek");
  10. Statement st = con.createStatement();
  11. ResultSet rs = st.executeQuery("Select * from [Sheet1$]");
  12. ResultSetMetaData rsmd = rs.getMetaData();
  13. int numberOfColumns = rsmd.getColumnCount();
  14. while (rs.next())
  15. {
  16. for (int i = 1; i <= numberOfColumns; i++)
  17. {
  18. if (i > 1)
  19. System.out.print(", ");
  20. String columnValue = rs.getString(i);
  21. System.out.print(columnValue);
  22. }
  23. System.out.println("");
  24. }
  25. st.close();
  26. con.close();
  27. } catch (Exception ex) {
  28. System.err.print("Exception: ");
  29. System.err.println(ex.getMessage());
  30. }
  31. }
  32. }
OUTPUT
img8.jpg
Resources