Excel Templates
Rather than just providing a simple dump of your data, dbFront allows you to export your data to an existing Excel (xlsx) template file. This allows you to create and use preformatted Excel worksheets, including formulas.
Once you select the output format of Excel then you will have the option to specify a template file. Your dbFront install automatically includes at least one sample Excel template.
Once the data is inserted, then dbFront will automatically resize the columns to fit the newly inserted data.
Custom Excel Templates
dbFront looks for Excel templates on the server.
- The default folder is:
C:\Data\dbFront\Excel\. - The template files must have the extension .xlsx.
Selected Range(s)
If you select the option Sample-Template, then you should set the range to B5:K8. This tells dbFront where the header and data should be inserted. If no range is specified, then dbFront will insert your data at the bottom of the first worksheet. If a range is specified, then dbFront automatically expands the number of columns and rows as needed using the selected styles as pattern for the additional rows and columns.
Table footers should not be included in the specified range. dbFront will automatically push footers down to make space for the inserted data.
Multiple Ranges can be specified to account for the multiple result sets that a stored procedure can return. At one client, 32 different result sets are positioned in various locations within a single Excel export.
Selected Worksheet(s)
It is possible to specify a sheet name and range, e.g. $’data table’!B5:K8
If the Worksheet does not exist, then dbFront will automatically create it.
Formula Columns
If a table range is specified, then dbFront will automatically skip over formula columns. This makes it easy to include calculations in the spreadsheet.
Uses
You can configure the Excel export directly within a Quick Report, a Run Procedure button, or from the Procedure API.