www.pudn.com > jworkbook-0.3.0.zip > XLWriter.java
/* =================================================
* JWorkbook : data export from Java to spreadsheets
* =================================================
*
* Project Info: http://www.jfree.org/jworkbook/index.html;
* Project Lead: David Gilbert (david.gilbert@object-refinery.com);
*
* (C) Copyright 2001-2003, by Object Refinery Limited.
*
* This library is free software; you can redistribute it and/or modify it under the terms
* of the GNU Lesser General Public License as published by the Free Software Foundation;
* either version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
* without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
* See the GNU Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public License along with this
* library; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330,
* Boston, MA 02111-1307, USA.
*
* -------------
* XLWriter.java
* -------------
* (C) Copyright 2002, 2003, by Object Refinery Limited.
*
* Original Author: David Gilbert (for Object Refinery Limited);
* Contributor(s): -;
*
* $Id$
*
* Changes:
* --------
* 12-Feb-2002 : Version 1 (DG);
* 07-Jul-2003 : Changed GPL --> LGPL, changed package name, updated company name (DG);
*
*/
package org.jfree.workbook.io;
import java.io.BufferedOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Iterator;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.jfree.workbook.Border;
import org.jfree.workbook.Cell;
import org.jfree.workbook.ColumnAttributes;
import org.jfree.workbook.ColumnAttributesManager;
import org.jfree.workbook.JWorkbook;
import org.jfree.workbook.Row;
import org.jfree.workbook.RowAttributesManager;
import org.jfree.workbook.Style;
import org.jfree.workbook.Worksheet;
/**
* This class takes a JWorkbook object and saves it to an XL compatible file format. The POI/HSSF
* library (http://sourceforge.net/projects/poi) is being used to achieve this. POI/HSSF doesn't
* have all the functions we require yet (e.g. formula support) so the .xls file will be missing
* some information. However, as new versions of POI/HSSF are released, this will improve.
*/
public class XLWriter {
/**
* Constructs an XLWriter.
*/
public XLWriter() {
}
/**
* Saves the workbook in an XL compatible file format. This is achieved by converting the
* JWorkbook object to an HSSFWorkbook so that the POI/HSSF project can be used as the basis
* for saving to XL. For more information about POI, see http://sourceforge.net/projects/poi.
*
* @param workbook the workbook.
* @param filename the name of the file.
*
* @throws IOException if there is an I/O problem.
*/
public void saveWorkbook(JWorkbook workbook, String filename) throws IOException {
try {
OutputStream out = new BufferedOutputStream(new FileOutputStream(filename));
HSSFWorkbook hssfWorkbook = createHSSFWorkbook(workbook);
hssfWorkbook.write(out);
out.close();
}
catch (IOException e) {
System.out.println(e.getMessage());
}
}
////////////////////////////////////////////////////////////////////////////////////////////////
/**
* Converts the JWorkbook to an HSSFWorkbook.
*
* @param workbook the workbook.
*
* @return The HSSF workbook.
*/
private HSSFWorkbook createHSSFWorkbook(JWorkbook workbook) {
HSSFWorkbook result = new HSSFWorkbook();
Iterator iterator = workbook.getWorksheetsIterator();
while (iterator.hasNext()) {
Worksheet worksheet = (Worksheet) iterator.next();
createHSSFWorksheet(result, worksheet);
}
return result;
}
/**
* Creates a new sheet in the HSSFWorkbook, based on the supplied worksheet.
*
* @param hssfWorkbook the workbook.
* @param worksheet the worksheet.
*/
private void createHSSFWorksheet(HSSFWorkbook hssfWorkbook, Worksheet worksheet) {
HSSFSheet hssfSheet = hssfWorkbook.createSheet(worksheet.getName());
Iterator iterator = worksheet.getCells().getRowsIterator();
while (iterator.hasNext()) {
Row row = (Row) iterator.next();
createHSSFRow(hssfWorkbook, hssfSheet, worksheet, row);
}
ColumnAttributesManager attributes = worksheet.getColumnAttributesManager();
// default column width (pts as double) : convert it to chars as short...
short w = (short) (attributes.getDefaultColumnWidth() / 4.0);
hssfSheet.setDefaultColumnWidth(w);
RowAttributesManager rowAttrs = worksheet.getRowAttributesManager();
// default row height ...
hssfSheet.setDefaultRowHeightInPoints((float) rowAttrs.getDefaultRowHeight());
// now define the individual column widths...
iterator = attributes.getAttributesIterator();
while (iterator.hasNext()) {
ColumnAttributes ca = (ColumnAttributes) iterator.next();
for (int c = ca.getStartColumn(); c <= ca.getEndColumn(); c++) {
hssfSheet.setColumnWidth((short) c, (short) (ca.getWidth() * 64));
}
}
}
/**
* Creates a new row in the HSSFSheet, based on the cells in the supplied row.
*
* @param hssfWorkbook the workbook.
* @param hssfSheet the HSSF worksheet.
* @param worksheet the worksheet.
* @param row the row.
*
*/
private void createHSSFRow(HSSFWorkbook hssfWorkbook, HSSFSheet hssfSheet,
Worksheet worksheet, Row row) {
HSSFRow hssfRow = hssfSheet.createRow((short) row.getIndex());
// work out the height of the row...
float rowHeight = (float) worksheet.getRowAttributesManager().getHeight(row.getIndex());
hssfRow.setHeightInPoints(rowHeight);
Iterator iterator = row.getCellsIterator();
while (iterator.hasNext()) {
Cell cell = (Cell) iterator.next();
createHSSFCell(hssfWorkbook, hssfRow, worksheet, cell);
}
}
/**
* Creates a new cell in the HSSFRow, based on the contents of the supplied cell.
*
* @param hssfWorkbook the workbook.
* @param hssfRow the row.
* @param worksheet the worksheet.
* @param cell the cell.
*/
private void createHSSFCell(HSSFWorkbook hssfWorkbook, HSSFRow hssfRow,
Worksheet worksheet, Cell cell) {
HSSFCell hssfCell = hssfRow.createCell((short) cell.getColumn());
if (cell.getType() == Cell.LABEL_TYPE) {
hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);
hssfCell.setCellValue(cell.getContent());
}
else if (cell.getType() == Cell.VALUE_TYPE) {
hssfCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
hssfCell.setCellValue(Double.valueOf(cell.getContent()).doubleValue());
}
else if (cell.getType() == Cell.DATE_TYPE) {
hssfCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
hssfCell.setCellValue(Double.valueOf(cell.getContent()).doubleValue());
}
HSSFCellStyle hssfStyle = hssfWorkbook.createCellStyle();
Style style = worksheet.getStyles().getStyle(cell.getRow(), cell.getColumn());
hssfStyle.setAlignment(getXLHorizontalAlignment(style));
hssfStyle.setVerticalAlignment(getXLVerticalAlignment(style));
hssfStyle.setWrapText(style.isWrapText());
//hssfStyle.setFillBackgroundColor(style.getBackgroundColor());
hssfStyle.setBorderTop(getXLBorder(style.getBorder().getTop()));
hssfStyle.setBorderBottom(getXLBorder(style.getBorder().getBottom()));
hssfStyle.setBorderLeft(getXLBorder(style.getBorder().getLeft()));
hssfStyle.setBorderRight(getXLBorder(style.getBorder().getRight()));
hssfCell.setCellStyle(hssfStyle);
}
/**
* A utility method that returns the HSSF horizontal alignment constant that corresponds to
* the horizontal alignment for the specified style.
*
* @param style the style.
*
* @return The HSSF alignment.
*/
private short getXLHorizontalAlignment(Style style) {
short result = HSSFCellStyle.ALIGN_GENERAL;
int align = style.getHorizontalAlignment();
if (align == Style.ALIGN_GENERAL) {
result = HSSFCellStyle.ALIGN_GENERAL;
}
else if (align == Style.ALIGN_LEFT) {
result = HSSFCellStyle.ALIGN_LEFT;
}
else if (align == Style.ALIGN_RIGHT) {
result = HSSFCellStyle.ALIGN_RIGHT;
}
else if (align == Style.ALIGN_CENTER) {
result = HSSFCellStyle.ALIGN_CENTER;
}
else if (align == Style.ALIGN_CENTER_ACROSS_SELECTION) {
result = HSSFCellStyle.ALIGN_CENTER_SELECTION;
}
else if (align == Style.ALIGN_JUSTIFY_HORIZONTAL) {
result = HSSFCellStyle.ALIGN_JUSTIFY;
}
else if (align == Style.ALIGN_FILL) {
result = HSSFCellStyle.ALIGN_FILL;
}
return result;
}
/**
* A utility method that returns the HSSF vertical alignment constant that corresponds to
* the vertical alignment for the specified style.
*
* @param style the style.
*
* @return The alignment.
*/
private short getXLVerticalAlignment(Style style) {
short result = HSSFCellStyle.VERTICAL_TOP;
int align = style.getVerticalAlignment();
if (align == Style.ALIGN_TOP) {
result = HSSFCellStyle.VERTICAL_TOP;
}
else if (align == Style.ALIGN_MIDDLE) {
result = HSSFCellStyle.VERTICAL_CENTER;
}
else if (align == Style.ALIGN_BOTTOM) {
result = HSSFCellStyle.VERTICAL_BOTTOM;
}
else if (align == Style.ALIGN_JUSTIFY_VERTICAL) {
result = HSSFCellStyle.VERTICAL_JUSTIFY;
}
return result;
}
/**
* A utility method that returns the HSSF border constant that corresponds to
* the border for the specified style.
*
* @param style the style.
*
* @return The border constant.
*/
private short getXLBorder(int style) {
short result = HSSFCellStyle.BORDER_NONE;
if (style == Border.NONE) {
result = HSSFCellStyle.BORDER_NONE;
}
else if (style == Border.DOTTED) {
result = HSSFCellStyle.BORDER_DOTTED;
}
else if (style == Border.DASHED1) {
result = HSSFCellStyle.BORDER_DOTTED;
}
else if (style == Border.DASHED2) {
result = HSSFCellStyle.BORDER_DASHED;
}
else if (style == Border.DASHED3) {
result = HSSFCellStyle.BORDER_DASH_DOT;
}
else if (style == Border.DASHED4) {
result = HSSFCellStyle.BORDER_DASH_DOT_DOT;
}
else if (style == Border.LINE) {
result = HSSFCellStyle.BORDER_THIN;
}
else if (style == Border.THICK_DASHED1) {
result = HSSFCellStyle.BORDER_MEDIUM;
}
else if (style == Border.THICK_DASHED2) {
result = HSSFCellStyle.BORDER_MEDIUM_DASHED;
}
else if (style == Border.THICK_DASHED3) {
result = HSSFCellStyle.BORDER_MEDIUM_DASH_DOT;
}
else if (style == Border.THICK_DASHED4) {
result = HSSFCellStyle.BORDER_MEDIUM_DASH_DOT_DOT;
}
else if (style == Border.THICK_LINE) {
result = HSSFCellStyle.BORDER_THICK;
}
else if (style == Border.EXTRA_THICK_LINE) {
result = HSSFCellStyle.BORDER_THICK;
}
else if (style == Border.DOUBLE) {
result = HSSFCellStyle.BORDER_DOUBLE;
}
return result;
}
}