Mapping Between Parameters and Cells

You can use direct cell mapping to select a single cell to map to/from an Optimization Process Composer scalar parameter or array element or to select a range of cells to map to/from an Optimization Process Composer array parameter of the same size.

This task shows you how to:

See Also
About the Excel Adapter
Running Excel Macros
Using Advanced Options
Setting Execution Permissions

Map Between a Scalar Parameter and a Single Cell

You can select a single cell from the worksheet and map its value to or from a scalar parameter.

  1. From the Applications section of the action bar, click Excel and drop it on the process diagram.
  2. Double-click Excel .
    The Excel Editor appears.
  3. From the Content list, select a document containing an Excel workbook from the content of the activity. If the document does not exist, you can select one of the following from the menu:
    • Search for and select a document containing the workbook to add as referenced content .
    • Create a non-versioned document that contains the workbook.
    • Create a versioned document that contains the workbook.

    Note: Alternatively, you can select Parameter and select an object parameter that refers to a document containing an Excel workbook.

  4. Click Load Excel.
    A read-only view of the Excel workbook and its worksheets appears in the editor.
  5. From the desired worksheet, select a single cell to map to an Optimization Process Composer scalar parameter.
  6. From the menu below the view of the worksheet, expand the Map parameter pane.
  7. From the Parameter field, do one of the following to select the parameter to map:
    • Enter the name of an existing scalar parameter.

    • Leave the selection empty and allow Optimization Process Composer to create the parameter and name it using the selected cell.

      Note: If you have applied formatting to the cells in your Excel worksheet (such as bold or italic font, font color, fill color, or bordered cells), Optimization Process Composer cannot determine the selected cell range, and you must enter the range in the field provided.

    • Select a scalar Optimization Process Composer parameter from the Parameter list.

    • Click to create a new scalar Optimization Process Composer parameter.

  8. Select the mapping direction:

    • Map to Excel maps the value of the Optimization Process Composer parameter to the selected cell in the Excel worksheet.

    • Map from Excel maps the selected cell in the Excel worksheet to the value of the Optimization Process Composer parameter.

  9. Click Add mapping from selected Excel data.

    Optimization Process Composer adds the mapping to the list at the bottom of the editor. The parameter mode, type, and value are set automatically. Parameters that map to Excel are set to input mode; parameters that map from Excel are set to output mode.

  10. Repeat the above steps for each parameter that you want to map.
  11. Click Ok to save your changes and to close the Excel Editor.

Map Between an Array Parameter and a Block of Cells

You can select a block of contiguous cells from the worksheet and map their value to or from an array parameter of the same dimensions.

  1. From the Applications section of the action bar, click Excel and drop it on the process diagram.
  2. Double-click Excel .
    The Excel Editor appears.
  3. From the Content list, select a document containing an Excel workbook from the content of the activity. If the document does not exist, you can select one of the following from the menu:
    • Search for and select a document containing the workbook to add as referenced content .
    • Create a non-versioned document that contains the workbook.
    • Create a versioned document that contains the workbook.

    Note: Alternatively, you can select Parameter and select an object parameter that refers to a document containing an Excel workbook.

  4. Click Load Excel.
    A read-only view of the Excel workbook and its worksheets appears in the editor.
  5. From the desired worksheet, select a contiguous block of cells to map to an Optimization Process Composer array parameter of the same size.

    Note: The selected cells cannot be empty.

  6. From the menu below the view of the worksheet, expand the Map parameter pane.
  7. From the Parameter field, do one of the following to select the parameter to map:
    • Enter the name of an existing array parameter.

    • Leave the selection empty and allow Optimization Process Composer to create the array parameter and name it using the selected range of cells.

      Note: If you have applied formatting to the cells in your Excel worksheet (such as bold or italic font, font color, fill color, or bordered cells), Optimization Process Composer cannot determine the selected cell range, and you must enter the range in the field provided.

    • Select an array Optimization Process Composer parameter from the Parameter list.

    • Click to create a new array Optimization Process Composer parameter. The dimensions of the array parameter must be the same as the dimensions of the selected cells.

  8. Select the mapping direction:

    • Map to Excel maps the value of the Optimization Process Composer parameter to the selected cells in the Excel worksheet.

    • Map from Excel maps the selected cells in the Excel worksheet to the value of the Optimization Process Composer parameter.

  9. Click Add mapping from selected Excel data.

    Optimization Process Composer adds the mapping to the list at the bottom of the editor. The parameter mode, type, and value are set automatically. Parameters that map to Excel are set to input mode; parameters that map from Excel are set to output mode.

  10. Repeat the above steps for additional parameters that you want to map.
  11. Click Ok to save your changes and to close the Excel Editor.

