by
ru
en
by

Avemey

logo
FilesMainLinksPhotosAnime

Code examples for ZEXMLSS

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

Examples of use ZEXMLSS:

Cell style, merge cells, alignment, notes, links. Saving in formats: excel XML Spreadsheet and OpenDocument Format (ODS).


A document created using zexmlss opened in OpenOffice Calc
A document created using zexmlss opened in OpenOffice Calc.

Code example for Lazarus/Delphi Code example for C++Builder
//uses zexmlss, zeodfs, zexmlssutils, zeformula, zsspxml, zexlsx;
procedure TForm1.btnCreateClick(Sender: TObject);
var
  XMLSS: TZEXMLSS;
  i, j: integer;
  TextConverter: TAnsiToCPConverter;
  sEOL: string;

begin
  TextConverter := nil;
  {$IFNDEF FPC}
    {$IF CompilerVersion < 20} // < RAD Studio 2009
  TextConverter := @AnsiToUtf8;
    {$IFEND}
  {$ENDIF}

  {$IFDEF FPC}
  sEOL := LineEnding;
  {$ELSE}
  sEOL := sLineBreak;
  {$ENDIF}

  XMLSS := TZEXMLSS.Create(nil);
  try
    //There are 2 pages in this document
    XMLSS.Sheets.Count := 2;
    XMLSS.Sheets[0].Title := 'Test table';
    //Add styles
    XMLSS.Styles.Count := 13;
    //0 - for title (20)
    XMLSS.Styles[0].Font.Size := 20;
    XMLSS.Styles[0].Font.Style := [fsBold];
    XMLSS.Styles[0].Font.Name := 'Tahoma';
    XMLSS.Styles[0].BGColor := $CCFFCC;
    XMLSS.Styles[0].CellPattern := ZPSolid;
    XMLSS.Styles[0].Alignment.Horizontal := ZHCenter;
    XMLSS.Styles[0].Alignment.Vertical := ZVCenter;
    XMLSS.Styles[0].Alignment.WrapText := true;
    //1 - for table
    XMLSS.Styles[1].Border[0].Weight := 1;
    XMLSS.Styles[1].Border[0].LineStyle := ZEContinuous;
    for i := 1 to 3 do XMLSS.Styles[1].Border[i].Assign(
    	XMLSS.Styles[1].Border[0]);
    //2 - for table title (bold and centered)
    XMLSS.Styles[2].Assign(XMLSS.Styles[1]);
    XMLSS.Styles[2].Font.Style := [fsBold];
    XMLSS.Styles[2].Alignment.Horizontal := ZHCenter;
    //3 style
    XMLSS.Styles[3].Font.Size := 16;
    XMLSS.Styles[3].Font.Name := 'Arial Black';
    //4 style
    XMLSS.Styles[4].Font.Size := 18;
    XMLSS.Styles[4].Font.Name := 'Arial';
    //5 style
    XMLSS.Styles[5].Font.Size := 14;
    XMLSS.Styles[5].Font.Name := 'Arial Black';
    //6 style
    XMLSS.Styles[6].Assign(XMLSS.Styles[1]);
    for i := 1 to 4 do
      XMLSS.Styles[6].Border[i].Weight := 3;
    XMLSS.Styles[6].Border[5].Weight := 2;
    XMLSS.Styles[6].Border[0].Color := clRed;
    XMLSS.Styles[6].Border[5].LineStyle := ZEContinuous;

    XMLSS.Styles[6].Border[5].Color := clGreen;
    XMLSS.Styles[6].BGColor := clYellow;
    XMLSS.Styles[6].CellPattern := ZPSolid;
    XMLSS.Styles[6].Font.Color := clRed;

    //Copied table style
    for i := 7 to 12 do
      XMLSS.Styles[i].Assign(XMLSS.Styles[1]);

    //The horizontal alignment (left, center and right);
    XMLSS.Styles[7].Alignment.Horizontal := ZHLeft;
    XMLSS.Styles[8].Alignment.Horizontal := ZHCenter;
    XMLSS.Styles[9].Alignment.Horizontal := ZHRight;

    //Vertical alignment (top, center, bottom);
    XMLSS.Styles[10].Alignment.Vertical := ZVTop;
    XMLSS.Styles[11].Alignment.Vertical := ZVCenter;
    XMLSS.Styles[12].Alignment.Vertical := ZVBottom;

    with XMLSS.Sheets[0] do
    begin
      //the count of rows and columns
      RowCount := 50;
      ColCount := 20;

      Cell[0, 0].CellStyle := 3;
      Cell[0, 0].Data := 'Example of use zexmlss';

      //The cell with link
      Cell[0, 2].CellStyle := 4;
      Cell[0, 2].Data := 'Home page: http://avemey.com';
      Cell[0, 2].Href := 'http://avemey.com';
      Cell[0, 2].HRefScreenTip := 'Click on link' + sEOL + 
      	'(tooltip text for the link)';
      //Merge cells
      MergeCells.AddRectXY(0, 2, 10, 2);

      //Note
      Cell[0, 3].CellStyle := 5;
      Cell[0, 3].Data := 'There is a note!';
      Cell[0, 3].Comment := 'The text of note 1';
      Cell[0, 3].CommentAuthor := 'Someone 1 left a note';
      Cell[0, 3].ShowComment := true;

      Cell[10, 3].CellStyle := 5;
      Cell[10, 3].Data := 'Note';
      Cell[10, 3].Comment := 'The text of note 2';
      Cell[10, 3].CommentAuthor := 'Someone 2 left a note';
      Cell[10, 3].ShowComment := true;
      Cell[10, 3].AlwaysShowComment := true;
      ColWidths[10] := 160; // column width
      Columns[10].WidthMM := 40; //~40 mm

      //Merged cells
      Cell[2, 1].CellStyle := 0;
      Cell[2, 1].Data := 'Some title';
      MergeCells.AddRectXY(2, 1, 12, 1);

      Cell[0, 6].Data := 'Merge' + sEOL + 'cell!';
      Cell[0, 6].CellStyle := 0;
      MergeCells.AddRectXY(0, 6, 3, 14);

      Cell[9, 6].Data := ' style';
      Cell[9, 6].CellStyle := 2;

      Cell[10, 6].Data := 'Style sample';
      Cell[10, 6].CellStyle := 2;

      j := 8;

      for i := 0 to 6 do
      begin
        Cell[9,  j].Data := IntToStr(i) + '-th';
        Cell[10, j].Data := 'text';
        Cell[10, j].CellStyle := i;
        inc(j, 2);
      end;

      Columns[5].WidthMM := 30;
      Columns[6].WidthMM := 30;

      Cell[5, 5].Data := 'Alignment';
      MergeCells.AddRectXY(5, 5, 6, 5);

      Cell[5, 6].Data := 'Horizontal';
      Cell[5, 6].CellStyle := 2;

      Cell[6, 6].Data := 'Vertical';
      Cell[6, 6].CellStyle := 2;

      for i := 5 to 6 do
      for j := 7 to 9 do
        Cell[i, j].Data := 'text';

      for i := 7 to 9 do
      begin
        Cell[5, i].CellStyle := i;
        Cell[6, i].CellStyle := i + 3;
        Rows[i].HeightMM := 14;
      end;
    end;

    //Copying from page 0 into page 1
    XMLSS.Sheets[1].Assign(XMLSS.Sheets[0]);
    XMLSS.Sheets[1].Title := 'Test table (copy)';

    //Saving pages 0 and 1 as:
    //excel xml
    SaveXmlssToEXML(XMLSS, {some path}'save_test.xml', 
    	[0, 1], [], TextConverter, 'UTF-8');
    //Path examples:
    //	/home/user_name/some_path/
    //	d:\some_path\
    //The path must exist!
    //not packed ODS
    SaveXmlssToODFSPath(XMLSS, 'd:\work\ods_path\', 
    	[0, 1], [], TextConverter, 'UTF-8');
    //not packed xlsx
    SaveXmlssToXLSXPath(XMLSS, 'd:\work\xlsx_path\', 
    	[0, 1], [], TextConverter, 'UTF-8');
    {$IFDEF FPC}
    //For now only for Lazarus
    //Save as ODS
    SaveXmlssToODFS(XMLSS, {some path}'save_test.ods', 
    	[0, 1], [], TextConverter, 'UTF-8');
    //Save as xlsx
    SaveXmlssToXLSX(XMLSS, {some path}'save_test.xlsx', 
    	[0, 1], [], TextConverter, 'UTF-8');
    {$ENDIF}

  finally
    FreeAndNil(XMLSS);
  end;
