Various basic questions about Smart Markers

Aspose.Cells Product Family
1

Hello –

I’m starting to look into using Smart Markers and I have some basic questions that aren’t really clear to me after reading the page in the Wiki about Smart Markers.

On the wiki page, the example shows this for the structure of a smart marker:

&=DataSource.FieldName
&=[Data Source].[Field Name]
So, does “DataSource” refer to the name property of a DataSet? Or the name property of a DataTable within a DataSet?

What is the meaning of the square-brackets “[” in the second example? When would I use that syntax?

In my application, I’m hoping to use a DataSet that contains two DataTables. The DataTables are named “Master” and “Detail”. Would the syntax to access the various fields be something like:

&=Master.CompanyName
&=Master.City
&=Detail.OrderNumber
&=Detail.Price

Thanks for your guidance on using Smart Markers.


2

So, does "DataSource" refer to the name property of a DataSet? Or the name property of a DataTable within a DataSet?
It means DataTable.Name.

What is the meaning of the square-brackets "[" in the second example? When would I use that syntax?
Actually you don't need to use it. I design it first just for compatibility.

And you syntax for Master, Detail table is correct.

3

Hi,

Thanks for considering Aspose.

Well, A Smart Marker represents a single data point or value that gives a means to Aspose.Cells to place relevant data into different cells of the worksheet in a workbook. We make use of Designer spreadsheets in which we write smart markers into different cells. Normally a smart marker consists of DataSource and a Field Name and starts with "&=". The DataSource can be a DataSet, DataTable, DataView or an Object variable etc. And It refers to the WokbookDesigner.SetDataSource() overloaded method and you will use their related objects in it in the code. You can also make use of dynamic formulas that allows you to insert MS Excel's formulas into cells. Moreover, you may calculate totals and sub totals of any data field too. If your DataSource name or FieldName have space(s) between their names, e.g., "Orders Details", then you should put square brackets around it, I have attached two files one is designer template and the other is output file, you may check the smart tags, I also used some dynamic formulas.

And here is the code (I used Northwind access database), It will give you some insight:

OleDbConnection con = new OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=d:\\test\\Northwind.mdb");

con.Open();

OleDbCommand cmd = new OleDbCommand("Select * from [Order Details]",con);

OleDbDataAdapter da = new OleDbDataAdapter();

da.SelectCommand = cmd;

DataSet ds = new DataSet();

da.Fill(ds,"Order Details");

DataTable dt = ds.Tables["Order Details"];

WorkbookDesigner wd = new WorkbookDesigner();

wd.Open("d:\\test\\tempsmartmarker.xls");

wd.SetDataSource(dt);

wd.Process(false);

wd.Save("d:\\test\\smoutputfile.xls");

And in your application you are doing fine.

Thank you.

4

Thank you for your guidance on Smart Markers. It’s working well for me.

I have a follow-up question: I would like some help in coming up with a formula for Excel that would convert a text value to numeric, since all of the data that I’m passing into the Excel file through smart markers is text.

What I’d like to accomplish is something like this:
&=&=Value( &=Detail.SalesPrice )

I would like to call the Value function on the &=Detail.SalesPrice. Is it possible to accomplish all of this in one cell? I’ve seen that I could put the repeating dynamic formula in a different cell, but I really only want the value once.

I realize that most people would have data that actually uses data types, but I’m not that lucky!

Thanks for your thoughts.

5

Hi,

&=&=Value( &=Detail.SalesPrice )
No, you cannot use it in this way.

The following will work, if your smart tag "&=Detail.SalesPrice " is in the A column:

&=&=VALUE(A{r})

I think you can also format the smart marker tag in your template file before it is processed by Aspose.Cells. E.g., In MS Excel, you have embeded "&=Detail.SalesPrice" smart tag into A1 cell in the designer file, Right click on the cell, "Format Cells" and then apply your required format on this cell.

Thank you.

6

Hmm, well those ideas won’t work for me.

In your idea of using &=&=Value(A{r}), the challenge is that the merged data would have to be duplicated in column A, of course.

As for formatting the cell, while that’s true, since the merged data is coming as as Text, the formatting doesn’t apply (or at least I haven’t figured out how to make it apply.)

I’ll keep investating options in Excel, or, worse case, see if I can get the data to include data types so that Aspose.Cells will merge “real” numeric data.

Thanks again.

7

You can try:

&=Detail.SalesPrice(numeric)

8

Laurence –

That TOTALLY solved the problem! You ROCK! Thank you!

Any other little tricks about Smart Markers you can share? Especially, other data-type options? You made my day!

m.

9

Please check Smart Markers for reference. Feel free to contact me if you have any further questions.

10

Yep, thanks. I’ve studied that document, but, somehow I missed the “numeric” parameter. I see it there now!

What about a date parameter? Would this work? Probably not, but it sure would be great:
&=OrderDetails.SalesDate(date)

Also, any suggestions about concatenating two or more values into a single cell? What I’d like to accomplish is something like:

&=Customer.City + ", " + &=Customer.StateProv + " " + &=Customer.Postal

Thanks again.