How to make gap between lines in StringGrid

[shobits1]

Ok, first of all I have 0 experience with StringGrid  .

What I want to achieve is visually explained in the attached image and have this key point.
1- Is it possible to make gap between lines? - a work around is let a blank line between each two lines but I do not want this.
2- Is it possible to merge Cells Vertically and Horizontally?.
3- Does StringGrid support different border styles?.
4- Is it possible to draw cells background differently from each other?.

[Bart]

4- Is it possible to draw cells background differently from each other?.

Yes, take a look at the OnPrepareCanvas event.
The Grids reference page is also a good place to start.

Bart

[FTurtle]

Try looking in that direction:

http://wiki.freepascal.org/FPSpreadsheet
http://wiki.freepascal.org/Category:FPSpreadsheet

But it is much more difficult.

[wp]

Ok, first of all I have 0 experience with StringGrid  .

You selected a tough task for your first job with it...

1- Is it possible to make gap between lines? - a work around is let a blank line between each two lines but I do not want this.

You must turn off the built-in gridl lines (remove goHorzLine and goVertLine from the grid's options) and paint all the required grid lines by yourself. I am not sure if all the required methods are available from TStringGrid - I'd propose to create a new grid, TMyStringGrid, inheriting from TCustomStringGrid, and override some its protected Draw* methods. The grid's internal architecture is very well-structured and relatively easy to understand.

2- Is it possible to merge Cells Vertically and Horizontally?.

Yes, but it's a lot of work. I did this for the grid used by fpspreadsheet. Again, you must create your own grid class and paint a border only around the merged cells. And the cell painting methods of the merged cells must get the rectangle of the merged boundary.

3- Does StringGrid support different border styles?.

Again, no built-in support. But you must paint the borders yourself anyway - once you are able to do this it will be easy to change line styles.

4- Is it possible to draw cells background differently from each other?.

This one's easy: Write a handler for OnPrepareCanvas. This event fires immediately before a cell is being drawn and after its default attributes are set. Therefore, you simply change the color of the brush to modify the background, or the font style to draw the cell text in bold. There is no need to output any text here, the cell will be painted afterwards automatically using either the default or your modified attributes.

In total, you may see that this is really a lot of work. Since you are new to TStringGrid you might as well switch to another, third-party grid. I don't have much experience with these other grids, but you could try the KGrid from the KControls package (https://sourceforge.net/p/lazarus-ccr/svn/HEAD/tree/components/kcontrols/), or the RxGrid from the RX package (https://sourceforge.net/p/lazarus-ccr/svn/HEAD/tree/components/rx/trunk/). Or, as FTurtle mentioned above, you could also try TsSpreadsheetGrid from fpspreadsheet, but this is more than a simple string grid and links the entire fpspreadsheet libraray into your program.

[shobits1]

thank you Bart, FTurtle and wp.

FPSpreadsheet is more complicated and overkill for what I need; also it won't support gaps between lines so it is not the way to go .

and StringGrid only support Cells background 

It looks I need to create my own component for this to work  , may be this will be my 1st and good experience.
 

[eny]

Don't forget to have a look at KGrid: http://wiki.lazarus.freepascal.org/KControls#KGrid_control

[wp]

FPSpreadsheet is more complicated and overkill for what I need; also it won't support gaps between lines so it is not the way to go

After seeing that the KGrid fails to paint merged cells correctly I went back to the SpreadsheetGrid. Looking at it from the point of view of somebody who does not know about the spreadsheet background and wants to use it only as a replacement for TStringGrid I saw that it is not complicated at all. It was just missing the Cells property which I implemented now.

In the new trunk version at ccr, cell content can be written or read by the means of new Cells[ACol, ARow] property which has the grid row and column index as arguments, just like the ordinary TStringGrid. Also, row and column indexes are in the same order as with the TStringGrid (in the underlying worksheet, they are reversed). The difference, however, is that the Cells value is a variant, i.e. numbers, dates, times and strings can be assigned to the Cells, such as

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

1.   Grid.Cells[2, 1] := 'Value';
2.   Grid.Cells[3, 1] := 2.15;

Similarly cell attributes can be set by means of properties such as BackgroundColor, HorAlignment etc:

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

1.   Grid.BackgroundColor[2,1] := scBlack;  // or clBlack - it is the same
2.   Grid.CellFontColor[2,1] := scWhite;
3.   Grid.HorAlignment[2,1] := haCenter;

As can be seen in the attached screen shot, the grid requested a few posts above can be created by simple code like this. Except for the used units, there is no direct reference to fpspreadsheet. The code used is pasted below. Just create a new form, add a TsWorksheetGrid from the laz_fpspreadsheet_visual package, and use this Form OnCreate event handler:

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

1. unit Unit1;
2.  
3. {$mode objfpc}{$H+}
4.  
5. interface
6.  
7. uses
8.   Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs, Grids,
9.   fpspreadsheetgrid;
10.  
11. type
12.  
13.   { TForm1 }
14.  
15.   TForm1 = class(TForm)
16.     Grid: TsWorksheetGrid;
17.     procedure FormCreate(Sender: TObject);
18.   private
19.     { private declarations }
20.   public
21.     { public declarations }
22.   end;
23.  
24. var
25.   Form1: TForm1;
26.  
27. implementation
28.  
29. {$R *.lfm}
30.  
31. uses
32.   variants, fpstypes;
33.  
34. { TForm1 }
35.  
36. procedure TForm1.FormCreate(Sender: TObject);
37. const
38.   THICK_BORDER: TsCellBorderStyle = (LineStyle: lsThick; Color: scBlack);
39. var
40.   r,c: Integer;
41. begin
42.   // Turn off default grid lines
43.   Grid.ShowGridLines := false;
44.  
45.   // Turn off spreadsheet headers
46.   //Grid.ShowHeaders := false;
47.  
48.   // Cell block for "Sunday"
49.   Grid.MergeCells(Rect(2,2, 2,8));
50.   Grid.Cells[2,2] := 'Sunday';
51.   Grid.CellFontSize[2,2] := 12;
52.   Grid.CellFontStyle[2,2] := [fssBold];
53.   Grid.CellFontColor[2,2] := scBlack;
54.   Grid.TextRotation[2,2] := rt90DegreeCounterClockwiseRotation;
55.   Grid.HorAlignment[2,2] := haCenter;
56.   Grid.VertAlignment[2,2] := vaCenter;
57.  
58.   // Cell block for date
59.   Grid.MergeCells(Rect(3,2,3,8));
60.   Grid.Cells[3,2] := DateToStr(EncodeDate(2016, 1, 17));
61.   Grid.TextRotation[3,2] := rt90DegreeCounterClockwiseRotation;
62.   Grid.HorAlignment[3,2] := haCenter;
63.   Grid.VertAlignment[3,2] := vaCenter;
64.  
65.   // cells 1,2,3,4
66.   Grid.Cells[4,2] := 1;
67.   Grid.CellFontStyle[4,2] := [fssBold];
68.   Grid.HorAlignment[4,2] := haCenter;
69.   Grid.VertAlignment[4,2] := vaCenter;
70.  
71.   Grid.Cells[4,4] := 2;
72.   Grid.CellFontStyle[4,4] := [fssBold];
73.   Grid.HorAlignment[4,4] := haCenter;
74.   Grid.VertAlignment[4,4] := vaCenter;
75.  
76.   Grid.Cells[4,6] := 3;
77.   Grid.CellFontStyle[4,6] := [fssBold];
78.   Grid.HorAlignment[4,6] := haCenter;
79.   Grid.VertAlignment[4,6] := vaCenter;
80.  
81.   Grid.Cells[4,8] := 4;
82.   Grid.CellFontStyle[4,8] := [fssBold];
83.   Grid.HorAlignment[4,8] := haCenter;
84.   Grid.VertAlignment[4,8] := vaCenter;
85.  
86.   // other cells
87.   for c := 5 to 16 do
88.   begin
89.     if not (c in [13, 16]) then
90.     begin
91.       Grid.Cells[c,2] := 'L1';
92.       Grid.BackgroundColor[c,2] := $5BBA8A;
93.       Grid.HorAlignment[c,2] := haCenter;
94.     end;
95.     if not (c in [10, 11, 15, 16]) then
96.     begin
97.       Grid.Cells[c,4] := 'L2';
98.       Grid.BackgroundColor[c,4] := $4796F6;
99.       Grid.HorAlignment[c,4] := haCenter;
100.     end;
101.     if not (c in [8, 9]) then
102.     begin
103.       Grid.Cells[c,6] := 'M1';
104.       Grid.BackgroundColor[c,6] := $C5AB4B;
105.       Grid.HorAlignment[c,6] := haCenter;
106.     end;
107.     if (c < 12) then
108.     begin
109.       Grid.Cells[c,6] := 'M1';
110.       Grid.BackgroundColor[c,6] := $BC814F;
111.       Grid.HorAlignment[c,6] := haCenter;
112.     end;
113.     if not (c in [7, 10..16]) then
114.     begin
115.       Grid.Cells[c,8] := 'M2';
116.       Grid.BackgroundColor[c,8] := $FD6799;
117.       Grid.HorAlignment[c,8] := haCenter;
118.     end;
119.   end;
120.  
121.   // Column widths
122.   for c := 1 to Grid.ColCount-1 do
123.     Grid.ColWidths[c] := 40;
124.  
125.   // Intermediate row heights
126.   Grid.RowHeights[3] := 8;
127.   Grid.RowHeights[5] := 8;
128.   Grid.RowHeights[7] := 8;
129.  
130.   // Border lines
131.   for c:=2 to 16 do
132.   begin
133.     // top line
134.     Grid.CellBorder[c, 2] := Grid.CellBorder[c, 2] + [cbNorth];
135.     Grid.CellBorderStyle[c, 2, cbNorth] := THICK_BORDER;
136.     // bottom line
137.     Grid.CellBorder[c, 8] := Grid.CellBorder[c, 8] + [cbSouth];
138.     Grid.CellBorderStyle[c, 8, cbSouth] := THICK_BORDER;
139.   end;
140.   // intermediate lines
141.   for c:=4 to 16 do begin
142.     Grid.CellBorder[c,2] := Grid.CellBorder[c,2] + [cbWest, cbSouth, cbNorth];
143.     Grid.CellBorder[c,4] := Grid.CellBorder[c,4] + [cbWest, cbSouth, cbNorth];
144.     Grid.CellBorder[c,6] := Grid.CellBorder[c,6] + [cbWest, cbSouth, cbNorth];
145.     Grid.CellBorder[c,8] := Grid.CellBorder[c,8] + [cbWest, cbSouth, cbNorth];
146.   end;
147.   for r:=2 to 8 do begin
148.     // Left line
149.     Grid.CellBorder[2,r] := Grid.CellBorder[2,r] + [cbWest];
150.     Grid.CellBorderStyle[2,r, cbWest] := THICK_BORDER;
151.     // Right line
152.     Grid.CellBorder[17,r] := [cbWest];                  
153.     Grid.CellBorderStyle[17,r, cbWest] := THICK_BORDER;
154.     // Right line of "Sunday", right line of date
155.     Grid.CellBorder[3,r] := Grid.CellBorder[3,r] + [cbWest, cbEast];
156.     Grid.CellBorderStyle[3,r, cbEast] := THICK_BORDER;
157.   end;
158. end;
159.  
160. end.

[shobits1]

yesterday I kept trying to modify the grids unit to fulfill my requirements but it proven to be hard and time consuming more than I thought (especially with my 0 experience in this field).

this morning I woke up and decided to try FPSpreadSheet; I know It will work because the attached imaged was crafted in MSExcel. the only thing I hated was the gap being an actual row since my program will read each cell and generate an array of strings to start the real work;; well creating address translation function between the arrays and FPSpreadSheet Cells will be a lot of easier than moding grids unit.

[wp]

I finally began writing the wiki documentation of the WorksheetGrid: http://wiki.lazarus.freepascal.org/TsWorksheetGrid. It is not yet complete, but the stuff finished describes how the grid can be filled and formatted as requested here.

[shobits1]

wp, I have weird issue; while this line of code works perfectly

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

1. Grid.Cells[2,2] := 'Sunday';

those lines generate SIGSEGV error (from the code you provided)

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

1. Grid.Cells[c,2] := 'L1';
2. Grid.Cells[c,4] := 'L2';
3. Grid.Cells[c,6] := 'M1';
4. Grid.Cells[c,8] := 'M2';
5.  

Also the grid seem to lose some properties I specify in design time (like ColCount, RowCount, ColWidth, RowHeight...).

I'm using Lazarus v1.4.4, with FPC v2.6.4; synced fpspreadsheet from commit r4432 and Installed laz_fpspreadsheet_visual.lpk and laz_fpspreadsheetexport_visual.lpk

EDIT:
also tried Lazarus v1.6RC2 with lazarus-ccr latest update (r4435) same problem; also when scrolling all rows heights changes (returns to default value I presume)

[wp]

Is the code that I posted above running fine?

Don't use ColCount and RowCount - I'm not sure, there must be a reason why I did not remove them, they are not needed any more. The grid automatically expands when cells are added.

ColWidth and RowHeight are changed by my code sample.

[shobits1]

Is the code that I posted above running fine?

No It's not but I have successfully located the problem - It is a bug in fpspreadsheetgrid unit and I believe it is in line: 4454, you should change

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

1. cell := Worksheet.FindCell(r, c);

to

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

1. cell := Worksheet.GetCell(r, c);

so the grid would add cell if none found at desired location (col, row).

Don't use ColCount and RowCount - I'm not sure, there must be a reason why I did not remove them, they are not needed any more. The grid automatically expands when cells are added.

How do I restrict the number rows and columns.

ColWidth and RowHeight are changed by my code sample.

But When I scroll (horz or vert) the row heights changes 

[shobits1]

ColWidth and RowHeight are changed by my code sample.

But When I scroll (horz or vert) the row heights changes 

Ok, this overridden procedure seems to be responsible of the rows height changes

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

1. procedure TsCustomWorksheetGrid.TopLeftChanged;
2. begin
3.   UpdateRowHeights; // removing this line solved the problem
4.   inherited;
5. end;
6.  

I don't why you force auto calculate row height when scrolling; still is it safe to delete the concerned line?

[wp]

Thanks for debugging. Unfortunately I still cannot reproduce the crash you observe. But anyway, the code at line 4454 really is very fragile. I think it is working now. Please test.

The row heights and column widths are lost because my sample code only modified the grid values, but not those in the underlying worksheet. I'll fix it in the next update.

UpdateRowHeights cannot be removed because of some special cases which I cannot remember... (maybe vertically merged cells)

[wp]

How do I restrict the number rows and columns.

When the grid is created a hidden workbook (an fpspreadsheet thing...) is initialized with 26 columns (col index 'A' to 'Z' in Excel) and 100 rows. This is where all the cell data and formats go to. If you need less or more cols and rows just call NewWorkbook and pass the requested column and row counts as parameters. Note that this is for visual and navigation purposes only. If you add a cell by code beyond these limits the grid is automatically expanded. Please use the current trunk version, in older versions this method did not work like this.