end;   

/*
#include <zexmlss.hpp>
#include <zexmlssutils.hpp>
#include <zeodfs.hpp>
#include <zeformula.hpp>
#include <zsspxml.hpp>
*/
void __fastcall TForm1::btnCreateClick(TObject *Sender)
{
	TZEXMLSS *XMLSS = NULL;
	__try
	{
		TAnsiToCPConverter TextConverter = NULL;
		#if __BORLANDC__ < 0x613 // < RAD Studio 2009
		TextConverter = *AnsiToUtf8;
		#endif

		XMLSS = new TZEXMLSS(NULL);

		//There are 2 pages in this document
		XMLSS->Sheets->Count = 2;
		//Add styles
		XMLSS->Styles->Count = 13;
		//0 - for title (20)
		XMLSS->Styles->Items[0]->Font->Size = 20;
		XMLSS->Styles->Items[0]->Font->Style =  TFontStyles() << fsBold;
		XMLSS->Styles->Items[0]->Font->Name = "Tahoma";
		XMLSS->Styles->Items[0]->BGColor = 0xCCFFCC;
		XMLSS->Styles->Items[0]->CellPattern = ZPSolid;
		XMLSS->Styles->Items[0]->Alignment->Horizontal = ZHCenter;
		XMLSS->Styles->Items[0]->Alignment->Vertical = ZVCenter;
		XMLSS->Styles->Items[0]->Alignment->WrapText = true;
		//1 - for table
		XMLSS->Styles->Items[1]->Border->Border[0]->Weight = 1;
		XMLSS->Styles->Items[1]->Border->Border[0]->LineStyle = ZEContinuous;
		int i = 0;
		for (i = 1; i < 4; i++)
		{
			XMLSS->Styles->Items[1]->Border->Border[i]->Assign(
				XMLSS->Styles->Items[1]->Border->Border[0]);
		}
		//2 - for table title (bold and centered)
		XMLSS->Styles->Items[2]->Assign(XMLSS->Styles->Items[1]);
		XMLSS->Styles->Items[2]->Font->Style = TFontStyles() << fsBold;
		XMLSS->Styles->Items[2]->Alignment->Horizontal = ZHCenter;
		//3 style
		XMLSS->Styles->Items[3]->Font->Size = 16;
		XMLSS->Styles->Items[3]->Font->Name = "Arial Black";
		//4 style
		XMLSS->Styles->Items[4]->Font->Size = 18;
		XMLSS->Styles->Items[4]->Font->Name = "Arial";
		//5 style
		XMLSS->Styles->Items[5]->Font->Size = 14;
		XMLSS->Styles->Items[5]->Font->Name = "Arial Black";
        	//6 style
		XMLSS->Styles->Items[6]->Assign(XMLSS->Styles->Items[1]);
		for (i = 1; i < 4; i++)
		{
			XMLSS->Styles->Items[6]->Border->Border[i]->Weight = 3;
		}
		XMLSS->Styles->Items[6]->Border->Border[5]->Weight = 2;
		XMLSS->Styles->Items[6]->Border->Border[0]->Color = clRed;
		XMLSS->Styles->Items[6]->Border->Border[5]->LineStyle = ZEContinuous;

		XMLSS->Styles->Items[6]->Border->Border[5]->Color = clGreen;
		XMLSS->Styles->Items[6]->BGColor = clYellow;
		XMLSS->Styles->Items[6]->CellPattern = ZPSolid;
		XMLSS->Styles->Items[6]->Font->Color = clRed;

		//Copied table style
		for (i = 7; i < 13; i++)
		{
			XMLSS->Styles->Items[i]->Assign(XMLSS->Styles->Items[1]);
		}

		//The horizontal alignment (left, center and right);
		XMLSS->Styles->Items[7]->Alignment->Horizontal = ZHLeft;
		XMLSS->Styles->Items[8]->Alignment->Horizontal = ZHCenter;
		XMLSS->Styles->Items[9]->Alignment->Horizontal = ZHRight;

		//Vertical alignment (top, center, bottom);
		XMLSS->Styles->Items[10]->Alignment->Vertical = ZVTop;
		XMLSS->Styles->Items[11]->Alignment->Vertical = ZVCenter;
		XMLSS->Styles->Items[12]->Alignment->Vertical = ZVBottom;

		//the count of rows and columns
		XMLSS->Sheets->Sheet[0]->RowCount = 50;
		TZSheet * sh = XMLSS->Sheets->Sheet[0];
		sh->ColCount = 20;

		sh->Cell[0][0]->CellStyle = 3;
		sh->Cell[0][0]->Data = "Example of use zexmlss";

		//The cell with link
		sh->Cell[0][2]->CellStyle = 4;
		sh->Cell[0][2]->Data = "Home page: http://avemey.com";
		sh->Cell[0][2]->Href = "http://avemey.com";
		sh->Cell[0][2]->HRefScreenTip = String("Click on link") + sLineBreak + 
			String("(tooltip text for the link)");
		//Merge cells
		sh->MergeCells->AddRectXY(0, 2, 10, 2);

		//Note
		sh->Cell[0][3]->CellStyle = 5;
		sh->Cell[0][3]->Data = "There is a note!";
		sh->Cell[0][3]->Comment = "The text of note 1";
		sh->Cell[0][3]->CommentAuthor = "Someone 1 left a note";
		sh->Cell[0][3]->ShowComment = true;

		sh->Cell[10][3]->CellStyle = 5;
		sh->Cell[10][3]->Data = "Note";
		sh->Cell[10][3]->Comment = "The text of note 2";
		sh->Cell[10][3]->CommentAuthor = "Someone 2 left a note";
		sh->Cell[10][3]->ShowComment = true;
		sh->Cell[10][3]->AlwaysShowComment = true;
		sh->ColWidths[10] = 160; //column width
		sh->Columns[10]->WidthMM = 40; //~40 mm

		//Merged cells
		sh->Cell[2][1]->CellStyle = 0;
		sh->Cell[2][1]->Data = "Some title";
		sh->MergeCells->AddRectXY(2, 1, 12, 1);

		sh->Cell[0][6]->Data = String("Merge") + sLineBreak + 
			String("cell!");
		sh->Cell[0][6]->CellStyle = 0;
		sh->MergeCells->AddRectXY(0, 6, 3, 14);

		sh->Cell[9][6]->Data = " style";
		sh->Cell[9][6]->CellStyle = 2;

		sh->Cell[10][6]->Data = "Style sample";
		sh->Cell[10][6]->CellStyle = 2;

		int j = 8;

		for (i = 0; i < 7; i++)
		{
		  sh->Cell[9][j]->Data = IntToStr(i) + String("-th");
		  sh->Cell[10][j]->Data = "text";
		  sh->Cell[10][j]->CellStyle = i;
		  j+=2;
		}

		sh->Columns[5]->WidthMM = 30;
		sh->Columns[6]->WidthMM = 30;

		sh->Cell[5][5]->Data = "Alignment";
		sh->MergeCells->AddRectXY(5, 5, 6, 5);

		sh->Cell[5][6]->Data = "Horizontal";
		sh->Cell[5][6]->CellStyle = 2;

		sh->Cell[6][6]->Data = "Vertical";
		sh->Cell[6][6]->CellStyle = 2;

		for (i = 5; i < 7; i++)
		for (j = 7; j < 10; j++)
		  sh->Cell[i][j]->Data = "text";

		for (i = 7; i < 10; i++)
		{
		  sh->Cell[5][i]->CellStyle = i;
		  sh->Cell[6][i]->CellStyle = i + 3;
		  sh->Rows[i]->HeightMM = 14;
		}

		//Copying from page 0 into page 1
		XMLSS->Sheets->Sheet[1]->Assign(sh);

		//Saving pages 0 and 1 as:
		int sheets[2] = {0, 1};
		String sheetnames[2] = {String("Test table"), 
			String("Test table (copy)")};
                //excel xml
		SaveXmlssToEXML(XMLSS, "save_test.xml", sheets, 2, sheetnames, 
			2, TextConverter, "UTF-8");
                //The path must exist!
                //not packed ODS
		SaveXmlssToODFSPath(XMLSS, "d:\\work\\ods_path\\", sheets, 
			2, sheetnames, 2, TextConverter, "UTF-8");
	}
	__finally
	{
		if (XMLSS != NULL)
		{
			XMLSS->Free();
		}
    	}
}

