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.