I vastly prefer Javascript over VBScript for many of the reasons describe by Joel On Software and by this MSDN article. Many projects that I work on use Excel for simple data relationships. Most of the data is so simple that it does not warrant a full SQL database, but a single table (spreadsheet) will suffice. I use the spreadsheets to generate files for multiple systems in some cases. For example, in an embedded turbine governor project I used a spreadsheet to map the tagnames to the communication details (rSpeed1 = 32025). The spreadsheet generated a C source file for the embedded controller, an XML file for the configuration software, and a mapping file for the help documentation. Using the spreadsheet helped keep three distinct systems synchronized.

I still receive a lot of Excel files from customers and I usually need to process and filter the spreadsheets. I receive files with DCS tag names and I often need to filter the list. The auto-filter in Excel is fine for two or three types, but I usually need to filter based on more than ten regular expression filters. The following is an example of a Javascript file I used to filter the spreadsheet:

// Constants
var INITIAL_ROW = 3;                    // Two rows of header
var DELETED_COLUMN_NAME = "DELETED";
var FILTER_COLUMN_NAME = "INST_NO";
var INCLUDE_COLUMN = [
     "INST_NO"
    ,"PID_SHT"
    ,"INTOOLS__Loop_No"
    ,"INST"
    ,"DESCRP1"
    ,"DESCRP2"
    ,"HI_SCALE"
    ,"ENG_UNITS"
    ,"ALMLL"
    ,"ALML"
    ,"ALMH"
    ,"ALMHH"
    ,"SET_POINT"
];
var INCLUDE_TAGPREFIX = [
    // Analog Inputs
     "AI"
    ,"FI"
    ,"LI"
    ,"PI"
    ,"TI"

    // DCS Calculations
   
    // Controllers
    ,"AC"
    ,"FC"
    ,"LC"
    ,"PC"
    ,"TC"
    ,"AIC"
    ,"FIC"
    ,"LIC"
    ,"PIC"
    ,"TIC"
   
    // Hand Switch PV Flag
    ,"HS"
    ,"PVFL"
];


// Basic Excel object
var Excel = {
    _App : null
    ,_created : false
    ,Constants : {
         pause : 0
        //Excel.XlDeleteShiftDirection
        ,xlShiftToLeft    : 0xFFFFEFC1
        ,xlShiftUp        : 0xFFFFEFBE
    }

    ,newApp : function() {
        this._App = new ActiveXObject("Excel.Application");
        this._created = true;
        return this._App;
    }

    ,getApp : function() {
        this._App = GetObject("","Excel.Application");
        return this._App;
    }

    ,_quit : function() {
        if (this._App)
        {
            this._App.Quit();
            this._created = false;
            this._App = null;
        }
        return;
    }

    ,bye : function() {
        if (this._created)
            this._quit();
        this._App = null;
    }
};


// Load the header row
function getHeaderRow(sheet, table)
{
    if (sheet)
    {
        var row = 1;
        var lastCol = sheet.UsedRange.Columns.Count;

        for (var col = 1; col <= lastCol; ++col)
        {
            var title = "" + sheet.Cells(row, col).Text;
            table[title] = col;
            table["_" + col] = title;
        }
    }
}

function ProcessWorksheet(sheet, rowFunction, map)
{
    if (sheet)
    {
        var lastRow = sheet.UsedRange.Rows.Count;
        var row = INITIAL_ROW;
        while (row <= lastRow)
        {
            // If the row does not match, it is deleted and
            // do not move to the next row as the rows are shifted up.
               if ( rowFunction(sheet, row, map) )
                   ++row;
               else
                   --lastRow;
           }
    }
}

function matchTagPrefixSetup(prefixArray)
{
    var result;
    if (prefixArray)
    {
        result = new Array(prefixArray.length);
        for (var i = 0; i < prefixArray.length; ++i)
        {
            result[i] = new RegExp("^" + prefixArray[i] + "[0-9]");
        }
    }
    return result;
}

// Hide row if it does not match any filter prefix
function processRowFunction(sheet, row, data)
{
    var match = false;
    data.rowsProcessed += 1;
    var text = sheet.Cells(row, data.deleteCol).Value;
    if (!text)
    {
        text = sheet.Cells(row, data.matchCol).Value;
        for (var i = 0; i < data.prefixes.length; ++i)
        {
            var a = data.prefixes[i].exec(text);
            if (a != null)
            {
                match = true;
                break;
            }
        }
    }

    if (!match)
    {
        sheet.Cells(row, data.matchCol).EntireRow.Delete(Excel.Constants.xlShiftUp);
        data.rowsDeleted += 1;
    }
   
    return match;
}

function main(args)
{
    // Attempt to get an open instance of Excel
    var app = Excel.getApp();
    if (app)
        var sheet = app.ActiveSheet;

    if (sheet)
    {
        // Get an array of RegExp for the matching prefixes
        var prefixes = matchTagPrefixSetup(INCLUDE_TAGPREFIX);

        // Get the header row
        var table = new Object();
        getHeaderRow(sheet, table);
        var matchCol = table[FILTER_COLUMN_NAME];
        var deleteCol = table[DELETED_COLUMN_NAME];

        // Build the row process function
        var map =
        {
             "prefixes"        : prefixes
            ,"matchCol"        : matchCol
            ,"deleteCol"       : deleteCol
            ,"rowsProcessed"   : 0
            ,"rowsDeleted"     : 0
            ,"startTime"       : null
            ,"finishTime"      : null
            ,"elapsedTime"     : null
        };

        map.startTime = new Date();

        // Process the worksheet
        app.ScreenUpdating = false;
        ProcessWorksheet(sheet, processRowFunction, map);
        app.ScreenUpdating = true;

        map.finishTime = new Date();
        map.elapsedTime = map.finishTime - map.startTime;
       
        // Statistics
        WScript.Echo("Rows Processed: " + map.rowsProcessed);
        WScript.Echo("Rows Deleted: " + map.rowsDeleted);
        WScript.Echo("Elapsed Time: " + map.elapsedTime / 1000 + " seconds");
    }

    // Cleanup
    sheet = null;
    myApp = null;
    Excel.bye();
}

// Program entry point
main(WScript.Arguments);

Comment Section

Comments are closed.