WriteExcel - POI Excel Utilities
home      Version: 1.0.2

This document is an extract of the Javadoc for this package found at WriteExcel Javadocs

Overview of packages

This WriteExcel package contains classes that simplify using the Apache POI Excel interface. The package com.psec.excel contain the classes that directly interface with the POI API. They wrap the org.apache.poi.ss.usermodel found at Apache POI API

The WriteExcel package uses formatted strings as the primary way of passing information to the support methods which interpret the strings and issue the necessary POI method calls.

This utility is used in the examples of the PSEC Utility Package. Consequently this distribution zip contains classes belonging to the package com.psec.util that are used by that interface. They are not required to use the com.psec.excel package.

WriteExcel Description

WriteExcel creates a workbook using a simple API. One or more Area class instances are used to specify the portion of a Sheet to populate and with what.

In addition, an existing Workbook can be used as a template source so that sheets can be copied and then left intact, modified and/or supplemented.

The creation of Workbooks containing charts is supported by using an existing Workbook as a template that contains one or more charts and using WriteExcel to modify the data that the chart refers to.

The following sample program demonstrates the ease of writing a sheet such as this sales report.

Note: HelloExcelNHN extends WriteExcel so that Area can be referred to without the WriteExcel prefix. 

package com.psec.run;
import  com.psec.excel.WriteExcel;

public class HelloExcelNHN extends WriteExcel {

  public static void log(String msg) {System.out.println(msg);}
  public static Exception e(String s) {return new Exception(s); }
  WriteExcel wrtExcel;

  // ---------------------- Mainline ----------------------
  public void run(String[] args) throws Exception {
    log("HelloExcel writing "+args[0]);
    wrtExcel = WriteExcel.create(this,args[0]);
    wrtExcel.setNegativeFormat(true,"");

    writeSalesSheet();

    wrtExcel.close();
    wrtExcel = null;
    log("HelloExcel completed writing "+args[0]);
  }

  private void writeSalesSheet() throws Exception {

    String sheet = "sample-sales";
    Area area = wrtExcel.createArea(sheet,1,1)
      .header("{4.#title}Sample Sales Report")
      .header("")
      .header("Month/Unit Sales/Avg. Price/Revenue","#hdrBlue");

    String[] months = "January/February/March/April/May/June/July/August/September/October/November/December".split("/");
    double[] prices = new double[]{10.01,11.02,15.03 ,9.04,10.05,17.06 ,22.07,23.08,14.09 ,12.10,13.11,18.12};
    int[]    sales  = new int[]{15,61,88 ,23,-3,54 ,67,53,21 ,13,23,33};
    int[]    qtr    = new int[]{0,0,1 ,0,0,2 ,0,0,3 ,0,0,4};

    int    qtrSales = 0;
    double qtrRev   = 0.0;
    int    totSales = 0;
    double totRev   = 0.0;
    for(int i=0,iMax=12; i < iMax; i++) {
      area.addRow(String.format("{:R}%s/%d/%.2f/%.2f",months[i],sales[i],prices[i],prices[i] * sales[i]).split("/"),i+1);
      qtrSales += sales[i];
      qtrRev   += prices[i] * sales[i];
      totSales += sales[i];
      totRev   += prices[i] * sales[i];
      if (qtr[i] != 0) {
        area.addRow(String.format("{:Rb}Q%d/%d/%.2f/%.2f",qtr[i],qtrSales,qtrRev / qtrSales,qtrRev).split("/"),"#qtr");
        qtrSales    = 0;
        qtrRev      = 0.0;
      }
    }
    area.addRow(new String[0]);
    area.addRow(String.format("{:Rb}TOTAL/%d/%.2f/%.2f",totSales,totRev / totSales,totRev).split("/"),"#TOT");
    area.writeArea().colWidth(-1,3).addDataFilterLine();
  }

  public static void main(String[] args) {
    try {
      HelloExcelNHN helloExcel = new HelloExcelNHN();
      helloExcel.run(args);
    } catch (Exception e) {
      log("HelloExcel Croaked:"+e);
    }
  }
}

produces

Internally, WriteExcel is written using Hungarian Notation. Press HUNGARIAN to view the above example rewritten in Hungarian notation. It produces the same results.

com.psec.excel.ReadExcel

The ReadExcel class used to read an existing Excel Workbook and convert the cells into an array of Java objects.

The reading routines contain methods to specify the data scope and the column filters to apply.

The objects thus created can be passed to other processing routines (including routines that employ WriteExcel);

CellStyle Notions and Management

Excel is fussy about whether the data matches the Cell format. WriteExcel inspects the data being written to a cell and classifies it as a string, integer or number. Numbers are further classified as to how many decimal points there are and the appropriate internal style flag of nm1, num, nm3 or nm4 is applied. This classification is used to populate the cell with the correct CellStyle format. Furthermore, by default, integers and numbers are aligned to the right of a cell.

Numbers and integers will be shown in red when they are negative if method setNegativeFormat is called once before the first call to createArea

Other cell attributes such as font size, weight or color are specified using a simple syntax described in the CellStyle orders section.

