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(); } }
Advertisements
One thought on “Reading excel file in c#”