How to Create a WPF Application that Can Append Data to an Excel File

In this tutorial, we are going to implement a WPF application using MVVM pattern that appends some data to a provided Excel file. The program will consist of a View that displays the user interface, a Model that will hold the data and the logic for appending of the data to an Excel file, and a ViewModel that represents the model of the view. Additionally, we will place the code for the append operation within an async method to keep the application responsive. You will need the Visual Studio 2017 for this tutorial.

The GUI of the application

We start with the View that will look like in the image above. The View will consist of one input field the user will be able to enter the path to the desired excel file, multiple input fields for data that must be appended to the provided excel file, a text box for showing statuses and errors, as well as four buttons. The select button will open the OpenFile dialog so that the user can select an excel file using the explorer, while the clear button will clear all the data fields. The append button will execute the append operation. Finally, the cancel button will provide the user with the possibility to cancel the append operation. Additionally, we will have multiple labels to label the input fields.

First, create a new WPF App project. In the Visual Studio select File-> Project…

In the New Project window select Visual C# -> Windows Desktop -> WPF App (.NET Framework). Enter the project name, solution name, and select a location for the project. Click OK to confirm.

In the Solution Explorer double-click on the File MainWindow.xaml to open it. The MainWindow class will represent the View of our application. Replace the empty <Grid> element with the following code.

    <Grid>
        <Grid.ColumnDefinitions>
            <ColumnDefinition Width="100"></ColumnDefinition>
            <ColumnDefinition></ColumnDefinition>
            <ColumnDefinition Width="100"></ColumnDefinition>
        </Grid.ColumnDefinitions>
        <Grid.RowDefinitions>
            <RowDefinition Height="35"></RowDefinition>
            <RowDefinition Height="35"></RowDefinition>
            <RowDefinition Height="35"></RowDefinition>
            <RowDefinition ></RowDefinition>
        </Grid.RowDefinitions>

        <Label Content="Excel File" Grid.Row="0" Grid.Column="0" FontSize="14"/>
        <TextBox Grid.Row="0" Grid.Column="1" FontSize="14" Margin="5" Text="{Binding Path=Filename, Mode=TwoWay}"/>
        <Button x:Name="SelectFileButton" Content="Select" Grid.Row="0" Grid.Column="2" Margin="5" Command="{Binding SelectFileCommand}"/>

        <Label Content="Data" Grid.Row="1" Grid.Column="0" FontSize="14"/>
        <StackPanel Orientation="Horizontal" Grid.Row="1" Grid.Column="1">
            <Label Content="A" FontSize="14"/>
            <TextBox x:Name="AField" Width="60" Margin="0,5,0,5" Text="{Binding Path=Data.AField, Mode=TwoWay}"/>

            <Label Content="B" FontSize="14"/>
            <TextBox x:Name="BField" Width="60" Margin="0,5,0,5" Text="{Binding Path=Data.BField, Mode=TwoWay}"/>

            <Label Content="C" FontSize="14"/>
            <TextBox x:Name="CField" Width="60" Margin="0,5,0,5" Text="{Binding Path=Data.CField, Mode=TwoWay}"/>

            <Label Content="D" FontSize="14"/>
            <TextBox x:Name="DField" Width="60" Margin="0,5,0,5" Text="{Binding Path=Data.DField, Mode=TwoWay}"/>

            <Label Content="E" FontSize="16"/>
            <TextBox x:Name="EField" Width="60" Margin="0,5,0,5" Text="{Binding Path=Data.EField, Mode=TwoWay}"/>
        </StackPanel>
        
        <Button x:Name="ClearFieldsButton" Height="25" Content="Clear" Grid.Row="1" Grid.Column="2" Margin="5" Command="{Binding ClearCommand}"/>
        <Button x:Name="AppendButton" Height="25"  Content="Append" Grid.Row="2" Grid.Column="2" Margin="5" Command="{Binding AppendCommand}"/>
        <Button x:Name="CancelButton" Height="25" VerticalAlignment="Top" Content="Cancel" Grid.Row="3" Grid.Column="2" Margin="5" Command="{Binding CancelCommand}"/>

        <ScrollViewer Grid.Row="2" Grid.Column="0" Grid.RowSpan="2" Grid.ColumnSpan="2" Margin="10" Background="LightGray">
            <TextBlock TextWrapping="Wrap" FontSize="16"  Text="{Binding Data.Result}" Padding="10"/>
        </ScrollViewer>

    </Grid>

