Programmatically Manipulating Microsoft Excel Spreadsheets with the Apache POI API
The Apache Jakarta POI project consists of Java APIs dedicated to the manipulation of files based on Microsoftís OLE 2 Compound Document format. In this article, youíll learn how to use the APIs of the POI project to read from and write to Microsoft Excel spreadsheets. As you will see, the programmatic liberty to manipulate Excel files represents a powerful offering to the Java programmer.
Programmatically Manipulating Microsoft Excel Spreadsheets with the Apache POI API - Letís Learn How to Write to a Spreadsheet (Page 2 of 4 )
The spreadsheet weíll be manipulating is rather simple. A screen shot of the spreadsheet, named sales.xls and the graph embedded in the spreadsheet is shown below.
Figure 1: Our untouched spreadsheet, sales.txt.
The sales.xls file consists of a simple spreadsheet which represents sales amounts for an imaginary company in the states of Texas, California, and New York. To see what we can do with the POI API, weíll alter the values of our sales values programmatically. The code in Listing 1 does just that. We change the values for the cell values of B2, the sales of the state of California.
As you can see, the process is quite simple. First, we open our Excel file. From there, we pinpoint the appropriate cell we want to manipulate. Notice how the Apache POI provides values objects such as HSSFRow and HSSFCell which programmatically correspond to rows and cells from the Excel world. Note how rows of the spreadsheet are zero indexed while column indexing starts with 1.
Before writing to a cell, we specify our cell type: CELL_TYPE_NUMERIC. Our desired changes are reflected when we use the write method of our HSSFWorkbook object. By executing the code in Listing 1, we change the value of cell B2 to 89000. The resulting altered spreadsheet and embedded graph are shown in Figure 2.