Auto-Fit multiple columns

[Hafedh TRIMECHE]

How to apply the Auto-Fit to a range/all of columns.

[wp]

The WorksheetGrid has a method AutoColWidth(AIndex) which you call for each column requested. See attached demo.

[Hafedh TRIMECHE]

Thank you for response.

Only TsWorkbook + TsWorksheet components are involved in the project.

A routine must be written to calculate the real width of Cell.

[wp]

I fear this is not possible at the moment. fpspreadsheet is designed to be independent of the Graphics unit and therefore  does not know about the font metrics and the resolution of the screen on which the worksheet will be displayed; as a consequence the TsWorksheet cannot calculate the optimimum column width. I could imagine that there is a flag stored in the column record to force the application opening the file to calculate the column width and to get an auto-column width effect. But I did not find anything like this within fpspreadsheet. Looking at the documentation of the xls file format and at a file with an auto-width column written by LibreOffice doen't give an indication either. Only Excel 2007+ writes an attribute "bestfit" to the parameters of the auto-width column. So, in principle, this feature could be added to fpspreadsheet, but it will be a bit disappointing to the user because it will be ignored by two out of the three important spreadsheet formats.

[Hafedh TRIMECHE]

Indeed the solution is using TsWorksheetGrid as a render engine, but when adding sWorksheetGrid1.SaveToSpreadsheetFile('c:\sWorksheetGrid1.xls',True); at the end of FormCreate raised an exception.

[wp]

Maybe you did not mention the reader/writer unit it the "uses" clause: for saving to xlsx you must "use" xlsxOOXM, or fpsAllFormats to register all formats.

If you did, the bug must be somewhere else in your code. It is not related to fpspreadsheet as my attached simple demo does not raise the exception

[Hafedh TRIMECHE]

fpsallformats solved the problem.

The idea is to use (TsWorkbook,TsWorksheet). Once the document generated, it will be loaded into TsWorksheetGrid component.

WorksheetGrid := TsWorksheetGrid.Create(nil);
WorksheetGrid.LoadFromWorkbook(MyWorkbook);

--------- Apply Auto-Fit ---------

WorksheetGrid.SaveToSpreadsheetFile('c:\WorksheetGrid.xls');
WorksheetGrid.Free;

Freeing the WorksheetGrid component raised an exception.

Which is the best implementation for combining TsWorkbook & TsWorksheetGrid?

Best regards

[wp]

This is working for me without an exception. Did you assign a parent to the grid?

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

1. procedure TForm1.Button1Click(Sender: TObject);
2. var
3.   grid: TsWorksheetGrid;
4. begin
5.   grid := TsWorksheetGrid.Create(nil);
6.   try
7.     grid.Parent := self;
8.     grid.Visible := false;
9.     grid.Cells[1, 1] := 'a very very very long text';
10.     grid.CellFontName[1, 1] := 'Arial';
11.     grid.CellFontSize[1, 1] := 10;
12.     grid.AutoColWidth(1);
13.     grid.SaveToSpreadsheetFile('test-autocol.xlsx', true);
14.   finally
15.     grid.Free;
16.   end;
17. end;  

Please don't expect 100% perfection of the auto-sized column width. Microsoft adds a few pixels and I never understood the algorithm behind it. In the test above I explicitely specified the font because I thought maybe Excel's default font is different from the one used by fpspreadsheet; but still the text is a bit too wide.

[Hafedh TRIMECHE]

Would AutoColWidth be adjusted with a additional units to correct truncation visual effect?

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

1. procedure AutoColWidth(ACol: Integer);
2.  

becomes

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

1. procedure AutoColWidth(ACol: Integer;AUnits:Word);
2.  

No I didn't assigned a parent just loaded Workbook using:

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

