#1: Reading MS Excel File

Microsoft Excel is one of the best software by Microsoft which is bundled in Microsoft Office. It is often used for calculating and summarizing data. MS Excel has been widely used in business and in schools. Excel data format are often used to exchange information from one system to another. This blog is all about how to read Excel data within the .Net application.

I created this blog to help developers for .Net applications. Its my way of sharing my knowledge and skills to anybody interested coding in .Net. This is my first blog, so I hope you like it.

We only need to add Microsoft.Office.Interop.Excel to our reference. It is found in the .Net component library. Check this out;

 

Next thing is to follow the code below;

First, select an Excel file. You can use the OpenDialog to get the file. But on this demo, I use a fixed path. After getting the Excel file to read, we need to create a DataTable where we store our data. Then we will specify the Excel file path and the worksheet number

               Dim oTable As New DataTable
               Dim oRow As DataRow

                Dim xlApp = New Excel.Application
                Dim xlWorkBook = xlApp.Workbooks.Open("E:\MyFiles\sample.xls")
                Dim xlWorkSheet = xlWorkBook.Worksheets(1)
                Dim range = xlWorkSheet.UsedRange

                For cCnt = 1 To range.Columns.Count
                    oTable.Columns.Add("Column" + cCnt.ToString)
                 Next              

                For rCnt = CInt(txbRowTo.Text) + 1 To range.Rows.Count
                    oRow = oTable.NewRow

                     For cCnt = 1 To range.Columns.Count
                        oRow("Column" + cCnt.ToString) = xlWorkSheet.Cells(rCnt, cCnt).value.ToString
                    Next

                    oTable.Rows.Add(oRow)

                Next

 
That's it and we're done. Its easy right? I am using the VB.Net code but you can easily convert this in C#. This code is very useful specially if we have multiple sources of data and we want it to compile in a single database. If you have questions or something to discuss with me, please email at mrleefh78@gmail.com

Thank you for reading my blog.  I hope it helps you in a little way. Happy coding and best of luck to your projects!