If a cell style specification populates only the background color its use will will be merged with the primary cell style to produce a cell that is highlighted with a certain color. The resultant combination of CellStyle objects is managed to minimize the number of CellStyle objects stored in the Workbook.

Cell Style Orders Syntax

The cell style is specified as a string containing an unsequenced set of case-sensitive orders. Each order can optionally be separated by one or more spaces.

The orders are:
OrderEffect
bbold font
iitalic font
sstrike through font
uunderline font
nnFont size in points where nn is an integer
ffixed font
lMark as link (blue, underlined)
-Total. Single-line top border
~Sub-Total. Double-line top border
=Final Total. Double-line top, bottom border
CCenter text alignment
LLeft text alignment
RRight text alignment
TTop vertical alignment
MMiddle vertical alignment
BBottom vertical alignment
FG(r,g,b)Font color specified as RGB r, g and b integers
BG(r,g,b)Background color specified as RGB r, g and b integers.
When the only order, style will be combined with other styles as the Cell is created.
FF(name)Font family name
CE(str)Call custom exit.
Used to add custom style attributes not covered by these orders.
The str can contain a mix of alphanumeric characters as well as the - or _ characters.
The str value will be passed to customExit.

Note RGB colors can also be specified as the strings defined in the POI IndexedColor Enum. The specified strings are folded to uppercase and the - character replaced by the _ character. For example dark-green is equivalent to DARK_GREEN.

Choosing a Cell Style

The choice for a style uses several sources:
  1. The string being inserted into the cell starts with a {...} format specifier (Specific Style).
  2. The format of the data (string, integer, number) and matched according to the Generic Types or any added using XXXXXXXXXXXXXX (Data Format).
  3. The addRow method that provided the Cell has a format specifier or a stripe specifier (Row Style)
These sources are combined according to the following rules:
  1. The basic format is chosen according to the Data Format.
  2. If a Specific Style is present, it is applied.
  3. If a Row Style is present and there is no Specific Style, the Row Style is applied.
  4. If a Row Style is present and it only supplies a BG color and there is not yet a BG color specified through (2) or (3), the Row Style BG Color is applied.

Format Specifier Parsing

As the string for each cell being written is processed it is first inspected for at format specifier at the beginning of the string. If found, it is parsed according to the following rules and removed from the value written to the Cell.

{[n][.]([:format]|[fmt-name])}

  1. n specifies the number of columns to merge. Used mostly in headings. By defualt, merged cells are centered.
  2. . required as a separator if both the merge and format are specified.
  3. :format apply the anonymous format where the string following the : is one or more CellStyle orders.
  4. fmt-name use the named format that was added with the addStyleDefn method

Examples

{:bC} - anonymous, Bold and center cell
{3.#tot} - merge 3 cells and apply the #tot builtin style
{4} - merge 4 cells, center

Built-in Style Names

The following styles are available. They only result in CellStyle objects being added to the Workbook when they are referenced.

The ones marked generic are combined with the current set of Generic Types.

The ones marked BG color are capable of adding a background to a Cell when used as a Row Specifier as described in Choosing a Cell Style.
NameTypeOrdersUsage
#subgeneric-Subtotal
#totgeneric~Total
#fingeneric=Final Total
#lnkgenericlLink
#lkcgenericlCCentered link
#evnBG colorBG(245,255,245)Even stripe
#oddBG colorBG(245,245,255)Odd stripe
#qtrBG colorBG(255,200,145)Quarter line BG color
#TOTBG colorBG(255,168,80)Total line BG color
#hdrstringbCCentered bold header
#hdrBluestringbCBG(pale-blue)Pale-Blue centered bold header
#titlestringb16CLarge font title line

Standard Data Formats

Standard types are combined with other styles at run time. The Data Formats are determined by pattern matching against the data being stored into the cell.

Data Formats can be added using the See addDataFormat method.

The built-in generic types are:

NameFormatPattern
@int0^[0-9]+$
@nm10.0^[0-9][.][0-9]{1}$
@num0.00^[0-9][.][0-9]{2}$
@nm30.000^[0-9][.][0-9]{3}$
@nm40.0000^[0-9][.][0-9]{4}$
@strnullcatch all
See addDataFormat to augment this set.

The Area class

The Area class instance is used to specify what parts of a Sheet are to be populated.

More than one Area can be specified for each Sheet but they should not overlap each other.

The exception to the overlapping restriction above is the use of cells that are merged. Provided the Area with the overlapping merge order is written last it can overlap into previously written Areas. This functionality is most commonly used in top of page headings.

The Area specifies the Sheet name (which is created if it does not exist when the Area is written). It also specified the base row and column. All references to the row or column index with an Area are relative to base 0.

The getBaseRow and getBaseCol methods will add the bias in calculating the absolute position of the row or column on the sheet.
@author Steve Pritchard Original 2019


Powered by GAEL (c) 2019 authored by Steve Pritchard of Rexcel Systems Inc and licensed under a
Creative Commons Attribution-ShareAlike 3.0 Unported License.
Creative Commons License email address