Reading and Writing to Excel files using WPF 4.5

Microsoft Excel is an electronic spreadsheet that has become one of the most important and widely used software for storing, organizing and manipulating data. Be it a small medical store or the accounts department of a big enterprise, everybody uses Excel. Recently I came across a requirement where a customer had to programmatically read/write data to and from an Excel workbook file. In .NET, there are multiple ways to do so, right from using an interop assembly to using an OleDb connection API to read/write from and to Excel files.

To use an OLEDB connection in a WPF .NET app, the Office System Driver for Data Connectivity must be installed on your machine. The driver can be downloaded from here. If the .NET application uses an OleDb provider ‘Microsoft.ACE.OLEDB.12.0’ without installing this driver, the application will throw ‘the 'microsoft.ace.oledb.12.0' provider is not registered on the local machine’ exception.

Another important point to note is that, since the .NET application will read the data from Excel workbook which is managed by the Operating System filesystem, the workbook must be closed in order to successfully read or write data from it, else the .NET application throws an exception. If you want to keep the workbook open and yet want the .NET application to read the data from it, then the workbook must configured as a shared workbook.

For our demo, create an Excel File (2010/2013) with the following Structure:

Make the workgroup shared as shown here:

The Sharing options are as follows:

The Sharing options must be set to synchronize changes from the Excel Worksheet to the .NET client application.

Step 1: Open Visual Studio 2012/2013 and create a new WPF application targeted to using the .NET 4.5 framework. Name the app as ‘WPF_Excel_Reader_Writer’. In the MainWindow.xaml add a DataGrid and a Button as shown here:

<Grid>
<Grid.RowDefinitions>
    <RowDefinition Height="272*"/>
    <RowDefinition Height="71*"/>
</Grid.RowDefinitions>
<Button Content="Synchronize"
        Name="btnsync" Grid.Row="1"
        FontSize="40" FontFamily="Times New Roman"
        FontWeight="ExtraBold"
         />
<DataGrid Name="dgEmp" AutoGenerateColumns="False" ColumnWidth="*"
           >
    <DataGrid.Columns>
        <DataGridTextColumn Header="EmpNo" Binding="{Binding EmpNo}"></DataGridTextColumn>
        <DataGridTextColumn Header="EmpName" Binding="{Binding EmpName}"></DataGridTextColumn>
        <DataGridTextColumn Header="Salary" Binding="{Binding Salary}"></DataGridTextColumn>
        <DataGridTextColumn Header="DeptName" Binding="{Binding DeptName}"></DataGridTextColumn>
    </DataGrid.Columns>
</DataGrid>

</Grid>

Step 2: To the project, add a new class file and name it as ‘DataAccess.cs’. Add the following code in it:

using System;
using System.Threading.Tasks;

using System.Data.OleDb;
using System.Collections.ObjectModel;

