he purpose of the Jakarta POI open source project is to provide Java with the ability to manipulate file formats based on Microsoft’s OLE 2 Compound Document Format. Currently, the most widely accepted part of the project is the Horrible Spread Sheet Format (HSSF) (click the link to discover a bit of the history behind that name.
HSSF provides:
- An environmental API for read-only access to Excel (97-2000) format.
- A full user model API for creating, reading and modifying the Excel files.
The HSSF framework is divided into the following packages (see Table 1).
Table 1: Packages in the HSSF framework. The table lists the packages in the HSSF framework and the purpose of each.
Package |
Purpose |
org.apache.poi.hssf.eventmodel |
Handles different events generated in the process of readingand writing Excel documents. |
org.apache.poi.hssf.eventusermodel |
Provides classes to read Excel documents. |
org.apache.poi.hssf.record.formula |
Contains classes to handle FORMULA used in Excel document. |
org.apache.poi.hssf.usermodel |
Contains classes to generate Excel documents. |
org.apache.poi.hssf.util |
Contains utility classes to handle different attributes of theExcel document. |
Using these packages you can interact with existing Excel documents and create new ones. Before starting to develop the example, here’s a brief explanation of how the POI models an Excel document.
The Excel Document Model
From the HSSF point of view, the Excel document is divided into a Workbook, one or more Worksheets, Worksheet consists of rows. Rows have multiple cells and cells can have different display attributes and formula attached to them. This overall structure is as depicted in Figure 1.
![]() |
Build a Practical Example To generate this Excel document using HSSF, you must:
You can see that creating the spreadsheet requires separate tasks where you create a worksheet, create rows and cells, create a merged region for the worksheet caption, specify different styles for the cells and create a special type of cell to contain the mathematical formula that calculates the total. Finally, you have to save the workbook in the file system. Creating a new Excel worksheet is simple. First you create a new workbook:
Then you can create a new worksheet within that workbook:
You create rows by calling the Worksheet object’s createRow method. Remember, the row index starts from 0.
To create the merged area for the caption you want to define a merged region from the first to the third row, and from first to the tenth column. Here’s the code:
After creating the caption region, create a cell to contain the caption value.
Because the header row spans multiple rows and columns, the caption “The Bowling Score” will span the defined merged region. Worksheet cells have many display properties that you can control through HSSF. To do that, you create an HSSFCellStyle object and apply styles such as cell alignment (CENTER/LEFT/RIGHT). The following code shows how you can set the background colors and the font style for individual cells.
After defining cell styles, you can apply those styles as follows:
The Worksheet essentially consists of multiple rows and cells. You can create as many rows and cells as you need and then apply styles and assign data to the cells. The following example creates five rows with two cells in each row. The code sets the cell values last, using the setCellValue method.
One of the main advantages of Excel is that you can attach mathematical formulas to cells. The following code shows how to attach a formula to a cell. The example assigns a formula to calculate the total value of other cells in the worksheet. Notice that, in order to create a cell with a formula, you must first set the cell type to the FORMULA type.
When the workbook is complete, you can save it to a file system by opening a FileOutputStream object for the destination file and writing the Workbook contents to the stream using the write method.
It is really as simple as that. Here’s the entire procedure in condensed form.
Reading an Excel Document
Next, create a new workbook from the input stream. You can obtain a reference to any of the worksheets within the workbook. Notice that the worksheet index starts at 0.
A Little Bit More
The following points are important:
This tutorial should help you to get started in the world of Java-to-Microsoft bridges. The POI project offers considerably more than I’ve discussed here, such as the capability to interact with Microsoft Word and with Adobe’s PDF files. The project is very active, so you should monitor the POI project and keep up what’s going on. devx-admin
Share the Post:
![]() ![]() Proactive Web Security: Crucial for Businesses
Johannah Lopez
December 5, 2023
![]() ![]() Freshwater Reservoirs Boost Energy Storage Efficiency
Jordan Williams
December 5, 2023
![]() ![]() Indigenized Energy Empowers Native Tribes with Solar Power
Noah Nguyen
December 5, 2023
![]() ![]() Masdar Acquires Hybrid Renewable Projects in Poland
Grace Phillips
December 4, 2023
![]() ![]() Bridging AI Skills Gap in Software Development
Lila Anderson
December 4, 2023
![]() ![]() Taiwan Semiconductor: Essential Chip Manufacturer’s Growth
Jordan Williams
December 4, 2023
![]() ![]() Proactive Web Security: Crucial for Businesses
Johannah Lopez
December 5, 2023
9:15 AM
![]() ![]() Freshwater Reservoirs Boost Energy Storage Efficiency
Jordan Williams
December 5, 2023
8:21 AM
![]() ![]() Indigenized Energy Empowers Native Tribes with Solar Power
Noah Nguyen
December 5, 2023
6:16 AM
![]() ![]() Masdar Acquires Hybrid Renewable Projects in Poland
Grace Phillips
December 4, 2023
5:36 PM
![]() ![]() Bridging AI Skills Gap in Software Development
Lila Anderson
December 4, 2023
4:32 PM
![]() ![]() Taiwan Semiconductor: Essential Chip Manufacturer’s Growth
Jordan Williams
December 4, 2023
3:29 PM
![]() ![]() Convert Your PDF Documents to Excel Spreadsheets in One Click
DevX Editor
December 4, 2023
3:10 PM
![]() ![]() Alicia Barton Appointed Vineyard Offshore CEO
Noah Nguyen
December 4, 2023
2:26 PM
![]() ![]() Overcoming Multi-EDC Challenges in Healthcare Data Analysis
DevX Editor
December 4, 2023
2:18 PM
![]() ![]() Best Open-Source PBX Software Solutions
Rashan Dixon
December 4, 2023
1:54 PM
![]() ![]() AI Misuse Concerns: Strengthening Security Measures
Johannah Lopez
December 4, 2023
1:22 PM
![]() ![]() Cybersecurity Significance in Safeguarding Businesses
Johannah Lopez
December 4, 2023
10:09 AM
![]() ![]() Spotify Cuts Workforce to Strengthen Finances
Grace Phillips
December 4, 2023
9:19 AM
![]() ![]() Solutions for Website Access Denial Issues
Lila Anderson
December 4, 2023
8:16 AM
![]() ![]() Major US Companies Announce Job Cuts Amid Economic Uncertainty
Jordan Williams
December 4, 2023
7:14 AM
![]() ![]() Biden Proposes Wage Increase for Wildland Firefighters
Noah Nguyen
December 4, 2023
6:11 AM
![]() ![]() First Public Roadway with Wireless EV Charging
Lila Anderson
December 1, 2023
4:15 PM
![]() ![]() Novel CRISPR Systems Enhance Gene-Editing Precision
Johannah Lopez
December 1, 2023
1:33 PM
![]() ![]() Power-saving Mode Extends Android Battery Life
Jordan Williams
December 1, 2023
12:52 PM
![]() ![]() Setbacks Illuminate Advanced Nuclear Power Struggles
Noah Nguyen
December 1, 2023
11:49 AM
![]() ![]() Biden’s Guidelines Challenge EV Industry’s Reliance on China
Johannah Lopez
December 1, 2023
10:46 AM
![]() ![]() Sustainable Urban Transport: Innovative Solutions
Grace Phillips
December 1, 2023
9:44 AM
![]() ![]() IKEA Launches Smart Sensors for Home Monitoring
Lila Anderson
December 1, 2023
8:41 AM
![]() ![]() OpenAI’s Q* Algorithm: Solving Complex Math Problems
Jordan Williams
December 1, 2023
7:38 AM
![]() ![]() Neo-Tokyo Embraces Efficient Smart Homes
Noah Nguyen
December 1, 2023
6:35 AM
|