Creating Excel Templates

<< Click to Display Table of Contents >>

EQuIS 7  >>  Professional > Reports > How To >

Creating Excel Templates

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 the design might apply different formatting 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. In addition, 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 Excel

 

Design Report Template

 

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

 

Note: Save 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 available for use are listed below.

 

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.

 

Tip: 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 an 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.

 

Tip: For additional header and footer syntax, read here.

 

Cell

Value

Explanation

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, SYS_LOC_CODE may be used. 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.

 

The template field name codes that can be used, must first exist in the Crosstab design’s Row Headers or in the DT_FACILITY table.

 

For example, if SYS_LOC_CODE is not populated in the Crosstab Configuration Row Header, it will not populate in the template field. However, this can be added as a row header and the Display value can be set to None to hide it in the output, and the template field will populate.

 

The placement of these fields is the key to getting them to populate. They do not necessarily need to be placed inside the page footer or header, however they must be placed above the row header columns.

 

The template fields do not use the standard Excel language. For example, the date value, Date: {sample_date:min:$YYYY-MM-dd}, will not populate the year. Removing the ‘$’ symbol allows the complete date to populate.

 

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 clicking Enter. Enter must be clicked in order for the name to 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 the cursor and choose the style from the Style toolbar.

4.For Crosstab Reports, the following styles will be used for that area of the Crosstab report output:

 

Crosstab Design Element

Style Name

Style

First Column Header

ColumnHeader0

Number: General

Alignment: Horizontal Centered, bottom aligned

Font: Times New Roman, 11, Bold

Border: Left, Right, Top, Bottom

Second Column Header

ColumnHeader1

Number: General

Alignment: Horizontal Centered, bottom aligned

Font: Times New Roman, 11, Bold

Border: Left, Right, Top, Bottom

First Row Header

RowHeader0

(Completed in Step 7):

Number: General

Alignment: Horizontal Centered, bottom aligned

Font: Times New Roman, 11, Bold

Border: Left, Right, Top, Bottom

Second Row Header

RowHeader1

(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

 

Note: So that previous Excel templates that only defined the ColumnHeader and RowHeader styles are compatible with the new iterated header styles (ColumnHeader0, ColumnHeader1, etc. and RowHeader0, RowHeader1, etc), when the new iterated styles are created and applied, the formatting from the ColumnHeader and RowHeader styles are copied to these new styles respectively.

 

To create these styles in the template:

 

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

 

clip0108

 

Define the Style as one of the Crosstab Design elements listed above. 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.

 

clip0109

 

Page Layout Design

 

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

a.In Excel 2007, go to the Page Layout tab and select Print Titles.

 

clip0110

 

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).

a.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.

 

Footer Design

 

1.Define the Footer information by following the instructions below.

a.In Excel 2007, go to the Page Layout tab and select Print Titles.

b.On the Header/Footer tab, define the Custom Footer by selecting Custom Footer and the site address, using the codes shown in the image below.

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

 

clip0111

 

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" />

 

Import 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.

 

clip0112

 

4.Click on the add template icon and a File Upload box will open.

5.In the File section, browse to select the saved 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.

 

clip0113

 

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.Excel will automatically open and display the report within the template.

 

15080-crosstab.rprt

 

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

 

Notes:

When using Excel templates to export a Crosstab, you can now use the selected ColumnHeaders as template codes.

Two new Crosstab templates are now available. These templates allow you to tile the crosstab horizontally or vertically when using the separate tables by each unique feature. When exporting the crosstab to Excel, you must change the file type to Macro-Enabled Excel spreadsheets from the Save File dialog window.

 

Office Hour Video

 

The Office Hour video Enhancing EQuIS Reports - Part 1 - Microsoft Templates in EQuIS Crosstab can be viewed on YouTube here.