How to read data from an excel sheet using asp.net C# code

How to read data from an excel sheet using asp.net C# code

Sometimes one needs to access data from excel sheet using code to perform some action using that data.

Taking dynamics crm into consideration one scenario may be that where we have a list of records (unique id or GUID of records) which we have to update from back-end using asp.net C# code.

Here we have step by step code to read data from excel sheet and free to do any action on that data in other system:

Ex: We want to read an excel sheet located at "d:\testexcel":

  Our testexcel have below data in it:

Through our demo application we'll read the data in this sheet and print it on console:

Step 1: Create a basic .Net console application in Visual Studio.

Step 2: In the solution explorer in right hand side,right click on reference and click add reference:

Step 3: Select "Microsoft Office 16.0 Object Library" and click on add:

Step 4: Include "using Excel = Microsoft.Office.Interop.Excel;" :

Step 5:  Add below function in you code to read the excel sheet:

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

 public static void ReadExcel()

        {

            Excel.Application xlApp;

            Excel.Workbook xlWorkBook;

            Excel.Worksheet xlWorkSheet;

            Excel.Range range;

            string str;

            int RowCount;

            int ColumnCount;

            int TotalRow = 0;

            int TotalColumn = 0;

            xlApp = new Excel.Application();

            xlWorkBook = xlApp.Workbooks.Open(@"d:\testexcel", 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;

            TotalRow = range.Rows.Count;

            TotalColumn = range.Columns.Count;

            for (RowCount = 1; RowCount <= TotalRow; RowCount++)

            {

                for (ColumnCount = 1; ColumnCount <= TotalColumn; ColumnCount++)

                {

                    str = (string)(range.Cells[RowCount, ColumnCount] as Excel.Range).Value2;

                    Console.Write(str);

                    Console.Write("\t");

                }

                Console.Write("\n");

            }

            xlWorkBook.Close(true, null, null);

            xlApp.Quit();

        }

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Below will be the output of above code on console screen:

Now you can manipulate the above code to use the data red from excel as per your requirement.

Hope it was helpful.