I'm trying to group the list of vehicles by their Make and then calculate the total purchase price of the group. I have attached the template.xslx that im using. In E2 cell i have setup Smart marker like this
&=[Vehicles].PurchasePrice(subtotal9:Vehicles.Make)
The output shows all the purchase price by group but it DOES NOT show the group total at the bottom of each group.
I follwed the example given here http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/smart-markers.html
Here is my code
using (Stream inputStream = new FileStream("Template.xlsx", FileMode.Open)) { using (Stream outputStream = new FileStream("TemplateOutput.xlsx", FileMode.Create)) { TemplateHelper helper = new TemplateHelper(); helper.Process(inputStream, outputStream); } }
public class TemplateHelper { public void Process(Stream inputStream, Stream outputStream) { // load the designer with workbook WorkbookDesigner designer = new WorkbookDesigner(); designer.Workbook = new Workbook(inputStream);<SPAN style="COLOR: green">// Get Data</SPAN> <SPAN style="COLOR: #2b91af">DataSet</SPAN> ds = GetData(); <SPAN style="COLOR: green">// set data source</SPAN> designer.SetDataSource(ds); <SPAN style="COLOR: green">// process</SPAN> designer.Process(<SPAN style="COLOR: blue">true</SPAN>); <SPAN style="COLOR: green">// save the output</SPAN> designer.Workbook.Save(outputStream, <SPAN style="COLOR: #2b91af">SaveFormat</SPAN>.Auto); } <SPAN style="COLOR: blue">public</SPAN> <SPAN style="COLOR: #2b91af">DataSet</SPAN> GetData() { <SPAN style="COLOR: #2b91af">DataSet</SPAN> ds = <SPAN style="COLOR: blue">new</SPAN> <SPAN style="COLOR: #2b91af">DataSet</SPAN>(); ds.Tables.Add(GetVehicleListing()); <SPAN style="COLOR: blue">return</SPAN> ds; } <SPAN style="COLOR: blue">private</SPAN> <SPAN style="COLOR: #2b91af">DataTable</SPAN> GetVehicleListing() { <SPAN style="COLOR: green">// setup vehicle listing</SPAN> <SPAN style="COLOR: #2b91af">DataTable</SPAN> vehicles = <SPAN style="COLOR: blue">new</SPAN> <SPAN style="COLOR: #2b91af">DataTable</SPAN>(<SPAN style="COLOR: #a31515">"Vehicles"</SPAN>); vehicles.Columns.Add(<SPAN style="COLOR: blue">new</SPAN> <SPAN style="COLOR: #2b91af">DataColumn</SPAN>(<SPAN style="COLOR: #a31515">"Make"</SPAN>, <SPAN style="COLOR: blue">typeof</SPAN>(<SPAN style="COLOR: blue">string</SPAN>))); vehicles.Columns.Add(<SPAN style="COLOR: blue">new</SPAN> <SPAN style="COLOR: #2b91af">DataColumn</SPAN>(<SPAN style="COLOR: #a31515">"Model"</SPAN>, <SPAN style="COLOR: blue">typeof</SPAN>(<SPAN style="COLOR: blue">string</SPAN>))); vehicles.Columns.Add(<SPAN style="COLOR: blue">new</SPAN> <SPAN style="COLOR: #2b91af">DataColumn</SPAN>(<SPAN style="COLOR: #a31515">"Year"</SPAN>, <SPAN style="COLOR: blue">typeof</SPAN>(<SPAN style="COLOR: blue">string</SPAN>))); vehicles.Columns.Add(<SPAN style="COLOR: blue">new</SPAN> <SPAN style="COLOR: #2b91af">DataColumn</SPAN>(<SPAN style="COLOR: #a31515">"VIN"</SPAN>, <SPAN style="COLOR: blue">typeof</SPAN>(<SPAN style="COLOR: blue">string</SPAN>))); vehicles.Columns.Add(<SPAN style="COLOR: blue">new</SPAN> <SPAN style="COLOR: #2b91af">DataColumn</SPAN>(<SPAN style="COLOR: #a31515">"PurchasePrice"</SPAN>, <SPAN style="COLOR: blue">typeof</SPAN>(<SPAN style="COLOR: blue">double</SPAN>))); <SPAN style="COLOR: #2b91af">DataRow</SPAN> dr = <SPAN style="COLOR: blue">null</SPAN>; <SPAN style="COLOR: blue">for</SPAN> (<SPAN style="COLOR: blue">int</SPAN> i = 0; i < 10; i++) { dr = vehicles.NewRow(); dr[<SPAN style="COLOR: #a31515">"Make"</SPAN>] = <SPAN style="COLOR: #a31515">"Toyota"</SPAN>; dr[<SPAN style="COLOR: #a31515">"Model"</SPAN>] = <SPAN style="COLOR: #a31515">"Camary"</SPAN> + i.ToString(); dr[<SPAN style="COLOR: #a31515">"Year"</SPAN>] = <SPAN style="COLOR: #a31515">"2009"</SPAN>; dr[<SPAN style="COLOR: #a31515">"VIN"</SPAN>] = <SPAN style="COLOR: #a31515">"125RTY45JUY26587K"</SPAN>; dr[<SPAN style="COLOR: #a31515">"PurchasePrice"</SPAN>] = 40000.00 + i; vehicles.Rows.Add(dr); } <SPAN style="COLOR: blue">for</SPAN> (<SPAN style="COLOR: blue">int</SPAN> i = 0; i < 10; i++) { dr = vehicles.NewRow(); dr[<SPAN style="COLOR: #a31515">"Make"</SPAN>] = <SPAN style="COLOR: #a31515">"Honda"</SPAN>; dr[<SPAN style="COLOR: #a31515">"Model"</SPAN>] = <SPAN style="COLOR: #a31515">"Civic"</SPAN> + i.ToString(); dr[<SPAN style="COLOR: #a31515">"Year"</SPAN>] = <SPAN style="COLOR: #a31515">"2009"</SPAN>; dr[<SPAN style="COLOR: #a31515">"VIN"</SPAN>] = <SPAN style="COLOR: #a31515">"125RTY45JUY26587K"</SPAN>; dr[<SPAN style="COLOR: #a31515">"PurchasePrice"</SPAN>] = 50000.00 + i; vehicles.Rows.Add(dr); } <SPAN style="COLOR: blue">for</SPAN> (<SPAN style="COLOR: blue">int</SPAN> i = 0; i < 10; i++) { dr = vehicles.NewRow(); dr[<SPAN style="COLOR: #a31515">"Make"</SPAN>] = <SPAN style="COLOR: #a31515">"Ford"</SPAN>; dr[<SPAN style="COLOR: #a31515">"Model"</SPAN>] = <SPAN style="COLOR: #a31515">"Focus"</SPAN> + i.ToString(); dr[<SPAN style="COLOR: #a31515">"Year"</SPAN>] = <SPAN style="COLOR: #a31515">"2009"</SPAN>; dr[<SPAN style="COLOR: #a31515">"VIN"</SPAN>] = <SPAN style="COLOR: #a31515">"125RTY45JUY26587K"</SPAN>; dr[<SPAN style="COLOR: #a31515">"PurchasePrice"</SPAN>] = 60000.00 + i; vehicles.Rows.Add(dr); } <SPAN style="COLOR: blue">return</SPAN> vehicles; } }</PRE> </PRE></PRE>