Excel datasets provide user configuration options to specify how you wish the Database DMI to work with Microsoft Excel. Excel datasets can only be used on the Windows platform and Excel must be available on the system. RiverWare will start a non-visible copy of Excel in the background and access the specified workbook during DMI execution. As a result, there are two important things to remember:

See Example 2: Excel DMI to Export Data Using Headers for a use case of creating a DMI to Excel.

Double-clicking an Excel dataset in the Dataset Manager opens the Excel Dataset editor. In this dialog, the dataset can be renamed and configured. It has two tabs, one for general configuration and one specific to the Excel implementation. Following is a description of the configuration options.

Select the desired Name Map from the Name Map menu. The selections are populated based on the Name Maps configured in the Name Map Manager. See Name Mapping for details on creating Name Maps. Name maps have special relevance for moving data using range specifications and header specification. See Slot Mapping—Spreadsheet Ranges Approach and Using Name Maps for details.

The user specifies how missing values are handled with Excel. The current choices are

For Output DMIs, specify the precision of values that are sent to the database, that is, the number of digits to the right of the decimal point. The options are:

Values are converted to the specified units, as described in Units, before rounding.

The rounding algorithm is: round(value * 10^N) / 10^N where value is after unit conversion, N is the number of digits of precision, and round() is a standard function which rounds its argument to the nearest integer value, rounding halfway cases away from zero.

Also, to be clear, all values exported from a Database DMI are floating point numbers regardless of precision. For example, an expression slot might compute a value of 7538.21. Exported with 0 digits of precision, this would appear to be 7538, but technically, it was exported as a floating point number, 7537.9999999974 to 15 significant digits. This fact is really only important if you database displays 15-17 digits.

The units that are used in interacting with Excel are specified in the Dataset dialog. A pull-down menu allows the user to specify that the Database DMI should Use Database Units, Prefer Database Units, or Use Dataset Units.

See Units for instructions on specifying the units to use.

Configuration specific to Excel is specified in the Excel tab of the Excel Dataset editor.

In all cases, the Workbook that the dataset will read data from or write data to must be specified. The path and workbook name can be typed into the Workbook text box, or you can select the adjacent button to select the file. When executing an input DMI, this workbook must already exist. For an output DMI, the workbook will be created if it does not already exist.

Two approaches are provided for mapping data between RiverWare and Excel.

Table 4.4 compares these approaches. Then more information is presented on the configuration options for both the Range and Header approach.

The Map by Spreadsheet Ranges approach presents several options for specifying ranges associated with slots. In all cases, the Name Map functionality of Database DMIs is used to map individual slots to their range specifications. You can map Series, Table, Periodic, or Scalar slots using this approach.

Discussions of the range specification options along with example Name Maps follow.

Under the Map to Excel Named Ranges with Name Map option, the user would set up named ranges in their Excel workbook. Named ranges are a feature of Excel where a number of cells can be selected and assigned a name by the user to identify these cells. The Name Map created in RiverWare and selected in the General tab of the dataset dialog then will associate the RiverWare slot with the named range as seen in the following example name map. In this case, BigResInflow and BigResOutflow are named ranges in the Excel sheet.

In the Map to Excel Range Specifications with Name Map option, there are two ways to specify ranges for slots. If all the ranges for the slots are on the same worksheet, then the Ranges on same worksheet option can be used. Here the worksheet name is specified in the Worksheet text box. Ranges specified in the name map can be of the form B2:B50, meaning the range will be from cell B2 to cell B50 of the specified worksheet. Figure 4.22 shows an example of the type of Name Map created and selected on the General tab of the dataset dialog for this approach.

If ranges for the slots are on different worksheets then the Ranges on different worksheets option must be used. Under this option, ranges specified in the name map must include the worksheet name as part of the range specification in the form Sheet1!B2:B50. In this way, ranges referring to different worksheets can be included in the same name map. Figure 4.23 shows an example of this type of Name Map.

Under all variations of the Map by Spreadsheet Ranges approach, there is an option available to Specify Range Offset for each Run of Multiple Run. In a multiple run, the user may want to read different data into a slot for each run or write out the results for the slot under each run. If the option is checked, the mapped range for the slot is used for the first run, and the specified row and column offset is applied for each run thereafter. For example, the range for a slot specified in the dataset name map could be Sheet1!B2:B50 and the offset specified here could be 0 rows and 1 column. For an input DMI in a multiple run, the first run will read range B2:B50 on Sheet1 into the slot, the second run will offset one column and read range C2:C50 on Sheet1, the third range D2:D50, etc. For an output DMI, the slot’s results from the first run will be written to range B2:B50 on Sheet1, results from the second run will offset one column and be written to range C2:C50 on Sheet1, the third to range D2:D50, etc.

