BackgroundColor for numeric cell lazarus / 49-FPSpreadsheet ---------------------------------------------------------------------- bigeno · 2013-02-10 20:38 After generating xls file there is no background on numeric cell. if I use:    MyWorksheet.WriteUTF8Text(...)    SCell:=MyWorksheet.GetCell(i,k);    SCell^.BackgroundColor:=scPURPLE;    SCell^.UsedFormattingFields := [uffBackgroundColor]; i get proper background, but if I use:    MyWorksheet.WriteNumber(...)    SCell:=MyWorksheet.GetCell(i,k);    SCell^.BackgroundColor:=scPURPLE;    SCell^.UsedFormattingFields := [uffBackgroundColor]; i don't get background. BackgroundColor is only for string cells ? ---------------------------------------------------------------------- felipemdc · 2013-02-12 11:29 It should be fixed At revision: 2671 ---------------------------------------------------------------------- bigeno · 2013-02-12 19:29 Yes, works fine now. THX ---------------------------------------------------------------------- thompsna · 2013-02-22 23:41 Hi, I'm having the same problem, but I don't under stand what you mean by "It should be fixed At revision: 2671."  Is that a future update coming out? Sorry for my ignorance, I'm purely an amateur. ---------------------------------------------------------------------- Blaazen · 2013-02-23 00:16 It means that FPSpreadsheet has its own SVN repository. You can download its actual development version. You need to have "subversion" installed. Tortoise SVN on Windows, or subversion package in Linux. And link is on the wiki: http://wiki.freepascal.org/FPSpreadsheet#Subversion ---------------------------------------------------------------------- thompsna · 2013-02-23 16:09 Blaazen, thank you so much for the quick reply!  Unfortunately, I'm not a programmer (just a statistician) so I don't know how to do any of that.  I guess I'll have to wait until the next release. ---------------------------------------------------------------------- thompsna · 2013-02-23 17:13 OK, never mind... I was able to just go to the URL in my browser and download the files.  But I think I might have found another small issue.  Continuing the example from above, if I write            MyCell := MyWorksheet.GetCell(row,14);            MyCell^.BackgroundColor:=scPURPLE;            MyCell^.UsedFormattingFields := [uffBackgroundColor]; ...then it works.  However, if I write            TempInt := 14;            MyCell := MyWorksheet.GetCell(row,TempInt);            MyCell^.BackgroundColor:=scPURPLE;            MyCell^.UsedFormattingFields := [uffBackgroundColor]; ...then it does not.  So I'm not able to use it when looping through an integer variable, which is what I need.  Moreover, the issue is only with the color.  If I do            MyWorksheet.WriteUsedFormatting(row,TempInt, [uffBold]); ...it works fine.  Only the use of the integer variable with color.  Am I doing something incorrectly? Thanks again for any help! ---------------------------------------------------------------------- User137 · 2013-02-23 19:13 Quote from: thompsna on February 23, 2013, 06:13:59 pmAm I doing something incorrectly?I think you gave too little information to answer that. As someone who has never used FPSpreadsheet you speak of, seeing the whole procedure where that loop is, would help seeing programming errors. Also type and initialization of every variable you are using in it. ---------------------------------------------------------------------- thompsna · 2013-02-23 21:26 Thanks, but I don't think the loop is the issue because all the cell values display correctly, and Bold correctly.  Only the color doesn't show up.  Here is the original (abbreviated) code without the troubleshooting of TempInt above.  I am dumping values from an array and then want to highlight ones above a certain value; I can do so using Bold but not color.  Color does work if I simply replace "column" for that line with any actual number.  The many other things I am doing with fpspreadsheet work fine too; I love it. Row, Column: integer; FlagValue : real; PropsArray: array of array of real; //setlength done elsewhere   for Row := 1 to i do begin       for Column := 1 to j do begin        MyWorksheet.WriteNumber(row, column, PropsArray[row,column]);        If PropsArray[row,column] > FlagValue then begin            MyCell := MyWorksheet.GetCell(row,column);  //works with a number... everything else is fine            MyCell^.BackgroundColor:=scPURPLE;            MyCell^.UsedFormattingFields := [uffBackgroundColor];            MyWorksheet.WriteUsedFormatting(row,column, [uffBold]);            end;       end; //Column     end;  //Row Is that sufficient?  Thank you for your time. ---------------------------------------------------------------------- felipemdc · 2013-02-27 15:01 Well, you do:            MyCell^.UsedFormattingFields := [uffBackgroundColor]; And next thing you remove the background color with this command:            MyWorksheet.WriteUsedFormatting(row,column, [uffBold]); The correct would be:            MyWorksheet.WriteUsedFormatting(row,column, [uffBackgroundColor, uffBold]); It is a full list of all used formatting options. Not a addition over what is already there. ---------------------------------------------------------------------- thompsna · 2013-03-28 20:40 Perfect.  Thank you so much! ---------------------------------------------------------------------- engranaje · 2013-11-18 09:08 Hello:   With de original BUILT_IN_COLOR constansts in  xlscommon.pas my code paints distinct colors. I must change the original constants values for obtain the color named by the constant.      // ORIGINAL  -> BUILT_IN_COLOR_PALLETE_RED  = $0A;  // FF0000H   BUILT_IN_COLOR_PALLETE_RED       = $0C;   // ORIGINAL  -> BUILT_IN_COLOR_PALLETE_BLUE       = $0C; // 0000FFH    BUILT_IN_COLOR_PALLETE_BLUE      = $0A;   // ORIGINAL  -> BUILT_IN_COLOR_PALLETE_YELLOW      = $0D; // FFFF00H    BUILT_IN_COLOR_PALLETE_YELLOW    = $0F;     // ORIGINAL  -> BUILT_IN_COLOR_PALLETE_CYAN    = $0F; // 00FFFFH   BUILT_IN_COLOR_PALLETE_CYAN      = $0D;   //-> ORIGINAL  -> BUILT_IN_COLOR_PALLETE_DARK_RED = $10; // 800000H    BUILT_IN_COLOR_PALLETE_DARK_RED  = $12;     // ORIGINAL  -> BUILT_IN_COLOR_PALLETE_DARK_BLUE  = $12; // 000080H   BUILT_IN_COLOR_PALLETE_DARK_BLUE = $10;   // ORIGINAL  -> BUILT_IN_COLOR_PALLETE_OLIVE      = $13; // 808000H   BUILT_IN_COLOR_PALLETE_OLIVE     = $15;   // ORIGINAL  -> BUILT_IN_COLOR_PALLETE_TEAL      = $15;  // 008080H   BUILT_IN_COLOR_PALLETE_TEAL      = $13;                                                it's only my? any idea what's happens? thank's ---------------------------------------------------------------------- BigChimp · 2013-11-24 10:37 @engranaje: what format are you trying to write (Excel 95, 97, xlsx, whatever)? What spreadsheet (LibreOffice, Excel, OpenOffice), which version+operating system? are you using to open it and view the results? Could you attach a small sample program that demonstrates the problem? See also first link in my signature. Note: you can use the [ code ] tags to make code snippets in posts readable, see the # button above when writing/editing posts. ---------------------------------------------------------------------- engranaje · 2013-11-27 11:48 Ok BigChimp thanks, i'm sorry. I paste here a little example procedure. With de original xlscommon.pas the 0,0 is not painted red, but with my xlscommon.pas works fine. Only need add fpspreadsheet to the uses. Code: [Select]procedure testBiff8CellBackgroundColor(sFullFileName:string); const   OUTPUT_FORMAT = sfExcel8; var   Workbook: TsWorkbook;   Worksheet: TsWorksheet;   Cell : PCell; begin   Workbook := TsWorkbook.Create;   Worksheet := Workbook.AddWorksheet('testPage1');   Worksheet.WriteNumber(0, 0, 12);   Cell := Worksheet.GetCell(0, 0);   Cell^.BackgroundColor :=  scRed;   if  not (uffBackgroundColor in Cell^.UsedFormattingFields) then     include (Cell^.UsedFormattingFields,uffBackgroundColor);   Workbook.WriteToFile(sFullFileName, OUTPUT_FORMAT, TRUE);   Workbook.free  ; end;                                                     anyone with the same problem?, I'm working ok with my modified xlscommon.pas ---------------------------------------------------------------------- BigChimp · 2013-11-27 15:57 That sample shows blue background color instead of the expected red for me (viewing with LibreOffice 4). Going to have a look at the constant definitions. The order of the palette in the original fpspreadsheet code does match the one specified in theopenoffice excel format pdf (linked to in the references in fpspreadsheet): 5.74.3 Built-In Default Colour Tables => I'll have a look at specifying the values in the enum and get back to you (if I don't, pls have a look at any updates for my code in my repository smalltools - see my other thread in fpspreadsheet). Thanks. ---------------------------------------------------------------------- BigChimp · 2013-11-27 19:23 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 · 2013-11-28 08:02 Ok, no problem, I'm intersted too in your date changes  . ---------------------------------------------------------------------- bigeno · 2013-11-28 11:34 Is there any way to use RGB or HTML colors instead of standard color palette ? ---------------------------------------------------------------------- engranaje · 2013-11-28 13:53 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 · 2013-11-29 09:47 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 · 2013-12-01 11:03 Quote from: engranaje on November 28, 2013, 02:53:16 pmAnd 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! Quote from: engranaje on November 28, 2013, 02:53:16 pmNow 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 · 2013-12-21 23:33 I guess this color issue is due to misleading documentation in the OpenOffice Excel FileFormat paper: Page 20 says: QuoteColour 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 (h ttp://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 · 2013-12-22 10:15 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 · 2013-12-26 18:06 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 · 2013-12-26 18:46 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 · 2013-12-27 12:50 Quote from: wp on December 22, 2013, 12:33:04 amwhere 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 · 2013-12-27 13:57 @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 · 2013-12-27 17:56 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 · 2013-12-27 17:59 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 · 2013-12-27 18:37 QuoteI'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. ---------------------------------------------------------------------- BigChimp · 2013-12-28 12:06 Quote from: wp on December 27, 2013, 06:56:49 pmI 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. Reported it as http://bugs.freepascal.org/view.php?id=25462 so it doesn't get forgotten. That said, I've had my fill of messing with Excel file formats for a while now