This code creates a grid with four rows and three columns. The last column (2) contains the buttons for all commands (Select File, Clear Data, Append Data, and Cancel Append Operation). Additionally, the first row includes the label with the text “Excel File” and a text box that stores the file path. We use TwoWay binding to connect the content of the text box with the property Filename. The TwoWay binding means that the content of the text box updates if the bound property updates and vice versa.

The second row contains a label with the text “Data” and five labeled text boxes the user can enter data in. Again, we use TwoWay binding to connect the content of the text boxes with appropriate properties.

Finally, the third and fourth rows contain a text box that we are going to use to provide the user with an application state or to report errors.

Next, we are going to create the classes for the ViewModel, Model, and Commands. The View (MainWindow.xaml) and the associated class (MainWindow.xaml.cs) exist already. To create a new class right-click on the project name in the Solution Explorer and select Add -> Class… Subsequently, in the Add New Item window enter the class name and click Add.

Create the following classes: ExcelWriterData.cs (Model); ExcelWriterViewController.cs (View Model); RelayCommand.cs (Commands). As a result, you should have the project structure as shown in the image below.

Next, we implement the ExcelWriterData class that will contain our Model. Open the ExcelWriterData.cs file and replace the content of the file with the following code.

using System;
using System.ComponentModel;
using System.Runtime.CompilerServices;
using System.Threading;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;

namespace ExcelWriter
{
    class ExcelWriterData : INotifyPropertyChanged
    {
        private string afield = string.Empty;
        private string bfield = string.Empty;
        private string cfield = string.Empty;
        private string dfield = string.Empty;
        private string efield = string.Empty;

        public string AField
        {
            get => afield;
            set { afield = value; NotifyPropertyChanged(); }
        }
        public string BField
        {
            get => bfield;
            set { bfield = value; NotifyPropertyChanged(); }
        }
        public string CField
        {
            get => cfield;
            set { cfield = value; NotifyPropertyChanged(); }
        }
        public string DField
        {
            get => dfield;
            set { dfield = value; NotifyPropertyChanged(); }
        }
        public string EField
        {
            get => efield;
            set { efield = value; NotifyPropertyChanged(); }
        }

        private string result;
        public string Result
        {
            get => result;
            set { result = value; NotifyPropertyChanged(); }
        }

        public ExcelWriterData()
        {
            Result = string.Empty;
        }

        public event PropertyChangedEventHandler PropertyChanged;

        private void NotifyPropertyChanged([CallerMemberName] string propertyName = "")
        {
            PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));
        }

        public void ClearValues()
        {
            AField = string.Empty;
            BField = string.Empty;
            CField = string.Empty;
            DField = string.Empty;
            EField = string.Empty;
        }

        public async Task AppendToFileAsync(string filename, CancellationToken cancellationToken)
        {
            try
            {
                await Task.Run(() =>
                {
                    Result = "Appending...";

                    Excel.Application app = null;
                    Excel.Workbook workbook = null;
                    Excel.Worksheet worksheet = null;

                    try
                    {
                        // Open a new Excel instance
                        app = new Excel.Application();
                        if (app == null)
                            throw new Exception("Could not open the Excel application");

                        // Open the file
                        workbook = app.Workbooks.Open(filename);
                        if (workbook == null)
                            throw new Exception($"Could not open the file: {filename}");

                        // Get the first worksheet
                        worksheet = (Excel.Worksheet)workbook.Worksheets[1];
                        if (worksheet == null)
                            throw new Exception("Could not acquire the worksheet");

                        // Find the first empty row
                        int rowId = 0;
                        int numEmptyCells = 1;
                        while (numEmptyCells > 0)
                        {
                            string cell = "A" + (++rowId).ToString();
                            Excel.Range row = app.Range[cell, cell].EntireRow;
                            numEmptyCells = (int)app.WorksheetFunction.CountA(row);
                        }

                        // Write data
                        worksheet.Cells[rowId, 1].Value = AField;
                        worksheet.Cells[rowId, 2].Value = BField;
                        worksheet.Cells[rowId, 3].Value = CField;
                        worksheet.Cells[rowId, 4].Value = DField;
                        worksheet.Cells[rowId, 5].Value = EField;

                        if (cancellationToken.IsCancellationRequested)
                            throw new OperationCanceledException();

                        // Save the workbook
                        workbook.Save();

                        // Close the workbook
                        workbook.Close(true);
                        Result = "Append operation was successful";
                    }
                    catch (OperationCanceledException)
                    {
                        // cancellation is requested
                        // close workbook without saving it
                        if (workbook != null)
                            workbook.Close(false);

                        Result = "Append operation was canceled";
                    }
                    catch (Exception e)
                    {
                        Result = "Could not write data: " + e.Message;
                    }
                    finally
                    {
                        if (app != null)
                            app.Quit();
                    }
                },
                cancellationToken);
            }
            catch (OperationCanceledException)
            {
                Result = "Append operation was canceled";
            }
            catch (Exception)
            {
                Result = "Could not write data";
            }
        }
    }
}

