Wednesday, June 18, 2008

Pro/E VB API: Not Just for Visual Basic Anymore

In a previous article entitled "WebLink: What is it anyway?", I discussed Pro/WebLink and how it's not exactly a Javascript interface. Now it's the VB API's turn. The VB API is newly released by PTC with Wildfire 4. It is billed as a Visual Basic interface to PTC's PFC libraries, which is the same core that J-Link and Pro/WebLink are built upon.

While this is true, it's not that limited. Don't let the name scare you away from using your favorite COM accessible programming or scripting language to write Pro/Engineer applications. This is a good thing because it opens the API to a much wider range of applications.

In this article, I'll walk through sample applications written in Visual Basic, VBScript (using Windows Script Host), Perl, and Javascript (in a HyperText Application), all using the VB API. The sample application will connect to a Pro/Engineer session, obtain the session object, display the name of the current model, then disconnect from the Pro/Engineer session. Each program will display the model name in a different way.

Suggested reading is the section on the importance of disconnecting from Pro/Engineer in Pro/E VB API: A First Look.

Excel Macro

This is the standard and documented approach to the sample program. Objects are declared up front, the connection to Pro/Engineer is made, then the session object is obtained. The model name is displayed both in cell A1 and in a MsgBox.

Sub Macro1()

Dim asynconn As New pfcls.CCpfcAsyncConnection
Dim conn As pfcls.IpfcAsyncConnection
Dim session As pfcls.IpfcBaseSession
Dim mdlname

Set conn = asynconn.Connect("", "", ".", 5)
Set session = conn.session

mdlname = session.CurrentModel.Filename
Range("A1").Select
ActiveCell.FormulaR1C1 = mdlname
MsgBox ("Name: " & mdlname)
conn.Disconnect(2)

End Sub
 

VBScript

The VBScript code is almost identical except that VBScript doesn't seem to allow for the type declaration of the objects. As a result, the CreateObject() call is used to instantiate a pfcAsyncConnection object.

The program is executed using the following command line:
  cscript vbapi_script.vbs
 

Dim asynconn
Dim conn
Dim session
Dim mdlname

Set asynconn = CreateObject("pfcls.pfcAsyncConnection")
Set conn = asynconn.Connect("", "", ".", 5)
Set session = conn.session

mdlname = session.CurrentModel.Filename
MsgBox ("Name: " & mdlname)
conn.Disconnect(2)
 

Perl

As with most COM applications, the Perl syntax is also very similar to the VBScript code, but with Perl's own syntactical flavor. Win32::OLE->new() is the Perl equivalent to VBScript's CreateObject(). The Perl program outputs the model name to the command prompt (or standard output).

use Win32::OLE;
$asynconn = Win32::OLE->new("pfcls.pfcAsyncConnection");
$conn = $asynconn->Connect( "", "", ".", 5 );
$session = $conn->Session;
$mdlName = $session->CurrentModel->FileName;

print "mdlName: $mdlName", "\n";
$conn->Disconnect(2);
 

Javascript

Pro/WebLink applications can finally break out of the embedded browser jail using the VB API. This example uses a "Hypertext Application", which is a web page with a special HTA tag and with a file extension of ".hta" instead of ".htm". The pfcUtils.js file cannot be used as-is because it tries to use COM objects with "pfc." prefixes, instead of those associated with VB API which have "pfcls." prefixes.

Other than those differences, it's essentially a Pro/WebLink application. As with the other examples, because it's an asynchronous application, the code must connect to the Pro/Engineer session. This is a step that embedded browser based Pro/WebLink applications don't have to worry about.

<html>
<head>
<title>VB API Test</title>

<HTA:APPLICATION
ID="vbapi-test"
APPLICATIONNAME="VB API Test"
SCROLL="auto"
SINGLEINSTANCE="yes"
>
</head>

<body>

<SCRIPT LANGUAGE="JavaScript">

