Welcome!

Weblogic Authors: 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.

@ThingsExpo Stories
This is not a small hotel event. It is also not a big vendor party where politicians and entertainers are more important than real content. This is Cloud Expo, the world's longest-running conference and exhibition focused on Cloud Computing and all that it entails. If you want serious presentations and valuable insight about Cloud Computing for three straight days, then register now for Cloud Expo.
You think you know what’s in your data. But do you? Most organizations are now aware of the business intelligence represented by their data. Data science stands to take this to a level you never thought of – literally. The techniques of data science, when used with the capabilities of Big Data technologies, can make connections you had not yet imagined, helping you discover new insights and ask new questions of your data. In his session at @ThingsExpo, Sarbjit Sarkaria, data science team lead ...
So, you bought into the current machine learning craze and went on to collect millions/billions of records from this promising new data source. Now, what do you do with them? Too often, the abundance of data quickly turns into an abundance of problems. How do you extract that "magic essence" from your data without falling into the common pitfalls? In her session at @ThingsExpo, Natalia Ponomareva, Software Engineer at Google, will provide tips on how to be successful in large scale machine lear...
IoT device adoption is growing at staggering rates, and with it comes opportunity for developers to meet consumer demand for an ever more connected world. Wireless communication is the key part of the encompassing components of any IoT device. Wireless connectivity enhances the device utility at the expense of ease of use and deployment challenges. Since connectivity is fundamental for IoT device development, engineers must understand how to overcome the hurdles inherent in incorporating multipl...
The IETF draft standard for M2M certificates is a security solution specifically designed for the demanding needs of IoT/M2M applications. In his session at @ThingsExpo, Brian Romansky, VP of Strategic Technology at TrustPoint Innovation, will explain how M2M certificates can efficiently enable confidentiality, integrity, and authenticity on highly constrained devices.
The paradigm has shifted. A Gartner survey shows that 43% of organizations are using or plan to implement the Internet of Things in 2016. However, not just a handful of companies are still using the old-style ad-hoc trial-and-error ways, unaware of the critical barriers, paint points, traps, and hidden roadblocks. How can you become a winner? In his session at @ThingsExpo, Tony Shan will present a methodical approach to guide the holistic adoption and enablement of IoT implementations. This ov...
We’ve worked with dozens of early adopters across numerous industries and will debunk common misperceptions, which starts with understanding that many of the connected products we’ll use over the next 5 years are already products, they’re just not yet connected. With an IoT product, time-in-market provides much more essential feedback than ever before. Innovation comes from what you do with the data that the connected product provides in order to enhance the customer experience and optimize busi...
Artificial Intelligence has the potential to massively disrupt IoT. In his session at 18th Cloud Expo, AJ Abdallat, CEO of Beyond AI, will discuss what the five main drivers are in Artificial Intelligence that could shape the future of the Internet of Things. AJ Abdallat is CEO of Beyond AI. He has over 20 years of management experience in the fields of artificial intelligence, sensors, instruments, devices and software for telecommunications, life sciences, environmental monitoring, process...
SYS-CON Events announced today that Ericsson has been named “Gold Sponsor” of SYS-CON's @ThingsExpo, which will take place on June 7-9, 2016, at the Javits Center in New York, New York. Ericsson is a world leader in the rapidly changing environment of communications technology – providing equipment, software and services to enable transformation through mobility. Some 40 percent of global mobile traffic runs through networks we have supplied. More than 1 billion subscribers around the world re...
SYS-CON Events announced today that Stratoscale, the software company developing the next generation data center operating system, will exhibit at 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. Stratoscale is revolutionizing the data center with a zero-to-cloud-in-minutes solution. With Stratoscale’s hardware-agnostic, Software Defined Data Center (SDDC) solution to store everything, run anything and scale everywhere...
Angular 2 is a complete re-write of the popular framework AngularJS. Programming in Angular 2 is greatly simplified – now it's a component-based well-performing framework. This immersive one-day workshop at 18th Cloud Expo, led by Yakov Fain, a Java Champion and a co-founder of the IT consultancy Farata Systems and the product company SuranceBay, will provide you with everything you wanted to know about Angular 2.
SYS-CON Events announced today that Men & Mice, the leading global provider of DNS, DHCP and IP address management overlay solutions, will exhibit at 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. The Men & Mice Suite overlay solution is already known for its powerful application in heterogeneous operating environments, enabling enterprises to scale without fuss. Building on a solid range of diverse platform support,...
In his session at @ThingsExpo, Chris Klein, CEO and Co-founder of Rachio, will discuss next generation communities that are using IoT to create more sustainable, intelligent communities. One example is Sterling Ranch, a 10,000 home development that – with the help of Siemens – will integrate IoT technology into the community to provide residents with energy and water savings as well as intelligent security. Everything from stop lights to sprinkler systems to building infrastructures will run ef...
You deployed your app with the Bluemix PaaS and it's gaining some serious traction, so it's time to make some tweaks. Did you design your application in a way that it can scale in the cloud? Were you even thinking about the cloud when you built the app? If not, chances are your app is going to break. Check out this webcast to learn various techniques for designing applications that will scale successfully in Bluemix, for the confidence you need to take your apps to the next level and beyond.
Manufacturers are embracing the Industrial Internet the same way consumers are leveraging Fitbits – to improve overall health and wellness. Both can provide consistent measurement, visibility, and suggest performance improvements customized to help reach goals. Fitbit users can view real-time data and make adjustments to increase their activity. In his session at @ThingsExpo, Mark Bernardo Professional Services Leader, Americas, at GE Digital, will discuss how leveraging the Industrial Interne...
Whether your IoT service is connecting cars, homes, appliances, wearable, cameras or other devices, one question hangs in the balance – how do you actually make money from this service? The ability to turn your IoT service into profit requires the ability to create a monetization strategy that is flexible, scalable and working for you in real-time. It must be a transparent, smoothly implemented strategy that all stakeholders – from customers to the board – will be able to understand and comprehe...
Increasing IoT connectivity is forcing enterprises to find elegant solutions to organize and visualize all incoming data from these connected devices with re-configurable dashboard widgets to effectively allow rapid decision-making for everything from immediate actions in tactical situations to strategic analysis and reporting. In his session at 18th Cloud Expo, Shikhir Singh, Senior Developer Relations Manager at Sencha, will discuss how to create HTML5 dashboards that interact with IoT devic...
There is an ever-growing explosion of new devices that are connected to the Internet using “cloud” solutions. This rapid growth is creating a massive new demand for efficient access to data. And it’s not just about connecting to that data anymore. This new demand is bringing new issues and challenges and it is important for companies to scale for the coming growth. And with that scaling comes the need for greater security, gathering and data analysis, storage, connectivity and, of course, the...
The increasing popularity of the Internet of Things necessitates that our physical and cognitive relationship with wearable technology will change rapidly in the near future. This advent means logging has become a thing of the past. Before, it was on us to track our own data, but now that data is automatically available. What does this mean for mHealth and the "connected" body? In her session at @ThingsExpo, Lisa Calkins, CEO and co-founder of Amadeus Consulting, will discuss the impact of wea...
Digital payments using wearable devices such as smart watches, fitness trackers, and payment wristbands are an increasing area of focus for industry participants, and consumer acceptance from early trials and deployments has encouraged some of the biggest names in technology and banking to continue their push to drive growth in this nascent market. Wearable payment systems may utilize near field communication (NFC), radio frequency identification (RFID), or quick response (QR) codes and barcodes...