The formulas for ODS and excel xml

For ODF use the A1-style formulas notation (=A1+B10-C12). For excel xml use R1C1 style (=R1C1+R10C2-R12C3). For translate from one to another formulas notation use ZEA1ToR1C1 and ZER1C1ToA1 functions.


using formulas
using formulas

Code example for Lazarus/Delphi Code example for C++Builder
//uses zexmlss, zeodfs, zexmlssutils, zeformula, zsspxml;
procedure TForm1.btnFormulaClick(Sender: TObject);
var
  XMLSS: TZEXMLSS;
  i, j: integer;
  TextConverter: TAnsiToCPConverter;
  sEOL, s: string;

begin
  TextConverter := nil;
  {$IFNDEF FPC}
    {$IF CompilerVersion < 20} // < RAD Studio 2009
  TextConverter := @AnsiToUtf8;
    {$IFEND}
  {$ENDIF}

  {$IFDEF FPC}
  sEOL := LineEnding;
  {$ELSE}
  sEOL := sLineBreak;
  {$ENDIF}

  XMLSS := TZEXMLSS.Create(nil);
  try
    //There are 2 pages in this document
    XMLSS.Sheets.Count := 2;
    XMLSS.Sheets[0].Title := 'Formulas (ODF)';
    //Add styles
    XMLSS.Styles.Count := 3;
    //0 - for title (20)
    XMLSS.Styles[0].Font.Size := 20;
    XMLSS.Styles[0].Font.Style := [fsBold];
    XMLSS.Styles[0].Font.Name := 'Tahoma';
    XMLSS.Styles[0].BGColor := $CCFFCC;
    XMLSS.Styles[0].CellPattern := ZPSolid;
    XMLSS.Styles[0].Alignment.Horizontal := ZHCenter;
    XMLSS.Styles[0].Alignment.Vertical := ZVCenter;
    XMLSS.Styles[0].Alignment.WrapText := true;
    //1 - for table
    XMLSS.Styles[1].Border[0].Weight := 1;
    XMLSS.Styles[1].Border[0].LineStyle := ZEContinuous;
    for i := 1 to 3 do 
      XMLSS.Styles[1].Border[i].Assign(XMLSS.Styles[1].Border[0]);
    //2 - for table title (bold and centered)
    XMLSS.Styles[2].Assign(XMLSS.Styles[1]);
    XMLSS.Styles[2].Font.Style := [fsBold];
    XMLSS.Styles[2].Alignment.Horizontal := ZHCenter;

    with XMLSS.Sheets[0] do
    begin
      //the count of rows and columns
      RowCount := 50;
      ColCount := 20;

      Cell[0, 0].CellStyle := 0;
      Cell[0, 0].Data := 'An example of the use of formulas in zexmlss';
      MergeCells.AddRectXY(0, 0, 10, 0);

      for  i := 0 to 4 do
      begin
        Cell[i, 1].CellStyle := 2;
        Cell[i, 12].CellStyle := 2;
      end;
      Cell[0, 1].Data := 'Num';
      for i := 1 to 3 do
         Cell[i, 1].Data := 'Value ' + IntToStr(i);
      Cell[4, 1].Data := 'Formula';
      Cell[0, 12].Data := 'Total';

      for i := 2 to 11 do
      begin
        Cell[0, i].Data := IntToStr(i);
        Cell[0, i].CellStyle := 1;
        for j := 1 to 4 do
        begin
          Cell[j, i].Data := IntToStr(Random(100));
          Cell[j, i].CellStyle := 1;
          Cell[j, i].CellType := ZENumber;
        end;
      end;
    end;

    //Copying from page 0 into page 1
    XMLSS.Sheets[1].Assign(XMLSS.Sheets[0]);
    XMLSS.Sheets[1].Title := 'Formulas (Excel XML)';

    //Formulas for ODF
    with XMLSS.Sheets[0] do
    begin
      for i := 2 to 11 do
      begin
        s := IntToStr(i + 1);
        Cell[4, i].Formula := '=B' + s + '*C' + s + ' - D' + s; //=B*C-D
      end;
      for i := 1 to 4 do
      begin
        s := ZEGetA1byCol(i); //Get the name of the column by its number
        Cell[i, 12].Formula := '=SUM(' + s +'3:' + s + '12)';
      end;
    end;

    //Formulas Excel XML SpreadSheet
    with XMLSS.Sheets[1] do
    begin
      for i := 2 to 11 do
        Cell[4, i].Formula := '=RC[-3] * RC[-2] - RC[-1]'; //=B*C-D
      for i := 1 to 4 do
        Cell[i, 12].Formula := '=SUM(R2C:R12C)';
    end;

    //Saving pages 0 and 1
    SaveXmlssToEXML(XMLSS, 'excelxml_formula.xml', 
    	[0, 1], [], TextConverter, 'UTF-8');
    SaveXmlssToODFSPath(XMLSS, 'd:\work\ods_path\', 
    	[0, 1], [], TextConverter, 'UTF-8');
    {$IFDEF FPC}
    SaveXmlssToODFS(XMLSS, 'ods_formula.ods', 
    	[0, 1], [], TextConverter, 'UTF-8');
    {$ENDIF}

  finally
    FreeAndNil(XMLSS);
  end;
