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

[dodgex]

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

[wp]

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][+][-]

1. program project1;
2. uses
3.   FPSpreadsheet, FPSTypes, xlsxOOXML;
4. var
5.   wb: TsWorkbook;
6.   wsh: TsWorksheet;
7.   r: Cardinal;
8. begin
9.   wb := TsWorkBook.Create;
10.   try
11.     wsh := wb.AddWorksheet('Page 1');
12.  
13.     // *** PREPARATION OF WORKSHEET ***
14.  
15.     // A1
16.     wsh.WriteNumber(0, 0, 1.234);
17.  
18.     // B1
19.     wsh.WriteNumber(0, 1, 1.48);
20.     wsh.WriteFontStyle(0, 1, [fssItalic]);
21.  
22.     // B2
23.     wsh.WriteNumber(1, 1, 2.124);
24.     wsh.WriteFontStyle(1, 1, [fssItalic]);
25.  
26.     // B3
27.     wsh.WriteNumber(2, 1, 3.8934);
28.     wsh.WriteFontStyle(2, 1, [fssItalic]);
29.  
30.     // B4
31.     wsh.WriteFormula(3, 1, '=Sum(B1:B3)+$A$1');
32.     wsh.WriteFontStyle(3, 1, [fssBold]);
33.     wsh.WriteBackgroundColor(3, 1, scYellow);
34.  
35.     // *** COPY COLUMN WITH FORMATS ***
36.  
37.     // Step 1: Insert column before col1 --> col1 is col2 now!
38.     wsh.InsertCol(1);
39.  
40.     // Step 2: Copy formats, cell-by-cell, from source to destination columns
41.     for r := wsh.GetFirstRowIndex to wsh.GetLastRowIndex do
42.       wsh.CopyFormat(wsh.FindCell(r, 2), r, 1);
43.  
44.     // Save to file for checking
45.     wb.WriteToFile('test.xlsx', true);
46.   finally
47.     wb.Free;
48.   end;
49.  
50. end.