by
ru
en
by

Avemey

logo
FilesMainLinksPhotosAnime

Code examples for ZEXMLSS

ZEXMLSS 0.0.8 tested with:
  • Lazarus 1.2.6 (FPC 2.6 + Debian 8 / Windows)
  • Delphi 7, XE, XE2
  • C++Builder XE
Code examples:

Examples of using conditional formatting in ZEXMLSS (only for ODS!):

Main: add area for applying conditional formatting, applying styles in conditional formatting, using conditions


Conditional formatting for ODS in libreOffice Calc
Conditional formatting for ODS in libreOffice Calc.

Code example for Lazarus/Delphi Code example for C++Builder
//uses zexmlss, zeodfs, zexmlssutils;
procedure TForm1.btnCreateClick(Sender: TObject);
var
  _path: string;
  ze: TZEXMLSS;
  _fname: string;
  _sheet: TZSheet;
  _cfStyle: TZConditionalStyle;
  _cfItem: TZConditionalStyleItem;
  i, j: integer;

begin
  ze := nil;
  _path := ExtractFilePath(Paramstr(0));
  Randomize();

  try
    ze := TZEXMLSS.Create(nil);

    //Prepare styles
    ze.Styles.Count := 3;
    //Using red text on silver background with central alignment for negative numbers
    ze.Styles[0].Font.Color := clRed;
    ze.Styles[0].BGColor := clSilver;
    ze.Styles[0].Alignment.Horizontal := ZHCenter;
    //For numbers from 10 to 30: lime color bold and italic text with 
    //left alignment on black background
    ze.Styles[1].Font.Color := clLime;
    ze.Styles[1].Font.Style := [fsBold, fsItalic];
    ze.Styles[1].BGColor := clBlack;
    ze.Styles[1].Alignment.Horizontal := ZHLeft;
    //For numbers > 40: yellow text with right alignment on purple backround
    ze.Styles[2].Font.Color := clYellow;
    ze.Styles[2].BGColor := clPurple;
    ze.Styles[2].Alignment.Horizontal := ZHRight;

    ze.Sheets.Count := 1;
    _sheet := ze.Sheets[0];
    _sheet.RowCount := 25;
    _sheet.ColCount := 25;

    for i := 2 to 20 do
    for j := 2 to 20 do
    begin
      _sheet[i, j].Data := IntToStr(Random(100) - 50);
      _sheet[i, j].CellType := ZENumber;
    end;

    //Add conditional formatting:
    //Area for using conditional formatting:
    // begins with cell [5, 5] width = 15, height = 15
    _cfStyle := _sheet.ConditionalFormatting.Add(5, 5, 15, 15);

    //1-th condition: apply 0 style for cells values < 0
    _cfItem := _cfStyle.Add();
    //    Apply 0 style
    _cfItem.ApplyStyleID := 0;
    //    Use condition: operator
    _cfItem.Condition := ZCFCellContentOperator;
    //    Operator = "<"
    _cfItem.ConditionOperator := ZCFOpLT;
    //    Value = 0
    _cfItem.Value1 := '0';

    //2-nd condition: apply 1-th style for cells values between 10 and 30
    _cfItem := _cfStyle.Add();
    _cfItem.ApplyStyleID := 1;
    //    Use condition: cell value between 10 and 30
    _cfItem.Condition := ZCFCellContentIsBetween;
    //    value1 = 10, value2 = 30
    _cfItem.Value1 := '10';
    _cfItem.Value2 := '30';

    //3 condition: apply 2-nd style for cells values  > 40
    _cfItem := _cfStyle.Add();
    _cfItem.ApplyStyleID := 2;
    _cfItem.Condition := ZCFCellContentOperator;
    //    Operatr = ">"
    _cfItem.ConditionOperator := ZCFOpGT;
    _cfItem.Value1 := '40';

    _fname := _path + 'tst_fcf_main.ods';
    DeleteFile(_fname);
    SaveXmlssToODFS(ze, _fname);
  finally
    if (Assigned(ze)) then
      FreeAndNil(ze);
  end;
end;   

