WPF

Read, Write and Update an Excel File in WPF

Here you see how to read, write and update data in an Excel file in WPF.

The following is my Excel file:


Image 1

It may be that user has already opened this Excel file during read/write operations, so to avoid the file is already in use problem do some of the following settings.

Go to Review then select Share WorkBook.


Image 2

Here the current user/window user should be added.


Image 3

Now close this Excel file.


Image 4


Image 5

To do the Create, Read and Update operations in a WPF .NET application, we need to use an OLEDB connection. So the Office System Driver for Data Connectivity must be installed in your machine.

Download the driver from the following location.


Image 6

If you try to use the OleDb provider "Microsoft.ACE.OLEDB.12.0" without installing this driver, the application will throw the following exception.

“The microsoft.ace.oledb.12.0' provider is not registered on the local machine”.

After installing this driver now create a new WPF application.

Open Visual Studio -> New Project.


Image 7

Now add a new class to your WPF application.


Image 8

The following shows my code in my ExcelDataService.cs. Here in this class I am also using a class Student.

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Collections.ObjectModel;  
  4. using System.Data.OleDb;  
  5. using System.Linq;  
  6. using System.Text;  
  7. using System.Threading.Tasks;  
  8.   
  9. namespace SchoolManagement_ExcelData  
  10. {  
  11.     public class Student  
  12.     {  
  13.         public int StudentID { get; set; }  
  14.         public string Name { get; set; }  
  15.         public string Email { get; set; }  
  16.         public string Class { get; set; }  
  17.         public string Address { get; set; }  
  18.     }  
  19.     public class ExcelDataService  
  20.     {  
  21.         OleDbConnection Conn;  
  22.         OleDbCommand Cmd;  
  23.   
  24.         public ExcelDataService()  
  25.         {   
  26.             string ExcelFilePath = @"H:\\SchoolManagement.xlsx";  
  27.             string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 12.0;Persist Security Info=True";  
  28.             Conn = new OleDbConnection(excelConnectionString);  
  29.         }  
  30.   
  31.         /// <summary>  
  32.         /// Method to Get All the Records from Excel  
  33.         /// </summary>  
  34.         /// <returns></returns>  
  35.         public async Task<ObservableCollection<Student>> ReadRecordFromEXCELAsync()  
  36.         {  
  37.             ObservableCollection<Student> Students = new ObservableCollection<Student>();  
  38.             await Conn.OpenAsync();  
  39.             Cmd = new OleDbCommand();  
  40.             Cmd.Connection = Conn;  
  41.             Cmd.CommandText = "Select * from [Sheet1$]";  
  42.             var Reader = await Cmd.ExecuteReaderAsync();  
  43.             while (Reader.Read())  
  44.             {  
  45.                 Students.Add(new Student()  
  46.                 {  
  47.                     StudentID = Convert.ToInt32(Reader["StudentID"]),  
  48.                     Name = Reader["Name"].ToString(),  
  49.                     Email = Reader["Email"].ToString(),  
  50.                     Class = Reader["Class"].ToString(),  
  51.                     Address = Reader["Address"].ToString()  
  52.                 });  
  53.             }  
  54.             Reader.Close();  
  55.             Conn.Close();  
  56.             return Students;  
  57.         }  
  58.   
  59.         /// <summary>  
  60.         /// Method to Insert Record in the Excel  
  61.         /// S1. If the EmpNo =0, then the Operation is Skipped.  
  62.         /// S2. If the Student is already exist, then it is taken for Update  
  63.         /// </summary>  
  64.         /// <param name="Emp"></param>  
  65.         public async Task<bool> ManageExcelRecordsAsync(Student stud)  
  66.         {  
  67.             bool IsSave = false;  
  68.             if (stud.StudentID != 0)  
  69.             {  
  70.                 await Conn.OpenAsync();  
  71.                 Cmd = new OleDbCommand();  
  72.                 Cmd.Connection = Conn;  
  73.                 Cmd.Parameters.AddWithValue("@StudentID", stud.StudentID);  
  74.                 Cmd.Parameters.AddWithValue("@Name", stud.Name);  
  75.                 Cmd.Parameters.AddWithValue("@Email", stud.Email);  
  76.                 Cmd.Parameters.AddWithValue("@Class", stud.Class);  
  77.                 Cmd.Parameters.AddWithValue("@Address", stud.Address);  
  78.   
  79.                 if (!IsStudentRecordExistAsync(stud).Result)  
  80.                 {  
  81.                     Cmd.CommandText = "Insert into [Sheet1$] values (@StudentID,@Name,@Email,@Class,@Address)";  
  82.                 }  
  83.                 else  
  84.                 {  
  85.                     Cmd.CommandText = "Update [Sheet1$] set StudentID=@StudentID,Name=@Name,Email=@Email,Class=@Class,Address=@Address where StudentID=@StudentID";  
  86.   
  87.                 }  
  88.                 int result = await Cmd.ExecuteNonQueryAsync();  
  89.                 if (result > 0)  
  90.                 {  
  91.                     IsSave = true;  
  92.                 }  
  93.                 Conn.Close();  
  94.             }  
  95.             return IsSave;  
  96.   
  97.         }        
  98.         /// <summary>  
  99.         /// The method to check if the record is already available   
  100.         /// in the workgroup  
  101.         /// </summary>  
  102.         /// <param name="emp"></param>  
  103.         /// <returns></returns>  
  104.         private async Task<bool> IsStudentRecordExistAsync(Student stud)  
  105.         {  
  106.             bool IsRecordExist = false;  
  107.             Cmd.CommandText = "Select * from [Sheet1$] where StudentId=@StudentID";  
  108.             var Reader = await Cmd.ExecuteReaderAsync();  
  109.             if (Reader.HasRows)  
  110.             {  
  111.                 IsRecordExist = true;  
  112.             }  
  113.   
  114.             Reader.Close();  
  115.             return IsRecordExist;  
  116.         }  
  117.     }  
  118. }  
