Hi
I am trying to use a dynamic formula to add a calculated percentage for each row i.e… Row/Sum of Rows.
The following formula &=&=E{r}/E3 is not updating the E3 cell reference when rows are inserted the reference to the total in E3 is not updated to say E13 if 10 rows of data are inserted.
Thanks
Nick
Hi,
Well, I am afraid, Aspose.Cells would not update the “E3” cell reference accordingly in dynamic formulas in Smart Markers as the Summary cell is pasted in the second row after your dynamic formula cell, so it would take it as constant “E3” and will not update its reference. I think you may skip inputting the dynamic formula markers in the template file and process the remaining markers which would surely place your data for smart markers pointers and your summary row would be also updated and inserted at the right spot. Once you process the markers, now you may input the dynamic formulas dynamically using the Aspose.Cells APIs into the F column cells accordingly. I have written a sample code for your reference to accomplish your task, please refer to it and update your codes accordingly for your needs. I have also attached the output file for your reference:
e.g
Sample code:
Workbook wb = new Workbook(@“e:\test2\example.xlsx”);
WorkbookDesigner d = new WorkbookDesigner();
d.Workbook = wb;
DataTable dt = new DataTable(“Query1”);
dt.Columns.Add("#");
dt.Columns.Add(“CODE”);
dt.Columns.Add(“Name”);
dt.Columns.Add(“Leases”);
dt.Columns.Add(“Rent”, typeof(int));
dt.Rows.Add(new object[] { “1”, “111”, “ABC”, “AAA”, 100 });
dt.Rows.Add(new object[] { “2”, “112”, “ACC”, “BAA”, 200 });
dt.Rows.Add(new object[] { “3”, “113”, “ADC”, “CAA”, 300 });
dt.Rows.Add(new object[] { “4”, “114”, “AEC”, “DAA”, 400 });
dt.Rows.Add(new object[] { “5”, “115”, “AFC”, “EAA”, 500 });
dt.Rows.Add(new object[] { “6”, “116”, “AGC”, “FAA”, 600 });
dt.Rows.Add(new object[] { “7”, “116”, “AHC”, “GAA”, 700 });
dt.Rows.Add(new object[] { “8”, “117”, “AGC”, “JAA”, 800 });
dt.Rows.Add(new object[] { “9”, “118”, “AIC”, “AAK”, 900 });
d.SetDataSource(dt);
d.Process();
wb.CalculateFormula();
//Now find the Sum() formula cell.
Cells cells = wb.Worksheets[0].Cells;
FindOptions options = new FindOptions();
options.LookInType = LookInType.Formulas;
options.LookAtType = LookAtType.Contains;
Cell found = cells.Find("=SUM(E2", null, options);
//Get the name of the cell.
string cellname = found.Name;
//Now loop through the F column to add your dynamic formulas
for (int i = 1; i < found.Row; i++)
{
cells[i, 5].Formula = “=E” + (i + 1) + “/” + cellname;
}
wb.Save(@“e:\test2\outexample1.xlsx”);
Hope, this helps a bit.
Thank you.