1.   MyWorkbook := TsWorkbook.Create;
2.   MyWorksheet := MyWorkbook.AddWorksheet('My Worksheet');
3.  
4.   // Write some number cells
5.   MyWorksheet.WriteNumber(0, 0, 1.0);
6.   MyWorksheet.WriteNumber(0, 1, 2.0);
7.   MyWorksheet.WriteNumber(0, 2, 3.0);
8.   MyWorksheet.WriteNumber(0, 3, 4.0);
9.  
10.   // Write the formula E1 = A1 + B1
11.   MyWorksheet.WriteFormula(0, 4, 'A1+B1');
12.  
13.   // Creates a new worksheet
14.   MyWorksheet := MyWorkbook.AddWorksheet('My Worksheet 2');
15.  
16.   // Write some string cells
17.   MyWorksheet.WriteText(0, 0, 'First');
18.   MyWorksheet.WriteText(0, 1, 'Second');
19.   MyWorksheet.WriteText(0, 2, 'Third');
20.   MyWorksheet.WriteText(0, 3, 'Fourth');
21.  
22. ----------- HERE -----------  
23.   WorksheetGrid := TsWorksheetGrid.Create(nil);
24.   WorksheetGrid.LoadFromWorkbook(MyWorkbook);
25.   WorksheetGrid.SaveToSpreadsheetFile('c:\WorksheetGrid.xls');
26.   WorksheetGrid.Free;
27.   MyWorkbook.Free;
28.  

The exception is raised when MyWorkbook.Free is invoked.

Regards.

[Hafedh TRIMECHE]

It seams that the TsWorksheetGrid component frees the TsWorkbook one by the WorksheetGrid's destructor.

The 2 commented instructions generated memory leek

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

1. //  WorksheetGrid.Free;
2. //  MyWorkbook.Free;
3.  

On the other hand, when only the MyWorkbook.Free is commented, no memory leak is detected which indicates that WorksheetGrid freed it.

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

1. //  WorksheetGrid.Free;
2.   MyWorkbook.Free;
3.  

[wp]

Yes, when you load an existing workbook into a worksheet by calling WorksheetGrid.LoadFromWorksheet, the grid takes ownership of the workbook and will destroy it automatically. I see that this is against the rule that the user must destroy everything himself which he created. Maybe I should change this behavior, but it would mean that the input spreadsheet must be copied into the internal workbook which could cause trouble if users provide large workbooks. At least there is a note in the comments of this routine which also appears in the help file.

You could add data to the worksheet provided by the worksheetgrid directly. In this case there is no external workbook at all, and no need to destroy it (look at my example above).

The Parent is needed when you call AutoColWidth because without a Parent the grid cannot create a Handle, and without a Handle it cannot measure text widths.

[Hafedh TRIMECHE]

I enhanced AutoFit implementation by using this procedure and adjusting cell width by an empirical value 1.06.

But this can be achieved only if the render container has a window control as parent and itself is a child of the Form.

Would TsWorksheetGrid be bound to  a non Form component? (Parent := nil /Commenting RenderContainer.Parent := Self;)

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

1. procedure AutoFit;
2. type
3.   RenderClass = TWinControl;
4. var
5.   WorksheetGrid   : TsWorksheetGrid;
6.   RenderContainer : RenderClass;
7.   s               ,
8.   c               : Integer;
9. begin
10.   RenderContainer        := RenderClass.Create(nil);
11.   RenderContainer.Parent := Self;
12.   WorksheetGrid          := TsWorksheetGrid.Create(RenderContainer);
13.   WorksheetGrid.Parent   := RenderContainer;
14.   WorksheetGrid.LoadFromWorkbook(Workbook);
15.   for s:=0 to WorksheetGrid.Workbook.GetWorksheetCount-1 do
16.   begin
17.     WorksheetGrid.SelectSheetByIndex(s);
18.     for c:=1 to WorksheetGrid.ColCount do
19.     begin
20.       WorksheetGrid.AutoColWidth(c);
21.       WorksheetGrid.ColWidths[c-1] := Round(WorksheetGrid.ColWidths[c-1]*1.06);
22.     end;
23.   end;
24.   WorksheetGrid.SaveToSpreadsheetFile('c:\WorksheetGrid.xls');
25.   RenderContainer.Free;
26. end;
27.  

 Regards.

