4.6.6 ExcelWrapperExample
Export to Excel Example
This complex example is created based on an Field-Map inventory project. Tasks that calculate tree numbers in 4 different ways are used to illustrate using the TExcelWrapper class.
This example generates an excel file (CloseAndSave procedure) with 5 worksheets. Active content is created on first sheet, formatted query results on the following sheets.
If ShowExcelPreview procedure is used (rather than CloseAndSave procedure), results can be previewed prior to exporting to en excel file or pdf.
The code of the example is as follows:
Var
xls :TExcelWrapper;
tab : TFieldMapTableScriptWrapper;
lst: TStringListWrapper;
query, FileName:string;
Cols, Rows, Col, Row, SumRows, NewPlotRow, i:integer;
LastPlot, LastDiameterClass:integer;
Begin
{------------------data preparation-------------------}
query:='SELECT t.IDPlots , qd.ID AS IDDiameterClass5, qd.Value1 AS DiameterClass5, qs.ID AS IDSpecies, qs.value1 AS Species, SUM(ROUND(t.ExpansionFactor*t.TreeNumber)) AS NumberOfTrees'
+' FROM TREES_2013 t'
+' RIGHT JOIN QDiameterClass5 qd ON qd.ID=t.DiameterClass5'
+' RIGHT JOIN QSpecies qs ON qs.ID=t.Species '
+' WHERE t.deadtree=100 '
+' GROUP BY t.IDPlots , qd.ID, qd.value1 , qs.ID , qs.value1 ';
tab:=Project.GetQueryResult(query);
{------------------document preparation-------------------}
FileName:=ProjectDir+'ExcelTasks\TreeNumbers';
xls:=TExcelWrapper.Create;
try
xls.CreateFile(FileName);
//xls.SetPaperSize('A4');
{------------------Formats-------------------}
xls.AddUserFormat('Title_h1',14,'BOLD','0,0,0',0,0,'CENTER','CENTER',false);
xls.AddUserFormat('Title_h2',12,'','0,0,0',0,0,'CENTER','CENTER',false);
xls.AddUserFormat('TXT',8,'','0,0,0',0,1,'LEFT','CENTER',false);
xls.AddUserFormat('DEC0_small',8,'','0,0,0',0,0,'RIGHT','CENTER',false);
xls.AddUserFormat('ColCaption_c',8,'BOLD','0,0,0',0,0,'CENTER','CENTER',false);
{------------------Sheet: Content -------------------}
xls.AddSheet('',true);
{Create table of content}
xls.CreateHeaderOfTableContent;
lst:=TStringListWrapper.Create;
lst.Add(format('%s|%s',['Living and dead trees by species (DBH above 20 cm)','By Species, DBH>20']));
lst.Add(format('%s|%s',['Living and dead trees by species (DBH above 7 cm)','By Species, DBH>7']));
lst.Add(format('%s|%s',['Living and dead trees by species (with trees from regeneration)','By species with regeneration']));
lst.Add(format('%s|%s',['Living trees by diameter class and species','By diameter class and species']));
xls.CreateTableOfContent(lst);
{------------------Sheet: Living and dead trees by species (DBH above 20 cm)-------------------}
xls.AddSheet('By Species, DBH>20',true);
Cols:=9;
Row:=1;
{hyperlink to Content}
xls.AddBackToTableOfContent(Row,2,Cols);
Inc(Row);
{Title}
xls.SetValueAndMerge(Row,1,Row, Cols,'Number of living and dead trees by species (DBH above 20 cm)','Title_h1','','');
inc(Row); inc(Row);
xls.SetHeader(format('&L&"Calibri,Italic"&9Field-Map Project: %s',[Project.Name]));
{managing formats}
{setting cell values}
{etc....}
{------------------Sheet: Living and dead trees by species (DBH above 7 cm)-------------------}
xls.AddSheet('By Species, DBH>7',true);
Cols:=9;
Row:=1;
{hyperlink to Content}
xls.AddBackToTableOfContent(Row,3,Cols);
Inc(Row);
{Title}
xls.SetValueAndMerge(Row,1,Row, Cols,'Number of living and dead trees by species (DBH above 7 cm)','Title_h1','','');
inc(Row); inc(Row);
xls.SetHeader(format('&L&"Calibri,Italic"&9Field-Map Project: %s',[Project.Name]));
{managing formats}
{setting cell values}
{etc....}
{------------------Sheet: Living and dead trees by species (with trees from regeneration)-------------------}
xls.AddSheet('By species with regeneration',true);
Cols:=9;
Row:=1;
{hyperlink to Content}
xls.AddBackToTableOfContent(Row,4,Cols);
Inc(Row);
{Title}
xls.SetValueAndMerge(Row,1,Row, Cols,'Number of living and dead trees by species (with trees from regeneration)','Title_h1','','');
inc(Row); inc(Row);
xls.SetHeader(format('&L&"Calibri,Italic"&9Field-Map Project: %s',[Project.Name]));
{managing formats}
{setting cell values}
{etc....}
{------------------Sheet: Number of living trees by diameter class (5 cm) and species (DBH above 7 cm)-------------------}
{add sheet}
xls.AddSheet('By diameter class and species',true);
Cols:=6;
Row:=1;
{hyperlink to Content}
xls.AddBackToTableOfContent(Row,5,Cols);
Inc(Row);
{Title}
xls.SetValueAndMerge(Row,1,Row, Cols,'Number of living trees by diameter class and species','Title_h1','','');
inc(Row);
xls.SetValueAndMerge(Row,1,Row, Cols,'(5 cm diameter classes; DBH above 7 cm)','Title_h2','','');
inc(Row); inc(Row);
{header and footer}
xls.SetHeader(format('&L&"Calibri,Italic"&9Field-Map Project: %s',[Project.Name]));
xls.SetFirstHeaderAndFooter('','','','&I&8Project... ',' ', '&I&8Strana &P');
{Title rows (column headers)}
xls.SetValue(Row,1,'Plot number','ColCaption_c','','');
xls.SetValue(Row,2,'Diamter Class ID','ColCaption_c','','');
xls.SetValue(Row,3,'Diamter Class','ColCaption_c','','');
xls.SetValue(Row,4,'Species ID','ColCaption_c','','');
xls.SetValue(Row,5,'Species','ColCaption_c','','');
xls.SetValue(Row,6,'Number of trees','ColCaption_c','','');
xls.DrawBorders(Row,1,Row,Cols,'BOTTOM','THIN');
xls.SetTitleRows(Row,Row+1);
inc(Row);
{Listing data}
tab.First;
i:=1;
LastPlot:=0; LastDiameterClass:=0; NewPlotRow:=Row;
with tab do
while not eof do begin
if (ValueAsInteger['IDPlots']<>lastPlot) then begin {next plot}
if LastPlot<>0 then begin {total per plot}
xls.SetValue(Row, 4 , format('Number of trees per plot ID %d: ',[lastPlot]), 'TXT','','');
SumRows:=Row-NewPlotRow;
xls.SetFormula(Row, Cols ,format('=SUM(R[-%d]C:R[-1]C)',[SumRows]),'DEC0_small','R1C1');
xls.DrawBorders(Row,1,Row,Cols,'TOP','HAIR');
xls.DrawBorders(Row,1,Row,Cols,'BOTTOM','THIN');
inc(Row);
end;
xls.SetValue(Row,1,ValueAsInteger['IDPlots'],'DEC0_small','','');
//xls.DrawBorders(Row,1,Row,Cols,'TOP','THIN');
NewPlotRow:=Row;
end;
if ((ValueAsInteger['IDDiameterClass5']<>lastDiameterClass) or (ValueAsInteger['IDPlots']<>lastPlot)) then begin {next diameter class}
xls.SetValue(Row,2,ValueAsInteger['IDDiameterClass5'],'DEC0_small','','');
xls.SetValue(Row,3,ValueAsString['DiameterClass5'],'TXT','','');
//xls.DrawBorders(Row,2,Row,Cols,'TOP','THIN');
end;
{detail}
xls.SetValue(Row,4,ValueAsInteger['IDSpecies'],'DEC0_small','','');
xls.SetValue(Row,5,ValueAsString['Species'],'TXT','','');
xls.SetValue(Row,6,ValueAsInteger['NumberOfTrees'],'DEC0_small','','')
inc(Row);
lastPlot:=ValueAsInteger['IDPlots'];
lastDiameterClass:=ValueAsInteger['IDDiameterClass5'];
Next;
end;
{total per last plot}
xls.SetValue(Row, 4 , format('Number of trees per plot ID %d: ',[lastPlot]), 'TXT','','');
SumRows:=Row-NewPlotRow;
xls.SetFormula(Row, Cols ,format('=SUM(R[-%d]C:R[-1]C)',[SumRows]),'DEC0_small','R1C1');
xls.DrawBorders(Row,1,Row,Cols,'TOP','HAIR');
xls.DrawBorders(Row,1,Row,Cols,'BOTTOM','THIN');
xls.SetColWidth_mm(1,1,20.00);
xls.SetColWidth_mm(2,3,30.00);
xls.SetColWidth_mm(4,4,20.00);
xls.SetColWidth_mm(5,6,30.00);
//xls.AutofitRow(Row,Row,false,1.05);
//xls.SetPrintMargins(10,19,10,19,8,8);
{------------------set active sheet------------------}
xls.MakeSheetActive('Content');
{------------------ save, open, preview ------------------}
try
xls.CloseAndSave(true); // save and open file in Excel
{or
xls.ShowExcelPreview('OpenInExcel=0;SaveToExcelFile=0'); // show thumbnail preview
or
xls.CloseAndSave(false); // save file in Excel
xls.OpenInExcel(FileName); // open file in Excel
}
except
ShowError(format('Error on saving file %s. The file is probably open.',[FileName]));
end;
finally
xls.Free;
CloseAndFreeTable(tab);
end;
end.