Excel for WinRT
Creating Subtotals
Excel for WinRT > Excel for WinRT Task-Based Help > Creating Subtotals

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.Xaml.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. Double-click the HelloButton to 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;
            }
  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.

 

 

Copyright (c) GrapeCity, inc. All rights reserved.

Product Support Forum  |  Documentation Feedback