Professional - Open - Reports …
  • RSS Feed

Last modified on 5/16/2014 6:56 PM by User.

Professional - Open - Reports - Creating Excel Templates

Keywords: Excel templates, creating a template, creating an Excel template

Excel templates are “designs” and/or “layouts” for reports that may be applied to different standard database reports in EQuIS. For example, the Location Information Report can be designed so that certain columns are highlighted or so that different formatting is applied to headers, etc. 

These Excel templates may be designed to include Macros that will reformat the layout of a standard report. 

Excel Templates can be created to associate different parts of the template to the tabbed data, column header, and row header sections of a Crosstab Report. Additionally, headers and footers can be set up for the report to draw information from the DT_FACILITY table in the EQuIS facility that data is exported from.

Create a Template in MS Excel

To design the report template

Open a new workbook in Microsoft Excel, and save as Crosstab-Template-All_Locations.xlt.

Note:  The template should be saved as an Excel 97-2003 template.

Header Design

Headers and footers may be customized to include additional information using field name codes. The field names that are available for use are:

  • Any field included in the Crosstab design's Row Headers
  • Any field from DT_FACILITY. These codes correspond to row headers that have been defined in the Crosstab table

1. Insert a logo or other image file into cell $A$1 to $B$3.

Idea:  In the file that will be exported from EQuIS Professional, the logo image may be distorted when copied across multiple sheets. This is a direct result of copying a Microsoft Excel worksheet. The distortion can be minimized by ensuring that the image is pasted into a single column or pasted across multiple columns, which must have a defined column width.

2. In cells D1 to D3 enter the following values to export the FACILITY_NAME, SYS_LOC_CODE and SAMPLE_DATE as header information.

Idea:  For additional header and footer syntax, see Table 5: Guidelines for Header and Footer Syntax.

Cell Value Explanations
D1 Project: {dt_facility.facility_name} "DT_FACILITY" has been included, because this field was not a part of the Crosstab's Row Headers.
D2 Location: {sys_loc_code} In the crosstab design for this exercise, each location has been separated into an individual worksheet. By entering just "SYS_LOC_CODE," the report will enter only the individual SYS_LOC_CODE for each worksheet. 

TIP:  If multiple locations are on one worksheet, and if it is desired that each location be listed in the header, the following may be used; SYS_LOC_CODE. This would be extremely useful for Location Group Codes, for example "Shallow Aquifer and Deep Aquifer."
D3 Date Range: {sample_date:min} - {sample_date:max} This entry will show the minimum and maximum dates found in the output of the worksheet, and include it in the header information.

Return to Top

Table Body Design

1. Place the cursor and select cell A7. Right-click and select Name a Range. This is the location in the worksheet where the top-left cell of the exported table will appear.

2. Name the range cell A7, Data, followed immediately by hitting Enter. Unless Enter is pressed, the name will not be applied to the cell.

3. Format the rows and columns with borders and shading as desired. To apply a certain style, highlight the target cells with your cursor and choose the style from the Style toolbar.

4. The available styles may be customized as follows:

  • From the Style toolbar, select a New Cell Style, or select one of the existing styles, right-click and select Duplicate. 

     
     
  • Define the Style as ColumnHeader. This will define the format of the Column Headers from the Crosstab Design.
  • Using the format button and the check boxes, define the style as shown in the following figure.

5. Repeat Step 4 to format the remaining Crosstab Design elements, Row Headers and Tabbed Data fields.

Crosstab Design Element Style Name Style
Row Headers RowHeader Number: General
Alignment: Horizontal Centered, bottom aligned
Font: Times New Roman, 11, Bold
Border: Left, Right, Top, Bottom
Column Headers ColumnHeader (Completed in Step 7):
Number: General
Alignment: Horizontal Centered, bottom aligned
Font: Times New Roman, 11, Bold
Border: Left, Right, Top, Bottom
First Tabbed Data Field GroupColumn0 Number: General
Alignment: Horizontal Centered, bottom aligned
Font: Times New Roman, 10, Regular
Border: Left, Right, Top, Bottom
Second Tabbed Data Field GroupColumn1 Number: General
Alignment: Horizontal Centered, bottom aligned
Font: Times New Roman, 10, Regular
Border: Left, Right, Top, Bottom

Return to Top

Page Layout Design

1. To automatically copy the template headers to each page in the Excel workbook, define Print Titles.

  •  In MS Excel 2007 go to the Page Layout tab and select Print Titles.
  • On the Sheet tab define the Rows to Repeat: $1:$5 as these contain the rows where the log and title information have been inserted. Select "OK."

     
     

     2. To ensure that the entire template design is copied to multiple pages in the output file, delete any additional sheets in the workbook, e.g. Sheet2 and Sheet3.

Return to Top

Footer Design

 1. Define the Footer information:

  •  In Microsoft Excel 2007, go to the Page Layout tab and select Print Titles.
  •  On the Header/Footer tab define the Custom Footer by selecting "Custom Footer" and the site address, using the codes shown in Figure 7.

2. Save the template again. At this point the template should resemble the following figure.

 
 

3. Close Excel. 

Note:  Set an appSetting in your .config file to force a style to be used in an Excel file:

<add key="XlsForceStyle" value="True" />

Return to Top

Import the template into EQuIS Professional

1. Open EQuIS Professional.

2. Open and run any report, and then begin a Crosstab Report. 

3. In the Crosstab configuration screen, place a check in the box Use Excel Template, and the associated drop-down box is activated.

4. Click on the add template icon and a File Upload box is opened.

5. In the File section, browse to select the saved Microsoft Excel template file. Add a title and other information, and select Next.* *The file is uploaded to the database and is now available from the Excel template drop-down menu for future use by all users. Any .xlt files added in this way will appear in the drop-down box.

 

6.  In the Crosstab configuration screen, click Go to run the report. The Crosstab Report produced in EQuIS Professional will not contain any evidence of the Excel template at this stage.

7. Export the template to Excel by clicking the Excel icon.

8. After a few minutes, Excel will automatically open and display the report within the template.

9. Go to Print Preview to view the footer layout.

Note:  When using Excel templates to export a Crosstab, users can now use the selected ColumnHeaders as template codes.
Note:  Two new Crosstab templates are now available. These templates allow users to tile the crosstab horizontally or vertically when using the "separate tables by each unique" feature. When exporting the crosstab to Excel, the user must change the file type to Macro-Enabled Excel spreadsheets from the save file dialog window.

Excel Template Office Hour Video

An Office Hour Excel Template video is available. View below or click the corresponding icon to view via Screencast or download.

Return to Top