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. About Our Editorial ProcessAt DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere. See our full editorial policy. About Our JournalistCharlie Frank Charlie has over a decade of experience in website administration and technology management. As the site admin, he oversees all technical aspects of running a high-traffic online platform, ensuring optimal performance, security, and user experience. View Author Mastering finances key to startup success Cameron Wiggins April 29, 2024 5:30 PM Factors to Consider When Purchasing a Windows 10 Key Noah Nguyen April 29, 2024 4:53 PM Paris-based Payflows raises $26M for fintech growth Noah Nguyen April 29, 2024 3:20 PM FDIC assumes control of Republic First Bank Noah Nguyen April 29, 2024 3:19 PM Japanese yen depreciation raises economic concerns Noah Nguyen April 29, 2024 1:48 PM Walmart’s One offers BNPL loans for high-cost goods Cameron Wiggins April 29, 2024 1:20 PM Humble coffee cart evolves into multi-million business April Isaacs April 29, 2024 1:07 PM Jackie Aina shares insights for aspiring entrepreneurs Noah Nguyen April 29, 2024 8:56 AM Shiba Inu coin shows promising future Cameron Wiggins April 29, 2024 7:55 AM Walmart’s fintech startup to disrupt BNPL market Cameron Wiggins April 26, 2024 5:38 PM From humble cart to thriving business: The Nitro Bar’s success story Rashan Dixon April 26, 2024 3:50 PM Adapting business strategies amidst unpredictable economies April Isaacs April 26, 2024 3:11 PM Singaporean entrepreneur’s journey to grocery chain success Cameron Wiggins April 26, 2024 3:06 PM Rhode Island coffee startup finds success April Isaacs April 26, 2024 1:39 PM Erel Margalit’s enduring impact on Israeli tech industry Noah Nguyen April 26, 2024 1:12 PM Bilt Rewards transform US rental sector Cameron Wiggins April 26, 2024 8:58 AM Chinese e-commerce platform upsets US market Noah Nguyen April 26, 2024 8:54 AM Stripe refines business model for fintech competitiveness Johannah Lopez April 26, 2024 7:13 AM Addressing challenges in Israel’s tech sector Rashan Dixon April 25, 2024 5:58 PM Gender disparity challenged by women-centric coworking spaces April Isaacs April 25, 2024 5:56 PM From cart to empire: The Nitro Bar’s rise Cameron Wiggins April 25, 2024 5:23 PM FlexAI nets $30 million to simplify AI computing infrastructure Noah Nguyen April 25, 2024 3:36 PM Google fires 28 protesting employees, maintains professional ethics Johannah Lopez April 25, 2024 1:49 PM Growing interest in small business ownership among millennials Johannah Lopez April 25, 2024 1:31 PM India’s rise in space exploration with private corporations Johannah Lopez April 25, 2024 8:42 AM |