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);
Comments are closed.