Reading and Writing to Excel Using Python in an Automated BuildIT Process
Overview
One of the key features of BuildIT is the generation of extensive reports, including several layouts and formats. However, in cases when the user already has an Excel report template and needs to keep its same report layout, in addition to using values computed through the Excel spreadsheet as inputs for the automated process, it is possible to leverage the Python interpreter that is available through the automated processes to perform those tasks.
This article provides an overview of how to achieve this through a simple process example. This example uses the win32com module, which drives Excel through its COM interface.
Other specialized Python modules also exist for writing and reading Excel spreadsheet, such as pyexcel and openpyxl. However, for our purpose in this example, win32com is sufficient, and it is also bundled with the BuildIT Python interpreter.
Example Workflow
- Populate some values in the spreadsheet (cells in light green), based on the BuildIT model's content
- In this example, we'll transfer the feature tolerances actual values, displayed in the feature callouts
- Have Excel compute some results
- Read the results computed by Excel (displayed in the lower-right portion of the spreadsheet - cells "L32" and "L33")
- Use those values in the BuildIT process. They will be displayed to the user in a feedback message.
Example BuildIT model (already inspected):
Example Excel template:
Walkthrough
Download the example files used in the walkthrough:
- Download and unzip all files contained in "Excel Process Example.zip" in the same folder
- Play process "Excel Process Example.process" (press F5, or drag-and-drop in the viewport)
- The process example can be examined by opening "Excel Process Example.py". This script follows a simple structure:
- Define a dictionary for associating each feature property to report with a range (a cell address in the spreadsheet)
- Copy the Excel template to a user-defined location, and open that copy for populating it
- Populate the report, using the lookup table defined previously through a dictionary
- Populating the spreadsheet will update its formulas dynamically and automatically, just as filling them in manually would
- Read the results of some calculations performed by Excel
- Display those results to the user