[wp]

This will not work - just try, it will throw a "control has no parent" exception.

If it's really important, you should look at the freetype units coming in fcl (ftfont in fcl-image) and package LazUtils (unit LazFreeType). Here you can find routines to measure the text width directly, without an lcl rendering canvas. I once did this for TAChart, but forgot all the details (you can have a look at the units TADrawerOpenGL and TADrawerFPCanvas). The biggest problem was how to reliably find the font files.

[Hafedh TRIMECHE]

It worked with excepted result !

Just set the Container's Parent to the MainForm of the Application:

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

1. procedure AutoFit;
2. var
3.   WorksheetGrid   : TsWorksheetGrid;
4.   RenderContainer : TWincontrol;
5.   s               ,
6.   c               : Integer;
7. begin
8.   RenderContainer       := TWincontrol.CreateParentedControl(Application.MainForm.Handle);
9.   WorksheetGrid         := TsWorksheetGrid.Create(RenderContainer);
10.   WorksheetGrid.Parent  := RenderContainer;
11.   WorksheetGrid.Visible := False;
12.   WorksheetGrid.LoadFromWorkbook(Workbook);
13.   for s:=0 to WorksheetGrid.Workbook.GetWorksheetCount-1 do
14.   begin
15.     WorksheetGrid.SelectSheetByIndex(s);
16.     for c:=1 to WorksheetGrid.ColCount do
17.     begin
18.       WorksheetGrid.AutoColWidth(c);
19.       WorksheetGrid.ColWidths[c-1] := Round(WorksheetGrid.ColWidths[c-1]*1.06);
20.     end;
21.   end;
22.   WorksheetGrid.SaveToSpreadsheetFile('c:\WorksheetGrid.xls',xlsFormat);
23.   RenderContainer.Free;
24. end;
25.  

[Hafedh TRIMECHE]

using this approach (TBGRABitmap as a renderer), the result is quite acceptable

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

1. procedure AutoAdjustColumn(Worksheet:TsWorksheet;ACol: Integer);
2. var
3.   W        ,
4.   MaxW     : single;
5.   Txt      : string;
6.   Cell     : PCell;
7.   RTL      : Boolean;
8.   Renderer : TBGRABitmap;
9. begin
10.   if Worksheet=nil then Exit;
11.   Renderer := TBGRABitmap.Create;
12.   MaxW     := -1;
13.   for Cell in Worksheet.Cells.GetColEnumerator(ACol) do
14.   begin
15.     if Worksheet.IsMerged(Cell) then Continue;
16.     Txt := Worksheet.ReadAsText(Cell);
17.     if Txt = '' then Continue;
18.     case Worksheet.ReadBiDiMode(Cell) of
19.       bdRTL: RTL := true;
20.     else RTL := false;
21.     end;
22.     W := RichTextWidth(Renderer.Canvas,Workbook,Rect(0,0,MaxInt,MaxInt),
23.                        Txt+'|',Cell^.RichTextParams,Worksheet.ReadCellFontIndex(Cell),
24.                        Worksheet.ReadTextRotation(Cell),false,RTL,Worksheet.ZoomFactor);
25.     if W>MaxW then MaxW := W;
26.   end;
27.   if MaxW=-1 then MaxW := Worksheet.ReadDefaultColWidth(suPoints);
28.   Worksheet.WriteColWidth(ACol,MaxW,suPoints);
29.   Renderer.Free;
30. end;
31.  
32. procedure AutoFit;
33. var
34.   Worksheet : TsWorkSheet;
35.   s         ,
36.   c         : Integer;
37. begin
38.   for s:=0 to Workbook.GetWorksheetCount-1 do
39.   begin
40.     Worksheet := Workbook.GetWorksheetByIndex(s);
41.     for c:=Worksheet.GetFirstColIndex to Worksheet.GetLastColIndex do AutoAdjustColumn(Worksheet,c);
42.   end;
43.   Workbook.WriteToFile('c:\WorksheetAdv.xls',sfExcel8,True);
44. end;
45.