Read and Write from Excel using .NET

<< Click to Display Table of Contents >>

Navigation:  Bizagi Studio > Process wizard > Integrate > Application integration > Integrating APIs and extending Bizagi > Read and Write from Excel >

Read and Write from Excel using .NET

In the following example, we will use the Component Library feature to read and write an Excel file using a .NET-based environment.

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.

 

What you need to do in Bizagi

To read or write an Excel file, keep in mind the following steps:

 

1.Create a Base Library

Create a component in .NET in which, the file to be read or write it is loaded by the component and its inner content will be transformed into an XML structure.

 

2.Import your Base Library into Bizagi

Having built the component file in our previous step, we will register the resultant DLL in Bizagi's component library.

 

1.Create a Base Library

The first step is to create a .Net solution in which, you can load an Excel file, read its inner data, write a new file and transform a data set into an XML structure.

 

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.

 

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;
}  

 

2.Import your Base Library into Bizagi

Once you have compiled your solution, add the Base library to your Bizagi project.

For more information refer to Component Library example for .NET edition.

 

Examples

 

1.Load data from an Excel file into a Bizagi Entity

Create an expression where you want to perform the excel file reading.

Within this rule, implement the following code to save data 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);  

 

2.Create and write an Excel file with data from Bizagi

Create a rule where you want to write the Excel file.

Within this rule, implement the following code to write data from a Bizagi Entity to an Excel file 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.