Recent

Author Topic: Stringgrid to XLSX with border lines.  (Read 7567 times)

seghele0

  • Full Member
  • ***
  • Posts: 173
Re: Stringgrid to XLSX with border lines.
« Reply #30 on: August 16, 2023, 03:54:51 pm »
Hopefully my last question on this topic.
Can you please provide the code to adjust the RowHeight, so that the rows (from Row 1 to end) of the grid are set to a different height than for the header (Row 0) ?
 ;)

Code: Pascal  [Select][+][-]
  1.   Varworksheet.WriteRowHeight(0, round(14*1.5), suPoints);  

wp

  • Hero Member
  • *****
  • Posts: 11996
Re: Stringgrid to XLSX with border lines.
« Reply #31 on: August 16, 2023, 05:58:32 pm »
Here is a modification of your code which shows how to change row heights. I noticed that when the DefaultFont of the workbook is changed also the DefaultRowheight of the worksheets must be adapted - I added this. And I also introduced "speaking" constants for the "magic" values 12, 10, and 1.5 so that the code is easier to understand.

Somewhere towards the end I also show how rowheights can be adjusted automatically. This is not needed here because the row height already have been set (and in case of a large worksheet, the automatic calculation may slow down the code because it must iterate over all cells which, in the worst case, may have different fonts).