/*
#include <zexmlss.hpp>
#include <zeodfs.hpp>
*/
void __fastcall TForm1::btnCreateClick(TObject *Sender)
{
	Randomize();
	String _path = ExtractFilePath(ParamStr(0));
	TZEXMLSS *ze = new TZEXMLSS(NULL);
	try
	{
		//Prepare styles
		ze->Styles->Count = 3;
		//Using red text on silver background with central alignment for negative numbers
		ze->Styles->Items[0]->Font->Color = clRed;
		ze->Styles->Items[0]->BGColor = clSilver;
		ze->Styles->Items[0]->Alignment->Horizontal = ZHCenter;
		//For numbers from 10 to 30: lime color bold and italic text with 
    		//left alignment on black background
		ze->Styles->Items[1]->Font->Color = clLime;
		ze->Styles->Items[1]->Font->Style = TFontStyles() << fsBold << fsItalic;
		ze->Styles->Items[1]->BGColor = clBlack;
		ze->Styles->Items[1]->Alignment->Horizontal = ZHLeft;
		//For numbers > 40: yellow text with right alignment on purple backround
		ze->Styles->Items[2]->Font->Color = clYellow;
		ze->Styles->Items[2]->BGColor = clPurple;
		ze->Styles->Items[2]->Alignment->Horizontal = ZHRight;

		ze->Sheets->Count = 1;
		TZSheet *_sheet = ze->Sheets->Sheet[0];
		_sheet->RowCount = 25;
		_sheet->ColCount = 25;

		int i, j;
		for (i = 2; i <= 20; i++)
		for (j = 2; j <= 20; j++)
		{
		  _sheet->Cell[i][j]->Data = IntToStr(Random(100) - 50);
		  _sheet->Cell[i][j]->CellType = ZENumber;
		}

		//Add conditional formatting:
    		//Area for using conditional formatting:
    		// begins with cell [5, 5] width = 15, height = 15
		TZConditionalStyle *_cfStyle = _sheet->ConditionalFormatting->Add(5, 5, 15, 15);

		//1-th condition: apply 0 style for cells values < 0
		TZConditionalStyleItem *_cfItem = _cfStyle->Add();
		//    Apply 0 style
		_cfItem->ApplyStyleID = 0;
		//    Use condition: operator
		_cfItem->Condition = ZCFCellContentOperator;
		//    Operator = "<"
		_cfItem->ConditionOperator = ZCFOpLT;
		//    Value = 0
		_cfItem->Value1 = "0";

		//2-nd condition: apply 1-th style for cells values between 10 and 30
		_cfItem = _cfStyle->Add();
		_cfItem->ApplyStyleID = 1;
		//    Use condition: cell value between 10 and 30
		_cfItem->Condition = ZCFCellContentIsBetween;
		//    value1 = 10, value2 = 30
		_cfItem->Value1 = "10";
		_cfItem->Value2 = "30";

		//3 condition: apply 2-nd style for cells values > 40
		_cfItem = _cfStyle->Add();
		_cfItem->ApplyStyleID = 2;
		_cfItem->Condition = ZCFCellContentOperator;
		 //    Operatr = ">"
		_cfItem->ConditionOperator = ZCFOpGT;
		_cfItem->Value1 = "40";

		String _fname = _path + "tst_fcf_main.ods";
		DeleteFile(_fname);
		SaveXmlssToODFS(ze, _fname);
	}
	__finally
	{
		delete ze;
	}
}

Conditional formatting for text

Using conditions: ZCFContainsText (Contains text), ZCFNotContainsText (Not contains text), ZCFBeginsWithText (Begins with text), ZCFEndsWithText (Ends with text).

Conditional formatting for text
Conditional formatting for text

Code example for Lazarus/Delphi Code example for C++Builder
//uses zexmlss, zeodfs, zexmlssutils;
procedure TForm1.btnCreateClick(Sender: TObject);
const
  const_arr_size = 10;

  const_text_array: array [0..const_arr_size - 1] of string =
                    (
                    'text begin',
                    'text some',
                    'some text some',
                    'some TEXT some',
                    'sdstixtjk',
                    'bla-bla-bla',
                    'end text',
                    'end TeXt',
                    'end tixt',
                    'text'
                    );

var
  _path: string;
  ze: TZEXMLSS;
  _fname: string;
  _sheet: TZSheet;
  _cfStyle: TZConditionalStyle;
  _cfItem: TZConditionalStyleItem;
  i, j: integer;

