none
Get Excel Sheet names only (Skip Named ranges) RRS feed

  • Question

  • Below is the code which gives me list of all sheets in Excel book. I need only sheets not Named Ranges. How to tweak the code to get that?

     public void Main()
            {
                string excelFile;
                string connectionString;
                OleDbConnection excelConnection;
                DataTable tablesInFile;
                int tableCount = 0;
                string currentTable;
                int tableIndex = 0;
                string[] excelTables = new string[5];
                excelFile = Dts.Variables["ExcelFile"].Value.ToString();
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                    "Data Source=" + excelFile + ";Extended Properties=Excel 8.0";
                excelConnection = new OleDbConnection(connectionString);
                excelConnection.Open();
                tablesInFile = excelConnection.GetSchema("Tables");
                tableCount = tablesInFile.Rows.Count;
                foreach (DataRow tableInFile in tablesInFile.Rows)
                {
                    currentTable = tableInFile["TABLE_NAME"].ToString();
                    excelTables[tableIndex] = currentTable;
                    tableIndex += 1;
                }
                Dts.Variables["ExcelTables"].Value = excelTables;
                Dts.TaskResult = (int)ScriptResults.Success;
            }


    -Vaibhav Chaudhari

    • Moved by CoolDadTx Monday, February 16, 2015 5:57 PM Office related
    Monday, February 16, 2015 11:51 AM

