Overview
With Bizagi, you can read data from an Excel file (for example, to populate an entity or collection) or write data to an Excel file (for example, to create a spreadsheet with information stored in Bizagi).
To do this, use the Component Library to create a custom component that integrates with Excel files.
For more information, refer to Component library.
|
You can also create Excel files without programming by using Document templates. |
What you need to do
If you want to create custom code to manage Excel files, follow these steps:
1.Create your Excel component
Create a component that uses Excel libraries or APIs to read and write data in xls or xlsx files.
2.Import this component into Bizagi
After building 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 for either scenario (reading from or writing to an Excel file).
Example
The following example illustrates one possible approach. Other integration approaches are also supported.
Reading or writing an Excel file follows a similar structure to reading or writing a database table through SQL queries.
The following examples are implemented using OLE DB.
1. Create your Excel component
Create a class library that relies on Excel APIs or libraries to read and write data in Excel files.
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 DataSet into an XML structure (or vice-versa).
The following sample function converts a DataSet into an XML structure that can be processed by 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 about building a solution to read and write Excel files, refer to http://www.codeproject.com/Tips/813187/Csharp-Read-and-Write-Excel-xls-and-xlsx-Files-Con. The method presented above can be used to transform a DataSet into 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.
3. Create a rule that uses the methods in your component
The following examples illustrate 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, keep the following in mind:
•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$]: Use this structure to reference the worksheet that contains the data, that is, the worksheet name enclosed in square brackets and ending with $.
Last Updated 10/21/2026 12:50:41 PM