Forum > FPSpreadsheet

Stringgrid to XLSX with border lines.

(1/7) > >>

seghele0:
Can you give me the code to get lines between rows and columns.
The exported XLSX file will then look clearer to read.
 :)

--- 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";}};} ---unit Unit1;{$mode objfpc}{$H+}interfaceuses  Classes, SysUtils, Forms, Controls, Graphics, Dialogs, Grids, ExtCtrls,  StdCtrls, fpSpreadsheet, fpsTypes, xlsxOOXML;type  { TForm1 }  TForm1 = class(TForm)    Button1: TButton;    Panel1: TPanel;    StringGrid1: TStringGrid;    procedure Button1Click(Sender: TObject);  private  public  end;var  Form1: TForm1;implementationuses  DateUtils;{$R *.lfm}function MyStrToFloat(s: String): Double;var  fs: TFormatSettings;begin  fs := FormatSettings;  fs.DecimalSeparator := '.';  if not TryStrToFloat(s, Result, fs) then  begin    fs.DecimalSeparator := ',';    Result := StrToFloat(s, fs);  end;end; function MyStrToDate(s: String): TDate;begin  Result := ScanDateTime('yyyy"/"mm"/"dd', s);end; function MyStrToCurr(s: String): Double;begin  Result := MyStrToFloat(Copy(s, 1, Length(s)-2));end;//**************************{ TForm1 }procedure TForm1.Button1Click(Sender: TObject);var  workbook: TsWorkbook;  worksheet: TsWorksheet;  r, c: Integer;begin  // Create a workbook  workbook := TsWorkbook.Create;  try    // Add a worksheet to the workbook    worksheet := workbook.AddWorksheet('Export from grid');    // Write the title row    for c := 0 to StringGrid1.ColCount-1 do      worksheet.WriteText(0, c, StringGrid1.Cells[c, 0]);    // Write the data cells    for r := 1 to StringGrid1.RowCount-1 do    begin      for c := 0 to StringGrid1.ColCount-1 do        case c of          0: worksheet.WriteNumber(r, c, StrToInt(StringGrid1.cells[c, r]), nfFixed, 0);          1: worksheet.WriteNumber(r, c, MyStrToFloat(StringGrid1.Cells[c, r]), nfGeneral);          2: worksheet.WriteText(r, c, StringGrid1.Cells[c, r]);          3: worksheet.WriteDateTime(r, c, MyStrToDate(StringGrid1.Cells[c, r]), nfShortDate);          4: worksheet.WriteCurrency(r, c, MyStrToCurr(StringGrid1.Cells[c, r]), nfCurrency, 2, '$');        end;    end;    // Save the workbook as xlsx file    workbook.WriteToFile('Test.xlsx', sfOOXML, true);//    workbook.WriteToFile('Test.ods', sfOpenDocument, true);  // or save it for LibreOffice  finally    // Destroy the workbook after usage.    workbook.Free;  end;end;end. 

wp:
I suppose you mean cell border lines?

Call worksheet.WriteBorders to define which side of the cell will get a border, the sides are specified as a set of the values cbNorth, cbSouth, cbEast, cbWest, cbDiagUp, cbDiagDown (type TsCellBorder).


--- 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";}};} ---  // horizontal border lines above and below cell B2  worksheet.WriteBorders(1, 1, [cbNorth, cbSouth]);
If you don't do anything else these borders will have black solid lines. Additionally, you can also specify the appearance of each border line by calling WriteBorderColor and/or WriteBorderLineStyle:


--- 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";}};} ---  // The bottom border line of cell B2 should be a thick line in red  worksheet.WriteBorderLineStyle(1, 1, cbSouth, lsThick);  worksheet.WriteBorderColor(1, 1, cbSouth, scRed);
Both calls can be combined if you define a cell border style and use it in WriteBorderStyle:


--- 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";}};} ---const  THICK_RED_BORDER: TsCellBorderStyle = (LineStyle:lsThick; Color:scRed);...  worksheet.WriteBorderStyle(1, 1, cbSourth, THICK_RED_BORDER);
See attachment for a sample project similar to yours.

seghele0:
Thanks for the example (zip).
However, what I hope to get is lines around all cells, horizontally and vertically.
The number of rows can change and are therefore not predetermined.
Can someone correct the example?
Thanks.
 ;)

rvk:

--- Quote from: seghele0 on August 07, 2023, 05:51:29 pm ---However, what I hope to get is lines around all cells, horizontally and vertically.

--- End quote ---
Aren't there already lines between your rows and columns on screen?
Or is this for print (in which case you could enable that as page-options)?

There are several options for lines between rows and columns.
1) The normal view grid lines. These can also be turned on for printing.
2) More solid bold lines around each cell. (They will always show up on print.)

Personally I find the second a bit overwhelming when viewing a complete sheet.

wp:

--- Quote from: seghele0 on August 07, 2023, 05:51:29 pm ---Thanks for the example (zip).
However, what I hope to get is lines around all cells, horizontally and vertically.
The number of rows can change and are therefore not predetermined.
Can someone correct the example?
Thanks.
 ;)

--- End quote ---
Yes, I could. But I don't want to... Doing something by oneself is the most important step in learning it. It's really easy, and I told you everything you need in the previous post. Look at the code, try to understand it. Ask if there is something which I did not describe well enough. But don't ask me for a ready-made code sample.

P.S.
Well, I should be fair enough to point you also to the fpspreadsheet documentation: https://wiki.lazarus.freepascal.org/FPSpreadsheet#Cell_borders

Navigation

[0] Message Index

[#] Next page

Go to full version