More Examples Importing Data from Excel

The following examples show different ways to bind imported data to RASON model objects. Each example uses the same basic Product Mix optimization model, but the source of the data and the way the data is referenced differs slightly.

Example 1: Excel Table Binding with valueCol

Open by clicking Examples – Optimization – Optimization with Data Binding in RASON Desktop Extension. The ProductMixExcel.xlsx workbook can be opened from C:\ProgramData\Frontline Systems\Examples\ExcelRunner.

In ProductMixExcel1.json, the data section binds three Excel datasources to RASON data objects:

"data": { 
  "parts2": { 
    "binding": "parts_data", 
    "valueCol": "qty", 
    "comment": "always assign a single valueCol" 
  }, 
  "invent": { 
    "binding": "invent_data", 
    "valueCol": "inventory" 
  }, 
  "profit": { 
    "binding": "profit_data", 
    "valueCol": "profits" 
  } 
}

This example imports data from ProductMixExcel.xlsx and uses indexCols and valueCols in the datasource definitions. The important point is that each object in the data section binds to a datasource and explicitly identifies the single value column to use. For example, parts2 binds to parts_data and uses the qty column, while invent uses inventory and profit uses profits.

This is useful when the Excel range is being interpreted as a table. The index columns identify the row or column labels, while valueCol tells RASON which numeric column should be used in calculations. In this example, the imported part requirements are later reshaped with PIVOT, while inventory and profit are used directly in the constraint and objective formulas.

Example 2: Excel Table Binding with Results Written Back to Excel

Open by clicking Examples – Optimization – Optimization with Data Binding in RASON Desktop Extension. The ProductMixExcel.xlsx workbook can be opened from C:\ProgramData\Frontline Systems\Examples\ExcelRunner.

In ProductMixExcel11.json, the data section is similar to the first Excel example:

"data": { 
  "parts2": { 
    "binding": "parts_data", 
    "valueCol": "qty" 
  }, 
  "invent": { 
    "binding": "invent_data", 
    "valueCol": "inventory" 
   }, 
   "profit": { 
     "binding": "profit_data", 
     "valueCol": "profits" 
    } 
  }

The main difference is not in the data section itself, but in the surrounding model. This example adds two additional Excel datasources:

"vars_data": { 
  "type": "excel", 
  "connection": "ProductMixExcel.xlsx", 
  "selection": "Sheet1!Q2:R4", 
  "indexCols": ["prods"], 
  "valueCols": ["initials"] 
 }, 
 "fcns_data": { 
   "type": "excel", 
   "connection": "ProductMixExcel.xlsx", 
   "selection": "Sheet1!U2:U6", 
   "direction": "export" 
}

Here, vars_data supplies the initial values for the decision variable x, while fcns_data is used to export constraint results back to Excel. This means the model both imports input data from Excel and writes selected results back to the workbook.

The formula references are also slightly different. This example uses invent and profit directly:

"formula": "MMULT(piv_parts, x) - invent"

and:

"formula": "sumproduct(x, profit)"

instead of using invent[] and profit[].

Key Differences

The Excel examples use valueCol in the data section to identify the single numeric column that should be used from each imported table. This is especially important when a datasource is interpreted as a table with both index columns and value columns.

The second Excel example also demonstrates Excel write-back. It imports data for the model, imports initial values for the decision variables, and exports constraint results back to Excel. This makes it different from the basic Excel import example, which only imports data.