Now open MainWindow.xaml and use the following code:
  1. <Window x:Class="SchoolManagement_ExcelData.MainWindow"  
  2.         xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"  
  3.         xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"  
  4.         Title="Manage Excel Data" Height="350" Width="575"  
  5.         Loaded="Window_Loaded" Background="SkyBlue">  
  6.     <Grid>  
  7.         <Grid.RowDefinitions>  
  8.             <RowDefinition Height="40*"/>  
  9.             <RowDefinition Height="202*"/>  
  10.         </Grid.RowDefinitions>  
  11.         <Button Content="Refresh Record"   
  12.                 Name="btnRefreshRecord" Grid.Row="0"  
  13.                 FontSize="16" Click="btnRefreshRecord_Click" Margin="342,1,26,5"/>  
  14.             <DataGrid Name="dataGridStudent" AutoGenerateColumns="False" ColumnWidth="*"  RowBackground="WhiteSmoke"  
  15.                    CellEditEnding="dataGridStudent_CellEditEnding" RowEditEnding="dataGridStudent_RowEditEnding"  
  16.                    SelectionChanged="dataGridStudent_SelectionChanged" Grid.Row="2" Background="LightBlue">  
  17.             <DataGrid.Columns>  
  18.                 <DataGridTextColumn Header="Student ID" Binding="{Binding StudentID}" Width="70"></DataGridTextColumn>  
  19.                 <DataGridTextColumn Header="Name" Binding="{Binding Name}"></DataGridTextColumn>  
  20.                 <DataGridTextColumn Header="Email" Binding="{Binding Email}" Width="140"></DataGridTextColumn>  
  21.                 <DataGridTextColumn Header="Class" Binding="{Binding Class}" Width="80"></DataGridTextColumn>  
  22.                 <DataGridTextColumn Header="Address" Binding="{Binding Address}" Width="170"></DataGridTextColumn>  
  23.             </DataGrid.Columns>  
  24.         </DataGrid>  
  25.         <Label  HorizontalAlignment="Left" Margin="20,10,0,0" Grid.Row="0" VerticalAlignment="Top"  FontWeight="Bold" FontSize="16" Foreg         round="Green" Content="Showing All Student Information"/>  
  26.     </Grid>  
  27. </Window>  
