Advanced Export To Excel Using FieldMap Scripting IFER

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 Fieled. Map Project Manager Tools / Export data Database Query Tool Query Result / Save to Excel file Firebird Desktop Export to Excel Scripting Save. To. Excel function TExcel. Wrapper object

Export to Excel in Field-Map Fieled. Map Project Manager

Export to Excel in Field-Map Fieled. Map 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 Field-Map

Export to Excel in scripting Ø Save. To. Excel function Function Save. To. Excel( const Filename_ : string; const Sheet. Name_ : string; const Open. Excel_, Over. Write. Sheet_ : boolean) : boolean Ø TExcel. Wrapper class

Creating a new Excel file Constructor TExcel. Wrapper. Create Procedure Create. File (const Filename_ : string)

Creating a new Excel file var xls : TExcel. Wrapper; File. Name: string; Begin. . . {document preparation} xls: =TExcel. Wrapper. Create; try xls. Create. File(File. Name); . . . finally xls. Free; end; End.

Opening an Excel file Procedure Close. And. Save( const Open. Excel_ : boolean); Procedure Open. In. Excel; Procedure Show. Excel. Preview;

Opening an Excel file var xls : TExcel. Wrapper; File. Name: string; Begin. . . {document preparation} xls: =TExcel. Wrapper. Create; try xls. Create. File(File. Name); . . . xls. Show. Excel. Preview; finally xls. Free; end; End.

Managing Sheets Procedure Add. Sheet(const Sheet. Name_ : string; const Overwrite. Sheet_ : boolean) Procedure Make. Sheet. Active (const Sheet. Name_ : string) Procedure Clear. Sheet (const Sheet. Name_ : string)

Setting values Procedure Set. Value( const row, col : integer; const Value : variant; const Format_, Borders_, Border. Style_ : string)

Setting values xls. Set. Value(Row, 1, Value. As. Integer['IDPlots'], 'DEC 0', ''); xls. Set. Value(Row, 3, Value. As. String['Diameter. Class'], 'TXT', ''); xls. Set. Value(Row, 5, Value. As. String['Species'], 'TXT', ''); xls. Set. Value(Row, 6, Value. As. Integer['Number. Of. Trees'], 'DEC', '');

Setting values Procedure Set. Value. And. Merge ( const row 1, col 1, row 2, col 2 : integer; const Value : variant; const Format_, Borders_, Border. Style_ : string) Procedure Set. Formula ( const row, col : integer; const Formula, Format_, Formula. Reference. Style_ : string)

Getting values Function Get. Cell. Value(const row, col : integer) : variant

Page setup Set. Paper. Size (const Paper. Size_ : string) Property Landscape : boolean Procedure Set. Print. Margins ( const Left_mm, Top_mm, Right_mm, Bottom_mm, Header_mm, Footer_mm : double)

Page setup Procedure Set. Print. Area ( const row 1, col 1, row 2, col 2 : integer) Function Row. Count: integer Function Col. Count : integer

Page break Procedure Insert. HPage. Break (const row : integer; const a. Goes. After : boolean) Procedure Insert. VPage. Break (const col : integer; const a. Goes. After: boolean)

Title rows Procedure Set. Title. Rows (const row 1, row 2 : integer) Procedure Set. Title. Rows. And. Cols (const row 1, row 2, col 1, col 2 : integer)

Header and footer Procedure Set. Header (const Header_ : string) Procedure Set. Footer (const Footer_ : string) Procedure Set. Header. And. Footer (const Header. Left_, Header. Center_, Header. Right_, Footer. Left_, Footer. Center_, Footer. Right_ : string) Procedure Set. First. Header. And. Footer (const Header. Left_, Header. Center_, Header. Right_, Footer. Left_, Footer. Center_, Footer. Right_ : string)

