Last news

SetupABudge Debt Elimination Software 2.0 with Keygen Activation
The penitentiary network, « backbone of terror » during the communist era. Reeducation in Romanian communist prisons was a series of processes initiated after the installment of the communist regime at the end of the second world war, that targeted the people who were...
Read more
e My'Net'Assistant 6.10 + License Key
Doctors for Life International v Speaker of the National Doctors for Life International v Speaker of the National Assembly and Others (CCT12/05) 2006 ZACC 11; 2006 (12) BCLR 1399 (CC 2006 (6) SA 416 (CC) (17). Doctors for Life International v Speaker of the National...
Read more
Aostsoft BMP to Word OCR Converter 3.8.7 with License Key
Accessing Serial Flash Memory Using SPI Interface - Microsemi Configuration Manual cab Label Printers - cab Produkttechnik 2 Accessing Serial Flash Memory using SPI Interface - Libero SoC v11.7 and Keil. Builder. It describes how to build, debug, and run Keil uVision application. Revision 5...
Read more

Import Table from Excel for IBM DB2


Desktop Share 3.5 Serial key plus patch

How to use export/import/load in Data Studio to migrate data to. - IBM Eclipse - How do you import a spreadsheet into DB2? - Stack Overflow Excel IBM DB2 Import, Export Convert - Software Informer. Use MS

© 2002 International Business Machines Corporation. All rights reserved.

Let's face it. Not everyone in the world is SQL-savvy. As a consultant, I tend to deal with the full spectrum of job titles at my client organizations. On one end of the spectrum we have tech-head software engineers that can probably take a look at binary code and troubleshoot a software bug; on the other end, we might have business experts who know the ins and outs of the business, but don't know anything about programming. In this article, we'll address the needs of the latter.

The business need

I have been on some locations where the customer would like to see what is in their IBM® DB2® Universal DatabaseTM, but do not know how to or do not want to use the SQL command line. They want to know if there is a utility that can go through their DB2 database tables and produce Microsoft® Excel® spreadsheets that contain the data in the database. The business expert can then use Microsoft Excel for data manipulation (such as totals, averages, and so on) and for producing reports.

In this article, you'll learn how to build an application that will allow for the transfer of DB2 data to Microsoft Excel spreadsheets. We'll tap into the offerings of JDBCTM to provide a no-cost solution. We'll also use the assistance of an open source offering of the Apache Software Foundation.

Back to top

What you'll learn

Yes, there are other ways to do this... Note that there are other, more automated, ways to get DB2 data into Excel:

  • The IMPORT and EXPORT utilities in DB2 Version 8.1 support a worksheet file form at (for Lotus 1-2-3). This file type is then easily imported into Excel.
  • Excel can import data directly from DB2 through an OLE DB connection.

Of course, my main point is to introduce you to the Apache POI project. As you will see, the API can provide you with the tools you need to create your own, customized solution to writing and reading Excel files.

Back to top

Apache's POI project and HSSF

The Apache Software Foundation has taken on a project known as POI, which stands for Poor Obfuscation Implementation. This project provides JavaTM APIs for manipulating file formats that are based on Microsoft's OLE 2 Compound Document format. The project is divided into several components, including one called HSSF, which writes Excel files. This is the component that we will use. You can read more about the overall structure of the POI project on the POI website at http://jakarta.apache.org/poi/index.html.

HSSF stands for Horrible Spreadsheet Format. I don't believe any further comment on that is necessary.

The HSSF component lets programmers read and write to Microsoft Excel 97-2002 files (in the BIFF8 file format) using Java. In particular, you'll be using the org.apache.poi.hssf.usermodel package in the POI jar file. You can obtain the JAR file at http://jakarta.apache.org/builds/jakarta-poi/. At the time of writing of this article, Apache was at version 1.5.1 of the POI project. The contributors of the Apache Jakarta project are constantly updating their code to better serve the open source community, so you might see a newer version. In this article, we use some of the basic offerings of the POI API, so you should be okay if you use a newer version.

What you can do with HSSF

HSSF lets you manipulate Excel files extensively. Not only can you simply write numeric and string cell values (which is what I show you in this article), you can go as far as to define row and column sizes, and format a cell's style (make text bold or italicized, add borders, etc.).

Back to top

Installing and running the application

The sample code provided here reads data from DB2 tables and writes to a chosen Excel file. It would be a good idea to look over the code as I describe it to get a good grasp of how it works.

Ensure that your environment is set up as follows:

  • You are running DB2 7.2 in a Microsoft Windows® environment. Although I built my application on DB2 7.2, you should also be able to run this with DB2 8.1.
  • The db2java.zip and the POI jar file are added to your Java class path.
    • db2java.zip contains the JDBC API that we will need to interact with your DB2 database. You must make sure that you are using JDBC level 2. If you are not, you should run the usejdbc2.bat file located in your <DB2INSTALLDIR>\SQLLIB\java12 directory.
    • The POI jar file contains the Apache POI package (org.apache.poi.hssf.usermodel) you will use to produce Microsoft Excel files.

I developed this application using IBM WebSphere® Studio Application Developer Version 5.0. It is a good idea to use an IDE like WebSphere Studio to work with the DB2Excel application at first so that you can step through the code to get a proper understanding of how it works. The main class of the application you will need to run is located in DB2Excel.java.

Back to top

Stepping through the application

The first thing you must do when you run DB2Excel is log in, as seen in Figure 1. You do this by interacting with the DB2Excel GUI. The GUI of DB2Excel was designed using Java Swing. The login portion of the application is handled by the code of LoginDialog.java and LoginService.java. The class SQLFacade.java is used throughout the application to hide the programmer from the intricacies of using the JDBC API. If the login properties specified by a user are valid, then an SQLFacade Object is instantiated. Note that the sample application assumes that the database you are trying to connect to is either local or cataloged locally. This SQLFacade object is then used to interact with the DB2 database data.

Figure 1. Logging in
logging in

After you have successfully logged in, you will be provided with a screen from which you can choose a table from the tables that are available to the current connection, as seen in Figure 2.

Figure 2. Choosing a DB2 table
Choosing a DB2 table

To gather the list of available tables, we tap into the metadata of our Connection object. You can see this in the getTablesFromDb() function of the SQLFacade class, as is shown in Listing 1:

Listing 1. getTablesFromDb()
/ Queries the database for the tables created by the user / public Object[] getTablesFromDb() throws SQLException { DatabaseMetaData md = connection.getMetaData(); String types[] = {"TABLE"}; ResultSet set = md.getTables(null, null, null, types); List list = new ArrayList(); list.add(NO_TABLE); while (set.next()) { list.add(set.getObject(3)); } set.close(); if (list.size() > 0) return list.toArray(); else return null; }

The user must choose a table from the pulldown and then left click on the Load Table button, which loads the table data into memory. The user can preview the data in the chosen table, as seen in Figure 3.

Figure 3. Loading a table's content
Loading a table's content

You can find the code that powers the table data load process in the ResultSetTableModel class in the reloadTableModel method shown in Listing 2.

Listing 2. reloadTableModel
/ reloads the TableModel with the contents of specified tableName @param tableName table with which to reload the TableModel @exception SQLException if a database error occurs @exception ClassNotFoundException / public void reloadTableModel(String tableName) throws SQLException, ClassNotFoundException { ResultSet rs = null; this.tableName = tableName; try { rs = sqlFacade.executeQuery("SELECT from " + tableName); clearAll(); updateColumnModel(rs.getMetaData()); while (rs.next()) { ArrayList list = new ArrayList(); for (int i = 1; i <= colCount; i++) { Object o = rs.getObject(i); list.add(o); } result.add(list); rowCount++; } fireTableStructureChanged(); } finally { if (rs != null) { try { rs.close(); } catch (SQLException se) { System.out.println ("An error has occurred: " + se); } } } }

You will notice that a list of lists is used as the data structure to house the DB2 table data temporarily in memory.

After a table has been loaded into memory, the user can click the Create My Spreadsheet button to set the wheels in motion to create a Microsoft Excel spreadsheet from the current table data.

The user is provided with a Save dialog, as seen in Figure 4.

Figure 4. Saving to an Excel file
Saving to an Excel file

After the user chooses a destination file, control is passed to the ExcelFileGenerator class. It is in this class that we do the dirty work of creating our Excel Spreadsheet. In particular, the run() method is where things really happen.

A threaded model is used simply to show a progress bar to the user as the Microsoft Excel file is in the process of being generated (in other words, eye candy).

In the interest of saving space, the entire run() method listing is not included in this article, so let's focus on the important steps of the method to get a good grasp on how we create our Excel spreadsheet:

  1. We instantiate a FileOutStream object using the name we specified in the Save dialog.
    FileOutputStream fileOut = newFileOutputStream(this.fileName);
  2. We then use the HSSF API for the first time and create a workbook that contains a spreadsheet of the name of our source table.
    HSSFWorkbook wb = new HSSFWorkBook(); HSSFSheet sheet1 = wb.createSheet(this.tableName);
  3. Next we create a row in the spreadsheet and populate this row with the column names of our table.
    HSSFRow row = sheet1.createRow((short)0); List columnNames = tableModel.getColumnNames(); for (int i=0; i<columnNames.size(); i++) { row.createCell((short)i).setCellValue((String)columnNames.get(i)); }
  4. Moving along through the code, we then populate the spreadsheet with our table data. The code extracted from the run() method below is what is used to do this. The rest of the code you see is mostly GUI-related.
    int rowCount= this.tableModel.getRowCount(); for (int i=0; i<rowCount) { List tableData = this.tableModel.getRow(i); row = sheet1.createRow((short)(i+1)); for (int j=0; j<tableData.size(); j++) { String columnData = (String)tableData.get(j); Row.createCell((short)j).setCellvalue(columnData); } }
  5. To wrap things up, we close our workbook and our FileOutPutStream object:
    wb.write(fileOut); fileOut.close();

Back to top

The fruits of your labor

As mentioned before, a progress bar (shown in Figure 5) is shown to users to let them know that the Excel file is being generated.

Figure 5. Progress indicator
Progress indicator

When all is said and done, you should see an Excel file written to the destination you specified. Figure 6 shows a screenshot of the resulting Excel file for the data I had in my sample database table.

Figure 6. Sample spreadsheet produced by DB2Excel
Sample spreadsheet produced by DB2Excel

Back to top

Conclusion

In this article, you learned how to use the offerings of Apache's POI to build an application that can read from your DB2 tables and write to Microsoft Excel spreadsheets. POI is yet another representative example of how open source offerings can be tapped into to create some valuable, powerful applications for your enterprise operation that work synergistically with your commercial applications. Feel free to modify the DB2Excel application to fit your particular organization's needs.

Back to top

Download




361
Sitemap