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.