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");
ds.Tables.Add(dt);
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;
}
ds.Tables["dtExcel"].Rows.Add(dr);
}

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

// release object
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}

private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Unable to release the Object " + ex.ToString());
}
finally
{
GC.Collect();
}
}

Download source files

Video tutorial

Advertisements

1 Comment

  1. Pingback: Reading Excel files easily in c# using Excel Data Reader | randomtutes

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