How to read simple excel file in c#.net Or Import excel file in C#.net

Previously I have explained the code for saving data to Excel file. But here I am going to give the C#.Net code to read data from excel file row by row and column by column automatically. You no need to specify the column to read.

The below code will read all the row and column data field and assign to string value. You can modify the saving methodology to DataTable or List which other way you want.

Source Code:

 

private void LoadICMClient()

        {

            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

            if (xlApp == null)

            {

                MessageBox.Show("Excel is not properly installed!!");

                return;

            }

            Microsoft.Office.Interop.Excel.Workbook xlWorkBook;

            Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;

            object misValue = System.Reflection.Missing.Value;

            Microsoft.Office.Interop.Excel.Range range;

            string str;

            int rCnt = 0;

            int cCnt = 0;

            xlWorkBook = xlApp.Workbooks.Open(Directory.GetCurrentDirectory() + @"\Reports\Test.xls", 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

            xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            range = xlWorkSheet.UsedRange;

            for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)

            {

                for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)

                {

                    str = (string)(range.Cells[rCnt, cCnt] as Microsoft.Office.Interop.Excel.Range).Value2;

                    MessageBox.Show(str);

                }

            }

            xlWorkBook.Close(true, null, null);

            xlApp.Quit();

            releaseObject(xlWorkSheet);

            releaseObject(xlWorkBook);

            releaseObject(xlApp);

        }

The below code will release the Excel dll object and clear the reference info.

private void releaseObject(object obj)

        {

            try

            {

                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);

                obj = null;

            }

            catch (Exception ex)

            {

                obj = null;

                MessageBox.Show("Exception Occured while releasing object " + ex.ToString());

            }

            finally

            {

                GC.Collect();

            }

        }