Java Platform, Enterprise Edition

Java EE Journal

Subscribe to Java EE Journal: eMailAlertsEmail Alerts newslettersWeekly Newsletters
Get Java EE Journal: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn


J2EE Journal Authors: Zakia Bouachraoui, Douglas Lyon, Stackify Blog, APM Blog, Sumith Kumar Puri

Related Topics: Java EE Journal, Apache Web Server Journal

J2EE Journal: Article

WebLogic Tutorial: "Integrating Apache Poi in WebLogic Server"

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

for (int i=1;resultSet.next(); i++)
         {
     row=sheet1.createRow(i);
row.createCell((short)0).setCellValue(resultSet.getString(1));
row.createCell((short)1).setCellValue(resultSet.getString(2));
row.createCell((short)2).setCellValue(resultSet.getString(3));
row.createCell((short)3).setCellValue(resultSet.getString(4));
row.createCell((short)4).setCellValue(resultSet.getString(5));
}

Create a FileOutputStream to output the Excel spreadsheet to an XLS file. An XLS file represents an Excel spreadsheet:

FileOutputStream output=new FileOutputStream(new File("c:/excel/catalog.xls"));

Output the Excel spreadsheet to an XLS file:

wb.write(output);

The ExcelWebLogic.jsp JSP used to generate an Excel spreadsheet is available in the References section.

To run the ExcelWebLogic.jsp JSP in the WebLogic Server, copy the JSP to the <weblogic81>\samples\server\examples\build\mainWebApp directory. Run the JSP with the URL http://localhost:7001/ExcelWebLogic.jsp.

An Excel spreadsheet gets generated which may be opened in Excel (http://office.microsoft.com/en-us/FX010858001033.aspx) or the Excel Viewer tool (http://office.microsoft.com/en-us/assistance/HA011620741033.aspx).

Storing an Excel Document in a Database Table
In this section we will store an Excel spreadsheet in a MySQL database table with the Apache POI API. The example Excel document stored is the spreadsheet, catalog.xls, which was generated in the previous section. The Excel spreadsheet is stored in MySQL table Catalog. Drop the Catalog table from which the Excel document was generated in the previous section with the MySQL command:

MySQL>DROP table Catalog;

Develop a JSP application to store the example Excel document in the MySQL database. In the JSP application import the Apache POI packages org.apache.poi.poifs.filesystem and org.apache.poi.hssf.usermodel. The org.apache.poi.poifs.filesystem package has classes to create an Excel workbook and the org.apache.poi.hssf.usermodel package has classes that represent an Excel workbook, spreadsheet, spreadsheet row, and row cell.

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

As in the previous section, obtain a JDBC connection from the MySQL datasource:

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

Create java.sql.Statement from the JDBC connection:

Statement stmt=conn.createStatement();

Create a MySQL table in which the Excel spreadsheet will be stored:

String createTable="CREATE TABLE Catalog(CatalogId VARCHAR(25) PRIMARY KEY,Journal
VARCHAR(25),Section VARCHAR(25),Edition VARCHAR(25),Title Varchar(125),Author Varchar(25))";

stmt.execute(createTable);

Create a POIFSFileSystem to read the Excel document:

File catalogExcel=new File("C:/ExcelWebLogic/catalog.xls");
FileInputStream inputStream=new FileInputStream(catalogExcel);
POIFSFileSystem fileSystem=new POIFSFileSystem(inputStream);

Obtain a HSSF workbook from the POIFSFileSystem:

HSSFWorkbook wb=new HSSFWorkbook(fileSystem);

Obtain an Excel spreadsheet from the Excel workbook:

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

Iterate over the rows in the spreadsheet with a row iterator:

java.util.Iterator rowIterator=sheet1.rowIterator();
HSSFRow row=(HSSFRow)rowIterator.next();

Retrieve the row cell values for each of the rows. For example, the CatalogId row cell value is retrieved with:

String catalogId=row.getCell((short)0).getStringCellValue();

Add a table row for each of the rows in the Excel spreadsheet:

String exceltable="INSERT INTO Catalog VALUES("+"\'"+catalogId+"\
'"+","+"\'"+journal
+"\'"+","+"\'"+section+"\'"+","+"\'"+edition+"\'"+","+"\'"+title+"\
'"+","+"\'"+author+"\'"+")";
stmt.execute(exceltable);

Copy the POIWebLogic.jsp to the <weblogic81>\samples\server\examples\build\mainWebApp directory. Run the JSP with the URL http://localhost:7001/POIWebLogic.jsp. A MySQL database table gets generated from the Excel spreadsheet. The POIWebLogic.jsp used to generate a database table from an Excel spreadsheet is available in the References section.

Conclusion
In this tutorial, an Excel spreadsheet was generated from a MySQL database table and subsequently the spreadsheet was stored in a database table. The WebLogic Server facilitates the conversion from database table to Excel spreadsheet and from spreadsheet to database table by providing a datasource and a J2EE application server to run a JSP application.

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.