locked
fill excel sheet name into the combobox except hidded sheets RRS feed

  • Question

  • hi ;

    I want to fill excel sheet name into the combobox except hidded sheets

    I already fill to combobox below code , I just add the hidden sheet filters.

     DataTable dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

    foreach (DataRow drSheet in dtExcelSchema.Rows)
    {
    if (drSheet["TABLE_NAME"].ToString().Contains("$"))
    {
    comboSheet.Items.Add(drSheet["TABLE_NAME"].ToString());
    }

    Wednesday, October 10, 2018 6:54 PM

All replies

  • Working with OleDb you can't get names of hidden WorkSheet objects as OleDb knows nothing about hidden sheets. No different than OleDb can't get sheet names in ordinal position, only sorted A-Z.

    You would need to look at Excel automation, OpenXml or a third party library to get hidden sheets or not get hidden sheets.


    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

    Wednesday, October 10, 2018 10:35 PM
  • Hi Aly14,

    You can use free spire.xls dll available on NuGet to get the names of the visible sheets.

    using Spire.Xls;
    
    namespace GetNameOfVisibleSheets
    {
        class Program
        {
            static void Main(string[] args)
            {
                Workbook workbook = new Workbook();
    
                workbook.LoadFromFile("input.xlsx");
    
                foreach(Worksheet sheet in workbook.Worksheets)
                {
                    if (sheet.Visibility == WorksheetVisibility.Visible)
                    {
                        string sheetName = sheet.Name; 
                        //......
                    }
                }
    
            }
        }
    }

    Friday, October 12, 2018 7:06 AM