Recent

Author Topic: [Solved] how can i set Fit column and RTL ?  (Read 1911 times)

majid.ebru

  • Hero Member
  • *****
  • Posts: 508
[Solved] how can i set Fit column and RTL ?
« on: December 20, 2023, 12:04:01 pm »
HI
please help or guide me

I am a beginner in using "FPSpreadsheet".

1 - how can i fit width of all column?
read this topic https://forum.lazarus.freepascal.org/index.php/topic,39805.15.html
and i used those codes
Code: Pascal  [Select][+][-]
  1.  
  2. MyWorkbook := TsWorkbook.Create;
  3. MyWorksheet := MyWorkbook.AddWorksheet(arySheet_Name[nI]);
  4. with(MyWorksheet)do begin
  5. ...            
  6.    PageLayout.Options := PageLayout.Options + [poFitPages];
  7.    PageLayout.FitWidthToPages := 0;     // all columns on one page width
  8.    PageLayout.FitHeightToPages := 0;
  9. ...
  10. end;

but didn't work

2 - how can i set page layout to "Right to Left"?

thank you
« Last Edit: December 21, 2023, 05:27:41 am by majid.ebru »

wp

  • Hero Member
  • *****
  • Posts: 12013
Re: how can i set Fit column and RTL ?
« Reply #1 on: December 20, 2023, 07:08:29 pm »
Do you still export from a TStringGrid or did you migrate to a TsWorksheetGrid?

If you work with TStringGrid, have a look at the attached demo where you can measure the optimum column widths in the grid and then convert them to the worksheet columns. But note that I never understood how the office applications measure the column width, and therefore the result may be off by a few pixels. To avoid truncation I added a safety margin of 3 millimeters. The demo also shows how to handle right-to-left text. But disclaimer here as well: I do not have access to such a system, therefore the result may be completely wrong (it would be good to get feedback on this).
« Last Edit: December 20, 2023, 07:59:52 pm by wp »

majid.ebru

  • Hero Member
  • *****
  • Posts: 508
