Read and Write from Excel

<< Click to Display Table of Contents >>

Navigation:  Bizagi Studio > Process wizard > Integrate > Application integration > Integrating APIs and extending Bizagi >

Read and Write from Excel

Overview

With Bizagi, you may read from an Excel file (e.g to populate a table in Bizagi), or similarly write into an Excel file (e.g, create a spreadsheet with business information stored in Bizagi).

Doing this is achieved by using the Component library to write your own component that integrates with Excel files.

For more information, refer to Component library.

 

note_pin

Creating Excel files without programming, is also possible through the out-of-the-box features presented by the Document templates.

 

What you need to do

If you wish to create your custom code to manage Excel files, most likely to read from excel files, carry out the following steps:

 

1.Create your Excel component

Create a component that relies on Excel libraries/APIs so that you can interpret content in xls or xlsx files.

 

2.Import this component into Bizagi

When having built the component, register it in Bizagi's component library.

 

3.Create a rule that uses the methods in your component

Create a business rule in Bizagi to for either scenario (read/write) of your Excel File.

 

 

Example

The following example illustrates one way to achieve this (notice this is not the only approach to code integration with Excel).

This example is targeted specifically for projects running in a .NET platform.

Reading or Writing an excel file follows the same structure than reading or writing a DB table through SQL queries. The following examples are implemented using a .Net solution using OLE DB.

 

 

1. Create your Excel component

The first step is to create a .NET class library that relies on APIs/libraries to interpret content in Excel.

In this specific example and given that Bizagi can manage information in its data model through standard XML structures, the component is capable of transforming a data set into an XML structure (or vice-versa).

Therefore, the following is a sample public function that will create an Excel file from content stored in Bizagi:

 

public string DatasetToXML(DataSet data)
{
  string xmlpath = System.IO.Path.GetTempPath() + DateTime.Now.Ticks + ".xml";
  data.WriteXml(xmlpath);
  XmlDocument xml = new XmlDocument();
  xml.Load(xmlpath);
  if (File.Exists(xmlpath)  
  {
      File.Delete(xmlpath);
  }
  return xml.InnerXml;
}  

 

For more information, refer to http://www.codeproject.com/Tips/813187/Csharp-Read-and-Write-Excel-xls-and-xlsx-Files-Con (in order to build your own solution to read and write an Excel file and use the following method to made the transformation from DataSet to XML).

 

2.  Import this component into Bizagi

Once you have compiled your solution, add this component as your base library to your Bizagi project.

For more information about the steps needed for this, refer to Component Library example for .NET edition.

 

3. Create a rule that uses the methods in your component

The following examples illustrates the code needed in rules to use the sample component as implemented above.

 

3.1 Load data from an Excel file into a Bizagi Entity

Create an expression having the following code to save data directly in any Bizagi Entity.

 

// Create a new instance of the library
var path = "..." // Add the location of your Excel file, this location must be accessible from Bizagi.
var excel = new SolutionNameSpace.MainClass(path); // use the necessary parameters according to your solution
 
// Create and execute the query to be read
var query = "SELECT col1, col2, .... colN FROM [YourSheet$]" // Use '*' to invoke all columns
var dsData = excel.ReadingMethod(query); // use the necessary parameters according to your solution  
var xmlData = new XmlDocument();
xmlData.InnerXml = excel.DatasetToXml(dsData); //method mentioned above
 
// Load the XML previously created in the target Entity (EntityName)
var transformation = CHelper.getXSL("EntityName", "TrasformationName");
var xmlTransformed = CEntityXmlHelper.transformXmlAsString(xmlData.InnerXml,transformation);
CEntityXmlHelper.fromXmlToEntityWithScopes(Me,xmlTransformed);  

 

3.2 Create and write an Excel file with data from Bizagi

Create an expression having the following code to write data from a Bizagi Entity to an Excel file while using a template.

 

// Create a new instance of the library
var path = "..." // Add the location of your Excel template, this location must be accessible from Bizagi.
var excel = new SolutionNameSpace.MainClass(path); // use the necessary parameters according to your solution
 
// Perform the transformations
var xsdIn=CHelper.getXSD("EntityName","XSD_TransformationName"); // xsd to get Bizagi data
var xslIn=CHelper.getXSL("EntityName","XSL_TransformationName"); // xsl to transform Bizagi data into an XML structure  
xmlBizagi=CEntityXmlHelper.entityToStringXmlWithScopes(Me, xsdIn, xslIn); // Generate xml to load data into the excel file
excel.WritingMethod(xmlBizagi); //Write the Excel File,  use the necessary parameters according to your solution
NewFile = Me.addRelation("RelationName"); // Generate a new File in Bizagi
NewFile.setXPath("FileName", "FileName.xlsx");  
NewFile.setXPath("Data", excel.GetExcelContent());

 

For both examples presented above, take into count the following:

 

SolutionNameSpace: the namespace of your solution

MainClass: the class of your solution.

ReadingMethod: The method implemented to read the Excel file.

WritingMethod: The method implemented to write an Excel file.

Transformations: to know more about the transformations called above, refer to Bizagi's data model XML schemas.

[YourSheet$]: Keep this structure to call the Sheet of your excel file where the data is located, i.e  the name of the sheet ending with '$' and between Square Brackets.