Avemey

Files

Main

Links

Photos

Anime

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.

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

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

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;
	}
}