Hi,
Well, your desired feature may not be supported in Smart Markers, but, I think you may try our new feature Subtotals (supported in latest version v4.7.1), check the doc topic: http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/creating-a-list-object.html
I have created a sample code for your need. In the example, I used Smart Markers and process the markers to fill the data into the worksheet cells. Then, I utilized the Subtotals feature to accomplish your desired results. Please see the input file first, check the second worksheet's smart markers. The output file is also attached. I utilized Northwind Access sample database for the demo.
Sample code:
//Create a connection object, specify the provider info and set the data source.
OleDbConnection con = new OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=f:\\test\\Northwind.mdb");
//Open the connection object.
con.Open();
//Create a command object and specify the SQL query.
OleDbCommand cmd = new OleDbCommand("Select * from [Order Details]", con);
//Create a data adapter object.
OleDbDataAdapter da = new OleDbDataAdapter();
//Specify the command.
da.SelectCommand = cmd;
//Create a dataset object.
DataSet ds = new DataSet();
//Fill the dataset with the table records.
da.Fill(ds, "Order Details");
//Create a datatable with respect to dataset table.
DataTable dt = ds.Tables["Order Details"];
//Create WorkbookDesigner object.
WorkbookDesigner wd = new WorkbookDesigner();
//Open the template file (which contains smart markers).
wd.Open("f:\\test\\SmartMarker_Designernew1.xls");
//Set the datatable as the data source.
wd.SetDataSource(dt);
//Process the smart markers to fill the data into the worksheets.
wd.Process(true);
//Get the Cells collection in the first worksheet
Cells cells = wd.Workbook.Worksheets[1].Cells;
//Create a cellarea i.e.., B3:C19
CellArea ca = new CellArea();
ca.StartRow = 4;
ca.StartColumn = 0;
ca.EndRow = cells.MaxDataRow;
ca.EndColumn = cells.MaxDataColumn;
//Apply subtotal, the consolidation function is Sum and it will applied to
//some columns in the list
cells.Subtotal(ca, 0, ConsolidationFunction.Sum, new int[] { 2,3 });
//Save the excel file.
wd.Save("f:\\test\\outSmartMarker_Designernew1.xls");
Thank you.
Amjad Sahi
Support Developer,
Aspose Sialkot Team
Contact Us
Live Chat