begin
  ze := nil;
  _path := ExtractFilePath(Paramstr(0)) ;

  try
    ze := TZEXMLSS.Create(nil);

    //Prepare styles
    ze.Styles.Count := 2;

    ze.Styles[0].Font.Color := clYellow;
    ze.Styles[0].BGColor := clBlack;
    ze.Styles[0].Alignment.Horizontal := ZHCenter;

    ze.Styles[1].Alignment.Horizontal := ZHCenter;
    ze.Styles[1].Font.Style := [fsBold];

    ze.Sheets.Count := 1;
    _sheet := ze.Sheets[0];
    _sheet.RowCount := 25;
    _sheet.ColCount := 25;

    for i := 1 to 4 do
    begin
      _sheet.Columns[i].WidthMM := 40;
      _sheet[i, 3].CellStyle := 1;
    end;

    _sheet[1, 3].Data := 'Contain text';
    _sheet[2, 3].Data := 'Not Contains text';
    _sheet[3, 3].Data := 'Begins With Text';
    _sheet[4, 3].Data := 'Ends With Text';

    for i := 1 to 4 do
      for j := 0 to const_arr_size - 1 do
        _sheet[i, j + 4].Data := const_text_array[j];

    //Add conditional formatting:

    //Area for using conditional formatting:
    // begins with cell [1, 4] width = 1, height = 10
    _cfStyle := _sheet.ConditionalFormatting.Add(1, 4, 1, 10);

    //using 0-th style for cells contains text
    _cfItem := _cfStyle.Add();
    _cfItem.ApplyStyleID := 0;
    _cfItem.Condition := ZCFContainsText;
    _cfItem.Value1 := 'text';

    //Add conditional formatting: cell not conatains text
    _cfStyle := _sheet.ConditionalFormatting.Add(2, 4, 1, 10);
    _cfItem := _cfStyle.Add();
    _cfItem.ApplyStyleID := 0;
    _cfItem.Condition := ZCFNotContainsText;
    _cfItem.Value1 := 'text';

    //Add conditional formatting: cell begins with text
    _cfStyle := _sheet.ConditionalFormatting.Add(3, 4, 1, 10);
    _cfItem := _cfStyle.Add();
    _cfItem.ApplyStyleID := 0;
    _cfItem.Condition := ZCFBeginsWithText;
    _cfItem.Value1 := 'text';

    //Add conditional formatting: cell ends with text
    _cfStyle := _sheet.ConditionalFormatting.Add(4, 4, 1, 10);
    _cfItem := _cfStyle.Add();
    _cfItem.ApplyStyleID := 0;
    _cfItem.Condition := ZCFEndsWithText;
    _cfItem.Value1 := 'text';

    _fname := _path + 'tst_fcf_text.ods';
    DeleteFile(_fname);
    SaveXmlssToODFS(ze, _fname);
  finally
    if (Assigned(ze)) then
      FreeAndNil(ze);
  end;
end;   

/*
#include <zexmlss.hpp>
#include <zeodfs.hpp>
*/

