Contents - Index - Previous - Next

Template Formats

Each Excel template must be defined using a specific format in order to correctly send EMME/2 data to Excel. The template may have an optional title area with headings and must have a data area to define the format and layout of the data.

Let's take a look at the Centroids.xls template below.



The first thing you will notice is that there are a lot of #NAME? error messages (if there isn't enough space for this text, Excel replaces it with # signs). This is normal. Pick any such cell and look at the Formula bar. Almost all the formulas include the pattern =xlData_FieldName or =xlHeading_FieldName. The underscore is required and the FieldName is the name of the EMME/2 data attribute or heading. An xlData formula has data fields while an xlHeading formula has heading fields. Since Excel cannot find these cells in the workbook immediately it displays #NAME? You can apply any formatting to any workbook cells, insert pictures, and modify any of the parameters of the workbook itself.

To create a report of all centroids, we created a specially formatted set of cells - A6:J7. Row 5 contains the headers for each column and Row 8 is the row that is displayed at the bottom of the report. Note that you may also to use all of the standard Excel formulas as well as functions, references, etc. in any cell. 

The A6:J7 range was named Centroids. Select this name from the Name dialog and you'll see this named range. Note the empty column to the left of the range and the empty row below. The empty column to the left is called the option column while the empty row below is called the option row. These cells may be used to describe additional actions that are used to manipulate the data. 

In the option row below the data row (Row 7 in this example) you can define a number of summary options to further format the data. Only one summary option per column is permitted. These summary options include:
  • Sort - If you enter the word "sort" in the Boardings option cell (Cell H7 in this example), then the data will be sorted in ascending order by the total boardings. To sort the data in descending order, enter "sort;desc". You may sort on up to 3 columns.
  • Sum - Enter the word "sum" in an option cell to sum the values of a column.
  • Count - Enter "count" in an option cell to count the number of items in a column.
  • CountNums - Enter "countnums" to count the number of rows containing numeric data.
  • Avg - Enter "avg" to average the values in a column.
  • Min - Enter "min" to show the minimum value of a column.
  • Max - Enter "max" to show the maximum value of a column.
  • StDev - Enter "stdev" to calculate an estimate of the standard deviation of a population, where the column is the sample.
  • StDevP - Enter "stdevp" to calculate the standard deviation of a population, where the column is the entire population.
  • Var - Enter "var" to calculate an estimate of the variance of a population, where the column is the sample.
  • VarP - Enter "varp" to calculate the variance of a population, where the column is the entire population.
     
    In addition to these summary options, you may define the Group option. Let's look at the Links template as an example.



    Note that the option cell E7 has the word "group" and the option cell M7 has the word "sum". The group option has the result of grouping values in a column in sequence. In this example, the data will be grouped by mode and a sub-total of auto volumes will be calculated for each mode and then for all modes. You can group up to 16 columns, where the data is grouped in left-to-right order. If you enter the word "group\collapse" then only the sub-total and total rows are displayed for each grouped column.

    In the option column (cell A7 in this example), you may enter a number of range options. A range option is an option that is performed on all values in the data range. Range options include:
  • OnlyValues - Enter "onlyvalues" to substitute all formulas in the data range with their values.
  • AutoFilter - Enter "autofilter" to perform the same function as the Data|Filter|AutoFilter Excel function.
  • RowsFit - Enter "rowsfit" to automatically size all data rows to their contents.
  • ColsFit - Enter "colsfilt" to automatically size all columns to their contents.
     
    You may enter more than one range option by separating each option with a semi-colon (for example, rowsfit;colsfit).