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.
|
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.
Last Updated 2/28/2024 9:42:38 AM