在Excel中使用Oledb获取特定表格的NamedRanges

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

使用Oledb，是否可以在Excel中获取一个特定表的所有NamedRanges ?

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.

我写了下面的代码，它给了我NamedRanges，但是我不知道NamedRange指的是哪个表。

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();
        }
    }
}

1 个解决方案

#1

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.

我是一个开放的XML SDK爱好者。解决方案是简单。这将返回工作簿和表作用域的命名范围，在左边是Excel名称管理器定义，在每个表中有两个命名范围，在右边是一个示例运行。

MSDN reference.

MSDN参考。

    /// <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;
    }