Working with Spreadsheet Data in NX
How can we use a spreadsheet to drive the design of an NX part?
NX part files can contain, reference, and link to spreadsheet data. This is often used to define iterations or multiple configurations of a design such as Pattern features, Part Families, etc. Methods such as Patterns generally retain the spreadsheet data within the feature definition and not the part file as in the case of Part Families. This article discusses the application and use of spreadsheet driven data from both perspectives.
The Spreadsheet serves as an advanced expression editor for NX hybrid modeling thus providing a seamless transfer of conceptual model data between NX and the spreadsheet.
This section focuses specifically on the spreadsheet extensions and applications in NX and is intended to be a supplement to the standard spreadsheet documentation, which can be viewed in the NX Documentation Set.
The spreadsheet consists of three main session applications:
- A Gateway spreadsheet function, available in Tools tab→Utilities group→Spreadsheet.
- A Modeling spreadsheet function, available from Tools tab→Utilities group→Spreadsheet. Some spreadsheet programs (such as ” Goalseek”), may not be fully supported within the Modeling Spreadsheet extended functionality.
- A basic Expression Editor. You can find the expression edit spreadsheet in the Tools→Expression dialog in Modeling. Click “Spreadsheet Edit.”
While the spreadsheet functions are active, you have full spreadsheet functionality. You can edit and modify cells; change color, captions, and text; or even import or export spreadsheets that could have originated on a PC.
The Gateway spreadsheet is activated in current application modes except for Modeling.
NX will start a copy of Xess, or Microsoft Excel on Windows, and make connections to it automatically. Messages on the status line inform you of the progress made toward connecting with the spreadsheet. If there is a copy of the spreadsheet already running on your display that can accept connections, then NX uses that copy without invoking another instance of the spreadsheet.
- If there is already a spreadsheet saved in the part, then that spreadsheet will be opened with the application that created it (Xess or Excel) regardless of the default spreadsheet set in the spreadsheet preferences. If there is no spreadsheet stored in the part, and you are running on Windows, the setting from Preferences→Spreadsheet (available only on Windows) will be used to determine whether to use Xess or Excel.
- An Xess spreadsheet session started from a command line can accept connections to NX if you start the program with the command line options “-connect a” or if you toggle the Connections→Accept Connections option on the spreadsheet main menu bar. Remember that the NX specific version of Xess, xessu, cannot be started from the command line.
The Modeling spreadsheet is launched by Tools→Spreadsheet and requires that the current application mode be in “Modeling”.
NX will start a copy of the spreadsheet and make connections to it automatically. Messages on the status line inform you of the progress made toward connecting with the spreadsheet. If there is a copy of the spreadsheet already running on your display that can accept connections, then NX will use that copy without invoking another instance of the spreadsheet.
- If there is already a spreadsheet saved in the part, it will be opened with the application that created it (either Xess or Excel), regardless of the default spreadsheet set in its preferences. If there is no spreadsheet stored in the part, and are running on Windows, the setting from File tab→Preferences→Spreadsheet will be used to determine whether to use Xess or Excel.
- In these discussions about the Modeling spreadsheet, the pull-down menus such as File→, Edit→, and Tools→ usually refer to options within the spreadsheet, not the pull-down menus from the NX main Menu Bar (unless specified).
With the exception of the Reference Layout, a Spreadsheet option is available for all other layouts in the Pattern dialog. This allows the user to drive the design of the duplicated feature(s) to be driven by spreadsheet data. Once the feature to duplicate has been selected and the Layout requirements have been satisfied, the Use Spreadsheet option will become active.
NX allows the access of spreadsheet data directly from an outside file. There are two ways to do this, depending on what format you want.
1. Go to Tools→Import and Export Expressions, select the Import option, and browse for the spreadsheet file to bring in. You’ll note there are two formats you can filter for, “.xls” and “.xlsx”.
A listing window reports the success or failure of the imported expressions:
This process allows outside spreadsheet data in .xls or .xlsx format to be brought into an NX part file directly as expressions but without any linking or associativity.
2. Go to Tools→Expressions and in that dialog at the top there’s an icon for Spreadsheet Edit.
This will start a new spreadsheet and from there you can open an existing spreadsheet or copy/paste from another spreadsheet. NX doesn’t offer an Import Spreadsheet command under File→Import but that does not matter if you can just open an existing spreadsheet using this method. Once the Worksheet is open, note the ADD-INS tab at the top resulting from the NX integration for spreadsheets in the NX installation.
Next to the Spreadsheet Edit icon in that dialog, there are also two icons for Import Expressions from File and Export Expressions to File. These two functions allow the copying of an expressions file (with the extension of “.exp”) into or out of an NX part file.
With the use of the spreadsheet and expressions functions discussed above, the NX user has the ability to reuse mathematical values, formulae, expressions, equations, and even character strings both inside and outside of an NX part file. This functionality is practically identical when working with assembly or drawing files as well.
However, these practices do not develop any associativity or linking between the data within the NX part and that or the spreadsheet or expression file.
Procedure for linking a value in a spreadsheet outside of a part file to an internal expression:
1. Choose Tools tab→Utilities group→Expressions.
2. In the Expressions dialog box, in the Name box, type “thickness”.
3. Click Functions.
4. In the Insert Function dialog box, from the Or Choose a Category list, select spreadsheet.
5. From the list of functions, select ug_cell_read.
|Specify a Spreadsheet icon|
6. Click OK.
7. In the Function Arguments dialog box, click Specify a spreadsheet.
8. Navigate to the desired spreadsheet and click OK.
9. In the Specify a Cell box, type the desired cell callout such as “B3”.
The Expressions dialog box is displayed and the function ug_cell_read(“<SPREADSHEET_NAME>”, “B3”) appears in the Formula box.
10. Click OK.
11. Verify the value of the expression and click OK.
Migrating data between spreadsheet applications (Excel/Xess)
To assist you with migrating data between spreadsheet applications, NX provides a migration utility.
On Windows, you can migrate spreadsheet data from Xess to Excel and vice versa. On all systems, you don’t need to have access to the spreadsheet where the data is stored. You only need access to the spreadsheet to which you are migrating.
When migrating from Xess to Excel, you only need Excel. Similarly, migrating spreadsheet data from Excel to Xess requires only Xess (which also means that Exceed must be present).
Before You Begin
Before you begin, make a copy of the part containing the old spreadsheet data. Keep the copy until you verify that the new spreadsheet data is correct.
In this example, the value of the expression is 2.5.
Note: If the spreadsheet is modified, you can update the expression by clicking Refresh Values from External Spreadsheet.
|Refresh Values from External Spreadsheet Icon|
Spreadsheets are also available in the Drafting application. As an example, a Tabular Note on a drawing can be edited using a spreadsheet. Simply right-click the handle of the Note and select Edit Using Spreadsheet from the popup menu.
Note that by default, Expressions functionality is not enabled in the Drafting application. It can be turned on either by editing the settings in the global customer defaults file (.dpv) or by going to File→Utilities→Customer Defaults→Drafting→Drawing→General tab, and toggle the Allow Expressions option on.
Want to learn more about NX and improve your efficiency? Enroll in NX training or request a custom class or mentoring today!