PIAB has the ability to bring data from spreadsheets into reports. This is a reference guide to these features.
PIAB can read data tables from spreadsheets. It provides features to allow you to format the output so it appears the way you want in reports. Firstly, it attempts to replicate the look of the spreadsheet table e.g. text and background colors, text alignment etc. Secondly it provides a way of overriding the formatting to provide, for example, conditional formatting.
PIAB reads cell style and format information directly from the spreadsheet, which is returned as follows.
Item | Description |
---|---|
type | E.g. 'number' or 'date' |
excel_color | The text color in HTML format. |
excel_backgroundcolor | The cell background in HTML format. |
excel_borders | The excel cell borders converted to CSS format. |
excel_originalnumberformat | The original number or date format set in the spreadsheet. |
excel_numberformat | A translated number format for use in the output report. |
excel_textalign | Text alignment e.g. 'left', 'centre', 'right'. |
excel_style | Some font styles in CSS format: font-weight, font-style. |
Yan override these implicit values with explicit ones using the PIAB inline attribute feature. E.g. the following cell would have return a value of 100.1, with white text on a red background:
100.1 [attribute color="White"][attribute backgroundcolor="Red"]
The set of inline attributes is as follows:
Item | Example | Description |
---|---|---|
inline_color | [attribute color=“#FF0000”] | The text color in HTML format. |
inline_backgroundcolor | [attribute backgroundcolor=“#FF0000”] | The cell background in HTML format. |
inline_borders | [attribute border-top:1px solid #000000;“] | The cell borders in CSS format. |
inline_textalign | [attribute textalign=“left”] | Text alignment e.g. 'left', 'centre', 'right'. |
inline_style | [attribute style=“font-size:12pt;”] | CSS Styles |
inline_class | [attribute class=“myclass” | CSS Class |
Spreadsheets like Excel have a set of format codes that you can apply to cells to format numerical data e.g. using accountancy formats. They also have many ways to format dates e.g. standard formats for a region, or custom ones for a specific task. In many cases you will want to translate these specific date formats for suitable display in a report. PIAB provides a way to let you identify a spreadsheet-specific format string and convert it to a suitable output format for display in its HTML reports.
For example, one of the accounting formats in Excel looks like this…
"£"#,##0.00;[Red]"£"#,##0.00
…and PIAB will replace it with the following in the report…
£#,##0.00
The following Excel date format…
DD MM YYYY
…is replaced with the output display format…
dd MM yyyy
These replacements can be specified by clicking the 'Options' link the 'Project Summary Information' form. The can also be permanently set in the the Excel plugin config file:
[Install Folder]\plugins\excelgembox.cfg
The format is…
<config> <options> <!-- Date Formats --> <celldateformats> <cellformat from='M/D/YY' to='M/d/yy'/> <cellformat from='ddd\ dd\ mmm\ \ yy' to='ddd dd MMM yy'/> </celldateformats> <!-- Number Formats --> <cellnumberformats> <cellformat from='_-"£"* #,##0_-;\-"£"* #,##0_-;_-"£"* "-"_-;_-@_-' to='£#,###0'/> <cellformat from='#,##0_ ;[Red]\-#,##0\' to='#,###0'/> <cellformat from='_(#,##0_);[Red](#,##0)' to='#,###0'/> </cellnumberformats> </options> </config>
PIAB includes a number of special cell directives e.g.
Item | Description |
---|---|
[url] | Display the cell contents as a clickable URL. |
[img] | The cell contains the HTTP source of an image to be displayed. |
[exclude] | Exclude this cell. |
[formatonly] | Only display the cell format, not the value. |
You can tell PIAB to use or not to use the various cell styles, both at project and portfolio level. This is useful e.g. for using explicit, conditional formatting at project report level, but using implicit styling at portfolio report level. To use this features, one cell must contain the directive:
[project <instruction> <instruction>..<instruction>]
or
[portfolio <instruction> <instruction>..<instruction>]
Where the instructions are:
Instruction | Description |
---|---|
include | Include the table at this level i.e. project, portfolio. |
exclude | Exclude the table at this level. |
color | Include the text color. |
backgroundcolor | Include the background color. |
borders | Include the borders. |
textalign | Include the text align style. |
style | Include the CSS style. |
class | Include the CSS class. |
nocolor | Exclude the text color. |
nobackgroundcolor | Exclude the background color. |
noborders | Exclude the borders. |
notextalign | Exclude the text align style. |
nostyle | Exclude the CSS style. |
noclass | Exclude the CSS class. |
For example, the following directive would tell PIAB to exclude the table from the project report, but include it in the portoflio report, but ignoring the text color and cell borders from the spreadsheet.
[project exclude][portfolio include nocolor noborders]
PIAB can merge table data from spreadsheets to create an aggregated portfolio table. In order to merge data from tables from different projects, the following criteria must be met:
Note that there are two kind of aggregations: merging and appending. In merging, the cells of equivalent tables are merged according to the instructions e.g. add, min, max. In appending, an output table is built up by appending the rows from each of the equivalent tables.
To merge two or more tables from different project, your spreadsheet tables need to be laid out as in this example format:
[portfolio include] [hide] | Value [label] | Item A | Item B | Item C |
---|---|---|---|---|
[add] | Add Example | 100 | 200 | 300 |
[multiply] | Multiply Example | 5 | 6 | 7 |
[average] | Average Example | 0.2 | 0.2 | 0.3 |
[min] | Min Example | 70 | 71 | 72 |
[max] | Max Example | 70 | 71 | 72 |
[exclude] | Exclude Example | A | B | C |
[first] | First Example | E | F | G |
To append rows from two or more tables from different project, your spreadsheet tables need to be laid out as in this example format:
[portfolio treatment] [hide] [remove blank rows] | Value [label] | Item A | Item B | Item C [sort asc] |
---|---|---|---|---|
[append row] | Name 1 | 100 | 200 | 300 |
[append row] | Name 2 | 5 | 6 | 7 |
[append row] | Name 3 | 0.2 | 0.2 | 0.3 |
Other sort instructions:
There are various ways to specify the cell ranges of spreadsheet tables in the Project Summary Information form. These create report tables with different name formats. If you specifiy a 'Description' for the table when you define it, then this description value is used to identify the table for merging. Otherwise, the name is generated as in the following table:
Type | Example | Report Table Name |
---|---|---|
A worksheet and cell range | “Export Data” “A1:D5” | ”spreadsheet.xls (Export Data.A1:D5)“ |
A named range in the spreadsheet | “NamedRange1” | ”spreadsheet.xls (NamedRange1)“ |
A 'Template Range' | ”!Portfolio Risk“ | “Porfolio Risk” |
Note the use of the ! character in the 'Template Range' name example. A 'Template Range' is one that you specify in the project template itself. It is a definition of a cell range in the spreadsheet, for example:
<spreadsheetnamedranges> <range name="Portfolio Risk" worksheet="export data" cellrange="A1:D9" /> .. </spreadsheetnamedranges>