end;   

/*
#include <zexmlss.hpp>
#include <zexmlssutils.hpp>
#include <zeodfs.hpp>
#include <zeformula.hpp>
#include <zsspxml.hpp>
*/
void __fastcall TForm1::btnFormulaClick(TObject *Sender)
{
	TZEXMLSS *XMLSS = NULL;
	__try
	{
		TAnsiToCPConverter TextConverter = NULL;
		#if __BORLANDC__ < 0x613 // < RAD Studio 2009
		TextConverter = *AnsiToUtf8;
		#endif

		XMLSS = new TZEXMLSS(NULL);

		//There are 2 pages in this document
		XMLSS->Sheets->Count = 2;
		//Add styles
		XMLSS->Styles->Count = 3;
		//0 - for title (20)
		XMLSS->Styles->Items[0]->Font->Size = 20;
		XMLSS->Styles->Items[0]->Font->Style =  TFontStyles() << fsBold;
		XMLSS->Styles->Items[0]->Font->Name = "Tahoma";
		XMLSS->Styles->Items[0]->BGColor = 0xCCFFCC;
		XMLSS->Styles->Items[0]->CellPattern = ZPSolid;
		XMLSS->Styles->Items[0]->Alignment->Horizontal = ZHCenter;
		XMLSS->Styles->Items[0]->Alignment->Vertical = ZVCenter;
		XMLSS->Styles->Items[0]->Alignment->WrapText = true;
		//1 - for table
		XMLSS->Styles->Items[1]->Border->Border[0]->Weight = 1;
		XMLSS->Styles->Items[1]->Border->Border[0]->LineStyle = ZEContinuous;
		int i = 0;
		for (i = 1; i < 4; i++)
		{
			XMLSS->Styles->Items[1]->Border->Border[i]->Assign(
				XMLSS->Styles->Items[1]->Border->Border[0]);
		}
		//2 - for table title (bold and centered)
		XMLSS->Styles->Items[2]->Assign(XMLSS->Styles->Items[1]);
		XMLSS->Styles->Items[2]->Font->Style = TFontStyles() << fsBold;
		XMLSS->Styles->Items[2]->Alignment->Horizontal = ZHCenter;

		//the count of rows and columns
		XMLSS->Sheets->Sheet[0]->RowCount = 50;
		TZSheet * sh = XMLSS->Sheets->Sheet[0];
		sh->ColCount = 20;

		sh->Cell[0][0]->CellStyle = 3;
		sh->Cell[0][0]->Data = "An example of the use of formulas in zexmlss";
		sh->MergeCells->AddRectXY(0, 0, 10, 0);
		for (i = 0; i < 5; i++)
		{
			sh->Cell[i][1]->CellStyle = 2;
			sh->Cell[i][12]->CellStyle = 2;
		}
		sh->Cell[0][1]->Data = "Num";
		for (i = 1; i < 4; i++)
			sh->Cell[i][1]->Data = String("Value ") + IntToStr(i);
		sh->Cell[4][1]->Data = "Formula";
		sh->Cell[0][12]->Data = "Total";

		int j = 0;
		for (i = 2; i < 12; i++)
		{
			sh->Cell[0][i]->Data = IntToStr(i);
			sh->Cell[0][i]->CellStyle = 1;
			for (j = 1; j < 5; j++)
			{
				sh->Cell[j][i]->Data = IntToStr(Random(100));
				sh->Cell[j][i]->CellStyle = 1;
				sh->Cell[j][i]->CellType = ZENumber;
			}
		}

		//Copying from page 0 into page 1
		XMLSS->Sheets->Sheet[1]->Assign(sh);

		//Formulas for ODF
		String s;
		for (i = 2; i < 12; i++)
		{
			s = IntToStr(i + 1);
			sh->Cell[4][i]->Formula = String("=B") + s + 
				String("*C") + s + String(" - D") + s; //=B*C-D
		}
		for (i = 1; i < 5; i++)
		{
			s = ZEGetA1byCol(i); //Get the name of the column by its number
			sh->Cell[i][12]->Formula = String("=SUM(") + s + 
				String("3:") + s + String("12)");
		}

		//Formulas Excel XML SpreadSheet
		sh = XMLSS->Sheets->Sheet[1];
		for (i = 2; i < 12; i++)
			sh->Cell[4][i]->Formula = String("=RC[-3] * RC[-2] - RC[-1]"); //=B*C-D
		for (i = 1; i < 5; i++)
			sh->Cell[i][12]->Formula = String("=SUM(R2C:R12C)");

		//Saving pages 0 and 1
		int sheets[2] = {0, 1};
		String sheetnames[2] = {String("Formulas (ODF)"), String("Formulas (Excel XML)")};
		SaveXmlssToEXML(XMLSS, "excelxml_formula.xml", sheets, 
			2, sheetnames, 2, TextConverter, "UTF-8");
		SaveXmlssToODFSPath(XMLSS, "d:\\work\\ods_path\\", sheets, 
			2, sheetnames, 2, TextConverter, "UTF-8");
	}
	__finally
	{
		if (XMLSS != NULL)
		{
			XMLSS->Free();
		}
	}
}

