Forum > FPSpreadsheet

[Solved] how can i set Fit column and RTL ?

(1/2) > >>

majid.ebru:
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  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---  MyWorkbook := TsWorkbook.Create;MyWorksheet := MyWorkbook.AddWorksheet(arySheet_Name[nI]);with(MyWorksheet)do begin ...               PageLayout.Options := PageLayout.Options + [poFitPages];   PageLayout.FitWidthToPages := 0;     // all columns on one page width   PageLayout.FitHeightToPages := 0; ...end;
but didn't work

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

thank you

wp:
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).

majid.ebru:
No
I just create DLL to export stringgrid to excel.

--- Code: Pascal  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---library exportXLS; {$mode objfpc}{$H+} uses  Classes, SysUtils, Grids,   Dialogs,  Interfaces, Controls, Graphics, StdCtrls,dynlibs, Types, fpspreadsheet, xlsbiff8, laz_fpspreadsheet,fpsTypes ,LCLIntf; const  z1 = 'DLL exportXLS ';   function chkTrue(arySTG:Array of TStringGrid  ;arySheet_Name,strTxt_Chek,aryColor_Int:Array of String  ;sAdres,sFont:String;aryColor:Array of TColor; nI: Integer): Boolean;begin //chkTrue(arySTG,arySheet_Name,strTxt_Chek,aryColor_Int,sAdres,sFont)                Result := True;         if(Length(arySTG) < 1)then Showmessage(z1+'21 arySTG is Empty')  else if(Length(arySheet_Name)<1)then Showmessage(z1+'22 arySheet_Name is Empty')         else if(Length(strTxt_Chek) < 1)then Showmessage(z1+'23 strTxt_Chek is Empty')         else if(Length(aryColor_Int) < 1)then Showmessage(z1+'24 aryColor_Int is Empty')         else if(Length(sAdres) < 1)then Showmessage(z1+'25 sAdres is Empty')         else if(Length(sFont) < 1)then Showmessage(z1+'26 sFont is Empty')         else if(Length(aryColor) < 1)then Showmessage(z1+'27 aryColor is Empty')  else if(Length(arySTG) <> Length(arySheet_Name))then           Showmessage(z1+'29 Length(arySheet_Name) is not Equal Length(arySTG)')  else if(Length(strTxt_Chek) <> Length(aryColor_Int))then           Showmessage(z1+'31 Length(strTxt_Chek) is not Equal Length(aryColor_Int)')                else Result := False;end; function find_Index(sSel:String;sA1,sA2:Array of String ): String ;var  nI : Integer;begin  Result := '0';  for nI := 0 to Length(sA1) - 1 do    if(sA1[nI].Contains(sSel))then begin                    Result := sA2[nI];      Exit;                                end;end; function find_Color(sSel:String): TsColor ;begin  case sSel of    '1' : Result := scBlack     ; // = $00000000;    '2' : Result := scWhite     ; // = $00FFFFFF;    '3' : Result := scRed       ; // = $000000FF;    '4' : Result := scGreen     ; // = $0000FF00;    '5' : Result := scBlue      ; // = $00FF0000;    '6' : Result := scYellow    ; // = $0000FFFF;    '7' : Result := scMagenta   ; // = $00FF00FF;    '8' : Result := scCyan      ; // = $00FFFF00;    '9' : Result := scDarkRed   ; // = $00000080;    '10': Result := scDarkGreen ; // = $00008000;    '11': Result := scDarkBlue  ; // = $00800000;    '12': Result := scOlive     ; // = $00008080;    '13': Result := scPurple    ; // = $00800080;    '14': Result := scTeal      ; // = $00808000;    '15': Result := scSilver    ; // = $00C0C0C0;    '16': Result := scGray      ; // = $00808080;    else Result := scTransparent;                end;end; function export2XLS(arySTG:Array of TStringGrid  ;arySheet_Name,strTxt_Chek,aryColor_Int:Array of String  ;sAdres,sFont:String;aryColor:Array of TColor):Boolean;var  MyWorkbook: TsWorkbook;  MyWorksheet: TsWorksheet;  nI ,nJ ,nK ,nL ,nR, nC: Integer;  strAray: TStringArray;  sStr2 : String;begin        Result := False;        //Showmessage('DLL export2XLS OK  ');        try                if(chkTrue(arySTG,arySheet_Name,strTxt_Chek                ,aryColor_Int,sAdres,sFont,aryColor,0))then                        Exit;                MyWorkbook := TsWorkbook.Create;                for nI := 0 to Length(arySTG) - 1 do begin                        MyWorksheet := MyWorkbook.AddWorksheet(arySheet_Name[nI]); // create Sheet                        with(arySTG[nI])do begin                                for nR := 0 to RowCount - 1 do                                        for nC := 0 to ColCount - 1 do                                                with(MyWorksheet)do begin                                                        WriteUTF8Text(nR, nC, arySTG[nI].Cells[nC, nR]);                                                        WriteBackgroundColor(nR, nC, aryColor[1 + (nR mod 2)]);              WriteVertAlignment(nR,nC,vaCenter);              WriteHorAlignment(nR,nC,haCenter);              WriteBorders(nR,nC,[cbNorth, cbWest, cbEast, cbSouth]);              PageLayout.Options := PageLayout.Options + [poFitPages];              PageLayout.FitWidthToPages := 0;              PageLayout.FitHeightToPages := 0;                                                        if(nR = 0)then begin // Title Row                                                                WriteBackgroundColor(nR, nC, InvertColor(find_Color('6')));                                                                WriteFont(nR, nC, sFont,10,[fssBold,fssItalic]                  ,find_Color('6'));                                                        end                                                        else begin                                                                WriteFont(nR, nC, sFont, 10, [],aryColor[0]);                                                        end;                                        for nK := 0 to Length(strTxt_Chek) - 1 do                                                if(arySTG[nI].Cells[nC, nR].Contains(strTxt_Chek[nK]))then begin                                                        sStr2 := find_Index(strTxt_Chek[nK]                                                .    ,strTxt_Chek,aryColor_Int);                                                if(not sStr2.Equals('0'))then begin                                                        WriteBackgroundColor(nR,nC,find_Color(sStr2));                                                 //WriteCellFormat([nR,nC],haCenter);                                                  WriteFont(nR,nC,sFont,10,[],InvertColor(find_Color(sStr2)));                                                end;                                                //WriteBackgroundColor(nR, nC, $0000FF);                                                // $BBGGRR for color, i.e $0000FF is red                                                //WriteFont(nR, nC, sFont, 12, [fssBold,fssUnderline],$0000FF);                                                // Font 'Arial', size 10, bold, black                                        end;                                end;                        end;                end;                MyWorkbook.WriteToFile(sAdres, sfOOXML); // sfidExcel8                Result := True;        finally        MyWorkbook.Free;        end;end; exports  export2XLS; begin 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:

--- Quote from: majid.ebru on December 20, 2023, 08:10:48 pm ---but I don't know how can I change page layout to right to left?

--- End 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.


--- Quote from: majid.ebru on December 20, 2023, 08:10:48 pm ---And I don't know how can I fit column width?

--- End 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.


--- Quote from: majid.ebru on December 20, 2023, 08:10:48 pm ---And I don't know what is different between TsWorksheet and TsWorksheetGrid ? I use TsWorksheet

--- 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)

majid.ebru:
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.

--- End quote ---

i use this code :  OK

--- Code: Pascal  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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.

--- End quote ---

i use this code :

--- Code: Pascal  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---nW := px2mm(arySTG[nI].ColWidths[nC], ScreenInfo.PixelsPerInchX);WriteColWidth(nC, nW + EXTRA_SPACE, suMillimeters);
but i changed this line :

--- Code: Pascal  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---function px2mm(px, ppi: Integer): Double;begin  Result := px/ppi * 17.4; //  ==> 25.4end;  


--- 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)

--- End quote ---

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

Navigation

[0] Message Index

[#] Next page

Go to full version