How to read from a Excel file using sheet index RRS feed

  • Question

  • User1150900820 posted


    Here is the sample code that read data from Excel and dump into a DataTable.

    string Connection = "Provider=microsoft.jet.oledb.4.0; Data Source=" + filepath + "; Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\";";
    OleDbConnection con = new OleDbConnection(Connection);
    DataTable dt = new DataTable();
    OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", con);
    But what I need is get data by Sheet ID instead of Sheet Name. Because I've to read Excel files which have variable Sheet Name. 
    Can anyone help..??
    Thanks in advance/Tanvir
    Thursday, March 22, 2012 2:18 PM


All replies

  • User-2016185570 posted

    Basic sample:

    Dim workbook As Excel.Workbook = Nothing
    workbook = appOP.Workbooks.Open(strFileName, 2, False, 5, "", "", True, Excel.XlPlatform.xlWindows, vbTab, False, True, 0, True, 1, 0)

    Dim SheetRChart As Object = Nothing
    Dim nTotalWorkSheets As Integer = workbook.Sheets.Count
    For nWorkSheet As Integer = 1 To nTotalWorkSheets - 2
        SheetRChart = workbook.Sheets(DirectCast(nWorkSheet, Object))
        If TypeOf SheetRChart Is Excel.Worksheet Then
            strSheetname = DirectCast(SheetRChart, Excel.Worksheet).Name
        End If


    Thursday, March 22, 2012 2:30 PM
  • User1839833660 posted

    you need to use GetOleDbSchemaTable method 

    Refer upload and read excel file in asp.net 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 22, 2012 2:37 PM