As you can see, the class implements the INotifyPropertyChanged interface, which belongs to the System.ComponentModel assembly. We have to implement that interface to ensure the TwoWay binding we used in our View. The interface allows notifying Views about property changes so that Views could read the new values and update their visual state. The INotifyPropertyChanged requires the declaration of an event of the type PropertyChangedEventHandler, what we have done with the following line of code.

public event PropertyChangedEventHandler PropertyChanged;

Additionally, we defined the NotifyPropertyChanged method that must be called on every property change. The method uses the
PropertyChangedEventHandler to notify Views about changes.

private void NotifyPropertyChanged([CallerMemberName] string propertyName = "")
        {
            PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));
        }

Next, we declared five string fields (afield,…, efield) that will contain the user data. The application will append the content of these fields to the Excel file on the execution of the append operation.

private string afield = string.Empty;
private string bfield = string.Empty;
private string cfield = string.Empty;
private string dfield = string.Empty;
private string efield = string.Empty;

For each string field, we defined a public property. We use these properties in the View for the TwoWay binding. As you can see, we execute the NotifyPropertyChanged method on each call of the set-method to notify the View about changes.

public string AField
{
    get => afield;
    set { afield = value; NotifyPropertyChanged(); }
}

Additionally, we defined the Result property that we are going to use to communicate to the user the application state and errors.

Finally, we declare the method AppendToFileAsync that represents the append operation. The method is async which means it will run in the background and won’t block the graphical user interface, thus keeping it responsive. The method takes two parameters, namely, the name of an Excel file the data has to be appended to; and the CancelationToken that we will use to allow the user to cancel the operation.

The AppendToFileAsync method starts a new thread in the background. Within the new thread, we first try to open a new instance of the Excel application. Using that instance and the provided path to the Excel file, we open the file and acquire the first worksheet of the workbook.

Next, we look for the first empty row. For that, we traverse down the worksheet row for row starting with the first row. In each row, we count the number of not empty cells in the row. If the number is higher than zero, then we consider the row as not empty and move to the next one. If, however, the number equals zero, then the row is empty, and the while-loop ends. The ID of the empty row is stored in the rowID variable.

// Find the first empty row
int rowId = 0;
int numNotEmptyCells = 1;
while (numNotEmptyCells > 0)
{
    string cell = "A" + (++rowId).ToString();
    Excel.Range row = app.Range[cell, cell].EntireRow;
    numNotEmptyCells = (int)app.WorksheetFunction.CountA(row);
}

Next, we write our data into the founded empty row by merely setting the value for each cell. We do not save the data immediately into the file though. We first check if the user requested cancellation of the operation. If yes, we leave the thread by throwing a new exception. If no, we save the changes into the file and close the file. Finally, we close the instance of the Excel application.

So far, we implemented the View and the Model. In our next step, we are going to implement the Commands. We will implement a base class for all our commands since their behavior is similar. Open the RelayCommand.cs file using the Solution Explorer and replace the content of the file with the following code.

class RelayCommand : ICommand
{
    public event EventHandler CanExecuteChanged;

    private Action action;
    private Func<bool> canExecute;

    public RelayCommand(Action action, Func<bool> canExecute)
    {
        this.action = action;
        this.canExecute = canExecute;
    }

    public bool CanExecute(object parameter)
    {
        return canExecute();
    }

    public void Execute(object parameter)
    {
        action();
    }

    public virtual void OnCanExecuteChanged()
    {
        CanExecuteChanged?.Invoke(this, new EventArgs());
    }
}

As you can see the RelayCommand class implements the ICommand interface which is included in the System.Windows.Input assembly. The ICommand interface allows for binding to actionable GUI elements like Buttons. The interface requires an event of the type EventHandler and the methods CanExecute and Execute. The CanExecute method returns a boolean value that indicates if the command can be executed (returns true) or not (returns false). The Execute method executes the functionality attached to or associated with the command.

