Add the function of generating conditional format.
lazarus / 49-FPSpreadsheet
----------------------------------------------------------------------
abcthing · 2020-06-11 02:17
Add the function of generating conditional format.
Demo：
Code: Pascal [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.Button1Click(Sender: TObject); var fDifferentialFormat:
TsDifferentialFormat; fConditionalFormat: TsCellConditionalFormat;
begin with fDifferentialFormat do begin
bgColor:='FF92D050'; end; sWorksheetGrid1.Workbook.AddDifferential
Format(fDifferentialFormat); with fConditionalFormat do begin
sqref := 'A2'; cfRuleType:='expression'; dxfId:=0;
priority:=1; formula:='$A1=1'; end; sWorksheetGrid1.Work
sheet.AddCellConditionalFormat(fConditionalFormat);
sWorksheetGrid1.SaveToSpreadsheetFile('test.xlsx'); 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);
----------------------------------------------------------------------
wp · 2020-06-11 09:58
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.
----------------------------------------------------------------------
abcthing · 2020-06-11 12:59
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.
----------------------------------------------------------------------
abcthing · 2020-06-12 15:17
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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i public
boolean evaluate(final SheetCell cell) { final boolean applies
= this.evaluateHelper(cell); if (applies) {
cell.applyConditionalStyle(this._style); } return
applies; } private boolean evaluateHelper(final SheetCell
cell) { switch (this._type) { case ABOVE_AVERAGE: {
return this.evaluateAverage(cell); }
case BEGINS_WITH: { return
this.evaluateFormulaCondition(cell); } case
CELL_VALUE: { return
this.evaluateValueCondition(cell); } case
COLOR_SCALE: { return this.evaluateCF(cell,
CFType.ColorScale); } case CONTAINS_BLANKS: {
return this.evaluateFormulaCondition(cell); }
case CONTAINS_ERRORS: { return
this.evaluateFormulaCondition(cell); } case
CONTAINS_TEXT: { return
this.evaluateFormulaCondition(cell); } case
DATA_BAR: { return false; } case
DUPLICATE_VALUES: { return
this.evaluateDuplicateCondition(cell); } case
ENDS_WITH: { return
this.evaluateFormulaCondition(cell); } case
EXPRESSION: { return
this.evaluateFormulaCondition(cell); } case
ICON_SET: { return this.evaluateCF(cell,
CFType.IconSet); } case NOT_CONTAINS_BLANKS: {
return this.evaluateFormulaCondition(cell); }
case NOT_CONTAINS_ERRORS: { return
this.evaluateFormulaCondition(cell); } case
NOT_CONTAINS_TEXT: { return
this.evaluateFormulaCondition(cell); } case
TIME_PERIOD: { return
this.evaluateFormulaCondition(cell); } case
TOP_10: { return this.evaluateTop10Condition(cell);
} case UNIQUE_VALUES: { return
false; } default: { return
false; } } }
----------------------------------------------------------------------
wp · 2020-06-25 17:27
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
----------------------------------------------------------------------
abcthing · 2020-07-13 07:22
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 'FIND($B$3,$G9,1)<
>1' in xlsx.
That's exactly what I need.
----------------------------------------------------------------------
abcthing · 2020-07-14 07:39
For my own solution：
1)Type TsCFCondition in fpsconditionalformat unit add:
cfcexpression
2)Method WriteConditionalFormatCellRule in xlsxooxml unit add:
cfcExpression:
begin
formula1Str :=
'' +
VarToStr(ARule.Operand1) +
'';
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 · 2020-07-15 17:07
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.
----------------------------------------------------------------------
abcthing · 2020-07-16 03:00
I have downloaded and run the new version.
Thank you very much.