In previous articles (BOM Data to Excel, Point Data to Excel), I've discussed the transfer of data from Pro/ENGINEER to Excel. Of course, transfering the data in the other direction, from Excel to Pro/Engineer, is not only possible, but it's just as easy.
In this two part article series, I will be discussing how to take data from a selected range in Excel and transfer that into a new table in a Pro/ENGINEER drawing, and vice versa. Part 1 concerns the Excel to Pro/Engineer transfer, and part 2 concerns the Pro/Engineer to Excel transfer.
HTML Page
The HTML page for this application is very simple: two javascript files, two buttons, and two status fields. pfcUtils.js is provided by PTC (see my pfcCreate optimization article).
All remaining code from this article is in the excel2table.js file. One of the buttons allows transfer from Excel to ProEngineer using the ExcelToTable() function, while the other allows transfer in the other direction using the TableToExcel() function. The two div fields allow for the display of error and general status information.
<HTML>
<SCRIPT LANGUAGE="JavaScript" type=text/javascript src="pfcUtils.js"></SCRIPT>
<SCRIPT LANGUAGE="JavaScript" type=text/javascript src="excel2table.js"></SCRIPT>
<BODY>
<form name="f">
<table border=1>
<tr>
<td>Excel</td>
<td>
<INPUT name="button1" type=button value=">>" onclick="ExcelToTable()">
<br>
<INPUT name="button2" type=button value="<<" onclick="TableToExcel()">
</td>
<td>Drawing Table</td>
</tr>
</table>
<br><font color='red'><a id="errmesg"></a></font>
<br><a id="mesg"></a><br>
</form>
</BODY>
</HTML>
ExcelToTable Function
The core of ExcelToTable() is pretty basic. Other than resetting the status fields, it uses ArrayFromExcel() to obtain an array of Excel data and ArrayToProETable() to create a drawing table from the array.
function ExcelToTable ( ) {
var mesg = document.getElementById("mesg");
var errmesg = document.getElementById("errmesg");
mesg.innerHTML = "";
errmesg.innerHTML = "";
var array = ArrayFromExcel();
if (array == null) {
mesg.innerHTML = "null array";
return;
}
if (array != null) { ArrayToProETable(array); }
}
ArrayFromExcel Function
The ArrayFromExcel() function has three main tasks: get the Excel session object using GetObject(), get the current range selection object, then loop through the rows and columns of the selection and extract the cell values.
Looping through the selection is done using Enumerator objects, one for rows and one for columns. The rows Enumerator is created using the 'rows' property of the selection object, then a for loop is used to iterate over the rows. For each row, a columns Enumerator is created using the 'columns' property, which references the selected columns of the row. The second for loop iterates over those columns, which in this case are cells.
The Enumerator has an item() method which is used to obtain the row object from the row Enumerator and the cell object from the column Enumerator. The 'value' property returns the cell value, which would usually be of type 'Number' or 'String'. Regardless of the type, the value is coerced into a String for ease of processing later. Pro/ENGINEER treats all table cell values as strings anyway, so it doesn't really matter too much what the type is.
The array used is two dimensional, populated in the iteration of the selection. A two dimensional array is created by first constructing an Array object, then populating it with array objects. The elements of the internal arrays contain the cell values. In the iteration, as a new row is processed, a new Array object is created and then assigned to the top level array. In Javascript, elements can be added to an Array object by a simple assignment statement, almost endlessly.
Once the iteration of the selection via Enumerator's has completed and the array has been populated, the array is returned.
function ArrayFromExcel ( ) {
var oXL = null;
var mesg = document.getElementById("mesg");
var errmesg = document.getElementById("errmesg");
// Try to access Excel and get Application object.
//
try {
oXL = GetObject("","Excel.Application");
if (oXL == null) {
errmesg.innerHTML = "Failed to get Excel session object.";
return null;
}
}
catch (e) {
errmesg.innerHTML = "Excel must be running!";
return null;
}
// Get Selection object
var sel = null;
try {
sel = oXL.ActiveWindow.Selection;
}
catch (e) {
errmesg.innerHTML = "Could not get Selection object.";
return null;
}
if (sel.columns.count <= 1 && sel.rows.count <= 1) {
errmesg.innerHTML = "A range of cells must be selected in Excel.";
return null;
}
mesg.innerHTML = "Selection: " + sel.rows.count + " rows, " + sel.columns.count + " columns";
var i_row = 0;
var e_rows = new Enumerator(sel.rows);
var array = new Array();
// Loop through each row of the selection
//
for ( ; !e_rows.atEnd(); e_rows.moveNext() ) {
var i_col = 0;
var row = e_rows.item();
var e_cols = new Enumerator(row.columns);
array[i_row] = new Array();
// Within the row, loop through each column
//
for ( ; !e_cols.atEnd(); e_cols.moveNext() ) {
var cell = e_cols.item();
var val = cell.value;
if (val == null) { val = "-"; }
array[i_row][i_col] = "" + val;
i_col++;
}
i_row++;
}
return array;
}
ArrayToProETable Function
The ArrayToProETable() function takes an array and creates a drawing table. Its main steps are: get the ProE session object, prompt the user for the table location, create the table creation instructions object, create the row and column specification objects, create the table, add content to the table cells, and display the table. As you can see, the ProE portion is quite a bit more complicated than the Excel part.
Included in the function, part of the row and column specs, are calculations to auto-size the rows and columns to fit the data in Excel. This is primarily for readability of the resulting table making it optional, but very helpful.
Also included is a workaround for a table creation bug that limits the size of a table to no more than 50 rows and/or no more than 50 columns. This is a bug in all Pro/Engineer API's, even Pro/Toolkit and J-Link. It doesn't mean that a huge table can't be created, just that it can't be created in one shot.
The workaround in the function is to create the table with a single row or column then add more rows or columns as necessary to get the table to the requested size. With the adjustment step (adding rows and/or columns) coming after the table creation step, but before the table display step, the time required by the workaround is hardly noticable, except with huge tables.
function ArrayToProETable ( array ) {
var errmesg = document.getElementById("errmesg");
var session = null;
var drawing = null;
// Get ProE session object
//
try {
session = pfcGetProESession();
}
catch (e) {
errmesg.innerHTML = "Unable to connect to Pro/Engineer";
return null;
}
// Get model object and ensure it's a drawing
//
try {
drawing = session.CurrentModel;
if (drawing == null || drawing.Type != pfcCreate("pfcModelType").MDL_DRAWING) {
errmesg.innerHTML = "A drawing must be active!";
return null;
}
}
catch (e) {
errmesg.innerHTML = "A drawing must be active.";
return null;
}
// Get location for new table by mouse click
//
var location = getMousePickPosition("Select location for table", true);
if (location == null) { return null; }
// Setup the table creation instructions object
//
var instrs = new pfcCreate("pfcTableCreateInstructions").Create(location);
instrs.SizeType = pfcCreate("pfcTableSizeType").TABLESIZE_BY_NUM_CHARS;
// Figure out max sizes to auto-expand the table
var ArrayMaxData = getArrayMaxData(array);
// Generate column data (for table creation instructions object)
// API Bug: Can't create table with more than 50 columns
// Workaround: Create with one column, add more later
//
var column_just = pfcCreate("pfcColumnJustification").COL_JUSTIFY_LEFT;
var columnData = pfcCreate("pfcColumnCreateOptions");
var columnOption = pfcCreate("pfcColumnCreateOption");
var maxOneShotCols = 50; // Most columns that can be created without workaround
if (ArrayMaxData.cols < maxOneShotCols) {
// Number of columns is ok, setup instructions to create table as-is.
// Use max column widths to auto-size columns
for (var i=0; i < ArrayMaxData.max_col_width.length; i++ ) {
var column = columnOption.Create(column_just, ArrayMaxData.max_col_width[i]);
columnData.Append(column);
}
}
else {
// Too many columns to create table as-is, setup instructions
// to create table with one column, expand after creation
var column = columnOption.Create(column_just, ArrayMaxData.max_col_width[0]);
columnData.Append(column);
}
// Add column data object to table creation instructions
instrs.ColumnData = columnData;
// Generate row data (for table creation instructions object)
// API Bug: Can't create table with more than 50 rows
// Workaround: Create with one row, add more later
//
var rowData = pfcCreate("realseq");
var maxOneShotRows = 50; // Most rows that can be created without workaround
if (ArrayMaxData.rows < maxOneShotRows) {
// Number of rows is ok, setup instructions to create table as-is.
// Use max row heights to auto-size rows
for (var i = 0; i < array.length; i++) {
rowData.Append(ArrayMaxData.max_row_height[i]);
}
}
else {
// Too many rows to create table as-is, setup instructions
// to create table with one row, expand after creation
rowData.Append(ArrayMaxData.max_row_height[0]);
}
// Add row data object to table creation instructions
instrs.RowHeights = rowData;
// Create the table (no contents yet, and won't be visible yet)
//
var dwgTable = null;
try {
dwgTable = drawing.CreateTable(instrs);
}
catch (e) {
errmesg.innerHTML += "Cannot create table: " + e.message;
return;
}
// Add rows if data had more than allowed to create in one shot
//
try {
if (ArrayMaxData.rows >= maxOneShotRows) {
for (var i=1; i < array.length; i++ ) {
// Add remaining rows needed. Index starts
// at 1 because one row already exists
dwgTable.InsertRow(ArrayMaxData.max_row_height[i], null, false)
}
}
}
catch (e) {
errmesg.innerHTML += "Cannot add rows: " + e.message;
return;
}
// Add columns if data had more than allowed to create in one shot
//
try {
if (ArrayMaxData.cols >= maxOneShotCols) {
for (var i=1; i < ArrayMaxData.max_col_width.length; i++ ) {
// Add remaining columns needed. Index starts
// at 1 because one column already exists
dwgTable.InsertColumn(ArrayMaxData.max_col_width[i], null, false);
}
}
}
catch (e) {
errmesg.innerHTML += "Cannot add columns: " + e.message;
return;
}
// Fill in each cell with data
//
for (var i=0; i < array.length; i++ ) {
for (var j=0; j < array[i].length; j++ ) {
// Table cell indexes start with one, arrays with zero
writeTextInCell(dwgTable, i+1, j+1, array[i][j]);
}
}
// Display the table
dwgTable.Display ();
}
getMousePickPosition Function
The getMousePickPosition() function is used to prompt the user for a position by using the left mouse button. It uses the UIGetNextMousePick() method of the pfcSession object to accomplish this. The function also hides the browser window, displays an alert dialog to prompt the user, and then restores the browser window to its previous size.
function getMousePickPosition ( prompt, hideBrowser ) {
var browserSize = null;
var session = pfcGetProESession();
if (hideBrowser) {
// Minimize browser, remember browser size for later restoration
browserSize = session.CurrentWindow.GetBrowserSize();
session.CurrentWindow.SetBrowserSize(0.0);
}
// Prompt user and get user specified location
alert(prompt);
var mouseButton = pfcCreate("pfcMouseButton");
var mouseClick = session.UIGetNextMousePick(mouseButton.MOUSE_BTN_LEFT);
if (hideBrowser) {
// Restore browser window
session.CurrentWindow.SetBrowserSize(browserSize);
}
return mouseClick.Position;
}
getArrayMaxData Function
The getArrayMaxData function analyzes an array and returns an object containing four properties. Two properties are the number of rows and columns in the array, which is expected to be two dimensional. The other two properties are maximum column widths for each column and maximum row heights for each row.
If a row in excel contains a cell with a single linefeed character, that row will have a maximum row height of at least two characters. Similarly, if a column contains a cell with 50 characters, the maximum column width for that column will be at least 50 characters.
Once these values are populated, the object is returned to the calling function.
function getArrayMaxData ( array ) {
var obj = new Object();
// Figure out max size for each row and column to auto-expand
//
obj.max_col_width = new Array();
obj.max_row_height = new Array();
for (var i=0; i < array.length; i++ ) {
obj.max_row_height[i] = 0;
}
for (var i=0; i < array[0].length; i++ ) {
obj.max_col_width[i] = 0;
}
obj.rows = array.length;
obj.cols = 0;
// populate arrays of max column widths and max row heights
//
for (var i=0; i < array.length; i++) {
for (var j=0; j < array[i].length; j++) {
if (obj.cols < array[i].length) { obj.cols = array[i].length; }
var lines = array[i][j].split("\n");
if (lines.length > obj.max_row_height[i]) {
obj.max_row_height[i] = lines.length;
}
for (var k=0; k < lines.length; k++ ) {
if (lines[k].length > obj.max_col_width[j]) {
obj.max_col_width[j] = lines[k].length;
}
}
}
}
return obj;
}
writeTextInCell Function
The writeTextInCell() function writes text to a specific table cell. If the cell data in Excel contains linefeeds, the result will be a table cell that matches with the same number of lines of text.
function writeTextInCell ( table, row, col, text ) {
var cell = pfcCreate("pfcTableCell").Create(row, col);
var lines = pfcCreate("stringseq");
var cell_lines = text.split("\n");
for (var i=0; i < cell_lines.length; i++ ) {
lines.Append(cell_lines[i]);
}
table.SetText(cell, lines);
}
There may be strange issues if "zombie" or multiple Excel sessions are running. If Excel is clearly running, but unexpected error messages are received, it may be best to kill all Excel sessions, then start it over again.
Part 2: Sending drawing table data to Excel
23 comments:
Hi,
I need some information on where to paste the code given by you and how do I proceed. Can u please give a detailed step by step process. I'm not a software guy and have no idea about codes, but I'm interested in the transfer of data from Excel to Proe and vice versa, 'coz I'm very much in Mechanical work using Proe.
Thanks,
Suresh
Using Pro/WebLink is very easy to setup and use, but getting it setup correctly can
be a little frustrating. The only tool required is a text editor and the ability to
change the Internet Explorer security settings for the "Local intranet" zone.
To familiarize yourself with the Internet Explorer security settings, read this post:
Getting Started Tips
As it says in the article, if you are attempting to have Pro/WebLink communicate with
Excel, you need a lower level of security. Set the option discussed to "Enable", as
the default option "Prompt" will not work when working with Excel. Also keep in mind
that the program will not work if placed on a local drive (i.e. C: drive), make sure
it is saved onto a network drive.
Save the javascript code mentioned in these articles to a file called "excel2table.js",
and the html code to a file called "excel2table.htm" in your desired network drive
location:
Excel to Drawing Table Part 1
Excel to Drawing Table Part 2
Copy the pfcUtils.js file, which can be found buried a few levels under the "weblink"
folder structure in your Pro/Engineer install, into the desired network folder location
along with the code from parts 1 & 2 of the article.
Using the Pro/Engineer embedded web browser, open the "excel2table.htm" and run the
program from there. There are various reasons for it not working, including incorrect
Internet Explorer security settings as mentioned in the "Getting Started Tips" article.
Please review that carefully if you have trouble.
If you have other questions or trouble, please let me know.
Marc
MarcMettes@InversionConsulting.com
It seems this wouldn't work with Pro Engineer 2001 as it doesn't have an imbedded web browser. Any thoughts on how to transfer tables (NOT using an OLE object) to a Pro-E 2001 drawing?
Before the Wildfire versions, Pro/WebLink didn't seem to have any mechanism to access drawing table cells. Without that, you would have to use the "save to text" option.
Using J-Link or Pro/Toolkit are other options to consider.
Marc
I would like to know if this script will work with a Pro/E file on a UNIX machine and the excel file on a PC.
Thanks
Not directly, but if the data is saved to disk, it could be read into Excel on your PC.
The program is designed to transfer data directly between Pro/Engineer and Excel, but it's really a four part program. To transfer from Unix Pro/Engineer you need only the "from ProE" part to save the data to disk.
How you would read it in depends on the complexitiy of the data (i.e. multiple carriage returns in table cells). The "to Excel" part could be used to put the saved data into Excel, or via File->Open in Excel.
This requires modification to save the data to disk and read it back again. If this is something you need, I can post an example on how to do this.
Marc
Hi,
I really like the idea of this function. I followed the instructions, but, in PRO/E browser I get the error message "can not connect to PRO/E". My browser settings are correct and the web/link setting to.
Could it be the .dll? I'm not familiar with how to check this.
tanx in advance.
grtz
Dries
Make sure you follow the steps shown in this article and be sure to set the options to make the interaction with Excel work correctly (must change from "Prompt" to "Enable"):
WebLink Getting Startup Tips
I have noticed that occasionally I have to run the program first as a windows administrator before the program will work for non-admin users. If you are not at least a windows "Power User", then have an admin log into windows, start Pro/E, and run the program. Unfortunately, I am seeing this more and more often, but it is easy to do, just annoying.
Ok. I just figured it out. Thank you for charing this information.
I'm looking cor a way to detect the blank sheetmetal size automatically. Do you have an idee where i can find soms info on this topic?
Tanx n grtz
Dries
If anyone figures out a way to automatically figure out the size of a flat blank (X & Y) from a sheetmetal model, please publish that somewhere and send it to me (jdurston@yahoo.com)
Hi
I do not get where html and js code should be written (pasted)? I did the adjustments.. Is there any text editor on ProEngineer to type the code? How can I access that?
Thanks,
Alex
This article helps explain some of the setup procedures for Pro/WebLink:
Pro/WebLink: Tips on Getting Started
In summary, save the HTML code to a .htm file. Save the Javascript code to a .js file. Make sure the HTML file references the .js file you choose (i.e. excel2table.js). Copy these files along with pfcUtils.js (found in Pro/E install) to a network drive (not the local C drive) or onto a web server. Open the .htm file from this location using the Pro/E embedded web browser.
The "tips" article mentions many of the security changes that are required so be sure to read that for successful interaction with excel.
Marc
Thanks
I did that and it worked; I can send table data from ProE to Excel sheet.. Is it possible to create a dynamic link between ProE and Excel data so that change of one table can update the other simultaneously?
Hi Marc,
I need Some help from your end.can we operate a Mapkey in Proe With exel?
Ex:if I created a Mapkey,whic Hiden Some Layers in Model Tree.and from the outside the proe,in excel Sheet "Is layer Status is Checked" in that row if I write "yes",the Mapkey in Proe should work and set the layers hiden!. if it is Possible!clearly explain! how to do this type of automaization?
it wil be very useful to me!
Thanks in adavance,
-Phani
Yes, that should be possible, but with a few conditions. First of all you must use the Pro/E VB API, which requires WF4 and higher. This API is not a default install option, but you can add at any time using the install disk.
To run a mapkey from one of the APIs, use a similar syntax for running a mapkey from within a mapkey (i.e. ...;%qw;... to run mapkey "qw"). Mapkeys are executed using the "macro" related methods.
For specific questions, please contact me directly.
Marc
--
Marc Mettes
MarcMettes@InversionConsulting.com
Marc,
I was able to run this in WF3 but we have upgraded to WF4 and I am now getting the unable to connect to Pro/Engineer error. I have tried updating pfcUtils.js file from the WF3 version to the WF4 version with no luck. Do you have any advice?
Thanks in advance,
Scott
The code should work as-is in WF3 and WF4. I have seen problems with Pro/WebLink when people switch between Pro/E installs. It seems that Pro/E doesn't always re-register the Pro/WebLink DLLs correctly.
Are you sure the WF4 install has the WEB_ENABLE_JAVASCRIPT set to ON?
Marc
--
Marc Mettes
MarcMettes@InversionConsulting.com
Hey,
I used the code as mentioned in the part1 and can transfer data from excel file to ProE drawing but when I tried copying the part 2 code below the part 1 javascript code in the file excel2table.js. I get a script error code. Please help...
Thanks,
Kartik
It was working fine for me on Windows XP. Now my company replaced my PC with 64-bit Windows 7 and the script doesn't work any more. I used the same settings in IE 8.0 as before for XP. And yes the WEB_ENABLE_JAVASCRIPT is set to "ON" in my config.pro file.
Any ideas folks?
Pro/WebLink apps are always working around the IE security system, which makes troubleshooting things difficult. I have seen cases where a user with admin privileges has to login and run the app before non-admin users can run it successfully.
If you've worked through all the other setup steps and nothing else works, have an admin login and run it.
Feel free to send me an email directly to discuss.
Marc
Hello,
at first I want to apologize for my bad english. You are really my last hope.
I want to send a Excel Table to ProE. I took some of your code and it works really good. I advanced the code to work with merged cells. But now I want to change the orientation of the Note in the cells up to 90°. I'm trying now for 30 hours, without success.
I think, I have to use the "DetailNoteInstructions" but I don`t know how ?
Please, can you help me ?
Hello,
at first I want to apologize for my bad english. You are really my last hope.
I want to send a Excel Table to ProE. I took some of your code and it works really good. I advanced the code to work with merged cells. But now I want to change the orientation of the Notes in the cells up to 90°. I'm trying now for 30 hours, without success.
I think, I have to use the "DetailNoteInstructions" but I don`t know how ?
Please, can you help me ?
Just wanted to say thanks for the code! I was facing manually entering about a few 200x6 spreadsheets and you've saved me a few days of work.
Post a Comment