Email: Password:       Forgot Password 
    .netCodeSG
A Saarsha Group Online Community for dot net codes group like C#, Asp.NET, VB.NET, Sharepoint, JavaScript, JQuery, Ajax, SQL, WCF, WPF.
 
TECHNOLOGIES:
 

WPF Read Write and Update in an EXCEL File

Article:
Viewed:  2364 
Posted On:  23/11/2014 09:27:49 
How we can read, write & update in an EXCEL file using WPF application ? 

In this article I am going to explain how we can read, write and update data in an Excel file in WPF.

Below is my Excel File#

1.png

Image 1.

It may be a chance user has already open this excel file during read/ write operations, so to avoid File is already in use problem do some below setting.

Go to Review -> Select Share WorkBook.

2.png

Image 2.

Here current user/ Window user should be added.

3.png

Image 3.

4.png

Now close this excel file.

Image 4.

5.png

Image 5.

To perform Create, Read & Update operation in WPF .NET application, we need to use OLEDB connection. So Office System Driver for Data Connectivity must be installed on your machine.

Download the driver from below location.

6.png

Image 6.

If you try to uses an OleDb provider 'Microsoft.ACE.OLEDB.12.0' without installing this driver, the application will throw below 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.

7.png

Image 7.

Now add a new Class in your WPF application.

8.png

Image 8.

Code in my ExcelDataService.cs  is #

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);
        }
 
        /// <summary>
        /// Method to Get All the Records from Excel
        /// </summary>
        /// <returns></returns>
        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;
        }
 
        /// <summary>
        /// Method to Insert Record in the Excel       
        /// </summary>
        /// <param name="Emp"></param>
        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;
 
        }
 
 
 
        /// <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> 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 do below 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" Foreground="Green"
                Content="Showing All Student Information"/>
 
    </Grid>
</Window>
 

Now open MainWindow.xaml.cs :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
 
namespace SchoolManagement_ExcelData
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        ExcelDataService _objExcelSer;
        Student _stud = new Student();
 
        public MainWindow()
        {
            InitializeComponent();
        }
 
 
        /// <summary>
        /// Getting Data From Excel Sheet
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Window_Loaded(object sender, RoutedEventArgs e)
        {
            GetStudentData();
        }
 
        private void GetStudentData()
        {
            _objExcelSer = new ExcelDataService();
            try
            {
                dataGridStudent.ItemsSource = _objExcelSer.ReadRecordFromEXCELAsync().Result;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
 
        private void btnRefreshRecord_Click(object sender, RoutedEventArgs e)
        {
            GetStudentData();
        }
 
        /// <summary>
        /// Getting Data of each cell
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void dataGridStudent_CellEditEnding(object sender, DataGridCellEditEndingEventArgs e)
        {
            try
            {
                FrameworkElement stud_ID = dataGridStudent.Columns[0].GetCellContent(e.Row);
                if (stud_ID.GetType() == typeof(TextBox))
                {
                    _stud.StudentID = Convert.ToInt32(((TextBox)stud_ID).Text);
                }
 
                FrameworkElement stud_Name = dataGridStudent.Columns[1].GetCellContent(e.Row);
                if (stud_Name.GetType() == typeof(TextBox))
                {
                    _stud.Name = ((TextBox)stud_Name).Text;
                }
 
                FrameworkElement stud_Email = dataGridStudent.Columns[2].GetCellContent(e.Row);
                if (stud_Email.GetType() == typeof(TextBox))
                {
                    _stud.Email = ((TextBox)stud_Email).Text;
                }
 
                FrameworkElement stud_Class = dataGridStudent.Columns[3].GetCellContent(e.Row);
                if (stud_Class.GetType() == typeof(TextBox))
                {
                    _stud.Class = ((TextBox)stud_Class).Text;
                }
 
                FrameworkElement stud_Address = dataGridStudent.Columns[4].GetCellContent(e.Row);
                if (stud_Address.GetType() == typeof(TextBox))
                {
                    _stud.Address = ((TextBox)stud_Address).Text;
                }
 
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
 
        /// <summary>
        /// Get entire Row
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void dataGridStudent_RowEditEnding(object sender, DataGridRowEditEndingEventArgs e)
        {
            try
            {
                bool IsSave = _objExcelSer.ManageExcelRecordsAsync(_stud).Result;
                if (IsSave)
                {
                    MessageBox.Show("Student Record Saved Successfully.");
                }                
        else                
                {
                    MessageBox.Show("Some Problem Occured.");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
 
        }
 
        /// <summary>
        /// Get Record info to update
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void dataGridStudent_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            _stud = dataGridStudent.SelectedItem as Student;
        }
    }
}
 

Now Run the Application:

 

9.png

 

Image 9.

 

Now add a new Row.

 

10.png

 

Image 10.

 

Now check Excel File.

 

11.png

 

Image 11.

 

Now Edit any Record.

 

12.png

 

Image 12.

 

Now check Excel File.

 

13.png

 

Image 13.

 

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

 

14.png

Image 14.

 

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

 

15.png

 

Image 15.

  Comment:
         HOME   |   Submit Article   |   Contact Us   |   About Us   |   Terms & Condition   |   Advertise With us