### Bookstore

 Computer Math and Games in Pascal (preview) Lazarus Handbook

### Author Topic: Add the function of generating conditional format.  (Read 1311 times)

#### abcthing

• New Member
• Posts: 19
##### Add the function of generating conditional format.
« on: June 11, 2020, 04:17:57 am »
Add the function of generating conditional format.

Demo：
Code: Pascal  [Select][+][-]
1. procedure TForm1.Button1Click(Sender: TObject);
2. var
3.    fDifferentialFormat: TsDifferentialFormat;
4.    fConditionalFormat: TsCellConditionalFormat;
5. begin
6.    with fDifferentialFormat do
7.    begin
8.       bgColor:='FF92D050';
9.    end;
11.
12.     with fConditionalFormat do
13.     begin
14.        sqref := 'A2';
15.        cfRuleType:='expression';
16.        dxfId:=0;
17.        priority:=1;
18.        formula:='\$A1=1';
19.     end;
21.
23. end;

TsCellConditionalFormat
PsCellConditionalFormat
TsDifferentialFormat
PsDifferentialFormat

TsConditionalFormatList
TsDifferentialFormatList

TsWorksheet:
FConditionalFormats: TsConditionalFormatList;
function AddCellConditionalFormat(const AValue: TsCellConditionalFormat): Integer;
function GetCellConditionalFormat(AIndex: Integer): TsCellConditionalFormat;
function GetNumCellConditionalFormats: Integer;

TsWorkbook：
FDifferentialFormatList: TsDifferentialFormatList;
function AddDifferentialFormat(const AValue: TsDifferentialFormat): Integer;
function GetDifferentialFormat(AIndex: Integer): TsDifferentialFormat;
function GetNumDifferentialFormats: Integer;

procedure WriteConditionalFormats(AStream: TStream; AWorksheet: TsBasicWorksheet);
procedure WriteDifferentialFormattingList(AStream: TStream);

#### wp

• Hero Member
• Posts: 8415
##### Re: Add the function of generating conditional format.
« Reply #1 on: June 11, 2020, 11:58:14 am »
Thank you for your addition. I'll put it on my to-do list.

There is a lot to be added, though. Your code only writes to xlsx, it does not read. And it does not support the other formats which probably has these features as well, ods and xls.

Since ods is not covered completely so far, I am not sure whether the TsCellConditionalFormat has the correct layout. The format should be defined such that at least both xlsx and ods can be handled as well (xls and Excel xml will probably be similar to Excel xlsx).

I also am not sure whether it is required to have separate lists for the normal, conditional and differential formats. Maybe the TsCellFormat can be extended such that it can handle the new cases as well. Having a common root to all these format variants probably could facilitate clipboard handling.

The next user-request probably will be to have these features available in the visual components as well; therefore, the TsWorksheetgrid must evaluate the formulas and format the cells accordingly.

And a final question: Could you point me to what "differential format" is? I've never used this feature.
« Last Edit: June 11, 2020, 12:02:37 pm by wp »
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

#### abcthing

• New Member
• Posts: 19
##### Re: Add the function of generating conditional format.
« Reply #2 on: June 11, 2020, 02:59:07 pm »
You are right, these problems all exist, the "differential format" is not accurate. This code only does my job.
I need the function of conditional format , thank you for adding this function to  your to-do list.
« Last Edit: June 11, 2020, 03:11:39 pm by abcthing »

#### abcthing

• New Member
• Posts: 19
##### Re: Add the function of generating conditional format.
« Reply #3 on: June 12, 2020, 05:17:30 pm »
It is a complex project to fully realize the reading, writing and displaying of conditional formats.
See the following for other program written in Java:

