Welcome!

Weblogic Authors: Yeshim Deniz, Elizabeth White, Michael Meiner, Michael Bushong, Avi Rosenthal

Related Topics: Weblogic

Weblogic: Article

WebLogic Tutorial: "Integrating Apache Poi in WebLogic Server"

The Apache Jakarta POI project provides components for the access and generation of Excel documents

The Apache Jakarta POI project provides components for the access and generation of Excel documents. The POI HSSF API is used to generate Excel Workbooks and to add Excel spreadsheets to a workbook. An Excel spreadsheet consists of rows and cells. The layout and fonts of a spreadsheet are also set with the POI HSSF API.

A database table is often required to be presented in an Excel spreadsheet. Also, a developer's requirement could be to store an Excel spreadsheet in a database table. The Apache POI HSSF project is an API to create an Excel spreadsheet. The data in the Excel spreadsheet generated with the POI HSSF project may be static data in an XML document, or dynamically retrieved data from a database. Also, an Excel document may be converted to an XML document or be stored in a database. In this tutorial we will discuss the procedure to create an Excel spreadsheet from a MySQL database table in WebLogic Server. Subsequently, an Excel spreadsheet will be stored in a database table.

Preliminary Setup
The implementation of the POI HSSF project is provided in the org.apache.poi.hssf.usermodel package. The org.apache.poi.hssf.usermodel package classes are required in the Classpath to generate an Excel spreadsheet or to parse an Excel spreadsheet. Download the Apache POI library poi-bin-2.5.1-final-20040804.zip file and extract the zip file to an installation directory (http://jakarta.apache.org/poi/). Install WebLogic 8.1 Server. Download the MySQL database (www.mysql.com). Extract the MySQL zip file mysql-4.0.25-win32.zip to a directory. Install the MySQL database. Download the MySQL Connector/J JDBC driver (www.mysql.com/products/connector/j/). Extract the MySQL zip file mysql-connector-java-3.1.10.zip to a directory. Add the MySQL JDBC driver jar file, mysql-connector-java-3.1.10-bin.jar, to the <weblogic81>\samples\domains\examples\startExamplesServer script CLASSPATH variable.

Login to the MySQL database with the DOS command:

>mysql

Access the example database test with the command:

mysql>use test

Create an example database table in the MySQL database from which an Excel spreadsheet will be generated. The SQL script to create example table Catalog is shown in Listing 1.

Next, we will add the Apache POI .jar file to the WebLogic Server Classpath and create a JDBC datasource in the WebLogic Server to retrieve data for an Excel spreadsheet.

Add the poi-2.5.1-final-20040804.jar file to the CLASSPATH variable in the <weblogic81>\samples\domains\examples\startExamplesServer script. <weblogic81> is the directory in which the WebLogic Server is installed.

Next, create a JDBC connection with the MySQL database in WebLogic Server. Start the examples server with the script startExamplesServer. Access the administration console with the URL http://localhost:7001/console or with the Administration Console link in the WebLogic Server Examples index. In the administration console, right-click on the examples>Services>JDBC>Connection Pools node and select Configure a new JDBCConnectionPool. Specify the following connection properties to configure a JDBC connection pool:

  • The database type: MySQL
  • The JDBC driver: MySQL's Driver (Type 4)
  • Database name: test
  • Host name: localhost
  • Port number: 3306
  • Driver class name: com.mysql.jdbc.Driver
  • Connection URL: jdbc:mysql://localhost:3306/test
Next, configure a JDBC datasource in the administration console. Right-click on the examples>Services>JDBC>DataSources node and select Configure a new JDBCTxDataSource. In the "Configure the data source" frame, specify a datasource name and a JNDI Name - MySqlDS, for example. In the "Connect to connection pool" frame select the Connection Pool previously configured with the MySQL database. In the "Target the datasource frame," select the examplesServer. A datasource gets configured with the MySQL database.

Generating an Excel Document with Apache POI
In this section we will generate an Excel spreadsheet from the example database table. First, create a JSP application to generate an Excel spreadsheet.

In the JSP an Excel spreadsheet will be created from a MySQL database table. The Apache POI HSSF API is used to generate an Excel spreadsheet. The Apache POI HSSF package has classes for the different components of an Excel spreadsheet. Some of the commonly used classes of the Apache POI HSSF package are listed in Table 1.

First, import the Apache POI HSSF package:

<%@ page import="org.apache.poi.hssf.usermodel.*, java.sql.*,
java.io.*,javax.naming.InitialContext"%>

Create an Excel stylesheet workbook:

HSSFWorkbook wb=new HSSFWorkbook();

Next, create an Excel spreadsheet:

HSSFSheet sheet1=wb.createSheet("sheet1");

The data for the stylesheet is retrieved from a MySQL database table. Obtain a JDBC connection from the database. The JDBC connection is obtained with the datasource JNDI MySqlDS.

InitialContext initialContext = new InitialContext();
javax.sql.DataSource ds = (javax.sql.DataSource)
initialContext.lookup("MySqlDS");
java.sql.Connection conn = ds.getConnection();

Create a java.sql.Statement and get a result set from the example table Catalog:

Statement stmt=conn.createStatement();
ResultSet resultSet=stmt.executeQuery("Select * from Catalog");

Create a header row for the Excel spreadsheet. The rows in an Excel spreadsheet are "0" based.

HSSFRow row=sheet1.createRow(0);

Set the header row cell values corresponding to the table columns. The row cells are also "0" based. For example, the value for the first cell in the row is set with the setCellValue method to CatalogId.

row.createCell((short)0).setCellValue("CatalogId");

To add rows to the spreadsheet, iterate over the result set and add a row for each of the table rows. Retrieve the column values from the ResultSet and set the values in the row cells.

More Stories By Deepak Vohra

Deepak Vohra is a Sun Certified Java 1.4 Programmer and a Web developer.

More Stories By Ajay Vohra

Ajay Vohra is a senior solutions architect with DataSynapse Inc.

Comments (2) View Comments

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.


Most Recent Comments
bob kennelly 08/19/06 05:49:54 PM EDT

Hello this is just what i've been looking for, it's a very helpfull article!

In the article there is a statement saying "see the Reference area" to find the JSP code, can anyone tell me where i can find the reference section please?

Thanks very much!

SYS-CON Italy News Desk 02/19/06 03:33:36 PM EST

The Apache Jakarta POI project provides components for the access and generation of Excel documents. The POI HSSF API is used to generate Excel Workbooks and to add Excel spreadsheets to a workbook. An Excel spreadsheet consists of rows and cells. The layout and fonts of a spreadsheet are also set with the POI HSSF API.

IoT & Smart Cities Stories
We are seeing a major migration of enterprises applications to the cloud. As cloud and business use of real time applications accelerate, legacy networks are no longer able to architecturally support cloud adoption and deliver the performance and security required by highly distributed enterprises. These outdated solutions have become more costly and complicated to implement, install, manage, and maintain.SD-WAN offers unlimited capabilities for accessing the benefits of the cloud and Internet. ...
Business professionals no longer wonder if they'll migrate to the cloud; it's now a matter of when. The cloud environment has proved to be a major force in transitioning to an agile business model that enables quick decisions and fast implementation that solidify customer relationships. And when the cloud is combined with the power of cognitive computing, it drives innovation and transformation that achieves astounding competitive advantage.
In an era of historic innovation fueled by unprecedented access to data and technology, the low cost and risk of entering new markets has leveled the playing field for business. Today, any ambitious innovator can easily introduce a new application or product that can reinvent business models and transform the client experience. In their Day 2 Keynote at 19th Cloud Expo, Mercer Rowe, IBM Vice President of Strategic Alliances, and Raejeanne Skillern, Intel Vice President of Data Center Group and G...
DXWorldEXPO LLC announced today that "IoT Now" was named media sponsor of CloudEXPO | DXWorldEXPO 2018 New York, which will take place on November 11-13, 2018 in New York City, NY. IoT Now explores the evolving opportunities and challenges facing CSPs, and it passes on some lessons learned from those who have taken the first steps in next-gen IoT services.
The current age of digital transformation means that IT organizations must adapt their toolset to cover all digital experiences, beyond just the end users’. Today’s businesses can no longer focus solely on the digital interactions they manage with employees or customers; they must now contend with non-traditional factors. Whether it's the power of brand to make or break a company, the need to monitor across all locations 24/7, or the ability to proactively resolve issues, companies must adapt to...
"IBM is really all in on blockchain. We take a look at sort of the history of blockchain ledger technologies. It started out with bitcoin, Ethereum, and IBM evaluated these particular blockchain technologies and found they were anonymous and permissionless and that many companies were looking for permissioned blockchain," stated René Bostic, Technical VP of the IBM Cloud Unit in North America, in this SYS-CON.tv interview at 21st Cloud Expo, held Oct 31 – Nov 2, 2017, at the Santa Clara Conventi...
Founded in 2000, Chetu Inc. is a global provider of customized software development solutions and IT staff augmentation services for software technology providers. By providing clients with unparalleled niche technology expertise and industry experience, Chetu has become the premiere long-term, back-end software development partner for start-ups, SMBs, and Fortune 500 companies. Chetu is headquartered in Plantation, Florida, with thirteen offices throughout the U.S. and abroad.
René Bostic is the Technical VP of the IBM Cloud Unit in North America. Enjoying her career with IBM during the modern millennial technological era, she is an expert in cloud computing, DevOps and emerging cloud technologies such as Blockchain. Her strengths and core competencies include a proven record of accomplishments in consensus building at all levels to assess, plan, and implement enterprise and cloud computing solutions. René is a member of the Society of Women Engineers (SWE) and a m...
SYS-CON Events announced today that DatacenterDynamics has been named “Media Sponsor” of SYS-CON's 18th International Cloud Expo, which will take place on June 7–9, 2016, at the Javits Center in New York City, NY. DatacenterDynamics is a brand of DCD Group, a global B2B media and publishing company that develops products to help senior professionals in the world's most ICT dependent organizations make risk-based infrastructure and capacity decisions.
A valuable conference experience generates new contacts, sales leads, potential strategic partners and potential investors; helps gather competitive intelligence and even provides inspiration for new products and services. Conference Guru works with conference organizers to pass great deals to great conferences, helping you discover new conferences and increase your return on investment.