Additionally, to the methods and variables required by the interface, we implemented a constructor that takes two parameters. The first parameter is a delegate of the type Action which represents a simple method that takes zero parameters and returns nothing. The method passed as the action to the command will be executed in the Execute method.

The second parameter is a function that returns a boolean value. We use this function within the CanExecute method. So, in general terms, a command is a wrapper for something defined elsewhere. In our case, within the controller.

Finally, we added the OnCanExecuteChanged method that notifies Views about the changes of the CanExecute state.

At last, we need to implement the Controller that will connect all parts together. For that, open the ExcelWriterViewController.cs file and replace the content with the following code.

class ExcelWriterViewController : INotifyPropertyChanged
{
    private CancellationTokenSource cancellationTokenSource;
    public ExcelWriterData Data { get; set; }

    private string filename = string.Empty;
    public string Filename { get => filename; set { filename = value; NotifyPropertyChanged(); } }

     private bool isBusy = false;
     private bool isCanceling = false;

     public RelayCommand AppendCommand { get; set; }
     public RelayCommand CancelCommand { get; set; }
     public RelayCommand ClearCommand { get; set; }
     public RelayCommand SelectFileCommand { get; set; }

     public ExcelWriterViewController()
     {
         Data = new ExcelWriterData();
         AppendCommand = new RelayCommand(Append, IsIdle);
         CancelCommand = new RelayCommand(Cancel, CanCancel);
         ClearCommand = new RelayCommand(Clear, IsIdle);
         SelectFileCommand = new RelayCommand(SelectFile, IsIdle);
     }

     public event PropertyChangedEventHandler PropertyChanged;
     private void NotifyPropertyChanged([CallerMemberName] string propertyName = "")
     {
         PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));
     }

     bool IsIdle() { return !isBusy; }
     bool CanCancel() { return !isCanceling && isBusy; }

     void updateBusy(bool busyValue)
     {
         isBusy = busyValue;
         AppendCommand.OnCanExecuteChanged();
         CancelCommand.OnCanExecuteChanged();
         ClearCommand.OnCanExecuteChanged();
         SelectFileCommand.OnCanExecuteChanged();
     }

     public async void Append()
     {
         updateBusy(true);
         cancellationTokenSource = new CancellationTokenSource();
         await Data.AppendToFileAsync(filename, cancellationTokenSource.Token);
         updateBusy(false);
         isCanceling = false;
     }

     public void Cancel()
     {
         isCanceling = true;
         CancelCommand.OnCanExecuteChanged();
         Data.Result = "Appand operation is being canceled";
         if (cancellationTokenSource != null)
             cancellationTokenSource.Cancel();
     }

     public void Clear()
     {
         Data.ClearValues();
     }

     public void SelectFile()
     {
         OpenFileDialog openFileDialog = new OpenFileDialog();
         openFileDialog.Filter = "Excel Dokumente (*.xlsx, *.xls, *.xlsm, *.xlst) | *.xlsx;*.xls;*.xlsm;*.xlst";
         bool? result = openFileDialog.ShowDialog();
         if (result == true)
         {
             Filename = openFileDialog.FileName;
         }
     }
}

The Controller contains a CancellationTokenSource that can be used to cancel the append operation.

private CancellationTokenSource cancellationTokenSource;

Next, it has the property named Data that references our Model. Additionally, we have the Filename property that stores the path to the provided Excel file.

The Controller also has two state variables isBusy and isCanceling which we are going to use for the definition of the CanExecute states of our commands. Finally, we have four properties that reference the append command, clear command, cancel command, and select file command.

In the constructor, we instantiate the Model and the Commands. For the commands, we use appropriate methods defined in the Controller: Append, Cancel, Clear, and SelectFile. We also provide the reference to the isIdle method to the AppendCommand, ClearCommand, and SelectFileCommand to indicate the CanExecute state of these commands. For the CancelCommand, we use the CanCancel method to define the CanExecute state.

Using the updateBusy method, we update the isBusy state variable and notify the commands that their states have been changed.

The content of the methods that are used as actions within the commands is actually clear.

The last thing we have to do is to connect our View with the Controller. For that, open the MainWindow.xaml.cs file and in the constructor add the following line of code.

DataContext = new ExcelWriterViewController();

That is all! You can also extend the View and the Model to allow the user more data at once or add some other nice features.

You can also download the entire project here:

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d