function HitMe ( ) {
var obj = null;
var elem = document.getElementById("mesg");

if (obj == null) {
try {
obj = new ActiveXObject("pfcls.pfcAsyncConnection");
}
catch (e) {
elem.innerHTML = "Failed to create object";
return;
}
}

var conn = obj.Connect( "", "", ".", 5 );
var session = conn.Session;
var mdlName = session.CurrentModel.FileName;

elem.innerHTML = "mdlName: " + mdlName;
conn.Disconnect(2);
}

</SCRIPT>

<form name="f">
<INPUT name="a" type=button value="Hit me!" onclick="HitMe()">
<br><a id="mesg"></a><br>
</form>

</body>
</html>
 

These are just a few of the possibilities. As you can see, there is nothing really Visual Basic specific about the VB API. It's just an API.

Tuesday, June 17, 2008

Pro/WebLink: Send Excel Data To Pro/ENGINEER Drawing Tables, and Back Again - Part 2

In part 1, the transfering of data from Excel to Pro/ENGINEER drawing tables was discussed. In this second part, the discussion will focus on the other direction, sending Pro/ENGINEER drawing table data to an Excel workbook.

TableToExcel Function

The TableToExcel() function is also pretty simple. After resetting the status fields, it uses ArrayFromProETable() to obtain an array of data from a user selected drawing table, then uses ArrayToExcel() to populate a new Excel workbook with the data from the array.

function TableToExcel ( ) {

var mesg = document.getElementById("mesg");
var errmesg = document.getElementById("errmesg");
mesg.innerHTML = "";
errmesg.innerHTML = "";

var array = ArrayFromProETable();
if (array == null) {
mesg.innerHTML = "null array";
return;
}

if (array != null) { ArrayToExcel(array); }
}
 

ArrayFromProETable Function

The ArrayFromProETable() function gathers drawing table data into an array. It's main steps are: Get the Pro/Engineer session object, obtaining the current model object and ensure a drawing is active, request a drawing table selection from the user, and iterate through the table cells to populate the array elements. After the table is selected by the user, UnHighlight() is used to repaint the table with its normal colors.

The table cell iteration code cycles first through the table rows, then the columns of each row, then through the lines of text in each cell. When there is more than one line of text in a table cell, a linefeed character is added as a line separator. This is correctly interpreted by Excel as multi-line data.

It's important to note that while in Javascript (as in many programming languages) arrays are indexed starting with zero, both drawing tables and Excel cells are indexed starting with one.

Once the iteration of the table cells has completed and the array has been populated, the array is returned.

function ArrayFromProETable ( ) {

var mesg = document.getElementById("mesg");
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;
}


// Prompt user to select an existing table
//
var selections = null;
var table = null;

try {
selections = selectItems("dwg_table", 1);
var tabnum = selections.Item(0).SelItem;
table = drawing.GetTable(tabnum.Id);
}
catch (e) { // nothing selected
errmesg.innerHTML = "A drawing table was not selected.";
return null;
}

selections.Item(0).UnHighlight();


mesg.innerHTML = "Selected Table: "
+ table.GetRowCount() + " rows, "
+ table.GetColumnCount() + " columns";


// Gather data from table and populate into array
//

var array = new Array();

for (var i=0; i<table.GetRowCount(); i++) {

array[i] = new Array();

for (var j=0; j<table.GetColumnCount(); j++) {

// Table cell indexes start with one, arrays with zero
var cell = pfcCreate("pfcTableCell").Create(i+1,j+1);
var mode = pfcCreate("pfcParamMode").DWGTABLE_NORMAL;
array[i][j] = "";

try {
var textseq = table.GetText(cell, mode);

for (var k=0; k<textseq.Count; k++) {
var textitem = textseq.Item(k);
if (k > 0) { array[i][j] += "\n"; }
array[i][j] += textitem;
}
}
catch (e) { // cell has no value
// ignore
}

}

}

return array;
}
 

ArrayToExcel Function

The ArrayToExcel() function takes the two dimensional array and populates cells in a new Excel workbook. There are only three steps in this function: Get the Excel session object, Create a new workbook and get the active sheet object, Populate cells of the active sheet from data in the array.

As an alternative, a new Excel session could be started if an existing one can not be found.

function ArrayToExcel ( array ) {

var oXL;
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) {
// oXL = new ActiveXObject("Excel.Application");
errmesg.innerHTML = "Excel must be running!";
return;
}


