Windows Forms: How to read an excel (.xls/.xlsx) file in C#

By FoxLearn 5/24/2017 8:59:35 PM   6.36K
How to read an excel (*.xls/.xlsx) file in C# using ExcelDataReader, lightweight and fast library written in C# for reading Microsoft Excel files ('97-2007)

Step 1Click New Project, then select Visual C# on the left, then Windows and then select Windows Forms Application. Name your project "ReadExcelFile" and then click OK

excel data readerStep 2: Right click on your project select Manage NuGet Packages -> Search ExcelDataReader -> Install

install excel data readerStep 3: Design your form as below

excel data reader

Create an excel file, then copy data from Northwind database to the excel file

excel data readerStep 4: Add code to handle your form

using Excel;
using System;
using System.Data;
using System.IO;
using System.Windows.Forms;

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

        DataSet result;

        private void btnOpen_Click(object sender, EventArgs e)
        {
            using(OpenFileDialog ofd = new OpenFileDialog() { Filter= "Excel Workbook 97-2003|*.xls|Excel Workbook|*.xlsx", ValidateNames = true })
            {
                if (ofd.ShowDialog() == DialogResult.OK)
                {
                    FileStream fs = File.Open(ofd.FileName, FileMode.Open, FileAccess.Read);
                    IExcelDataReader reader;
                    if (ofd.FilterIndex == 1)
                        reader = ExcelReaderFactory.CreateBinaryReader(fs);//Read excel 97-2003
                    else
                        reader = ExcelReaderFactory.CreateOpenXmlReader(fs);//Read excel 2007
                    reader.IsFirstRowAsColumnNames = true;
                    result = reader.AsDataSet();
                    cboSheet.Items.Clear();
                    //Add sheet to comboxbox
                    foreach (DataTable dt in result.Tables)
                        cboSheet.Items.Add(dt.TableName);
                    reader.Close();
                }
            }
        }

        private void cboSheet_SelectedIndexChanged(object sender, EventArgs e)
        {
            //Fill data from excel into DataGridView based on sheet selection
            dataGridView.DataSource = result.Tables[cboSheet.SelectedIndex];
        }
    }
}

VIDEO TUTORIALS