Building Java Excel Tools with Apache POI in NetBeans

Many Australian businesses still rely on spreadsheets for everyday reporting, whether it is a quarterly report for the ASX, a council grant submission, or a small e-commerce outfit tracking GST figures. Java developers in Sydney, Melbourne, and Brisbane often end up writing tooling that can read and write .xlsx files without forcing end users to open Excel. Apache POI is the de facto library for this work, and NetBeans gives you a clean workspace to assemble everything. This walkthrough takes you through creating a Maven project, wiring in POI, and producing a working export utility.

NetBeans has a long track record of supporting Ant and Maven projects out of the box. Apache POI handles both the older XLS binary format and the modern XML-based XLSX, so a single library covers most of what Australian finance teams and government departments throw at your application.

Setting Up the Project in NetBeans

Start by launching NetBeans and creating a new Maven Java application. Give the project a sensible name such as poi-excel-exporter, set the group ID to match your organisation (perhaps an au.com.<company> reverse domain), and pick a recent JDK release. If your shop is new to multi-module layouts, the tutorial on creating a Maven multi-module project is a helpful starting point for splitting reader and writer logic into separate modules later.

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.5</version>
</dependency>

Once the project is generated, open the pom.xml and add the POI dependencies. You need poi and poi-ooxml for full coverage, including the streaming variant that matters for very large files. Refresh the project, let NetBeans pull the JARs, and you are ready to write code.

Comparing POI Components

POI ships with several APIs that target different Excel formats and use cases. Choosing between them depends on whether your colleagues are still sending you XLS exports from a 2003-vintage system or modern XLSX files generated by Power BI.

Component	Format	Memory Use	Best For
HSSF	XLS (BIFF8)	Moderate	Legacy spreadsheets from pre-2007 systems
XSSF	XLSX (OOXML)	Higher	Modern workbooks with rich formatting
SXSSF	XLSX streaming	Low	Large exports with millions of rows
Common SS	Both	Varies	Shared styling, fonts, formulas

HSSF targets the legacy XLS format and is fine for smaller, older spreadsheets. XSSF is the workhorse for modern files but loads everything in memory. SXSSF streams rows out, which keeps memory under control when exporting millions of rows from a backend service.

Reading Excel Files

Reading a workbook in POI follows the same pattern regardless of format. Open a FileInputStream, hand it to WorkbookFactory.create(...), and POI figures out whether it is dealing with XLS or XLSX. From there, iterate through Sheet, Row, and Cell to extract the data.

For an Australian payroll feed pulled from a local council system, you might read a sheet called "Timesheets", skip the first three header rows, and map the remaining content into POJOs. cell.getStringCellValue() and cell.getNumericCellValue() give you the raw contents, while DataFormatter returns formatted strings that respect locale settings, which is useful when handling dollar amounts and date columns.

Writing Excel Files

Writing is where POI really earns its keep. Build a workbook with new XSSFWorkbook(), create a sheet with workbook.createSheet("Invoices"), and populate cells through the row and cell accessors. For example, a small business in Adelaide exporting monthly invoices to a client might produce columns for invoice number, date, GST amount, and total.

Remember to call workbook.write(outputStream) followed by workbook.close() so resources are released. Failing to close the workbook is a common source of locked files, particularly when running the export as part of a scheduled job on a server in a Sydney data centre.

Styling Cells with Built-In Formats

Good-looking output matters when your file lands in front of a stakeholder. POI's CellStyle API covers fonts, borders, fills, alignment, and number formats. A few useful patterns:

Apply bold headers with XSSFFont and a header row style
Use CreationHelper.createDataFormat() for currency strings such as $#,##0.00
Freeze the top row with sheet.createFreezePane(0, 1) so labels stay visible while scrolling
Highlight negative values in red with a custom number format like $#,##0.00;[Red]-$#,##0.00

These tweaks make a workbook far easier to read when the file is forwarded up the chain to a general manager in the Melbourne head office.

Handling Large Workbooks and Memory

Standard XSSF loads the entire workbook into memory, which becomes a problem once you cross a few hundred thousand rows. SXSSF is the streaming variant: it keeps a sliding buffer of rows in memory and flushes the rest to disk. The trade-off is that SXSSF is write-only, so if you need to read and rewrite a giant workbook, you either process it in chunks or convert it to CSV first.

For Australian government datasets, such as a state education department publishing school enrolment figures, SXSSF is usually the right choice. Set the row access window size based on how much RAM your server has; a value of 100 to 500 is a sensible starting point.

Packaging and Deploying the Application

When the application is ready, you have a few deployment paths to consider:

Build a shaded JAR with the maven-shade-plugin for a single executable artefact
Package as a WAR and deploy to a Tomcat instance behind a corporate intranet
Ship a CLI tool and run it through a scheduled task on a Linux server
Distribute a JDK-aware installer using jpackage for a friendly Windows experience

After deploying, validate the output by opening a generated file in Excel on a test workstation, checking the formula bar to confirm cell types, and running a checksum on the row count. A quick smoke test catches most issues before the file reaches its destination. For more background on NetBeans workflows and project layouts, swing by the NetBeans Blog.