Code: Pascal  [Select][+][-]
1.    public boolean evaluate(final SheetCell cell) {
2.         final boolean applies = this.evaluateHelper(cell);
3.         if (applies) {
4.             cell.applyConditionalStyle(this._style);
5.         }
6.         return applies;
7.     }
8.
9.     private boolean evaluateHelper(final SheetCell cell) {
10.         switch (this._type) {
11.             case ABOVE_AVERAGE: {
12.                 return this.evaluateAverage(cell);
13.             }
14.             case BEGINS_WITH: {
15.                 return this.evaluateFormulaCondition(cell);
16.             }
17.             case CELL_VALUE: {
18.                 return this.evaluateValueCondition(cell);
19.             }
20.             case COLOR_SCALE: {
21.                 return this.evaluateCF(cell, CFType.ColorScale);
22.             }
23.             case CONTAINS_BLANKS: {
24.                 return this.evaluateFormulaCondition(cell);
25.             }
26.             case CONTAINS_ERRORS: {
27.                 return this.evaluateFormulaCondition(cell);
28.             }
29.             case CONTAINS_TEXT: {
30.                 return this.evaluateFormulaCondition(cell);
31.             }
32.             case DATA_BAR: {
33.                 return false;
34.             }
35.             case DUPLICATE_VALUES: {
36.                 return this.evaluateDuplicateCondition(cell);
37.             }
38.             case ENDS_WITH: {
39.                 return this.evaluateFormulaCondition(cell);
40.             }
41.             case EXPRESSION: {
42.                 return this.evaluateFormulaCondition(cell);
43.             }
44.             case ICON_SET: {
45.                 return this.evaluateCF(cell, CFType.IconSet);
46.             }
47.             case NOT_CONTAINS_BLANKS: {
48.                 return this.evaluateFormulaCondition(cell);
49.             }
50.             case NOT_CONTAINS_ERRORS: {
51.                 return this.evaluateFormulaCondition(cell);
52.             }
53.             case NOT_CONTAINS_TEXT: {
54.                 return this.evaluateFormulaCondition(cell);
55.             }
56.             case TIME_PERIOD: {
57.                 return this.evaluateFormulaCondition(cell);
58.             }
59.             case TOP_10: {
60.                 return this.evaluateTop10Condition(cell);
61.             }
62.             case UNIQUE_VALUES: {
63.                 return false;
64.             }
65.             default: {
66.                 return false;
67.             }
68.         }
69.     }

#### wp

• Hero Member
• Posts: 8415
##### Re: Add the function of generating conditional format.
« Reply #4 on: June 25, 2020, 07:27:31 pm »
I just committed the initial version with conditional formatting. But note that it is far from being complete - missing more advanced conditions, no reading support, writing only for xlsx. I hope that the concept is general enough that it can be extended to write ODS at least

There is a demo in folder (fpspreadsheet)/examples/other/conditional_formatting
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

#### abcthing

• New Member
• Posts: 19
##### Re: Add the function of generating conditional format.
« Reply #5 on: July 13, 2020, 09:22:26 am »
It's very nice of you implement many conditional formats.
But the conditional format of expression is missing.
It is displayed as "Use formula to determine which cells to format" in Excel.
It is  displayed as '<conditionalFormatting sqref="E9"><cfRule type="expression" dxfId="2" priority="3"><formula>FIND(\$B\$3,\$G9,1)&lt;&gt;1</formula></cfRule></conditionalFormatting>' in xlsx.
That's exactly what I need.
« Last Edit: July 13, 2020, 09:39:41 am by abcthing »

#### abcthing

• New Member
• Posts: 19
##### Re: Add the function of generating conditional format.
« Reply #6 on: July 14, 2020, 09:39:10 am »
For my own solution：
1)Type TsCFCondition in fpsconditionalformat unit add:
cfcexpression
2)Method WriteConditionalFormatCellRule in xlsxooxml unit add:
cfcExpression:
begin
formula1Str :=
'<formula>' +
VarToStr(ARule.Operand1) +
'</formula>';
param1Str := '';
end
3)Usage method:
formula:= 'ISNUMBER(FIND(\$A1,"|AAA|BBB|CCC|",1))';
sWorksheetGrid1.Worksheet.WriteConditionalCellFormat(Range(row, 4, row, 4), cfcExpression, formula, fmtIdx);

#### wp

• Hero Member
• Posts: 8415
##### Re: Add the function of generating conditional format.
« Reply #7 on: July 15, 2020, 07:07:42 pm »
Extended the xlsx, ods and Excel-xml writers for "expression" syntax. The formula can be entered with or without leading '='. The syntax of the formulas is not checked; there are exception handlers for ods and Excel-xml which need a syntax conversion from Excel-A1 to ODS and Excel-R1C1, respectively, during which invalid expressions may crash the program; the opening office applications will ignore faulty expressions, too, as I could see.
Mainly Lazarus trunk / fpc 3.2.0 / all 32-bit on Win-10, but many more...

#### abcthing

• New Member
• Posts: 19
##### Re: Add the function of generating conditional format.
« Reply #8 on: July 16, 2020, 05:00:07 am »
I have downloaded and run the new version.
Thank you very much.