Re: how can i set Fit column and RTL ?
« Reply #2 on: December 20, 2023, 08:10:48 pm »
No
I just create DLL to export stringgrid to excel.
Code: Pascal  [Select][+][-]
  1. library exportXLS;
  2.  
  3. {$mode objfpc}{$H+}
  4.  
  5. uses
  6.   Classes, SysUtils, Grids,   Dialogs,  Interfaces,
  7.  Controls, Graphics, StdCtrls,
  8. dynlibs, Types, fpspreadsheet, xlsbiff8, laz_fpspreadsheet
  9. ,fpsTypes ,LCLIntf;
  10.  
  11. const
  12.   z1 = 'DLL exportXLS ';
  13.  
  14.  
  15.  
  16. function chkTrue(arySTG:Array of TStringGrid
  17.   ;arySheet_Name,strTxt_Chek,aryColor_Int:Array of String
  18.   ;sAdres,sFont:String;aryColor:Array of TColor; nI: Integer): Boolean;
  19. begin //chkTrue(arySTG,arySheet_Name,strTxt_Chek,aryColor_Int,sAdres,sFont)
  20.                 Result := True;
  21.          if(Length(arySTG) < 1)then Showmessage(z1+'21 arySTG is Empty')
  22.   else if(Length(arySheet_Name)<1)then Showmessage(z1+'22 arySheet_Name is Empty')
  23.          else if(Length(strTxt_Chek) < 1)then Showmessage(z1+'23 strTxt_Chek is Empty')
  24.          else if(Length(aryColor_Int) < 1)then Showmessage(z1+'24 aryColor_Int is Empty')
  25.          else if(Length(sAdres) < 1)then Showmessage(z1+'25 sAdres is Empty')
  26.          else if(Length(sFont) < 1)then Showmessage(z1+'26 sFont is Empty')
  27.          else if(Length(aryColor) < 1)then Showmessage(z1+'27 aryColor is Empty')
  28.   else if(Length(arySTG) <> Length(arySheet_Name))then
  29.            Showmessage(z1+'29 Length(arySheet_Name) is not Equal Length(arySTG)')
  30.   else if(Length(strTxt_Chek) <> Length(aryColor_Int))then
  31.            Showmessage(z1+'31 Length(strTxt_Chek) is not Equal Length(aryColor_Int)')
  32.                 else Result := False;
  33. end;
  34.  
  35. function find_Index(sSel:String;sA1,sA2:Array of String ): String ;
  36. var
  37.   nI : Integer;
  38. begin
  39.   Result := '0';
  40.   for nI := 0 to Length(sA1) - 1 do
  41.     if(sA1[nI].Contains(sSel))then begin
  42.                     Result := sA2[nI];
  43.       Exit;
  44.                                 end;
  45. end;
  46.  
  47. function find_Color(sSel:String): TsColor ;
  48. begin
  49.   case sSel of
  50.     '1' : Result := scBlack     ; // = $00000000;
  51.     '2' : Result := scWhite     ; // = $00FFFFFF;
  52.     '3' : Result := scRed       ; // = $000000FF;
  53.     '4' : Result := scGreen     ; // = $0000FF00;
  54.     '5' : Result := scBlue      ; // = $00FF0000;
  55.     '6' : Result := scYellow    ; // = $0000FFFF;
  56.     '7' : Result := scMagenta   ; // = $00FF00FF;
  57.     '8' : Result := scCyan      ; // = $00FFFF00;
  58.     '9' : Result := scDarkRed   ; // = $00000080;
  59.     '10': Result := scDarkGreen ; // = $00008000;
  60.     '11': Result := scDarkBlue  ; // = $00800000;
  61.     '12': Result := scOlive     ; // = $00008080;
  62.     '13': Result := scPurple    ; // = $00800080;
  63.     '14': Result := scTeal      ; // = $00808000;
  64.     '15': Result := scSilver    ; // = $00C0C0C0;
  65.     '16': Result := scGray      ; // = $00808080;
  66.     else Result := scTransparent;
  67.                 end;
  68. end;
  69.  
  70. function export2XLS(arySTG:Array of TStringGrid
  71.   ;arySheet_Name,strTxt_Chek,aryColor_Int:Array of String
  72.   ;sAdres,sFont:String;aryColor:Array of TColor):Boolean;
  73. var
  74.   MyWorkbook: TsWorkbook;
  75.   MyWorksheet: TsWorksheet;
  76.   nI ,nJ ,nK ,nL ,nR, nC: Integer;
  77.   strAray: TStringArray;
  78.   sStr2 : String;
  79. begin
  80.         Result := False;
  81.         //Showmessage('DLL export2XLS OK  ');
  82.         try
  83.                 if(chkTrue(arySTG,arySheet_Name,strTxt_Chek
  84.                 ,aryColor_Int,sAdres,sFont,aryColor,0))then
  85.                         Exit;
  86.                 MyWorkbook := TsWorkbook.Create;
  87.                 for nI := 0 to Length(arySTG) - 1 do begin
  88.                         MyWorksheet := MyWorkbook.AddWorksheet(arySheet_Name[nI]); // create Sheet
  89.                         with(arySTG[nI])do begin
  90.                                 for nR := 0 to RowCount - 1 do
  91.                                         for nC := 0 to ColCount - 1 do
  92.                                                 with(MyWorksheet)do begin
  93.                                                         WriteUTF8Text(nR, nC, arySTG[nI].Cells[nC, nR]);
  94.                                                         WriteBackgroundColor(nR, nC, aryColor[1 + (nR mod 2)]);
  95.               WriteVertAlignment(nR,nC,vaCenter);
  96.               WriteHorAlignment(nR,nC,haCenter);
  97.               WriteBorders(nR,nC,[cbNorth, cbWest, cbEast, cbSouth]);
  98.               PageLayout.Options := PageLayout.Options + [poFitPages];
  99.               PageLayout.FitWidthToPages := 0;
  100.               PageLayout.FitHeightToPages := 0;
  101.                                                         if(nR = 0)then begin // Title Row
  102.                                                                 WriteBackgroundColor(nR, nC, InvertColor(find_Color('6')));
  103.                                                                 WriteFont(nR, nC, sFont,10,[fssBold,fssItalic]
  104.                   ,find_Color('6'));
  105.                                                         end
  106.                                                         else begin
  107.                                                                 WriteFont(nR, nC, sFont, 10, [],aryColor[0]);
  108.                                                         end;
  109.                                         for nK := 0 to Length(strTxt_Chek) - 1 do
  110.                                                 if(arySTG[nI].Cells[nC, nR].Contains(strTxt_Chek[nK]))then begin
  111.                                                         sStr2 := find_Index(strTxt_Chek[nK]
  112.                                                 .    ,strTxt_Chek,aryColor_Int);
  113.                                                 if(not sStr2.Equals('0'))then begin
  114.                                                         WriteBackgroundColor(nR,nC,find_Color(sStr2));
  115.                                                  //WriteCellFormat([nR,nC],haCenter);
  116.                                                   WriteFont(nR,nC,sFont,10,[],InvertColor(find_Color(sStr2)));
  117.                                                 end;
  118.                                                 //WriteBackgroundColor(nR, nC, $0000FF);
  119.                                                 // $BBGGRR for color, i.e $0000FF is red
  120.                                                 //WriteFont(nR, nC, sFont, 12, [fssBold,fssUnderline],$0000FF);
  121.                                                 // Font 'Arial', size 10, bold, black
  122.                                         end;
  123.                                 end;
  124.                         end;
  125.                 end;
  126.                 MyWorkbook.WriteToFile(sAdres, sfOOXML); // sfidExcel8
  127.                 Result := True;
  128.         finally
  129.         MyWorkbook.Free;
  130.         end;
  131. end;
  132.  
  133. exports  export2XLS;
  134.  
  135. begin
  136.  
  137. end.

but I don't know how can I change page layout to right to left?
And I don't know how can I fit column width?
And I don't know what is different between TsWorksheet and TsWorksheetGrid ? I use TsWorksheet
Thank you WP maste