Now open MainWindow.xaml.cs:
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Text;  
  5. using System.Threading.Tasks;  
  6. using System.Windows;  
  7. using System.Windows.Controls;  
  8. using System.Windows.Data;  
  9. using System.Windows.Documents;  
  10. using System.Windows.Input;  
  11. using System.Windows.Media;  
  12. using System.Windows.Media.Imaging;  
  13. using System.Windows.Navigation;  
  14. using System.Windows.Shapes;  
  15.   
  16. namespace SchoolManagement_ExcelData  
  17. {  
  18.     /// <summary>  
  19.     /// Interaction logic for MainWindow.xaml  
  20.     /// </summary>  
  21.     public partial class MainWindow : Window  
  22.     {  
  23.         ExcelDataService _objExcelSer;  
  24.         Student _stud = new Student();  
  25.   
  26.         public MainWindow()  
  27.         {  
  28.             InitializeComponent();  
  29.         }  
  30.   
  31.   
  32.         /// <summary>  
  33.         /// Getting Data From Excel Sheet  
  34.         /// </summary>  
  35.         /// <param name="sender"></param>  
  36.         /// <param name="e"></param>  
  37.         private void Window_Loaded(object sender, RoutedEventArgs e)  
  38.         {  
  39.             GetStudentData();  
  40.         }  
  41.   
  42.         private void GetStudentData()  
  43.         {  
  44.             _objExcelSer = new ExcelDataService();  
  45.             try  
  46.             {  
  47.                 dataGridStudent.ItemsSource = _objExcelSer.ReadRecordFromEXCELAsync().Result;  
  48.             }  
  49.             catch (Exception ex)  
  50.             {  
  51.                 MessageBox.Show(ex.Message);  
  52.             }  
  53.         }  
  54.   
  55.         private void btnRefreshRecord_Click(object sender, RoutedEventArgs e)  
  56.         {  
  57.             GetStudentData();  
  58.         }  
  59.   
  60.         /// <summary>  
  61.         /// Getting Data of each cell  
  62.         /// </summary>  
  63.         /// <param name="sender"></param>  
  64.         /// <param name="e"></param>  
  65.         private void dataGridStudent_CellEditEnding(object sender, DataGridCellEditEndingEventArgs e)  
  66.         {  
  67.             try  
  68.             {  
  69.                 FrameworkElement stud_ID = dataGridStudent.Columns[0].GetCellContent(e.Row);  
  70.                 if (stud_ID.GetType() == typeof(TextBox))  
  71.                 {  
  72.                     _stud.StudentID = Convert.ToInt32(((TextBox)stud_ID).Text);  
  73.                 }  
  74.   
  75.                 FrameworkElement stud_Name = dataGridStudent.Columns[1].GetCellContent(e.Row);  
  76.                 if (stud_Name.GetType() == typeof(TextBox))  
  77.                 {  
  78.                     _stud.Name = ((TextBox)stud_Name).Text;  
  79.                 }  
  80.   
  81.                 FrameworkElement stud_Email = dataGridStudent.Columns[2].GetCellContent(e.Row);  
  82.                 if (stud_Email.GetType() == typeof(TextBox))  
  83.                 {  
  84.                     _stud.Email = ((TextBox)stud_Email).Text;  
  85.                 }  
  86.   
  87.                 FrameworkElement stud_Class = dataGridStudent.Columns[3].GetCellContent(e.Row);  
  88.                 if (stud_Class.GetType() == typeof(TextBox))  
  89.                 {  
  90.                     _stud.Class = ((TextBox)stud_Class).Text;  
  91.                 }  
  92.   
  93.                 FrameworkElement stud_Address = dataGridStudent.Columns[4].GetCellContent(e.Row);  
  94.                 if (stud_Address.GetType() == typeof(TextBox))  
  95.                 {  
  96.                     _stud.Address = ((TextBox)stud_Address).Text;  
  97.                 }  
  98.   
  99.             }  
  100.             catch (Exception ex)  
  101.             {  
  102.                 MessageBox.Show(ex.Message);  
  103.             }  
  104.         }  
  105.   
  106.         /// <summary>  
  107.         /// Get entire Row  
  108.         /// </summary>  
  109.         /// <param name="sender"></param>  
  110.         /// <param name="e"></param>  
  111.         private void dataGridStudent_RowEditEnding(object sender, DataGridRowEditEndingEventArgs e)  
  112.         {  
  113.             try  
  114.             {  
  115.                 bool IsSave = _objExcelSer.ManageExcelRecordsAsync(_stud).Result;  
  116.                 if (IsSave)  
  117.                 {  
  118.                     MessageBox.Show("Student Record Saved Successfully.");  
  119.                 }  
  120.                 else  
  121.                 {  
  122.                     MessageBox.Show("Some Problem Occured.");  
  123.                 }  
  124.             }  
  125.             catch (Exception ex)  
  126.             {  
  127.                 MessageBox.Show(ex.Message);  
  128.             }  
  129.   
  130.         }  
  131.   
  132.         /// <summary>  
  133.         /// Get Record info to update  
  134.         /// </summary>  
  135.         /// <param name="sender"></param>  
  136.         /// <param name="e"></param>  
  137.         private void dataGridStudent_SelectionChanged(object sender, SelectionChangedEventArgs e)  
  138.         {  
  139.             _stud = dataGridStudent.SelectedItem as Student;  
  140.         }  
  141.     }  
  142. }  
Now run the application:


Image 9

Now add a new Row.


Image 10

Now check the Excel the file.


Image 11

Now edit any record.


Image 12

Now check the Excel file.


Image 13

Now see what event is firing on what action in my application:


Image 14

I saved my Excel file inside the Application folder. To run the application with my Excel sheet you can save it your system and change the Excel File Path in ExcelDataService.cs.


Image 15

Up Next
    Ebook Download
    View all
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs