Suggestion on how to store meta data with a smart marker cell?

Aspose.Cells Product Family
1

Hello,

I’m happily using Aspose.Cells to merge data from a DataTable to an Excel workbook. I’m wondering if you would give me a little advice on how to extend my solution.

What I want to do is to store a bit of meta data, in addition to the data merged with the cell. (What would be incredible would be a mail merge handler, much like Aspose.Words, but I don’t think that exists for Cells, right?) The meta data would be an Id and a FieldName from which the merged data is associated with.

For example, say I have a smart marker like this:

&=Contact.FirstName

Essentially what I want to do is to associate with that same cell the Id and FieldName from the data where that field value originated in the DataTable.

I’m considering the use of Cell Comments to store that information, but wanted ask whether you all had a suggestion as to (1) where to store meta data (comment, note, etc) and (2) how I could possibly get the WorkbookDesigner to capture this information (any ideas like a mail merge event handler?)

Any thoughts would be appreciated.

Mike

2

Hi Mike,

Yes, I think you may try to utilize comments associated with the cells for you requirement.

I have created a sample code which might give you some insight so that you can accomplish your task. Attached are the source files (input + output files). I used a template file containing a single smart marker in it @ A1 cell (in the first worksheet) i.e. &=Customers.CustomerID. I used Northwind database table i.e. Customers for my source datatable in the example.

Sample code:

OleDbConnection con = new OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=d:\\test\\Northwind.mdb");
con.Open();
OleDbCommand cmd = new OleDbCommand("Select * from Customers",con);
OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds,"Customers");
DataTable dt = ds.Tables["Customers"];
WorkbookDesigner wd = new WorkbookDesigner();
wd.Open("d:\\test\\bkmarkers.xls");
wd.SetDataSource(dt);
wd.Process(false);

Workbook workbook = wd.Workbook;
Worksheet worksheet = workbook.Worksheets[0];
Cells cells = worksheet.Cells;
Comments comments = worksheet.Comments;
for(int i = 0; i<dt.Rows.Count;i++)
{
int cnt = comments.Add(i,0);
Comment comment = comments[cnt];
comment.Note = dt.Rows[i]["City"].ToString();
}
wd.Save("d:\\test\\out_bkmarkers.xls");

Thank you.

3

Thanks very much for this. I appreciate it.

Mike