Working with Microsoft Excel spreadsheets and Microsoft Access databases in a Java application

1
Working with Microsoft Excel spreadsheets and
Microsoft Access databases in a Java application
Sean Sullivan July 27, 2006
2

• This presentation is available at
• http//oscon2006.sourceforge.net/

3
Overview

• Introduction
• Jakarta POI project
• Jackcess project
• Demo web application
• Demo Swing application

4
Jakarta POI

• Java class library for reading and writing
• Microsoft Office documents
• Apache License 2.0
• http//jakarta.apache.org/poi

5
POI features

• Read/write OLE2 Compound Document Format
• Read/write Microsoft Office documents
• XLS
• PPT
• DOC
• Not supported
• MDB
• VSD

6
Who is using Jakarta POI?
7
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

8
Microsoft Excel file format
222 pages!
9
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

10
Demo Hello POI
_at_see HelloPoi.java
11
Can I use formulas?

• Yes!
• HSSFCell
• getCellFormula
• setCellFormula("formulaString")
• HSSFFormulaEvaluator
• evaluate(HSSFCell)

12
Other POI topics

• Macros - must load from a template XLS file
• Charts - limited support
• Pivot tables - not supported

13
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/

14
Jackcess features

• Read/write Microsoft Access MDB files
• Copy ResultSet to MDB table
• Copy CSV file to MDB table

• Microsoft Access 2000 only

15
Jackcess API

• Package
• com.healthmarketscience.jackcess
• Core classes
• Database
• Table
• Column
• DataType

16
Hello Jackcess
Database db Database.open(new
File("demo.mdb")) for (String tname
db.getTableNames()) Table t
db.getTable(tname) MapltString, Objectgt row
while ((row t.getNextRow()) ! null)
for (String colName row.keySet())
System.out.println(row.get(colName))

17
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

18
Swing demo

• javax.swing.JTable
• javax.swing.table.TableModel
• Jakarta POI

19
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/