Working with drools using excel sheet supplied from outside project — part-1

3 min readJun 26, 2021

Drools is a great platform to execute the business rules. Its JAVA based and it runs on a Kie execution environment. RedHat also provides a UI based drools workbench to work with which people can use to create business rules and deploy them with a single click.

The workbench is free to use, but support is costly. Its a great tool to work with, but when it comes to saving cost, best is to containerize the environment and run it as a cloud native app. For writing rules, simple excel sheet can be used and its a great tool to process your data.

Drools need both rules and schema to process your data. You can write complex and simple rules in the excel sheet and process them using REST call. In the part-1 of this story, I’ll use schema and data as a part of the project and then later on enhance it with metadata based schema creation and supplying of data during the REST call along with rules in excel sheet.

Models Customer and CustomerType are defined as:

package com.company.vo;import lombok.Getter;
import lombok.Setter;
import lombok.ToString;@ToString @Getter @Setter
public class Customer {
    private CustomerType type;private int years;private int discount;public Customer (CustomerType type, int years) {
        this.type = type;
        this.years = years;
    }
}//-----------------------public enum CustomerType {
    INDIVIDUAL,
    BUSINESS;
}

The business logic is written in service layer as:

package com.company.service;import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;import lombok.extern.slf4j.Slf4j;
import org.drools.decisiontable.InputType;
import org.drools.decisiontable.SpreadsheetCompiler;
import org.kie.api.command.Command;
import org.kie.api.io.ResourceType;
import org.kie.api.runtime.ClassObjectFilter;
import org.kie.api.runtime.ExecutionResults;
import org.kie.api.runtime.StatelessKieSession;
import org.kie.internal.command.CommandFactory;
import org.kie.internal.utils.KieHelper;
import org.springframework.stereotype.Service;import com.company.vo.ApiRequest;
import com.company.vo.Customer;@Slf4j
@Service
public class RulesProcessingService {public List<Customer> applyRules(ApiRequest req) {InputStream is = null;
        try {
            is= new FileInputStream(req.getRulefilepath());
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        SpreadsheetCompiler sc = new SpreadsheetCompiler();
        String rules=sc.compile(is, InputType.XLS);StringBuffer drl = new StringBuffer(rules);
        log.info("drool file == " + drl);StatelessKieSession kSession = new KieHelper().addContent(rules, ResourceType.DRL).build().newStatelessKieSession();Customer customer = new Customer(CustomerType.BUSINESS, 2);log.info("Executing rules ...");List<Command> cmds = new ArrayList<>();
        cmds.add(CommandFactory.newInsert(customer));
        cmds.add(CommandFactory.newFireAllRules());
        cmds.add(CommandFactory.newGetObjects(new ClassObjectFilter(Customer.class), "output"));ExecutionResults results = kSession.execute(CommandFactory.newBatchExecution(cmds));List customer_out = (List<Customer>) (Collection<?>) results.getValue("output");log.info("rules output = " + customer_out);return customer_out;
    }
}

The code here reads the rules excel sheet as IO stream and then SpreadsheetCompiler API converts it to a drl rule. If you see the logs, you’ll notice the drl rule file printed and that way you can debug as well if your rules are correct or not, for e.g. this case drl file is as:

package com.company.rules;//generated from Decision Tableimport com.company.vo.Customer;import com.company.vo.Customer.CustomerType;// rule values at A9, header at A4rule "Individual > 3y"when$customer:Customer($customer.getType() in (CustomerType.INDIVIDUAL), $customer.getYears() >= (3))then$customer.setDiscount(15);end// rule values at A10, header at A4rule "Individual < 3y"when$customer:Customer($customer.getType() in (CustomerType.INDIVIDUAL), $customer.getYears() >= (0), $customer.getYears() < (3))then$customer.setDiscount(5);end// rule values at A11, header at A4rule "Business Any"when$customer:Customer($customer.getType() in (CustomerType.BUSINESS))then$customer.setDiscount(20);end