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:
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.
|
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();
}
}
}
|
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
|
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();
}
}
}
|
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
|
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;
}
//---------------------------------------------------------------------------
|
|
