ComponentOne Excel for UWP
Excel for UWP 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 to get a subtotal or your data.

    In XAML View

    Complete the following steps to create the XAML View for this topic:

    1. Right-click References in the Solution Explorer and select Add Reference from the list.
    1. Browse to find C1.UWP.Excel.dll.
    2. Click OK to add the assembly reference to your application.
    1. In XAML View, place your cursor between the <Grid> </Grid> tags.
    2. Add two standard Button controls and one standard TextBox control to the page.
    1. Edit the markup for the first button so that it resembles the following:
    Markup
    Copy Code 
    <Button x:Name="HelloButton" Content="Click Hello" />
    1. Edit the markup for the second button so that it resembles the following:
    Markup
    Copy Code 
    <Button x:Name="SaveButton" Content="Save" />
    1. Edit the markup for the TextBox control so that it resembles the following:
    Markup
    Copy Code 
    <TextBox               
   Name="_tbContent"
   Text="Empty"
   IsReadOnly="True"
   AcceptsReturn="True"
   FontFamily="Courier New"
   Background="White" Margin="465,10,242,722" />
    1. Create  an event named HelloButton_Click for HelloButton and switch to the code view of MainPage.xaml. This will also add a HelloButton_Click event to the code.
    1. Switch back to Design View and double-click the SaveButton to add a SaveButton_Click event to the code. This will open the Code View.

    In Code View

    Complete the following steps to create the Code View for this topic:

    1. Add a using statement to the top of the page:
    C#
    Copy Code 
    using C1.Xaml.Excel;
    1. Add the following code to the MainPage class so that it resembles the following:
    C#
    Copy Code 
    public sealed partial class MainPage : Page
    {
        C1XLBook _book;
    }
    1. Create a C1XLBook by adding the following code to the InitializeComponent() method:
    C#
    Copy Code 
    _book = new C1XLBook();
    1. Add the RefreshView() method. You will call this method later in the code:
    C#
    Copy Code 
    void RefreshView()
        {
        }
    1. Add code to format the cells.
    C#
    Copy Code 
    private void HelloButton_Click(object sender, RoutedEventArgs e)
        {
        XLSheet sheet = _book.Sheets[0];
                // create a style
                XLStyle totalStyle = new XLStyle(_book);
                 totalStyle.Font = new XLFont("Arial", 12, true, false);
               // create an outline and apply styles
                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;

            // allow user to save the file
            _tbContent.Text = "'Hello World' workbook has been created, you can save it now.";
            RefreshView();
            }
    1. Save the workbook.
    C#
    Copy Code 
    async void SaveButton_Click(object sender, RoutedEventArgs e)
         {
             Debug.Assert(_book != null);

             var picker = new Windows.Storage.Pickers.FileSavePicker();
             picker.SuggestedStartLocation = Windows.Storage.Pickers.PickerLocationId.DocumentsLibrary;
             picker.FileTypeChoices.Add("Open XML Excel file", new List<string>() { ".xlsx" });
             picker.FileTypeChoices.Add("BIFF Excel file", new List<string>() { ".xls" });
             picker.SuggestedFileName = "New Book";

             var file = await picker.PickSaveFileAsync();
             if (file != null)
             {
                 try
                 {
                     // step 1: save file
                     var fileFormat = Path.GetExtension(file.Path).Equals(".xls") ? FileFormat.Biff8 : FileFormat.OpenXml;
                     await _book.SaveAsync(file, fileFormat);
                     // step 2: user feedback
                     _tbContent.Text = string.Format("File has been saved to: {0}.", file.Path);
                     RefreshView();
                 }
                 catch (Exception x)
                 {
                     _tbContent.Text = string.Format("EXCEPTION: {0}", x.Message);
                 }
             }
         }
       
    1. Run the program. Save and open the file. The spreadsheet will look similar to the following:

    The SUBTOTAL formulas get the sum of the specified rows.