Writing Information to an Excel Spreadsheet
Up to this point, we have been able to set all of the parameter values necessary to construct the 3D model, and to compute the part numbers. The last rule we’ll write in this tutorial will pass the part numbers and other parameter values associated with this file to an Excel spreadsheet. Some of these part numbers are in the iParts, and the screw part number will be generated by a rule that we are going to write.
Below is an image of the spreadsheet that has been created for this tutorial:
Add a new rule named “update_excel_spreadsheet_rule”. This rule will fill in the necessary cells dependent upon the state of the model.
For convenient reference, we have named all of the cells in the spreadsheet that we will be passing values to. These names loosely correspond to the information that will be written to each cell.
This rule utilizes a set of functions that are available from the Data Links category, found under the Rule Syntax tab in the iLogic Rule Editor. We’ll highlight these functions below.
The first portion of the rule opens the spreadsheet, and writes the first three cell values.
GoExcel.CurrentCellValue(“Block_Part_Number”) = iProperties.Value(“manifold_block:1″, “Project”, “Part Number”)
Here, we are referencing the “part_number.xls” spreadsheet file, which is included in this tutorial project. We then set the values for three named cells: Block_Type, Block_Style, and Block_Part_Number. The first two of these values are set from assembly parameters; the last is set from the block’s Part Number iProperty.
The next portion of the rule writes the values of the three port sizes to the spreadsheet. Notice how we use a placeholder value of “N/A” if we’re creating an elbow-style manifold.
If block = “tee” Then
GoExcel.CurrentCellValue(“port_b_size”) = port_b_size
GoExcel.CurrentCellValue(“port_b_size”) = “N/A”
GoExcel.CurrentCellValue(“port_c_size”) = port_c_size
The next few lines in the rule assign cell values from parameters contained in the manifold block component. Note the reference to the “manifold_block:1” component in this section.
The next portion of the rule assigns cell values from the part numbers of the union component, as well as from the screw parts, as computed by screw_part_number_rule.
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
GoExcel.CurrentCellValue(“port_b_union_cap”) = “N/A”
GoExcel.CurrentCellValue(“port_b_screw_kit”) = “N/A”
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 above, notice how there is conditional handling of the Port B-related values.
Finally, we need to save the changes to the spreadsheet. This is easily accomplished with one function available in the Data Links category. Double-click “GoExcel.Save”…
Now, press OK to save and close the rule. The spreadsheet will be updated as the rule fires upon closing. The rule will automatically close the spreadsheet when it is finished executing.
Now open the spreadsheet with Excel and see that it is up to date. Make sure that you close the spreadsheet document before the rule is executed again as the rule cannot update the spreadsheet if it is already opened with Excel.
Subsribe to Post Notifications