Using Microsoft Excel for inputs

ODBC interface using f90SQL from Canaima Software is planned for ORMSware to facilitate both reading from and writing to Excel workbooks and Access databases directly from models. In the mean time, NMOD has a limited facility (GetTable) to read tables from Excel worksheets.

The limitations are as follows:

• There should be only one worksheet in any Excel workbook used for storing input tables of NMOD models. If you want to use data from a workbook you already have with multiple sheets, this limitation can be circumvented by creating and intermediate workbook with just one sheet and pulling into it the desired data from the multiple-sheet workbook.
  
  
• Workbooks containing input tables must be saved in html format.
  
  
• A table should be delineated with Table object tags in column A (first column) as in Figure 14. Table labels (names) should be fully visible, since NMOD only reads the displays in spreadsheet cells. They should be no longer than 25 characters. However, characters in the tag are not counted for compliance by GetTable, which means that NETrans will not catch it if you violate this requirement.
  
  
• Every blank cell in a table should be indicated so with a dash in it. You will see examples of this in Chapter 20 (Reinforcing concepts through PrimerSubNet's feedback file).
  
  
• Columns in table spaces should not be hidden.
  
  
• The top delineator should be followed in the same row in column B by the number of rows used for row titles, and in column C by the number of columns used for column titles. In the CostPerPieceInputs table in Figure 14, 0 rows are used for row titles and 1 column is used for column titles. In the LaborCosts table, 2 rows are used for row titles and 1 column is used for column titles (though there are no entries in the title column in this case).
  
  
• Every table must have at least 1 title column. Please remember that number of title columns are defined in column C of every table's tag row. It is a common mistake to enter it in column B and to enter the number of title rows in column C. In other words, the order is row-column, not column-row.
  
  
• To let NMOD deduce the number of rows and columns of data in the table, there should be no row entries to the right of the last column of a given table. In this case, there should be no entries in columns C and higher in the rows containing CostPerPieceInputs table values, and there should be no entries in columns D and higher in the rows containing LaborCosts table values.
  
  
• Tables should be of character (string) type, or real/floating point type of the dbl KIND. If the LaborCosts table above were of character type (say) of 100-character string elements, it's name would be {Table.100}LaborCosts.

Two-dimensional arrays for storing tables from Excel will be automatically named in the DefNet.F95 file generated for the model by NETrans.EXE (network translator). For example, two-dimensional arrays for the above tables in Figure 14 will be automatically named and declared as

The two colons in the DIMENSION attributes of dblCostPerPieceInputs and dblLaborCosts indicate that they are two-dimensional arrays (i.e. the rank of both arrays is 2), and that the shape definition of the arrays (i.e. the extent along each of the two dimensions) is deferred. When NMOD fetches the tables during model run, it will deduce the shapes of the tables from the html code and allocate memory for table arrays accordingly.

Click here to open a separate (PrimerSigDefNetF95.TXT) window to view the DefNet.F95 file generated by NETrans.EXE for the current version of our example model.

Glance down for the section comment Table declaration(s) deduced by... Notice the array declarations for LaborCosts and CostPerPieceInputs tables.

Close PrimerSig's DefNet.F95 window.

At run time, NMOD will deduce from html file containing the worksheet that dblCostPerPieceInputs table has 3 rows and 1 column and dblLaborCosts table has 4 rows and 2 columns. It will allocate memory for both tables accordingly when the analyst uses NMOD's GetTable procedure to load the table from Excel.

To get data from Excel tables, the analyst should open the file containing the table (we recommend that you use NMOD's OpenTableFile procedure) and use NMOD's GetTable procedure to load tables. Click here to see how this is done in PrimerSig.NET, the NET file of the latest version of our model diagram displayed in Figure 13/PrimerSig.VSD window (click here if it is not open). Page down in PrimerSigNET.TXT window, or search, to find {Object.6}.

Under [6]'s Trans property procedure notice the statement for opening the PrimerSigTables.HTM file. This is the file containing the two tables used in this model. Though we used the PrimerSig name, however, name of a file containing tables for a model need not resemble the model name in any fashion. A model can use tables from as many files as necessary. 

The next two lines under the OpenTableFile statement call the GetTable procedure to load CostPerPieceInputs and LaborCosts tables into the model. Once tables are loaded, there is no need to keep PrimerSigTables.HTM open. Hence, the CLOSE statement below the GetTable calls.

Numbers in parenthesis at the end of the table's name (in the statements with {t.D} notation) refers to the row and column positions of the element in the table. Notice that this table has only one column of data.

In the three statements in this section, elements of the table are being assigned to various surrogate and global properties in the model. Notice that though the table array is a column vector, we have to use both row and column subscripts to retrieve values from the table. Since there is only one column in the array, all elements in the array have 1 for their column subscripts.

NMOD's LookUp function is similar to those found in spreadsheets:

• NMOD provides vertical lookup function (no horizontal lookup for now).
  
  
• LookUp function is (data) type-specific. A table can contain only one of two types of entries; the dbl KIND or string entries. If one needs to have a table with mixed entries, the analyst can accomplish that by using NMOD's VarLookUp procedure to read user-defined variables/properties, and numeral scalar or array values from a table of strings holding numerical values.
  
  

  Note: User-defined variables are explained in the next chapter.

   

• NMOD searches for the lookup value from the highest row of the table down to the lowest row. It stops the search when the value in the the first column of a row is less than the search value. If the search value is less than the first element of a table (i.e. row 1, column 1), NMOD considers the first row of the table to be the answer row.
  
  
• LookUp returns other values in addition to the value in the desired column of the answer row. It returns a binary value that indicates whether the search value matched a row value in the first column or just the inequality part of the test was satisfied. It also returns the number of the answer row.

Find {Object.4} in the PrimerSigNET.TXT window to see an example of how LookUp is used. We had to use &D.Temp as the lookup variable, because LaborCosts is a table of the dbl KIND. The lookup value we are using is {cp.B}PiecesPerHour (i.e. big KIND customer property PiecesPerHour).

Look at the table in Figure 14. If the value of {cp.B}PiecesPerHour is 40 or above, NMOD will set the value of #S.LaborCostPerHour to 35.00; if the value of {cp.B}PiecesPerHour is 30 or above, but less than 40, NMOD will set the value of #S.LaborCostPerHour to 30.00; and so on. If the value of {cp.B}PiecesPerHour is <= 0, NMOD will set the value of #S.LaborCostPerHour to 20.00.