[rules-users] Decision table multiple sheets...
Sumeet Karawal
sumeet.karawal at tcs.com
Wed Dec 28 06:35:04 EST 2011
Hi,
Thanks all for the responses.
I was able to parse the sheets(I had sheets with only decision tables) with
the help of below code :
Pasting my code :
KnowledgeBase kbase = readBase();
StatefulKnowledgeSession ksession =
kbase.newStatefulKnowledgeSession();
ksession.insert(m1);
ksession.insert(c1);
ksession.insert(p1);
ksession.fireAllRules();
private static KnowledgeBase readBase()throws IOException, BiffException{
DecisionTableConfiguration dtconf =
KnowledgeBuilderFactory.newDecisionTableConfiguration();
dtconf.setInputType(DecisionTableInputType.XLS);
KnowledgeBuilder kbuilder =
KnowledgeBuilderFactory.newKnowledgeBuilder();
File xls = new File
("/home/sumeet/workspaceDrools5.2/DecisionTableExample.../src/com/sample/MemberDiscountRules.xls");
SpreadsheetCompiler compiler = new SpreadsheetCompiler();
Workbook w;
String sheetName = "";
FileInputStream in = null;
try {
w = Workbook.getWorkbook(xls);
for (Sheet sheet : w.getSheets()) {
sheetName = sheet.getName();
in = new FileInputStream(xls);
System.out.println("The sheet name is : "+ sheetName);
compileSheet(kbuilder, xls, compiler, sheetName, in);
}
}
catch (DecisionTableParseException e) {
System.out.println("Failed to parse spreadsheet " + sheetName +" "+
e);
}
KnowledgeBase kb = KnowledgeBaseFactory.newKnowledgeBase();
kb.addKnowledgePackages(kbuilder.getKnowledgePackages());
return kb;
}
private static KnowledgeBuilder compileSheet(KnowledgeBuilder kbuilder,
File xls, SpreadsheetCompiler compiler, String sheetName, FileInputStream
in) {
try {
String compiled = compiler.compile(in, sheetName);
kbuilder.add(ResourceFactory.newReaderResource(new StringReader
(compiled)), ResourceType.DRL);
System.out.println
("***************************************drl**************************************");
System.out.println(compiled);
}
catch (DecisionTableParseException dtpe) {
if (dtpe.getMessage().equals("No RuleTable's were found in
spreadsheet.")) {
System.out.println("No rule tables found in sheet {}" +
sheetName);
}
else {
throw dtpe;
}
}
return kbuilder;
}
}
Regards,
Sumeet
Mailto: sumeet.karawal at tcs.com
From: Michael Anstis <michael.anstis at gmail.com>
To: Rules Users List <rules-users at lists.jboss.org>
Date: 12/21/2011 11:05 PM
Subject: Re: [rules-users] Decision table multiple sheets...
Sent by: rules-users-bounces at lists.jboss.org
Using a KnowledgeBuilder defaults to using
org.drools.decisiontable.DecisionTableProviderImpl. This in turn uses
org.drools.decisiontable.SpreadsheetCompiler that delegates to
org.drools.decisiontable.parser.xls.ExcelParser in its compile method. The
delegation uses a ExcelParser constructor that does not parse multiple
sheets.
If you look at SpreadsheetCompiler and make changes to use the constructor
"public ExcelParser(final Map<String, List<DataListener>> sheetListeners)"
you should be able to create the applicable DRL for multiple sheets and
pass this String to a KnowledgeBuilder as a DRL resource type. The map is
keyed off the sheet name and values of
org.drools.decisiontable.parser.DefaultRuleSheetListener should be OK. Not
as user-friendly as using a KnowledgeBuilder for XLS types, but it might
provide a suitable "near to out of the box" hack.
Whilst it could be argued KnowledgeBuilder for XLS types should scan the
sheet for multiple sheets and use the correct constructor it is difficult
to ascertain which sheets are decision tables and which are simply present
to provide look-up data (as in some of the examples) without additional
meta-data. Parsing of sheets not meant as decision tables would cause a
compilation error and hence failure of all of the sheets. I suspect it is
for this reason the parsing of multiple sheets in a single workbook needs
some assistance from the developer.
On 21 December 2011 10:52, Swindells, Thomas <TSwindells at nds.com> wrote:
We have the following code to load multiple sheets from a single xls -
it's more involved than I would like and I'd agree with the sentiment
that drools should load all the sheets not just the first one.
It's brutely pulled from out codebase so I'll leave it as an exercise to
the reader to tidy it up for your usage,
Thomas
protected File loadXLS(KnowledgeBuilder kbuilder, Map<String, String>
compiledFiles, File xls)
throws KnowledgeBaseLoadException {
log.info("Loading spreadsheet from {}",
xls.getAbsolutePath());
DecisionTableConfiguration dtconf =
KnowledgeBuilderFactory.newDecisionTableConfiguration();
dtconf.setInputType(DecisionTableInputType.XLS);
SpreadsheetCompiler compiler = new
SpreadsheetCompiler();
Workbook w;
String sheetName = "";
FileInputStream in = null;
try {
w = Workbook.getWorkbook(xls);
for (Sheet sheet : w.getSheets()) {
in = new FileInputStream(xls);
sheetName = sheet.getName();
log.info("Compiling sheet {} from
{}", sheetName, xls.getAbsolutePath());
compileSheet(kbuilder,
compiledFiles, xls, compiler, sheetName, in);
IOUtils.closeQuietly(in);
}
}
catch (DecisionTableParseException e) {
throw new KnowledgeBaseLoadException
("Failed to parse spreadsheet " + sheetName, e);
}
//CHECKSTYLE:OFF
//Need to catch the DecisionTableParseException
exception
catch (RuntimeException e) {
throw new KnowledgeBaseLoadException
("Failed to import decision table " + sheetName, e);
}
//CHECKSTYLE:OFN
catch (BiffException e) {
throw new KnowledgeBaseLoadException
("Failed to read spreadsheet: " + e.getMessage(), e);
}
catch (IOException e) {
throw new KnowledgeBaseLoadException
("Failed to read spreadsheet: " + e.getMessage(), e);
}
finally {
IOUtils.closeQuietly(in);
}
}
private void compileSheet(KnowledgeBuilder kbuilder, Map<String,
String> compiledFiles, File xls,
SpreadsheetCompiler compiler, String
sheetName, FileInputStream in) {
try {
String compiled = compiler.compile(in, sheetName);
log.debug("Sheet {} compiled to:\n{}", sheetName,
compiled);
compiledFiles.put(xls.getName() + "#" + sheetName,
compiled);
kbuilder.add(ResourceFactory.newReaderResource(new
StringReader(compiled)), ResourceType.DRL);
}
catch (DecisionTableParseException dtpe) {
// TODO fix dirty hack ... multi sheet support
should be moved into drools
if (dtpe.getMessage().equals("No RuleTable's were
found in spreadsheet.")) {
log.info("No rule tables found in sheet
{}", sheetName);
}
else {
throw dtpe;
}
}
}
> -----Original Message-----
> From: rules-users-bounces at lists.jboss.org [mailto:rules-users-
> bounces at lists.jboss.org] On Behalf Of Wolfgang Laun
> Sent: 21 December 2011 10:38
> To: Rules Users List
> Subject: Re: [rules-users] Decision table multiple sheets...
>
> By definition (see the "Expert" manual) only sheet 1 is used for
generating
> rules.
>
> For controlling rule firing you may consider using any of the rule
attributes
> supported by spreadsheets, e.g., agenda-group.
>
> -W
>
> On 21/12/2011, Sumeet Karawal <sumeet.karawal at tcs.com> wrote:
> >
> > Hi,
> >
> > I am having my rules in a Decision Table (.xls)
> >
> > There I have 3 sheets for rules.
> >
> > Like in Sheet1 there are two Rule Tables... In Sheet2 there are 2 and
> > in
> > Sheet3 there is only 1.
> >
> > When I am creating the knowledgebase and firing rules, the rules that
> > are in the Sheet1 are getting fired. But the rules in other sheets
are
> > not getting fired.
> >
> > When I convert them to String to check using this code :
> >
> > String str = DecisionTableFactory.loadFromInputStream
> > (ResourceFactory.newClassPathResource
> > ("com/sample/MemberDiscountRules.xls").getInputStream(), dtconf);
> > System.out.println(str);
> >
> > I can see the rules that are present in Sheet1 only.
> >
> > Please anybody help me on this. How can I get the rules in other two
> > sheets to get fired.
> > Also how can we fire the rules of only a particular Rule table in the
> > decision table.
> >
> > Thanks & Regards,
> > Sumeet
> >
> > Mailto: sumeet.karawal at tcs.com
> >
> >
> > =====-----=====-----=====
> > Notice: The information contained in this e-mail message and/or
> > attachments to it may contain confidential or privileged information.
> > If you are not the intended recipient, any dissemination, use,
review,
> > distribution, printing or copying of the information contained in
this
> > e-mail message and/or attachments to it are strictly prohibited. If
> > you have received this communication in error, please notify us by
> > reply e-mail or telephone and immediately and permanently delete the
> > message and any attachments. Thank you
> >
> >
> >
> > _______________________________________________
> > rules-users mailing list
> > rules-users at lists.jboss.org
> > https://lists.jboss.org/mailman/listinfo/rules-users
> >
> _______________________________________________
> rules-users mailing list
> rules-users at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/rules-users
**************************************************************************************
This message is confidential and intended only for the addressee. If you
have received this message in error, please immediately notify the
postmaster at nds.com and delete it from your system as well as any copies.
The content of e-mails as well as traffic data may be monitored by NDS
for employment and security purposes. To protect the environment please
do not print this e-mail unless necessary.
NDS Limited. Registered Office: One London Road, Staines, Middlesex, TW18
4EX, United Kingdom. A company registered in England and Wales.
Registered no. 3080780. VAT no. GB 603 8808 40-00
**************************************************************************************
_______________________________________________
rules-users mailing list
rules-users at lists.jboss.org
https://lists.jboss.org/mailman/listinfo/rules-users
_______________________________________________
rules-users mailing list
rules-users at lists.jboss.org
https://lists.jboss.org/mailman/listinfo/rules-users
More information about the rules-users
mailing list