Hi,
I’m currently evaluating Aspose.Excel for a client. The feature that I’m particularly interested in are the smart markers. I have designed a template spreadsheet with a row of cells containing the smart markers, using the pattern &=DataSource.FieldName. How do I associate a datatable with the template spreadsheet?
thanks,
Dennis
With further experimentation I think I got it. I used the Replace method on the excel object passing in the first smart marker pattern and the datatable, and it replaced all the smart markers.
Yes. Replace method can be used to replace the smart markers. And we enhanced it with ExcelDesigner object.
The sample code of Smart Marker demo is not included in the setup msi yet. We will add it in the next release.
The following code is the sample code of the demo:
[C#]
using Aspose.Excel;
namespace Aspose.Excel.Demos
{
public class SmartMarker : System.Web.UI.Page
{
private void Page_Load(object sender, System.EventArgs e)
{
string param = this.Request.Params[0];
if(param == “result”)
{
DataSet ds = CreateDataSource();
ExcelDesigner designer = new ExcelDesigner();
string path = MapPath(".");
path = path.Substring(0, path.LastIndexOf("\"));
string designerFile = path + “\Designer\SmartMarkerDesigner.xls”;
designer.Open(designerFile);
designer.SetDataSource(ds);
designer.SetDataSource(“Variable”, “Single Variable”);
designer.SetDataSource(“MultiVariable”, new string[]{“Variable 1”, “Variable 2”, “Variable 3”});
designer.SetDataSource(“MultiVariable2”, new string[]{“Skip 1”, “Skip 2”, “Skip 3”});
designer.Process();
designer.Save(“SmartMarker.xls”, SaveType.OpenInBrowser, FileFormatType.Default, this.Response);
}
else if(param == “designer”)
{
string path = MapPath(".");
path = path.Substring(0, path.LastIndexOf("\")) + “\Designer\SmartMarkerDesigner.xls”;
FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read);
byte[] data = new byte[fs.Length];
fs.Read(data, 0, data.Length);
fs.Close();
Response.ContentType = “application/vnd.ms-excel”;
Response.AddHeader( “content-disposition”,“attachment; filename=SmartMarkerDesigner.xls”);
Response.BinaryWrite(data);
Response.End();
}
}
#region Private code to create data source
private DataSet CreateDataSource()
{
DataSet ds = new DataSet();
OleDbConnection oleDbConnection1 = new OleDbConnection();
string path = MapPath(".");
path = path.Substring(0, path.LastIndexOf("\"));
oleDbConnection1.ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + path + “\Database\Northwind.mdb”;
OleDbDataAdapter oleDbDataAdapter1 = new OleDbDataAdapter();
oleDbDataAdapter1.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {new System.Data.Common.DataTableMapping(“Table”, “Order Details”, new System.Data.Common.DataColumnMapping[] {new System.Data.Common.DataColumnMapping(“Discount”, “Discount”), new System.Data.Common.DataColumnMapping(“OrderID”, “OrderID”), new System.Data.Common.DataColumnMapping(“ProductID”, “ProductID”), new System.Data.Common.DataColumnMapping(“Quantity”, “Quantity”), new System.Data.Common.DataColumnMapping(“UnitPrice”, “UnitPrice”)})});
OleDbCommand oleDbSelectCommand1 = new OleDbCommand();
oleDbSelectCommand1.Connection = oleDbConnection1;
oleDbDataAdapter1.SelectCommand = oleDbSelectCommand1;
oleDbSelectCommand1.CommandText = “SELECT Discount, OrderID, ProductID, Quantity, UnitPrice FROM [Order Details]”;
OleDbDataAdapter oleDbDataAdapter2 = new OleDbDataAdapter();
oleDbDataAdapter2.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {new System.Data.Common.DataTableMapping(“Table”, “Customers”, new System.Data.Common.DataColumnMapping[] {new System.Data.Common.DataColumnMapping(“Address”, “Address”), new System.Data.Common.DataColumnMapping(“City”, “City”), new System.Data.Common.DataColumnMapping(“CompanyName”, “CompanyName”), new System.Data.Common.DataColumnMapping(“ContactName”, “ContactName”), new System.Data.Common.DataColumnMapping(“ContactTitle”, “ContactTitle”), new System.Data.Common.DataColumnMapping(“Country”, “Country”), new System.Data.Common.DataColumnMapping(“CustomerID”, “CustomerID”), new System.Data.Common.DataColumnMapping(“Fax”, “Fax”), new System.Data.Common.DataColumnMapping(“Phone”, “Phone”), new System.Data.Common.DataColumnMapping(“PostalCode”, “PostalCode”), new System.Data.Common.DataColumnMapping(“Region”, “Region”)})});
OleDbCommand oleDbSelectCommand2 = new OleDbCommand();
oleDbSelectCommand2.Connection = oleDbConnection1;
oleDbDataAdapter2.SelectCommand = oleDbSelectCommand2;
oleDbSelectCommand2.CommandText = “SELECT Address, City, CompanyName, ContactName, ContactTitle, Country, CustomerID, Fax, Phone, PostalCode, Region FROM Customers”;
oleDbConnection1.Open();
oleDbDataAdapter1.Fill(ds);
oleDbDataAdapter2.Fill(ds);
oleDbConnection1.Close();
return ds;
}
#endregion
#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}
///
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
///
private void InitializeComponent()
{
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
}
}
[VB]
Imports System.Data.OleDb
Imports System.IO
Public Class SmartMarker
Inherits System.Web.UI.Page
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
Private Sub InitializeComponent()
End Sub
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim param As String = Me.Request.Params(0)
If param = “result” Then
Dim ds As DataSet = CreateDataSource()
Dim designer As ExcelDesigner = New ExcelDesigner()
Dim path As String = MapPath(".")
path = path.Substring(0, path.LastIndexOf(""))
Dim designerFile As String = path + “\Designer\SmartMarkerDesigner.xls”
designer.Open(designerFile)
designer.SetDataSource(ds)
designer.SetDataSource(“Variable”, “Single Variable”)
designer.SetDataSource(“MultiVariable”, New String() {“Variable 1”, “Variable 2”, “Variable 3”})
designer.SetDataSource(“MultiVariable2”, New String() {“Skip 1”, “Skip 2”, “Skip 3”})
designer.Process()
designer.Save(“SmartMarker.xls”, SaveType.OpenInBrowser, FileFormatType.Default, Me.Response)
ElseIf param = “designer” Then
Dim path As String = MapPath(".")
path = path.Substring(0, path.LastIndexOf("")) + “\Designer\SmartMarkerDesigner.xls”
Dim fs As FileStream = New FileStream(path, FileMode.Open, FileAccess.Read)
Dim data As Byte() = New Byte(fs.Length) {}
fs.Read(Data, 0, Data.Length)
fs.Close()
Response.ContentType = “application/vnd.ms-excel”
Response.AddHeader(“content-disposition”, “attachment; filename=SmartMarkerDesigner.xls”)
Response.BinaryWrite(Data)
Response.End()
End If
End Sub
#Region " Private code to create data source "
Private Function CreateDataSource() As DataSet
Dim ds As DataSet = New DataSet()
Dim oleDbConnection1 As OleDbConnection = New OleDbConnection()
Dim path As String = MapPath(".")
path = path.Substring(0, path.LastIndexOf(""))
oleDbConnection1.ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + path + “\Database\Northwind.mdb”
Dim oleDbDataAdapter1 As OleDbDataAdapter = New OleDbDataAdapter()
oleDbDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping(“Table”, “Order Details”, New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping(“Discount”, “Discount”), New System.Data.Common.DataColumnMapping(“OrderID”, “OrderID”), New System.Data.Common.DataColumnMapping(“ProductID”, “ProductID”), New System.Data.Common.DataColumnMapping(“Quantity”, “Quantity”), New System.Data.Common.DataColumnMapping(“UnitPrice”, “UnitPrice”)})})
Dim oleDbSelectCommand1 As OleDbCommand = New OleDbCommand()
oleDbSelectCommand1.Connection = oleDbConnection1
oleDbDataAdapter1.SelectCommand = oleDbSelectCommand1
oleDbSelectCommand1.CommandText = “SELECT Discount, OrderID, ProductID, Quantity, UnitPrice FROM [Order Details]”
Dim oleDbDataAdapter2 As OleDbDataAdapter = New OleDbDataAdapter()
oleDbDataAdapter2.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping(“Table”, “Customers”, New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping(“Address”, “Address”), New System.Data.Common.DataColumnMapping(“City”, “City”), New System.Data.Common.DataColumnMapping(“CompanyName”, “CompanyName”), New System.Data.Common.DataColumnMapping(“ContactName”, “ContactName”), New System.Data.Common.DataColumnMapping(“ContactTitle”, “ContactTitle”), New System.Data.Common.DataColumnMapping(“Country”, “Country”), New System.Data.Common.DataColumnMapping(“CustomerID”, “CustomerID”), New System.Data.Common.DataColumnMapping(“Fax”, “Fax”), New System.Data.Common.DataColumnMapping(“Phone”, “Phone”), New System.Data.Common.DataColumnMapping(“PostalCode”, “PostalCode”), New System.Data.Common.DataColumnMapping(“Region”, “Region”)})})
Dim oleDbSelectCommand2 As OleDbCommand = New OleDbCommand()
oleDbSelectCommand2.Connection = oleDbConnection1
oleDbDataAdapter2.SelectCommand = oleDbSelectCommand2
oleDbSelectCommand2.CommandText = “SELECT Address, City, CompanyName, ContactName, ContactTitle, Country, CustomerID, Fax, Phone, PostalCode, Region FROM Customers”
oleDbConnection1.Open()
oleDbDataAdapter1.Fill(ds)
oleDbDataAdapter2.Fill(ds)
oleDbConnection1.Close()
Return ds
End Function
#End Region
End Class
In the online demo, you can see Smart Marker "&=Customers.City" in the designer file. You can create an ExcelDesigner object and use it to open the designer file. Then you set the data source with a DataSet. In this DataSet, there is a DataTable named "Customers". After calling ExcelDesigner.Process method, the Smart Marker "&=Customers.City" is replaced with the data in the "City" column in the "Customers" table.
Simply and easy.
Awesome! Thanks, that’s exactly what I was looking for.
I do have an enhancement request. It would be nice if the ExcelDesigner object had an event that was triggered after it has inserted each row (or maybe as it wrote out each smart marker column value), with the current datarow and the range of cells just inserted as parameters in the event args. That way, we could do some further processing of the excel row that is not handled by the smart markers.
Dennis
Dear Dennis,
It's easy to know how many rows are inserted for each smart marker. For example, you can put a string below the smart marker, such as "SmartMarkerCity". Then you can try the following code:
ExcelDesigner excelDesigner = new ExcelDesigner();
excelDesigner.Open(designFileName);
Excel excel = excelDesigner.Excel;
Cell cell = excel.Worksheets[0].Cells.FindString("SmartMarkerCity");
int rowNumberBeforeProcess = cell.Row;
//Process the smart marker
excelDesigner.SetDataSource(...);
excelDesigner.Process();
cell = excel.Worksheets[0].Cells.FindString("SmartMarkerCity");
int rowNumberAfterProcess = cell.Row;
int insertRows = rowNumberAfterProcess - rowNumberBeforeProcess;
Hi Laurence,
I needed to create a list of hyperlinks next to each row that was outputted. In the end I did something similiar.
Now I’m onto the next problem, which is that for each row that is outputted from the datarow, a new worksheet is added (the hyperlink is an internal hyperlink to the new worksheet). The new worksheet is actually a cloned worksheet with some more smart markers. Now the smart markers need to be associated with a different datasource than the one that I used previously. How can I do this?
Thanks for all your help by the way. It is much appreciated.
Dennis.
Hi Dennis,
You can just use SetDataSource method to add another datasource.
For example, you have two tables: one is “Customers”, another is “Orders”. In you designer spreadsheet, you put the smart markers “&=Cusotmers.Name”, “&=Orders.Amount”.
Then you can set the data source as following:
//Set data source of a DataTable with name of "Customers"
excelDesigner.SetDataSource(dtCusotmers);
//Set data source of a DataTable with name of "Orders"
excelDesigner.SetDataSource(dtOrders);
Aspose.Excel can find the data source automatically and put the data in the correct location of the smart markers.
Thanks Laurence for the prompt reply.
The problem is that each worksheet is a clone of a template worksheet, and is going to have its own dataset (rather than datatable) and each dataset is going to have the same tables and fields as each other (since the smart markers will be the same). So the problem is that I would like to set the datasource at the sheet level rather than the excel (or excel designer) level.
I know I can do this programmatically, without using smart markers, but the ability to allow the clients to customise the output of their excel spreadsheets without requiring coding changes is too good to pass up on.
I have a couple of suggestions :
It would be great if operations in excel were be available on a varying level of granularity. For example Replace works on the excel object (which is really the workbook), but there doesnt appear to be a way to use the call on a worksheet, or range object.
Smart markers that aren’t recognised by the ExcelDesigner are removed after calling Process. They should really be preserved (or rather have an option to preserve them) as they may be later replaced in code via a call to the Replace method, or searched for using the Find method.
Back to the idea of having an event triggered as it replaces a smart marker, it would be handy for also providing formatting of the value. In my case, I have a date field that is being handled by the smart marker. But the value that was being written into the cell is not a value that resembles a date, and I have no control over how that date field is formatted. Having an event that is triggered, I could check that if the value of the field is of a date time, be able to pass back through a parameter in the event args, the formatted date value that I would want to appear.
Please do not take any of this as criticism, rather as (hopefully) useful suggestions for improving the functionality of the product. I like the flexibility that it gives me, but as is always the case, I want more, more, more!
Dennis.
Hi Dennis,
Thanks for your suggestions. Your ideas are great and helpful.
I will overload the Process method to make it working on worksheet level and preserving un-recognized smart markers. Other features will be investigated and added to Aspose.Excel if possible.
Laurence,
Excellent!
One other problem, that you may not have picked up on from my previous post, is the formatting of the content. If I have a smart marker that represents a date time field (or any other field for that matter), I have no control over the formatting of that field. And in fact for date time fields, the actual output does not seem to look like a date or time.
Thanks,
Dennis
Hi Dennis,
You can set the date time format to that smart marker cell. For example, one of your smart marker is “&=Products.ShipDate” in Cell A2, you can set the number format of cell A2 to “3-14-2001”, then the populate date time will show correctly.
Thanks Laurence,
I should have thought of that, I’ll try it as soon as I can.
Could you give me an ETA as to when the overloaded process will be available?
Dennis
Hi Dennis,
The overloaded Process method will be availabe in next week. Thanks for your patience.
Thanks Laurence,
one more question for today (I promise!). I want to get the textual contents of a cell preserving the texts’ formatting. Is there anyway to get the textual content as an an RTF string? If not, is there any other way to do this?
Dennis
Hi Dennis,
Currently Aspose.Excel doesn’t support to get/set a RTF string to a cell. It can only return string and whole font setting of a cell.
However, Aspose.Excel supports to import RTF string of a cell in designer file. You can put RTF string to a cell in your designer file. Aspose.Excel will import it and write to the result file.
Thanks again Laurence.
Might I suggest that as another enhancement. The reason being it would be nice to have the ability to generate the designer file programmatically.
Dennis
Hi Dennis,
Please download the latest hotfix.
Now you can use the following overloaded Process methods:
///
/// Processes the smart markers and populates the data source values.
///
/// True if the unrecognized smarte marker is preserved.
public void Process(bool isPreserved)
///
/// Processes the smart markers and populates the data source values.
///
/// Worksheet index.
/// True if the unrecognized smarte marker is preserved.
/// This method works on worksheet level.
public void Process(int sheetIndex, bool isPreserved)
Thanks Laurence, your help has been very much appreciated.
I am still running into a problem with the new process method. I’ll try to explain what I am trying to do.
I have a workbook, with a couple of worksheets, the first worksheet is a sheet that has smart markers to generate a list of account managers and their status reports. The second worksheet is a sheet that has smart markers to generate a complete list of customer details for all account managers. For each account managers, a copy of the second worksheet is made, so that there will be a worksheet for each account manager showing the customer details for the particular account manager.
That means I’ll be dealing with a number of datasets, one dataset containing the list of account managers and their status, and one dataset for each account manager containing their list of customers, and each account manager datasets are identical in terms of table and columns structure.
So the code in essence looks like the following:
protected bool generateReport(string templateFilePath, string outputFilePath)
{
ExcelDesigner designer = new ExcelDesigner();
designer.Open(templateFilePath);
// dsStatusReport has list of account managers and their status
designer.SetDataSource(dsStatusReport);
// grab a reference to the
Worksheet wsNotes = designer.Excel.Worksheets[ACCOUNT_MANAGER_STATUS_SHEET];
// for each account manager
foreach (DataRow dr in dsStatusReport.Tables[ACCOUNT_MANAGER_DATATABLE].Rows)
{
string lname = dr[AM_LNAME].ToString();
// create a sheet for them based on the account manager template
int idx = designer.Excel.Worksheets.AddCopy(ACCOUNT_MANAGER_NAME_SHEET);
Worksheet sheetAccountManager = designer.Excel.Worksheets[ACCOUNT_MANAGER_NAME_SHEET];
// set the name of the worksheet to the account manager’s last name
Worksheet sheetAM = designer.Excel.Worksheets[idx];
sheetAM.Name = lname;
// replace the account manager status smart markers
designer.Process(0, true);
}
// find where the links should be
Cell cellLink = wsNotes.Cells.FindStringEndsWith("!A1", null );
foreach (DataRow dr in dsStatusReport.Tables[ACCOUNT_MANAGER_DATATABLE].Rows)
{
string lname = dr[AM_LNAME].ToString();
// add a link from the account manager’s tab
wsNotes.Hyperlinks.AddInternalLink(cellLink.Row , cellLink.Column, 1, 1, lname + “!A1”);
cellLink = wsNotes.Cells[cellLink.Row + 1, cellLink.Column];
}
// for each account manager, associate the dataset and replace smart markers
int startAMSheet = 2;
for (int i = 0; i < this.slAccountManagerDataSets.Count; i++)
{
DataSet dsAM = (DataSet)this.slAccountManagerDataSets.GetByIndex(i);
// associate account manager dataset (would like to do this to sheet)
designer.SetDataSource(dsAM);
designer.Process(startAMSheet + i, true);
}
// save the generated report
designer.Save(outputFilePath, FileFormatType.Default);
return true;
}
I think there is a problem in trying to associate the multiple account manager’s datasets with the designer. One solution would be to overload the Process method again to look like the following :
Process(int sheetIndex, bool isPreserved, DataSource ds)
Dennis.
Hi Dennis,
I think this method can meet your need:
public ClearDataSource()
You can set data source for the specific worksheet, process it and clear it. Then you can set data source for another worksheet, process it and clear it.
I can add this method in the next hotfix.