How to use Java to integrate Excel worksheets

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).

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT WebSphere Strategies for iSeries professionals Application modernization strategies for System i Application modernization in the i world Natively supported Web applications for Power running i Enterprise open source basics Basic security considerations for a Domino/WebSphere system Simplifying data access using Java Standard Tag Library Integrating Microsoft ActiveX components with WebSphere Choices for running Web workloads on iSeries Virtual hosting for iSeries Web applications Automate WebSphere configuration backups on the iSeries (i5) iSeries Java programming Groovy programming on IBM i EGL Rich UI on IBM i: Do you Dojo? Programming for the Web on the IBM i, what is possible Database performance comparisons on IBM i Database drivers on the i: MySQL vs. IBM Toolbox How to: Output SQL script to a text file from an AS/400 Application modernization for the iSeries: Why bother? JDBCODBC functionality -- Java to Excel for complex workbooks Necessity leads to iSeries Watchdog development Accessing AS/400 data using Excel ODBC drivers iSeries Java programming Research iSeries SQL commands and statements Enhancing RPG with external SQL stored procedures Tracking data changes on IBM i with triggers Introduction to SQLRPGLE on IBM i: Making a report Making the most of RPG data handling on IBM i When is the YES option for 'reuse deleted files' function the best choice? Monitoring members 'stuck' within a physical file on an EDI system Creating a host variable of the 'where in' statement in SQL SQL server error message -321 Choose which column names are returned via ODBC when working with DB2 files Convert a numeric physical file to a character in SQL without leading zeros iSeries SQL commands and statements Research

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

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

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.

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

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.

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.

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.

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.

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:

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.

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