Header and footer xls. Set. Header('Number of trees'); xls. Set. First. Header. And. Footer( '', '', 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. Set. Header('&L&I&A'); xls. Set. First. Header. And. Footer( '', '', format('&L&"Calibri, Italic"&9 Field-Map Project: %s', [Project. Name]), ' ', '&I&8 Page&P');

Table of content Procedure Create. Header. Of. Table. Content Procedure Create. Table. Of. Content (lst : TString. List. Wrapper) {list of task caption|sheet name} Procedure Add. Back. To. Table. Of. Content (const Row_, Row. No. In. Content_, n. Col_ : integer)

Table of content xls. Create. Header. Of. Table. Content; lst: =TString. List. Wrapper. 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. Create. Table. Of. Content(lst);

Format cells Procedure Format. Cells ( const row 1, col 1, row 2, col 2 : integer; const Font. Size_ : integer; const Font. Style_ : string; const Decimal. Places_ : integer; const Indent_ : boolean; const Horizontal. Alignment_, Vertical. Alignment_ : string)

User formats Procedure Add. User. Format( const Format. Name_ : string; const Font. Size_ : integer; const Font. Style_ : string; {BOLD, ITALIC, UNDERLINE} const Font. Color_ : string; {RGB} const Decimal. Places_ : integer; const Indent_ : integer; const Horizontal. Alignment_ : string; {LEFT, RIGHT, CENTER} const Vertical. Alignment_ : string; {TOP, BOTTOM, CENTER} const Wrap. Text_ : boolean);

User formats xls. Add. User. Format('Title_h 1', 14, 'BOLD', '0, 0, 0', 0, 0, 'CENTER', false); xls. Add. User. Format('Title_h 2', 12, '', '0, 0, 0', 0, 0, 'C ENTER', 'CENTER', false); xls. Add. User. Format('TXT', 8, '', '0, 0, 0', 0, 1, 'LEFT', ' CENTER', false); xls. Add. User. Format('DEC 0', 8, '', '0, 0, 0', 0, 0, 'RIGHT', 'CENTER', false); xls. Add. User. Format('Col. Caption', 8, 'BOLD', '0, 0, 0', 0, 0, 'CENTER', false);

Default formats Default. Centered Default. Indented Default. Wrapped Default. Centered. Wrapped Bold. Centered Col. Caption Main. Title Col. Caption. Italic Dec 0, Dec 1, Dec 2, Dec 3, Dec 4, Dec 5, Dec 6

Draw borders procedure Draw. Borders ( const row 1, col 1, row 2, col 2 : integer; const Borders_, Border. Style_ : string); {LEFT, RIGHT, TOP, BOTTOM}

Format cells Procedure Fill. Color ( const row 1, col 1, row 2, col 2 : integer; const Color_ : integer) Procedure Merge. Cells (const row 1, col 1, row 2, col 2 : integer) Procedure Set. Text. Orientation ( const row 1, col 1, row 2, col 2 : integer; const Degrees_ : integer) Procedure Align ( const row 1, col 1, row 2, col 2 : integer; const Horizontal. Alignment_, Vertical. Alignment_ : string) {LEFT, RIGHT, CENTER; TOP, BOTTOM, CENTER}

Format cells procedure Set. Optimal. Col. Width ( const col 1, col 2 : integer; const Adjustment_ : double{e. g. 1. 05}); Procedure Autofit. Col ( const col 1, col 2 : integer; const ignore. Strings : boolean; const adjustment : double) Procedure Autofit. Row ( const row 1, row 2 : integer; const keep. Height. Automatic : boolean; const adjustment : double)

Format cells Procedure Set. Col. Width ( const col 1, col 2 : integer; const Width_ : integer); Procedure Set. Row. Height ( const row 1, row 2 : integer; const Height_ : integer); Procedure Set. Col. Width_mm ( const col 1, col 2 : integer; const Width_mm_ : double) Procedure Set. Row. Height_mm const row 1, row 2 : integer; const Height_mm_ : double) (

Zebra with tab do while not eof do begin xls. Set. Value(Row, 1, Value. As. Integer['IDPlots'], 'DEC 0_small', ''); xls. Set. Value(Row, 2, Value. As. Integer['IDDiameter. Class 5'], 'DEC 0_small', ''); xls. Set. Value(Row, 3, Value. As. String['Diameter. Class 5'], 'TXT', ''); xls. Set. Value(Row, 4, Value. As. Integer['IDSpecies'], 'DEC 0_small', ''); xls. Set. Value(Row, 5, Value. As. String['Species'], 'TXT', ''); xls. Set. Value(Row, 6, Value. As. Integer['Number. Of. Trees'], 'DEC 0_small', '') //setting zebra if (Row mod 2)=0 then xls. Fill. Color(Row, 1, Row, Cols, cl. Btn. Face); inc(Row); Next; end;

Thank you for your attention