Transcript Slide - oscon2006

Working with Microsoft Excel spreadsheets and
Microsoft Access databases in a Java application
Sean Sullivan
July 27, 2006
This presentation is available at
http://oscon2006.sourceforge.net/
Overview
•
•
•
•
•
Introduction
Jakarta POI project
Jackcess project
Demo: web application
Demo: Swing application
Jakarta POI
• Java class library for reading and writing
Microsoft Office documents
• Apache License 2.0
• http://jakarta.apache.org/poi
POI features
• Read/write OLE2 Compound Document Format
• Read/write Microsoft Office documents
• XLS
• PPT
• DOC
• Not supported
• MDB
• VSD
Who is using Jakarta POI?
POI terminology
• POIFS: low-level file I/O
• HSSF: Horrible Spreadsheet Format
• HWPF: Horrible Word-processing Format
• HSLF: Horrible Slide Format
• HPSF: Horrible Property Set Format
• DDF:
Dreadful Drawing Format
Microsoft Excel file format
222 pages!
Working with XLS files
Packages
• org.apache.poi.hssf
• org.apache.poi.hssf.eventusermodel
• org.apache.poi.hssf.usermodel
Core classes
• HSSFWorkbook
• HSSFSheet
• HSSFRow
• HSSFCell
• HSSFCellStyle
Demo: Hello POI
@see HelloPoi.java
Can I use formulas?
Yes!
HSSFCell
• getCellFormula
• setCellFormula("formulaString")
HSSFFormulaEvaluator
• evaluate(HSSFCell)
Other POI topics
• Macros - must load from a template XLS file
• Charts - limited support
• Pivot tables - not supported
Jackcess project
•
•
•
•
•
Java class library for reading and writing MDB files
developed by Health Market Science Inc.
LGPL
requires JRE 1.5 or higher
http://jackcess.sourceforge.net/
Jackcess features
• Read/write Microsoft Access MDB files*
• Copy ResultSet to MDB table
• Copy CSV file to MDB table
* Microsoft Access 2000 only
Jackcess API
Package:
com.healthmarketscience.jackcess
Core classes:
•
Database
•
Table
•
Column
•
DataType
Hello Jackcess
Database db = Database.open(new File("demo.mdb"));
for (String tname: db.getTableNames())
{
Table t = db.getTable(tname);
Map<String, Object> row;
while ((row = t.getNextRow()) != null)
{
for (String colName : row.keySet())
{
System.out.println(row.get(colName));
}
}
}
Web application demo
•
•
•
•
•
•
•
Eclipse 3.2
WebTools 1.5
Subversion 1.3.x
Subclipse 1.1.x
Tomcat 5.5.17
Firefox 1.5.0.5
Maven 2.0.4
•
•
•
•
•
•
•
POI 3.0 alpha 2
Jackcess 1.1.5
OpenCSV 1.5
Wicket 1.2
Derby 10.1.2.1
Log4j 1.2.13
Sun JDK 1.5
Swing demo
• javax.swing.JTable
• javax.swing.table.TableModel
• Jakarta POI
Summary
• Use POI to read/write XLS files
http://jakarta.apache.org/poi/
• Use Jackcess to read/write MDB files
http://jackcess.sourceforge.net/
• This presentation
http://oscon2006.sourceforge.net/