Creating Excel w/ Java and POI

[kpmac]

Here is a convenience class that I created for creating Excel documents using POI. I wrote this at my old job and had planned on installing it over the existing POI Excel implementation but I never had the time. If someone creates an RPG prototype for the methods you will be able to call it from RPG. I know there are plenty of example out there for using POI but I have found this wrapper to be extremely straight forward (my biased opinion) and easy to use.

Feel free to post any questions you may have.

You will need to download the POI jar to use this class.
A really nice addition to this would be a ZIP program to compact the excel sheets before emailing them out.

The attached zip file contains a TestExcel class that serves as an example for creating the excelsheet.

excel.zip

/**
 * @author KpMac
 * @version 1.1.0 
 * Convenience class for creating excel worksheets using the Jakarta POI classes.
 * Author of this Convenience class asuumes zero responsibility for problems that may arise 
 * through its use.  In other words use at your own risk. 
 * T
 */

package biz.vss.excel;

import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DateFormat;
import java.text.ParseException;
import java.util.Date;
import java.util.HashMap;

import org.apache.poi.hssf.model.Workbook;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;

public class ExcelWriter {

    private FileOutputStream fos;

    private HSSFWorkbook workBook;

    private Workbook dataFormatWorkBook;

    private HSSFSheet sheet;

    private HSSFRow row;

    private HSSFFont font = null;

    private int rowNumber = 0;

    private String sheetName;

    private String errorMessage = "";

    private boolean hasError = false;

    private Date dateObject = null;

    private HSSFCellStyle intCellStyle = null;

    private HSSFCellStyle stringCellStyle = null;

    private HSSFCellStyle dollarCellStyle = null;

    private HSSFCellStyle dollar2CellStyle = null;

    private HSSFCellStyle blackDollarCellStyle = null;

    private HSSFCellStyle percentCellStyle = null;

    private HSSFCellStyle dateCellStyle = null;

    private HSSFCellStyle returnStyle = null;

    private HSSFCellStyle newStyle = null;

    private HSSFCellStyle current = null;

    private HSSFDataFormat dataFormat = null;

    private StyleMap styleMap = null;

    private Key key = null;

    /* 
     * Creates a new excel workbook in the specified directory.
     * If the workbook already exists it will be overwritten.
     * It is good practive to append the username and a timestamp to the workbook name.
     */

    public void createWorkBook(String workBookName) {
        workBook = new HSSFWorkbook();

        try {
            fos = new FileOutputStream(workBookName);
        } catch (Exception e) {
            errorMessage = e.getMessage();
        }
    }

    /* 
     * Creates a new worksheet in the workbook. This implementation 
     * can only write to one worksheet at a time.
     */

    public void createSheet(String sheetName) {

        sheet = workBook.createSheet(sheetName);
        this.sheetName = sheetName;
        rowNumber = 0;
        makeRow();

    }

    /* 
     * Moves the cursor down one row
     */

    public void nextRow() {
        rowNumber++;
        makeRow();

    }

    /* 
     * Skips n amount of rows from current row.
     */

    public void nextRow(int rows) {
        rowNumber += rows;
        makeRow();
    }

    /* 
     * Writes an integer value into the cell.
     */

    public void writeInt(int cell, int value) {
        if (intCellStyle == null) {
            intCellStyle = workBook.createCellStyle();
            intCellStyle.setDataFormat(HSSFDataFormat
                    .getBuiltinFormat("(#,##0_);[Red](#,##0)"));
        }
        row.createCell((short) cell).setCellValue(value);
        row.getCell((short) cell).setCellStyle(intCellStyle);
    }

    /* 
     * Writes a string value into the cell
     */

