locked
Subtotals within ListObject rows (not TotalRow) RRS feed

  • Question

  • I have an example where I would like to make certain rows inside a ListObject calculate subtotals. I programmatically insert formulas and the values are calculated correctly, however the formulas don't stick in the cells so any updates to values aren't reflected in the subtotal. Here's an example...

    DataTable table;
    private void Sheet3_Startup(object sender, System.EventArgs e) {
    	this.Outline.SummaryRow = Microsoft.Office.Interop.Excel.XlSummaryRow.xlSummaryAbove;
    		
    	table = new DataTable();
    	DataColumn column1 = new DataColumn("Names", typeof(string));
    	DataColumn column2 = new DataColumn("Department", typeof(string));
    	DataColumn column3 = new DataColumn("Salary", typeof(decimal));
    	table.Columns.Add(column2);
    	table.Columns.Add(column1);
    	table.Columns.Add(column3);
    
    	// Add the four rows of data to the table.
    	int[] Ages = { 32, 44, 28, 61 };
    	string[] Names = { "Reggie", "Sally", "Henry", "Christine" };
    	string[] Departments = { "HR", "Sales" };
    	double[] Salaries = { 73200, 14400, 20800, 61000 };
    	DataRow row;
    	for (int i = 0; i < 4; i++) {
    		row = table.NewRow();
    		row["Names"] = Names[i];
    		row["Department"] = Departments[(int)Math.Floor(i / 2.0)];
    		row["Salary"] = Salaries[i];
    		table.Rows.Add(row);
    	}
    	row = table.NewRow();
    	row["Names"] = null;
    	row["Department"] = Departments[0];
    	row["Salary"] = 0;
    	table.Rows.InsertAt(row, 0);
    	row = table.NewRow();
    	row["Names"] = null;
    	row["Department"] = Departments[1];
    	row["Salary"] = 0;
    	table.Rows.InsertAt(row, 3);
    
    	Microsoft.Office.Tools.Excel.ListObject list1 =this.Controls.AddListObject(this.Range["A1", "B4"], "list1");
    
    	// Bind the list object to the table.
    	list1.SetDataBinding(table);
    	list1.ShowTotals = true;
    	//insert department groups, subtotals
    	Microsoft.Office.Interop.Excel.Range sum = this.Range["C2", "C2"];
    	///The following formula doesn't appear in the worksheet, however the value is calculated correctly.
    	///Since the formula isn't there, changes to the salary ranges aren't reflected in this cell.
    	sum.Formula = "=SUBTOTAL(9,C3:C4)";
    	this.Range["A3", "A4"].Rows.Group(missing, missing, missing, missing);
    
    	sum = this.Range["C5", "C5"];
    	sum.Formula = "=SUBTOTAL(9,C6:C7)";
    	this.Range["A6", "A7"].Rows.Group(missing, missing, missing, missing);
    }
    
    Is there a way to make the formula work? Am I abusing the ListObject? Is there some other way to do this?
    Tuesday, January 26, 2010 4:06 PM

Answers

  • Hello ADurkin,

    I put these codes in two lines of,
    this.EnableCalculation = false;
    ...
    this.EnableCalculation = true;

    Then Excel will not automatically calculate, and replace the formula with the result value. It works for me.

    The whole codes are,
    -------------------------------------------------------
            private void Sheet3_Startup(object sender, System.EventArgs e)
            {
                this.Outline.SummaryRow = Microsoft.Office.Interop.Excel.XlSummaryRow.xlSummaryAbove;
                this.EnableCalculation = false;
                DataTable table = new DataTable();
                DataColumn column1 = new DataColumn("Names", typeof(string));
                DataColumn column2 = new DataColumn("Department", typeof(string));
                DataColumn column3 = new DataColumn("Salary", typeof(decimal));
                table.Columns.Add(column2);
                table.Columns.Add(column1);
                table.Columns.Add(column3);

                // Add the four rows of data to the table.
                int[] Ages = { 32, 44, 28, 61 };
                string[] Names = { "Reggie", "Sally", "Henry", "Christine" };
                string[] Departments = { "HR", "Sales" };
                double[] Salaries = { 73200, 14400, 20800, 61000 };
                DataRow row;
                for (int i = 0; i < 4; i++)
                {
                    row = table.NewRow();
                    row["Names"] = Names[i];
                    row["Department"] = Departments[(int)Math.Floor(i / 2.0)];
                    row["Salary"] = Salaries[i];
                    table.Rows.Add(row);
                }
                row = table.NewRow();
                row["Names"] = null;
                row["Department"] = Departments[0];
                row["Salary"] = 0;
                table.Rows.InsertAt(row, 0);
                row = table.NewRow();
                row["Names"] = null;
                row["Department"] = Departments[1];
                row["Salary"] = 0;
                table.Rows.InsertAt(row, 3);
                Microsoft.Office.Tools.Excel.ListObject list1 = this.Controls.AddListObject(this.Range["A1", "B4"], "list1");
                // Bind the list object to the table.
                list1.SetDataBinding(table);
                list1.ShowTotals = true;
                //insert department groups, subtotals
                Microsoft.Office.Interop.Excel.Range sum = this.Range["C2", "C2"];
                ///The following formula doesn't appear in the worksheet, however the value is calculated correctly.
                ///Since the formula isn't there, changes to the salary ranges aren't reflected in this cell.
                sum.Formula = "=SUBTOTAL(9,C3:C4)";
                this.Range["A3", "A4"].Rows.Group(missing, missing, missing, missing);

                sum = this.Range["C5", "C5"];
                sum.Formula = "=SUBTOTAL(9,C6:C7)";
                this.Range["A6", "A7"].Rows.Group(missing, missing, missing, missing);

                this.EnableCalculation = true;
            }
    -------------------------------------------------------



    Best regards,
    Ji Zhou
    MSDN Subscriber Support in Forum


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Ji.Zhou Tuesday, February 2, 2010 4:52 AM
    Wednesday, January 27, 2010 8:53 AM

