Reading excel file using C# and ExcelDataReader

I want to introduce you in a library that I usually used to read excel file in C#. It’s called ExcelDataReader, it’s lightweight library for reading excel files. I will not discuss more about it, you can just to the github repository to the details about it.

Create a new Windows Forms Project. Then add these controls to make it look like the image below.

  • TextBox named txtPath
  • Button named btnBrowse
  • OpenFileDialog named openFileDialog1
  • DataGridView named resultGrid

Form

You need to install two nuget packages for this. Right click to References then Manage Nuget Packages. Go to Browse tab and search and install ExcelDataReader and ExcelDataReader.DataSet.

install exceldatareader

install exceldataset

Then after installing it, we can now go to code.

First let’s create a Person class. Our person class will only have a property of FirstName and LastName which are both string.

public class Person
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }

Go to form design and double click btnBrowse button to generate a click event method. Then change the code of Click event method to look like the code below.

private void btnBrowse_Click(object sender, EventArgs e)
        {
            this.openFileDialog1.Filter = "Excel Files(.xlsx)|*.xlsx";
            this.openFileDialog1.Title = "Select an excel file";
            if (this.openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                this.txtPath.Text = openFileDialog1.FileName;
                FileStream stream = File.Open(openFileDialog1.FileName, FileMode.Open, FileAccess.Read);
                IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                DataSet result = excelReader.AsDataSet();

                var people = new List<Person>();
                while (excelReader.Read())
                {
                    people.Add(new Person
                    {
                        FirstName = excelReader.GetString(0),
                        LastName = excelReader.GetString(1)
                    });
                }

                this.resultGrid.DataSource = people;
            }
        }

Then we’re done. Run your project to test it.

This is the content of the excel file that I used.

excel file

This should give you this output.

output

The source code for this project is available in my github repository. https://github.com/alexcalingasan/ExcelDataReaderSample

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s