none
How do we get worksheet index based on Worksheet name RRS feed

  • Question

  • I am getting trouble to read sheet name in the workbook.

    Atleast if i get the index, then and there i could able to read the sheet based on worksheet name.

    Let us say Example...

    I am having multiple sheet like (A,B,C,D,E,F,G,H,etc,).

    Suppose if i give "D", then it should return "3" (4th index)  (0 to 3)..

    how to get this index in c#.net or linq C#

    Monday, December 31, 2018 9:44 AM

Answers

  • Thanks for the detailed code..

    Is it possible to get only sheet index..

    For example, if i give sheetname is "people",

    it must return index is "3". 

    simple one line code which suits for .net..?

    Public Function SheetIndex(fileName As String, sheetName As String) As Integer
        Dim xlApp As Excel.Application = Nothing
        Dim xlWorkBooks As Excel.Workbooks = Nothing
        Dim xlWorkBook As Excel.Workbook = Nothing
        Dim xlWorkSheet As Excel.Worksheet = Nothing
        Dim xlWorkSheets As Excel.Sheets = Nothing
        Dim xlCells As Excel.Range = Nothing
    
        xlApp = New Excel.Application
        xlApp.DisplayAlerts = False
        xlWorkBooks = xlApp.Workbooks
        xlWorkBook = xlWorkBooks.Open(fileName)
    
        xlApp.Visible = False
        xlWorkSheets = xlWorkBook.Sheets
    
        For index As Integer = 1 To xlWorkSheets.Count
            xlWorkSheet = CType(xlWorkSheets(index), Excel.Worksheet)
    
            If xlWorkSheet.Name = sheetName Then
                Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing
    
                Return index -1
            End If
            Marshal.FinalReleaseComObject(xlWorkSheet)
            xlWorkSheet = Nothing
    
        Next
    
        xlWorkBook.Close()
        xlApp.UserControl = True
        xlApp.Quit()
    
        ReleaseExcelObject(xlCells)
        ReleaseExcelObject(xlWorkSheets)
        ReleaseExcelObject(xlWorkSheet)
        ReleaseExcelObject(xlWorkBook)
        ReleaseExcelObject(xlWorkBooks)
        ReleaseExcelObject(xlApp)
        Return -1
    End Function

    EDIT changed return index to return index -1

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites



    Monday, December 31, 2018 12:16 PM
    Moderator