// Make session visible, get Sheet object
//
try {
oXL.Visible = true;
var oWB = oXL.Workbooks.Add();
var oSheet = oWB.ActiveSheet;
}
catch (e) {
errmesg.innerHTML = "Problem creating new workbook.";
return;
}


// Put the array data into the cells of the active sheet
//
for (var i=0; i < array.length; i++ ) {
for (var j=0; j < array[i].length; j++ ) {
// Excel cell indexes start with one, arrays with zero
oSheet.Cells(i+1, j+1).Value = array[i][j];
}
}

}
 

selectItems Function

The selectItems() function is largely based on selectItems() from an example in the Pro/WebLink documentation. It is changed here to be slightly more generic. The function builds a pfcSelectionOptions object that defines the type and number of allowable items that can be selected. It also minimizes the browser window during the selection process.

A sequence of selections (or null if nothing was selected) is returned.

function selectItems ( options, max ) {

// Setup options object
selOptions = pfcCreate("pfcSelectionOptions").Create(options);
selOptions.MaxNumSels = parseInt(max);


var session = pfcGetProESession();
var browserSize = session.CurrentWindow.GetBrowserSize();
session.CurrentWindow.SetBrowserSize(0.0);


var selections = null;

try {
selections = session.Select(selOptions, null);
session.CurrentWindow.SetBrowserSize(browserSize);
}
catch (err) {
session.CurrentWindow.SetBrowserSize(browserSize);
// In case user didn't select expected item
var errstr = pfcGetExceptionType(err);
if (errstr == "pfcXToolkitUserAbort" || errstr == "pfcXToolkitPickAbove") {
return null;
}
}

if (selections == null || selections.Count == 0)
return null;


return selections;
}
 


As I've mentioned, interaction with Excel is easy and straightforward. Although the Pro/Engineer interaction code is much more complex, the PFC API's contain a rich set a classes and methods making automation, such as I have shown here, possible. It's a quantum leap beyond relying upon mapkeys.

Wednesday, June 11, 2008

Intralink SQL: Searching Based on Attribute Values

Searching based on attribute values can be performed using Oracle SQL, but a little investigation work needs to be done beforehand.

Attributes and their characteristics are contained in the pdm.PDM_CLASSATTR table in the Oracle database. The first step that needs to be done is to identify the 'id' number of the attribute.

The following query shows, for the "file based" attributes, the 'id', the name, and the 'type'. Also included is the CLADEFVALU column which would show any default values for the attributes.



-- show file based attributes
--
select CLAID,CLANAME,CLADEFVALUE,CLATYPE
from pdm.PDM_CLASSATTR
where CLAUSERDEF=1 and CLAFILEBASED=1
order by CLANAME;

CLAID CLANAME CLADEFVALU CLATYPE
---------- -------------------- ---------- ----------
123 Desc 5
234 Matl 5
345 Mc_Config 5
456 Mc_Errors 2
567 Mc_Mode 5
678 Model_Check 5
789 Weight 3
 

The CLATYPE numbers correspond to these data types:

  • 2: Integer
  • 3: Real
  • 4: Boolean
  • 5: String

If the 'Desc' attribute is needed, we take the CLAID value and prepend "UDA" to form the column name in the pdm.PDM_PRODUCTITEM_VUDA1 table. In this example, the Desc attribute corresponds to the "UDA123" column.


Putting this into an example, the following query searches for Intralink PIV's with the Desc attribute matching "...some string...".

-- search based on attribute values 
--
column piname format a35
column UDA123 format a40
column REVVER format a7

SELECT
piv.pivid, PINAME, PIVREV'.'PIVVER as REVVER, UDA123
FROM
PDM.PDM_PRODUCTITEM pi,
PDM.PDM_BRANCH br,
PDM.PDM_PRODUCTITEMVERSION piv,
pdm.PDM_PRODUCTITEM_VUDA1 vuda
WHERE
pi.PIID = br.PIID
AND br.BRID = piv.BRID
AND piv.pivid = vuda.pivid
AND UDA123 like '%some string%'
;
 