All replies

  • Hello ADurkin,

    I put these codes in two lines of,
    this.EnableCalculation = false;
    ...
    this.EnableCalculation = true;

    Then Excel will not automatically calculate, and replace the formula with the result value. It works for me.

    The whole codes are,
    -------------------------------------------------------
            private void Sheet3_Startup(object sender, System.EventArgs e)
            {
                this.Outline.SummaryRow = Microsoft.Office.Interop.Excel.XlSummaryRow.xlSummaryAbove;
                this.EnableCalculation = false;
                DataTable table = new DataTable();
                DataColumn column1 = new DataColumn("Names", typeof(string));
                DataColumn column2 = new DataColumn("Department", typeof(string));
                DataColumn column3 = new DataColumn("Salary", typeof(decimal));
                table.Columns.Add(column2);
                table.Columns.Add(column1);
                table.Columns.Add(column3);

                // Add the four rows of data to the table.
                int[] Ages = { 32, 44, 28, 61 };
                string[] Names = { "Reggie", "Sally", "Henry", "Christine" };
                string[] Departments = { "HR", "Sales" };
                double[] Salaries = { 73200, 14400, 20800, 61000 };
                DataRow row;
                for (int i = 0; i < 4; i++)
                {
                    row = table.NewRow();
                    row["Names"] = Names[i];
                    row["Department"] = Departments[(int)Math.Floor(i / 2.0)];
                    row["Salary"] = Salaries[i];
                    table.Rows.Add(row);
                }
                row = table.NewRow();
                row["Names"] = null;
                row["Department"] = Departments[0];
                row["Salary"] = 0;
                table.Rows.InsertAt(row, 0);
                row = table.NewRow();
                row["Names"] = null;
                row["Department"] = Departments[1];
                row["Salary"] = 0;
                table.Rows.InsertAt(row, 3);
                Microsoft.Office.Tools.Excel.ListObject list1 = this.Controls.AddListObject(this.Range["A1", "B4"], "list1");
                // Bind the list object to the table.
                list1.SetDataBinding(table);
                list1.ShowTotals = true;
                //insert department groups, subtotals
                Microsoft.Office.Interop.Excel.Range sum = this.Range["C2", "C2"];
                ///The following formula doesn't appear in the worksheet, however the value is calculated correctly.
                ///Since the formula isn't there, changes to the salary ranges aren't reflected in this cell.
                sum.Formula = "=SUBTOTAL(9,C3:C4)";
                this.Range["A3", "A4"].Rows.Group(missing, missing, missing, missing);

                sum = this.Range["C5", "C5"];
                sum.Formula = "=SUBTOTAL(9,C6:C7)";
                this.Range["A6", "A7"].Rows.Group(missing, missing, missing, missing);

                this.EnableCalculation = true;
            }
    -------------------------------------------------------



    Best regards,
    Ji Zhou
    MSDN Subscriber Support in Forum


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Marked as answer by Ji.Zhou Tuesday, February 2, 2010 4:52 AM
    Wednesday, January 27, 2010 8:53 AM
  • Hello ADurkin,

    Have you tried my suggestion in my last reply? If you need further help on this, please let me know and I will do my best to follow up.


    Best regards,
    Ji Zhou
    MSDN Subscriber Support in Forum
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Monday, February 1, 2010 5:49 AM