Trouble with Cell borders [ Solved]

[Badger]

 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  )

[wp]

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]

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?

[wp]

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

[Badger]

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?

[wp]

I'm trying to emulate the LO Calc command Format>Cells>Borders>Padding.  Does FPSpreadsheet have a similar ability?

No

[Badger]

OK Thanks

[wp]

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.