|   Although Excel and other spreadsheet programs are the traditional interfaces for creating and reading spreadsheets, sometimes you might need to create .xls files directly from other programs . Whether you're creating Excel files from databases on an Apache server or you just want to present the information stored in a Java program to Excel users, there are a variety of packages out there that will enable you to create and access .xls files without actually using Excel. Although these packages are frequently more difficult to use than the SpreadsheetML described in [Hack #96] and [Hack #97], they generally produce files readable by any version of Excel since Excel 97 (including Macintosh versions of Excel), and often support more Excel features as well. Some of the more popular (free) packages for working with Excel data include the following: -
- Spreadsheet::WriteExcel
-
A Perl package for creating Excel documents, available at http://search.cpan.org/dist/Spreadsheet-WriteExcel/ -
- Spreadsheet::ParseExcel
-
A toolkit that enables Perl programs to read Excel files, available at http://search.cpan.org/~kwitknr/Spreadsheet-ParseExcel-0.2602/ -
- Jakarta POI
-
A product of the Apache Project that provides both read and write access to Excel spreadsheets through a Java API, available at http://jakarta.apache.org/poi/index.html -
- JExcelApi
-
A Java API for reading and writing Excel spreadsheets that includes Excel-to-CSV and Excel-to-XML converters, available at http://www.andykhan.com/jexcelapi/ You also can automate Excel in various ways, often through the use of Microsoft's .NET Framework. To see an example of how to do this in the C# language, visit http://www.eggheadcafe.com/articles/20021012.asp. As an example of how this process works, the Java code in Example 8-7 will generate an Excel spreadsheet using the POI API. (Even if you aren't a Java programmer, you'll probably get the idea.) You can skip reading the license, though it's required to be included in the code. Example 8-7. Java code for generating an Excel spreadsheet with POI // This code is derived from the org.apache.poi.hssf.dev.HSSF class, // hence the long license. /* ==================================================================== * The Apache Software License, Version 1.1 * * Copyright (c) 2003 The Apache Software Foundation. All rights * reserved. * * Redistribution and use in source and binary forms, with or without * modification, are permitted provided that the following conditions * are met: * * 1. Redistributions of source code must retain the above copyright * notice, this list of conditions and the following disclaimer. * * 2. Redistributions in binary form must reproduce the above copyright * notice, this list of conditions and the following disclaimer in * the documentation and/or other materials provided with the * distribution. * * 3. The end-user documentation included with the redistribution, * if any, must include the following acknowledgment: * "This product includes software developed by the * Apache Software Foundation (http://www.apache.org/)." * Alternately, this acknowledgment may appear in the software itself, * if and wherever such third-party acknowledgments normally appear. * * 4. The names "Apache" and "Apache Software Foundation" and * "Apache POI" must not be used to endorse or promote products * derived from this software without prior written permission. For * written permission, please contact apache@apache.org. * * 5. Products derived from this software may not be called "Apache", * "Apache POI", nor may "Apache" appear in their name, without * prior written permission of the Apache Software Foundation. * * THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED * WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES * OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE * DISCLAIMED. IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR * ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF * USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT * OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF * SUCH DAMAGE. * ==================================================================== * * This software consists of voluntary contributions made by many * individuals on behalf of the Apache Software Foundation. For more * information on the Apache Software Foundation, please see * <http://www.apache.org/>. */ import java.io.*; import java.util.Random; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.apache.poi.hssf.record.*; import org.apache.poi.hssf.model.*; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.hssf.util.*; public class PoiDemo { public static void main (String[] args) throws Exception { short rownum; // create a destination file FileOutputStream out = new FileOutputStream("zingot.xls"); // create a new workbook object; note that the workbook // and the file are two separate things until the very // end, when the workbook is written to the file. HSSFWorkbook wb = new HSSFWorkbook( ); // create a new worksheet HSSFSheet ws = wb.createSheet( ); // create a row object reference for later use HSSFRow r = null; // create a cell object reference HSSFCell c = null; // create two cell styles - formats //need to be defined before they are used HSSFCellStyle cs1 = wb.createCellStyle( ); HSSFCellStyle cs2 = wb.createCellStyle( ); HSSFDataFormat df = wb.createDataFormat( ); // create two font objects for formatting HSSFFont f1 = wb.createFont( ); HSSFFont f2 = wb.createFont( ); //set font 1 to 10 point bold type f1.setFontHeightInPoints((short) 10); f1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //set font 2 to 10 point red type f2.setFontHeightInPoints((short) 10); f2.setColor( (short)HSSFFont.COLOR_RED ); //for cell style 1, use font 1 and set data format cs1.setFont(f1); cs1.setDataFormat(df.getFormat("#,##0.0")); //for cell style 2, use font 2, set a thin border, text format cs2.setBorderBottom(cs2.BORDER_THIN); cs2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); cs2.setFont(f2); // set the sheet name in Unicode wb.setSheetName(0, "Test sheet", HSSFWorkbook.ENCODING_UTF_16 ); // create a sheet with 10 rows (0-9) for (rownum = (short) 0; rownum < 10; rownum++) { // create a row r = ws.createRow(rownum); //r.setRowNum(( short ) rownum); // create six cells (0-5) (the += 2 becomes apparent later for (short cellnum = (short) 0; cellnum < 6; cellnum += 2) { // create a numeric cell c = r.createCell(cellnum); // fill with numbers based on position c.setCellValue(rownum * 10 + cellnum + (((double) rownum / 10) + ((double) cellnum / 100))); // create a string cell c = r.createCell((short) (cellnum + 1)); // on every other row (this is why +=2) if ((rownum % 2) == 0) { // set this cell to the first cell style we defined c.setCellStyle(cs1); // set the cell's string value to "Test" c.setEncoding( HSSFCell.ENCODING_UTF_16 ); c.setCellValue( "Test" ); } else { c.setCellStyle(cs2); // set the cell's string value to "1... 2... 3..." c.setEncoding( HSSFCell.ENCODING_UTF_16 ); c.setCellValue( "1... 2... 3..." ); } } } // use some formulas // advance a row rownum++; r = ws.createRow(rownum); //create formulas. for (short cellnum = (short) 0; cellnum < 6; cellnum += 2) { //produce SUMs for appropriate columns int column= 65+cellnum; char columnLabel=(char)column; String formula="SUM("+columnLabel+"1:"+columnLabel+"10)"; c = r.createCell(cellnum); c.setCellStyle(cs1); c.setCellFormula(formula); } // write the workbook to the output stream, // remembering to close our file wb.write(out); out.close( ); } } To run this code, you must first download the latest POI binary file and put the main POI jar file ( poi-2.0-final-20040126.jar in this case) on your classpath, as appropriate to the platform on which you run it. When run, it takes no arguments and creates a single file, called zingot.xls . If you open that file, you'll see a spreadsheet such as the one in Figure 8-28 (or Figure 8-29, if you're using a Macintosh). Figure 8-28. Spreadsheet created from a Java program in Excel for Windows  Figure 8-29. Spreadsheet created from a Java program in Excel for Macintosh  The logic in Example 8-7 is hardly an exemplary model of how to create a spreadsheet, but it shows off the basic functionality needed to create new sheets, cells, and formulas. If you use this to build spreadsheets for your own applications, you'll undoubtedly replace the loops with references to the data structures you're presenting, the destination files will be more logical and probably will vary depending on the data, and you might take advantage of more features than the basics shown here. One other feature of POI is particularly worth noting if you're generating spreadsheets that are going to be part of a dynamically generated web site. You can combine POI with Cocoon, a Java framework also from Apache, that uses XML documents and other sources to generate content accessible to web browsers. An XML.com article at http://www.xml.com/pub/a/2003/01/22/cocoon-excel.html provides details and a demonstration of how to do this. Simon St.Laurent |
