Overview of packages
This 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 APIThey use 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 package is used in the PSEC tool. The com.psec.util package contains classes 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: HelloExcel extends WriteExcel so that Area can be referred to without the WriteExcel prefix.
package com.psec.run;
import com.psec.excel.WriteExcel;
public class HelloExcel extends WriteExcel {
public static void log(String sMsg) {System.out.println(sMsg);}
public static Exception e(String s) {return new Exception(s); }
WriteExcel oWE;
public void run(String[] sArgs) throws Exception {
log("HelloExcel writing "+sArgs[0]);
oWE = WriteExcel.create(this,sArgs[0]);
oWE.showNegativeAsRed();
writeSalesSheet();
oWE.close();
oWE = null;
log("HelloExcel completed writing "+sArgs[0]);
}
private void writeSalesSheet() throws Exception {
String sSheet = "sample-sales";
Area oA = oWE.createArea(sSheet,1,1)
.header("{4.#title}Sample Sales Report")
.header("")
.header("Month/Unit Sales/Avg. Price/Revenue","#hdrBlue");
String[] sMonths = "January/February/March/April/May/June/July/August/September/October/November/December".split("/");
double[] dPrices = 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[] nSales = new int[]{15,61,88 ,23,-3,54 ,67,53,21 ,13,23,33};
int[] nQtr = new int[]{0,0,1 ,0,0,2 ,0,0,3 ,0,0,4};
int nQtrSales = 0;
double dQtrRev = 0.0;
int nTotSales = 0;
double dTotRev = 0.0;
for(int i=0,iMax=12; i < iMax; i++) {
oA.addRow(String.format("{:R}%s/%d/%.2f/%.2f",sMonths[i],nSales[i],dPrices[i],dPrices[i] * nSales[i]).split("/"),i+1);
nQtrSales += nSales[i];
dQtrRev += dPrices[i] * nSales[i];
nTotSales += nSales[i];
dTotRev += dPrices[i] * nSales[i];
if (nQtr[i] != 0) {
oA.addRow(String.format("{:Rb}Q%d/%d/%.2f/%.2f",nQtr[i],nQtrSales,dQtrRev / nQtrSales,dQtrRev).split("/"),"#qtr");
nQtrSales = 0;
dQtrRev = 0.0;
}
}
oA.addRow(new String[0]);
oA.addRow(String.format("{:Rb}TOTAL/%d/%.2f/%.2f",nTotSales,dTotRev / nTotSales,dTotRev).split("/"),"#TOT");
oA.writeArea().colWidth(-1,3).addDataFilterLine();
}
}
produces
For those that prefer not to use Hungarian Notation, here is the above example rewritten with plain variable names. It produces the same results.
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);
}
}
}
com.psec.excel.ReadExcel
TheReadExcel 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 showNegativeAsRed
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:
| Order | Effect |
|---|---|
b | bold font |
i | italic font |
s | strike through font |
u | underline font |
nn | Font size in points where nn is an integer |
f | fixed font |
l | Mark as link (blue, underlined) |
- | Total. Single-line top border |
~ | Sub-Total. Double-line top border |
= | Final Total. Double-line top, bottom border |
C | Center text alignment |
L | Left text alignment |
R | Right text alignment |
T | Top vertical alignment |
M | Middle vertical alignment |
B | Bottom 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:- The string being inserted into the cell starts with a {...} format specifier (Specific Style).
- The format of the data (string, integer, number) and matched according to the Generic Types or any added using XXXXXXXXXXXXXX (Data Format).
- The
addRowmethod that provided the Cell has a format specifier or a stripe specifier (Row Style)
- The basic format is chosen according to the Data Format.
- If a Specific Style is present, it is applied.
- If a Row Style is present and there is no Specific Style, the Row Style is applied.
- 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])}
nspecifies the number of columns to merge. Used mostly in headings. By defualt, merged cells are centered..required as a separator if both the merge and format are specified.:formatapply the anonymous format where the string following the : is one or more CellStyle orders.fmt-nameuse the named format that was added with theaddStyleoraddStylesmethod
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.
| Name | Type | Orders | Usage |
|---|---|---|---|
#sub | generic | - | Subtotal |
#tot | generic | ~ | Total |
#fin | generic | = | Final Total |
#lnk | generic | l | Link |
#lkc | generic | lC | Centered link |
#evn | BG color | BG(245,255,245) | Even stripe |
#odd | BG color | BG(245,245,255) | Odd stripe |
#qtr | BG color | BG(255,200,145) | Quarter line BG color |
#TOT | BG color | BG(255,168,80) | Total line BG color |
#hdr | string | bC | Centered bold header |
#hdrBlue | string | bCBG(pale-blue) | Pale-Blue centered bold header |
#title | string | b16C | Large 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 com.psec.exec.WriteExcel#addDataFormat addDataFormat) method. Other
The built-in generic types are:
| Name | Format | Pattern |
|---|---|---|
@int | 0 | ^[0-9]+$ |
@nm1 | 0.0 | ^[0-9][.][0-9]{1}$ |
@num | 0.00 | ^[0-9][.][0-9]{2}$ |
@nm3 | 0.000 | ^[0-9][.][0-9]{3}$ |
@nm4 | 0.0000 | ^[0-9][.][0-9]{4}$ |
@str | null | catch all |
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.