Forum > FPSpreadsheet

Trouble with Cell borders [ Solved]

(1/2) > >>

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  [+][-]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";}};} ---for x:=0 to 5 dobegin  SelectedCell:=ARS.ActiveWorksheet.WriteBorders(y,x,[cbNorth]);  ARS.ActiveWorksheet.WriteBorderLineStyle(SelectedCell,cbNorth, lsThin, scBlue);end 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  [+][-]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";}};} ---SelectedCell := ARS.ActiveWorksheet.WriteText(5, 2, 'abc');SelectedCell ^.Borders[[cbNorth, cbSouth]] := CellBorderStyle(lsThick, scGreen); It didn't like .borders - "no ident" and Got "Word", expected "TsLineStyle" after scGreen

Any suggestions (Helpful  :D)

wp:

--- Quote from: Badger on April 05, 2023, 09:51:20 am ---
--- 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";}};} ---  ARS.ActiveWorksheet.WriteBorderLineStyle(SelectedCell,cbNorth, lsThin, scBlue);
--- End quote ---
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  [+][-]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";}};} ---program Project1; uses  fpSpreadsheet, fpsTypes, xlsxOOXML;var  book: TsWorkbook;  sheet: TsWorksheet;  cell: PCell;begin  book := TsWorkbook.Create;  sheet := book.AddWorksheet('Test');  cell := sheet.WriteBorders(1, 1, [cbNorth]);  sheet.WriteBorderStyle(cell, cbNorth, lsThin, scBlue);  { alternatively:  sheet.WriteBorderLineStyle(cell, cbNorth, lsThin);  sheet.WriteBorderColor(cell, cbNorth, scBlue);  }  book.WriteToFile('test.xlsx', true);  book.Free;end.

--- Quote from: Badger on April 05, 2023, 09:51:20 am ---
--- 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";}};} ---SelectedCell := ARS.ActiveWorksheet.WriteText(5, 2, 'abc');SelectedCell ^.Borders[[cbNorth, cbSouth]] := CellBorderStyle(lsThick, scGreen);
--- End quote ---
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  [+][-]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";}};} ---program Project1; uses  fpSpreadsheet, fpsTypes, fpsUtils, xlsxOOXML;var  book: TsWorkbook;  sheet: TsWorksheet;  cell: PCell;  fmt: TsCellFormat;begin  book := TsWorkbook.Create;  sheet := book.AddWorksheet('Test');   // Find cell at the given row/col coordinates (or create a new one)  cell := sheet.GetCell(1, 1);  // Extract format record from the workbook's format list  fmt := book.GetCellFormat(cell^.FormatIndex);  // Set the flag to show the top border  fmt.Border := [cbNorth];  // Set the style to be used for the top border  fmt.BorderStyles[cbNorth] := CellBorderStyle(scBlue, lsThin);  // or combined: fmt.SetBorders([cbNorth], scBlue, lsThin);  // Write the format record back into the workbook's format list and  // store its index in the format record  cell^.FormatIndex := book.AddCellFormat(fmt);   book.WriteToFile('test.xlsx', true);  book.Free;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  [+][-]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";}};} ---  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:

--- Quote ---Column width calculation in Excel/LO Calc has always been a mystery..
--- End quote ---

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?

Navigation

[0] Message Index

[#] Next page

Go to full version