Author: Daniel Rubio
POI comprises a handful of projects, all of which have their own objectives. POIFS was the first sub-project created for POI and provides POI’s underpinnings, given that it’s used under the hood of the other POI APIs to deal with the OLE file format. Its use is generic in nature, since it does not define a specific Office format to deal with, like an xls (Excel) or doc (Word) file, but a global means of accessing OLE 2 formats.
HWPF is the set of Java APIs that allows you to read and write Microsoft Word 97(-XP) documents. HSSF is its counterpart for Excel 97(-XP), used for accessing and modifying spreadsheet information from a Java environment. HPSF is a complementary project to both the aforementioned POI components; it lets programs read and alter properties in an Office document — specifically, parameters like date, title, and author, all of which do not form an integral part of the actual document as defined by the OLE 2 format. Finally, POI-Utils is a general set of classes that do not fit with the overall scope of POI, but are often helpful used in combination with POIs core components.
To see how we can use POI, let’s create a simple spreadsheet like the one illustrated in the following graphic:
To do this we will use the HSSF API provided with POI. The following snippet could be placed inside a Servlet or any other Java code, so long as the POI libraries are accessible:
// The following code is based on classes from the following libraries // org.apache.poi.hssf.usermodel.*; // java.io.*; HSSFWorkbook document = new HSSFWorkbook(); HSSFSheet sheet = document.createSheet("CD-Catalog"); // Create an initial row with cell HSSFRow row = sheet.createRow((short)0); row.createCell((short)0).setCellValue("CD Catalog"); // Create lines for the catalog HSSFRow row2 = sheet.createRow((short)1); HSSFRow row3 = sheet.createRow((short)2); HSSFRow row4 = sheet.createRow((short)3); // Define rows and initial cells with product name row2.createCell((short)0).setCellValue("CD-R 80 Min"); row3.createCell((short)0).setCellValue("CD-R 21 Min (Mini)"); row4.createCell((short)0).setCellValue("Business Card CD"); // Define cells with prices row2.createCell((short)1).setCellValue(0.39); row3.createCell((short)1).setCellValue(0.57); row4.createCell((short)1).setCellValue(0.63); // Write the output to a file system FileOutputStream spreadsheet = new FileOutputStream("catalog.xls"); document.write(spreadsheet); spreadsheet.close();
All of the classes used in the generation of the spreadsheet are located in the
org.apache.poi.hssf.usermodel library included in POIs distribution. The first object definition in the form of a
HSSFWorkbook instance is used to define the actual document. Later on, we associate a specific sheet through the
HSSFSheet class to this same document instance.
HSSFRow class along with its methods is used to execute the majority of the workload, which is defining the actual rows, cells, and values. Ending the sequence, we define a
FileOutputStream, which is used to write the document to the file system.
Now that we have generated a spreadsheet from scratch, let’s use POI to modify an existing document. The following code modifies the previous spreadsheet, inserting the current date and placing it in a blue-colored cell:
// The following code is based on classes from the following libraries // org.apache.poi.hssf.usermodel.*; // org.apache.poi.hssf.util.*; // org.apache.poi.poifs.filesystem.*; // java.io.*; // java.util.*; POIFSFileSystem poiStream = new POIFSFileSystem(new FileInputStream("catalog.xls")); HSSFWorkbook document = new HSSFWorkbook(poiStream); HSSFSheet sheet = document.getSheetAt(0); HSSFRow row = sheet.getRow(0); // Generate today's date and place it in a cell HSSFCell cell = row.createCell((short)1); cell.setCellValue(new Date()); // Define a style for both the date and cell background HSSFCellStyle style = document.createCellStyle(); style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy")); style.setFillBackgroundColor(HSSFColor.BLUE.index); cell.setCellStyle(style); // Rewrite the spreadsheet to the File System FileOutputStream modSpreadsheet = new FileOutputStream("catalog.xls"); document.write(modSpreadsheet); modSpreadsheet.close();
For reading an OLE-based document we use the
POIFSFileSystem class, then access the document with the
getter methods provided in the corresponding
HSSFRow classes. We later generate a date value and place it inside an
HSSFCell instance. To format this last cell, we create an
HSSFCellStyle instance, and through its
setFillBackgroundColor methods give it a specific date format and blue background. Finally, we rewrite the spreadsheet back to the file system using a
We leave you to explore POI’s more advanced capabilities, such as its powerful word processing API for Microsoft documents and its enhanced formatting features for Microsoft spreadsheets, all of which give you the ability to create or alter these common formats from a Java programming environment.
Daniel Rubio is the principal consultant at Osmosis Latina, a firm specializing in enterprise software development, training, and consulting based in Mexico.