Creating a Microsoft Excel Spreadsheet Template to use with RPG2SQL

 

When designing a new spreadsheet in Microsoft Excel that RPG2SQL will populate or read:

1. Decide what type of data you need to capture:
   • Detailed data records - Detailed information is required. Multiple data rows will be added to the spreadsheet. 
   • Fixed or changeable column headings - Can be hard-coded in the spreadsheet template and formatted with Excel prior to using the template and then be updated with the RPG2SQL code.

     For example: Setting dates or month names at the top of each column of data and shifting the values depending on which month is current.

   • Individual numeric or character data values - Field values to fill in when generating data into the spreadsheet.

     For example: Column headings, a part number, customer name, rep name, and so on.

2. Create the new spreadsheet file and add:

   Note: Always leave the first row of the spreadsheet template blank, unless you want to use row one for column names. Start placing data in row 2 instead.

   • As many sheets as required.

     For example: You may want to create a new spreadsheet template with worksheets for each month. 

   • Any static fields you need.
   • Any formulas that you would like to use to summarize data later when RPG2SQL adds data to the spreadsheet.
3. For detailed record information, in Excel, go to Insert > Name > Define and create a named range. Call the named range something like: JulyDetail or DetailInfo.

   RPG2SQL will later user this named range to add or clear detail records within the spreadsheet. Think of a named range as a pre-defined detail area or single cell within a spreadsheet. A spreadsheet template can have multiple named ranges across multiple worksheets.