sullivan poi jackcess oscon2006 by EHYMl7

VIEWS: 10 PAGES: 19

									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         •   POI 3.0 alpha 2
•   WebTools 1.5        •   Jackcess 1.1.5
•   Subversion 1.3.x    •   OpenCSV 1.5
•   Subclipse 1.1.x     •   Wicket 1.2
•   Tomcat 5.5.17       •   Derby 10.1.2.1
•   Firefox 1.5.0.5     •   Log4j 1.2.13
•   Maven 2.0.4         •   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/

								
To top