fpspreadsheet color
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
MSC · 2014-09-24 09:37

How can I get the color of a specific row within sWorkSheetGrid?
Hav´nt found much help on google.
Thx

fpSpreadsheet 1.1.4 from code typhoon
FPC 2.6.4
Lazarus 1.2.4 32-bit
on Windows 7 64bit

----------------------------------------------------------------------
Windsurfer · 2014-09-24 10:41

I am replying from my Android tablet, so can't easily give an example.
Use the grid Onpaint event. You will see the row and column are passed
as (probably) Arow and Acol. So try something like:
Code: [Select]if (arow = myrownum) then color := mycolor;
Edit: to get rather than set the colour, reverse the last part of the
line above. Refer to something like: mygrid.rows[my row].color.

----------------------------------------------------------------------
wp · 2014-09-24 11:10

There is no OnPaint event of the grid, use the OnPrepareCanvas
instead:

Code: [Select]procedure TMainFrm.WorksheetGridPrepareCanvas(sender:
TObject; aCol,
  aRow: Integer; aState: TGridDrawState);
begin
  if odd(ARow) then WorksheetGrid.Canvas.Brush.Color := clSilver;
end;

But note that this is for drawing purposes only, if you save this
spreadsheet the resulting file will not contain these colors. If you
want to have the colors in the file as well then you have use
WorksheetGrid.BackgroundColor[ARect] := AColor where the rectangle
contains the column/row indexes of the top left and right bottom
corners of the rectangle receiving the requested color. fpspreadsheet
currently does not yet support row formats.

----------------------------------------------------------------------
MSC · 2014-09-24 12:05

Just to specify my problem:
On my Worksheetgrid which I have loaded with data from the web as
Excel .xlsx, I have some rows which are colored in gray. These rows
are used as a separator and header(text/string) for other data in
following rows (@wp: see ELPP file from my previous posts on
fpSpreadsheet). These data I want to read into a database (using the
headertext for grouping). Since I have no access to the originator of
the Excel-sheet, in future headertext may change or additional rows
are added etc. Only the color of the separator/header rows will
(hopefully) stay in gray color.
To get the data into my database, I want to iterate through all rows,
checking if row color = gray, take actual rownumber(or index) and save
data(rows) between previous and actual rownumber into database.
So I need to readout the actual color of a row in the Worksheetgrid.

----------------------------------------------------------------------
BigChimp · 2014-09-24 12:24

Yes, so there must be a backgroundcolor property for cells, I
suppose!?!? Have you tried the CHM help file supplied with
fpspreadsheet?

Edit: remove underline stuff

----------------------------------------------------------------------
wp · 2014-09-24 12:28

I think that's not feasible in a stable manner at the moment because
fpspreadsheet does not support row formats, and I see from the ELPP
file that it does make use of that feature.

However, in a less stable manner, i.e. ignoring empty cells, you could
iterate along each row and ask for the background color of a cell. If
all existing cells are gray then you can consider the row to be gray
as well. Something like that:

Code: [Select]function IsGrayRow(Worksheet: TsWorksheet; ARow:
Cardinal): boolean;
const
  GRAY_COLOR = scGray;  // Enter the exact color index here
var
  c: Cardinal;  // column index
begin
  if Worksheet = nil then
    Exit(false);
  for c := 0 to Worksheet.GetLastColIndex do begin
    cell := Worksheet.FindCell(ARow, c);
    if (cell = nil) then  // non-existing cell --> ignore
      Continue;
    if (uffBackgroundColor in cell^.UsedFormattingFields) and
       (cell^.BackgroundColor <> GRAY_COLOR)
    then
      Exit(false);
  end;
  Result := true; // if the function gets to this point all existing
cells are gray
end;

----------------------------------------------------------------------
MSC · 2014-09-25 06:15

@wp:
Thx, the snippet of your last post does the job I was looking for.
I just replaced the line "if (uffBackgroundColor..." by
"if cell^.BackgroundColor = GRAY_COLOR then Result := true;".
This seems to be easier to understand and it does the job.

----------------------------------------------------------------------
wp · 2014-09-25 07:29

The UsedFormattingFields provide a general mechanism to turn the
corresponding format on and off. So, if the uffBackgroundColor is not
set in the UsedFormattingFields then the value of the backgroundcolor
is ignored. This is alright when reading files, but may happen when
manipulating cell properties manually.