How to use Java to integrate Excel worksheets

If you want to integrate Excel with relational data on any platform, Java Database Connectivity support allows you to use simple, dynamic SQL to do this easily.


 

Jim Mason

While you can buy many tools that automate the process of integrating Excel worksheets and iSeries data, some -- such as iSeries Client Access -- are expensive. If you want to integrate Excel with relational data on any platform, Java Database Connectivity (JDBC) support allows you to use simple, dynamic SQL to do this easily.

Why is this a big deal for some companies? Companies often use Excel to share subsets of key data with employees, customers and vendors. Excel also provides simple way to summarize data graphically. Using Java and Excel macros, the production of Excel workbooks from operational data in any database (including iSeries) can be automated to a high degree relatively easily.

Where is this capability useful? In many places. Currently, I'm building an automated support system that will let customers report software defects or requests in an Excel workbook via e-mail to our support desk. My Java code can automatically read the e-mail with the Excel attachment, open the workbook, read the data, post the results to our support database, post any support status updates back to the workbook and automatically send that workbook back to the user via e-mail. All of that can be done quickly without any manual work from our support team. Our support database is updated directly for status by the quality control team so any request will have the current status. In fact, when you change status, the status is sent directly back to the client as an updated Excel workbook automatically.

I know of many areas this capability can help companies manage information automatically, accurately and efficiently: tracking production status, tracking sales performance, tracking delivery performance, and reviewing financial performance (against budget or whatever).

Connecting Java to an Excel workbook data source

Java applications use SQL to access any relational database, including Excel workbooks. The JDBC support mentioned earlier provides SQL access to a data source as the interface to a relational database or an Excel workbook.

The JDBC access to the Excel workbook is done using the Sun JdbcOdbc driver. This driver uses the Microsoft Excel ODBC driver to access an Excel workbook. The Microsoft Excel ODBC driver is included in current versions of Windows (Windows 2000, Windows Professional and all versions of Windows server).

The key issue when using Excel with Java SQL is understanding the MANY limitations the Excel workbook and the Microsoft ODBC driver for Excel have. Normally, you can do very complex SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, DROP TABLE, ALTER TABLE and other statements. With the Excel ODBC driver you're primarily limited to the following:

  1. Creating a new worksheet page inside a workbook
  2. Inserting data rows into the worksheet
  3. Reading data from the worksheet selectively

Our sample Java Excel application

I've created a simple Java program, TestExcelAccess.java, below that shows how to do the following:

  1. Create a new worksheet in an Excel workbook
  2. Insert new rows of data into the worksheet
  3. Read data from the worksheet

To build and run our sample application, we must do these things:

  1. Review the Microsoft Excel data source in Windows
  2. Create a sample Java application class in WebSphere Studio (or Eclipse)
  3. Test our sample application in WebSphere Studio

Review your Microsoft Excel data source
First we need to make sure we have a valid data source in Windows for Excel access. Most versions of Windows will already have this set up in the administration tools section of the Control Panel under Data Sources (ODBC). Here's a snapshot of the default configuration for my Windows XP Professional setup showing the User Data Source for the default Microsoft Excel driver. If you don't have this on the Windows system you are using, contact your Microsoft administrator.

Note: You do not have to select a specific workbook here. We can dynamically point to the workbook in the Java program.

Create a sample Java application class in WebSphere Studio (or Eclipse)
I created my TestExcelAccess.java class in Eclipse by creating a Java project and then creating a new Java class. The key methods in this test class are displayed in the chart below.

Method Purpose
Main Controls the main workflow for this application
ConnectDb Shows how to connect to a specific Excel workbook
CreateDb Shows how to create a worksheet and insert data rows
ExcelSelect Shows how to select data rows from a worksheet
CloseDb Shows how to close the worksheet