Answers

  • Hello,

    Using OleDb is limiting as it can not see all objects in an Excel file. See if the following is helpful or not.

    Create a class project, add a reference to Microsoft.Office.Interop.Excel.

    ExcelInfo.cs

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Runtime.InteropServices;
    using Excel = Microsoft.Office.Interop.Excel;
    
    namespace ExcelHelper
    {
        public class ExcelInfo
        {
            public Exception LastException { get; set; }
    
            private List<ExcelReferenceTable> mReferenceTables;
    
            /// <summary>
            /// List of reference tables
            /// </summary>
            /// <value></value>
            /// <returns></returns>
            /// <remarks></remarks>
            public List<ExcelReferenceTable> ReferenceTables
            {
                get
                {
                    return mReferenceTables;
                }
            }
    
            private string[] Extensions = { ".xls", ".xlsx" };
            private string mFileName;
    
            /// <summary>
            /// Valid/existing Excel file name to work with.
            /// </summary>
            /// <value></value>
            /// <returns></returns>
            /// <remarks></remarks>
            public string FileName
            {
                get
                {
                    return mFileName;
                }
                set
                {
                    if (!(Extensions.Contains(System.IO.Path.GetExtension(value.ToLower()))))
                    {
                        throw new Exception("Invalid file name");
                    }
                    mFileName = value;
                }
            }
    
            private List<string> mNameRanges = new List<string>();
    
            /// <summary>
            /// List of named ranges in current file
            /// </summary>
            /// <value></value>
            /// <returns></returns>
            /// <remarks></remarks>
            public List<string> NameRanges
            {
                get
                {
                    return mNameRanges;
                }
            }
    
            private List<string> mSheets = new List<string>();
    
            /// <summary>
            /// List of work sheets in current file
            /// </summary>
            /// <value></value>
            /// <returns></returns>
            /// <remarks></remarks>
            public List<string> Sheets
            {
                get
                {
                    return mSheets;
                }
            }
    
            private Dictionary<Int32, string> mSheetsData = new Dictionary<Int32, string>();
    
            public Dictionary<Int32, string> SheetsData
            {
                get
                {
                    return mSheetsData;
                }
            }
    
            public ExcelInfo()
            {
            }
    
            /// <summary>
            /// File to get information from
            /// </summary>
            /// <param name="FileName"></param>
            /// <remarks>
            /// The caller is responsible to ensure the file exists.
            /// </remarks>
            public ExcelInfo(string FileName)
            {
                this.FileName = FileName;
            }
    
            /// <summary>
            /// Retrieve worksheet and name range names.
            /// </summary>
            /// <returns></returns>
            /// <remarks></remarks>
            public bool GetInformation()
            {
                bool Success = true;
    
                if (!(System.IO.File.Exists(FileName)))
                {
                    Exception ex = new Exception("Failed to locate '" + FileName + "'");
                    this.LastException = ex;
                    throw ex;
                }
    
                mSheets.Clear();
                mNameRanges.Clear();
                mSheetsData.Clear();
    
                if (mReferenceTables != null)
                {
                    mReferenceTables.Clear();
                }
    
                Excel.Application xlApp = null;
                Excel.Workbooks xlWorkBooks = null;
                Excel.Workbook xlWorkBook = null;
                Excel.Workbook xlActiveRanges = null;
                Excel.Names xlNames = null;
                Excel.Sheets xlWorkSheets = null;
    
                try
                {
                    xlApp = new Excel.Application();
                    xlApp.DisplayAlerts = false;
                    xlWorkBooks = xlApp.Workbooks;
                    xlWorkBook = xlWorkBooks.Open(FileName);
    
                    xlActiveRanges = xlApp.ActiveWorkbook;
                    xlNames = xlActiveRanges.Names;
    
                    for (int x = 1; x <= xlNames.Count; x++)
                    {
                        Excel.Name xlName = xlNames.Item(x);
                        mNameRanges.Add(xlName.Name);
                        Marshal.FinalReleaseComObject(xlName);
                        xlName = null;
                    }
    
                    xlWorkSheets = xlWorkBook.Sheets;
    
                    for (int x = 1; x <= xlWorkSheets.Count; x++)
                    {
                        Excel.Worksheet Sheet1 = (Excel.Worksheet)xlWorkSheets[x];
                        mSheets.Add(Sheet1.Name);
                        mSheetsData.Add(x, Sheet1.Name);
                        Marshal.FinalReleaseComObject(Sheet1);
                        Sheet1 = null;
                    }
    
                    GetReferenceTables(xlWorkSheets);
                    ReleaseComObject(xlWorkSheets);
                    xlWorkBook.Close();
    
                    xlApp.UserControl = true;
                    xlApp.Quit();
                }
                catch (Exception ex)
                {
                    this.LastException = ex;
                    Success = false;
                }
                finally
                {
                    if (xlWorkSheets != null)
                    {
                        Marshal.FinalReleaseComObject(xlWorkSheets);
                        xlWorkSheets = null;
                    }
    
                    if (xlNames != null)
                    {
                        Marshal.FinalReleaseComObject(xlNames);
                        xlNames = null;
                    }
    
                    if (xlActiveRanges != null)
                    {
                        Marshal.FinalReleaseComObject(xlActiveRanges);
                        xlActiveRanges = null;
                    }
                    if (xlActiveRanges != null)
                    {
                        Marshal.FinalReleaseComObject(xlActiveRanges);
                        xlActiveRanges = null;
                    }
    
                    if (xlWorkBook != null)
                    {
                        Marshal.FinalReleaseComObject(xlWorkBook);
                        xlWorkBook = null;
                    }
    
                    if (xlWorkBooks != null)
                    {
                        Marshal.FinalReleaseComObject(xlWorkBooks);
                        xlWorkBooks = null;
                    }
    
                    if (xlApp != null)
                    {
                        Marshal.FinalReleaseComObject(xlApp);
                        xlApp = null;
                    }
                }
    
                return Success;
            }
    
            private List<ExcelReferenceTable> GetReferenceTables(Excel.Sheets xlWorkSheets)
            {
                List<ExcelReferenceTable> Result = new List<ExcelReferenceTable>();
                string Temp = "";
                Excel.Worksheet xlWorkSheet = null;
                Excel.ListObjects xlListObjects = null;
                Excel.ListObject ThisItem = null;
    
                for (int x = 1; x <= xlWorkSheets.Count; x++)
                {
                    ExcelReferenceTable Item = new ExcelReferenceTable();
    
                    xlWorkSheet = (Excel.Worksheet)xlWorkSheets[x];
                    xlListObjects = xlWorkSheet.ListObjects;
    
                    Int32 TotalCount = xlListObjects.Count - 1;
                    for (int y = 0; y <= TotalCount; y++)
                    {
                        ThisItem = xlListObjects.Item[y + 1];
                        Item.Name = ThisItem.Name;
                        Item.SheetName = xlWorkSheet.Name;
    
                        // TODO: Need to tinker with this.
                        try
                        {
                            Excel.QueryTable QT = ThisItem.QueryTable;
                            Item.SourceDataFile = QT.SourceDataFile;
                            ReleaseComObject(QT);
                        }
                        catch (Exception)
                        {
                            Item.SourceDataFile = "";
                        }
    
                        Excel.Range ThisRange = ThisItem.Range;
                        Temp = ThisRange.Address;
    
                        Item.Address = Temp.Replace("$", "");
    
                        Result.Add(Item);
    
                        Marshal.FinalReleaseComObject(ThisRange);
                        ThisRange = null;
    
                        Marshal.FinalReleaseComObject(ThisItem);
                        ThisItem = null;
    
                        Marshal.FinalReleaseComObject(xlListObjects);
                        xlListObjects = null;
                    }
                }
    
                ReleaseComObject(xlWorkSheet);
    
                mReferenceTables = Result;
    
                return Result;
            }
    
            /// <summary>
            ///
            /// </summary>
            /// <param name="obj"></param>
            /// <remarks>
            /// Generally speaking we should not have to call
            /// GC.Collect() but about one percent of the time
            /// Excel will refuse to release an object dependency
            /// thus no choice but to call GC.Collect(). Please
            /// make every effort to use ReleaseComObjectClean
            /// rather than this procedure unless a object refuses
            /// to release.
            /// </remarks>
            private void ReleaseComObject(object obj)
            {
                try
                {
                    Marshal.ReleaseComObject(obj);
                    obj = null;
                }
                catch (Exception)
                {
                    obj = null;
                }
                finally
                {
                    GC.Collect();
                }
            }
    
            public void ReleaseComObjectClean(object obj)
            {
                try
                {
                    Marshal.ReleaseComObject(obj);
                    obj = null;
                }
                catch (Exception)
                {
                    obj = null;
                }
            }
        }
    }

    ExcelReferenceTable.cs (really not needed but included it as it might be of use in the future)

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace ExcelHelper
    {
        public class ExcelReferenceTable
        {
            public string Name { get; set; }
            public string SheetName { get; set; }
            public string Address { get; set; }
            public string SelectString
            {
                get
                {
                    return "SELECT * FROM [" + SheetName + "$" + Address + "]";
                }
            }
            public string SourceDataFile { get; set; }
            [System.Diagnostics.DebuggerStepThrough()]
            public ExcelReferenceTable()
            {
            }
            public override string ToString()
            {
                return Name;
            }
        }
    }
    

    Test Console project

    using ExcelHelper;
    using System;
    
    namespace ExcelHelperTest
    {
        internal class Program
        {
            private static void Main(string[] args)
            {
                ExcelInfo Helper = new ExcelInfo();
                Helper.FileName = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "File1.xlsx");
                Console.WriteLine(Helper.FileName);
                if (Helper.GetInformation())
                {
                    var SheetNames = Helper.Sheets;
                    Console.WriteLine("Sheet names");
                    foreach (var Sheet in SheetNames)
                    {
                        Console.WriteLine(Sheet);
                    }
                    Console.WriteLine();
                    var ReferenceTables = Helper.ReferenceTables;
                    if (ReferenceTables !=null)
                    {
                        Console.WriteLine("Reference tables");
                        foreach (var item in ReferenceTables)
                        {
                            Console.WriteLine(item);
                        }                    
                    }
                    else
                	{
                        Console.WriteLine("No reference tables found");
    	            }
    
                }
                Console.ReadLine();
            }
        }
    }


    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.

    • Marked as answer by L.HlModerator Wednesday, February 25, 2015 2:54 AM
    Monday, February 16, 2015 12:24 PM
  • private String[] GetExcelSheetNames(string excelFile)
    {
      OleDbConnection objConn = null;
      System.Data.DataTable dt = null;
    
      try
      {
        // Connection String. Change the excel file to the file you
        // will search.
        String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + 
            "Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";
        // Create connection object by using the preceding connection string.
        objConn = new OleDbConnection(connString);
        // Open connection with the database.
        objConn.Open();
        // Get the data table containg the schema guid.
        dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
     
        if(dt == null)
        {
          return null;
        }
    
        String[] excelSheets = new String[dt.Rows.Count];
        int i = 0;
    
        // Add the sheet name to the string array.
        foreach(DataRow row in dt.Rows)
        {
          excelSheets[i] = row["TABLE_NAME"].ToString();
          i++;
        }
    
        // Loop through all of the sheets if you want too...
        for(int j=0; j < excelSheets.Length; j++)
        {
          // Query each excel sheet.
        }
    
        return excelSheets;
      }
      catch(Exception ex)
      {
        return null;
      }
      finally
      {
        // Clean up.
        if(objConn != null)
        {
          objConn.Close();
          objConn.Dispose();
        }
        if(dt != null)
        {
          dt.Dispose();
        }
      }
    }


    Mark as answer or vote as helpful if you find it useful | Ammar Zaied [MCP]

    • Marked as answer by L.HlModerator Wednesday, February 25, 2015 2:54 AM
    Monday, February 16, 2015 4:10 PM

