BackgroundColor for numeric cell

[bigeno]

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]

It should be fixed At revision: 2671

[bigeno]

Yes, works fine now. THX

[thompsna]

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]

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]

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]

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]

Am 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]

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]

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]

Perfect.  Thank you so much!

[engranaje]

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]

@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]

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]

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.