Retrieving Data From Excel Files Having Multiple Worksheets In ASP .NET

While working with ASP.NET we may come across a situation where we need to import some data from excel files having multiple work sheets and need to insert/update the data in the database accordingly. Following example demonstrates the way to fetch the data from excel file having multiple sheets and use those data for insertion/updation.

So first of all we have to connect to the Microsoft Excel workbook using the OLEDB.NET data provider

Dim dAdapter As System.Data.OleDb.OleDbDataAdapter
          Dim conn As System.Data.OleDb.OleDbConnection
 
           conn = New System.Data.OleDb.OleDbConnection( _
                  "provider=Microsoft.Jet.OLEDB.4.0; " & _
                  "data source=" & dataSourceFileName & "; " & _
                  "Extended Properties=""Excel 8.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text""")
 
          //Opening the connection
          If conn.State = ConnectionState.Closed Then
              conn.Open()
          End If

Here the ‘dataSourceFileName ‘ refers to the excel file(along with the path) from which we want to fetch the data. Then we have to load the workbook that is present in that Excel file to a workbook object. Through this workbook object we will able to get all the worksheets available and can use them as per our requirement.

Dim objWorkBook As Workbook = Workbook.Load(dataSourceFileName)
         Dim objWorkSheet As Worksheet
         Dim workSheetName As String = String.Empty
 
         For Each objWorkSheet In objWorkBook.Worksheets
             workSheetName = objWorkSheet.Name
             Exit For
         Next objWorkSheet

Here in the above code snippet I have only retrieved the first worksheet from the workbook(using Exit For). As per your requirement you can iterate through the workbook to get all the worksheets.
Then we have to retrieve the data from the excel sheet as per the condition and fill a dataset with that data.

dAdapter = New System.Data.OleDb.OleDbDataAdapter("select * from [" & workSheetName & "$]", conn)
           Dim requiredDataSet As New System.Data.DataSet
           Dim requiredDataTable As New DataTable
              
            dAdapter.Fill(requiredDataSet )
           requiredDataTable = requiredDataSet .Tables(0)

Here while retrieving data I am fetching all the the records from the worksheet. You can give the condition over there to fetch the data conditionally as per the requirement. Now we have the data table with us which contains all the data as per the condition. So next to it we can use this data table inorder to insert/update data in the database.