The Map by Header Text and Sheet Name approach is the alternative to using ranges to move RiverWare slot data to or from Excel. The approach uses a row header, a column header, and a worksheet name to map Excel data with a RiverWare slot. Figure 4.24 shows an example of the dataset dialog with this configuration selected.

Name maps are not needed in the header approach. However they can be used to map to a different name in Excel. The following is performed for each slot:

When the Series Slots option is chosen, you can specify the format of the series slots in the spreadsheet.

The Header Orientation frame provides four orientation choices for how timesteps, slots, and runs will map to rows, columns, and sheets in Excel. For example, the first orientation as selected in the adjacent screenshot indicates that rows are timesteps, columns are slots, and worksheets are runs.

Figure 4.25 shows an Excel spreadsheet with the first orientation. In this orientation, timesteps are rows, so the timestep header is the first column with a time label for each row. Similarly, the slot header is the first row with each column labeled with the slot name. Sheets are runs, so the sheet is named with the single run name specified. With the orientation specified, the data in Excel associated with a slot over a time range can be found for reading via an input DMI, or can be found and overwritten, if existing, or be newly created by writing via an output DMI.

The headers may not always be the first row and the first column. To accommodate these cases, an option is provided to specify how the headers are offset from the corner of the spreadsheet. By indicating how many rows and how many columns the header is offset from the upper left corner of the spreadsheet, the user can match how data resides in an existing spreadsheet or indicate where the headers and data should be written to a new sheet.

When the Table / Periodic / Scalar Slots option is chosen, you are not allowed to specify the format of the slots in the spreadsheet. Instead, there is a very specific format that is used. Figure 4.26 shows a sample workbook. Table slots have a header merged cell for the slot name, cells for column headers, and the columns that contain row headers and data columns. Periodic slots include a column of dates, one or more columns of data, and a header cell. Scalar slots are represented by a header cell and a value cell.

Functionality to import/export Table, Periodic, and Scalar slots from/to Excel has the following limitations:

The Run Name Type options indicate how runs in the Excel workbook will be labeled.

Use one of the following options:

For Series Slots, an option is provided to Use Begin Timestep Time Instead of End of Timestep.

Timesteps in RiverWare are dated with the end of timestep time, that is, a 1 Day timestep for the last day of the year 2000 is dated 12/31/2000 24:00. If the begin timestep option is not checked, this date is written to (and is expected to be read from) Excel as 12/31/2000 23:59. A minute is subtracted compared to the RiverWare end of time so that the date appears in the correct month and year in Excel. (Excel has no concept of a 24:00 display, so 12/31/2000 24:00 is interpreted by Excel as 1/1/2001 00:00, which would appear to be the wrong month and year.)

If Use Begin Timestep Time Instead of End of Timestep is checked, then the timestep is written to (and is expected to be read from) Excel as the beginning of timestep in RiverWare. For the 12/31/2000 24:00 timestep in RiverWare at a 1 Day timestep size, the time in Excel would be 12/31/2000 00:00. This option may be particularly useful if the user is entering data into Excel and is typing in 1 Day timesteps as 12/30/2000, 12/31/2000, 1/1/2001, and so on, which end up by default with 00:00 hours and minutes in the Excel date format. With the begin timestep option checked, the data associated with these times will be moved into RiverWare for their correct timesteps. When the model timestep is 1 Hour, the 12/31/2000 24:00 timestep in RiverWare would have a time in Excel of 12/31/2000 23:00 if the Use Begin Timestep Time option is checked and an Excel time of 12/31/2000 23:59 if the option is not checked.

Select the Clear Dates and Values for Output options to remove the dates and values before writing outputs.

This is valid and enabled only under the following conditions:

When this option is selected, both the dates and values will be deleted first and then the DMI will write new dates and values. For example, in the following figure, it will first clear out the dates and value for rows for 2 through 8, for 11/21 through 11/27, and then write values for 12/1 to 12/7 to get the screenshot on the right.

An option is also provided with the header approach to Use Unit Name with Slot Name. In an output DMI, this option will attach the unit name to the slot name when the slot name is written out to its appropriate row, column, or sheet, depending on the orientation selected. The format is the slot name, a space, and the unit name in parentheses, such as Storage Reservoir.Inflow (cfs). In an input DMI, this option means that the DMI will expect the unit name to be attached to the slot name as indicated above when it is looking for the slot in the Excel headers or sheets.

The final part of the Excel DMI tab is the choice of Connection Process as shown in Figure 4.27.

This configurations setting specified how the RiverWare DMI should connect or communicate with the Excel workbook. The two choices are: