Transcript Another Look at Microsoft Office Using Apache Jakarta POI

Another Look at
Microsoft Office Using
Apache Jakarta POI
Jeremy Lund
June 28th, 2006
CFUNITED – The premier ColdFusion conference
www.cfunited.com
About Me
 Manager of the Web Resource Center
University Health Care (Salt Lake City,
Utah)
 Sun Certified Java Programmer for the
Java 2 platform
 B.S., Computer Engineering from University
of Utah
Goals







What is POI?
How do I install POI?
What benefits do I get from using POI?
What won't it do?
POI alternatives
Java tips
Lots of code examples
What is POI?
What is Apache Jakarta POI?
 Poor Obfuscation Implementation
 A set of APIs for interacting with OLE 2
Compound Document Format files
Examples: Word (.doc), Excel (.xls), and
PowerPoint (.ppt)
Why Use POI?
Data Import/Export
Why Use POI?
Content Management
Why Use POI?
Reporting
In an Ideal World...
In the Real World...
What other tools?




Save as XML
Save as HTML
COM
OpenOffice.org 2.0
POI is just one more tool
Anatomy of an OLE2CDF File
DirectoryEntry
DirectoryEntry
DocumentEntry
DocumentEntry
DocumentEntry
DocumentEntry
POIFS: At the Core
 POI file system (POIFS) is used to read
and manipulate OLE2CDF files
Package: org.apache.poi.poifs.filesystem
 Two types of stream entries supported:
DirectoryEntry
DocumentEntry
POIFS Examples
 Example: POIFSExample1.cfm
 Example: POIFSExample2.cfm
How Do I Get to the Data?
 APIs to interact with specific document
types and streams
 XLS files (HSSF) 2.5.1
 PPT files (HSLF) 3.0 alpha
 DOC files (HWPF) 2.5.1 scratchpad
 Document props (HPSF) 2.5.1 (read-only)
 2.5.1 final ships with CF 7
 I am using 3.0 alpha for this presentation
How to Install
 For 2.5.1 release:
http://apache.cs.utah.edu/jakarta/poi/release/
 For 3.0 alpha:
http://apache.cs.utah.edu/jakarta/poi/dev/




Scratchpad is not included with CF 7
Stop CF services
Save in {cfwebroot}/WEB-INF/lib
Restart CF services
Quick Java-CF Review
 To use Java classes:
createObject() – loads the class
 createObject(“java”,”fully.qualified.name”)
 Can use for static method calls
obj.init() - creates an instance of the class
using the specified constructor
Java Tip #1
Know your casting tools!
Java Tip #1
 Casting tools
javaCast(type,cfVar)
 boolean, int, long, float, double, string, null
 Null only available in 7
Primitive wrapper classes
 <cfset bType =
createObject(“java”,”java.lang.Byte”)/>
 <cfset aByte = bType.parseByte(cfVar)/>
Array reflection classes
 java.lang.reflect.Array
 Call newInstance(class,int length)
Java Tip #2
 Java reflection is your friend!
Java Tip #2
 obj.getClass() gives you a reference to the
class
String value of name: obj.getClass().getName()
Wrapper classes have a TYPE static variable
to represent primitives.
org.apache.poi.hssf.usermodel
 Interface for reading and writing XLS files.
 Mature and reliable
 Basic class structure is similar to HTML
table
HSSFSheet = table
HSSFRow = tr
HSSFCell = td
 Example: HSSFExample1.cfm
Add some style to the spreadsheet
 HSSFCellStyle,HSSFFont
 Can modify:
Fonts (size and style)
Colors
Background Fills
Borders
Widths and heights
 Example: HSSFExample2.cfm
Reading Spreadsheets




Similar to writing spreadsheets
Requires more care with datatypes!
Null
Example: HSSFExample3.cfm
org.apache.poi.hwpf
 Interface for reading and writing Word
(DOC) files.
 In the scratchpad section of the library.
 Has worked for what I have used it for
(reading files), which is all I will talk about
today.
Structure of a Word Document
Range
Section
Section
Paragraph
Paragraph
Paragraph
CharacterRun
CharacterRun
CharacterRun
CharacterRun
HWPF Example 1
 Example: HWPFExample1.cfm
HWPF Example 2
 More meaningful text
 Examples:
HWPFExample2.cfm
basicDocumentFacade.cfc
HWPF Example 3
 Lists, Style
 Examples:
HWPFExample3.cfm
advancedDocumentFacade.cfc
org.apache.poi.org.apache.poi.hslf
 Interface for reading and writing
PowerPoint (PPT) files.
 Early, but very active development
HSLF Example 1
 Simple Slideshow parser
 Example: HSLFExample1.cfm
HSLF Example 2
 Improved Slideshow parser
XSLT
 Example:
HSLFExample2.cfm
slideShowExtractor.cfc
Limitations
 Available documentation
 Images
 Development activity
Resources
 http://jakarta.apache.org/poi/
 http://www.wotsit.org/
 google “jakarta poi”
Thank you
Jeremy Lund
[email protected]