Dynamic Formulas with Variable Number of Rows

Aspose.Cells Product Family
1

I am trying to use dynamic formulas to run totals but the number of rows that I have will vary with each run of the program. I need to know how to build the formula.

What I have now in one cell is something like

dtDataTable.Salary(numeric)

In the cell below I need to run the sum of all data brought in. I would normally use $==Sum(D5:D15), but I need for this to be truly dynamic as the number of rows will change. How can I do that?

Thanks for the assistance!

2

Hi,

Please change your formula to "=Sum(D5:D6)". I have created a sample solution for your need. Kindly consult it. Attached is a zip file containing template and output excel files and following is the code which I used with Northwind Access database.

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\\dynformula.xls");
wd.SetDataSource(dt);
wd.Process();
wd.Save("d:\\test\\outdynformula.xls");

For reference please check:

Thank you.

3

What I am looking for is how to build the dynamic formula. I have a case where the data sometimes starts on row 13 of the spreadsheet, but sometimes starts on row 16. This particular grid can have 0 to 10 rows of data. If I specify the exact rows it works well, but I don't know how to get the dynamic formula to know where to start and where to end. In cell C12 I have dtData.Field(numeric) In cell C13 if I use &==sum(C13:C22) it works well. If I use &==sum(C13:C{r-1}) it errors on me. I need to find a way to get it to total for me whether I have 2 rows of data or 10 rows of data. How do I do build the dynamic formula?

Thanks,

4

Hi,

Thanks for considering Aspose.

I think you may place your dynamic formulas using Aspose.Cells APIs for your need. Yor are not to place your dynamic formulas in your template excel file. You may do it in your code after processing the smart markers.

May the following sample example helps you for your need.

E.g.,

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\\templatebook.xls");
wd.SetDataSource(dt);
wd.Process();

Cells cells = wd.Workbook.Worksheets[0].Cells;
int lastrowindex = cells.MaxDataRowInColumn(0);
int maxrow = lastrowindex +1;
cells[maxrow,0].Formula = "=SUM(A2:A" + maxrow.ToString()+ ")";
wd.Workbook.CalculateFormula();

wd.Save("d:\\test\\outbook.xls");
Thank you.
5

PERFECT!!!

Thank you very much!