Translating formulas from A1 to R1C1 style and back

Convert formula from A1 to R1C1 style: function ZEA1ToR1C1(const formula: string; CurCol, CurRow: integer; options: integer; StartZero: boolean = true): string;
Convert formula from R1C1 to A1 style: function ZER1C1ToA1(const formula: string; CurCol, CurRow: integer; options: integer; StartZero: boolean = true): string;
formula - A1 or R1C1 formulas text, CurRow - number of row, CurCol - number of column, options - conversion options, StartZero - if true then row/column counting starts from 0 (A1 = (CurCol=0; CurRow=0)).

Example of converting formulas
Example of converting formulas

Code example for Lazarus/Delphi Code example for C++Builder
//An example of the using of functions ZEA1ToR1C1 and ZER1C1ToA1 for translations formulas.
program consoletest;

{$IFDEF FPC}
{$mode objfpc}{$H+}
{$ELSE}
{$APPTYPE CONSOLE}
{$ENDIF}

uses
  {$IFDEF UNIX}{$IFDEF UseCThreads}
  cthreads,
  {$ENDIF}{$ENDIF}
  SysUtils, zeformula;

var
  s: string;

function _WriteFormulaA1(const formula: string; Column, Row, Options: integer): string;
var
  s: string;

begin
  s := ZEA1ToR1C1(formula, Column, Row, Options, true);
  WriteLn('Cell ' + ZEGetA1byCol(Column, true) + IntToStr(Row + 1) + ': ' + formula + ' <=> ' + s);
  result := s;