All replies

  • Hello,

    Using OleDb is limiting as it can not see all objects in an Excel file. See if the following is helpful or not.

    Create a class project, add a reference to Microsoft.Office.Interop.Excel.

    ExcelInfo.cs

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Runtime.InteropServices;
    using Excel = Microsoft.Office.Interop.Excel;
    
    namespace ExcelHelper
    {
        public class ExcelInfo
        {
            public Exception LastException { get; set; }
    
            private List<ExcelReferenceTable> mReferenceTables;
    
            /// <summary>
            /// List of reference tables
            /// </summary>
            /// <value></value>
            /// <returns></returns>
            /// <remarks></remarks>
            public List<ExcelReferenceTable> ReferenceTables
            {
                get
                {
                    return mReferenceTables;
                }
            }
    
            private string[] Extensions = { ".xls", ".xlsx" };
            private string mFileName;
    
            /// <summary>
            /// Valid/existing Excel file name to work with.
            /// </summary>
            /// <value></value>
            /// <returns></returns>
            /// <remarks></remarks>
            public string FileName
            {
                get
                {
                    return mFileName;
                }
                set
                {
                    if (!(Extensions.Contains(System.IO.Path.GetExtension(value.ToLower()))))
                    {
                        throw new Exception("Invalid file name");
                    }
                    mFileName = value;
                }
            }
    
            private List<string> mNameRanges = new List<string>();
    
            /// <summary>
            /// List of named ranges in current file
            /// </summary>
            /// <value></value>
            /// <returns></returns>
            /// <remarks></remarks>
            public List<string> NameRanges
            {
                get
                {
                    return mNameRanges;
                }
            }
    
            private List<string> mSheets = new List<string>();
    
            /// <summary>
            /// List of work sheets in current file
            /// </summary>
            /// <value></value>
            /// <returns></returns>
            /// <remarks></remarks>
            public List<string> Sheets
            {
                get
                {
                    return mSheets;
                }
            }
    
            private Dictionary<Int32, string> mSheetsData = new Dictionary<Int32, string>();
    
            public Dictionary<Int32, string> SheetsData
            {
                get
                {
                    return mSheetsData;
                }
            }
    
            public ExcelInfo()
            {
            }
    
            /// <summary>
            /// File to get information from
            /// </summary>
            /// <param name="FileName"></param>
            /// <remarks>
            /// The caller is responsible to ensure the file exists.
            /// </remarks>
            public ExcelInfo(string FileName)
            {
                this.FileName = FileName;
            }
    
            /// <summary>
            /// Retrieve worksheet and name range names.
            /// </summary>
            /// <returns></returns>
            /// <remarks></remarks>
            public bool GetInformation()
            {
                bool Success = true;
    
                if (!(System.IO.File.Exists(FileName)))
                {
                    Exception ex = new Exception("Failed to locate '" + FileName + "'");
                    this.LastException = ex;
                    throw ex;
                }
    
                mSheets.Clear();
                mNameRanges.Clear();
                mSheetsData.Clear();
    
                if (mReferenceTables != null)
                {
                    mReferenceTables.Clear();
                }
    
                Excel.Application xlApp = null;
                Excel.Workbooks xlWorkBooks = null;
                Excel.Workbook xlWorkBook = null;
                Excel.Workbook xlActiveRanges = null;
                Excel.Names xlNames = null;
                Excel.Sheets xlWorkSheets = null;
    
                try
                {
                    xlApp = new Excel.Application();
                    xlApp.DisplayAlerts = false;
                    xlWorkBooks = xlApp.Workbooks;
                    xlWorkBook = xlWorkBooks.Open(FileName);
    
                    xlActiveRanges = xlApp.ActiveWorkbook;
                    xlNames = xlActiveRanges.Names;
    
                    for (int x = 1; x <= xlNames.Count; x++)
                    {
                        Excel.Name xlName = xlNames.Item(x);
                        mNameRanges.Add(xlName.Name);
                        Marshal.FinalReleaseComObject(xlName);
                        xlName = null;
                    }
    
                    xlWorkSheets = xlWorkBook.Sheets;
    
                    for (int x = 1; x <= xlWorkSheets.Count; x++)
                    {
                        Excel.Worksheet Sheet1 = (Excel.Worksheet)xlWorkSheets[x];
                        mSheets.Add(Sheet1.Name);
                        mSheetsData.Add(x, Sheet1.Name);
                        Marshal.FinalReleaseComObject(Sheet1);
                        Sheet1 = null;
                    }
    
                    GetReferenceTables(xlWorkSheets);
                    ReleaseComObject(xlWorkSheets);
                    xlWorkBook.Close();
    
                    xlApp.UserControl = true;
                    xlApp.Quit();
                }
                catch (Exception ex)
                {
                    this.LastException = ex;
                    Success = false;
                }
                finally
                {
                    if (xlWorkSheets != null)
                    {
                        Marshal.FinalReleaseComObject(xlWorkSheets);
                        xlWorkSheets = null;
                    }
    
                    if (xlNames != null)
                    {
                        Marshal.FinalReleaseComObject(xlNames);
                        xlNames = null;
                    }
    
                    if (xlActiveRanges != null)
                    {
                        Marshal.FinalReleaseComObject(xlActiveRanges);
                        xlActiveRanges = null;
                    }
                    if (xlActiveRanges != null)
                    {
                        Marshal.FinalReleaseComObject(xlActiveRanges);
                        xlActiveRanges = null;
                    }
    
                    if (xlWorkBook != null)
                    {
                        Marshal.FinalReleaseComObject(xlWorkBook);
                        xlWorkBook = null;
                    }
    
                    if (xlWorkBooks != null)
                    {
                        Marshal.FinalReleaseComObject(xlWorkBooks);
                        xlWorkBooks = null;
                    }
    
                    if (xlApp != null)
                    {
                        Marshal.FinalReleaseComObject(xlApp);
                        xlApp = null;
                    }
                }
    
                return Success;
            }
    
            private List<ExcelReferenceTable> GetReferenceTables(Excel.Sheets xlWorkSheets)
            {
                List<ExcelReferenceTable> Result = new List<ExcelReferenceTable>();
                string Temp = "";
                Excel.Worksheet xlWorkSheet = null;
                Excel.ListObjects xlListObjects = null;
                Excel.ListObject ThisItem = null;
    
                for (int x = 1; x <= xlWorkSheets.Count; x++)
                {
                    ExcelReferenceTable Item = new ExcelReferenceTable();
    
                    xlWorkSheet = (Excel.Worksheet)xlWorkSheets[x];
                    xlListObjects = xlWorkSheet.ListObjects;
    
                    Int32 TotalCount = xlListObjects.Count - 1;
                    for (int y = 0; y <= TotalCount; y++)
                    {
                        ThisItem = xlListObjects.Item[y + 1];
                        Item.Name = ThisItem.Name;
                        Item.SheetName = xlWorkSheet.Name;
    
                        // TODO: Need to tinker with this.
                        try
                        {
                            Excel.QueryTable QT = ThisItem.QueryTable;
                            Item.SourceDataFile = QT.SourceDataFile;
                            ReleaseComObject(QT);
                        }
                        catch (Exception)
                        {
                            Item.SourceDataFile = "";
                        }
    
                        Excel.Range ThisRange = ThisItem.Range;
                        Temp = ThisRange.Address;
    
                        Item.Address = Temp.Replace("$", "");
    
                        Result.Add(Item);
    
                        Marshal.FinalReleaseComObject(ThisRange);
                        ThisRange = null;
    
                        Marshal.FinalReleaseComObject(ThisItem);
                        ThisItem = null;
    
                        Marshal.FinalReleaseComObject(xlListObjects);
                        xlListObjects = null;
                    }
                }
    
                ReleaseComObject(xlWorkSheet);
    
                mReferenceTables = Result;
    
                return Result;
            }
    
            /// <summary>
            ///
            /// </summary>
            /// <param name="obj"></param>
            /// <remarks>
            /// Generally speaking we should not have to call
            /// GC.Collect() but about one percent of the time
            /// Excel will refuse to release an object dependency
            /// thus no choice but to call GC.Collect(). Please
            /// make every effort to use ReleaseComObjectClean
            /// rather than this procedure unless a object refuses
            /// to release.
            /// </remarks>
            private void ReleaseComObject(object obj)
            {
                try
                {
                    Marshal.ReleaseComObject(obj);
                    obj = null;
                }
                catch (Exception)
                {
                    obj = null;
                }
                finally
                {
                    GC.Collect();
                }
            }
    
            public void ReleaseComObjectClean(object obj)
            {
                try
                {
                    Marshal.ReleaseComObject(obj);
                    obj = null;
                }
                catch (Exception)
                {
                    obj = null;
                }
            }
        }
    }

    ExcelReferenceTable.cs (really not needed but included it as it might be of use in the future)

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace ExcelHelper
    {
        public class ExcelReferenceTable
        {
            public string Name { get; set; }
            public string SheetName { get; set; }
            public string Address { get; set; }
            public string SelectString
            {
                get
                {
                    return "SELECT * FROM [" + SheetName + "$" + Address + "]";
                }
            }
            public string SourceDataFile { get; set; }
            [System.Diagnostics.DebuggerStepThrough()]
            public ExcelReferenceTable()
            {
            }
            public override string ToString()
            {
                return Name;
            }
        }
    }
    

    Test Console project

    using ExcelHelper;
    using System;
    
    namespace ExcelHelperTest
    {
        internal class Program
        {
            private static void Main(string[] args)
            {
                ExcelInfo Helper = new ExcelInfo();
                Helper.FileName = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "File1.xlsx");
                Console.WriteLine(Helper.FileName);
                if (Helper.GetInformation())
                {
                    var SheetNames = Helper.Sheets;
                    Console.WriteLine("Sheet names");
                    foreach (var Sheet in SheetNames)
                    {
                        Console.WriteLine(Sheet);
                    }
                    Console.WriteLine();
                    var ReferenceTables = Helper.ReferenceTables;
                    if (ReferenceTables !=null)
                    {
                        Console.WriteLine("Reference tables");
                        foreach (var item in ReferenceTables)
                        {
                            Console.WriteLine(item);
                        }                    
                    }
                    else
                	{
                        Console.WriteLine("No reference tables found");
    	            }
    
                }
                Console.ReadLine();
            }
        }
    }


    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.

    • Marked as answer by L.HlModerator Wednesday, February 25, 2015 2:54 AM
    Monday, February 16, 2015 12:24 PM
  • private String[] GetExcelSheetNames(string excelFile)
    {
      OleDbConnection objConn = null;
      System.Data.DataTable dt = null;
    
      try
      {
        // Connection String. Change the excel file to the file you
        // will search.
        String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + 
            "Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";
        // Create connection object by using the preceding connection string.
        objConn = new OleDbConnection(connString);
        // Open connection with the database.
        objConn.Open();
        // Get the data table containg the schema guid.
        dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
     
        if(dt == null)
        {
          return null;
        }
    
        String[] excelSheets = new String[dt.Rows.Count];
        int i = 0;
    
        // Add the sheet name to the string array.
        foreach(DataRow row in dt.Rows)
        {
          excelSheets[i] = row["TABLE_NAME"].ToString();
          i++;
        }
    
        // Loop through all of the sheets if you want too...
        for(int j=0; j < excelSheets.Length; j++)
        {
          // Query each excel sheet.
        }
    
        return excelSheets;
      }
      catch(Exception ex)
      {
        return null;
      }
      finally
      {
        // Clean up.
        if(objConn != null)
        {
          objConn.Close();
          objConn.Dispose();
        }
        if(dt != null)
        {
          dt.Dispose();
        }
      }
    }


    Mark as answer or vote as helpful if you find it useful | Ammar Zaied [MCP]

    • Marked as answer by L.HlModerator Wednesday, February 25, 2015 2:54 AM
    Monday, February 16, 2015 4:10 PM