Drinking Water Services Program Update - Fall Training
Stay informed with the latest updates on the Drinking Water Services Program. Topics include LCRR Service Line Inventory, BIL Implementation Timelines, and BIL Funding details. Learn about upcoming training sessions, funding allocations, and priorities for emerging contaminants. Get involved in lead service line replacement programs and support for disadvantaged communities."
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
Advanced Export To Excel Using Field-Map Scripting IFER Monitoring and Mapping Solutions Ltd. http://www.field-map.com
Export to Excel in Field-Map FieledMap Project Manager Tools / Export data Database Query Tool Query Result / Save to Excel file Firebird Desktop Export to Excel Scripting SaveToExcel function TExcelWrapper object
Export to Excel in Field-Map FieledMap Project Manager
Export to Excel in Field-Map FieledMap Project Manager
Export to Excel in Field-Map Database Query Tool
Export to Excel in Field-Map Database Query Tool
Export to Excel in Field-Map Database Query Tool
Export to Excel in Field-Map Firebird Desktop
Export to Excel in Field-Map Firebird Desktop
Export to Excel in scripting SaveToExcel function Function SaveToExcel( const Filename_ :string; const SheetName_ :string; const OpenExcel_,OverWriteSheet_ :boolean) :boolean TExcelWrapper class
Creating a new Excel file Constructor TExcelWrapper.Create Procedure CreateFile (const Filename_ :string)
Creating a new Excel file var xls :TExcelWrapper; FileName:string; Begin . . . {document preparation} xls:=TExcelWrapper.Create; try xls.CreateFile(FileName); . . . . . . finally xls.Free; end; End.
Opening an Excel file Procedure CloseAndSave( const OpenExcel_ :boolean); Procedure OpenInExcel; Procedure ShowExcelPreview;
Opening an Excel file var xls :TExcelWrapper; FileName:string; Begin . . . {document preparation} xls:=TExcelWrapper.Create; try xls.CreateFile(FileName); . . . xls.ShowExcelPreview; finally xls.Free; end; End.
Managing Sheets Procedure AddSheet(const SheetName_ :string; const OverwriteSheet_ :boolean) Procedure MakeSheetActive (const SheetName_ :string) Procedure ClearSheet (const SheetName_ :string)
Setting values Procedure SetValue( const row,col :integer; const Value :variant; const Format_,Borders_,BorderStyle_ :string)
Setting values xls.SetValue(Row,1,ValueAsInteger['IDPlots'],'DEC0','',''); xls.SetValue(Row,3,ValueAsString['DiameterClass'],'TXT','',''); xls.SetValue(Row,5,ValueAsString['Species'],'TXT','',''); xls.SetValue(Row,6,ValueAsInteger['NumberOfTrees'],'DEC','','');
Setting values Procedure SetValueAndMerge ( const row1,col1,row2,col2 :integer; const Value :variant; const Format_,Borders_,BorderStyle_ :string) Procedure SetFormula ( const row,col :integer; const Formula,Format_,FormulaReferenceStyle_ :string)
Getting values Function GetCellValue(const row,col :integer) :variant
Page setup SetPaperSize (const PaperSize_ :string) Property Landscape :boolean Procedure SetPrintMargins ( const Left_mm,Top_mm, Right_mm,Bottom_mm, Header_mm,Footer_mm :double)
Page setup Procedure SetPrintArea ( const row1,col1,row2,col2 :integer) Function RowCount:integer Function ColCount :integer
Page break Procedure InsertHPageBreak (const row :integer; const aGoesAfter :boolean) Procedure InsertVPageBreak (const col :integer; const aGoesAfter: boolean)
Title rows Procedure SetTitleRows (const row1,row2 :integer) Procedure SetTitleRowsAndCols (const row1,row2,col1,col2 :integer)
Header and footer Procedure SetHeader (const Header_ :string) Procedure SetFooter (const Footer_ :string) Procedure SetHeaderAndFooter (const HeaderLeft_,HeaderCenter_,HeaderRight_, FooterLeft_,FooterCenter_,FooterRight_ :string) Procedure SetFirstHeaderAndFooter (const HeaderLeft_,HeaderCenter_,HeaderRight_, FooterLeft_,FooterCenter_,FooterRight_ :string)
Header and footer xls.SetHeader('Number of trees'); xls.SetFirstHeaderAndFooter( '','','', format('Field-Map Project: %s',[Project.Name]),' ', '');
Header and footer codes Code Description &P Inserts the current page number. &N Inserts the total number of pages in a workbook. &D Inserts the current date. &T Inserts the current time. &Z Inserts the workbook file path. &F Inserts the name of a workbook file. &A Inserts the name of a worksheet. &"fontname" Prints the characters that follow using the specified font. &nn Prints the characters that follow using the specified font size. &P+number Inserts the page number plus the specified number. &P-number Inserts the page number minus the specified number. && Inserts an ampersand character.
Header and footer codes Code Description &B Turns bold on or off for the characters that follow. &I Turns italic on or off for the characters that follow. &U Turns underline on or off for the characters that follow. &E Turns double underline on or off for the characters that follow. &S Turns strikethrough on or off for the characters that follow. &Y Turns subscript on or off for the characters that follow. &X Turns superscript on or off for the characters that follow. &L Left aligns the characters that follow. &C Centers the characters that follow. &R Right aligns the characters that follow.
Header and footer xls.SetHeader('&L&I&A'); xls.SetFirstHeaderAndFooter( '','','', format('&L&"Calibri,Italic"&9Field-Map Project: %s',[Project.Name]),' ', '&I&8Page&P');
Table of content Procedure CreateHeaderOfTableContent Procedure CreateTableOfContent (lst :TStringListWrapper) {list of task caption|sheet name} Procedure AddBackToTableOfContent (const Row_,RowNoInContent_,nCol_ :integer)
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 1','By diameter class and species 1'])); lst.Add(format('%s|%s',['Living trees by diameter class and species 2','By diameter class and species 2'])); xls.CreateTableOfContent(lst);
Format cells Procedure FormatCells ( const row1,col1,row2,col2 :integer; const FontSize_ :integer; const FontStyle_ :string; const DecimalPlaces_ :integer; const Indent_ :boolean; const HorizontalAlignment_,VerticalAlignment_ :string)
User formats Procedure AddUserFormat( const FormatName_ :string; const FontSize_ :integer; const FontStyle_ :string; {BOLD,ITALIC,UNDERLINE} const FontColor_ :string; {RGB} const DecimalPlaces_ :integer; const Indent_ :integer; const HorizontalAlignment_ :string; {LEFT,RIGHT,CENTER} const VerticalAlignment_ :string; {TOP,BOTTOM,CENTER} const WrapText_ :boolean);
User 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,'C ENTER','CENTER',false); xls.AddUserFormat('TXT',8,'','0,0,0',0,1,'LEFT',' CENTER',false); xls.AddUserFormat('DEC0',8,'','0,0,0',0,0,'RIGHT' ,'CENTER',false); xls.AddUserFormat('ColCaption',8,'BOLD','0,0,0',0 ,0,'CENTER','CENTER',false);
Default formats DefaultCentered DefaultIndented DefaultWrapped DefaultCenteredWrapped Bold BoldCentered ColCaption MainTitle ColCaptionItalic Dec0, Dec1, Dec2, Dec3, Dec4, Dec5, Dec6
Draw borders procedure DrawBorders ( const row1,col1,row2,col2 :integer; const Borders_,BorderStyle_ :string); {LEFT,RIGHT,TOP,BOTTOM}
Format cells Procedure FillColor ( const row1,col1,row2,col2 :integer; const Color_ :integer) Procedure MergeCells (const row1,col1,row2,col2 :integer) Procedure SetTextOrientation ( const row1,col1,row2,col2 :integer; const Degrees_ :integer) Procedure Align ( const row1,col1,row2,col2 :integer; const HorizontalAlignment_,VerticalAlignment_ :string) {LEFT,RIGHT,CENTER; TOP,BOTTOM,CENTER}
Format cells procedure SetOptimalColWidth ( const col1,col2 :integer; const Adjustment_ :double{e.g. 1.05}); Procedure AutofitCol ( const col1,col2 :integer; const ignoreStrings :boolean; const adjustment :double) Procedure AutofitRow ( const row1,row2 :integer; const keepHeightAutomatic :boolean; const adjustment :double)
Format cells Procedure SetColWidth ( const col1,col2 :integer; const Width_ :integer); Procedure SetRowHeight ( const row1,row2 :integer; const Height_ :integer); Procedure SetColWidth_mm ( const col1,col2 :integer; const Width_mm_ :double) Procedure SetRowHeight_mm ( const row1,row2 :integer; const Height_mm_ :double)
Zebra with tab do while not eof do begin xls.SetValue(Row,1,ValueAsInteger['IDPlots'],'DEC0_small','',''); xls.SetValue(Row,2,ValueAsInteger['IDDiameterClass5'],'DEC0_small','',''); xls.SetValue(Row,3,ValueAsString['DiameterClass5'],'TXT','',''); xls.SetValue(Row,4,ValueAsInteger['IDSpecies'],'DEC0_small','',''); xls.SetValue(Row,5,ValueAsString['Species'],'TXT','',''); xls.SetValue(Row,6,ValueAsInteger['NumberOfTrees'],'DEC0_small','','') //setting zebra if (Row mod 2)=0 then xls.FillColor(Row,1,Row,Cols,clBtnFace); inc(Row); Next; end;
