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

I am a passionate software developer. I develop software using microsoft technologies and other trending web technologies. currently works at sitecore as a developer

One thought on “Reading excel file in c#

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s