Correction of conditional formatting formulas

[suspended]

Hello. I am writing a program that inserts strings into a template xlsx. The template uses conditional formatting with references to strings that shift when my strings are added. For example, the conditional formatting formula in the template is: I5=$I$18-$J$18. The range of this conditional formatting in the template is: $I$5:$I$6. When this condition is met, cells I5 and I6 are colored.
Next, I add the lines 6,7,8...N (between 5 and 18) and wish the conditional formatting to extend to them. I found a workaround to change the range

Code: Pascal  [Select][+][-]

1. Pranges:PsCellRange;
2. currentFormat:TsConditionalFormat;
3.  
4. currentFormat:=wbook.GetConditionalFormat(i);
5. Pranges:=@currentFormat.CellRange;
6. Pranges^:=targetRange;
7. StoreCFIndexInCells(wsheet,i,targetRange);
8.  

StoreCFIndexInCells is a copy of your non-public method from fpspreadsheet_cf.inc

However, the text of the conditional formatting formula remains the same and the condition does not work. I would like to get I5=$I$M-$J$M, where M is the new index.

Are there any methods by which I could implement offsets of references to offset cells?

[wp]

I think inserting/deleting of rows or columns should be transparent, and you should not be forced to write any code to adjust the conditional format ranges.

Therefore I committed a new version of fpspreadsheet to svn (https://sourceforge.net/p/lazarus-ccr/svn/HEAD/tree/components/fpspreadsheet/) in which the DeleteRow/DeleteCol/InsertRow/InsertCol methods of TsWorksheet adjust the conditional format ranges in the same way as they do with comments, hyperlinks and formulas.

The following project demonstrates this. Of course, you need the new version for it to work properly.

Code: Pascal  [Select][+][-]

1. program insdel_demo;
2.  
3. uses
4.   fpspreadsheet, fpstypes, fpsutils, fpsconditionalformat, xlsxooxml;
5. const
6.   TEST = 4;  // or 1, 2, 3
7. var
8.   workbook: TsWorkbook;
9.   worksheet: TsWorksheet;
10.   r: Cardinal;
11.   srcCell: PCell;
12.   fmt: TsCellFormat;
13.   fmtIdx: Integer;
14. begin
15.   workbook := TsWorkbook.Create;
16.   try
17.     worksheet := workbook.AddWorksheet('Test');
18.  
19.     // Add test data
20.     worksheet.WriteText(0, 0, 'x');
21.     worksheet.WriteText(0, 1, 'y');
22.     for r := 1 to 25 do
23.       worksheet.WriteNumber(r, 0, -10 + r - 1);
24.     srcCell := worksheet.WriteFormula(1, 1, 'A2*A2/10-5');
25.     for r := 2 to 25 do
26.       worksheet.CopyFormula(srcCell, r, 1);
27.  
28.     // Write conditional formats
29.     InitFormatRecord(fmt);
30.     fmt.SetBackgroundColor(scRed);
31.     fmtIdx := workbook.AddCellFormat(fmt);
32.     worksheet.WriteConditionalCellFormat(Range(1, 1, 21, 1), cfcLessThan, 0, fmtIdx);
33.     fmt.SetBackgroundColor(scGreen);
34.     fmtIdx := workbook.AddCellFormat(fmt);
35.     worksheet.WriteConditionalCellFormat(Range(1, 1, 21, 1), cfcGreaterEqual, 0, fmtIdx);
36.  
37.     // Test cases. Set TEST to 1, 2, 3, or 4.
38.     case TEST of
39.       1: worksheet.DeleteRow(0);
40.       2: worksheet.DeleteRow(11);
41.       3: worksheet.InsertRow(0);
42.       4: begin
43.            worksheet.InsertRow(10);
44.            worksheet.WriteNumber(10, 0, 0.5);
45.            worksheet.CopyFormula(srcCell, 10, 1);
46.          end;
47.     end;
48.  
49.     // Write file
50.     workbook.WriteToFile('testdata.xlsx', true);
51.   finally
52.     workbook.Free;
53.   end;
54. end.