Write Information to an Excel Spreadsheet

We have now set all the parameter values necessary to construct the model compute the part numbers. The last rule we write passes the part numbers and other parameter values associated with this file to an Excel spreadsheet. Some of these part numbers are in iParts, and this rule generated the screw part number.

The update_excel_spreadsheet_rule utilizes a set of functions that are available from the Excel Data Links category, in the Snippets area of the Edit Rule dialog box.

1. Add a new rule named update_excel_spreadsheet_rule.

   This rule fills in the necessary cells depending upon the state of the model. In the spreadsheet, all the cells to which data is being passed have been named to correspond to the information being written.

2. Create the first portion of the rule, which opens the spreadsheet and writes the first three cell values.
   

   Copy Code Block

   GoExcel.CellValue("part_number.xls", "Sheet1", "Block_Type") = component_type
   GoExcel.CurrentCellValue("Block_Style") = block
   GoExcel.CurrentCellValue("Block_Part_Number") = iProperties.Value("manifold_block:1", "Project", "Part Number")

   In this section, we reference the part_number.xls spreadsheet file included with this tutorial project. We then set the values for the cells Block_Type, Block_Style, and Block_Part_Number. The first two values are set from assembly parameters, and the last value is set from the block’s Part Number iProperty.

3. Create the next portion of the rule, which writes the values of the three port sizes to the spreadsheet.
   

   Copy Code Block

   GoExcel.CurrentCellValue("port_a_size") = port_a_size
   If block = "tee" Then
   GoExcel.CurrentCellValue("port_b_size") = port_b_size
   Else
   GoExcel.CurrentCellValue("port_b_size") = "N/A"
   End If
   GoExcel.CurrentCellValue("port_c_size") = port_c_size

   Note how we use a placeholder value of N/A for an elbow-style manifold.

4. Add another section to the rule which assigns cell values from parameters contained in the manifold block component.
   

   Copy Code Block

   GoExcel.CurrentCellValue("block_depth") = Parameter("manifold_block:1", "block_depth")
   GoExcel.CurrentCellValue("block_width") = Parameter("manifold_block:1", "block_width")
   GoExcel.CurrentCellValue("block_height") = Parameter("manifold_block:1", "block_height")

   Note the reference to the manifold_block:1 component in this section.

5. Add a section to the rule which assigns cell values from the part numbers of the union component and the screw parts, as computed by screw_part_number_rule.
   

   Copy Code Block

   GoExcel.CurrentCellValue("port_a_union_cap") = port_a_union_part_number
   GoExcel.CurrentCellValue("port_a_screw_kit") = port_a_screw_part_number
   If block = "tee" Then
   GoExcel.CurrentCellValue("port_b_union_cap") = port_b_union_part_number
   GoExcel.CurrentCellValue("port_b_screw_kit") = port_b_screw_part_number
   Else
   GoExcel.CurrentCellValue("port_b_union_cap") = "N/A"
   GoExcel.CurrentCellValue("port_b_screw_kit") = "N/A"
   End If
   GoExcel.CurrentCellValue("port_c_union_cap") = port_c_union_part_number
   GoExcel.CurrentCellValue("port_c_screw_kit") = port_c_screw_part_number

   As with port_b_size in a previous section of the rule, note the conditional handling of the values related to Port B.

6. To end this rule, save the changes to the spreadsheet. Use the GoExcel.Save function, which is available from the Excel Data Links node in the Snippets area
   

   Copy Code Block

   GoExcel.Save

7. Click OK to save and close the rule. The spreadsheet updates as the rule executes upon closing. The rule automatically closes the spreadsheet after executing.
8. Open the part_number.xls spreadsheet with Excel, and verify that it is updated. Close the spreadsheet document before the rule executes again, because the rule cannot update the spreadsheet if it is already open from Excel.

Previous | Next