void __fastcall TForm1::btnCreateClick(TObject *Sender)
{
	const int const_arr_size = 10;
	const String const_text_array[const_arr_size] = {
					"text begin",
					"text some",
					"some text some",
					"some TEXT some",
					"sdstixtjk",
					"bla-bla-bla",
					"end text",
					"end TeXt",
					"end tixt",
					"text"
					};
	String _path = ExtractFilePath(ParamStr(0));
	TZEXMLSS *ze = new TZEXMLSS(NULL);
	try
	{
		//Prepare styles
		ze->Styles->Count = 2;

		ze->Styles->Items[0]->Font->Color = clYellow;
		ze->Styles->Items[0]->BGColor = clBlack;
		ze->Styles->Items[0]->Alignment->Horizontal = ZHCenter;

		ze->Styles->Items[1]->Alignment->Horizontal = ZHCenter;
		ze->Styles->Items[1]->Font->Style = TFontStyles() << fsBold;

		ze->Sheets->Count = 1;
		TZSheet *_sheet = ze->Sheets->Sheet[0];
		_sheet->RowCount = 25;
		_sheet->ColCount = 25;

		int i;
		for (i = 1; i < 5; i++)
		{
			_sheet->Columns[i]->WidthMM = 40;
			_sheet->Cell[i][3]->CellStyle = 1;
		}

		_sheet->Cell[1][3]->Data = "Contain text";
		_sheet->Cell[2][3]->Data = "Not Contains text";
		_sheet->Cell[3][3]->Data = "Begins With Text";
		_sheet->Cell[4][3]->Data = "Ends With Text";

		int j;
		for (i = 1; i < 5; i++)
		for (j = 0; j < const_arr_size; j++)
		{
			_sheet->Cell[i][j + 4]->Data = const_text_array[j];
		}

		//Add conditional formatting:

    		//Area for using conditional formatting:
    		// begins with cell [1, 4] width = 1, height = 10
		TZConditionalStyle *_cfStyle = _sheet->ConditionalFormatting->Add(1, 4, 1, 10);

		//using 0-th style for cells contains text
		TZConditionalStyleItem *_cfItem = _cfStyle->Add();
		_cfItem->ApplyStyleID = 0;
		_cfItem->Condition = ZCFContainsText;
		_cfItem->Value1 = "text";

		//Add conditional formatting: cell not conatains text
		_cfStyle = _sheet->ConditionalFormatting->Add(2, 4, 1, 10);
		_cfItem = _cfStyle->Add();
		_cfItem->ApplyStyleID = 0;
		_cfItem->Condition = ZCFNotContainsText;
		_cfItem->Value1 = "text";

		//Add conditional formatting: cell begins with text
		_cfStyle = _sheet->ConditionalFormatting->Add(3, 4, 1, 10);
		_cfItem = _cfStyle->Add();
		_cfItem->ApplyStyleID = 0;
		_cfItem->Condition = ZCFBeginsWithText;
		_cfItem->Value1 = "text";

		//Add conditional formatting: cell ends with text
		_cfStyle = _sheet->ConditionalFormatting->Add(4, 4, 1, 10);
		_cfItem = _cfStyle->Add();
		_cfItem->ApplyStyleID = 0;
		_cfItem->Condition = ZCFEndsWithText;
		_cfItem->Value1 = "text";

		String _fname = _path + "tst_fcf_text.ods";
		DeleteFile(_fname);
		SaveXmlssToODFS(ze, _fname);
	}
	__finally
	{
		delete ze;
	}

Conditional formatting: formulas

Using condition ZCFIsTrueFormula: for using conditional formatting formula must return True.
Functions used in example:
  • ISODD - return True if the value is odd
  • ISEVEN - return True if the value is even
  • ROW - return the row number of cell
  • COLUMN - return the column number of cell


Using formulas in conditional formatting
Using formulas in conditional formatting

Code example for Lazarus/Delphi Code example for C++Builder
//uses zexmlss, zeodfs, zexmlssutils;
procedure TForm1.btnCreateClick(Sender: TObject);
var
  _path: string;
  ze: TZEXMLSS;
  _fname: string;
  _sheet: TZSheet;
  _cfStyle: TZConditionalStyle;
  _cfItem: TZConditionalStyleItem;
  i, j: integer;

begin
  ze := nil;
  _path := ExtractFilePath(Paramstr(0)) ;

  try
    ze := TZEXMLSS.Create(nil);

    //Prepare styles
    ze.Styles.Count := 2;

    ze.Styles[0].BGColor := clSilver;

    ze.Styles[1].Alignment.Horizontal := ZHCenter;
    ze.Styles[1].Font.Style := [fsBold];

    ze.Sheets.Count := 1;
    _sheet := ze.Sheets[0];
    _sheet.RowCount := 25;
    _sheet.ColCount := 25;

    _sheet[0, 1].Data := 'Conditional formatting: formualas';
    _sheet[0, 1].CellStyle := 1;
    _sheet.MergeCells.AddRectXY(0, 1, 20, 1);

    _sheet[0, 3].Data := 'Used formula: ISODD(ROW())';
    _sheet[10, 3].Data := 'Used formula: ISEVEN(COLUMN())';
    _sheet[0, 3].CellStyle := 1;
    _sheet[10, 3].CellStyle := 1;
    _sheet.MergeCells.AddRectXY(0, 3, 9, 3);
    _sheet.MergeCells.AddRectXY(10, 3, 20, 3);

    for i := 0 to 20 do
    for j := 4 to 21 do
      _sheet[i, j].Data := IntToStr((i + 1) * (j - 3));

    //Add conditional formatting:

    //Area for using conditional formatting:
    // begins with cell [0, 4] width = 10, height = 18
    _cfStyle := _sheet.ConditionalFormatting.Add(0, 4, 10, 18);
    //using 0 style if row is odd
    _cfItem := _cfStyle.Add();
    _cfItem.ApplyStyleID := 0;
    _cfItem.Condition := ZCFIsTrueFormula;
    _cfItem.Value1 := 'ISODD(ROW())';

    //Area for using conditional formatting:
    _cfStyle := _sheet.ConditionalFormatting.Add(10, 4, 10, 18);
    //using 0 style if column is even
    _cfItem := _cfStyle.Add();
    _cfItem.ApplyStyleID := 0;
    _cfItem.Condition := ZCFIsTrueFormula;
    _cfItem.Value1 := 'ISEVEN(COLUMN())';

    _fname := _path + 'tst_fcf_formula.ods';
    DeleteFile(_fname);
    SaveXmlssToODFS(ze, _fname);
  finally
    if (Assigned(ze)) then
      FreeAndNil(ze);
  end;
end;   

/*
#include <zexmlss.hpp>
#include <zeodfs.hpp>
*/

void __fastcall TForm1::btnCreateClick(TObject *Sender)
{
	String _path = ExtractFilePath(ParamStr(0));
	TZEXMLSS *ze = new TZEXMLSS(NULL);
	try
	{
		//Prepare styles
		ze->Styles->Count = 2;

		ze->Styles->Items[0]->BGColor = clSilver;

		ze->Styles->Items[1]->Alignment->Horizontal = ZHCenter;
		ze->Styles->Items[1]->Font->Style = TFontStyles() << fsBold;

		ze->Sheets->Count = 1;
		TZSheet *_sheet = ze->Sheets->Sheet[0];
		_sheet->RowCount = 25;
		_sheet->ColCount = 25;

		_sheet->Cell[0][1]->Data = "Conditional formatting: formualas";
		_sheet->Cell[0][1]->CellStyle = 1;
		_sheet->MergeCells->AddRectXY(0, 1, 20, 1);

		_sheet->Cell[0][3]->Data = "Used formula: ISODD(ROW())";
		_sheet->Cell[10][3]->Data = "Used formula: ISEVEN(COLUMN())";
		_sheet->Cell[0][3]->CellStyle = 1;
		_sheet->Cell[10][3]->CellStyle = 1;
		_sheet->MergeCells->AddRectXY(0, 3, 9, 3);
		_sheet->MergeCells->AddRectXY(10, 3, 20, 3);

		int i, j;
		for (i = 0; i < 21; i++)
		for (j = 4; j < 22; j++)
		{
			_sheet->Cell[i][j]->Data = IntToStr((i + 1) * (j - 3));
		}

		//Add conditional formatting:

    		//Area for using conditional formatting:
    		// begins with cell [0, 4] width = 10, height = 18
		TZConditionalStyle *_cfStyle = _sheet->ConditionalFormatting->Add(0, 4, 10, 18);
		//using 0 style if row is odd
		TZConditionalStyleItem *_cfItem = _cfStyle->Add();
		_cfItem->ApplyStyleID = 0;
		_cfItem->Condition = ZCFIsTrueFormula;
		_cfItem->Value1 = "ISODD(ROW())";

		//Area for using conditional formatting:
		_cfStyle = _sheet->ConditionalFormatting->Add(10, 4, 10, 18);
		//using 0 style if column is even
		_cfItem = _cfStyle->Add();
		_cfItem->ApplyStyleID = 0;
		_cfItem->Condition = ZCFIsTrueFormula;
		_cfItem->Value1 = "ISEVEN(COLUMN())";

		String _fname = _path + "tst_fcf_formula.ods";
		DeleteFile(_fname);
		SaveXmlssToODFS(ze, _fname);
	}
	__finally
	{
		delete ze;
	}
}
FilesMainLinksPhotosAnime

Copyright © 2006-2012 Ruslan V. Neborak