Gantt chart with fpSpreadsheet (was TextWidth in Spreadsheet)

[bpranoto]

Is there a way to get the text width in the fpsreadsheet cell?

Background:
I want to generate simple  gantt chart in excel spreadsheet. To make it easier I want to draw a string of '=' to represent the timebar.

One column is represents one work day (24 hours)

So if a job needs 6 hours. I want to draw the time bar with string of '=' with the width approximately 0.25

With fpspreadsheet, it's easy to get the cell width. But, I don't know how to get the TextWidth. Is there something like TCanvas.TextWidth in fpSpreadsheet?


Thank you.

[paweld]

In my opinion, it is enough to retrieve the width using Canvas.TextWidth. You only need set the font for Canvas from the cell.

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

1. unit Unit1;
2.  
3. {$mode objfpc}{$H+}
4.  
5. interface
6.  
7. uses
8.   Classes, SysUtils, Forms, Controls, Graphics, Dialogs, StdCtrls, fpstypes, fpspreadsheet, fpsallformats, fpsopendocument, xlsxooxml, fpsutils,
9.   LCLIntf;
10.  
11. type
12.  
13.   { TForm1 }
14.  
15.   TForm1 = class(TForm)
16.     Button1: TButton;
17.     procedure Button1Click(Sender: TObject);
18.     procedure FormCreate(Sender: TObject);
19.   private
20.  
21.   public
22.     procedure SaveFPS;
23.   end;
24.  
25. var
26.   Form1: TForm1;
27.  
28. implementation
29.  
30. {$R *.lfm}
31.  
32. { TForm1 }
33.  
34. const
35.   loremipsum =
36.     'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Sed feugiat efficitur consectetur. Duis maximus in nulla eget cursus. Nullam viverra orci ac lacus ' +
37.     'euismod sagittis at eget purus. Vestibulum non magna massa. Vivamus nec arcu sed tortor pellentesque maximus. Suspendisse commodo, mauris non ullamcorper ' +
38.     'consectetur, justo quam maximus ex, sit amet cursus nulla lorem vel ipsum. Class aptent taciti sociosqu ad litora torquent per conubia nostra, per inceptos ' +
39.     'himenaeos. Praesent ultricies commodo odio et sollicitudin. Donec aliquet ligula ac orci iaculis tincidunt. Lorem ipsum dolor sit amet, consectetur adipiscing ' +
40.     'elit.Class aptent taciti sociosqu ad litora torquent per conubia nostra, per inceptos himenaeos. Donec pulvinar viverra dui a euismod. Class aptent taciti ' +
41.     'sociosqu ad litora torquent per conubia nostra, per inceptos himenaeos. Sed nibh tellus, viverra id dui quis, auctor congue sapien. ';
42.  
43. procedure TForm1.Button1Click(Sender: TObject);
44. begin
45.   SaveFPS;
46. end;
47.  
48. procedure TForm1.FormCreate(Sender: TObject);
49. begin
50.   Randomize;
51. end;
52.  
53. procedure TForm1.SaveFPS;
54. const
55.   fnarr: Array[0..2] of String = ('Arial', 'Courier', 'TimesNewRoman');
56.   fsarr: Array[0..3] of TsFontStyles = ([], [fssBold], [fssItalic], [fssBold, fssItalic]);
57. var
58.   plik: TsWorkbook;
59.   arkusz: TsWorksheet;
60.   i, j: Integer;
61.   sl: TStringList;
62.   fnt: TFont;
63.  
64.   function TsFontToTFont(afont: TsFont): TFont;
65.   begin
66.     Result := TFont.Create;
67.     Result.Name := afont.FontName;
68.     Result.Size := trunc(afont.Size);
69.     Result.Style := [];
70.     if fssBold in afont.Style then
71.       Result.Style := Result.Style + [fsBold];
72.     if fssItalic in afont.Style then
73.       Result.Style := Result.Style + [fsItalic];
74.     if fssUnderline in afont.Style then
75.       Result.Style := Result.Style + [fsUnderline];
76.     if fssStrikeOut in afont.Style then
77.       Result.Style := Result.Style + [fsStrikeOut];
78.   end;
79.  
80. begin
81.   sl := TStringList.Create;
82.   sl.StrictDelimiter := False;
83.   sl.DelimitedText := loremipsum;
84.   plik := TsWorkbook.Create;
85.   arkusz := plik.AddWorksheet('test');
86.   for j := 0 to 9 do
87.   begin
88.     if j = 0 then
89.       arkusz.WriteColWidth(j, 50, suChars);
90.     arkusz.WriteText(0, j, Format('Column %d', [j + 1]));
91.   end;
92.   arkusz.WriteText(0, 10, 'Col0 text width');
93.   for i := 1 to 100 do
94.   begin
95.     for j := 0 to 4 do
96.     begin
97.       arkusz.WriteFont(i, j, fnarr[Random(Length(fnarr))], Random(10) + 6, fsarr[Random(Length(fsarr))], scBlack, fpNormal);
98.       arkusz.WriteText(i, j, sl[Random(sl.Count)]);
99.     end;
100.     for j := 5 to 9 do
101.       arkusz.WriteNumber(i, j, Random(50));
102.     fnt := TsFontToTFont(arkusz.ReadCellFont(arkusz.GetCell(i, 0)));
103.     Canvas.Font := fnt;
104.     arkusz.WriteNumber(i, 10, Canvas.TextWidth(arkusz.ReadAsText(i, 0)));
105.     fnt.Free;
106.   end;
107.   //save to file
108.   plik.WriteToFile('testfile.xlsx', sfOOXML, True);
109.   plik.Free;
110.   OpenDocument('testfile.xlsx');
111. end;
112.  
113. end.
114.  
115.  

