Re: support for repeating format code in calc

Hi Noel,

Sorry, this took too long..

On Wednesday, 2012-04-18 21:01:41 +0100, Noel Power wrote:

> I've been looking at providing support for the repeating character
> in number formats. I've been playing abit with the code and what I
> have sofar seems to work reasonably well ( no doubt there are plenty
> of edge cases still to be discovered )  But.. life would be simpler
> I think if we had just stored a plain format code like docx seems to
> rather than the fancy pants xml that's there now :/

Well, try to exactly specify that format code syntax for an open
standard, with all it's quirks MS may have hidden.. ironically even
OOXML didn't attempt that. Yes, _they_ got away with that..

> thus I need
> some input at the very least on persisting the format code to odf.

Note that Excel's '*' definition is a bit "unsharp":

http://office.microsoft.com/en-us/excel-help/number-format-codes-HP005198679.aspx
Text and spacing, Repeating characters:

| To repeat the next character in the format to fill the column width,
| include an asterisk (*) in the number format. For example, type 0*- to
| include enough dashes after a number to fill the cell, or type *0 before
| any format to include leading zeros.

So "fill the column width" actually knows (I guess?) three conditions:
* start of text: fill from left border to text
* in text: split text in two, left justify first part, right justify
  second part, fill with character in between
* end of text: fill from text to right border
  (this I only guess, does Excel do that?)

Whatever Excel does nowadays if more than one * are present, I hope it
still bails out with an error. And what if it occurs in date/time format
codes?

I assume also left/centered/right justification affects things for
leading/trailing fills.

Given that I think we'd need a number:fill-character or some such
attribute to go with
16.27 Data Styles
http://docs.oasis-open.org/office/v1.2/cs01/OpenDocument-v1.2-cs01-part1.html#__RefHeading__1416346_253892949
with the limitation that it can be present only once (if that is what
Excel does) and specyfing what happens in these three conditions.

Your number:repeated does fine, but may not survive ODF-speak (neither
may my number:fill-character ;-) as someone might ask "how many times to
be repeated".


> Currently I have implemented a pretty simple change that introduces
> a new child element to the number-style definition ( see
> 0002-xxxxx.patch attached ) Talking to Kohei he says that you
> investigated those special financial formats that use the repeat
> code at some point and I wondered if you had any input, ideas or
> comments.

Well, yes, some years ago.. ;) but not specifically related to those
spread/filled financial formats. Btw, IIRC this is not restricted to
currency formats but a general feature in Excel format codes. (is it?)

> I'm not at all sure about the xml choice I made and would
> be happy to take on board any suggestions for that or.... perhaps
> you might have a sneaky way to avoid an odf change/addition
> altogether.  Also I worry a little that there seemed to be at one
> point support for the '*' format code, I wonder what the history was
> or is it just a case of unfinished business?

That is a remainder of old times.. the format code scanner is able to
handle that (and it should), but support at view level never existed in
this constellation. I think there was back in early 90s Pascal times..
when the formatter was part of StarCalc 1.0 ...

> Note: I partially
> re-enabled that for the basic xls import support ( see 
> http://cgit.freedesktop.org/libreoffice/core/commit/?id=46e18bc915ac94a81462642cc9d56d29c04d0c46
> and follow up patch 
> http://cgit.freedesktop.org/libreoffice/core/commit/?id=808dd658a265f565a638556322250a0957e8e535
> )

Seems to be ok so far.

> sofar the results can be probably best explained by a simple
> screencast http://users.freedesktop.org/~noelp/fillformat/financial-cell-format.ogv

Thanks :-)

Looks good.


> +        if ( nNumCharsToInsert )
> +        {
> +            for ( int i = 0; i < nNumCharsToInsert; ++i )
> +                aTmpStr.Insert( mnChar, mnPos );
> +        }

In case that turns out to be a bottleneck for wide columns with many
rows of data displayed we could use

    String aFill;
    aFill.Expand( nNumCharsToInsert, mnChar);
    aTmpStr.Insert( aFill, mnPos);

to avoid multiple reallocations with character-based Insert.


> +    SfxObjectShell* pDocSh  = SfxObjectShell::Current();
> +    if ( pDocSh )
> +    {
> +        // is this a calc document
> +        Reference< XServiceInfo > xSI( pDocSh->GetModel(), UNO_QUERY );
> +        if ( xSI.is() )
> +            bUseStarFormat = xSI->supportsService( rtl::OUString( 
> "com.sun.star.sheet.SpreadsheetDocument" ) );
> +    }

Heh, that's clever :-)

  Eike

-- 
LibreOffice Calc developer. Number formatter stricken i18n transpositionizer.
GnuPG key 0x293C05FD : 997A 4C60 CE41 0149 0DB3  9E96 2F1A D073 293C 05FD

Attachment: pgp8wkafTcXe_.pgp
Description: PGP signature