How to Read an Excel file in Windows Forms Application using C# and Vb.Net

Using Excel, you can now maintain complex data structures, create graphs and more. But, do you know you can use Excel data in a Windows Forms Application. Here in this post, I’ll show you how to read an Excel file or import data from an Excel file in your Windows Forms application using C# and Vb.Net and how you can use the data.

To read an Excel file (either .xlsx or .xls) from you Windows Forms Application, you will have to include Microsoft.Office.Interop.Excel namespace in your project. The namespace provides methods and properties that will help read, write and add data to an Excel file.

I have shared three examples (simple codes) showing how you can select and extract data from Excel and how and where you can use this data.

1) The first example imports data from Excel and populates a Combo box.
2) The second example imports data from Excel and binds the data to a DataGridView control.
3) The third example shows how to insert the data extracted data (from Excel) into an SQL Server database table.

First, create a new Windows Form Application project, choosing your preferred language. I have written code in both C# and Vb.

Add “Microsoft.Office.Interop.Excel” Reference

After you have created the project, add a reference. From the top menu (in your projects IDE), select the Project tab and choose Add Reference… It will open the Add Reference window. Select the COM tab and find Microsoft Excel 12.0 Object Library library (or a higher version) from the list. Click Ok. See the image.

Windows Forms Add Reference for Excel

The Excel Data

The data in my Excel file looks like this. I have kept it simple.

Import Excel data in Windows Forms Application

Import the Namespace in your Project

The namespace Microsoft.Office.Interop.Excel has all the necessary methods and properties that you need to connect with your Excel file. However, you will have to import or use the namespace in your project.

For C#

using Excel = Microsoft.Office.Interop.Excel;

For Vb.Net

Imports Excel = Microsoft.Office.Interop.Excel

Note: To access data from a Word file, first add Microsoft Word 12.0 Object Library reference and import or use the namespace. For example,

using Word = Microsoft.Office.Interop.Word;

To select an Excel file from your windows form, you’ll need to add the OpenFileDialog tool to the form. Therefore, go to the design mode of your form. From the toolbox drag and drop OpenFireDialog on the form.

The various ways you can use the extracted data.

1) Read Excel and Populate Data in a Combo Box

The first example shows you how to select, read an Excel file and populate a Combo Box with the data.

In your form, add a button and a Combo Box and name it cmbEmp. See the image.

Read Excel from Your Windows Applications

Double click the button and open the code behind window. We’ll write our code inside the buttons click event.

Clicking the button will open a file dialog box. Selecting the file will call a private procedure, which read the data in the first column (Employee) in Sheet 1 and populate the combo box with the data.

Code Behind (C#)

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

using Excel = Microsoft.Office.Interop.Excel; 

namespace read_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 FILE DIALOG AND SELECT AN EXCEL FILE.
        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);
                }
            }
        }

        // GET DATA FROM EXCEL AND POPULATE COMB0 BOX.
        private void readExcel(string sFile)
        {
            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Open(sFile);           // WORKBOOK TO OPEN THE EXCEL FILE.
            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 LOOP.
                }
                else 
                {               // POPULATE COMBO BOX.
                    cmbEmp.Items.Add(xlWorkSheet.Cells[iRow, 1].value);
                }
            }

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

Note: Alway use try… catch in your code to handle exceptions.

Remember: You can work with multiple worksheets data. Simply add the name of the Sheet.

Code Behind (Vb.Net)

Option Explicit On
Imports Excel = Microsoft.Office.Interop.Excel          ' EXCEL APPLICATION.

Public Class frmImportExcel

    ' CREATE EXCEL OBJECTS.
    Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    Private Sub cmdSelect_Click(sender As System.Object, e As System.EventArgs) Handles cmdSelect.Click
        With OpenFileDialog1
            .Title = "Excel File to Edit"           ' DIALOG BOX TITLE.
            .FileName = ""
            .Filter = "Excel File|*.xlsx;*.xls"     ' FILTER ONLY EXCEL FILES IN FILE TYPE.

            Dim sFileName As String = ""

            If .ShowDialog() = DialogResult.OK Then
                sFileName = .FileName

                If Trim(sFileName) <> "" Then
                    readExcel(sFileName)            ' READ EXCEL DATA.
                End If
            End If
        End With
    End Sub

    ' GET DATA FROM EXCEL AND POPULATE COMB0 BOX.
    Private Sub readExcel(ByVal sFile As String)
        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Open(sFile)            ' WORKBOOK TO OPEN THE EXCEL FILE.
        xlWorkSheet = xlWorkBook.Worksheets("Sheet1")       ' NAME OF THE WORK SHEET.

        Dim iRow As Integer
        For iRow = 2 To xlWorkSheet.Rows.Count
            If Trim(xlWorkSheet.Cells(iRow, 1).value) = "" Then
                Exit For        ' BAIL OUT IF REACHED THE LAST ROW.
            Else
                ' POPULATE COMBO BOX.
                cmbEmp.Items.Add(xlWorkSheet.Cells(iRow, 1).value)
            End If
        Next

        xlWorkBook.Close() : xlApp.Quit()

        ' CLEAN UP. (CLOSE INSTANCES OF EXCEL OBJECTS.)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp) : xlApp = Nothing
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBook) : xlWorkBook = Nothing
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkSheet) : xlWorkSheet = Nothing
    End Sub
