Lazarus

Programming => Packages and Libraries => FPSpreadsheet => Topic started by: abcthing on June 11, 2020, 04:17:57 am

Title: Add the function of generating conditional format.
Post by: abcthing 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;
10.    sWorksheetGrid1.Workbook.AddDifferentialFormat(fDifferentialFormat);
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;
20.     sWorksheetGrid1.Worksheet.AddCellConditionalFormat(fConditionalFormat);
21.  
22.     sWorksheetGrid1.SaveToSpreadsheetFile('test.xlsx');  
23. end;

Add to fpsTypes.pas：
  TsCellConditionalFormat
  PsCellConditionalFormat
  TsDifferentialFormat
  PsDifferentialFormat

Add to fpsclasses.pas：
  TsConditionalFormatList
  TsDifferentialFormatList

Add to fpspreadsheet:
  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; 

Add to xlsxooxml.pas：
  TsSpreadOOXMLWriter:
    procedure WriteConditionalFormats(AStream: TStream; AWorksheet: TsBasicWorksheet); 
    procedure WriteDifferentialFormattingList(AStream: TStream);

Title: Re: Add the function of generating conditional format.
Post by: wp 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.

Title: Re: Add the function of generating conditional format.
Post by: abcthing 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.

Title: Re: Add the function of generating conditional format.
Post by: abcthing 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.     }

   

Title: Re: Add the function of generating conditional format.
Post by: wp 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

Title: Re: Add the function of generating conditional format.
Post by: abcthing 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.

Title: Re: Add the function of generating conditional format.
Post by: abcthing 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);       

Title: Re: Add the function of generating conditional format.
Post by: wp 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.

Title: Re: Add the function of generating conditional format.
Post by: abcthing on July 16, 2020, 05:00:07 am

I have downloaded and run the new version.
Thank you very much.