DotNetCurry Logo

Reading and Writing to Excel files using WPF 4.5

Posted by: Mahesh Sabnis , on 3/10/2014, in Category WPF
Views: 39600
Abstract: Using WPF we can Read and Write Data to Excel files using OLEDB features. In this article, we will explain this technique with an application.

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:

excel-workbook

Make the workgroup shared as shown here:

shared-workgroup

The Sharing options are as follows:

excel-sharing sharing-options

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:

wpf-excel

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

excel-wpf-add-new

(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.

wpf-grid-synchronize

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

wpf-new-record

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)

Was this article worth reading? Share it with fellow developers too. Thanks!
Share on Google+
Further Reading - Articles You May Like!
Author
Mahesh Sabnis is a DotNetCurry author and Microsoft MVP having over 17 years 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). Follow him on twitter @maheshdotnet


Page copy protected against web site content infringement 	by Copyscape




Feedback - Leave us some adulation, criticism and everything in between!
Comment posted by Waldemar Lederer on Monday, April 28, 2014 8:47 AM
Tried to run it with Visual C# 2012 (express) on a laptop (Windows 7 pro, Office 2007 pro).
Got an error “Object reference not set to an instance of an object?”
What does it mean? Something not installed, not declared or something else?
Comment posted by Silvia on Saturday, June 28, 2014 9:49 AM
I use VS 2013 and everythigs looks OK, but the project can't connect to the .xlsx file. Starts, but empty. I changed the path here: Data Source=F:\\FromC\\VS2013\\WPF_45_DEMOS\\Employee.xlsx - to my file location. I installed Office System Driver for Data Connectivity. Is there something else I should do?
Thank You in advance!

Silvia
Comment posted by sam on Monday, September 8, 2014 12:36 AM
HI Mahesh
i am new in wpf can u help me uploading files to database and retrive them
Comment posted by sam on Monday, September 8, 2014 7:39 AM
Hi Mahesh

I found the solution ,how to save a pdf to database
can u say to me how to save multiple files to data base and retrive then accordingly


Thanks in Advance

sam
Comment posted by Jonathan Alfredo Gómez Gómez on Thursday, October 23, 2014 8:43 AM
Hi Mahesh!
Great article, it was just what I've been looking for.
Comment posted by sampad on Wednesday, November 5, 2014 4:07 AM
Hi Mahesh
i put a checkbox in template field of a listbox.for insert it is working fine.but while retriving the data i am unable to check those checkboxes .can you please give me some solution for it

Thanks in advance
Comment posted by Jorge on Thursday, December 4, 2014 4:12 PM
Hi Mahesh!
Awesome article!
Please help me with this:
How can I insert a row by code?

Regards
Comment posted by J. Heiser on Tuesday, February 3, 2015 12:29 PM
Great article Mahesh.

Need to know how you linked in the events to support editing, etc. For example how does the "dgEmp_CellEditEnding" event fire?
Comment posted by J. Heiser on Wednesday, February 4, 2015 7:35 AM
Missed the subscription step. Added the events to the button and datagrid. All works well. Thanks Mahesh