End Class

2) Populate a DataGridView Control with the Excel Data

You can populate a DataGridView control dynamically with the Excel data. It is very simple. The data extraction part is similar to the one I have explained in the above example.

First, add a DataGridView control to the form. You can find this control in the toolbox. Now let’s see the code. The form already has the OpenFileDialog control to select files.

Code behind C#

I am extending my previous example above. You’ll just have to modify the readExcel() method. The method takes a parameter in the form of the file name.

private void readExcel(string sFile)
{
    xlApp = new Excel.Application();
    xlWorkBook = xlApp.Workbooks.Open(sFile);              	 
    xlWorkSheet = xlWorkBook.Worksheets["Sheet1"];          

    // FIRST, CREATE THE DataGridView COLUMN HEADERS.
    int iCol;
    for (iCol = 1; iCol <= xlWorkSheet.Columns.Count; iCol++)
    {
        if (xlWorkSheet.Cells[1, iCol].value == null)
        {
            break;      // BREAK LOOP.
        }
        else
        {
            DataGridViewColumn col = new DataGridViewTextBoxColumn();
            col.HeaderText = xlWorkSheet.Cells[1, iCol].value;
            int colIndex = dataGridView1.Columns.Add(col);     // ADD A NEW COLUMN.
        }
    }

    // ADD ROWS TO THE GRID.
    for (iRow = 2; iRow <= xlWorkSheet.Rows.Count; iRow++)  // START FROM THE SECOND ROW.
    {
        if (xlWorkSheet.Cells[iRow, 1].value == null)
        {
            break;      // BREAK LOOP.
        }
        else 
        {
            // CREATE A STRING ARRAY USING THE VALUES IN EACH ROW OF THE SHEET.
            string[] row = new string[] { xlWorkSheet.Cells[iRow, 1].value,  
                xlWorkSheet.Cells[iRow, 2].value.ToString(), 
                xlWorkSheet.Cells[iRow, 3].value };

            // ADD A NEW ROW TO THE GRID USING THE ARRAY DATA.
            dataGridView1.Rows.Add(row);
        }
    }

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

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

I am creating the DataGridView header by running a for loop using the columns in the Excel file. Next, I am running another loop to extract rows in the Excel Sheet and assigning the values to the Grid.

Code Behind (Vb.Net)

Private Sub readExcel(ByVal sFile As String)
    xlApp = New Excel.Application
    xlWorkBook = xlApp.Workbooks.Open(sFile)            
    xlWorkSheet = xlWorkBook.Worksheets("Sheet1")       

    ' FIRST, CREATE THE DataGridView COLUMN HEADERS.
    Dim iCol As Integer
    For iCol = 1 To xlWorkSheet.Columns.Count
        If Trim(xlWorkSheet.Cells(1, iCol).value) = "" Then
            Exit For        ' BAIL OUT IF REACHED THE LAST COL.
        Else
            Dim col = New DataGridViewTextBoxColumn()
            col.HeaderText = xlWorkSheet.Cells(1, iCol).value
            Dim colIndex As Integer = dataGridView1.Columns.Add(col)        ' ADD A NEW COLUMN.
        End If
    Next

    ' ADD ROWS TO THE GRID.
    Dim iRow As Integer
    For iRow = 2 To xlWorkSheet.Rows.Count
        If Trim(xlWorkSheet.Cells(iRow, 1).value) = "" Then
            Exit For        ' BAIL OUT IF REACHED THE LAST ROW.
        Else
            ' CREATE A STRING ARRAY USING THE VALUES IN EACH ROW OF THE SHEET.
            Dim row As String() = New String() { _
                xlWorkSheet.Cells(iRow, 1).value, _
                xlWorkSheet.Cells(iRow, 2).value.ToString(), _
                xlWorkSheet.Cells(iRow, 3).value}

            ' ADD A NEW ROW TO THE GRID USING THE ARRAY DATA.
            dataGridView1.Rows.Add(row)
        End If
    Next

    xlWorkBook.Close() : xlApp.Quit()

    ' CLEAN UP. (CLOSE INSTANCES OF EXCEL OBJECTS.)
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp) : xlApp = Nothing
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBook) : xlWorkBook = Nothing
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkSheet) : xlWorkSheet = Nothing
End Sub

3) Save Excel Data to SQL Server Database Table

You might also want to save the Excel data (as it is) to an SQL Server database table.

Create a table first with three columns and name it Employee.

CREATE TABLE [dbo].[Employee](
	[EmpName] [varchar](50) NULL,
	[Mobile] [bigint] NULL,
	[Address] [varchar](200) NULL
) ON [PRIMARY]

Now the code.

C# Code

using Excel = Microsoft.Office.Interop.Excel;           
using System.Data.SqlClient;			        // FOR DATA CONNECTION.

private void saveExcelData(string sFile)
{
    xlApp = new Excel.Application();
    xlWorkBook = xlApp.Workbooks.Open(sFile);               
    xlWorkSheet = xlWorkBook.Worksheets["Sheet1"];

    string sInsert = "";

    // GET VALUES FROM EXCEL AND CREATE SQL INSERT QUERY.
    for (iRow = 2; iRow <= xlWorkSheet.Rows.Count; iRow++)  // START FROM THE SECOND ROW.
    {
        if (xlWorkSheet.Cells[iRow, 1].value == null)
        {
            break;      // BREAK LOOP.
        }
        else 
        {
            if (sInsert.Trim() == "")
            {
                sInsert = "INSERT INTO dbo.Employee1 (EmpName, Mobile, Address) " +
                    "VALUES ('" + xlWorkSheet.Cells[iRow, 1].value + "', " +
                        xlWorkSheet.Cells[iRow, 2].value + ", '" +
                        xlWorkSheet.Cells[iRow, 3].value + "')";
            }
            else
            {
                sInsert = sInsert + System.Environment.NewLine +
                    "INSERT INTO dbo.Employee1 (EmpName, Mobile, Address) " +
                        "VALUES ('" + xlWorkSheet.Cells[iRow, 1].value + "', " +
                            xlWorkSheet.Cells[iRow, 2].value + ", '" +
                            xlWorkSheet.Cells[iRow, 3].value + "')";
            }
        }
    }

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

    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBook);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkSheet);

    // CREATE DATABASE PROPERTIES.
    using (SqlConnection con = new SqlConnection("Data Source=DNA;Persist Security Info=False;" +
        "Initial Catalog=DNA_Classified;User Id=sa;Password=demo;Connect Timeout=30;"))
    {
        if (sInsert.Trim() != "") {
            SqlCommand command = con.CreateCommand();

            try {
                con.Open();	// OPEN THE CONNECTION.
                command.CommandText = sInsert;    // INSERT THE DATA.
                command.ExecuteNonQuery();

                MessageBox.Show("Data Saved");
            }
            finally {
                con.Close();
            }
        }
    }
}

Inside the for loop extract Excel data row by row and create the INSERT query with values. Create a database connection and insert the data to the database table.

Vb.Net Code

Private Sub saveExcelData(ByVal sFile As String)
    xlApp = New Excel.Application
    xlWorkBook = xlApp.Workbooks.Open(sFile)
    xlWorkSheet = xlWorkBook.Worksheets("Sheet1") 

    Dim iRow As Integer
    Dim sInsert As String = ""

    ' GET VALUES FROM EXCEL AND CREATE SQL INSERT QUERY.
    For iRow = 2 To xlWorkSheet.Rows.Count
        If Trim(xlWorkSheet.Cells(iRow, 1).value) = "" Then
            Exit For
        Else
            If Trim(sInsert) = "" Then
                sInsert = "INSERT INTO dbo.Employee1 (EmpName, Mobile, Address) " & _
                    "VALUES ('" + xlWorkSheet.Cells(iRow, 1).value + "', " & _
                        xlWorkSheet.Cells(iRow, 2).value.ToString + ", '" & _
                        xlWorkSheet.Cells(iRow, 3).value.ToString + "')"
            Else
                sInsert = sInsert & vbCrLf & _
                    "INSERT INTO dbo.Employee1 (EmpName, Mobile, Address) " & _
                        "VALUES ('" + xlWorkSheet.Cells(iRow, 1).value + "', " & _
                            xlWorkSheet.Cells(iRow, 2).value.ToString + ", '" & _
                            xlWorkSheet.Cells(iRow, 3).value + "')"
            End If
        End If
    Next

    xlWorkBook.Close() : xlApp.Quit()

    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp) : xlApp = Nothing
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBook) : xlWorkBook = Nothing
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkSheet) : xlWorkSheet = Nothing

    // CREATE DATABASE PROPERTIES.
    Using con As SqlConnection = New SqlConnection("Data Source=DNA;Persist Security Info=False;" & _
        "Initial Catalog=DNA_Classified;User Id=sa;Password=demo;Connect Timeout=30;")

        If Trim(sInsert) <> "" Then
            Try
                Dim command As SqlCommand = con.CreateCommand()
                con.Open()
                command.CommandText = sInsert
                command.ExecuteNonQuery()

                MessageBox.Show("Data Saved")
            Finally
                con.Close()
            End Try
        End If
    End Using
End Sub

Well, that’s it. Hope you find the examples useful. Thanks for reading ☺.

← PreviousNext →