An Excel document is a workbook. A workbook in Excel is typically represented by a window in the Excel application. While a document is always exactly one workbook, a workbook can have as many worksheets as you want. A worksheet is the grid you work on. Worksheets are selected using the tabs located at the bottom of the current workbook.
Cells are located inside a worksheet with (row,column) pairs.
So far a typical object tree can be described as follows :
Excel application
|
|--> Workbook - expenses
|
|--> Worksheet - 2001
|
|--> Cells[1][1] = 1000
|--> Cells[2][1] = 1200
|--> Cells[3][1] = 800
|
|--> Worksheet - 2002
|
|--> Cells[10][3] = 1500
|--> Cells[11][3] = 1800
|--> Cells[12][3] = 1300
|
|--> Worksheet - 2003
|
|...
|
|...
|
|--> Workbook - revenue
|
|...
And the object model to access the underlying object model can be described as :
Excel application
|
|--> IWorkbook
|
|--> IWorksheet
|
|-->
A workbook must access one or more worksheets. Since the xlsgen interfaces are meant to be used for the generation of Excel files, the programming interface for a workbook is very thin : you can create a new worksheet, use it, then create another, use it, and so on, until you close the entire workbook to finish your work. This interface is reproduced below :
IXlsWorkbook
{
IXlsWorksheet AddWorksheet(String name); // adds a worksheet with the given name
Close(); // close the workbook being manipulated
}
The worksheet provides direct access to cells content. Because content is either formatted numbers or general strings, the programming interface is again thin and simple :
IXlsWorksheet
{
Label[int row][int col] = String; // a general string
Number[int row][int col] = int; // an integer
Float[int row][int col] = double; // a floating point number
}
In order to put some content in a cell, just pass its location row (1, 2, ...), its location column (1, 2, ...), and the actual content.
A typical generation code would iterate that sequence and produce tables.
That being said, the actual programming interface is a bit richer than that, to take advantage of cell capabilities. This includes formatting, formulas, conditional formatting, merged cells and rich formatting.
Formatting is the ability to apply a given combination of styles to one or more cells. Typically, the style needs to be created, using a combination of number format styles, alignment styles, font styles, border styles and pattern styles. Styles pan the Excel formatting options from a cell context menu. Once the style object is created, it has to be applied in order to be taken into account for all content that will be created afterwards. At any moment, the user can apply one or any other existing style to accomodate an arbitrarily complex formatting requirement.
To apply a style, just call the Apply method from that style object. Below is the short layout of the IXlsStyle programming interface :
IXlsStyle
{
IXlsFont Font; // font tab
IXlsBorders Borders; // border tab
IXlsPattern Pattern; // pattern tab
IXlsAlignment Alignment; // alignment tab
Format = String; // number format tab (example : ###,###.00)
Apply(); // when called, this style becomes the default cell style
}
When content needs to be calculated as a result of a given formula (whether it is numeric or not), xlsgen provides native access to Excel formulas for that matter. Formulas are automatically calculated when the Excel file is opened in Excel. The syntax for formulas is exactly the one for Excel formulas. Here are formula examples :
=SUM(R1C4:R3C4) // calculates the sum of the specified cell range =IF(R1C4 > 5; "yes"; "no") // calculates a content depending on a given condition
When individual cells or cell ranges are referenced, the R1C1 notation is used. RxxxCyyy is one of the two notations supported by Excel, where xxx is a row and yyy is a column.
Putting formulas in cells is done pretty much like for any static content, using services exposed by the current worksheet. Below is the update worksheet interface that adds styles as well as formulas :
IXlsWorksheet
{
Label[int row][int col] = String; // a general string
Number[int row][int col] = int; // an integer
Float[int row][int col] = double; // a floating point number
Formula[int row][int col] = String; // a formula (example : =SUM(R1C4:R3C4) )
IXlsStyle NewStyle(); // creates a new style object
}
Conditional formatting is yet another powerful mean to apply dynamic formatting styles to cells according to some other content or formula. Since conditional formatting has rich properties, it has a full-fledged object to play with, IXlsConditionalFormatting.
A conditional formatting can combine one or more conditional formattings. When such combinations are built, both conditions are evaluated and checked against. In each individual condition, a formula rules the formatting change. That formula is either a full fledged-formula, just like normal Excel formulas, or one of the simple cell-based template conditions that are often used and easy to fill out. The programming interface sums up all this :
IXlsConditionalFormatting
{
IXlsCellCondition CellCondition; // sets a template cell-based condition
IXlsFormulaCondition FormulaCondition; // sets a formula condition
Style = IXlsStyle; // defines the formatting changes that are applied if that condition is valid
IXlsConditionalFormatting AdditionalCondition(); // creates another condition (logical OR)
}
IXlsCellCondition
{
Between(String arg1, String arg2);
NotBetween(String arg1, String arg2);
EqualTo(String arg);
NotEqualTo(String arg);
GreaterThan(String arg);
LessThan(String arg);
GreaterThanOrEqualTo(String arg);
LessThanOrEqualTo(String arg);
}
IXlsFormulaCondition
{
Formula = String;
}
An example of conditional formatting is, for a whole range of cells, put the background color in red if the value is under a given threshold. In order to do that, create the conditional formatting, then use the LessThan(threshold) in the CellCondition properties, create a style with a red background color, and set the style to the conditional formatting. What remains to be done is set that conditional formatting to the range of cells. In order to do that, use one more property exposed by the worksheet, and iterate through all cells.
Merged cells is a special kind of range that pans along a contiguous height of rows, or a contiguous width of columns, or both. A merged cell object must be created, and its width and height must be specified. The merged cells programming interface is given below :
IXlsMergedCells
{
Top = int; // bounding box of the merged cells
Left = int;
Width = int;
Height = int;
Style = IXlsStyle;
Label = String;
Number = int;
Float = double;
Formula = String;
}
The last feature available for labels in cells is rich formatting. It's the ability to indicate more than one formatting style in a single cell. Once a rich formatting object is created, (label, style) pairs are concatenated to represent a final cell.