end;

function _WriteFormulaR1C1(const formula: string; Column, Row, Options: integer): string;
var
  s: string;

begin
  s := ZER1C1ToA1(formula, Column, Row, Options, true);
  WriteLn('Cell ' + ZEGetA1byCol(Column, true) + IntToStr(Row + 1) + ': ' + formula + ' <=> ' + s);
  result := s;
end;

begin
  s := '=A1+b1+c1+d10+k10';
  s := _WriteFormulaA1(s, 1, 1, 0);
  _WriteFormulaR1C1(s, 1, 1, ZE_RTA_ODF);

  s := '=AVERAGE(R[-942]C:R[-3]C)';
  s := _WriteFormulaR1C1(s, 5, 1000, 0);
  s := _WriteFormulaA1(s, 5, 1000, 0);

  s := '=IF([''some list name''.A1] <> A3; 23; A1+B2+C2)';
  s := _WriteFormulaA1(s, 6, 6, 0);
  _WriteFormulaR1C1(s, 6, 6, ZE_RTA_ODF);

  s := '=R1C1 + list1!RC + R[0]C[0] + R[1]C + r[-1]C + sum(R1C1 + R5C5; 23 ; RC)';
  s := _WriteFormulaR1C1(s, 1, 3, 0);
  _WriteFormulaA1(s, 1, 3, ZE_ATR_DEL_PREFIX);

  s := '=list1!$A$1+$A1+A$1';
  s := _WriteFormulaA1(s, 1, 3, 0);
  _WriteFormulaR1C1(s, 1, 3, 0);

  s := 'of:=[.A1]+[.B2]+[.B3]';
  s := _WriteFormulaA1(s, 1, 3, ZE_ATR_DEL_PREFIX);
  _WriteFormulaR1C1(s, 1, 3, 0);

  s := 'of:=SUM([.A1:.C1])+[.A1]+23';
  s := _WriteFormulaA1(s, 1, 4, ZE_ATR_DEL_PREFIX);
  _WriteFormulaR1C1(s, 1, 4, 0);

  s := '=A1+B2+B3';
  s := _WriteFormulaA1(s, 1, 3, 0);
  _WriteFormulaR1C1(s, 1, 3, 0);

  s := '=SUM(A1:C1)+A1+23';
  s := _WriteFormulaA1(s, 1, 4, 0);
  _WriteFormulaR1C1(s, 1, 4, 0);

  s := '=$A$1-[.A3]+A1+A2+a3+A4-Sum(A1:A10) - $A$2';
  s := _WriteFormulaA1(s, 3, 3, 0);
  _WriteFormulaR1C1(s, 3, 3, 0);

  s := '=$A$1-B1';
  s := _WriteFormulaA1(s, 5, 5, 0);
  _WriteFormulaR1C1(s, 5, 5, 0);

  s := '=A1+B1+C1+D10+K10';
  s := _WriteFormulaA1(s, 2, 2, 0);
  _WriteFormulaR1C1(s, 2, 2, ZE_RTA_ODF);

  s := '=IF(A1 <> A3; 23; A1+B2+C2)';
  s := _WriteFormulaA1(s, 2, 2, 0);
  _WriteFormulaR1C1(s, 2, 2, ZE_RTA_ODF or ZE_RTA_ODF_NO_BRACKET);
