Extension: Aspose - quandis/qbo3-Documentation GitHub Wiki

Aspose provides a suite of tools to manipulate Microsoft Office documents on a server. QBO leverages several of these components.

Module Interface Description
AsposeWordGenerator IGenerator Execute mail merges against Microsoft Word templates. Data must be in the form of a DataSet.
AspenXmlGenerator IGenerator Execute mail merges against Microsoft Word templates. Data may be in the form of a XmlReader.
ExcelGenerator IGenerator Execute mail merges against Microsoft Excel templates. Useful when native ACE Drivers cannot read the Excel spreadsheet correctly.
AsposeExcelEngine IImport Reads data from Excel using Aspose Cells,
ExcelEngine IScore Performs analytics leveraging Excel modelling.

AsposeWordGenerator

This plugin performs a Word mail merge. The data source must be in the form of a DataSet, so power users must ensure that the method signature being used contains all the data required by the document.

AsposeXmlGenerator

This plugin enables Word mail merges without the need to write custom queries. The standard Summary operation will generally have all the data needed for even very complex mail merges. Examples of merge fields:

Example Description
Address Merges the first instance of an Address element.
BorrowerItem.1.LastName Merges the first borrower's LastName element.
BorrowerItem.-.LastName Merges the last borrower's LastName element.
LoanInformation.FundsPosted Merges a FundsPosted user-defined field from a task called Loan Information.

ExcelGenerator

Similar to the ExcelEngine below, this 'mail merges' data into Excel. You can include pivot tables, charts, and any other standard Excel components in your template. See the examples below for injecting data into Excel.

ExcelEngine

The Excel Engine plugin leverages Microsoft Excel spreadsheets to perform calculations. The Excel Engine plugin handles two things:

  • Injecting data into the spreadsheet, so Excel can perform calculations, and
  • Extracting data out of the spreadsheet, storing this data in QBO's ScoreItem table

To leverage this plugin:

  • Ensure the qbo.Score.AsposeCells plugin is deployed
  • Ensure the Scoring Excel Engine package has been imported
  • Create a new ScoreTemplate, ensuring you select ExcelEngine from the Score Engine drop down list
  • Upload an Excel spreadsheet (.xlsx file) as the Render attachment for the template

Injecting Data into Excel

There are two methods of injecting QBO data into Excel:

  • Individual data points cell-by-cell, or
  • Entire data tables

Individual Data Points

The simplest method of injecting data into Excel is to enter a value starting with ?=:

Example Description
?=UPBAmount Map the Loan.UPBAmount into a cell.
?=Borrower.1.LastName Map the Borrower.LastName of the first borrower associated with the record being scored.
?=MyTask.MyField Map the user-defined field MyField from the user-defined task MyTask.
?=//Some/Crazy/XPath/Expression Evaluate the XPath expression against the parent record's Summary output.

If one enters a ?= expressions into a cell's value, references to this cell may result in the dreaded #VALUE! errors. An alternative is to add a Name with a Comment, where the Comment contains the same ?= expression above. To add a name and comment to a cell, either:

  • Right-click on the cell, and choose Define Name, or
  • From the Formulas ribbon, choose Name Manager

Injecting Entire Tables

To inject tables into your spreadsheet, in any cell, enter a method signature beginning with ?=. For example:

Expression Description
?=Person/Search?OrderBy=LastName&SqlColumns=FirstName,LastName,Address,Person,PersonID List of users.
?=Valuation/Search?Object=Loan&ObjectID={LoanID} Valuations associated with a Loan.
?=LedgerItem/Search?DisplaySize=5&OrderBy=-LedgerItemID Random ledger items.

The method signatures may use string substitution, including any data from the score's parent object, or the score row itself. For example, if you are scoring a Loan, you may substitute any column from the Loan table. If you are scoring a Person, you may substitute any column from the Person table.

When the data is injected, spreadsheet cells to the right of the method signature cell will be overwritten with the resulting data. However, spreadsheet rows below the method signature line will remain in place; each row after the first row of data will cause a new spreadsheet row to be inserted. For example:

this Excel template

Sample Score
?=Person/Search?... Goodbye cruel world
Hello World

will result in this Excel result

Sample Score
123 John Doe ...
124 Mary Smith ...
{additional rows}
Hello World

Note that the 'Goodbye cruel world' cell was overwritten with 'John', but the 'Hello World' row remains intact.

Injecting Matrix Values

Rules implemented in the matrix module can be used in Excel spreadsheets using this pattern:

?=Matrix:{Matrix}:{Output}

For example:

?=Matrix:Calculation Matrix:InterestFloor

When the ExcelEngine encounters such an expression, the rules are evaluated against whatever the Score is associated with, and the best matching rule outputs will be injected into the matching cell(s).

Extracting Data from Excel to SQL

When defining an Excel spreadsheet as a scoring model, a named range called ScoreValue will be extracted and saved in the appropriate Score column depending on the ScoreTemplate.ReturnType:

  • money: saved to ScoreMoney
  • int: saved to ScoreInt
  • float: saved to ScoreFloat
  • boolean: saved to ScoreBoolean
  • date: saved to ScoreDate
  • string: saved to ScoreChar
  • enumerated: saved to ScoreChar

A ScoreTemplate can comprise zero or more ScoreTemplateItem rows, which are individual data points that contribute to a score. In the case of the Excel Engine plugin, each ScoreTemplateItem can be mapped to a cell in the spreadsheet by setting the name of the cell to match the name of a ScoreTemplateItem. For example, assume you have the following 'ScoreTemplateItem` rows defined for a template:

  • Per Diem
  • NPV
  • Confidence

In your spreadsheet, choose a cell (any cell in any sheet) that will represent the value to populate your ScoreItem with. The cell will typically be an Excel formula, and may leverage any data injected into the spreadsheet you wish. Select the cell, and change the 'name' of the cell to match the safe name of the ScoreTemplateItem. Safe name is the ScoreTemplateItem.ScoreTemplateItem, with spaces and special characters removed. For example, the safe name of 'Per Diem' is 'PerDiem'.

Excel Engine and Score/Calculate

When Score/Calculate is called, the following happens:

  • A spreadsheet is created from the 'template' uploaded as the ScoreTemplate.RenderID document
  • Each method signature is found, substituted, executed, and the method signature cell is overwritten with the resulting data
  • For each defined ScoreTemplateItem, if the spreadsheet contains a named cell matching the ScoreTemplateItem safe name, the cell's value is saved to the ScoreItem
  • The final spreadsheet is copied to the default imaging store, and associated with the Score.RenderID

Notes: Method signatures must specify a method that returns a DataSet (or DataReader); XmlReaders are not supported