Home arrow Apache arrow Page 2 - Programmatically Manipulating Microsoft Excel Spreadsheets with the Apache POI API
APACHE

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.

Author Info:
By: Kulvir S. Bhogal
Rating: 4 stars4 stars4 stars4 stars4 stars / 65
November 26, 2003
TABLE OF CONTENTS:
  1. · Programmatically Manipulating Microsoft Excel Spreadsheets with the Apache POI API
  2. · Letís Learn How to Write to a Spreadsheet
  3. · Letís Learn How to Read From a Spreadsheet
  4. · Conclusion

print this article
SEARCH DEVARTICLES

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.


Figure 2: Our Programmatically Altered Spreadsheet, sales.xls.


blog comments powered by Disqus
APACHE ARTICLES

- Programmatically Manipulating Microsoft Exce...
- Installing PHP under Windows
- Compressing Web Content with mod_gzip and mo...
- Compressing Web Output Using mod_deflate and...
- Setting Up Apache 2.0.45 to Parse PHP Pages
- Custom Error 404 Documents with PHP
- Using Apache and PHP on Mac OS X
- ASP: Active Sessions, Active Logins and Tota...
- Working With Oracle on Windows: Part 1
- The Quick-n-Dirty Guide to Setting Up Apache...
- Installing Apache With SSL: The Complete Gui...
- 7 Powerful .htaccess Customization Tips
- Trap And Get Notified: A Practical Solution ...
- One Way To Use Server Side Includes
- Using ForceType For Nicer Page URLs

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials