Change Cell Border colours

[witenite]

I have succeeded in developing a relatively complex table or grid based application using the FPSpreadsheet libary component TsWorksheetGrid. I am able to edit borders for cells (or blocks of cells) however I have not had any success changing the border colour. See code below as an example of my attempts:

for Col := sIndex to tIndex do
        begin
            tblProperties.BorderColor:=scBlue;
          if not isMerged[Col] then tblProperties.CellBorders[Col,0,Col,RowCount]:= [cbWest,cbEast] //If not merged cells, then set borders accordingly
          else if (MergeStart[Col]<>Col) and (MergeEnd[Col]<>Col) then tblProperties.CellBorders[Col,0,Col,RowCount]:= [] // if merged cells and not start or end (property or field) columns, then clear borders
            else if (MergeEnd[Col]=Col) then tblProperties.CellBorders[Col,0,Col,RowCount]:= [cbEast] // or if it's the last column for a property or field, set righ-hand border as required
              else tblProperties.CellBorders[Col,0,Col,RowCount]:= [cbWest]; // if we get this far it must be the first column for a merged cell property or field, in which case, set left-hand border as required
 
This effectively determines whether or not column borders should be set or cleared, depending on the state of merged cells in the header (row 0). All this code works perfectly, however the only method I have come across that refers to border colour (see tblProperties.BorderColor:=scBlue) has no effect. The border is always black. Note tblProperties is of type TsWorksheetGrid.

I actually need to set two different border colours, depending on whether or not the column is nested inside a merged cell (IE an inner border) or a merged cell column border (IE an outer border). By nested borders, I am of course referring to the columns below the merged cells in Row 0, and understand that for obvious reasons you would never see inner borders for merged cells anyway! I can post a screenshot if that helps.

Ta.

[wp]

I can post a screenshot if that helps.

Yes, please do this.

[witenite]

A picture says a 1000 words. Please see attachment WP. As can be seen (first picture) the merged cell (row 0) have outer borders, however the inner columns for these merged cells have no borders. I can add borders, that's not the issue, the issue is that I need to make them a different colour to differentiate these columns as "sub-fields" (row 1, or Text, Visible, Location) of the main field or property (Part Value, Voltage Rating, Reference). If I enable borders (as seen in second picture) for these sub field columns, it makes the table somewhat confusing to read. A light grey (for example) for these inner column borders would be far better.


aside from attempting to use the method mentioned above to change border colour, I have also tried using CellBorderStyle[ACol, ARow: Integer; ABorder: TsCellBorder] as instructed here:
http://wiki.freepascal.org/TsWorksheetGrid

[wp]

Here's code which maybe does what you want:

Code: Pascal  [Select][+][-]