namespace WPF_Excel_Reader_Writer
{

public class Employee
{
public int EmpNo { get;set; }
public string EmpName { get; set; }
public int Salary { get; set; }
public string DeptName { get; set; }
}
public class DataAccess
{
OleDbConnection Conn;
OleDbCommand Cmd;

public DataAccess()
{
    Conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\\FromC\\VS2013\\WPF_45_DEMOS\\Employee.xlsx;Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"");
}

/// <summary>
/// Method to Get All the Records from Excel
/// </summary>
/// <returns></returns>
public async Task<ObservableCollection<Employee>> GetDataFormExcelAsync()
{
    ObservableCollection<Employee> Employees = new ObservableCollection<Employee>();
    await Conn.OpenAsync();
    Cmd = new OleDbCommand();
    Cmd.Connection = Conn;
    Cmd.CommandText = "Select * from [Sheet1$]";
    var Reader = await Cmd.ExecuteReaderAsync();
    while (Reader.Read())
    {
        Employees.Add(new Employee() {
            EmpNo = Convert.ToInt32(Reader["EmpNo"]),
            EmpName = Reader["EmpName"].ToString(),
            DeptName = Reader["DeptName"].ToString(),
            Salary = Convert.ToInt32(Reader["Salary"])
        });
    }
    Reader.Close();
    Conn.Close();
    return Employees;
}

/// <summary>
/// Method to Insert Record in the Excel
/// S1. If the EmpNo =0, then the Operation is Skipped.
/// S2. If the Employee is already exist, then it is taken for Update
/// </summary>
/// <param name="Emp"></param>
public async Task<bool> InsertOrUpdateRowInExcelAsync(Employee Emp)
{
    bool IsSave = false;
    //S1
    if (Emp.EmpNo != 0)
    {
        await Conn.OpenAsync();
        Cmd = new OleDbCommand();
        Cmd.Connection = Conn;
        Cmd.Parameters.AddWithValue("@EmpNo", Emp.EmpNo);
        Cmd.Parameters.AddWithValue("@EmpName", Emp.EmpName);
        Cmd.Parameters.AddWithValue("@Salary", Emp.Salary);
        Cmd.Parameters.AddWithValue("@DeptName", Emp.DeptName);
        //S2
        if (!CheckIfRecordExistAsync(Emp).Result)
        {
            Cmd.CommandText = "Insert into [Sheet1$] values (@EmpNo,@EmpName,@Salary,@DeptName)";
        }
        else
        {
            if (Emp.EmpName != String.Empty || Emp.DeptName != String.Empty)
            {
                Cmd.CommandText = "Update [Sheet1$] set EmpNo=@EmpNo,EmpName=@EmpName,Salary=@Salary,DeptName=@DeptName where EmpNo=@EmpNo";
            }
        }
        int result = await Cmd.ExecuteNonQueryAsync();
        if (result > 0)
        {
            IsSave = true;
        }
        Conn.Close();
    }
    return IsSave;

}

/// <summary>
/// The method to check if the record is already available
/// in the workgroup
/// </summary>
/// <param name="emp"></param>
/// <returns></returns>
private async Task<bool> CheckIfRecordExistAsync(Employee emp)
{
    bool IsRecordExist = false;
    Cmd.CommandText = "Select * from [Sheet1$] where EmpNo=@EmpNo";
    var Reader = await Cmd.ExecuteReaderAsync();
    if (Reader.HasRows)
    {
        IsRecordExist = true;
    }
    
    Reader.Close();
    return IsRecordExist;
}
}

}

The code we just saw does the following:

-The Employee class is used to define attributes for the Employee object.

-The class DataAccess is used to interact with the Excel file using the following declarations:

• The connection and command objects are defined using OleDbConnection and OleDbCommand respectively.
• The constructor defines a connection string for the Excel file using Mictosoft.ACE.OLEDB.12.0 provider. Here the path of the Excel file passed to the connection string need to be changed as per the location of the Excel file.
• All methods are defined as async to make Async calls.
• The GetDataFromExcelAsync() method opens connection to the Excel file and reads data from the Worksheet with the name Sheet1. This methods returns an ObservableCollection.
• The method InsertOrUpdateRowInExcelAsync() method accepts an Employee objects. If the EmpNo from this object is zero(0) then the execution of the rest of the code of this method is skipped. This is just a simple check and you can customize is as per your business needs. This method calls the CheckIfRecordExistAsync() method which is responsible to check if the Employee record already exists. If yes, then the record is accepted for update, else a new record is created.

Step 3: We will now subscribe to the following events:

• Loaded event of the Window
• Click event of the button
• CellEditEnding, RowEditEnding and SelectionChanged events of the DataGrid

Step 4: Add the following code in the MainWindow.xaml.cs:

