Прыклады кода для ZEXMLSS
ZEXMLSS 0.0.8 правяраўся на:
- Lazarus 1.2.6 (FPC 2.6 + Debian 8 / Windows)
- Delphi 7, 2005-XE2
- C++Builder XE
Прыклады кода:
Стыль вочка, аб'яднаныя вочкі, выраўноўванне, нататкі, спасылкі. Захаванне ў фарматах: excel XML Spreadsheet і OpenDocument Format (ODS).
Дакумент, створаны з дапамогай zexmlss адкрыты ў OpenOffice Calc.
|
Прыклад кода для Lazarus/Delphi
|
Прыклад кода для C++Builder-a
|
//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
//У дакуменце 2 старонкі
XMLSS.Sheets.Count := 2;
XMLSS.Sheets[0].Title := 'Тэставая табліца';
//стылі
XMLSS.Styles.Count := 13;
//0 - для загалоўка (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 - стыль для табліцы
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 - стыль для загалоўка табліцы (тоўсты па цэнтры)
XMLSS.Styles[2].Assign(XMLSS.Styles[1]);
XMLSS.Styles[2].Font.Style := [fsBold];
XMLSS.Styles[2].Alignment.Horizontal := ZHCenter;
//3-i стыль
XMLSS.Styles[3].Font.Size := 16;
XMLSS.Styles[3].Font.Name := 'Arial Black';
//4-ы стыль
XMLSS.Styles[4].Font.Size := 18;
XMLSS.Styles[4].Font.Name := 'Arial';
//5-ы стыль
XMLSS.Styles[5].Font.Size := 14;
XMLSS.Styles[5].Font.Name := 'Arial Black';
//6-ы
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;
//Капіюецца стыль табліцы
for i := 7 to 12 do
XMLSS.Styles[i].Assign(XMLSS.Styles[1]);
//Гарызантальнае выраўноўванне (злева, цэнтр, справа);
XMLSS.Styles[7].Alignment.Horizontal := ZHLeft;
XMLSS.Styles[8].Alignment.Horizontal := ZHCenter;
XMLSS.Styles[9].Alignment.Horizontal := ZHRight;
//Вертыкальнае выраўноўванне (зверху, цэнтр, знізу);
XMLSS.Styles[10].Alignment.Vertical := ZVTop;
XMLSS.Styles[11].Alignment.Vertical := ZVCenter;
XMLSS.Styles[12].Alignment.Vertical := ZVBottom;
with XMLSS.Sheets[0] do
begin
//колькасць радкоў і слупкоў
RowCount := 50;
ColCount := 20;
Cell[0, 0].CellStyle := 3;
Cell[0, 0].Data := 'Прыклад выкарыстання zexmlss';
//вочка са спасылкай
Cell[0, 2].CellStyle := 4;
Cell[0, 2].Data := 'Хатняя старонка: http://avemey.com';
Cell[0, 2].Href := 'http://avemey.com';
Cell[0, 2].HRefScreenTip := 'Клікніце па спасылцы' + sEOL +
'(тэкст усплывальнай падказкі да спасылкі)';
//Аб'яднанае вочка
MergeCells.AddRectXY(0, 2, 10, 2);
//Нататак
Cell[0, 3].CellStyle := 5;
Cell[0, 3].Data := 'Ёсць нататак!';
Cell[0, 3].Comment := 'Тэкст нататка 1';
Cell[0, 3].CommentAuthor := 'Хтосьці 1 пакінуў нататку';
Cell[0, 3].ShowComment := true;
Cell[10, 3].CellStyle := 5;
Cell[10, 3].Data := 'Таксама нататак!';
Cell[10, 3].Comment := 'Тэкст нататкі 2';
Cell[10, 3].CommentAuthor := 'Хтосьці 2 пакінуў нататку';
Cell[10, 3].ShowComment := true;
Cell[10, 3].AlwaysShowComment := true;
ColWidths[10] := 160; //Шырыня слупка
Columns[10].WidthMM := 40; //~40 мм
//Аб'яднаныя вочкі
Cell[2, 1].CellStyle := 0;
Cell[2, 1].Data := 'Нейкі загаловак';
MergeCells.AddRectXY(2, 1, 12, 1);
Cell[0, 6].Data := 'Аб''яднаная' + sEOL + 'вочка!';
Cell[0, 6].CellStyle := 0;
MergeCells.AddRectXY(0, 6, 3, 14);
Cell[9, 6].Data := '№ стылю';
Cell[9, 6].CellStyle := 2;
Cell[10, 6].Data := 'Прыклад стылю';
Cell[10, 6].CellStyle := 2;
j := 8;
for i := 0 to 6 do
begin
Cell[9, j].Data := IntToStr(i) + '-ы';
Cell[10, j].Data := 'тэкст';
Cell[10, j].CellStyle := i;
inc(j, 2);
end;
Columns[5].WidthMM := 30;
Columns[6].WidthMM := 30;
Cell[5, 5].Data := 'Выраўноўванне';
MergeCells.AddRectXY(5, 5, 6, 5);
Cell[5, 6].Data := 'Гарызанталь';
Cell[5, 6].CellStyle := 2;
Cell[6, 6].Data := 'Вертыкаль';
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;
//капіяванне з 0 на 1-ую старонку
XMLSS.Sheets[1].Assign(XMLSS.Sheets[0]);
XMLSS.Sheets[1].Title := 'Тэставая табліца (копія)';
//захаваць 0-ую і 1-ую старонку
//захаваць як excel xml
SaveXmlssToEXML(XMLSS, {нейкі шлях}'save_test.xml',
[0, 1], [], TextConverter, 'UTF-8');
//Прыклады шляхоў:
// /home/user_name/some_path/
// d:\some_path\
//Шлях павінен існаваць!
//захаваць як незапакаваны ods
SaveXmlssToODFSPath(XMLSS, 'd:\work\ods_path\',
[0, 1], [], TextConverter, 'UTF-8');
//захаваць як незапакаваны xlsx
SaveXmlssToXLSXPath(XMLSS, 'd:\work\xlsx_path\',
[0, 1], [], TextConverter, 'UTF-8');
{$IFDEF FPC}
//Пакуль толькі для лазаруса
//захаваць як ods
SaveXmlssToODFS(XMLSS, {нейкі шлях}'save_test.ods',
[0, 1], [], TextConverter, 'UTF-8');
//захаваць як xlsx
SaveXmlssToXLSX(XMLSS, {нейкі шлях}'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);
//У дакуменце 2 старонкі
XMLSS->Sheets->Count = 2;
//стылі
XMLSS->Styles->Count = 13;
//0 - для загалоўка (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 - стыль для табліцы
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 - стыль для загалоўка табліцы (тоўсты па цэнтры)
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-i стыль
XMLSS->Styles->Items[3]->Font->Size = 16;
XMLSS->Styles->Items[3]->Font->Name = "Arial Black";
//4-ы стыль
XMLSS->Styles->Items[4]->Font->Size = 18;
XMLSS->Styles->Items[4]->Font->Name = "Arial";
//55-ы стыль
XMLSS->Styles->Items[5]->Font->Size = 14;
XMLSS->Styles->Items[5]->Font->Name = "Arial Black";
//6-ы
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;
//Капіюецца стыль табліцы
for (i = 7; i < 13; i++)
{
XMLSS->Styles->Items[i]->Assign(XMLSS->Styles->Items[1]);
}
//Гарызантальнае выраўноўванне (злева, цэнтр, справа);
XMLSS->Styles->Items[7]->Alignment->Horizontal = ZHLeft;
XMLSS->Styles->Items[8]->Alignment->Horizontal = ZHCenter;
XMLSS->Styles->Items[9]->Alignment->Horizontal = ZHRight;
//Вертыкальнае выраўноўванне (зверху, цэнтр, знізу);
XMLSS->Styles->Items[10]->Alignment->Vertical = ZVTop;
XMLSS->Styles->Items[11]->Alignment->Vertical = ZVCenter;
XMLSS->Styles->Items[12]->Alignment->Vertical = ZVBottom;
//колькасць радкоў і слупкоў
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 = "Прыклад выкарыстання zexmlss";
//вочка са спасылкай
sh->Cell[0][2]->CellStyle = 4;
sh->Cell[0][2]->Data = "Хатняя старонка: http://avemey.com";
sh->Cell[0][2]->Href = "http://avemey.com";
sh->Cell[0][2]->HRefScreenTip = String("Клікніце па спасылцы") + sLineBreak +
String("(тэкст усплывальнай падказкі да спасылкі)");
//Аб'яднанае вочка
sh->MergeCells->AddRectXY(0, 2, 10, 2);
//Нататак
sh->Cell[0][3]->CellStyle = 5;
sh->Cell[0][3]->Data = "Ёсць нататак!";
sh->Cell[0][3]->Comment = "Тэкст нататка 1";
sh->Cell[0][3]->CommentAuthor = "Хтосьці 1 пакінуў нататку";
sh->Cell[0][3]->ShowComment = true;
sh->Cell[10][3]->CellStyle = 5;
sh->Cell[10][3]->Data = "Таксама нататак!";
sh->Cell[10][3]->Comment = "Тэкст нататкі 2";
sh->Cell[10][3]->CommentAuthor = "Хтосьці 2 пакінуў нататку";
sh->Cell[10][3]->ShowComment = true;
sh->Cell[10][3]->AlwaysShowComment = true;
sh->ColWidths[10] = 160; //Шырыня слупка
sh->Columns[10]->WidthMM = 40; //~40 мм
//Аб'яднаныя вочкі
sh->Cell[2][1]->CellStyle = 0;
sh->Cell[2][1]->Data = "Нейкі загаловак";
sh->MergeCells->AddRectXY(2, 1, 12, 1);
sh->Cell[0][6]->Data = String("Аб'яднаная") + sLineBreak +
String("вочка!");
sh->Cell[0][6]->CellStyle = 0;
sh->MergeCells->AddRectXY(0, 6, 3, 14);
sh->Cell[9][6]->Data = "№ стылю";
sh->Cell[9][6]->CellStyle = 2;
sh->Cell[10][6]->Data = "Прыклад стылю";
sh->Cell[10][6]->CellStyle = 2;
int j = 8;
for (i = 0; i < 7; i++)
{
sh->Cell[9][j]->Data = IntToStr(i) + String("-ы");
sh->Cell[10][j]->Data = "тэкст";
sh->Cell[10][j]->CellStyle = i;
j+=2;
}
sh->Columns[5]->WidthMM = 30;
sh->Columns[6]->WidthMM = 30;
sh->Cell[5][5]->Data = "Выраўноўванне";
sh->MergeCells->AddRectXY(5, 5, 6, 5);
sh->Cell[5][6]->Data = "Гарызанталь";
sh->Cell[5][6]->CellStyle = 2;
sh->Cell[6][6]->Data = "Вертыкаль";
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;
}
//капіяванне з 0 на 1-ую старонку
XMLSS->Sheets->Sheet[1]->Assign(sh);
//захаваць 0-ую і 1-ую старонку
int sheets[2] = {0, 1};
String sheetnames[2] = {String("Тэставая табліца"),
String("Тэставая табліца (копія)")};
//захаваць як excel xml
SaveXmlssToEXML(XMLSS, "save_test.xml", sheets, 2, sheetnames,
2, TextConverter, "UTF-8");
//Шлях павінен існаваць!
//захаваць як незапакаваны ods
SaveXmlssToODFSPath(XMLSS, "d:\\work\\ods_path\\", sheets,
2, sheetnames, 2, TextConverter, "UTF-8");
}
__finally
{
if (XMLSS != NULL)
{
XMLSS->Free();
}
}
}
|
Для ODS выкарыстоўваюцца формулы ў стылі A1 (=A1+B10-C12), для excel xml формулы запісваюцца ў стылі R1C1 (=R1C1+R10C2-R12C3).
Для канвертавання з аднаго стылю ў другі выкарыстоўваюцца функцыі ZEA1ToR1C1 і ZER1C1ToA1.
Выкарыстанне формул
|
Прыклад кода для Lazarus/Delphi
|
Прыклад кода для C++Builder-a
|
//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
//У дакуменце 2 старонкі
XMLSS.Sheets.Count := 2;
XMLSS.Sheets[0].Title := 'Формулы (ODF)';
//стылі
XMLSS.Styles.Count := 3;
//0 - для загалоўка (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 - стыль для табліцы
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 - стыль для загалоўка табліцы (тоўсты па цэнтры)
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
//колькасць радкоў і слупкоў
RowCount := 50;
ColCount := 20;
Cell[0, 0].CellStyle := 0;
Cell[0, 0].Data := 'Прыклад выкарыстання формул у 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 := '№';
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;
//капіяванне з 0 на 1-ую старонку
XMLSS.Sheets[1].Assign(XMLSS.Sheets[0]);
XMLSS.Sheets[1].Title := 'Формулы (Excel XML)';
//формулы для 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); //Атрымаць найменне слупка па яго нумары
Cell[i, 12].Formula := '=SUM(' + s +'3:' + s + '12)';
end;
end;
//формулы для 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;
//захаваць 0-ую і 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);
//У дакуменце 2 старонкі
XMLSS->Sheets->Count = 2;
//стылі
XMLSS->Styles->Count = 3;
//0 - для загалоўка (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 - стыль для табліцы
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 - стыль для загалоўка табліцы (тоўсты па цэнтры)
XMLSS->Styles->Items[2]->Assign(XMLSS->Styles->Items[1]);
XMLSS->Styles->Items[2]->Font->Style = TFontStyles() << fsBold;
XMLSS->Styles->Items[2]->Alignment->Horizontal = ZHCenter;
//колькасць радкоў і слупкоў
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 = "Прыклад выкарыстання формул у 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 = "№";
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;
}
}
//капіяванне з 0 на 1-ую старонку
XMLSS->Sheets->Sheet[1]->Assign(sh);
//формулы для 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); //Атрымаць найменне слупка па яго нумары
sh->Cell[i][12]->Formula = String("=SUM(") + s +
String("3:") + s + String("12)");
}
//формулы для 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)");
//захаваць 0-ую і 1-ую старонку
int sheets[2] = {0, 1};
String sheetnames[2] = {String("Формулы (ODF)"), String("Формулы (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();
}
}
}
|
Перакладае формулу са стылю A1 у стыль R1C1: function ZEA1ToR1C1(const formula: string; CurCol, CurRow: integer; options: integer; StartZero: boolean = true): string;
Перакладае формулу са стылю R1C1 у стыль A1: function ZER1C1ToA1(const formula: string; CurCol, CurRow: integer; options: integer; StartZero: boolean = true): string;
formula - тэкст формулы, CurRow - нумар радка вочка, CurCol - нумар слупка вочка, options - налады пераўтварэння,
StartZero - пры true рахунак радка/слупка пачынаецца з 0 (A1 = (CurCol=0; CurRow=0)).
Канвертаванне формул
|
Прыклад кода для Lazarus/Delphi
|
Прыклад кода для C++Builder-a
|
//Прыклад выкарыстання функцый ZEA1ToR1C1 і ZER1C1ToA1 для канвертавання формул.
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.
| //Прыклад выкарыстання функцый ZEA1ToR1C1 і ZER1C1ToA1 для канвертавання формул.
//---------------------------------------------------------------------------
#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;
}
//---------------------------------------------------------------------------
|
|