For an exact match, use this as the last condition in the "WHERE" clause instead:

AND UDA123 = 'some string'
 

Pro/E VB API: A First Look

A peek under the hood of the new API for Wildfire 4

In case you didn't know about it, PTC introduced a new PFC based API for Pro/Engineer, starting with Wildfire 4. It's called the VB API and it allows code to be written in Visual Basic that can communicate with Pro/Engineer. (For the curious, it does not work with older versions of Pro/Engineer, sorry)

Unlike Pro/WebLink, it is not limited to running in the embedded web browser. Applications written with the VB API can be run almost anywhere that supports a Visual Basic environment (i.e. in an Excel macro). It's important to understand how this new API works in order to understand some of its limitations and to write robust applications.

The API is an asynchronous PFC API, which means that it shares a set of core classes and methods which are mostly identical to Pro/WebLink and J-Link. It is asynchronous in that VB API applications run outside of the Pro/Engineer process (i.e. in Excel, Word, etc), but still can communicate and control the Pro/Engineer session.

EXE COM Server?

Similar to Pro/WebLink, it is implemented as a COM server, but (interestingly) not as a DLL. While a VB API application is running, an executable called "pfclscom.exe" starts and runs in the background. This introduces a major limitation.

Only one VB API program can run at a time, even if there are multiple Pro/Engineer sessions. If a second VB API application tries to start, it won't be able to connect to any Pro/Engineer session if the first one is still connected. As long as the first application has disconnected, the second connection will be successful.

This is a little disappointing, because one can have several asynchronous Pro/Toolkit applications connected to a single Pro/Engineer process at the same time. In fact, there could be any number of Pro/Toolkit applications connecting to any number of Pro/Engineer sessions. There is really no underlying technical restriction causing this limitation.

You had me at goodbye

Those familiar with asynchronous Pro/Toolkit and J-Link applications probably already know that the main steps in an asynchronous application are: 1) Connect to the Pro/Engineer session, 2) Do some stuff, 3) Disconnect. While step 2 is somewhat optional, step 3 is absolutely critical.

The primary reason is that Pro/Engineer keeps track of all asynchronous connections, and considers connections that have not disconnected as "open". Unfortunately, there is a limit to the number of "open" connections, and that limit is surpisingly low.

If you have an asynchronous application that connects over and over with no disconnect, Pro/Engineer will eventually refuse any further connections. It doesn't matter if it's a Pro/Toolkit, J-Link, or VB API application. The limit is around 58-60 connections. That might sound a little high, but if you run VB API programs from an Excel macro over and over again, you might hit that limit pretty quick. If you disconnect properly, there should be no limit to the number of times you can connect to a Pro/Engineer session.

You'd think that with disconnecting being so important for asynchronous applications, it would deserve some special mention in the documentation. Well, you'd be wrong. Sure, the Disconnect() method is mentioned along with the various flavors of the Connect() method, but there is no mention of the importance of disconnecting. You have been warned.

It it weren't broke, they wouldn't have fixed it

All is not perfect in Pro/Engineer VB land however. Don't waste your time with the C000 build of Wildfire 4. The COM server that comes with the install won't allow any connections.

Another more minor issue has to do with "text message" files that nearly all Pro/Engineer API's use. The "text" directory containing these files would normally be defined in a protk.dat file. This is not used for asynchronous applications. Instead, it is specified as part of the connection process. All of the Connect() methods have an argument that is supposed to identify the "text" directory.

For some reason, this argument is ignored by the VB API. As a result, only files in subdirectories of the Pro/Engineer install can be used (Proe/text/usascii if your language is english). It's not a major problem, but definitely an inconvenience if distributing your code to other users and/or companies.

"Happiness equals reality minus expectations" -Tom Magliozzi

Although I question the choice of an EXE based COM server, on the whole, PTC did a great job making the VB API fully functional and very consistent with its sibling API's (Pro/WebLink and J-Link). While I find that Visual Basic has a kludgey syntax and a somewhat broken object model, the API itself gets the job done.

Monday, June 9, 2008

Pro/WebLink: Send Excel Data To Pro/ENGINEER Drawing Tables and Back Again - Part 1

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