Recent

Author Topic: Trouble with Cell borders [ Solved]  (Read 2234 times)

Badger

  • Full Member
  • ***
  • Posts: 144
Trouble with Cell borders [ Solved]
« on: April 05, 2023, 09:51:20 am »
 Hi All
I'm trying to set a row of cells with their top borders thin and blue.  I have tried a couple of ways but can't get them to run.

First I tried the following code from the Wiki:-
Code: Pascal  [Select][+][-]
  1. for x:=0 to 5 do
  2. begin
  3.   SelectedCell:=ARS.ActiveWorksheet.WriteBorders(y,x,[cbNorth]);
  4.   ARS.ActiveWorksheet.WriteBorderLineStyle(SelectedCell,cbNorth, lsThin, scBlue);
  5. end
  6.  
This throws up an error Got "ShortInt", expected "TsLineStyle" after scBlue

I then tried this from a previous forum thread but again couldn't get it to run:-
Code: Pascal  [Select][+][-]
  1. SelectedCell := ARS.ActiveWorksheet.WriteText(5, 2, 'abc');
  2. SelectedCell ^.Borders[[cbNorth, cbSouth]] := CellBorderStyle(lsThick, scGreen);
  3.  
It didn't like .borders - "no ident" and Got "Word", expected "TsLineStyle" after scGreen

Any suggestions (Helpful  :D)
« Last Edit: July 30, 2023, 01:31:54 pm by Badger »
Badger
(A bad tempered, grumpy animal that sleeps most of the winter!)

If at first you don't succeed - you're running about average!

I'm using Windows 10 Lazarus v2.4.4  FPC 3.2.2   Win 32/64

wp

  • Hero Member
  • *****
  • Posts: 12013
Re: Trouble with Cell borders
« Reply #1 on: April 05, 2023, 10:31:38 am »
Code: Pascal  [Select][+][-]
  1.   ARS.ActiveWorksheet.WriteBorderLineStyle(SelectedCell,cbNorth, lsThin, scBlue);
There's a variety of similar calls - look at the wiki example carefully: It shows "WriteBorderStyle" (without "Line" in between), and this works.
Code: Pascal  [Select][+][-]
  1. program Project1;
  2.  
  3. uses
  4.   fpSpreadsheet, fpsTypes, xlsxOOXML;
  5. var
  6.   book: TsWorkbook;
  7.   sheet: TsWorksheet;
  8.   cell: PCell;
  9. begin
  10.   book := TsWorkbook.Create;
  11.   sheet := book.AddWorksheet('Test');
  12.   cell := sheet.WriteBorders(1, 1, [cbNorth]);
  13.   sheet.WriteBorderStyle(cell, cbNorth, lsThin, scBlue);
  14.   { alternatively:
  15.   sheet.WriteBorderLineStyle(cell, cbNorth, lsThin);
  16.   sheet.WriteBorderColor(cell, cbNorth, scBlue);
  17.   }
  18.   book.WriteToFile('test.xlsx', true);
  19.   book.Free;
  20. end.

Code: Pascal  [Select][+][-]
  1. SelectedCell := ARS.ActiveWorksheet.WriteText(5, 2, 'abc');
  2. SelectedCell ^.Borders[[cbNorth, cbSouth]] := CellBorderStyle(lsThick, scGreen);
When you access the cells directly must know the related actions: The cell record does not store the cell format directly but only the index to a format list managed by the workbook. At first you must extract the currently used format of the cell because the format may control also other properties, not jut the cell border. In this format record you must set the Border property to identify at which side of the cell a border will be shown, and you must set the BorderStyle for this border. Finally you must write the modified format record back to the workbook's format list (workbook.AddFormat(...)). All this happens automatically when you call the corresponding worksheet method.
Code: Pascal  [Select][+][-]
  1. program Project1;
  2.  
  3. uses
  4.   fpSpreadsheet, fpsTypes, fpsUtils, xlsxOOXML;
  5. var
  6.   book: TsWorkbook;
  7.   sheet: TsWorksheet;
  8.   cell: PCell;
  9.   fmt: TsCellFormat;
  10. begin
  11.   book := TsWorkbook.Create;
  12.   sheet := book.AddWorksheet('Test');
  13.  
  14.   // Find cell at the given row/col coordinates (or create a new one)
  15.   cell := sheet.GetCell(1, 1);
  16.   // Extract format record from the workbook's format list
  17.   fmt := book.GetCellFormat(cell^.FormatIndex);
  18.   // Set the flag to show the top border
  19.   fmt.Border := [cbNorth];
  20.   // Set the style to be used for the top border
  21.   fmt.BorderStyles[cbNorth] := CellBorderStyle(scBlue, lsThin);
  22.   // or combined: fmt.SetBorders([cbNorth], scBlue, lsThin);
  23.   // Write the format record back into the workbook's format list and
  24.   // store its index in the format record
  25.   cell^.FormatIndex := book.AddCellFormat(fmt);
  26.  
  27.   book.WriteToFile('test.xlsx', true);
  28.   book.Free;
  29. end.