All replies

  • Hello,

    What method is being used to work with Excel? If using OleDb the method to get sheet names will end up not working as the schema method does a A-Z order while automation method does it in ordinal position.

    I took an existing example and tweak it for your question.

    Imports System.Runtime.InteropServices
    Imports Excel = Microsoft.Office.Interop.Excel
    
    
    Public Class ExcelOperations
        Public Function OpenExcelSimple(fileName As String) As Dictionary(Of Integer, String)
            Dim dict As New Dictionary(Of Integer, String)
    
            Dim xlApp As Excel.Application = Nothing
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
            Dim xlWorkSheet As Excel.Worksheet = Nothing
            Dim xlWorkSheets As Excel.Sheets = Nothing
            Dim xlCells As Excel.Range = Nothing
    
            xlApp = New Excel.Application
            xlApp.DisplayAlerts = False
            xlWorkBooks = xlApp.Workbooks
            xlWorkBook = xlWorkBooks.Open(fileName)
    
            xlApp.Visible = False
            xlWorkSheets = xlWorkBook.Sheets
    
            For sheetIndex As Integer = 1 To xlWorkSheets.Count
                xlWorkSheet = CType(xlWorkSheets(sheetIndex), Excel.Worksheet)
                dict.Add(sheetIndex - 1, xlWorkSheet.Name)
                Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing
            Next
    
    
            xlWorkBook.Close()
            xlApp.UserControl = True
            xlApp.Quit()
    
            ReleaseExcelObject(xlCells)
            ReleaseExcelObject(xlWorkSheets)
            ReleaseExcelObject(xlWorkSheet)
            ReleaseExcelObject(xlWorkBook)
            ReleaseExcelObject(xlWorkBooks)
            ReleaseExcelObject(xlApp)
    
            Return dict
    
        End Function
    
        Private Sub ReleaseExcelObject(ByVal excelObject As Object)
            Try
                If excelObject IsNot Nothing Then
                    Marshal.ReleaseComObject(excelObject)
                    excelObject = Nothing
                End If
            Catch ex As Exception
                excelObject = Nothing
            End Try
        End Sub
    End Class

    Form code

    Imports System.IO
    
    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim ops As New ExcelOperations
    
            Dim results = ops.OpenExcelSimple(
                Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Demo.xlsx"))
    
            For Each kvp As KeyValuePair(Of Integer, String) In results
                ListBox1.Items.Add($"{kvp.Key} {kvp.Value}")
            Next
        End Sub
    End Class

    Using a third party (free/open source) library named SpreadSheetLight. Installed via NuGet at the following NuGet page.

    Code

    Dim dict As New Dictionary(Of Integer, String)
    Using doc As New SLDocument(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Demo1.xlsx"))
        Dim sheetNames = doc.GetSheetNames()
        For index As Integer = 0 To sheetNames.Count - 1
            dict.Add(index, sheetNames(index))
        Next
    End Using


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites



    Monday, December 31, 2018 10:23 AM
    Moderator
  • Thanks..

    below is my example.

     string Worksheetname = "People";
     excelApp = new Excel.Application();
     Excel.Worksheet  XLWorkSheet;
     XLWorkBook = excelApp.Workbooks.Open("C:\Test\Test.xlsx");
     XLWorkSheet = XLWorkBook.Sheets[Worksheetname]; ==> getting Error if i used worksheet as "name"

    But, if i used as integer, may be it should work. 

    XLWorkSheet = XLWorkBook.Sheets[3]; ==> because "people" is 3rd sheet of workbook.

    for that i want to find the index of the particular worksheet.

    ???

    Monday, December 31, 2018 11:13 AM
  • Here is an example, in this case only People has data and only in A1, B2, B3 and B4.

    Imports System.Runtime.InteropServices
    Imports Excel = Microsoft.Office.Interop.Excel
    
    
    Public Class ExcelOperations
        Public Sub OpenExcelRead(fileName As String, sheetName As String)
            Dim proceed As Boolean = False
    
            Dim xlApp As Excel.Application = Nothing
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
            Dim xlWorkSheet As Excel.Worksheet = Nothing
            Dim xlWorkSheets As Excel.Sheets = Nothing
            Dim xlCells As Excel.Range = Nothing
    
            xlApp = New Excel.Application
            xlApp.DisplayAlerts = False
            xlWorkBooks = xlApp.Workbooks
            xlWorkBook = xlWorkBooks.Open(fileName)
    
            xlApp.Visible = False
            xlWorkSheets = xlWorkBook.Sheets
    
            For x As Integer = 1 To xlWorkSheets.Count
                xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)
    
                If xlWorkSheet.Name = sheetName Then
                    proceed = True
                    Exit For
                End If
    
                Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing
    
            Next
            If proceed Then
                Dim sb As New Text.StringBuilder
    
                Dim cells As String() = {"A1", "B2", "B3", "B4"}
                For Each cell As String In cells
                    Try
                        xlCells = xlWorkSheet.Range(cell)
                        sb.AppendLine($"{cell} = '{xlCells.Value}'")
                    Catch ex As Exception
                        ReleaseExcelObject(xlCells)
                    End Try
                Next
                Console.WriteLine(sb.ToString())
            End If
    
            xlWorkBook.Close()
            xlApp.UserControl = True
            xlApp.Quit()
    
            ReleaseExcelObject(xlCells)
            ReleaseExcelObject(xlWorkSheets)
            ReleaseExcelObject(xlWorkSheet)
            ReleaseExcelObject(xlWorkBook)
            ReleaseExcelObject(xlWorkBooks)
            ReleaseExcelObject(xlApp)
        End Sub
    
        Public Function OpenExcelSimple(fileName As String) As Dictionary(Of Integer, String)
            Dim dict As New Dictionary(Of Integer, String)
    
            Dim xlApp As Excel.Application = Nothing
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
            Dim xlWorkSheet As Excel.Worksheet = Nothing
            Dim xlWorkSheets As Excel.Sheets = Nothing
            Dim xlCells As Excel.Range = Nothing
    
            xlApp = New Excel.Application
            xlApp.DisplayAlerts = False
            xlWorkBooks = xlApp.Workbooks
            xlWorkBook = xlWorkBooks.Open(fileName)
    
            xlApp.Visible = False
            xlWorkSheets = xlWorkBook.Sheets
    
            For sheetIndex As Integer = 1 To xlWorkSheets.Count
                xlWorkSheet = CType(xlWorkSheets(sheetIndex), Excel.Worksheet)
                dict.Add(sheetIndex - 1, xlWorkSheet.Name)
                Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing
            Next
    
    
            xlWorkBook.Close()
            xlApp.UserControl = True
            xlApp.Quit()
    
            ReleaseExcelObject(xlCells)
            ReleaseExcelObject(xlWorkSheets)
            ReleaseExcelObject(xlWorkSheet)
            ReleaseExcelObject(xlWorkBook)
            ReleaseExcelObject(xlWorkBooks)
            ReleaseExcelObject(xlApp)
    
            Return dict
    
        End Function
    
        Private Sub ReleaseExcelObject(ByVal excelObject As Object)
            Try
                If excelObject IsNot Nothing Then
                    Marshal.ReleaseComObject(excelObject)
                    excelObject = Nothing
                End If
            Catch ex As Exception
                excelObject = Nothing
            End Try
        End Sub
    End Class
    

    Form code

    Imports System.IO
    
    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim ops As New ExcelOperations
    
            Dim results = ops.OpenExcelSimple(
                Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Demo.xlsx"))
    
            For Each kvp As KeyValuePair(Of Integer, String) In results
                'ListBox1.Items.Add($"{kvp.Key} {kvp.Value}")
                ListBox1.Items.Add(kvp.Value)
            Next
        End Sub
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            If ListBox1.Items.Count > 0 Then
                Dim ops As New ExcelOperations
                ops.OpenExcelRead(Path.Combine(
                    AppDomain.CurrentDomain.BaseDirectory, "Demo.xlsx"), ListBox1.Text)
            End If
        End Sub
    End Class
    

    Full source code

    https://1drv.ms/u/s!AtGAgKKpqdWjjHYuHed6uxrTvFg_


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, December 31, 2018 11:59 AM
    Moderator
  • Thanks..

    below is my example.

     string Worksheetname = "People";
     excelApp = new Excel.Application();
     Excel.Worksheet  XLWorkSheet;
     XLWorkBook = excelApp.Workbooks.Open("C:\Test\Test.xlsx");
     XLWorkSheet = XLWorkBook.Sheets[Worksheetname]; ==> getting Error if i used worksheet as "name"

    But, if i used as integer, may be it should work. 

    XLWorkSheet = XLWorkBook.Sheets[3]; ==> because "people" is 3rd sheet of workbook.

    for that i want to find the index of the particular worksheet.

    ???

    Note on your code

    C# (we are in a VB.NET forum), use () not [] for indexer.

    XLWorkSheet = XLWorkBook.Sheets[Worksheetname];


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, December 31, 2018 12:02 PM
    Moderator
  • Thanks for the detailed code..

    Is it possible to get only sheet index..

    For example, if i give sheetname is "people",

    it must return index is "3". 

    simple one line code which suits for .net..?

    Monday, December 31, 2018 12:07 PM
  • Thanks for the detailed code..

    Is it possible to get only sheet index..

    For example, if i give sheetname is "people",

    it must return index is "3". 

    simple one line code which suits for .net..?

    Public Function SheetIndex(fileName As String, sheetName As String) As Integer
        Dim xlApp As Excel.Application = Nothing
        Dim xlWorkBooks As Excel.Workbooks = Nothing
        Dim xlWorkBook As Excel.Workbook = Nothing
        Dim xlWorkSheet As Excel.Worksheet = Nothing
        Dim xlWorkSheets As Excel.Sheets = Nothing
        Dim xlCells As Excel.Range = Nothing
    
        xlApp = New Excel.Application
        xlApp.DisplayAlerts = False
        xlWorkBooks = xlApp.Workbooks
        xlWorkBook = xlWorkBooks.Open(fileName)
    
        xlApp.Visible = False
        xlWorkSheets = xlWorkBook.Sheets
    
        For index As Integer = 1 To xlWorkSheets.Count
            xlWorkSheet = CType(xlWorkSheets(index), Excel.Worksheet)
    
            If xlWorkSheet.Name = sheetName Then
                Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing
    
                Return index -1
            End If
            Marshal.FinalReleaseComObject(xlWorkSheet)
            xlWorkSheet = Nothing
    
        Next
    
        xlWorkBook.Close()
        xlApp.UserControl = True
        xlApp.Quit()
    
        ReleaseExcelObject(xlCells)
        ReleaseExcelObject(xlWorkSheets)
        ReleaseExcelObject(xlWorkSheet)
        ReleaseExcelObject(xlWorkBook)
        ReleaseExcelObject(xlWorkBooks)
        ReleaseExcelObject(xlApp)
        Return -1
    End Function

    EDIT changed return index to return index -1

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites



    Monday, December 31, 2018 12:16 PM
    Moderator
  • Excellent...working...
    Monday, December 31, 2018 12:58 PM