C ++ Excel Automation - 检查是否定义了命名范围 [英] C++ Excel Automation - Checking if a named range is defined

问题描述

我使用包装类使用.NET 2012 MFC创建了Excel 2007自动化应用程序。由于从CodeProject和MSDN等文章中收集到的信息，事情通常已经好了。但是目前我遇到了一个问题，我到目前为止无法解决，我想确认一个工作簿中存在一个命名范围。我知道这只能通过遍历为工作簿定义的名称列表并将每个条目与目标值进行比较来完成。



我有解决问题的方法使用以下代码使用C＃：

I have created Excel 2007 automation app with .NET 2012 MFC using wrapper classes. Things have generally gone OK thanks to information gleaned from articles on CodeProject and MSDN etc. but currently I have a problem which I've so far failed to solve, I want to confirm that a named range exists in a workbook. I understand that this can only be done by iterating through the list of names defined for the workbook and comparing each entry against the target value.

I have a solution the problem using C# using the following code:

try
{
    Excel.Application objApp = new Excel.Application();
    Excel.Workbook objWB = objApp.Workbooks.Open(textBox1.Text);

    foreach (Excel.Name name in objWB.Names)
    {
        if (name.Name == "DR_ClosureTracking")
        {
            bFound = true;
            break;
        }
    }

}
catch (Exception theException)
{
    m_theException = theException;
}

但我真正想要的是C ++中的等价物，但到目前为止我只有以下内容：

But what I really want is the the equivalent in C++, but so far I only have the following:

if ((lpDisp = pAppPtr->get_ActiveWorkbook()) != NULL)
{
    pWorkbook->AttachDispatch(lpDisp);

    if ((lpDisp = pWorkbook->get_Names()) != NULL)
    {
        pNames->AttachDispatch(lpDisp);

        for (i=0; i<pNames->get_Count(); i++)
        {
        }
    }
}

根据我的理解，问题的症结在于我不知道如何迭代for循环限制的值列表。基于同一工作簿的C＃解决方案，我知道pNames-> get_Count（）返回正确的值。我从类型库中获得了CName.h头文件，我假设这个类在某种程度上用于解释定义名称列表中的数据，但不知道如何从pNames获取数据。



我一直在努力寻找解决方案，但到目前为止无济于事。



有什么建议吗？

Based on my understanding, the crux of my problem is that I don't know how the iterate through the list of values bounded by the for loop. Based on the C# solution for the same workbook, I know that pNames->get_Count() returns the correct value. I have got the CName.h header file from the type library and I assume that this class is in some way used to interpret the data from the list of defined names but don't know how the get there from pNames.

I have searched long and hard for a solution to this but so far to no avail.

Any suggestions ?

推荐答案

找到以下页面后

http://msdn.microsoft.com/en-us/library/office/bb211879%28v=office.12%29.aspx [< a href =http://msdn.microsoft.com/en-us/library/office/bb211879%28v=office.12%29.aspxtarget =_ blanktitle =New Window> ^ ]

我提出了以下解决方案：

After finding the following page
http://msdn.microsoft.com/en-us/library/office/bb211879%28v=office.12%29.aspx[^]
I came up with the solution below:

// int nRetVal = 0;
int CExcelOLE_Func::FindName(const char * pszName, CName * pName)
{
    ...
    ...
    if (m_pWorkbook != NULL)
    {
        if ((lpDisp = m_pWorkbook->get_Names()) != NULL)
        {
            pNames->AttachDispatch(lpDisp);
    
            i = 1;
            while ((i <= pNames->get_Count()) && (nRetVal == 0))
            {
                if ((lpDisp = pNames->Item(COleVariant((short)i), varOption, varOption)) != NULL)
                {
                    pLocalName->AttachDispatch(lpDisp);
    
                    if (pLocalName->get_Name() == pszName)
                    {
                        nRetVal = 1;
                    }
                }
                i++;
            }
        }
    
    }

    ....
    ....
    return nRetVal;
}

我之前通过索引CNames集合从基数0而不是基数1发出错误（如果来自C / C ++背景！）。我对命名范围感兴趣，并且我能够使用以下方法获得与范围相关联的起始行：

I made a error previously by indexing the CNames collection from base 0 instead of base 1 (an easy mistake if come from a C/C++ background!). I'm interested in named ranges and I been able to obtain the starting row associated with the range using:

if (pExcelFunc->FindName("DR_ClosureTracking", pName))
{
    CRange range;

    if ((lpDisp = pName->get_RefersToRange()) != NULL)
    {
        range.AttachDispatch(lpDisp);

        nBaseRow = range.get_Row();
    }
}

这篇关于C ++ Excel Automation - 检查是否定义了命名范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！