CODEWITHSUNDEEP
c#exceloledb
TechGeek
TechGeek

Reputation: 2212

C# - Get NamedRanges of a particular sheet in Excel using Oledb

Using Oledb, is it possible to get all NamedRanges of a particula sheet in Excel?

I have written following code which gives me NamedRanges but I am not able to figure out to which sheet does the NamedRange refer to.

private String[] GetExcelSheetNames(string excelFilePath)
{
    OleDbConnection objConn = null;
    System.Data.DataTable dt = null;

    try
    {
        //String connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";Extended Properties=Excel 12.0;";

        string connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0", excelFilePath);
        objConn = new OleDbConnection(connectionString);
        objConn.Open();

        // Get the data table containg the schema guid.
        dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables_Info, 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();

        return excelSheets;
    }
    catch (Exception ex)
    {
        return null;
    }
    finally
    {
        // Clean up.
        if (objConn != null)
        {
            objConn.Close();
            objConn.Dispose();
        }
        if (dt != null)
        {
            dt.Dispose();
        }
    }
}

Upvotes: 1

Views: 949

Answers (1)

andrei.ciprian
andrei.ciprian

Reputation: 3025

I am a Open XML SDK fan. The solution is straightforward. This returns both workbook and sheet scoped named ranges, on the left there's the Excel name manager definitions, 2 sheets with 2 named ranges in each sheet, on the right a sample run.

MSDN reference.

enter image description here

    /// <summary>
    /// The procedure examines the workbook that you specify, 
    /// looking for the part that contains defined names. 
    /// If it exists, the procedure iterates through all the 
    /// contents of the part, adding the name and value for 
    /// each defined name to the returned dictionary
    /// </summary>
    public static IDictionary<String, String> XLGetDefinedNames(String fileName)
    {
      var returnValue = new Dictionary<String, String>();
      //
      using (SpreadsheetDocument document = 
          SpreadsheetDocument.Open(fileName, false))
      {
        var wbPart = document.WorkbookPart;
        //
        DefinedNames definedNames = wbPart.Workbook.DefinedNames;
        if (definedNames != null)
        {
          foreach (DefinedName dn in definedNames)
            returnValue.Add(dn.Name.Value, dn.Text);
        }
      }
      //
      return returnValue;
    }

Upvotes: 1

Related Questions