Correction of conditional formatting formulas

Forum > FPSpreadsheet

(1/1)

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 [+][-]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";}};} ---Pranges:PsCellRange; currentFormat:TsConditionalFormat; currentFormat:=wbook.GetConditionalFormat(i);Pranges:=@currentFormat.CellRange;Pranges^:=targetRange;StoreCFIndexInCells(wsheet,i,targetRange); 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 [+][-]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 insdel_demo; uses fpspreadsheet, fpstypes, fpsutils, fpsconditionalformat, xlsxooxml;const TEST = 4; // or 1, 2, 3var workbook: TsWorkbook; worksheet: TsWorksheet; r: Cardinal; srcCell: PCell; fmt: TsCellFormat; fmtIdx: Integer;begin workbook := TsWorkbook.Create; try worksheet := workbook.AddWorksheet('Test'); // Add test data worksheet.WriteText(0, 0, 'x'); worksheet.WriteText(0, 1, 'y'); for r := 1 to 25 do worksheet.WriteNumber(r, 0, -10 + r - 1); srcCell := worksheet.WriteFormula(1, 1, 'A2*A2/10-5'); for r := 2 to 25 do worksheet.CopyFormula(srcCell, r, 1); // Write conditional formats InitFormatRecord(fmt); fmt.SetBackgroundColor(scRed); fmtIdx := workbook.AddCellFormat(fmt); worksheet.WriteConditionalCellFormat(Range(1, 1, 21, 1), cfcLessThan, 0, fmtIdx); fmt.SetBackgroundColor(scGreen); fmtIdx := workbook.AddCellFormat(fmt); worksheet.WriteConditionalCellFormat(Range(1, 1, 21, 1), cfcGreaterEqual, 0, fmtIdx); // Test cases. Set TEST to 1, 2, 3, or 4. case TEST of 1: worksheet.DeleteRow(0); 2: worksheet.DeleteRow(11); 3: worksheet.InsertRow(0); 4: begin worksheet.InsertRow(10); worksheet.WriteNumber(10, 0, 0.5); worksheet.CopyFormula(srcCell, 10, 1); end; end; // Write file workbook.WriteToFile('testdata.xlsx', true); finally workbook.Free; end;end.

Navigation

[0] Message Index