From Business Process Management, BPM and Workflow Automation Wiki | BizAgi BPMS

<keywords content="keywords"> integration excel, Microsoft Excel, Interop.Excel </keywords>

How to Integrate with Excel

It is not unusual for a project to require the transfer of data to/from an Excel file, either to generate a report, include information from an external system into the Bizagi data model or maybe even to execute a complex operation with an existing macro. Whichever the case, it will involve the use of a component that has to be registered in Bizagi in order to use it in an Expression (see Component Library).

A few "ready to use" components may be found by searching the Web. Although using these libraries is the quickest way to handle Excel files, some of the solutions do not have full compatibility with Excel and most (if not all) have to be purchased. An alternative option is to use Automation. Virtually all of the actions that can be performed manually through the user interface can also be performed by programming.

Microsoft Support contains several articles with examples of Automation to Excel:

• How to automate Microsoft Excel from Microsoft Visual C# .NET
• How to transfer data to an Excel workbook by using Visual C# 2005 or Visual C# .NET
• Considerations for server-side Automation of Office
• How To Improve the Performance of Office Automation Code
• How to automate Excel by using Visual C# to fill or to obtain data in a range by using arrays
• How to transfer XML data to Microsoft Excel 2002 or Microsoft Excel 2003 by using Visual C#
• Office application does not quit after automation from Visual Studio .NET client

Example

The following is an example of how Automation can be used to integrate Bizagi with Excel. It is assumed that the organization (a bank) has an Excel file which contains all the necessary operations to determine the monthly payment for a mortgage. Some information needed in the Excel file is extracted from Bizagi and the result is then stored in Bizagi. Download the file from here: MortgageCalculator.xls

There would be a form in Bizagi where the annual interest rate, mortgage term and amount is entered. After this information has been entered, the Calculate button is clicked; this will prompt an Expression to be executed whereby a method from a custom library is used to determine the monthly payment of the mortgage.

The Expression would have the following instructions:

//Get the information entered in the form
var rate = <MortgageApplication.AnnualInterestRate>;
var term = <MortgageApplication.MortgageTerm>;
var amount = <MortgageApplication.MortgageAmount>;

//Set the path of the Excel file
var file = "D:\\Temp\\MortgageCalculator.xls";

//Use the method of the component that receive as parameter the annual interest rate, the term
//in years, the amount of the mortgage and the path of the file.
//The result is saved in a variable
var payment = BizagiExcelIntegration.CInvokeExcel.MortgageMonthlyPayment(rate, term, amount, file);

//Save the result in an attribute included in the form as uneditable
Me.setXPath("MortgageApplication.MonthlyPayment", payment);

Additionally, is necessary to create the component used in this Expression. Using Microsoft Visual Studio .NET, create a new Class Library Project. For this example, the project is named BizagiExcelIntegration. Include a class named CInvokeExcel with the MortgageMonthlyPayment method. To test the example presented here, download and extract the component here: BizagiExcelIntegration.rar.

This is the code of the CInvokeExcel class:

using System;
using InteropExcel;

namespace BizagiExcelIntegration
{
    public class CInvokeExcel
    {
        public CInvokeExcel()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        public static double MortgageMonthlyPayment(double AnnualRate, double YearsTerm, double MortgageAmount, string FilePath)
        {
            //Create the Excel instance with the given file
            CExcelDataSourceSingleton cExcel = new CExcelDataSourceSingleton();
            cExcel.Target = FilePath;

            //Use SetCellValue to copy AnnualRate, YearsTerm and MortgageAmount in the Excel sheet
            cExcel.SetCellValue(1, "C4", AnnualRate);
            cExcel.SetCellValue(1, "C6", YearsTerm);
            cExcel.SetCellValue(1, "C8", MortgageAmount);

            //Use this object to receive the the value calculated by Excel
            object oRetVal = cExcel.GetCellValue(1, "C10");

            cExcel.CloseFile();

            double dReturn = Convert.ToDouble(oRetVal);

            return dReturn;
        }
    }
}

In the second line (using InteropExcel), means that this library uses another library, which is the one that contains the CExcelDataSourceSingleton class. It is a "middle" component between Microsoft.Office.Interop.Excel and the previous library; so the BizagiExcelIntegration project should have a reference to this other library. This compiled class may be downloaded and extracted from here: InteropExcel.rar.

When the project is built, these three components would be found in its output path: BizagiExcelIntegration.dll, InteropExcel.dll and Interop.Microsoft.Office.Interop.Excel.dll. All of these components have to be registered in Bizagi using the Component Library tool.

After all the necessary configurations, it is possible to test this exercise in the web application.

<comments />