How to insert a column in the middle of a formatted Excel and keep the
style
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
dodgex · 2026-01-08 11:48

How to insert a column in the middle of a formatted Excel and keep the
style??

----------------------------------------------------------------------
wp · 2026-01-08 16:08

To insert a column call Worksheet.InsertCol(ColIndex) where ColIndex
is the index of the column BEFORE which the new column will be
inserted. This inserts a column with blank cells. In order to copy the
format of the cells in the original ColIndex you must iterate over the
cells and call Worksheet.CopyFormat. See highlighted lines in the
attached tested project:

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  wb:
TsWorkbook;  wsh: TsWorksheet;  r: Cardinal;begin  wb :=
TsWorkBook.Create;  try    wsh := wb.AddWorksheet('Page 1');     //
*** PREPARATION OF WORKSHEET ***     // A1    wsh.WriteNumber(0, 0,
1.234);     // B1    wsh.WriteNumber(0, 1, 1.48);   
wsh.WriteFontStyle(0, 1, [fssItalic]);     // B2    wsh.WriteNumber(1,
1, 2.124);    wsh.WriteFontStyle(1, 1, [fssItalic]);     // B3   
wsh.WriteNumber(2, 1, 3.8934);    wsh.WriteFontStyle(2, 1,
[fssItalic]);     // B4    wsh.WriteFormula(3, 1,
'=Sum(B1:B3)+$A$1');    wsh.WriteFontStyle(3, 1, [fssBold]);   
wsh.WriteBackgroundColor(3, 1, scYellow);     // *** COPY COLUMN WITH
FORMATS ***     // Step 1: Insert column before col1 --> col1 is col2
now!    wsh.InsertCol(1);     // Step 2: Copy formats, cell-by-cell,
from source to destination columns    for r := wsh.GetFirstRowIndex to
wsh.GetLastRowIndex do      wsh.CopyFormat(wsh.FindCell(r, 2), r,
1);     // Save to file for checking    wb.WriteToFile('test.xlsx',
true);  finally    wb.Free;  end; end.