Code: Pascal  [Select][+][-]
  1. unit Unit1;
  2. // Update:   15/08/2023.
  3. {$mode objfpc}{$H+}
  4. interface
  5. uses
  6.   Classes, SysUtils, Forms, Controls, Graphics, Dialogs, Grids, StdCtrls;
  7. type
  8.   { TForm1 }
  9.   TForm1 = class(TForm)
  10.     Button1: TButton;
  11.     StringGrid1: TStringGrid;
  12.     procedure Button1Click(Sender: TObject);
  13.   private
  14.   public
  15.   end;
  16. var
  17.   Form1: TForm1;
  18. implementation
  19. uses
  20.   DateUtils,
  21.   fpSpreadsheet, fpstypes, fpsUtils,
  22.   xlsxooxml;
  23. {$R *.lfm}
  24. { TForm1 }
  25. //**************************************************
  26. procedure TForm1.Button1Click(Sender: TObject);
  27. const
  28.   HEADER_FONT_SIZE = 10;   // in Points
  29.   DEFAULT_FONT_SIZE = 12;  // in Points
  30.   NICE_ROWHEIGHT_FACTOR = 1.5;  // adds extra margin to the cell
  31. var
  32.   VarWORKBOOK:  TsWorkbook;
  33.   VarWORKSHEET: TsWorksheet;
  34.   VarCELL: pCell;
  35.   VarR: Integer;  // Row
  36.   VarC: Integer;  // Column
  37. begin
  38.   //---------------------------------------------------------
  39.   // Create a workbook
  40.   //---------------------------------------------------------
  41.   VarWORKBOOK := TsWorkbook.Create;
  42.   try
  43.     // Add a worksheet to the workbook
  44.     VarWORKSHEET := VarWORKBOOK.AddWorksheet('Export from grid');
  45.     // set default Font for the entire workbook  //
  46.     // Later on the Font Size for the "header" will be changed !!! //
  47.     VarWORKBOOK.SetDefaultFont('Arial',DEFAULT_FONT_SIZE);
  48.     VarWORKSHEET.WriteDefaultRowHeight(DEFAULT_FONT_SIZE*NICE_ROWHEIGHT_FACTOR, suPoints);
  49.     //************************************************************
  50.     // set default Column widths like in the StrngGrid //
  51.     //Making use of "lpsUtils" in uses //
  52.     for VarC := 0 to StringGrid1.ColCount-1 do
  53.       VarWORKSHEET.writecolwidth
  54.         (VarC, pxToPts(StringGrid1.ColWidths[VarC],Screen.PixelsPerInch),suPoints);
  55.     //--------------------------------------------------------
  56.     // Write the title row
  57.     //---------------------------------------------------------
  58.     for VarC := 0 to StringGrid1.ColCount-1 do
  59.     begin
  60.       //Write the text of the header cell //
  61.       VarCELL:= VarWORKSHEET.WriteText(0, VarC, StringGrid1.Cells[VarC, 0]);
  62.       // Center text Horizontally //
  63.       VarWORKSHEET.WriteHorAlignment(VarCELL,haCenter);
  64.       // Center text Vertically //
  65.       VarWORKSHEET.WriteVertAlignment(VarCELL,vaCenter);
  66.       // Draw a default border around each cell
  67.       VarWORKSHEET.writeborders(VarCELL,[cbNorth,cbSouth,cbEast,cbWest]);
  68.       // text in header row in Bold //
  69.       VarWORKSHEET.WriteFontStyle(VarCELL, [fssBold]);
  70.       // text header font size //
  71.       VarWORKSHEET.WriteFontSize(VarCELL,HEADER_FONT_SIZE);
  72.       // name of the Font //
  73.       VarWORKSHEET.WriteFontName(VarCELL, 'Arial');
  74.       // Color Background of header cell //
  75.       VarWORKSHEET.WriteBackgroundColor(VarCELL,scYellow);
  76.      end;
  77.     // Set the row height for the header row which has a smaller font than
  78.     // the rest
  79.     VarWORKSHEET.WriteRowHeight(0, HEADER_FONT_SIZE*NICE_ROWHEIGHT_FACTOR, suPoints);
  80.     //-------------------------------------------------
  81.     // Write the data cells
  82.     //-------------------------------------------------
  83.     for VarR := 1 to StringGrid1.RowCount-1 do
  84.     begin
  85.       for VarC := 0 to StringGrid1.ColCount-1 do
  86.       begin
  87.         VarCELL:= VarWORKSHEET.WriteText
  88.                  (VarR,VarC,StringGrid1.Cells[VarC,VarR]);
  89.         // write border around the cells //
  90.         VarWORKSHEET.WriteBorders
  91.                  (VarCELL,[cbNorth,cbSouth,cbEast,cbWest]);
  92.         VarWORKSHEET.WriteHorAlignment
  93.                  (VarCELL,haCenter);
  94.         // By default cells are bottom-aligned. It looks better if they are centered.
  95.         VarWORKSHEET.WriteVertAlignment
  96.                  (VarCELL,vaCENTER);
  97.       end;
  98.     end;
  99.     (*
  100.     // Just to show you automatic row height adjustment (not needed since the
  101.     // row heights have been handled already).
  102.  
  103.     //---------------------------------------------------------
  104.     // Adjust the row heights
  105.     //---------------------------------------------------------
  106.     for VarR := 0 to VarWORKSHEET.GetLastRowIndex do
  107.     begin
  108.       VarWORKSHEET.WriteRowHeight(VarR, NICE_ROWHEIGHT_FACTOR * VarWORKSHEET.CalcAutoRowHeight(VarR), VarWORKBOOK.Units);
  109.     end;
  110.     *)
  111.     //---------------------------------------------------------
  112.     // Save the workbook as xlsx file
  113.     //---------------------------------------------------------
  114.     Showmessage('Will save to xlsx');
  115.     VarWORKBOOK.WriteToFile('Test.xlsx', sfOOXML, true);
  116.     // workbook.WriteToFile('Test.ods', sfOpenDocument, true);
  117.     // or save it for LibreOffice
  118.     Showmessage('Saving done !');
  119.   finally
  120.     // Destroy the workbook after usage.
  121.     VarWORKBOOK.Free;
  122.   end;
  123. end;
  124.  
  125. end.

seghele0

  • Full Member
  • ***
  • Posts: 173
Re: Stringgrid to XLSX with border lines.
« Reply #32 on: August 17, 2023, 12:05:42 pm »
WP,
Your programming work is great, thank you.  :)
Your support and dedication to my questions is a wonderful experience.

Perhaps adding this example to https://wiki.lazarus.freepascal.org/FPSpreadsheet,
would be an additional support for many beginners.
 ::)

 

TinyPortal © 2005-2018