CODEWITHSUNDEEP
c#sql-serverexcel
codeFinatic
codeFinatic

Reputation: 171

Getting Sheet Name From Excel

I am uploading excel files to a SQL Server database. I am currently using this line to get the data from the sheet:

string myQuery = "Select * from [Sheet1$]";

The problem is, if the sheet name isn't Sheet1 then it will fail. Is there a way to get the sheet name rather than hard coding in Sheet1?

Upvotes: 1

Views: 386

Answers (2)

user6748024
user6748024

Reputation: 

Function GetSheetNames(ByVal ExcelFile As String) As List(Of String)
    Dim _Sheets As Sheets = Nothing
    Dim LstSheetName As List(Of String) = Nothing
    Try
        LstSheetName = New List(Of String)
        Using Document As SpreadsheetDocument = SpreadsheetDocument.Open(ExcelFile, False)
            Dim _WorkbookPart As WorkbookPart = Document.WorkbookPart
            _Sheets = _WorkbookPart.Workbook.Sheets
        End Using

        For Each Item As Sheet In _Sheets
            LstSheetName.Add(Item.Name)
        Next
        Return LstSheetName
    Catch ex As Exception
        Throw ex
    End Try
End Function

Upvotes: 0

D Stanley
D Stanley

Reputation: 152624

You can query the schema first using GetOleDbSchemaTable:

DataTable schemaTable = connection.GetOleDbSchemaTable(
    OleDbSchemaGuid.Tables,
    new object[] { null, null, null, "TABLE" });

The name of the first table should be at schemaTable.Rows[0][0].

Upvotes: 2

Related Questions