Trouble with Cell borders [ Solved] lazarus / 49-FPSpreadsheet ---------------------------------------------------------------------- Badger · 2023-04-05 07:51 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 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  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 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  ) ---------------------------------------------------------------------- wp · 2023-04-05 08:31 Quote from: Badger on April 05, 2023, 09:51:20 amCode: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 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 amCode: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SelectedCell := ARS.ActiveWorksheet.WriteText(5, 2, 'abc');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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 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 · 2023-04-05 10:11 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 · 2023-04-05 12:37 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   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 · 2023-07-30 08:07 QuoteColumn 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 · 2023-07-30 08:47 Quote from: Badger on July 30, 2023, 10:07:07 amI'm trying to emulate the LO Calc command Format>Cells>Borders>Padding.  Does FPSpreadsheet have a similar ability? No ---------------------------------------------------------------------- Badger · 2023-07-30 11:32 OK Thanks ---------------------------------------------------------------------- wp · 2023-07-31 18:03 Quote from: wp on July 30, 2023, 10:47:28 amQuote from: Badger on July 30, 2023, 10:07:07 amI'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.