    public void write(int cell, String value) {
        if (stringCellStyle == null) {
            stringCellStyle = workBook.createCellStyle();
            stringCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("@"));

        }
        row.createCell((short) cell)
                .setCellValue(new HSSFRichTextString(value));
        row.getCell((short) cell).setCellType(HSSFCell.CELL_TYPE_STRING);
        row.getCell((short) cell).setCellStyle(stringCellStyle);

    }

    /* 
     * Writes a decimal value into the cell.
     */

    public void writeDouble(int cell, String value) {
        double number = Double.parseDouble(value);
        row.createCell((short) cell).setCellValue(number);
    }

    /* 
     * Writes dollar value into cell and does not show cents.
     */

    public void write$(int cell, String value) {
        if (dollarCellStyle == null) {
            dollarCellStyle = workBook.createCellStyle();
            dollarCellStyle.setDataFormat(HSSFDataFormat
                    .getBuiltinFormat("($#,##0_);[Red]($#,##0)"));
        }

        double dollars = Double.parseDouble(value);
        row.createCell((short) cell).setCellValue(dollars);
        row.getCell((short) cell).setCellStyle(dollarCellStyle);
    }

    /* 
     * Writes value into cell showing Dollars and cents in cell
     */

    public void write$$(int cell, String value) {
        if (dollar2CellStyle == null) {
            dollar2CellStyle = workBook.createCellStyle();
            dollar2CellStyle.setDataFormat(HSSFDataFormat
                    .getBuiltinFormat("($#,##0.00_);[Red]($#,##0.00)"));
        }

        double dollars = Double.parseDouble(value);
        row.createCell((short) cell).setCellValue(dollars);
        row.getCell((short) cell).setCellStyle(dollar2CellStyle);
    }

    /* 
     * Write percent value into cell.  Percents must be passed in as decmials.
     * Do not multiple value by 100 before passing in.
     */

    public void writePct(int cell, String value) {
        try {
            double percent = Double.parseDouble(value);
            if (percentCellStyle == null) {
                percentCellStyle = workBook.createCellStyle();
                percentCellStyle.setDataFormat(HSSFDataFormat
                        .getBuiltinFormat("0.00%"));
            }
            row.createCell((short) cell).setCellValue(percent);
            row.getCell((short) cell).setCellStyle(percentCellStyle);
        } catch (Exception e) {
            errorMessage = e.getMessage();
        }
    }

    /* 
     * Write date value in m/d/yy format
     */

    public void writeDate(int cell, String date) {
        if (dateCellStyle == null) {
            dateCellStyle.setDataFormat(HSSFDataFormat
                    .getBuiltinFormat("m/d/yy"));
        }

        try {
            dateObject = DateFormat.getInstance().parse("01/01/1899");
            dateObject = DateFormat.getInstance().parse(date);
        } catch (ParseException pe) {
            hasError = true;
            errorMessage = pe.getMessage();

        }

        row.createCell((short) cell).setCellValue(dateObject);
        row.getCell((short) cell).setCellStyle(dateCellStyle);
    }

    /* 
     * Write formula into cell
     */

    public void writeFormula(int cell, String formula) {
        row.createCell((short) cell);
        row.getCell((short) cell).setCellType(HSSFCell.CELL_TYPE_FORMULA);
        row.getCell((short) cell).setCellFormula(formula);
    }

    /* 
     * Changes the fore ground Color of the cell
     */

    public void colorCell(int cell, int color) throws Exception {

        if (key == null) {
            key = new Key();
        }

        if (styleMap == null) {
            styleMap = new StyleMap();
        }

        if (dataFormat == null) {
            dataFormatWorkBook = new Workbook();
            dataFormat = new HSSFDataFormat(dataFormatWorkBook);
        }

        current = row.getCell((short) cell).getCellStyle();

        /* If the current cell color equals red overide the data format
         * to display the numbers in black. Red on red is not visible.
         */

        if (dataFormat.getFormat(current.getDataFormat()).equals(
                "($#,##0.00_);[Red]($#,##0.00)")
                || dataFormat.getFormat(current.getDataFormat()).equals(
                        "($#,##0_);[Red]($#,##0)")) {
            if (Color.RED == color) {
                if (blackDollarCellStyle == null) {
                    blackDollarCellStyle = workBook.createCellStyle();
                    blackDollarCellStyle.setDataFormat(HSSFDataFormat
                            .getBuiltinFormat("($#,##0.00);($#,##0.00)"));
                }
                row.getCell((short) cell).setCellStyle(blackDollarCellStyle);
                current = row.getCell((short) cell).getCellStyle();
            }
        }

        key.setDataFormat(dataFormat.getFormat(current.getDataFormat()));
        key.setColor(Color.getColorName(color));
        key.setFontType(Integer.toString(current.getFontIndex()));

        if (styleMap.containsStyle(key.toString())) {
            newStyle = styleMap.getStyle(key.toString());
        } else {
            newStyle = clone(current);
            newStyle.setFillForegroundColor((short) color);

            newStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            styleMap.add(key.toString(), newStyle);
            //            System.out.println(key.toString());

        }

        row.getCell((short) cell).setCellStyle(newStyle);
    }

    /* 
     * Make the contents of the current cells BOLD.
     */

    public void makeBold(int from, int to) {
        for (int i = from; i <= to; i++) {
            makeBold(i);
        }

    }

    /* 
     * Make the contents of the current cell bold.
     */

    public void makeBold(int cell) {
        Key key = new Key();

        if (styleMap == null) {
            styleMap = new StyleMap();
        }

        if (font == null) {
            font = workBook.createFont();
        }

        if (dataFormat == null) {
            dataFormatWorkBook = new Workbook();
            dataFormat = new HSSFDataFormat(dataFormatWorkBook);
        }
        current = row.getCell((short) cell).getCellStyle();

        key.setDataFormat(dataFormat.getFormat(current.getDataFormat()));
        key.setColor(Color.getColorName(current.getFillForegroundColor()));
        key.setFontType(Integer.toString(current.getFontIndex()));

        if (styleMap.containsStyle(key.toString())) {
            newStyle = styleMap.getStyle(key.toString());
        } else {
            newStyle = clone(current);
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            newStyle.setFont(font);
            styleMap.add(key.toString(), newStyle);
            //            System.out.println(key.toString());

        }

        row.getCell((short) cell).setCellStyle(newStyle);

    }

    /* 
     * Freeze the cells at the specified location.
     */

    public void freezePane(int colSplit, int rowSplit, int leftMostColumn,
            int topRow) {
        sheet.createFreezePane(colSplit, rowSplit, leftMostColumn, topRow);
    }

    /* 
     * Merge Text over several cells
     */

    public void mergeCells(int rowFrom, int colFrom, int rowTo, int colTo) {
        sheet.addMergedRegion(new Region(rowFrom, (short) colFrom, rowTo,
                (short) colTo));
    }

    /* 
     * Close the workbook and flush the contents of the output stream.
     * Workbook will not contain information if this method is not called.
     */

    public void closeWorkbook() {
        try {
            workBook.write(fos);
        } catch (IOException ie) {
            System.err.println(ie);
        } finally {
            try {
                styleMap = null;
                fos.flush();
                fos.close();
                fos = null;
            } catch (IOException e) {

                e.printStackTrace();
            }

        }
        //        System.out.println("***Closed***");
    }

    /*
     * Checks to see if an error has Occured 
     */

    public boolean errorExists() {
        return hasError;
    }

    public String gtErrorMessage() {
        return errorMessage;
    }

    /* 
     * This method clones a copy of the current cell.  This methos is a work 
     * around because HSSFCellStyle does not implememnt Clonable.
     */

    private HSSFCellStyle clone(HSSFCellStyle style) {

        returnStyle = workBook.createCellStyle();

        returnStyle.setAlignment(style.getAlignment());
        returnStyle.setBorderBottom(style.getBorderBottom());
        returnStyle.setBorderLeft(style.getBorderLeft());
        returnStyle.setBorderRight(style.getBorderRight());
        returnStyle.setBorderTop(style.getBorderTop());
        returnStyle.setBottomBorderColor(style.getBottomBorderColor());
        returnStyle.setDataFormat(style.getDataFormat());
        returnStyle.setFillBackgroundColor(style.getFillBackgroundColor());
        returnStyle.setFillForegroundColor(style.getFillForegroundColor());
        returnStyle.setFillPattern(style.getFillPattern());
        returnStyle.setFont(workBook.getFontAt(style.getFontIndex()));
        returnStyle.setHidden(style.getHidden());
        returnStyle.setIndention(style.getIndention());
        returnStyle.setLeftBorderColor(style.getLeftBorderColor());
        returnStyle.setLocked(style.getLocked());
        returnStyle.setRightBorderColor(style.getRightBorderColor());
        returnStyle.setRotation(style.getRotation());
        returnStyle.setTopBorderColor(style.getTopBorderColor());
        returnStyle.setVerticalAlignment(style.getVerticalAlignment());
        returnStyle.setWrapText(style.getWrapText());

        return returnStyle;

    }

    /*
     * Creates an excel row. 
     */

    private void makeRow() {
        try {
            if (rowNumber >= 65536) {
                createSheet(this.sheetName + "_"
                        + workBook.getSheetIndex(sheetName) + 1);
            }

            row = sheet.createRow(rowNumber);

        } catch (Exception e) {
            errorMessage = e.getMessage();
        }
    }
}

