Recent

Author Topic: Worksheet.PageLayout  (Read 645 times)

seghele0

  • Full Member
  • ***
  • Posts: 238
Worksheet.PageLayout
« on: November 29, 2024, 03:46:58 pm »
I'm trying to add a line with: "Worksheet.PageLayout.HorizontalCentered := True;" but I get ERROR: "Identifier idents no member HorizontalCentered ".
Please help.

Code: Pascal  [Select][+][-]
  1. ………..
  2. uses
  3.   Windows,Dialogs, Interfaces, Classes, SysUtils, FileUtil,
  4.   Forms, Controls, ExtCtrls, StdCtrls, ComCtrls, Graphics, Menus,
  5.   Grids, Buttons, IniFiles,Printers, OSPrinters, PrintersDlgs,
  6.   DateUtils, Variants, lMessages, LCLProc, LCLType,  LCLIntf,
  7.   fpsexport, fpSpreadsheet, fpstypes, fpsUtils, xlsxooxml;      
  8. const
  9.   HEADER_FONT_SIZE = 11;   // in Points
  10.   DEFAULT_FONT_SIZE = 15;  // in Points
  11.   NICE_ROWHEIGHT_FACTOR = 1.5;  // adds extra margin to the cell
  12. var                                      
  13.   WORKBOOK: TsWorkbook;
  14.   WORKSHEET: TsWorksheet;
  15.   CELL: pCell;
  16.   Row: Integer;  // Row
  17.   Col: Integer;  // Column
  18.   tmpvalue: Single;
  19.   tmpColCountArr: array of Integer;   //col length array    
  20. …….
  21. ……
  22.   Workbook := TsWorkbook.Create;
  23.   try
  24.     // Add a worksheet to the workbook
  25.     Worksheet := Workbook.AddWorksheet('Export from grid');
  26.     // Set default font for the workbook
  27.     Workbook.SetDefaultFont('Dejavu Sans Mono', DEFAULT_FONT_SIZE);
  28.     Worksheet.WriteDefaultRowHeight(DEFAULT_FONT_SIZE * NICE_ROWHEIGHT_FACTOR, suPoints);
  29.     // Set default column widths based on StringGrid column sizes
  30.     for Col := 0 to StringGrid1.ColCount - 1 do
  31.       Worksheet.WriteColWidth(Col, pxToPts(StringGrid1.ColWidths[Col], Screen.PixelsPerInch), suPoints);
  32.     // Configure the header row
  33.     for Col := 0 to StringGrid1.ColCount - 1 do
  34.     begin
  35.       // Write header text
  36.       Cell := Worksheet.WriteText(0, Col, StringGrid1.Cells[Col, 0]);
  37.       Worksheet.WriteHorAlignment(Cell, haCenter);       // Center horizontally
  38.       Worksheet.WriteVertAlignment(Cell, vaCenter);      // Center vertically
  39.       Worksheet.WriteBorders(Cell, [cbNorth, cbSouth, cbEast, cbWest]); // Add borders
  40.       Worksheet.WriteFontStyle(Cell, [fssBold]);         // Bold font
  41.       Worksheet.WriteFontSize(Cell, HEADER_FONT_SIZE);   // Set font size
  42.       Worksheet.WriteFontName(Cell, 'Dejavu Sans Mono'); // Set font name
  43.       Worksheet.WriteBackgroundColor(Cell, scYellow);    // Set background color
  44.     end;
  45.     // Adjust the height for the header row
  46.     Worksheet.WriteRowHeight(0, HEADER_FONT_SIZE * NICE_ROWHEIGHT_FACTOR, suPoints);
  47.     // Write data cells
  48.     for Row := 1 to StringGrid1.RowCount - 1 do
  49.     begin
  50.       for Col := 0 to StringGrid1.ColCount - 1 do
  51.       begin
  52.         // Check if cell contains a number
  53.         if TryStrToFloat(StringGrid1.Cells[Col, Row], TmpValue) then
  54.           Cell := Worksheet.WriteNumber(Row, Col, TmpValue)
  55.         else
  56.           Cell := Worksheet.WriteText(Row, Col, StringGrid1.Cells[Col, Row]);
  57.         // Add borders and center text
  58.         Worksheet.WriteBorders(Cell, [cbNorth, cbSouth, cbEast, cbWest]);
  59.         Worksheet.WriteHorAlignment(Cell, haCenter);
  60.         Worksheet.WriteVertAlignment(Cell, vaCenter);
  61.       end;
  62.     end;
  63.     // Adjust row heights based on content
  64.     for Row := 0 to Worksheet.GetLastRowIndex do
  65.       Worksheet.WriteRowHeight(Row, NICE_ROWHEIGHT_FACTOR * Worksheet.CalcAutoRowHeight(Row), Workbook.Units);
  66.  
  67. [color=red]// --> !!!!  Set page layout: horizontally center the table[/color]
  68.   [b]  Worksheet.PageLayout.HorizontalCentered := True;[/b]
  69.  
  70.     // Save the workbook as an XLSX file
  71.     Workbook.WriteToFile('WIEZEN.xlsx', sfOOXML, True);
  72.   finally
  73.     Workbook.Free;
  74.   end;                                        

