Excel for WPF | ComponentOne
C1Excel Task-Based Help / Creating Subtotals
In This Topic
    Creating Subtotals
    In This Topic

    The following code provides an example of how to format the cells of a book.

    1. Select View | Code and add one of the following statements at the top of the form:
      • Import C1.C1Excel (Visual Basic)
      • using C1.C1Excel; (C#)
    2. Add a reference to C1.WPF.Excel.dll or C1.Silverlight.Excel.dll and create a C1XLBook.
      C#
      Copy Code
      // Create a new workbook
      C1XLBook book = new C1XLBook();
      
    3. Add the following code to the Form_Load event:
      Visual Basic
      Copy Code
      Private Sub Form1_Load(sender As Object, e As EventArgs)
         Dim book As New C1XLBook()
         Dim sheet As XLSheet = book.Sheets(0)
         Dim totalStyle As New XLStyle(book)
         totalStyle.Font = New XLFont("Arial", 10, true, false)
         sheet(2, 1).Value = "Number"
         sheet(2, 2).Value = "ID"
         sheet(3, 1).Value = 12
         sheet(3, 2).Value = 17
         sheet.Rows(3).OutlineLevel = 2
         sheet.Rows(3).Visible = False
         sheet(4, 1).Value = 12
         sheet(4, 2).Value = 14
         sheet.Rows(4).OutlineLevel = 2
         sheet.Rows(4).Visible = False
         sheet(5, 1).Value = "12 Total"
         sheet(5, 1).Style = totalStyle
         sheet(5, 2).Value = 31
         sheet(5, 2).Formula = "SUBTOTAL(9,C4:C5)"
         sheet.Rows(5).OutlineLevel = 1
         sheet(6, 1).Value = 34
         sheet(6, 2).Value = 109
         sheet.Rows(6).OutlineLevel = 2
         sheet(7, 1).Value = "34 Total"
         sheet(7, 1).Style = totalStyle
         sheet(7, 2).Value = 109
         sheet(7, 2).Formula = "SUBTOTAL(9,C7:C7)"
         sheet.Rows(7).OutlineLevel = 1
         sheet(8, 1).Value = "Grand Total"
         sheet(8, 1).Style = totalStyle
         sheet(8, 2).Value = 140
         sheet(8, 2).Formula = "SUBTOTAL(9,C4:C7)"
         sheet.Rows(8).OutlineLevel = 0
         book.Save("c:\mybook.xls")
         System.Diagnostics.Process.Start("C:\mybook.xls")
      End Sub
      

       

      C#
      Copy Code
      private void Form1_Load(object sender, EventArgs e)
              {
                  C1XLBook book = new C1XLBook();
                  XLSheet sheet = book.Sheets[0];
                  XLStyle totalStyle = new XLStyle(book);
                  totalStyle.Font = new XLFont("Arial", 10, true, false);
                  sheet[2, 1].Value = "Number";
                  sheet[2, 2].Value = "ID";
                  sheet[3, 1].Value = 12;
                  sheet[3, 2].Value = 17;
                  sheet.Rows[3].OutlineLevel = 2;
                  sheet.Rows[3].Visible = false;
                  sheet[4, 1].Value = 12;
                  sheet[4, 2].Value = 14;
                  sheet.Rows[4].OutlineLevel = 2;
                  sheet.Rows[4].Visible = false;
                  sheet[5, 1].Value = "12 Total";
                  sheet[5, 1].Style = totalStyle;
                  sheet[5, 2].Value = 31;
                  sheet[5, 2].Formula = "SUBTOTAL(9,C4:C5)";
                  sheet.Rows[5].OutlineLevel = 1;
                  sheet[6, 1].Value = 34;
                  sheet[6, 2].Value = 109;
                  sheet.Rows[6].OutlineLevel = 2;
                  sheet[7, 1].Value = "34 Total";
                  sheet[7, 1].Style = totalStyle;
                  sheet[7, 2].Value = 109;
                  sheet[7, 2].Formula = "SUBTOTAL(9,C7:C7)";
                  sheet.Rows[7].OutlineLevel = 1;
                  sheet[8, 1].Value = "Grand Total";
                  sheet[8, 1].Style = totalStyle;
                  sheet[8, 2].Value = 140;
                  sheet[8, 2].Formula = "SUBTOTAL(9,C4:C7)";
                  sheet.Rows[8].OutlineLevel = 0;
          
                  book.Save(@"c:\mybook.xls");
                  System.Diagnostics.Process.Start(@"C:\mybook.xls");
              }
      
    4. Run the program. The spreadsheet will open and look similar to the following:

      Subtotals in workbook

      The SUBTOTAL formulas get the sum of the specified rows.