Creating professional Excel Spreadsheets from basic data utilising

Creating professional Excel Spreadsheets from basic data, utilising Database-to-Excel maps. Using the new functionality offered with the two new Version 7 commands. (IMPXLDBF & CVTDBFXLSX)

Scenario: � There is a basic Excel spreadsheet. Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX)

Scenario: Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) � From the basic Excel Spreadsheet, the following detail worksheets will be produced: -

Scenario: Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) � From the basic Excel Spreadsheet, the following summary worksheet will be produced: -

Scenario: Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) � The Excel Spreadsheet will be produced, with the following : � Importing of data from an existing basic spreadsheet, combining all worksheets into one database file. � A Database-to-Excel Map to produce the Detail Spreadsheet (no Map is required for the Summary Worksheet). � Combining existing database fields to create a new database file. � Dynamic use of images. � Footer showing Date, Worksheet Name and Page Numbers, utilising Excel placeholders. � Distinctive Column Headings. � Styling, including the reformatting of data and numeric field editing. � Zebra formatting to make each row distinctive. � Sorting, keeping the zebra formatting. � Each different Make of Car in a separate worksheet. � Summary worksheet utilising new Version 7 report breaks and summaries. � Freezing of columns (new to Version 7). � Overall professional finish.

Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) The creation of the Excel Spreadsheets, are carried out in stages. IMPXLDBF Reference File MOTOR_IN RPG Program MOTOR_IN MOTOR_OUT CVTDBFXLSX MOTOR_OUT IMPXLDBF is used CVTDBFXLSX An RPG program is to used import data is used to to create detail Excel from. Summary the basic combine the Worksheets, imported Excel Worksheet, data Spreadsheet. with using existing the data The setup reference and combined the file is used as the images database and file. the basis to create zebra Report formatting. Breaks the new database and Summaries file, utilised. combining are the worksheets into one member. Click to to advance.

Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) Creating the Reference File � Physical File (XYZ_REF) has to be created. R MOTOR_R 1 MAKE# MODEL# REGIST# MILEAGE# COLOUR# ENGSIZE# FUELTYP# K MAKE# K MODEL# 20 20 10 7 10 5 10 0 � The IMPXLDBF command can now be built to import the data from the Excel Spreadsheet.

Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) Importing the Excel Spreadsheet. � The Import Excel to Database File (IMPXLDBF) command is built as follows: - � COLNAMING is set to *TABLE and NAMING to *SYS, to allow the reference file to be used as the basis. � FIRSTROW is set to 2, so the column headings are not imported.

Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) Importing the Excel Spreadsheet. � The data has been imported from the Excel Spreadsheet, into the database file MOTOR_IN. � The data now needs to be run through an RPG simple program to combine with existing data and setup dynamic images and zebra formatting.

Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) Combining imported data with existing data. � The existing database contains the following data, keyed by REGISTRATION.

Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) Combining imported data with existing data. � The new database file created, contains consolidated data and fields for the images and zebra formatting. � This database file is used with the CVTDBFXLSX commands. � A Database to Excel Map need creating for the Detail Spreadsheet.

Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) How the Detail Database-to-Excel map will be constructed. IMAGES TEXTS Parent Row Group FIELDS NEW_MAKE Car Make Company Logo Model Registrat. Mileage Colour Engine Size Fuel Type Car Owner Sale Value Contact Number MODEL# REGIST# Sub Row Group (XYZDETAIL) MILEAGE# COLOUR# OWNER# ENGSIZE# FUELTY# ESTVALUE# CONTACT NO# ZEB#

Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) Creating the Detail Database-to-Excel map. � The Database-to-Excel maps for the Detail Spreadsheet needs creating. � WRKDBFXL is used followed by F 6, or CRTDBFXL is used to the create the Detail Map @XYZ_MAP.

Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) Creating the Detail Database-to-Excel map. � A Parent Row Group is added. � This Row Group contains the dynamic images, the column headings and the option for separate worksheets, for each Car Make. � Enter option ‘ 8’ against the @XYZMAP to add the Parent Row Group.

Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) Creating the Detail Database-to-Excel map. � F 6 is used to create the Parent Row Group.

Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) Creating the Detail Database-to-Excel map. � Setting up the Parent Row Group is carried on two screens. � The Row Group is named NEW_MAKE. � A new Rule Group is setup to be effective on change of Car Make (MAKE#).

Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) Creating the Detail Database-to-Excel map. � The second screen of the Parent Row Group setup, allows an event to be actioned, on change of MAKE#. � Action is set to *NEWSHEET. � The new sheets will be named, as the variable MAKE#.

Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) Creating the Detail Database-to-Excel map. � Once the Parent Row Group has been created, cells need adding by enter ‘ 9’ against the Row Group.

Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) Creating the Detail Database-to-Excel map. � The cells to be added to the Parent Row Group, are listed below: � Row � 1 � 5 � 5 � 5 Col A E A B C D E F G H I Content *IMAGE *TEXT *TEXT *TEXT Column name or constant text *PATH <: IMGPATH 1#: > *PATH /home/lordg/xyzmotors. jpg 'Model' 'Registration' 'Mileage' 'Colour' 'Engine Size' 'Fuel Type' 'Car Owner‘ 'Contact No. ' 'Sale Value' � Note that the cell for 1 -A is an image and the scale is set to 0. 60 x 0. 60. � The variable <: IMGPATH 1#: > will add an image to the header, dependant on the image path stored in the database file (MOTOUR_OUT).

Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) Creating the Detail Database-to-Excel map. � A Sub-Row Group is now created linked to the Parent. � To create Sub-Row Group, enter ‘ 8’ against the Parent Row Group (NEW_MAKE), followed by F 6. � The Row-Group is named XYZDETAIL.

Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) Creating the Detail Database-to-Excel map. � Once the Sub-Row Group has been created the cells are added, as below: � Row Col � 1 A � 1 B � 1 C � 1 D � 1 E � 1 F � 1 G � 1 H � 1 J � 1 I Content *COLUMN *COLUMN *COLUMN Column name or constant text MODEL# REGIST# MILEAGE# COLOUR# ENGSIZE# FUELTYP# OWNER# CONTACTNO# ZEB# ESTVALUE# � The CVTDBFXLSX command can now built to create the Detail Excel Spreadsheet.

Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) Using CVTDBFXLSX to create the Detail Excel Spreadsheet. � The command is initially built with no formatting. � At this point no applied or conditional formatting has been used. � The header has been set to *NONE � Excel Map @XYZ_MAP has been used.

Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) Using CVTDBFXLSX to create the Detail Excel Spreadsheet. � The spreadsheet has been created, as shown below (worksheet 1 of 4) : - � The Car Make logo image has been dynamically placed at the top of the worksheet along with the Company Logo in the centre of the sheet. � There is one cell (column J), that will have to be hidden, as this is used to apply Zebra formatting.

Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) Using CVTDBFXLSX to create the Detail Excel Spreadsheet. � Formatting the Detail Spreadsheet. Ø Column widths are applied as follows: � Ø Column B D E F G H I Width 16 11 12 11 24 18 14 The following styles need to be created : � Style NEW_MAKE Font Size 12. 00 Text Colour *WHITE Bg Colour *LIGHTBLUE Hz Align. *CENTER Vr. Align. *CENTER Border (Bottom) XYZDETAIL HIDDENF ZEBRA 1 ZEBRA 2 FMTMGE 1 FMTMGE 2 FMTESV 1 FMTESV 2 10. 00 12. 00 *AUTO *BLACK *NONE *PALEBLUE *AQUA *PALEBLUE *CENTER *GENERAL *GENERAL *CENTER *BOTTOM *CENTER *CENTER *NONE *NONE Note: Height *THIN *BLACK 2 17 *AUTOFIT *AUTOFIT FMTMGE 1 & 2 have a Number Format of *ACCOUNTING, 0 decimal places, thousand separator and no currency symbol. FMTESV 1 & 2 have a Number Format of *ACCOUNTING, 0 decimal places, thousand separator and an’£’ currency symbol.

Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) Using CVTDBFXLSX to create the Detail Excel Spreadsheet. � The CVTDBFXLSX command is changed to apply formatting and set column widths.

Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) Using CVTDBFXLSX to create the Detail Excel Spreadsheet. � With formatting and columns widths applied, the following is produced : - � To apply zebra formatting of the rows, conditional formatting is applied using column J (now hidden).

Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) Using CVTDBFXLSX to create the Detail Excel Spreadsheet. � The CVTDBFXLSX command is changed to include the Conditional Formatting. � The EXCEL and XLSPRINT parameters are added to create the footer utilising Excel placeholders.

Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) Using CVTDBFXLSX to create the Detail Excel Spreadsheet. � The EXCEL and XLSPRINT parameters are now applied. � The command for creating the Detail Excel Spreadsheet is now complete.

Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) Using CVTDBFXLSX to create the Detail Excel Spreadsheet. � The completed Detail Spreadsheet is shown below: -

Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) Using CVTDBFXLSX to create the Summary Spreadsheet. � The command is initially built with no formatting. � The field containing the dynamic path of the images has been excluded. � The Header parameter has been used to Freeze the first row containing the column headings.

Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) Using CVTDBFXLSX to create the Summary Spreadsheet. � The spreadsheet created, with no formatting is shown below.

Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) Using CVTDBFXLSX to create the Summary Spreadsheet. � Formatting the Summary Spreadsheet. Ø Column widths are applied as follows: � Column B D E F G J Width 15 10 10 8 8 10 Ø The following styles are created : � Style SUMMEAD SUMMHEADC ZEBRA 3 ZEBRA 4 ZEBM 1 ZEBM 2 SUMM_LINE Note: Font Size 10. 00 10. 00 Text Colour *BLACK *AUTO *BLACK Bg Colour *TAN Hz Align. *GENERAL *CENTER *LIGHTYELLOW *GENERAL *YELLOWWHITE *GENERAL *PALEBLUE *GENERAL Vr. Align. *BOTTOM *CENTER *BOTTOM Border *NONE *NONE Height *AUTOFIT *AUTOFIT ZEBM 1 & 2 have a Number Format of *ACCOUNTING, 0 decimal places, thousand separator and no currency symbol.

Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) Using CVTDBFXLSX to create the Summary Spreadsheet. � The command now has styles applied, conditional formatting applied and the column widths set.

Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) Using CVTDBFXLSX to create the Summary Spreadsheet. � The spreadsheet created, now contains style formatting. � Column K is now hidden, as it does not need to be shown. � Now the EXCEL and XLSPRINT parameters are used to name the worksheet, set the margins and format the page footer.

Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) Using CVTDBFXLSX to create the Summary Spreadsheet. � The command now uses the EXCEL and XLSPRINT parameters.

Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) Using CVTDBFXLSX to create the Summary Spreadsheet. � The worksheet is now named and the page layout has been setup. � Finally, the new functionality that version 7 offers (RPTSMRY and RPTBRKS) can be applied.

Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) Using CVTDBFXLSX to create the Summary Spreadsheet. � The following is added to the command. � These additional parameters, allow sections of the spreadsheet to be collapsed and expanded.

Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) Using CVTDBFXLSX to create the Summary Spreadsheet. � The worksheet now contains report breaks and summaries.

Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) Using CVTDBFXLSX to create the Summary Spreadsheet. � Clicking ‘-’ to collapse the groups will have the following result: -

Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX) Using CVTDBFXLSX to create the Summary Spreadsheet. � Clicking the ‘+’ to expand groups will have the following result: -

Appendix. � Colour Chart for use when styling. Creating Excel workbooks using new Version 7 commands (IMPXLDBF & CVTDBFXLSX)