Map Between Parameter Name-Value Pairs

You can select two columns (or rows) of cells from the worksheet and create a parameter name-value pair. The name of the parameter is taken from the cell in the left column (or top row); the value of the parameter is taken from the cell in the right column (or bottom row).

  1. From the Applications section of the action bar, click Excel® and drop it on the process diagram.
  2. Double-click Excel .
    The Excel Editor appears.
  3. From the Content list, select a document containing an Excel workbook from the content of the activity. If the document does not exist, you can select one of the following from the menu:
    • Search for and select a document containing the workbook to add as referenced content .
    • Create a non-versioned document that contains the workbook.
    • Create a versioned document that contains the workbook.

    Note: Alternatively, you can select Parameter and select an object parameter that refers to a document containing an Excel workbook.

  4. Click Load Excel.
    A read-only view of the Excel workbook and its worksheets appears in the editor.
  5. From the desired worksheet, select the block of cells containing both the names of the parameters that you are mapping and the associated values of the parameters.

    The selected block must contain either two columns or two rows. Optimization Process Composer takes the names of the parameters from the cells in the left column or the top row; Optimization Process Composer takes the values of the parameters from the cells in the right column or the bottom row.

    Note: The selected cells cannot be empty. If you have applied formatting to the cells in your Excel worksheet (such as bold or italic font, font color, fill color, or bordered cells), Optimization Process Composer cannot determine the selected cell range, and you must enter the range in the field provided.

  6. From the menu below the view of the worksheet, expand the Map parameter pane.
  7. From the Map parameter pane, select the mapping direction:

    • Map to Excel maps the value of the new Optimization Process Composer parameters to cells in the Excel worksheet.

    • Map from Excel maps the cells in the Excel worksheet to the value of the new Optimization Process Composer parameter.

  8. Click Add Name-Value Mapping.

    Optimization Process Composer adds the name-value mapping to the list at the bottom of the editor. The parameter mode, type, and value are set automatically. Parameters that map to Excel are set to input mode; parameters that map from Excel are set to output mode.

  9. Repeat the above steps for additional parameters that you want to map.
  10. Click Ok to save your changes and to close the Excel Editor.

Map Between Parameter and Named Cells

You can name cells in Excel and map Optimization Process Composer parameters to the named cells. The parameters will have the same name as the name of the cell and the same value as the contents of the cell.

  1. From the Applications section of the action bar, click Excel and drop it on the process diagram.
  2. Double-click Excel .
    The Excel Editor appears.
  3. From the Content list, select a document containing an Excel workbook from the content of the activity. If the document does not exist, you can select one of the following from the menu:
    • Search for and select a document containing the workbook to add as referenced content .
    • Create a non-versioned document that contains the workbook.
    • Create a versioned document that contains the workbook.

    Note: Alternatively, you can select Parameter and select an object parameter that refers to a document containing an Excel workbook.

  4. Click Load Excel.
    A read-only view of the Excel workbook and its worksheets appears in the editor.
  5. From the menu below the view of the worksheet, expand the Map parameter pane.
  6. From the Map parameter pane, select the mapping direction:

    • Map to Excel maps the value of the new Optimization Process Composer parameters to cells in the Excel worksheet.

    • Map from Excel maps the cells in the Excel worksheet to the value of the new Optimization Process Composer parameters.

  7. Click Add Named Cells Mapping.

    Optimization Process Composer searches the workbook and creates a scalar parameter for each named cell that it finds. Optimization Process Composer uses the name of the cell as the name of the parameter and the contents of the cell as the value of the parameter. If the worksheet contains a range of cells that have been named, Optimization Process Composer creates an array parameter of the same dimensions. The parameter mode, type, and value are set automatically. Parameters that map to Excel are set to input mode; parameters that map from Excel are set to output mode.

  8. Click Ok to save your changes and to close the Excel Editor.