Badger

  • Full Member
  • ***
  • Posts: 144
Re: Trouble with Cell borders
« Reply #2 on: April 05, 2023, 12:11:46 pm »
Thanks wp Solution 1 works great - there's a lot to be said for cut and paste rather than retyping what you thought you read!

While I've got your attention, when I save the spreadsheet then open it in Libre, the text etc is hard up against the cell boundary.  Is there any way to tell Libre to include spaces between the text and cell boundaries?
Badger
(A bad tempered, grumpy animal that sleeps most of the winter!)

If at first you don't succeed - you're running about average!

I'm using Windows 10 Lazarus v2.4.4  FPC 3.2.2   Win 32/64

wp

  • Hero Member
  • *****
  • Posts: 12013
Re: Trouble with Cell borders [SOLVED - Almost]
« Reply #3 on: April 05, 2023, 02:37:52 pm »
Column width calculation in Excel/LO Calc has always been a mystery...

You could simply increase the DefaultColWidth a bit, maybe to 30 mm (the result will not be exact, because Excel/Calc add some poorly defined margins, and it depends on the monitor (*) ):
Code: Pascal  [Select][+][-]
  1.   sheet.WriteDefaultColWidth(30, suMillimeters);

[EDIT]
And if you operate the monitor in a high-dpi mode you should adjust the ScreenPixelsPerInch (declared in fpsUtils).
« Last Edit: April 05, 2023, 02:39:56 pm by wp »

Badger

  • Full Member
  • ***
  • Posts: 144
Re: Trouble with Cell borders
« Reply #4 on: July 30, 2023, 10:07:07 am »
Quote
Column width calculation in Excel/LO Calc has always been a mystery..

Just to go back to this part of the topic.  I'm trying to emulate the LO Calc command Format>Cells>Borders>Padding.  Does FPSpreadsheet have a similar ability?
Badger
(A bad tempered, grumpy animal that sleeps most of the winter!)

If at first you don't succeed - you're running about average!

I'm using Windows 10 Lazarus v2.4.4  FPC 3.2.2   Win 32/64

wp

  • Hero Member
  • *****
  • Posts: 12013
Re: Trouble with Cell borders
« Reply #5 on: July 30, 2023, 10:47:28 am »
I'm trying to emulate the LO Calc command Format>Cells>Borders>Padding.  Does FPSpreadsheet have a similar ability?
No

Badger

  • Full Member
  • ***
  • Posts: 144
Re: Trouble with Cell borders [ Solved]
« Reply #6 on: July 30, 2023, 01:32:13 pm »
OK Thanks
Badger
(A bad tempered, grumpy animal that sleeps most of the winter!)

If at first you don't succeed - you're running about average!

I'm using Windows 10 Lazarus v2.4.4  FPC 3.2.2   Win 32/64

wp

  • Hero Member
  • *****
  • Posts: 12013
Re: Trouble with Cell borders
« Reply #7 on: July 31, 2023, 08:03:41 pm »
I'm trying to emulate the LO Calc command Format>Cells>Borders>Padding.  Does FPSpreadsheet have a similar ability?
No
Hmmm...

If you're after displaying worksheets in the TsWorksheetGrid, there is a global variable varCellPadding defined in the grids units which defines the minimum distance between cell border and text for all grid types.

 

TinyPortal © 2005-2018