1. program Project1;
2.  
3. {$mode objfpc}{$H+}
4.  
5. uses
6.   Classes, fpstypes, fpspreadsheet, xlsxooxml, fpsOpenDocument;
7.  
8. const
9.   MARGIN = 2;
10.   OUTER_COLOR = scBlack;
11.   INNER_COLOR = scGray;
12.  
13. var
14.   book: TsWorkbook;
15.   sheet: TsWorksheet;
16.   r, c: Cardinal;
17.  
18.   procedure Border(ARow, ACol: Integer; ABorder: TsCellBorder; AColor: Integer;
19.     ALineStyle: TsLineStyle);
20.   var
21.     cell: PCell;
22.     currentBorders: TsCellBorders;
23.   begin
24.     cell := sheet.GetCell(ARow, ACol);  // Create a cell!
25.     currentBorders := sheet.ReadCellBorders(cell);
26.     Include(currentBorders, ABorder);
27.     sheet.WriteBorders(cell, currentBorders);
28.     sheet.WriteBorderColor(cell, ABorder, AColor);
29.     sheet.WriteBorderLineStyle(cell, ABorder, ALineStyle);
30.   end;
31.  
32. begin
33.   book := TsWorkbook.Create;
34.   try
35.     sheet := book.AddWorksheet('Sheet 1');
36.  
37.     sheet.WriteColWidth(1, 30, suMillimeters);
38.     sheet.WriteColWidth(2, 20, suMillimeters);
39.     sheet.WriteColWidth(3, 15, suMillimeters);
40.     sheet.WriteColWidth(4, 15, suMillimeters);
41.     sheet.WriteColWidth(5, 15, suMillimeters);
42.     sheet.WriteColWidth(6, 15, suMillimeters);
43.     sheet.WriteColWidth(7, 15, suMillimeters);
44.     sheet.WriteColWidth(8, 15, suMillimeters);
45.  
46.     sheet.WriteRowHeight(0, MARGIN, suMillimeters);
47.     sheet.WriteColWidth(0, MARGIN, suMillimeters);
48.  
49.     sheet.WriteText(1, 1, 'Component');
50.     sheet.WriteText(1, 2, 'Part Value');
51.     sheet.WriteText(1, 3, 'Voltage Rating');
52.     sheet.WriteText(1, 6, 'Reference');
53.  
54.     sheet.WriteText(2, 2, 'Text');
55.     sheet.WriteText(2, 3, 'Text');
56.     sheet.WriteText(2, 4, 'Visible');
57.     sheet.WriteText(2, 5, 'Location');
58.     sheet.WriteText(2, 6, 'Text');
59.     sheet.WriteText(2, 7, 'Location');
60.     sheet.WriteText(2, 8, 'Visible');
61.  
62.     sheet.MergeCells(1, 3, 1, 5);
63.     sheet.MergeCells(1, 6, 1,;
64.  
65.     { Outer lines }
66.     for r := 1 to 2 do
67.       for c := 1 to 8 do begin
68.         sheet.WriteHorAlignment(r, c, haCenter);
69.         sheet.WriteBackgroundColor(r, c, $C0DCC0);
70.       end;
71.     for c := 1 to 8 do begin
72.       Border(1, c, cbNorth, OUTER_COLOR, lsMedium);
73.       Border(2, c, cbSouth, OUTER_COLOR, lsMedium);
74.     end;
75.  
76.     { Vertical outer lines and non-subfield lines }
77.     for r := 1 to 30 do
78.       for c in [1, 2, 3, 6, 9] do
79.         Border(r, c, cbWest, OUTER_COLOR, lsMedium);
80.  
81.     {  Horizontal inner line }
82.     for c := 3 to 8 do
83.       Border(1, c, cbSouth, INNER_COLOR, lsThin);
84.  
85.     { Vertical inner subfield lines }
86.     for r := 2 to 30 do
87.       for c in [3, 4, 6, 7] do
88.         Border(r, c, cbEast, INNER_COLOR, lsThin);
89.  
90.     sheet.Options := sheet.Options - [soShowGridLines];
91.     sheet.SelectCell(1, 0);
92.  
93.     book.WriteToFile('test.xlsx', sfOOXML);
94.     book.WriteToFile('test.ods', sfOpenDocument);
95.  
96.   finally
97.     book.Free;
98.   end;
99.  
100. end.

[witenite]

Thanks WP,
I am trying to implement the code now. To date I have only been using the TsWorksheetGrid component from the library (IE I have a TsWorksheetGrid object on my form). This has worked quite well, and I have configured it to permit me to enter data, merge cells in row 0, change header colours, move columns around (and manage group moves depending on whether or not cells are merged for a given property) etc. I have generally avoided using any additional object, but am I meant to drop another one on my form now? When I try to implement the code I get "Identifier not found TsWorkbook" despite having double checked that I have fpspreadsheetgrid, fpstypes, xlsxooxml, fpsOpenDocument,  fpspreadsheetctrls all listed under uses.

The reason I did not implement any of the other classes or object types before is that I will be reading data from a CAD file (non spreadsheet data file unique to the Kicad CAD package) and enter the data into the table programmatically. I did not see any useful advantage to applying all the additional code that ordinarily would be used for reading from a standard spreadsheet format such as Excel or CSV. It appears though that perhaps I have to in order to be able to do things the way I want to, as I see a number of parameters (when manipulated via my TsWorksheetGrid object) remain unchanged. EG AlternateColor is unresponsive too.

You may think from all this that I have done no research, but honestly I have read and reread several articles online about using FPSpreadsheet (and trawled through much of the code to get some idea as to how it all fits together), yet still find it a complex issue to deal with.

Very impressive, and thanks for the attention you have given my frustrating problem. You have re-created in a matter of hours in your example, what took me several days to get configured! Although I've only been using Delphi/Lazarus for about 9 months now, my skills historically are in embedded hardware/software design, so object oriented programming is pretty much brand new to me.

So back to my dilemma, do I need to drop another object onto my form from the Lazarus library, or is there another piece to this puzzle? Perhaps the easiest way for me to get up to speed is if you were to provide me a copy of your code (or a zip file containing the whole project). I will then be able to see the structure as it should be in its entirety, and can then add my existing (non table) code to it and edit accordingly. If you are able and willing, that would be greatly appreciated, thank you.

[wp]

One important unit is missing: fpspreadsheet is the heart of the library, it contains all the code of TsWorkbook and TsWorksheet, in particular the methods how to write to and read from the worksheet.

If you work with the WorksheetGrid you must know that it already has a builtin workbook and a builtin worksheet. Therefore you can drop the lines "book := TsWorkbook.Create" (and "book.Free") and "sheet := book.AddWorksheet(...)", replace them to use the builtin versions: "book := sWorksheetGrid1.Workbook" and "sheet := sWorksheetGrid.Worksheet". Then my code should run also in a gui environment with a WorksheetGrid. Find the compilable demo in the attachment.

[witenite]

Hi WP, I've finally got some time to take a closer look at the example you've put together and uploaded for me! I've just compiled the code and it works perfectly.

I've been taking a look through the code example you created and uploaded for me, and it's a lot more clearer to me now how the worksheet building blocks work together now. I am very grateful for you valued assistance. Thank you ever so much, I can now finally move forward with my project again. Hopefully others can find this example and gain as much value out of it as I have. I'll be in touch again if/when the next question arises 

Regards,
Graham
New Zealand