Reading Excel files easily in c# using Excel Data Reader

So I found a useful Package to read excel files easily. No more cumbersome codes needed.

I have written post on How to read Excel file in c# in here . But lot of codes.

So I found ExcelDataReader package in github.

Excel Data Reader

It is awesome.

Just few lines of codes to read files as below.

string filePath = "Book1.xlsx";
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);

IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();



Download sample project file from github

Reading excel file in c#

Some times Reading Excel files can be bit cumbersome. I needed to allow user to upload excel document and read data from excel file. So I followed following way to get it done.

private void button1_Click(object sender, EventArgs e)
DataSet ds = new DataSet();
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
Excel.Range range;

xlWorkBook = xlApp.Workbooks.Open(Application.StartupPath + "\\sample.xlsx", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

range = xlWorkSheet.UsedRange;

// read data and bind to grid view
System.Data.DataTable dt = new System.Data.DataTable("dtExcel");
DataRow dr;
StringBuilder sb = new StringBuilder();
int jValue = xlWorkSheet.UsedRange.Cells.Columns.Count;
int iValue = xlWorkSheet.UsedRange.Cells.Rows.Count;
for (int j = 1; j <= jValue; j++)
dt.Columns.Add("column" + j, System.Type.GetType("System.String"));

for (int i = 1; i <= iValue; i++)
dr = ds.Tables["dtExcel"].NewRow();
for (int j = 1; j <= jValue; j++)
range = (Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[i, j];
string strValue = range.Text.ToString();
dr["column" + j] = strValue;

dataGridView1.DataSource = ds.Tables[0];
xlWorkBook.Close(true, null, null);

// release object

private void releaseObject(object obj)
obj = null;
catch (Exception ex)
obj = null;
MessageBox.Show("Unable to release the Object " + ex.ToString());

Download source files

Video tutorial

Serializing Lists of Classes to XML

So some times we want to convert object to xml. We can follow severel cumbersome methods. So I am using Utlity methods to convert c# object to xml and xml to c# object.

Suppose we have class like below

public class Student
 public int Id { get; set; }
 public string FirstName { get; set; }
 public string LastName { get; set; }

And we want serialize List<Student> to xml. So we can use this utlity function

/// <summary>
/// Serialize object to xml
/// </summary>
/// <typeparam name="T">Object type</typeparam>
/// <param name="obj">Object</param>
/// <returns>XML</returns>
public static string ToXml<T>(T obj)
using (StringWriter stringWriter = new StringWriter(new StringBuilder(), CultureInfo.InvariantCulture))
XmlSerializer xmlSerializer = new XmlSerializer(typeof(T));
xmlSerializer.Serialize(stringWriter, obj);
return stringWriter.ToString();

As a example we populate List<student>  with dummy data

 List <Student> studentList = new List<Student>();

studentList.Add(new Student {
 FirstName = "jeevan",
 LastName = "JLast name",
 Id = 1

studentList.Add(new Student
 FirstName = "john",
 LastName = "johnLast name",
 Id = 2

Then call out utility function

string xml = Utility.ToXml(studentList);

then out xml variable look like as below

<?xml version="1.0" encoding="utf-16"?>
<ArrayOfStudent xmlns:xsi="" xmlns:xsd="">
<LastName>JLast name</LastName>
<LastName>johnLast name</LastName>