Format table cells in RTF Template like Excel's "accounting" number-format?

Eric Geddes

If you're like me, you've got a RTF template that contains a table of numeric values, and you've been asked to format them "like the 'accounting' number-format from Excel" — with one difference. I'd appreciate some advise on how to make this happen?

From what I can tell, this means:

• a dollar sign aligned to the left margin of the cell;
• when the value is a valid number (i.e. not zero and not null):
  • comma separators at thousands (and beyond);
  • two decimal places with padded zeroes if necessary; and
  • entire column right-aligned to the decimal point.
• when the value is not a valid number (i.e. is zero or null):
  • a single dash, centered on the column's decimal points.

The difference is when the value is null, only the dash should appear — without the dollar sign.

The options as I see them, include:

Single Column with Conditionals and Formatting

Use a single column in table, with XSL (or similar) to vary the result based on the value. (And yes, the XSL could be more clever, but I'm more concerned with the structure of it.)

First, control the dollar sign visibility:

<xsl:if test="number(RATE)">
    <?'$'?>
</xsl:if>

Then, control the value visibility and what gets shown:

<xsl:choose>
    <xsl:when test="RATE='0'">
        <?'-'?>
    </xsl:when>
    <xsl:when test="number(RATE)">
        <?RATE?>
    </xsl:when>
    <xsl:otherwise>
        <?'-'?>
    </xsl:otherwise>
</xsl:choose>

With paragraph formatting that

• aligns to the left;
• has a left-aligned tab stop at 0.0 (for the dollar sign); and
• a decimal-aligned tab stop at (for example) 0.3.

With tabs added judiciously to trigger the tab stops.

[TAB] [dollar sign block] [TAB] [value block]

THE PROBLEM:

In a word: it's nasty.

The Word UI is extremely poorly suited to handle this kind of situation with any precision.

• The tab stops must be hand-coded if you don't want them to automatically snap to the very course ruler indicators.
• The first tab character (before the dollar sign block) isn't visible — but it's there!
• The preview may or may not look anything like the result — or the desired outcome!.

Two Columns with Conditionals and Formatting

Use one column for the dollar sign (and dollar sign conditional), and a second column for the value (and the value conditional)?

THE PROBLEMS:

The column label needs to be centered across merged cells (which is generally, in my experience, not ideal to do in a template — for maintainability — unless absolutely necessary). Heaven forbid you want to need to resize the (apparent) columns — one row in the template will comprise a merged cell, while the one below it does not.

Using "phantom" columns and merged headers becomes "not fun" when there are several in a single table — especially if you need to add a new column or change the widths of them.

An Ideal (but unlikely to exist) Solution

I could get much of the way to the solution (perhaps enough to satisfy my customer) if the number format allowed you to specify what happens when the value is zero or null. This is the approach native Microsoft Word takes ([positive format] ; [negative format] ; [zero format]), but I can't get it to work in the template.

Similarly, if this was just an ordinary Word document we were talking about, we could use a trick with full justification to separate the dollar sign block from the value block. Again, I can't get this approach to carry through when the template is processed.

Any suggestions, recommendations, or remedies?

RichardChan

The first thought that I could think of was to have column with no right boarder that conditionally displays a $ based on whether there is a value or not rather than coding something in the numeric column