[wp]

Is there a way to get the text width in the fpsreadsheet cell?

It depends on what you mean with "fpspreadsheet cell".

Strictly speaking, fpspreadsheet is the basic library to create, load and save workbooks and worksheets. There is no Canvas here, and the library does not have access to font metrics. Thus it is not possible to exactly measure text width. However, column width can be specified in several units, one of them is the width of the character '0' in the workbook default font (suChars). Of course, the usual fonts have varying character widths, but when you specify a fixed-width font (in Windows "Courier New") as default font, all characters are equally sized in width. Now, returning to you question, when you specify the default col width as 8 (for example) (workbook.SetDefaultColWidth(8, suChars)  and a Gantt bar should fill the entire column width (value = 1) you need to fill the cell with a text containing 8 bar-like characters ('='). When the Gantt value is 0.5 you need 4 characters, etc. Of course the resolution is not very high with colwidth=8. One remark: The Office apps do add some margin to the column width, but I never understood the algorithm how they do this, so you may have to play a bit with the command which sets the default column width. See attached demo for sample code.

The other possibility would be that you mean the fpspreadsheet grid (TsWorksheetGrid). It inherits from TCustomGrid (just like TStringGrid) and you can use its Canvas to determine the text width as usual. But you also have the possibility to custom-draw the cells (like in TStringGrid) and this way you could draw a real bar and you would have much higher resolution for the Gantt display. (Never tried this, though)

A completely different idea is to use a ready-made Gantt component - and there is a GantView in the TvPlanIt package which you can install via OPM. Find the TvPlanIt documentation with steps to get started in https://wiki.freepascal.org/Turbopower_Visual_PlanIt. Unfortunately there are no docs yet for the GanttView, except for the sample demo in the TvPlanIt installation folder.

[bpranoto]

Actually, It is machines production planning for a month.

I have made the gantt chart with TRxDBGrid like the screenshot below.

With TRxDBGrid drawing the time bar is easy with TCanvas.FillRect

But now, the user wants to be able to export the planning to a spreadsheet. With excel or libre office I can draw the bar with inserting rectangular shape. However, fpSpreadsheet doesn't support Shape object, or does it?

[wp]

No Shape support in fpspreadsheet. A workaround could be to create images for each Gantt cell and insert them. To avoid blowing up the file size I would create a standard-size image for each color (100x100 px) and scale and offset it within the Gantt cells by means of the parameters available for the worksheet's WriteImage method.

See attached demo project.

[bpranoto]

I think for the time bar, I will be using one column to represent 0.5 hours.

So for one shift (8 hours) there will be 16 small columns. As for the column header, I will merged center those 16 columns  and print the header PG/SG/ML (Morning, Noon, Night).

After that I will be using background color to represent the time portions like in the screenshot.

I think it will be simpler and more accurate.

Thank you for your attention.

[bpranoto]

This is the result.

Thank you very much to wp for your great work and support.  Too bad my English is not good enough to show how much I appreciate you. fpSpreadsheet is very wonderful!