1 / 14

Connecting Microsoft Excel and Access via JDBC-ODBC Bridge

This guide covers how to use the JDBC-ODBC Bridge to connect Microsoft Excel and Access databases with Java. It explains the steps to set up a data source, choose the Microsoft Excel driver, and interact with Excel files using JDBC. You'll learn how to create a connection, perform SQL queries, and manage data, including aggregation, updates, and inserts in Excel sheets. Additionally, the document includes source code examples demonstrating these operations and provides troubleshooting tips for establishing connections and executing queries.

arty
Télécharger la présentation

Connecting Microsoft Excel and Access via JDBC-ODBC Bridge

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. JDBC-ODBC Bridge Database Project

  2. Contents • Microsoft Excel • Microsoft Access JDBC-ODBC Bridge

  3. Microsoft Excel JDBC-ODBC Bridge

  4. Excel File JDBC-ODBC Bridge

  5. The Data Sources Administrative Tool JDBC-ODBC Bridge

  6. Add an Excel File Data Source JDBC-ODBC Bridge

  7. Choose the Microsoft Excel Driver JDBC-ODBC Bridge

  8. Select the Workbook as a Data Source JDBC-ODBC Bridge

  9. Specifying a Name for the Data Source JDBC-ODBC Bridge

  10. Confirm the Data Source Was Established JDBC-ODBC Bridge

  11. JDBC Code import java.io.*; import java.net.*; import java.sql.*; import java.util.*; public class InteractWithExcel { public static void main(String[] args) { try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Connection conn = DriverManager.getConnection("jdbc:odbc:EXCELJDBCTest"); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("select State,Population from [Sheet1$]"); while (rs.next()) { String state = rs.getString(1); int population = rs.getInt(2); System.out.println(state + " - " + population); } rs.close(); stmt.close(); } catch (Exception e) { e.printStackTrace(); } } } JDBC-ODBC Bridge

  12. Other SQLs • Aggregation queries • Select sum(population) from [MySheetName$] • Update queries • Insert into [Sheet1$] values(‘Illinois’, 12600620); JDBC-ODBC Bridge

  13. Microsoft Access JDBC-ODBC Bridge

  14. Source Code import java.sql.*; public class Test { public static void main(String[] args) { try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); String dataSourceName = "mdbTEST"; String dbURL = "jdbc:odbc:" + dataSourceName; Connection con = DriverManager.getConnection(dbURL, "",""); Statement s = con.createStatement(); s.execute("create table TEST12345 ( column_name integer )"); s.execute("insert into TEST12345 values(1)"); s.execute("select column_name from TEST12345"); ResultSet rs = s.getResultSet(); while ( rs.next() ) System.out.println("Data from column_name: " + rs.getString(1) ); s.execute("drop table TEST12345"); s.close(); con.close(); } catch (Exception e) { e.PrintStackTrace(); } } } JDBC-ODBC Bridge

More Related