How to Edit or Modify Excel Data in Windows Forms with C#

Want to keep up to date with all my lastest articles and tip?

You can easily modify data in an Excel file from your Windows Forms, popularly known as WinForms. In fact, you can do many things like read, edit and add data to your Excel file from WinForms. Here in this post, I am sharing an example showing how to edit or modify an Excel file or its edit its existing data from Windows Forms using C#.

Check this article if you are VB developer.

Modify or Edit Excel file in WinForms using C#

Here’s a simple scenario. I have an Excel file with employee details and looks like this.

Read Excel from your WinForms Application Using C#

I would select the Excel file from my WinForm application, extract the Employee names (only) and populate a Combo box. This would allow me to select the name whose phone no. and address I can change.

First, you’ll need to add COM library to your project. In your .Net IDE, click the Project tab from the top menu and choose Add Reference… In the Add Reference window, choose the COM tab. Find Microsoft Excel 12.0 Object Library (or any other version) from the list. Click Ok.

Windows Forms Add Reference for Excel

You now have access to the Interop namespace, which provides the necessary methods and properties to access Excel and the Microsoft Office apps.

The Form

Create a C# project and add few controls to the form. Add have two buttons, a combo box and two textboxes. You can add labels too. In addition, add OpenFileDialog (to select files) control to the form.

Using WinForm to Select and Modify Excel Data

The C# Code

using System;
using System.Collections.Generic;
using System.Windows.Forms;

using Excel = Microsoft.Office.Interop.Excel;      // EXCEL APPLICATION.

namespace edit_modify_excel
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        // CREATE EXCEL OBJECTS.
        Excel.Application xlApp = new Excel.Application();
        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWorkSheet;

        string sFileName;
        int iRow, iCol = 2;

        // OPEN AND SELECT THE EXCEL FILE TO EDIT.
        private void cmdSelect_Click(object sender, EventArgs e)
        {
            OpenFileDialog1.Title = "Excel File to Edit";
            OpenFileDialog1.FileName = "";
            OpenFileDialog1.Filter = "Excel File|*.xlsx;*.xls";

            if (OpenFileDialog1.ShowDialog() == DialogResult.OK)
            {
                sFileName = OpenFileDialog1.FileName;

                if (sFileName.Trim() != "")
                {
                    readExcel(sFileName);               // READ EXCEL DATA.
                }
            }
        }

        // GET NAMES FROM EXCEL AND POPULATE COMB0 BOX.
        private void readExcel(string sFile)
        {
            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Open(sFile);
            xlWorkSheet = xlWorkBook.Worksheets["Sheet1"];          // NAME OF THE SHEET.

            // START FROM THE SECOND ROW.
            for (iRow = 2; iRow <= xlWorkSheet.Rows.Count; iRow++)  
            {
                if (xlWorkSheet.Cells[iRow, 1].value == null)
                {
                    break;              // BREAK LOOP.
                }
                else 
                {                       // POPULATE COMBO BOX.
                    cmbEmp.Items.Add(xlWorkSheet.Cells[iRow, 1].value);
                }
            }

            xlWorkBook.Close();
            xlApp.Quit();
        }

        private void cmdSave_Click(object sender, EventArgs e)
        {
            if (sFileName.Trim() != "")
            {
                modifyExcel(sFileName);         // MODIFY DETAILS IN THE FILE.
            }
        }

        // NOW, MODIFY EMPLOYEE DETAILS WITH NEW DETAILS IN THE EXCEL FILE.
        private void modifyExcel(string sFile)
        {
            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Open(sFile);
            xlWorkSheet = xlWorkBook.Worksheets["Sheet1"];        // NAME OF THE SHEET.

            for (iRow = 2; iRow <= xlWorkSheet.Rows.Count; iRow++)      // START FROM THE SECOND ROW.
            {
                if (xlWorkSheet.Cells[iRow, 1].value == null) 
                {
                    break;          // BREAK, IF IT REACHED THE LAST ROW.
                }

                if (xlWorkSheet.Cells[iRow, 1].value == cmbEmp.Text)
                {
                    xlWorkSheet.Cells[iRow, iCol].value = tbMobile.Text;        // MODIFY MOBILE (IN CELL 2).
                    xlWorkSheet.Cells[iRow, iCol + 1].value = tbAddress.Text;   // MODIFY ADDRESS (IN CELL 3).
                }
            }

            xlWorkBook.Close();
            xlApp.Quit();

            // CLEAN UP.
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBook);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkSheet);
        }
    }
}

Also Read: How to Export Data in a WinForm to an Excel Sheet with AutoFormat Feature using C#

It’s a very simple code behind procedure. I have two private methods named readExcel and modifyExcel. Both the methods take a parameter each, in the form of a file name.

The first method readExcel is called after you select the Excel file. Here, it will extract all the Employee names in the first column and populate the Combo box with names.

The Second method modifyExcel will modify the phone number and address of the selected employee.

Well, that’s it.

Previous - Windows Forms App - Export Data to an Excel Sheet with AutoFormat Feature using C#Next - How to Read an Excel file in Windows Forms Application using C# and Vb.Net

Like this Article? Subscribe now, and get all the latest articles and tips, right in your inbox.

Enter your email id

Share this article

Join our Google Plus Community and be a part of a discussion!