Auto-Fit multiple columns lazarus / 49-FPSpreadsheet ---------------------------------------------------------------------- Hafedh TRIMECHE · 2018-01-24 17:25 How to apply the Auto-Fit to a range/all of columns. ---------------------------------------------------------------------- wp · 2018-01-25 08:51 The WorksheetGrid has a method AutoColWidth(AIndex) which you call for each column requested. See attached demo. ---------------------------------------------------------------------- Hafedh TRIMECHE · 2018-01-25 17:48 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 · 2018-01-25 20:48 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 · 2018-01-26 08:20 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 · 2018-01-26 08:39 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 · 2018-01-26 09:01 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 · 2018-01-26 09:27 This is working for me without an exception. Did you assign a parent to the grid? Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TForm1.Button1Click(Sender: TObject);var  grid: TsWorksheetGrid;begin  grid := TsWorksheetGrid.Create(nil);  try    grid.Parent := self;    grid.Visible := false;    grid.Cells[1, 1] := 'a very very very long text';    grid.CellFontName[1, 1] := 'Arial';    grid.CellFontSize[1, 1] := 10;    grid.AutoColWidth(1);    grid.SaveToSpreadsheetFile('test-autocol.xlsx', true);  finally    grid.Free;  end;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 · 2018-01-26 09:39 Would AutoColWidth be adjusted with a additional units to correct truncation visual effect? Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure AutoColWidth(ACol: Integer);  becomes Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure AutoColWidth(ACol: Integer;AUnits:Word);  No I didn't assigned a parent just loaded Workbook using: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   MyWorkbook := TsWorkbook.Create;  MyWorksheet := MyWorkbook.AddWorksheet('My Worksheet');   // Write some number cells  MyWorksheet.WriteNumber(0, 0, 1.0);  MyWorksheet.WriteNumber(0, 1, 2.0);  MyWorksheet.WriteNumber(0, 2, 3.0);  MyWorksheet.WriteNumber(0, 3, 4.0);   // Write the formula E1 = A1 + B1  MyWorksheet.WriteFormula(0, 4, 'A1+B1');   // Creates a new worksheet  MyWorksheet := MyWorkbook.AddWorksheet('My Worksheet 2');   // Write some string cells  MyWorksheet.WriteText(0, 0, 'First');  MyWorksheet.WriteText(0, 1, 'Second');  MyWorksheet.WriteText(0, 2, 'Third');  MyWorksheet.WriteText(0, 3, 'Fourth'); ----------- HERE -----------    WorksheetGrid := TsWorksheetGrid.Create(nil);  WorksheetGrid.LoadFromWorkbook(MyWorkbook);  WorksheetGrid.SaveToSpreadsheetFile('c:\WorksheetGrid.xls');  WorksheetGrid.Free;  MyWorkbook.Free;  The exception is raised when MyWorkbook.Free is invoked. Regards. ---------------------------------------------------------------------- Hafedh TRIMECHE · 2018-01-26 09:50 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i //  WorksheetGrid.Free;//  MyWorkbook.Free;  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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i //  WorksheetGrid.Free;  MyWorkbook.Free; ---------------------------------------------------------------------- wp · 2018-01-26 10:14 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 · 2018-01-26 13:56 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure AutoFit;type  RenderClass = TWinControl;var  WorksheetGrid   : TsWorksheetGrid;  RenderContainer : RenderClass;  s               ,  c               : Integer;begin  RenderContainer        := RenderClass.Create(nil);  RenderContainer.Parent := Self;  WorksheetGrid          := TsWorksheetGrid.Create(RenderContainer);  WorksheetGrid.Parent   := RenderContainer;  WorksheetGrid.LoadFromWorkbook(Workbook);  for s:=0 to WorksheetGrid.Workbook.GetWorksheetCount-1 do  begin    WorksheetGrid.SelectSheetByIndex(s);    for c:=1 to WorksheetGrid.ColCount do    begin      WorksheetGrid.AutoColWidth(c);      WorksheetGrid.ColWidths[c-1] := Round(WorksheetGrid.ColWidths[c-1]*1.06);    end;  end;  WorksheetGrid.SaveToSpreadsheetFile('c:\WorksheetGrid.xls');  RenderContainer.Free;end;   Regards. ---------------------------------------------------------------------- wp · 2018-01-26 18:56 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 · 2018-01-26 20:28 It worked with excepted result ! Just set the Container's Parent to the MainForm of the Application: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure AutoFit;var  WorksheetGrid   : TsWorksheetGrid;  RenderContainer : TWincontrol;  s               ,  c               : Integer;begin  RenderContainer       := TWincontrol.CreateParentedControl(Application.MainForm.Handle);  WorksheetGrid         := TsWorksheetGrid.Create(RenderContainer);  WorksheetGrid.Parent  := RenderContainer;  WorksheetGrid.Visible := False;  WorksheetGrid.LoadFromWorkbook(Workbook);  for s:=0 to WorksheetGrid.Workbook.GetWorksheetCount-1 do  begin    WorksheetGrid.SelectSheetByIndex(s);    for c:=1 to WorksheetGrid.ColCount do    begin      WorksheetGrid.AutoColWidth(c);      WorksheetGrid.ColWidths[c-1] := Round(WorksheetGrid.ColWidths[c-1]*1.06);    end;  end;  WorksheetGrid .SaveToSpreadsheetFile('c:\WorksheetGrid.xls',xlsFormat);  RenderContainer.Free;end; ---------------------------------------------------------------------- Hafedh TRIMECHE · 2018-01-30 11:18 using this approach (TBGRABitmap as a renderer), the result is quite acceptable Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure AutoAdjustColumn(Worksheet:TsWorksheet;ACol: Integer);var  W        ,  MaxW     : single;  Txt      : string;  Cell     : PCell;  RTL      : Boolean;  Renderer : TBGRABitmap;begin  if Worksheet=nil then Exit;  Renderer := TBGRABitmap.Create;  MaxW     := -1;  for Cell in Worksheet.Cells.GetColEnumerator(ACol) do  begin    if Worksheet.IsMerged(Cell) then Continue;    Txt := Worksheet.ReadAsText(Cell);    if Txt = '' then Continue;    case Worksheet.ReadBiDiMode(Cell) of      bdRTL: RTL := true;    else RTL := false;    end;    W := RichTextWidth(Renderer.Canvas,Workbook,Rect(0,0,MaxInt,MaxInt),                     Txt+'|',Cell^.RichTextParams,Worksheet.ReadCellFontIndex(Cell),                     Worksheet.ReadTextRotation(Cell),false,RTL,Worksheet.ZoomFactor);    if W>MaxW then MaxW := W;  end;  if MaxW=-1 then MaxW := Worksheet.ReadDefaultColWidth(suPoints);  Worksheet.WriteColWidth(ACol,MaxW,suPoints);  Renderer.Free;end; procedure AutoFit;var  Worksheet : TsWorkSheet;  s       ,  c         : Integer;begin  for s:=0 to Workbook.GetWorksheetCount-1 do  begin    Worksheet := Workbook.GetWorksheetByIndex(s);    for c:=Worksheet.GetFirstColIndex to Worksheet.GetLastColIndex do AutoAdjustColumn(Worksheet,c);  end;  Workbook.WriteToFile('c:\WorksheetAdv.xls',sfExcel8,True);end; ---------------------------------------------------------------------- wp · 2018-01-30 11:44 Nice. Any special reason why the standard TBitmap is not enough? Not sure whether "if Worksheet.IsMerged(Cell) then continue" is correct - I think this skips the base of the merged block. Probably better to use "if Worksheet.IsMerged(Cell) and not Worksheet.IsMergeBase(Cell) then continue". ---------------------------------------------------------------------- Hafedh TRIMECHE · 2018-01-30 11:59 The code is recovered from the procedure: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i procedure TsCustomWorksheetGrid.AutoAdjustColumn(ACol: Integer);  Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i for cell in Worksheet.Cells.GetColEnumerator(GetWorkSheetCol(ACol)) do  begin    // Merged cells are not considered for calculating AutoColWidth -- see Excel.    if Worksheet.IsMerged(cell) then      continue;                                     TBGRABitmap is replaced by TBitmap. I guessed that TBGRABitmap is best in term of performance. Would the PDF export functionality be added to FPSpreadsheet package? Regards. ---------------------------------------------------------------------- wp · 2018-01-30 12:24 Quote from: Hafedh TRIMECHE on January 30, 2018, 12:59:13 pmThe code is recovered from the procedure: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i for cell in Worksheet.Cells.GetColEnumerator(GetWorkSheetCol(ACol)) do  begin    // Merged cells are not considered for calculating AutoColWidth -- see Excel.Ah, I remember... Quote from: Hafedh TRIMECHE on January 30, 2018, 12:59:13 pmWould the PDF export functionality be added to FPSpreadsheet package? Certainly not to the basic fpspreadsheet package, I try to keep packages as slim as possible. But another dedicated package would be worth considering. Unfortunately I don't have the time at the moment-