dseligo

  • Hero Member
  • *****
  • Posts: 1408
Re: Worksheet.PageLayout
« Reply #1 on: November 29, 2024, 04:09:00 pm »
I think you have to do it for each cell (i.e. you can't do it for entire sheet).

wp

  • Hero Member
  • *****
  • Posts: 12459
Re: Worksheet.PageLayout
« Reply #2 on: November 29, 2024, 04:15:11 pm »
What do you want to do? Centering a table in a pagelayout for printing?

The correct way is this:
Code: Pascal  [Select][+][-]
  1.   Worksheet.PageLayout.Options := Workseet.Pagelayout.Option + [poHorCentered];

seghele0

  • Full Member
  • ***
  • Posts: 238
Re: Worksheet.PageLayout
« Reply #3 on: November 30, 2024, 12:01:34 pm »
Thanks "WP".
Your code is error-free, but does not place the stringgrid horizontally in the center of the page (see attachment).
Your code:
Code: Pascal  [Select][+][-]
  1. Worksheet.PageLayout.Options:= Worksheet.Pagelayout.Options + [poHorCentered];  
Hopefully you can offer a solution.
 ;)

wp

  • Hero Member
  • *****
  • Posts: 12459
Re: Worksheet.PageLayout
« Reply #4 on: November 30, 2024, 05:44:47 pm »
Oh, this case among the many print options must have been forgotton in xlsx. Fixed in svn repository. Fixed also a bug in ods print page orientation

seghele0

  • Full Member
  • ***
  • Posts: 238
Re: Worksheet.PageLayout
« Reply #5 on: December 01, 2024, 01:19:27 pm »
WP, what exactly should I do now?
Sotty, but your explanation isn't clear to me.
Do I need to update the "fpSpreadsheet" component and where can I download it and what's the best way to update it in Lazarus.
 ;)



wp

  • Hero Member
  • *****
  • Posts: 12459
Re: Worksheet.PageLayout
« Reply #6 on: December 01, 2024, 01:49:04 pm »
Sorry. Yes, you must get yourself the latest version of the FPSpreadsheet library: I am assuming that you do not use svn. Therefore you should go to https://sourceforge.net/p/lazarus-ccr/svn/HEAD/tree/components/fpspreadsheet/ and download the "snapshot" from that page. Unzip it to some folder.

Open the IDE and go to "Package" > "Load package file (.lpk)". Navigate to the folder to which you had unzipped the download, and select "laz_fpspreadsheet.lpk" > "Open". Click "Compile" (not absolutely necessary).

If you use the visual spreadsheet components you must repeat this with "laz_fpspreadsheet_visual.lpk" and with "laz_fpspreadsheet_visual_dsgn.lpk". In case of the latter package you must also click "Use" > "Install" and confirm the prompt to rebuild the IDE. When the IDE has restarted after a while you will have the new fpspreadsheet.

(You must repeat the same with "laz_fpspreadsheet_dataset.lpk" if you need to open spreadsheets as a database, and "laz_fpspreadsheetexport_visual.lpk" if you want to export database tables to spreadsheet files. And if you have encrypted Excel files you must also open the "laz_fpspreadsheet_crypto.lpk" package).

seghele0

  • Full Member
  • ***
  • Posts: 238
Re: Worksheet.PageLayout
« Reply #7 on: December 01, 2024, 04:30:15 pm »
"WP", real thanks.
Your clear explanation is highly recommended for everyone.
The StingGrid is now vertically in the center of the (printed) page.
 :)
In the short future, there will be a few more questions to make the code of my program more readable and efficient.
 ;)


 

TinyPortal © 2005-2018