BackgroundColor for numeric cell

[BigChimp]

Strange. The constants in the fpspreadsheet code appear to be ok, so it's probably something to do with how the colors are written to the xls files. Perhaps we're putting them in the wrong place.

Dealing with colors apparently worked for thompsna so I'm wondering what changed...
Sorry, but I think I'm going to focus on getting my date handling changes set up correctly and leave this issue (at least for now).

[engranaje]

Ok, no problem, I'm intersted too in your date changes  . 

[bigeno]

Is there any way to use RGB or HTML colors instead of standard color palette ?

[engranaje]

ok i' try it but nothing...
i think that  TsSpreadBIFF8Writer.WritePalette is ok and the five first colors are

Code: [Select]

{ Now the colors, first the standard 16 from Excel }
  AStream.WriteDWord(DWordToLE($000000)); // $08 --> Field rgColor[0] of Palette
  AStream.WriteDWord(DWordToLE($FFFFFF)); //--> Field rgColor[1] of Palette
  AStream.WriteDWord(DWordToLE($FF0000));//--> Field rgColor[2] of Palette
  AStream.WriteDWord(DWordToLE($00FF00));//--> Field rgColor[3] of Palette
  AStream.WriteDWord(DWordToLE($0000FF));//--> Field rgColor[4] of Palette
 
then i read
http://msdn.microsoft.com/en-us/library/dd907610%28v=office.12%29.aspx
http://msdn.microsoft.com/en-us/library/dd773056%28v=office.12%29.aspx
and i understand that this constants are ok too

Code: [Select]


{ Built In Color Pallete Indexes }
  BUILT_IN_COLOR_PALLETE_BLACK     = $08;
  // 000000H --> Field rgColor[0] of Palette is ok
   BUILT_IN_COLOR_PALLETE_WHITE     = $09;
   // FFFFFFH  --> Field rgColor[1] of Palette is ok
    BUILT_IN_COLOR_PALLETE_RED = $0A;
  // FF0000  -->Field rgColor[2] of Palette expeted but paint 0000FF field rgColor[4] of Palette
  BUILT_IN_COLOR_PALLETE_GREEN     = $0B;
  // 00FF00H --> Field rgColor[3] of Palette is ok
   BUILT_IN_COLOR_PALLETE_BLUE  = $0C;
  // 0000FF -->Field rgColor[4] of Palette expeted but paint 0000FF field rgColor[2] of Palette
but i dont understand why works ok for some fields of the palette and change anothers. maybe later....
OK.. i edit this post, i find this url
http://dmcritchie.mvps.org/excel/colors.htm
when i read this:

Code: [Select]


 str0 = Right("000000" & Hex(Cells(i + 1, 1).Interior.color), 6)
 'Excel shows nibbles in reverse order so make it as RGB
 str = Right(str0, 2) & Mid(str0, 3, 2) & Left(str0, 2)
 
And my tests confirm this excel paint hexadecimal colors changing pos 1 and 2 for pos 5 and 6...
if in
TsSpreadBIFF8Writer.WritePalette we add the color $FF0000 in the pos 2
when excel paint a cell with the pos 2 color of the palette  he paints $0000FF
same with any hexadecimal color, for example with the  Built In Color Palette Indexes:
we write        excel paint
008080H -->  808000H 
808000H -->  008080H
000080H -->  800000H
800000H -->  000080H
00FFFFH  -->  FFFF00H
FFFF00H  -->  00FFFFH

Now i'm not sure for the best mode of solve this  rewriting TsSpreadBIFF8Writer.WritePalette or maybe with a function that reverse the orders of nibbles if we want use standard html colors in the source. 

I'm thinking change de source for me Making a class Personal palette with a 2 dimensions array with te color name and the hexadecimal value of the colors for personalize the Palette with my personal colors. Writing of course a method writeInXlsPallete that reverse de hexadecimal colors introduced in  the addColor(ColorName,HexadecimalValue)  ¿Bad idea?

[engranaje]

Ok, I reply for update the time of this forum thread. I edited my previous post because i think that find the problem in http://dmcritchie.mvps.org/excel/colors.htm example

[BigChimp]

And my tests confirm this excel paint hexadecimal colors changing pos 1 and 2 for pos 5 and 6...
if in
TsSpreadBIFF8Writer.WritePalette we add the color $FF0000 in the pos 2
when excel paint a cell with the pos 2 color of the palette  he paints $0000FF
same with any hexadecimal color, for example with the  Built In Color Palette Indexes:
we write        excel paint
008080H -->  808000H 
808000H -->  008080H
000080H -->  800000H
800000H -->  000080H
00FFFFH  -->  FFFF00H
FFFF00H  -->  00FFFFH

Wow, nice work!

Now i'm not sure for the best mode of solve this  rewriting TsSpreadBIFF8Writer.WritePalette or maybe with a function that reverse the orders of nibbles if we want use standard html colors in the source. 

I'm thinking change de source for me Making a class Personal palette with a 2 dimensions array with te color name and the hexadecimal value of the colors for personalize the Palette with my personal colors. Writing of course a method writeInXlsPallete that reverse de hexadecimal colors introduced in  the addColor(ColorName,HexadecimalValue)  ¿Bad idea?