Java main method has main line workflow
The main method just calls the other methods in sequence: connectDb, createDb, excelSelect and closeDb.

 ** * main */ public static void main(String[] args) { System.out.println("\nTestExcelAccess.main started: " + new Date()); TestExcelAccess tester = new TestExcelAccess(); tester.log("connect to db"); tester.connectDb(); tester.log("\n>>create Sheet5 page in excel workbook"); tester.createDb(); tester.log("\n>> select contacts"); tester.excelSelect(); tester.log("\n>>insert a new expense for godaddy"); tester.log("close db"); tester.closeDb(); System.out.println(log.toString()); }

connectDb method
ConnectDb connects to a specific Excel workbook using the Sun JdbcOdbc driver, which is included with every Java Runtime environment free. Notice the flag that has READONLY=0. This allows me to update the workbook by inserting rows. If it were a 1, I could read from the workbook. I had to supply a URL for the workbook, a user id and a password (valid for my Windows system) to get a JDBC connection back for the workbook. I stored the connection in a connection variable for use in the other methods.

 /** * connectDb */ public void connectDb() { try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); String aUser = "myname"; String aPassword = "mypassword"; String aFile = "expenses1.xls"; String aPath = "C:\\save\\wrt\\_tips\\qwtips\\qwtip917_JavaExcelAccess\\"; String aBase = "Excel Files;DBQ="; String mode = "; READONLY=0"; url = "jdbc:odbc:" + aBase + aPath + aFile + mode; connection = java.sql.DriverManager.getConnection(url, aUser, aPassword); } catch (Exception e) {handleException("connectDb", "connection failed > ", e); }; }

createDb to create a worksheet and insert rows
The createDb method shows how to create a workbook page (here Sheet5) and then insert rows to the worksheet. I used a 2-D array to hold some data to load on the SQL row INSERT statements. Note: I created an SQL statement for CREATE TABLE and then executed and committed that. This ensures the workbook page is available before I insert rows.

I created a template for the INSERT SQL statement that used substitution variables. In the For loop for each row to insert, I set the value of each substitution variable on the INSERT statement. Then I executed the INSERT statement to add the row to the worksheet.

 /** * createDb */ public void createDb() { try { log("createDb started"); String tableName = "Sheet5"; rows[0] = rowdata1; rows[1] = rowdata2; rows[2] = rowdata3; createStmt = connection.prepareStatement("CREATE TABLE " + tableName + " (Id NUMBER, FirstName TEXT, LastName TEXT, Company TEXT, " + " Department TEXT, Title TEXT, Phone1 TEXT, Phone2 TEXT, email TEXT, " + " Address1 TEXT, Address2 TEXT, City TEXT, State TEXT, MailCode TEXT, " + " Country TEXT, Status TEXT, Comment TEXT)"); createStmt.executeUpdate(); connection.commit(); log("table " + tableName + " created in " + dbName); createStmt = connection.prepareStatement("INSERT INTO " + tableName + " (Id, FirstName, LastName, Company, " + " Department, Title, Phone1, email, Comment)" + " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"); for (int i = 0; i < 3; i++) { createStmt.setInt(1, Integer.parseInt(rows[i][0])); createStmt.setString(2, rows[i][1]); createStmt.setString(3, rows[i][2]); createStmt.setString(4, rows[i][3]); createStmt.setString(5, rows[i][4]); createStmt.setString(6, rows[i][5]); createStmt.setString(7, rows[i][6]); createStmt.setString(8, rows[i][7]); createStmt.setString(9, rows[i][8]); createStmt.execute(); connection.commit(); log("\n row: " + i + " inserted"); }; log("createDb for " + tableName + " completed.."); } catch (Exception e) {handleException("createDb", null, e); }; }

excelSelect to select rows from the worksheet
The excelSelect method below creates an SQL SELECT statement with a WHERE clause for the workbook page. The statement substitutes the value of the LastName variable into the statement before execution. Executing the SELECT statement generates an SQL result set. With JDBC support, I used the result set to do the following:

  1. Get the column names of each column retrieved and list it as a line on my report.
  2. Access the column data for each row in the result set. The column data was added as a single string to the report.

