Answered

Custom component to read Excel spreadsheet/ Error with XML Transformation

Hello, Guys!

I'm new to Bizagi studio and I'm trying to create a custom component, which can read an excel spreadsheet, save it into an entity and display it into a table.

I followed these steps:

http://help.bizagi.com/bpm-suite/en/index.html?excel_net.htm

What I did is the following:

* First I created two .NET methods - one to read the excel spreadsheet and the other to convert it into XML.

public class Class1

{

public static string path = @"C:\Users\stbgsffk3KAM\Desktop\test.xlsx";

public static string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";

public DataSet ReadingExcel() {

//String strExcelConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data Source=C:\Users\stbgsffk3KAM\Desktop\test.xlsx;" + "Extended Properties='Excel 8.0;HDR=Yes'";

OleDbConnection connExcel = new OleDbConnection(connStr);

OleDbCommand cmdExcel = new OleDbCommand();

cmdExcel.Connection = connExcel;

connExcel.Open();

DataTable dtExcelSchema;

dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

//connExcel.Close();

DataSet ds = new DataSet();

OleDbDataAdapter da = new OleDbDataAdapter();

string SheetName = dtExcelSchema.Rows[2]["TABLE_NAME"].ToString();

cmdExcel.CommandText = "SELECT B_N, OB_N, Product, PlanPrice, FinPrice, Unit From [" + SheetName + "]";

da.SelectCommand = cmdExcel;

da.Fill(ds);

return ds;

}

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;

}

}

* After that I created an entity called PriceList and generated a xml schema for it.

* On one of my forms I added an activity with the following expression:

var excel = new ExcelIntegration.Class1();

var dsData = excel.ReadingExcel();

var xmlData = new XmlDocument();

xmlData.InnerXml = excel.DatasetToXML(dsData);

var transformation = CHelper.getXSL("PriceList");

var xmlTransformed = CEntityXmlHelper.transformXmlAsString(xmlData.InnerXml,transformation);

CEntityXmlHelper.fromXmlToEntityWithScopes(Me,xmlTransformed);

I'm getting this error:

Unable to process the case:Error Evaluating Rule Test with id: 0f67ec93-993d-4f4d-a636-4578c5d851f5 - Error XSLT compile error. -Root element is missing..

My XML schema is:

<?xml version="1.0" encoding="utf-8"?>

<xs:schema attributeFormDefault="qualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">;

<xs:element name="PriceList">

<xs:complexType>

<xs:sequence>

<xs:element minOccurs="0" maxOccurs="1" name="Unit" type="xs:integer" />

<xs:element minOccurs="0" maxOccurs="1" name="OB_N" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="Product" type="xs:string" />

<xs:element minOccurs="0" maxOccurs="1" name="FinPrice" type="xs:decimal" />

<xs:element minOccurs="0" maxOccurs="1" name="PlanPrice" type="xs:decimal" />

<xs:element minOccurs="0" maxOccurs="1" name="B_N" type="xs:string" />

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:schema>

My question is do I need to have some kind of transformation between the two xml schemas - the one that Bizagi generated and the one that is coming from the component and how I'm supposed to do that.

Thank you in advance. I will really appreciate your help!!!

Albena Mincheva

Comments (1)

photo
1

Dear User,

This error happens because into the expression, it is invoking in a wrong way getXSL method. The method has two parameters: CHelper.getXSL([EntityName], [XSLName])

Regards