November 25,  2010

The Excel Converter version 1.0 now exists in a preliminary Beta shape. At this time, the output format for Excel 2003 has a 90% of features implemented.

As it was specified in the use case page, an XslFo file is used as a source for the Excel output. The XslFo document was feed with a Rational DOORS module data.

The Excel output can be generated from the command line, like below :

-fo argument specifies the XslFo input file path, and the -xls argument specifies the Excel output file path.

The template that runs to obtain the XslFo source file, is parsing an Rational DOORS module. Here is a sample of the module below :

The template requires data to be extracted in two different masterpages , named “Table2” and “Table2”. The Excel Converter turns every masterpage into a new sheet with the same name as the masterpage name. Here is the begin of the first masterpage inside the RPE template :

… And here is, in the first half of the screenshot, the begin of the second masterpage :

The last row in the last table contains formulas codes. These codes are specific for the Excel Converter, so the tool knows to interpret that type of codes.

Based on the template structure, RPE generates the XslFo output below :

Font properties are set at paragraph, row, cell, and text level. The Excel Converter is able to handle the font properties set for an element in order to be applied over child text elements; so any text element shall inherit all font properties that were set at higher levels. The properties set at text level have the highest priority.

Cell widths and row heights are also handled. When a cell width property has different values within the same column, the greatest value is considered.  External hyperlink type elements are also supported.

Here is the Excel output file, in the two screenshots below, one for the first sheet, and the second for the second sheet.

In the second sheet, there are cells that contain numbers. They are numbers indeed, and not simple strings, thus there is the possibility of making calculus within the sheet, by using those numbers.

The Excel Converter tool handles the formulas codes that are written in the last row of the last table within the RPE template, and displays the results :

Here are listed some characteristics of the Excel Converter pre-release :

  • Values that are coming from RPE/XslFo and represent numbers, are automatically converted to numbers; so the cells that contain those values can support formulas. Note that consecutive text/inline elements, like “1” and “45.67” are not merged in order to build the number 145.67. These cases are considered and treated like simple string values.
  • Consecutive text/inline elements are always within the same excel cell.
  • A cell in Excel can contain no more than one hyperlink element. This is Excel’s design behavior. In order to maintain that, for example when the XslFo source contains more hyperlinks in a single paragraph, the Excel cell that will contain that hyperlinks shall point to the last handled hyperlink, although the display text of all hyperlinks shall be written in that cell.
  • Colors that are supported are the ones written as strings in RPE : black,  blue, brown, green, maroon, orange, pink, red, violet, white, yellow
  • “For Microsoft Excel 2000 and later, the maximum number of fonts is 512” (source : http://support.microsoft.com/kb/215573/en-us). Thus if the XslFo contains more than 512 different elements with different font formatting settings (which means different property combinations), the Excel output might loose some of these formattings. At this time, Excel Converter supports 6 different font properties : font color, font size, font family, bold, italic, underline. At a brief look, there are about 64 possible combinations for 6 different properties, but considering that, for example, font family can have a full range of values (e.g. Arial, Garamond, Times New Roman etc), the number of possible combinations increases exponentially. Although, it is an almost 100% sure that even an advanced RPE user will not edit a template with more than 512 different font settings.