External Rules Coming From Excel
Home Product Company Customers Contact 
 Simple Rule Project with External Rules Defined in Excel Data Tables


Home
News
Overview
Examples
Methodology
Components
Rule Repository
Rule Templates
Architectures
Downloads
Documentation
Step-By-Step
Add-Ons
Fun Lab
Support
Services
Inside Track
Events
Presentations
What People Say


 

 

 

   

 


Rule Project "ExternalRulesFromExcel"

Step 1. Setting Up Rule Templates

Step 2. Defining Rule Tables in Excel Data Tables

Step 3. Creating and Executing External Rules from a Java Program

OpenRules allows you to keep your business rules in Excel data tables that correspond to the columns (conditions and actions) of Excel's templates based on which the proper rule tables will be executed. 

This project intends to produce greetings like "Good Morning, Mrs. Robinson!" based on the current time and different customer's attributes such as gender and marital status. In a way it is similar to the standard project "HelloJavaTemplates" but instead of using Excel-based rule tables it is using XML-based rule tables.

Step 1. Setting Up Rule Templates       ►top

The business logic for producing greetings and salutations is presented in the Excel file HelloTemplates.xls. The first template

Rules void defineGreeting(App app, int hour)
C1 C2 A1
min <= hour hour <= max

app.greeting =  greeting; 
int min int max String greeting
Hour From Hour To Set Greeting
    Unknown Greeting

specifies how to define different greetings (Good Morning, Good Afternoon, etc.) based on the hour of the day. If the parameter "hour" belongs to the interval [min;max] defined by a concrete rule then the attribute "greeting" of the parameter "app" will be set to the proper greeting. If no rules are satisfied, this multi-hit table will use the default greeting "Unknown Greeting". 

The second template

Rules void defineSalutation(App app, Customer c)
C1 C2 C3 A1
c.gender.equals(gender) c.maritalStatus.equals(status) c.age < age

app.salutation = salutation; 
String gender String status int age String salutation
Gender Marital Status Age Less Than Set Salutation
      Unknown Salutation

specifies how to define different salutations (Mr., Mrs., etc.) based on customer attributes Gender, Marital Status, and Age. If no rules are satisfied, this multi-hit table will use the default salutation "Unknown Salutation". 

Step 2. Defining Rule Tables in Excel Data Tables    ►top

We will create the main xls-file HelloRules.xls in the subdirectory "rules/mainl".  The first Data table  defines "greetingRules" that will be based on the template with the name "defineGreeting":

Data GreetingRule greetingRules
from to greeting
From To Greeting
0 11 Good Morning
12 17 Good Afternoon
18 22 Good Evening
23 24 Good Night

To access this table from java we define the following method:

Method GreetingRule[] getDefaultGreetingRules()
return greetingRules;

This data tale uses the datatype GreetingRules that is specified in the proper Java class:

public class GreetingRule {
	int from;
	int to;
	String greeting;
	
	public int getFrom() {
		return from;
	}
	public void setFrom(int from) {
		this.from = from;
	}
	public int getTo() {
		return to;
	}
	public void setTo(int to) {
		this.to = to;
	}
	public String getGreeting() {
		return greeting;
	}
	public void setGreeting(String greeting) {
		this.greeting = greeting;
	}
}

Similarly we create the second Data table "salutationRules":

Data SalutationRule salutationRules
gender maritalStatus maxAge salutation
Gender Marital Status Age Less Than Set Salutation
Female Married   Mrs.
Female Single   Ms.
Male     Mr.
Male Single 10 Little

and the proper method:

Method SalutationRule[] getDefaultSalutationRules()
return salutationRules;

This data tale uses the datatype SalutationRules that is specified in the proper Java class:

public class SalutationRule {

	String 	gender; 
	String  maritalStatus;
	String 	maxAge;
	String	salutation;

	public String getGender() {
		return gender;
	}
	public void setGender(String gender) {
		this.gender = gender;
	}

	public String getMaritalStatus() {
		return maritalStatus;
	}
	public void setMaritalStatus(String maritalStatus) {
		this.maritalStatus = maritalStatus;
	}

	public String getMaxAge() {
		return maxAge;
	}
	public void setMaxAge(String maxAge) {
		this.maxAge = maxAge;
	}

	public String getSalutation() {
		return salutation;
	}
	public void setSalutation(String salutation) {
		this.salutation = salutation;
	}
}

 

Step 3. Creating and Executing External Rules from a Java Program     ►top

All other modules are typical for basic rule projects. The main Java file RunExternalRulesFromXML.java is used to test the above rules:

import com.openrules.ruleengine.ExternalRules;
import com.openrules.ruleengine.OpenRulesEngine;

public class RunExternalRulesFromExcel {

