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;
  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;
  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. }
  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. }
  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);
  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";
  87. }
  88. int result = await Cmd.ExecuteNonQueryAsync();
  89. if (result > 0)
  90. {
  91. IsSave = true;
  92. }
  93. Conn.Close();
  94. }
  95. return IsSave;
  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. }
  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;
  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();
  26. public MainWindow()
  27. {
  28. InitializeComponent();
  29. }
  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. }
  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. }
  55. private void btnRefreshRecord_Click(object sender, RoutedEventArgs e)
  56. {
  57. GetStudentData();
  58. }
  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. }
  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. }
  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. }
  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. }
  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. }
  99. }
  100. catch (Exception ex)
  101. {
  102. MessageBox.Show(ex.Message);
  103. }
  104. }
  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. }
  130. }
  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