end.
//An example of the using of functions ZEA1ToR1C1 and ZER1C1ToA1 for translations formulas.
//---------------------------------------------------------------------------
#include <stdio.h>
#if __BORLANDC__ >= 0x613
#include <tchar.h>
#endif
#include <zeformula.hpp>

#if __BORLANDC__ < 0x613 // < RAD Studio 2009
#define str_for_printf "Cell %s%d: %s <=> %s\n"
#else
#define str_for_printf "Cell %ls%d: %ls <=> %ls\n"
#endif

//---------------------------------------------------------------------------

String _WriteFormulaA1(String formula, int Column, int Row, int Options)
{
	String ss = ZEA1ToR1C1(formula, Column, Row, Options, true);
	printf(str_for_printf, ZEGetA1byCol(Column, true), Row + 1, formula, ss);
	return ss;
}

String _WriteFormulaR1C1(String formula, int Column, int Row, int Options)
{
	String ss = ZER1C1ToA1(formula, Column, Row, Options, true);
	printf(str_for_printf, ZEGetA1byCol(Column, true), Row + 1, formula, ss);
	return ss;
}

#if __BORLANDC__ < 0x613
int main(int argc, char* argv[])
#else
int _tmain(int argc, _TCHAR* argv[])
#endif
{
	String s = "=A1+b1+c1+d10+k10";
	s = _WriteFormulaA1(s, 1, 1, 0);
	_WriteFormulaR1C1(s, 1, 1, ZE_RTA_ODF);

	s = "=AVERAGE(R[-942]C:R[-3]C)";
	s = _WriteFormulaR1C1(s, 5, 1000, 0);
	s = _WriteFormulaA1(s, 5, 1000, 0);

	s = "=IF(['some list name'.A1] <> A3; 23; A1+B2+C2)";
	s = _WriteFormulaA1(s, 6, 6, 0);
	_WriteFormulaR1C1(s, 6, 6, ZE_RTA_ODF);

	s = "=R1C1 + list1!RC + R[0]C[0] + R[1]C + r[-1]C + sum(R1C1 + R5C5; 23 ; RC)";
	s = _WriteFormulaR1C1(s, 1, 3, 0);
	_WriteFormulaA1(s, 1, 3, ZE_ATR_DEL_PREFIX);

	s = "=list1!$A$1+$A1+A$1";
	s = _WriteFormulaA1(s, 1, 3, 0);
	_WriteFormulaR1C1(s, 1, 3, 0);

	s = "of:=[.A1]+[.B2]+[.B3]";
	s = _WriteFormulaA1(s, 1, 3, ZE_ATR_DEL_PREFIX);
	_WriteFormulaR1C1(s, 1, 3, 0);

	s = "of:=SUM([.A1:.C1])+[.A1]+23";
	s = _WriteFormulaA1(s, 1, 4, ZE_ATR_DEL_PREFIX);
	_WriteFormulaR1C1(s, 1, 4, 0);

	s = "=A1+B2+B3";
	s = _WriteFormulaA1(s, 1, 3, 0);
	_WriteFormulaR1C1(s, 1, 3, 0);

	s = "=SUM(A1:C1)+A1+23";
	s = _WriteFormulaA1(s, 1, 4, 0);
	_WriteFormulaR1C1(s, 1, 4, 0);

	s = "=$A$1-[.A3]+A1+A2+a3+A4-Sum(A1:A10) - $A$2";
	s = _WriteFormulaA1(s, 3, 3, 0);
	_WriteFormulaR1C1(s, 3, 3, 0);

	s = "=$A$1-B1";
	s = _WriteFormulaA1(s, 5, 5, 0);
	_WriteFormulaR1C1(s, 5, 5, 0);

	s = "=A1+B1+C1+D10+K10";
	s = _WriteFormulaA1(s, 2, 2, 0);
	_WriteFormulaR1C1(s, 2, 2, ZE_RTA_ODF);

	s = "=IF(A1 <> A3; 23; A1+B2+C2)";
	s = _WriteFormulaA1(s, 2, 2, 0);
	_WriteFormulaR1C1(s, 2, 2, ZE_RTA_ODF || ZE_RTA_ODF_NO_BRACKET);
	return 0;
}
//---------------------------------------------------------------------------
FilesMainLinksPhotosAnime

Copyright © 2006-2012 Ruslan V. Neborak