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.