Do you mean use a conversion function that shifts the nibbles (Color2ExcelFormat, e.g. in xlscommon if it applies to more than just biff8) and call that each time in TsSpreadBIFF8Writer.WritePalette
e.g.

Code: [Select]

AStream.WriteDWord(DWordToLE($008080));
to

Code: [Select]

AStream.WriteDWord(DWordToLE(Color2ExcelFormat($008080)));

I think that's a good idea... hope the fpsreadsheet devs agree.

[wp]

I guess this color issue is due to misleading documentation in the OpenOffice Excel FileFormat paper:

Page 20 says:

Colour values are represented in RGB mode (red/green/blue).
Offset            Size             Contents
------------      -----------     ----------------------
0                   1                 Red component
1                   1                 Green component
2                   1                 Blue component
3                   1                 Not used
In this documentation, constant colour values are written as 6-digit hexadecimal values in RGB notation: RRGGBB (hex).
Example: The colour value FF8000(hex) describes the colour orange: red is FF(hex), green is 80(hex), and blue is 00(hex)

In addition, I found a Microsoft document of the xls file structure (http://download.microsoft.com/download/2/4/8/24862317-78F0-4C4B-B355-C7B2C1D997DB/%5BMS-XLS%5D.pdf) where the color values are referred to as type "LongRGBA" (page 359). The document contains a bit layout of this structure, saying that bits 0-7 correspond to red, bits 8-15 are green, bits 16-23 are blue, and bits 24-31 are alpha (page 742). The same document confirms "little endianness".

Since the "red" byte comes first in the file, the usual representation would be $BBGGRR in Pascal. Therefore, the bytes corresponding to red and blue must be interchanged in the fpspreadsheet sources.

[BigChimp]

Endianness.. Duh. That makes sense! Thanks, wp.
I'm getting very forgetful it seems. Thread above indicate already it's endianness related... but we're using NtoLE functions in biff8 WritePalette to convert to LE if needed... seems there's still things I don't understand. Some kind of "double LE switch" or something. Blegh.

[wp]

When I trace into NtoLE is see that this function just returns the input value for the currently active define?! Maybe this function is meant to correct endianness of different machine hardware?

Anyway, I flipped bytes in all color definitions of xls related units and got the colors correct now as can be shown in BigChimp's test case (testmanual.xls). This observation is for Windows, but I tested also on a virtual machine with Ubuntu passing the test as well. (Running fpspreadsheet on Ubuntu poses some other issues, though, which I'll put into a separate thread). It would be interesting to see the effect of endianness on a non-Intel machine...

The attached patch contains the required modifications of fpspreadsheet.pas, xlscommon.pas and xlsbiff8.pas

[marcov]

Color types is again something different. This because a record r,G,b,A : byte end; is not endian dependent, and dword based is.

OpenGL textures are also ABGR.

[BigChimp]

where the color values are referred to as type "LongRGBA" (page 359). The document contains a bit layout of this structure, saying that bits 0-7 correspond to red, bits 8-15 are green, bits 16-23 are blue, and bits 24-31 are alpha (page 742). The same document confirms "little endianness".

Since the "red" byte comes first in the file, the usual representation would be $BBGGRR in Pascal. Therefore, the bytes corresponding to red and blue must be interchanged in the fpspreadsheet sources.

Yep, you're right. though AFAIU, it's LongRGB (2.5.177) not LongRGBA (2.5.178) though in practice the size is the same and the A is always 0 anyway.

Thanks for the patch... was thinking about something like http://codes-sources.commentcamarche.net/source/37786-astuce-haaa-les-couleurs-integer-byte-evitez-les-shr-shl-et-autres-fonctions-delphi-va-se-charger-de-tout to be able to maintain both the existing constants (and quick reference to color tables etc) as well as correct output...

[BigChimp]

@wp: what about this patch? Edit: updated so it is slightly less gross. Not sure what it'll do on big endian.
(Also has some work in progress stuff for the grid)

scOrange, scDarkbrown, scBrown, scBeige, scWheat don't work - they all show white. The rest does...
Edit: just tried your patch which has the same problem. IOW it isn't our patches that are messing things up

[wp]

The patch is working fine. It has the advantage that it allows to paste the color constants from the doc into the source file.

I knew that the other colors scOrange etc are not correct, I had even reverted one of my patches to check if I did not mess it up. I guess they are not correctly implemented. Also, the implemented default color table is the one for biff2, in biff5 and biff8 there are more colors. It would be straightforward to specify these colors in the padded table entries. But how about their names then? Unfortunately, the color constants are defined in the central fpspreadsheet.pas unit. biff5 colors are a bit different from biff8, and these additional color constants would not correspond to a color in biff2 at all. Maybe it would be better to move the constants for the additonal colors to the individual units and call them "scOrange_biff8" etc. I guess proper management of these non-EGA colors requires some more thinking..

[BigChimp]

Totally agreed with your post. Let's give it some nights' sleep
(Anyway, I'm fairly happy with these colors but then again I'm not very interested in using fpspreadsheet to build pretty spreadsheets... others might).

Edit thanks, committed  r2871

[wp]

I'm fairly happy with these colors but then again I'm not very interested in using fpspreadsheet to build pretty spreadsheets...

The same with me. My main interest in fpspreadsheet is the possibility to export data to Excel from my programs, or to read data from Excel files written by other programs.