	public static void main(String[] args)
	{ 
		// The first engine
		String xlsMainRules = "file:rules/main/HelloRules.xls";
	        OpenRulesEngine engine1 = new OpenRulesEngine(xlsMainRules);
	    
		GreetingRule[] greetingRules = (GreetingRule[])engine1.run("getDefaultGreetingRules");
		String[][] greetingGrid = new String[greetingRules.length][3];
		for (int i = 0; i < greetingRules.length; i++) {
			GreetingRule rule = greetingRules[i];
			greetingGrid[i] = new String[] 
                            { Integer.toString(rule.from), Integer.toString(rule.to), rule.greeting };
		}
		
		SalutationRule[] salutationRules = (SalutationRule[])engine1.run("getDefaultSalutationRules");
		String[][] salutationGrid = new String[salutationRules.length][4];
		for (int i = 0; i < salutationRules.length; i++) {
			SalutationRule rule = salutationRules[i];
			salutationGrid[i] = new String[] 
                            { rule.gender, rule.maritalStatus, rule.maxAge, rule.salutation };
		}
		// create external rules
		ExternalRules externalRules = new ExternalRules();
		externalRules.addRuleTable(
			"greetingRules", 	//table name 
			"defineGreeting", 	//template name
			greetingGrid
		);
		externalRules.addRuleTable(
			"salutationRules", 	//table name 
			"defineSalutation", //template name
			salutationGrid
		);
                // Display external rules  
		for (int i = 0; i < externalRules.getRuleTables().size(); i++) {
			System.out.println(externalRules.getRuleTables().get(i));
		}

		// The second engine 
		String fileName = "file:rules/main/HelloCustomer.xls";
		OpenRulesEngine engine2 = new OpenRulesEngine(fileName,externalRules);
		App app = (App) engine2.run("getDefaultApplication");
		engine2.run("generateGreeting",app);
		System.out.println("\nGenerated Greeting:");
		System.out.println(app.getResult());
		
	}
}

The first instance 'engine1" of the class OpenRulesEngine is based on the main Excel-file HelloRules.xls. We create an array greetingRules by executing the method "createExternalRules" to create external rules from the xml files:

GreetingRule[] greetingRules = (GreetingRule[])engine1.run("getDefaultGreetingRules");

Then we convert this array into a simple "greetingGrid" of the type String[][]. Similarly we create the grid "salutationRules".

Then we create an instance of ExternalRules and add two rule tables into it:

ExternalRules externalRules = new ExternalRules();
externalRules.addRuleTable(
   "greetingRules", //table name
   "defineGreeting", //template name
   greetingGrid
);
externalRules.addRuleTable(
   "salutationRules", //table name
   "defineSalutation", //template name
   salutationGrid
);

The second instance "engine2" of the OpenRulesEngine is using the main Excel-file HelloCustomer.xls and newly created external rules:

OpenRulesEngine engine2 = new OpenRulesEngine(fileName,externalRules);

The main file HelloCustomer.xls defines the Environment as follows:

Environment
import.java hello.*
import.static com.openrules.tools.Methods
include ../include/HelloTemplates.xls
include ../include/HelloData.xls

This application uses two simple Java beans:

Customer.java:

String name;

String maritalStatus;

String gender;

int age;

App.java:

Customer customer;

String greeting;

String salutation;

String result;

The proper instance of the Customer and App are created based on the Excel file HelloData.xls using these data tables:

Data App apps
customer.name customer.maritalStatus customer.gender customer.age
Customer Name Marital Status Gender Age
Robinson Married Female 24
Smith Single Male 19
     
     
Method App getDefaultApplication()  
return apps[0];  

And finally, the engine2 will execute rules by calling the method "run":

engine2.run("generateGreeting",app);

The proper method "generateGreeting" is described in the file HelloCustomer.xls in the following table:

Method void generateGreeting(App app) 
int hour = Calendar.getInstance().get(Calendar.HOUR_OF_DAY);
greetingRules(app, hour);
salutationRules(app, app.customer);
app.result = app.greeting + ", " + app.salutation + " " + app.customer.name + "!";
 

You may validate the entire rule project by double-clicking on the file "compile.bat".  Because the actual external rule tables "greetingRules" and "salutationRules" will become known only in run-time the proper OpenRules Validator may produce errors (warnings) about unknown rule tables. You may ignore these errors or you may explicitly inform OpenRules about this fact by adding an optional table to the file HelloCustomer.xls:

ExternalRules
greetingRules defineGreeting
salutationRules defineSalutation

To run the project you may double-click on the file "run.bat". Here is an expected output:

INITIALIZE OPENRULES ENGINE 5.3.0 (build 03092009) for [file:rules/main/HelloRules.xls]
IMPORT.JAVA=hello.*
ExternalRules greetingRules template defineGreeting
0 11 Good Morning 
12 17 Good Afternoon 
18 22 Good Evening 
23 24 Good Night 

ExternalRules salutationRules template defineSalutation
Female Married null Mrs. 
Female Single null Ms. 
Male null null Mr. 
Male Single 10 Little 

INITIALIZE OPENRULES ENGINE 5.3.0 (build 03092009) for [file:rules/main/HelloCustomer.xls]
External rules table: greetingRules
External rules table: salutationRules
IMPORT.JAVA=hello.*
IMPORT.JAVA=com.openrules.tools.Operator
IMPORT.STATIC=com.openrules.tools.Methods
INCLUDE=../include/HelloTemplates.xls
[../include/HelloTemplates.xls] has been resolved to [file:<..>/ExternalRulesFromExcel/rules/include/HelloTemplates.xls]
INCLUDE=../include/HelloData.xls
[../include/HelloData.xls] has been resolved to [file:<..>/ExternalRulesFromExcel/rules/include/HelloData.xls]

Generated Greeting:
Good Afternoon, Mrs. Robinson!

  

►top

 

  
 
Back Next
Copyright © 2004-2010 OpenRules, Inc. All rights reserved