Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 100 Create Excel Spreadsheets Using Other Environments

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

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-excell
provides details and a demonstration of how to do this.

Simon St.Laurent