public partial class MainWindow : Window
{
//The object of the DataAccess class
DataAccess objDs;
public MainWindow()
{
    InitializeComponent();
}

//The Employee Object for Edit
Employee emp = new Employee();
/// <summary>
/// On Load get data from the Excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Window_Loaded(object sender, RoutedEventArgs e)
{
    objDs = new DataAccess();
    try
    {
            dgEmp.ItemsSource = objDs.GetDataFormExcelAsync().Result;
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}
/// <summary>
/// TO Synchronize the Excel Workbook with the Application
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private   void btnsync_Click(object sender, RoutedEventArgs e)
{
    try
    {
       dgEmp.ItemsSource =   objDs.GetDataFormExcelAsync().Result;
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}
/// <summary>
/// Read Data entered in each Cell
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void dgEmp_CellEditEnding(object sender, DataGridCellEditEndingEventArgs e)
{
    try
    {
            FrameworkElement eleEno = dgEmp.Columns[0].GetCellContent(e.Row);
            if (eleEno.GetType() == typeof(TextBox))
            {
                emp.EmpNo = Convert.ToInt32(((TextBox)eleEno).Text);
            }

            FrameworkElement eleEname = dgEmp.Columns[1].GetCellContent(e.Row);
            if (eleEname.GetType() == typeof(TextBox))
            {
                emp.EmpName = ((TextBox)eleEname).Text;
            }

            FrameworkElement eleSal = dgEmp.Columns[2].GetCellContent(e.Row);
            if (eleSal.GetType() == typeof(TextBox))
            {
                emp.Salary = Convert.ToInt32(((TextBox)eleSal).Text);
            }

            FrameworkElement eleDname = dgEmp.Columns[3].GetCellContent(e.Row);
            if (eleDname.GetType() == typeof(TextBox))
            {
                emp.DeptName = ((TextBox)eleDname).Text;
            }

    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}
/// <summary>
/// Get the Complete row
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void dgEmp_RowEditEnding(object sender, DataGridRowEditEndingEventArgs e)
{
    try
    {
      bool IsSave = objDs.InsertOrUpdateRowInExcelAsync(emp).Result;
      if (IsSave)
      {
          MessageBox.Show("Record Saved Successfully");
      }
      else
      {
          MessageBox.Show("Problem Occured");
      }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
   
}
/// <summary>
/// Select the Recod for the Update
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void dgEmp_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
    emp = dgEmp.SelectedItem as Employee;
}
}

The code has the following specifications:

• The Loaded event makes call to GetDataFromExcelAsync() method from the DataAccess class. The result returned from the method is then displayed in the DataGrid using ItemsSource property
• In the SelectionChanged implementation, the Employee object is generated which will be used for update
• The CellEditEnding implementation is used to read the cell value entered in the specific row of the DataGrid. These values will be put in the Employee object. This object will then be used to insert or update in the Excel Worksheet
• The RowEditEnding implementation will make a call to the InsertOrUpdateRowInExcelAsync() method and pass an Employee to it for inserting or updating
• Clicking the button will retrieve the latest data from the worksheet and display it in the DataGrid

Step 5: Run the application. You will see the following:

To test synchronization, add a new record in Excel and click on the ‘Synchronize’ button. The newly added record will be as shown below:

(that’s the best straight row highlighting I could do after 3 attempts ;) )

Since the Workbook is shared, the data entered in the worksheet of the workbook can be directly synchronized even when the workbook is open.

Similarly enter a record in the DataGrid Row. When you click on the next row or the current row loses focus, the record will be saved in the Worksheet as shown here.

To verify, close the application and reopen the Excel file. The newly added record will be displayed in Excel as shown here:

And that’s how using WPF, we can Read and Write Data to Excel files.

Conclusion: We just saw that if Excel files are used as a data store, using OleDb features the data from the Workgroup can be read and manipulated in a WPF .NET application.

Download the entire source code of this article (Github)

This article has been editorially reviewed by Suprotim Agarwal.

C# and .NET have been around for a very long time, but their constant growth means there’s always more to learn.

We at DotNetCurry are very excited to announce The Absolutely Awesome Book on C# and .NET. This is a 500 pages concise technical eBook available in PDF, ePub (iPad), and Mobi (Kindle).

Organized around concepts, this Book aims to provide a concise, yet solid foundation in C# and .NET, covering C# 6.0, C# 7.0 and .NET Core, with chapters on the latest .NET Core 3.0, .NET Standard and C# 8.0 (final release) too. Use these concepts to deepen your existing knowledge of C# and .NET, to have a solid grasp of the latest in C# and .NET OR to crack your next .NET Interview.

Click here to Explore the Table of Contents or Download Sample Chapters!

Author

Mahesh Sabnis is a DotNetCurry author and a Microsoft MVP having over two decades of experience in IT education and development. He is a Microsoft Certified Trainer (MCT) since 2005 and has conducted various Corporate Training programs for .NET Technologies (all versions), and Front-end technologies like Angular and React. Follow him on twitter @maheshdotnet or connect with him on LinkedIn