Reading and Writing Excel Files with POI. Avik Sengupta, Andrew Oliver and Rainer Klute. In our previous. article, we discussed what the POI. OLE 2 Compound Document. POI project. Probably half of the folks. How do I. write out a spreadsheet Good news In this article well show you how. Excel files and how to read any Microsoft files document. Conventions. The POI project is nearing a 2. In order to. keep this article relevant, well refer to the recent 1. While compiling the sources with later releases should work, there may be minor. Excel Basics. The Microsoft Excel 9. BIFF8. Recent versions. Excel have changed very little about this file format, and writing out the. So. when we say Excel 9. Excel 9. 7 to XP format. HSSFOur implementation of the Excel 9. HSSF, which stands. Horrible Spread. Sheet Format. We admire their method of. HSSF may have a comical name, but is a very. API. HSSF lets you read, write, and modify Excel files using nothing. Java. What does HSSF have to do with POIFS, which was covered in the previous. Like all of the other POI APIs, it is built on top of POIFS. So. there is code contained within HSSF that is very similar to the examples included with the previous. Generally, however, you dont need to know about POIFS APIs when writing to HSSF APIs. HSSF APIs. HSSF has two APIs for reading usermodel and eventusermodel. The former is. most familiar, and the latter is more cryptic but far more efficient. The. usermodel consists primarily of the classes in the. In earlier versions of HSSF. The usermodel package maps. Workbook. Sheet, Row, and Cell. It stores the. entire structure in memory as a set of objects. The eventusermodel package. It operates in a manner similar to XMLs SAX APIs or the AWT. It is also. read only, so you cannot modify files using the eventusermodel. Reading Using usermodel. Reading files using the HSSF usermodel is simple. Create a new. inputstream and construct an instance of. HSSFWorkbook. Input. Stream myxls new File. Input. Streamworkbook. HSSFWorkbook wb new HSSFWorkbookmyxls With the HSSFWorkbook instance, you can now retrieve a sheet. HSSFSheet sheet wb. Sheet. At0 first sheet. HSSFRow row sheet. Row2 third row. HSSFCell cell row. Cellshort3 fourth cell. This fetches the fourth cell of the third row from the first. You can retrieve a value from the cell object. Be sure. to note the cell type before retrieving its value. Cell. Type HSSFCell. Excel Ole File Format' title='Excel Ole File Format' />CELLTYPESTRING. The Cell was a String with value cell. String. Cell. Value. Cell. Type HSSFCell. CELLTYPENUMERIC. The cell was a number cell. Cell. Value. The cell was nothing were interested in. Have you ever seen this message Its not an error. You cant click Debug and go see which line of code its on when this happens. You cant even click Cancel. Excel repair software lets you repair a corrupt, damaged or inaccessible excel sheet and recover all the text, numbers, graphs, images, flowcharts, formulas. Introduction Several years ago I was given requirement for excelword reporting through PLSQL Oracle Forms 10g application. One catch was that these. Note that you may get an exception if you try and ask for an unrelated. Handle dates carefully while using HSSF. Excel stores all dates as numbers. The only way to distinguish a date is by the formatting of the. If you have ever formatted a cell containing a date in Excel, you will. I mean. Therefore, for a cell containing a date, cell. Cell. Type. will return HSSFCell. CELLTYPENUMERIC. However, you can use a. HSSFDate. Util. is. Cell. Date. Formattedcell, to check. This function checks the format against a few. Appendix 1 contains a complete example of using HSSF to create and return a workbook from a servlet. Example 1 reads in an Excel spreadsheet and converts it to comma separated values. Writing Files Using usermodel. Writing XLS files is even simpler. Create a new instance of. HSSFWorkbook. At some point, youll need to create an. HSSFWorkbook wb new HSSFWorkbook. File. Output. Stream file. Out new File. Output. Streamworkbook. Out. Out. close To create sheets and rows, you must do so from the parent object. For. instance HSSFSheet sheet wb. Sheet. HSSFRow row sheet. Rowshort0. HSSFCell cell row. Cellshort0. cell. Cell. Value1. row. Cellshort1. set. Cell. Value1. 2. Cellshort2. Cell. ValueThis is a string. Cellshort3. set. Cell. Valuetrue To style a cell, create a style and assign it to the cell. Assign that. style to as many cells as should be styled in that fashion. This confuses a. number of new users of HSSF, as it is currently possible to create an invalidly. Ideally, you want to create one style for the same. If you have a summary row and want to make its cells. Row. Style and assign it to any. It is important to realize that the Cell. Format and. Cell. Style objects are members of the workbook that are referenced. HSSFCell. Style style workbook. Cell. Style. style. Data. FormatHSSFData. Format. get. Builtin. Format,0 Red,0. Fill. Background. ColorHSSFColor. AQUA. Fill. PatternHSSFCell. Style. BIGSPOTS. Cell. Cell. Stylestyle. Other. Cell. set. Cell. Stylestyle Newer editions of HSSF allow you use a limited set of formulas. Note that. this is a beta quality feature. You should do appropriate testing before using. A formula assignment looks like this some. Cell. set. Cell. FormulaSUMA1 A2 At present, you can use any built in function or operator in the formula. IF function. This feature is being worked on even as we write this. Modifying Files Using usermodel. Files are modified by reading and rewriting. Example 3 takes in an Excel. Reading Files with the eventusermodel. Reading files via the eventusermodel is much harder but is much more memory efficient, since it expects the application to process the data as it is read. Reading in a file in this way is analogous to reading an XML document using. SAX, as opposed to DOM. You have to register your interest in the structures. Before using this model, however, you must familiarize yourself with some basic. Excel workbook. In HSSF, the low level binary structures are called records. Records come. in different types, each of which is modelled as a Java class in the. For example, the. BOFRecord denotes the start of the Workbook or. Sheet section. The Row. Record denotes that a row. Any record exposing the. Cell. Value. Record. Interface is a cell value. These include. Numeric. Record, Label. SSTRecord, and. Formula. Record. There are a few more that are obsolete or for. HSSF converts them. See The Structure of an HSSF Spreadsheet for more details. Event. Record. Factory factory new Event. Record. Factory. Listenernew ERFListener. RecordRecord rec. BOF Record. return true. BOFRecord. sid. Recordssome. Input. Stream The Structure of an HSSF Spreadsheet. HSSF, as we mentioned, is built on top of POIFS. More to the point, an. Excel 9. 7 file is an OLE 2 Compound Document. Convert Pdf A3 To A4 Percentage there. The underlying OLE 2 Compound. Document stores a stream, or File. This stream is always named. Workbook except in Excel 9. HSSF does not handle. Currently, HSSF deals strictly with this stream and delegates all functionality. POIFS. In recent builds, HSSF is aware enough to preserve these other. Macros and Images are stored in separate. OLE 2 CDF file. Macros should be preserved however, we have no API as of yet to. Within each stream is a set of records. A record is just an array of bytes. The header contains the record type AKA the id. The body is broken up into fields. Fields contain numeric data including references to other records, character. The following is the top level structure of an Excel Workbook Bla. OLE2. CDF headers. Workbook stream. Workbook. Static String Table Record. Sheet names. and pointers. Sheet. ROW. ROW. NUMBER RECORD cell. LABELSST Record cell. Document Summary.