wp

  • Hero Member
  • *****
  • Posts: 12013
Re: how can i set Fit column and RTL ?
« Reply #3 on: December 20, 2023, 11:09:29 pm »
but I don't know how can I change page layout to right to left?
Set the worksheet property BiDiMode to bdRTL (RTL = right-to-left). This makes the entire worksheet right-to-left, and the page layout inherits this.

And I don't know how can I fit column width?
See the code in my previous post. But I am adding an updated version of the code in which also a pagelayout is included.

And I don't know what is different between TsWorksheet and TsWorksheetGrid ? I use TsWorksheet
TsWorksheet is a nonvisual class which provides the data-structure for spreadsheet data and their formatting, it has only few dependencies and can be used also in commandline programs. TsWorksheetGrid is a visual LCL control which inherits from TCustomGrid (like TStringGrid); it is very similar to TStringGrid, but has additional spreadsheet features, such as individual cell formatting, colors, text alignment, fonts, formula support etc (see https://wiki.lazarus.freepascal.org/TsWorksheetGrid, or https://wiki.lazarus.freepascal.org/FPSpreadsheet_tutorial:_Writing_a_mini_spreadsheet_application)
« Last Edit: December 20, 2023, 11:11:21 pm by wp »

majid.ebru

  • Hero Member
  • *****
  • Posts: 508
Re: how can i set Fit column and RTL ?
« Reply #4 on: December 21, 2023, 05:27:18 am »
Hi
thank you WP master

Quote
Set the worksheet property BiDiMode to bdRTL (RTL = right-to-left). This makes the entire worksheet right-to-left, and the page layout inherits this.

i use this code :  OK
Code: Pascal  [Select][+][-]
  1. MyWorksheet.BiDiMode := bdRTL ;


Quote
See the code in my previous post. But I am adding an updated version of the code in which also a pagelayout is included.

i use this code :
Code: Pascal  [Select][+][-]
  1. nW := px2mm(arySTG[nI].ColWidths[nC], ScreenInfo.PixelsPerInchX);
  2. WriteColWidth(nC, nW + EXTRA_SPACE, suMillimeters);

but i changed this line :
Code: Pascal  [Select][+][-]
  1. function px2mm(px, ppi: Integer): Double;
  2. begin
  3.   Result := px/ppi * 17.4; //  ==> 25.4
  4. end;  


Quote
TsWorksheet is a nonvisual class which provides the data-structure for spreadsheet data and their formatting, it has only few dependencies and can be used also in commandline programs. TsWorksheetGrid is a visual LCL control which inherits from TCustomGrid (like TStringGrid); it is very similar to TStringGrid, but has additional spreadsheet features, such as individual cell formatting, colors, text alignment, fonts, formula support etc (see https://wiki.lazarus.freepascal.org/TsWorksheetGrid, or https://wiki.lazarus.freepascal.org/FPSpreadsheet_tutorial:_Writing_a_mini_spreadsheet_application)

i convert Stringgrid to Excel with DLL file , so i can't use TsWorksheetGrid

wp

  • Hero Member
  • *****
  • Posts: 12013
Re: how can i set Fit column and RTL ?
« Reply #5 on: December 21, 2023, 11:15:10 am »
i use this code :
Code: Pascal  [Select][+][-]
  1. nW := px2mm(arySTG[nI].ColWidths[nC], ScreenInfo.PixelsPerInchX);
  2. WriteColWidth(nC, nW + EXTRA_SPACE, suMillimeters);

but i changed this line :
Code: Pascal  [Select][+][-]
  1. function px2mm(px, ppi: Integer): Double;
  2. begin
  3.   Result := px/ppi * 17.4; //  ==> 25.4
  4. end;  

Why? One inch *is* 25.4 millimeters...

majid.ebru

  • Hero Member
  • *****
  • Posts: 508
Re: [Solved] how can i set Fit column and RTL ?
« Reply #6 on: December 23, 2023, 05:33:59 am »
sorry for late reply
this is result of width
« Last Edit: December 23, 2023, 05:37:19 am by majid.ebru »

wp

  • Hero Member
  • *****
  • Posts: 12013
Re: [Solved] how can i set Fit column and RTL ?
« Reply #7 on: December 23, 2023, 12:44:41 pm »
Can you give me the name of the font that you use for column header and cells? I would like to see why too wide columns are returned here with the correct inch-to-mm conversion factor.

majid.ebru

  • Hero Member
  • *****
  • Posts: 508
Re: [Solved] how can i set Fit column and RTL ?
« Reply #8 on: December 24, 2023, 12:58:12 pm »
you said true , i used different font

Mikhak font  : https://aminabedi68.github.io/Mikhak/

or

Mikhak font  :https://fontesk.com/mikhak-typeface/

wp

  • Hero Member
  • *****
  • Posts: 12013
Re: [Solved] how can i set Fit column and RTL ?
« Reply #9 on: December 24, 2023, 05:00:55 pm »
Strange... I don't know what's wrong here. But obviously you found a workaround.

 

TinyPortal © 2005-2018