Notice, I closed the selectStmt correctly at the end, which saves JDBC resources.

 /** * excelSelect * select data from an excel worksheet * @return */ public boolean excelSelect() { String data = ""; try { String aColName; boolean metaDataDefined = false; int colCount = 0; String vLastName = "'Mason'"; String table = "[Sheet5$]"; log("Report on: " + url + "\n"); // "connect to url - create an sql statement - execute the select statement sql = "SELECT * FROM " + table + " WHERE (LastName = " + vLastName + " )"; selectStmt = ((java.sql.Statement) connection.createStatement()); java.sql.ResultSet resultSet = selectStmt.executeQuery(sql); log("\nREPORT on : " + table + " table \t\t\t" + new java.util.Date() + "\n\n"); StringBuffer s = new StringBuffer(); s.append("\n"); while (resultSet.next()) { // "access rows" if ( ! metaDataDefined ) { // write out column headings from result set metadata colCount = resultSet.getMetaData().getColumnCount(); for ( int i = 1; i <= colCount; i++ ) { s.append(resultSet.getMetaData().getColumnName(i) + "\t"); }; }; if (! metaDataDefined) log("\n" + s.toString()); s = new StringBuffer(); for ( int i = 1; i <= colCount; i++) { s.append(resultSet.getObject(i) + "\t\t"); }; log("\n" + s.toString()); metaDataDefined = true; }; selectStmt.close(); } catch (Exception e) { handleException("excelSelect", sql, e ); } return result; }

closeDb closes the workbook connection
This method checks to see if a statement was used (createStmt or selectStmt) and then closes that statement if it's still open, freeing SQL resources. Next, I commit any open changes in SQL and then close and null the connection variable, freeing the SQL resources for the connection.

 /** * closeDb */ public void closeDb() { try { if (createStmt != null) { createStmt.close(); createStmt = null; }; if (selectStmt != null) { selectStmt.close(); selectStmt = null; }; if ( connection != null ) { connection.commit(); connection.close(); connection = null; }; System.out.println("closeDb - closed open connection , statements"); } catch (Exception e) {handleException("closeDb", null, e); }; }

Test our sample application in WebSphere Studio

From the workbench menu on the Java perspective, I selected Run > Run. This opens the prompt screen below. I entered the name of my Java class -- com.ebt.test.TextExcelAccess -- and hit the run button to run the application.

The output on the system console shows what executed:

 TestExcelAccess.main started: Thu Jun 16 12:39:46 EDT 2005 closeDb - closed open connection , statements TestExcelAccess.main ended connect to db >>create Sheet5 contacts page in excel workbook createDb started table Sheet5 created in SAMPLE row: 0 inserted row: 1 inserted row: 2 inserted createDb for Sheet5 completed.. >> select contacts Report on: jdbc:odbc:Excel Files;DBQ=C:\save\wrt\_tips\qwtips\qwtip917_JavaExcelAccess\expenses1.xls; READONLY=0 REPORT on : [Sheet5$] table Thu Jun 16 12:39:46 EDT 2005 Id FirstName LastName Company Department Title Phone1 Phone2 email Address1 Address2 City State MailCode Country Status Comment 1001.0 Jim Mason ebt-now Technical Services Architect other null jmason900@yahoo.com null null null null null null null null 1003.0 Joe Mason NDR Management President other null jmason@ndr.com null null null null null null null null close db

Note: The select statement pulled only the rows with LastName = 'Mason'. Below is the actual worksheet in the workbook that was created:

That's it! That's how to create an Excel worksheet, insert data and selectively read it back. Now you can integrate this in automating processing with any database you need. Imagine how this could simplify month-end processing for your financial teams.

---------------------------------------

About the author: Jim Mason works at ebt-now, an iSeries Web integration company, providing QuickWebServices for iSeries customers: Web planning, WebSphere, WebFacing, Web development, Web networking, Web support, Web security and training services. Jim is creating a self-study course for RPG programmers that teaches "hands-on" rapid visual development with WDSC for all types of iSeries and e-business applications without the need to become a Java expert. The course will be published by Rochester Initiative. You can reach Jim at jemason@ebt-now.com.


 

This was first published in July 2005

Dig deeper on iSeries Java programming

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchEnterpriseLinux

SearchDataCenter

Close