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.
- using System;
- using System.Collections.Generic;
- using System.Collections.ObjectModel;
- using System.Data.OleDb;
- using System.Linq;
- using System.Text;
- using System.Threading.Tasks;
-
- namespace SchoolManagement_ExcelData
- {
- public class Student
- {
- public int StudentID { get; set; }
- public string Name { get; set; }
- public string Email { get; set; }
- public string Class { get; set; }
- public string Address { get; set; }
- }
- public class ExcelDataService
- {
- OleDbConnection Conn;
- OleDbCommand Cmd;
-
- public ExcelDataService()
- {
- string ExcelFilePath = @"H:\\SchoolManagement.xlsx";
- string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 12.0;Persist Security Info=True";
- Conn = new OleDbConnection(excelConnectionString);
- }
-
-
-
-
-
- public async Task<ObservableCollection<Student>> ReadRecordFromEXCELAsync()
- {
- ObservableCollection<Student> Students = new ObservableCollection<Student>();
- await Conn.OpenAsync();
- Cmd = new OleDbCommand();
- Cmd.Connection = Conn;
- Cmd.CommandText = "Select * from [Sheet1$]";
- var Reader = await Cmd.ExecuteReaderAsync();
- while (Reader.Read())
- {
- Students.Add(new Student()
- {
- StudentID = Convert.ToInt32(Reader["StudentID"]),
- Name = Reader["Name"].ToString(),
- Email = Reader["Email"].ToString(),
- Class = Reader["Class"].ToString(),
- Address = Reader["Address"].ToString()
- });
- }
- Reader.Close();
- Conn.Close();
- return Students;
- }
-
-
-
-
-
-
-
- public async Task<bool> ManageExcelRecordsAsync(Student stud)
- {
- bool IsSave = false;
- if (stud.StudentID != 0)
- {
- await Conn.OpenAsync();
- Cmd = new OleDbCommand();
- Cmd.Connection = Conn;
- Cmd.Parameters.AddWithValue("@StudentID", stud.StudentID);
- Cmd.Parameters.AddWithValue("@Name", stud.Name);
- Cmd.Parameters.AddWithValue("@Email", stud.Email);
- Cmd.Parameters.AddWithValue("@Class", stud.Class);
- Cmd.Parameters.AddWithValue("@Address", stud.Address);
-
- if (!IsStudentRecordExistAsync(stud).Result)
- {
- Cmd.CommandText = "Insert into [Sheet1$] values (@StudentID,@Name,@Email,@Class,@Address)";
- }
- else
- {
- Cmd.CommandText = "Update [Sheet1$] set StudentID=@StudentID,Name=@Name,Email=@Email,Class=@Class,Address=@Address where StudentID=@StudentID";
-
- }
- int result = await Cmd.ExecuteNonQueryAsync();
- if (result > 0)
- {
- IsSave = true;
- }
- Conn.Close();
- }
- return IsSave;
-
- }
-
-
-
-
-
-
- private async Task<bool> IsStudentRecordExistAsync(Student stud)
- {
- bool IsRecordExist = false;
- Cmd.CommandText = "Select * from [Sheet1$] where StudentId=@StudentID";
- var Reader = await Cmd.ExecuteReaderAsync();
- if (Reader.HasRows)
- {
- IsRecordExist = true;
- }
-
- Reader.Close();
- return IsRecordExist;
- }
- }
- }
Now open MainWindow.xaml and use the following code:
- <Window x:Class="SchoolManagement_ExcelData.MainWindow"
- xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
- xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
- Title="Manage Excel Data" Height="350" Width="575"
- Loaded="Window_Loaded" Background="SkyBlue">
- <Grid>
- <Grid.RowDefinitions>
- <RowDefinition Height="40*"/>
- <RowDefinition Height="202*"/>
- </Grid.RowDefinitions>
- <Button Content="Refresh Record"
- Name="btnRefreshRecord" Grid.Row="0"
- FontSize="16" Click="btnRefreshRecord_Click" Margin="342,1,26,5"/>
- <DataGrid Name="dataGridStudent" AutoGenerateColumns="False" ColumnWidth="*" RowBackground="WhiteSmoke"
- CellEditEnding="dataGridStudent_CellEditEnding" RowEditEnding="dataGridStudent_RowEditEnding"
- SelectionChanged="dataGridStudent_SelectionChanged" Grid.Row="2" Background="LightBlue">
- <DataGrid.Columns>
- <DataGridTextColumn Header="Student ID" Binding="{Binding StudentID}" Width="70"></DataGridTextColumn>
- <DataGridTextColumn Header="Name" Binding="{Binding Name}"></DataGridTextColumn>
- <DataGridTextColumn Header="Email" Binding="{Binding Email}" Width="140"></DataGridTextColumn>
- <DataGridTextColumn Header="Class" Binding="{Binding Class}" Width="80"></DataGridTextColumn>
- <DataGridTextColumn Header="Address" Binding="{Binding Address}" Width="170"></DataGridTextColumn>
- </DataGrid.Columns>
- </DataGrid>
- <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"/>
- </Grid>
- </Window>
Now open MainWindow.xaml.cs:
Now run the application:
Image 9Now add a new Row.
Image 10
Now check the Excel the file.
Image 11Now edit any record.
Image 12Now check the Excel file.
Image 13Now see what event is firing on what action in my application:
Image 14I 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