Free Support Forum - aspose.com

Smart Markers and datatables with multiple rows

Hi,

I have set up a template with a number of smart markers and defined a dataset with 3 datatables and the relationships between them. Table 1 has a one to one relationship with table 2 and table 2 has a one to many relationship with table 3.

However, only the first row of datatable 3 is being written into the Excel file with the second row not being output although multiple rows are present in datatable 3.

Any ideas?

Thanks,

David

Dear David,

Which version are you using? Could you please upload your template here?

Laurence,

Version 3.0.1.1. I’ve uploaded the template. Column E is the one that there are multiple values.

Thanks,

David

Hi David,

I used the following code to test this issue and all work fine. Could you try it in your place?

ExcelDesigner excel = new ExcelDesigner();
excel.Open("d:\\bug\\input.xls");

DataSet ds = new DataSet();

DataTable dt = new DataTable();
dt.TableName = "EVENT";
dt.Columns.Add("EVENT_ID", typeof(int));
dt.Columns.Add("EVENT_Date", typeof(DateTime));
dt.Columns.Add("EVENT_DETAILS", typeof(string));

DataRow dr = dt.NewRow();
dr[0] = 1;
dr[1] = DateTime.Now;
dr[2] = "Hello world";

dt.Rows.Add(dr);

dr = dt.NewRow();
dr[0] = 2;
dr[1] = DateTime.FromOADate(12345);
dr[2] = "Test";

dt.Rows.Add(dr);

dr = dt.NewRow();
dr[0] = 3;
dr[1] = DateTime.FromOADate(2222.34);
dr[2] = "Laurence";

dt.Rows.Add(dr);

ds.Tables.Add(dt);

dt = new DataTable();
dt.TableName = "EVENT_DETAIL";
dt.Columns.Add("Event_type_ID", typeof(int));

dr = dt.NewRow();
dr[0] = 1000;

dt.Rows.Add(dr);

dr = dt.NewRow();
dr[0] = 1001;

dt.Rows.Add(dr);

ds.Tables.Add(dt);


dt = new DataTable();
dt.TableName = "EVENT_DETAIL_ACTION";
dt.Columns.Add("Event_detail_action_ID", typeof(int));

dr = dt.NewRow();
dr[0] = 9998;

dt.Rows.Add(dr);

dr = dt.NewRow();
dr[0] = 9999;

dt.Rows.Add(dr);

ds.Tables.Add(dt);


excel.SetDataSource(ds);
excel.Process();


I use the latest version v3.0.2.

Hi Laurence,

Thanks for the example. I have installed the latest version of Aspose.Excel and have tried it and attached the results. Whilst all the data is being written out there is a gap between 1000 and 1001 that I don’t understand.

I have also had another look at my original output and have found the “missing” data - it is being written 70 rows away down from where I was expecting it.

David

Laurence,

I have also modified your code so that it is similar to how my dataset is defined:


ExcelDesigner excel = new ExcelDesigner();

excel.Open(“c:\aspose\input.xls”);

DataSet ds = new DataSet();

DataTable dt = new DataTable();

dt.TableName = “EVENT”;

dt.Columns.Add(“EVENT_ID”, typeof(int));

dt.Columns.Add(“EVENT_Date”, typeof(DateTime));

dt.Columns.Add(“EVENT_DETAILS”, typeof(string));

DataRow dr = dt.NewRow();

dr[0] = 1;

dr[1] = DateTime.Now;

dr[2] = “Hello world”;

dt.Rows.Add(dr);

dr = dt.NewRow();

dr[0] = 2;

dr[1] = DateTime.FromOADate(12345);

dr[2] = “Test”;

dt.Rows.Add(dr);

dr = dt.NewRow();

dr[0] = 3;

dr[1] = DateTime.FromOADate(2222.34);

dr[2] = “Laurence”;

dt.Rows.Add(dr);

ds.Tables.Add(dt);

dt = new DataTable();

dt.TableName = “EVENT_DETAIL”;

dt.Columns.Add(“Event_ID”, typeof(int));

dt.Columns.Add(“Event_Detail_ID”, typeof(int));

dt.Columns.Add(“Event_type_ID”, typeof(int));

dr = dt.NewRow();

dr[0] = 1;

dr[1] = 1;

dr[2] = 1000;

dt.Rows.Add(dr);

dr = dt.NewRow();

dr[0] = 2;

dr[1] = 2;

dr[2] = 1001;

dt.Rows.Add(dr);

dr = dt.NewRow();

dr[0] = 3;

dr[1] = 3;

dr[2] = 1002;

dt.Rows.Add(dr);

ds.Tables.Add(dt);

dt = new DataTable();

dt.TableName = “EVENT_DETAIL_ACTION”;

dt.Columns.Add(“Event_Detail_Id”, typeof(int));

dt.Columns.Add(“Event_detail_action_ID”, typeof(int));

dr = dt.NewRow();

dr[0] = 1;

dr[1] = 9990;

dt.Rows.Add(dr);

dr = dt.NewRow();

dr[0] = 1;

dr[1] = 9991;

dt.Rows.Add(dr);

dr = dt.NewRow();

dr[0] = 2;

dr[1] = 9992;

dt.Rows.Add(dr);

dr = dt.NewRow();

dr[0] = 2;

dr[1] = 9993;

dt.Rows.Add(dr);

ds.Tables.Add(dt);

ds.Relations.Add(“EVENT_EVENT_DETAIL”,

ds.Tables[“EVENT”].Columns[“EVENT_ID”],

ds.Tables[“EVENT_DETAIL”].Columns[“EVENT_ID”]);

ds.Relations.Add(“EVENT_DETAIL_EVENT_DETAIL_ACTION”,

ds.Tables[“EVENT_DETAIL”].Columns[“EVENT_DETAIL_ID”],

ds.Tables[“EVENT_DETAIL_ACTION”].Columns[“EVENT_DETAIL_ID”]);

excel.SetDataSource(ds);

excel.Process();


And attached the results of running this. I will attach in a new message what I was expecting.

David

Laurence,

Since it didn’t get attached in the last message this file contains the actual and my expected results.

Thanks,

David

Hi David,

Aspose.Excel can only process the data in your dataset with the smart markers. It cannot process the relationship between them.

You can try to process datatable 1 and 2 with smart markers, insert extra rows, then populate datatable3.

Laurence,

Thanks for the suggestion.

David