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 Anticipating Bitcoin’s halving: impacts and strategies Rashan Dixon April 19, 2024 Nothing startup integrates ChatGPT into devices Rashan Dixon April 19, 2024 Google Maps enhances search for EV charging stations Cameron Wiggins April 19, 2024 Upcoming social security payments scheduled by birth dates Rashan Dixon April 19, 2024 Retirees to receive double Social Security payments soon Noah Nguyen April 19, 2024 Proposed Act Aims to Adjust Social Security for Seniors Rashan Dixon April 19, 2024 Anticipating Bitcoin’s halving: impacts and strategies Rashan Dixon April 19, 2024 5:39 PM Nothing startup integrates ChatGPT into devices Rashan Dixon April 19, 2024 5:06 PM Google Maps enhances search for EV charging stations Cameron Wiggins April 19, 2024 1:28 PM Upcoming social security payments scheduled by birth dates Rashan Dixon April 19, 2024 1:07 PM Retirees to receive double Social Security payments soon Noah Nguyen April 19, 2024 11:59 AM Proposed Act Aims to Adjust Social Security for Seniors Rashan Dixon April 19, 2024 8:33 AM USD/JPY stability holds amidst market ambiguities Cameron Wiggins April 19, 2024 8:27 AM Silver prices surge amid increasing demand Rashan Dixon April 19, 2024 7:52 AM Is Online Bookkeeping the Ideal Side Gig for Remote Workers? Kyle Lewis April 18, 2024 2:49 PM Global stock stability amid rising US interest rates Rashan Dixon April 18, 2024 1:44 PM Rivos Inc. raises $250 million pre-launch for AI chips Noah Nguyen April 18, 2024 1:19 PM Goldman Sachs advises caution in predicting Bitcoin prices Cameron Wiggins April 18, 2024 1:14 PM Predicted rise in social security benefits amid inflation Noah Nguyen April 18, 2024 1:09 PM Gold prices dip amid assertive fiscal comments Johannah Lopez April 18, 2024 11:46 AM Vorlon raises $15.7M to strengthen API security Noah Nguyen April 18, 2024 11:28 AM Last April social security disbursement approaching Johannah Lopez April 18, 2024 11:16 AM Rippling to pursue substantial funding round Johannah Lopez April 18, 2024 8:33 AM Simpson’s lawyer battles to protect estate from Goldmans Noah Nguyen April 18, 2024 8:31 AM April’s Social Security retirement payments underway Cameron Wiggins April 17, 2024 5:54 PM Rivian develops software to rank EV chargers Rashan Dixon April 17, 2024 5:50 PM Simpson’s will leaves Goldman family uncompensated April Isaacs April 17, 2024 5:26 PM Framework grapples with software sustainability issues Noah Nguyen April 17, 2024 3:44 PM Surprising economic growth attributed to China’s robust exports and domestic demand Rashan Dixon April 17, 2024 3:04 PM COBOL at 65: still a powerhouse in the tech industry Cameron Wiggins April 17, 2024 1:59 PM California’s minimum wage hike worries restaurateurs Noah Nguyen April 17, 2024 1:05 PM |