Hi: I am new to Aspose and find it to be a wonderful tool.
I am creating an excel 2007 spreadsheet. I am using Smart Marker and it is working fine pulling in the data from my application.
My problem is that I would like to sum the contents of the Amount Column, but The contents of the file can change. One week I might have 150 rows the next maybe 100.
I have reviewed the Dynamic formulas and did not see anything that I could use to calculate the total of a Column. I have tried using the sum function in excel But because the rows can vary I don't think I can use them.
Is there a variation of the dynamic formulas that I could use?
Well you may use Formulas, For your case you may put Worksheet formula "=SUM()" into your desired column. When smart markers are processed and data is filled into the worksheet, new rows are inserted based on the data in your source.
Kindly check the attached zip file which contains template file that will tell you how to place the summary formula with markers, the zip file also contains the the output excel file that has processed data.
And here is the sample code, I used Northwind Access database as the data source.
OleDbConnection con = new OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=d:\\test\\Northwind.mdb"); con.Open(); OleDbCommand cmd = new OleDbCommand("Select * from Products",con); OleDbDataAdapter da = new OleDbDataAdapter(); da.SelectCommand = cmd;
DataSet ds = new DataSet(); da.Fill(ds,"Products"); DataTable dt = ds.Tables["Products"];
WorkbookDesigner wd = new WorkbookDesigner(); wd.Open("d:\\test\\productsummary.xls"); wd.SetDataSource(dt); wd.Process();