/*
 * The StyleMap class is a holding location for styles that are created within the workbook.
 * Excel will bark if you create too many styles.  This class provides a container for styles
 * that have been previously created and allows you to access them by key.  
 */

class StyleMap {

    private HashMap styleMap;

    public StyleMap() {
        styleMap = new HashMap();
    }

    public void add(String key, HSSFCellStyle style) {
        styleMap.put(key, style);
    }

    public boolean containsStyle(String key) {
        return styleMap.containsKey(key);
    }

    public HSSFCellStyle getStyle(String key) {
        return (HSSFCellStyle) styleMap.get(key);
    }

}

/*
 * A simple style key for the StyleMap class.  
 */

class Key {

    private String dataFormat;

    private String fontType;

    private String color;

    public Key() {

    }

    public String getDataFormat() {
        return dataFormat;
    }

    public void setDataFormat(String dataFormat) {
        this.dataFormat = dataFormat;
    }

    public String getFontType() {
        return fontType;
    }

    public void setFontType(String fontType) {
        this.fontType = fontType;
    }

    public String getColor() {
        return color;
    }

    public void setColor(String color) {
        this.color = color;
    }

    public boolean equals(Object obj) {
        Key that = (Key) obj;
        return (this.dataFormat == that.dataFormat
                && this.fontType == that.fontType && this.color == that.color);

    }

    public int hashCode() {
        return this.hashCode();
    }

    public String toString() {
        StringBuffer toString = new StringBuffer();
        toString.append(this.dataFormat);
        toString.append(" - ");
        toString.append(this.fontType);
        toString.append(" - ");
        toString.append(this.color);
        return toString.toString();
    }

} 

[bryce4president]

Re: Creating Excel w/ Java and POI

Very nice, very clean. Looks great. Thanks for sharing

I've been staring at java code the last few weeks. I never did any java at all until this job and that is only in the last couple months. And I've learned more in these last few months than you could in 3 classes in college. Gotta love the freedom to learn on the job