ParseExcel.g

Description

This script shows how to read an array of data into a DataHub script from a source such as an Excel worksheet, and then parse and extract values as necessary. The values in this example are message strings that correspond with an alarm, but they could be any value. The results of the triggered alarm are written to a text file.

Required setup

You can test the script by manually changing the alarm point values using the DataHub's Data Browser window. If you want to see output to the Script Log as well as the output file, un-comment lines 106, 107, 113, and 114.

This script assumes that the data for the alarm points comes from an outside data source connected to the DataHub via some other configuration.

Code

The code for this and other example scripts can be found in the DataHub distribution archive, typically at one of these locations: C:\Program Files\Cogent\OPC DataHub\scripts\ C:\Program Files\Cogent\Cascade DataHub\scripts\ Please refer to Section 3.1, “How to Run a Script” for more information on using scripts.

/* All user scripts should derive from the base "Application" class */

require ("Application");

/* Get the Gamma library functions and methods for ODBC and/or
 * Windows programming.  Uncomment either or both. */

//require ("WindowsSupport");
//require ("ODBCSupport");

/* Applications share the execution thread and the global name
 * space, so we create a class that contains all of the functions
 * and variables for the application.  This does two things:
 *   1) creates a private name space for the application, and
 *   2) allows you to re-load the application to create either
 *      a new unique instance or multiple instances without
 *      damaging an existing running instance.
 */

class ParseExcel Application
{
    domain = "default";
	
    // The point containing the alarm table from Excel.  This
    // is chosen from the DDE configuration tab in the OPC
    // DataHub properties.
    pt_alarm_table;
    pt_time_stamp;
	
    // The alarm lookup table.  We parse the table we get from
    // Excel into a more efficient lookup table.
    alarm_lut = make_array(0);
	
    // The name of the log file.
    log_file_name = "c:/tmp/alarmlog.txt";
	
    // The file handle to the open file
    log_file;
}

/*
 * Hold information for one alarm entry in the Excel spreadsheet
 */
class AlarmSpec
{
    tagname;
    point;
    description;
    eventid;
}

method AlarmSpec.constructor (domain, tag, description)
{
    .tagname = tag;
    .description = description;
    .point = symbol(string(domain, ":", tag));
}

/*
 * Comparison function for sorting.  We don't really need to sort unless
 * we plan to write code that looks up an alarm by name in this table.  It
 * is more efficient to use the event handler (.OnChange) function to map
 * a point change to its alarm specification
 */
function CmpAlarmSpecs (alarm1, alarm2)
{
    symcmp (alarm1.point, alarm2.point);
}

method ParseExcel.NewAlarmTable(value)
{
    local    rows = string_split (string(value), "\r\n", 0);
    local    columns;
    local    tagsym;
	
    with alarm in .alarm_lut do
    {
       	.RemoveChange (alarm.eventid);
    }
	
    .alarm_lut = make_array(0);
    with row in rows do
    {
       	columns = list_to_array (string_split (row, "\t", 0));
        .alarm_lut[length(.alarm_lut)] = new AlarmSpec(.domain, columns[0], columns[1]);
    }
    .alarm_lut = sort (.alarm_lut, CmpAlarmSpecs);
	
    with alarm in .alarm_lut do
    {
        datahub_command (string ("(create ", stringc(alarm.point), " 1)"), 1);
        alarm.eventid = .OnChange (alarm.point, `(@self).AlarmOccurred (@alarm, value));
    }
}

/* 
 * This method is called whenever the alarm condition point
 * changes in the OPC server.
 */
method ParseExcel.AlarmOccurred(alarm, value)
{
    if (value != 0)
    {
       	writec (.log_file, format ("%-20s%-12s%s\n", $default:time_stamp,
                alarm.tagname, alarm.description));
       	//princ (format ("%-20s%-12s%s\n", $default:time_stamp,
       	//		alarm.tagname, alarm.description));
    }
    else
    {
       	writec (.log_file, format ("%-20s%-12s%s cleared\n", $default:time_stamp,
                alarm.tagname, alarm.description));
       	//princ (format ("%-20s%-12s%s cleared\n", $default:time_stamp,
       	//		alarm.tagname, alarm.description));
    }
    flush (.log_file);
}

/* Write the 'main line' of the program here. */
method ParseExcel.constructor ()
{
    .log_file = open (.log_file_name, "a");
    if (!.log_file)
    {
       	MessageBox (0, string ("Could not open alarm log file: ", .log_file_name),
                               "Error opening file", 0);
    }
    else
    {
       	// Set the point name for the alarm table coming from Excel
       	.pt_alarm_table = symbol (string (.domain, ":", "alarm_table"));
       	.pt_time_stamp = symbol (string (.domain, ":", "time_stamp"));
		
       	datahub_command (string ("(create ", stringc(.pt_alarm_table), " 1)"), 1);
       	datahub_command (string ("(create ", stringc(.pt_time_stamp), " 1)"), 1);
		
       	// Whenever somebody changes the Excel spreadsheet, update the
       	// alarm table.
       	.OnChange (.pt_alarm_table, `(@self).NewAlarmTable(value));
		
       	// If we already have a value for the alarm table point, create the
       	// alarm table.
       	if (!undefined_p(eval(.pt_alarm_table)) &&
       	    string_p(eval(.pt_alarm_table)))
                .NewAlarmTable (eval(.pt_alarm_table));
    }
}

/* Any code to be run when the program gets shut down. */
method ParseExcel.destructor ()
{
    if (.log_file)
       	close (.log_file);
}

/* Start the program by instantiating the class.  If your
 * constructor code does not create a persistent reference to
 * the instance (self), then it will be destroyed by the
 * garbage collector soon after creation.  If you do not want
 * this to happen, assign the instance to a global variable, or
 * create a static data member in your class to which you assign
 * 'self' during the construction process.  ApplicationSingleton()
 * does this for you automatically. */
ApplicationSingleton (ParseExcel);