通过Visual C ++ 2005进行MS Excel自动化 [英] MS Excel Automation via Visual C++ 2005

问题描述

我的任务是读取Excel（.xlsx）文件并将数据加载到SQL Server数据库中。



这是我的（不完整）代码;我已经删除了错误检查和printf：

I have been tasked with reading an Excel (.xlsx) file and load the data into a SQL Server database.

Here is my (incomplete) code; I have stripped out error checking and printf's:

#include "stdafx.h"

#include <string>
#include <iostream>
#include <errno.h>

#import "C:/Program Files (x86)/Common Files/microsoft shared/OFFICE15/MSO.DLL" \
    rename("RGB", "MSORGB")

using namespace Office;

#import "C:/Program Files (x86)/Common Files/microsoft shared/VBA/VBA6/VBE6EXT.OLB"

using namespace VBIDE;

#import "C:/Program Files (x86)/Microsoft Office/OFFICE14/EXCEL.EXE" \
    rename("DialogBox",   "ExcelDialogBox") \
    rename("RGB",         "ExcelRGB")       \
    rename("CopyFile",    "ExcelCopyFile")  \
    rename("ReplaceText", "ExcelReplaceText")

int main(int argc, char* argv[])
{
    // 1st parm is path of Excel file
    char xlFilepath[MAX_PATH];
    // strcpy_s(xlFilepath, MAX_PATH, argv[1]);    
    sprintf_s(xlFilepath, MAX_PATH, "%s", argv[1]);
    short firstRow = 5;                // Default first row

    if (argc >= 3)
    {
        firstRow = atoi(argv[2]) + 1;
    }

    // Initialize COM
    HRESULT hr = CoInitialize(NULL);

    // Create Excel Application Object pointer
    Excel::_ApplicationPtr pXL; ///// (1)
    if (FAILED(pXL.CreateInstance("Excel.Application")))
    {
        std::cout << "Failed to initialize Excel::_Application!" << std::endl;
        return 0;
    }

    // Open the Excel Workbook, but don't make it visible
    _bstr_t bFilepath(xlFilepath); ///// (2)
    Excel::WorkbooksPtr pWB = pXL->Workbooks->Open(xlFilepath);
    /*
    if (NULL == pWB)
    {
        std::cerr << "Failed to open Excel file: " << xlFilepath 
                  << ": " << errno << std::endl;
        _exit(3);
    }
    */
    pXL->PutVisible(0, FALSE);

    // Access Excel Worksheet and return pointer to Worksheet cells
    Excel::_WorksheetPtr pWksheet = pXL->ActiveSheet;
    Excel::RangePtr pRange = pWksheet->Cells;

    short currID;
    char  currProvider[31];
    short currentRow = firstRow;

    while (true)
    {
        // Have we reached the end of the data?
        if (pRange->Item[currentRow][1].vt == VT_EMPTY)
        {
            break;
        }
        currID = pRange->Item[firstRow][1].uintVal;
        char* p = _com_util::ConvertBSTRToString(pRange->Item[firstRow][2].bstrVal);
        strcpy_s(currProvider, 31, p);
        delete [] p;

        currentRow++;
    }

	return 0;
}

输入参数是文件路径和可选的要跳过的行数（默认值= 4）。



我有一个指向Excel应用程序（1）的指针，将文件路径转换为BSTR，然后在文件路径（2）上调用Open。我不知道这是否正常。 Open（）的文档说它返回一个指向工作簿的指针;此调用返回NULL。完成通话需要30秒（！）。下面的代码在显示errno的值后终止程序：0。所以，我注释掉整个块，因为没有错误。



我需要将C风格的字符串转换为BSTR以调用Open（）？我最初用字符串调用它，但是当我没有得到我想要的结果时改变它。



在while循环中，程序应该读取每个记录并将其插入数据库（未完成）。我猜测当工作表用完数据时，当前行的第一列将为空，因此我的程序将其与VT_EMPTY进行比较。



观看此代码调试器，我可以看到程序检查Variant :: vt的每一行，其值为9.超过6000条记录。 xlsx文件中只有500多条记录。



我的猜测（关于Excel自动化的文档稀疏）做得不好，所以现在我正在寻求帮助万一有人知道我不知道的事情（当然！）。



看来Excel :: Workbooks :: Open（）的调用成功了，因为errno为零（这是否适用于对Excel API的COM调用？）并且在调用后继续执行程序时没有任何事情发生。 pXL-> ActiveSheet提供有效地址，pWkshee-> Cells也是如此。 （我应该使用Range而不是Cells吗？）



当然，由于VARIANT的vt字段显示为9（VT_DISPATCH），因此值是有意义的IDispatch实例的地址，而不是指向C风格字符串的指针。



我做错了什么？对Open（）的调用似乎不会影响errno，但我觉得它可能无法正常工作：它返回NULL。



其他我不知道的地方知道我在做什么，我试图访问Cells属性，RangePtr的解释，以及每个实际单元格的访问。



如果有人知道Excel自动化（或办公自动化），我愿意接受任何想法。



谢谢

Input parameters are file path and optional # of rows to skip (default = 4).

I got a pointer to and Excel application (1), convert the file path to a BSTR, then call Open on the file path (2). I cannot tell if this works correctly or not. The documentation for Open() says it returns a pointer to the Workbook; this call returns NULL. It takes over 30 seconds (!) to complete the call. The code right below that terminates the program after displaying the value of errno: 0. So, I commented out that whole block, since there was no error.

Do I need to convert the C-style string to a BSTR for the call to Open()? I originally called it with the string, but changed it when I was not getting the results I wanted.

In the "while" loop is where the program should read each record and insert it into the database (not done). I guessed that when the Worksheet ran out of data, the first column of the current row would be empty, so my program compares it to VT_EMPTY.

Watching this code in the debugger, I can see that every row where the program checks the Variant::vt, its value is 9. For over 6000 records. There are only 500+ records in the xlsx file.

My guesses (documentation on Excel automation are sparse) have not done too well, so now I am asking for help in case anyone knows something that I don't (surely!).

It appears that the call to Excel::Workbooks::Open() succeeded, since errno is zero (does that apply to COM calls to the Excel API?) and nothing blows up when I continue the program after the call. pXL->ActiveSheet gives a valid address, as does pWkshee->Cells. (Should I use Range instead of Cells?)

Naturally, since the vt field of the VARIANT shows 9 (VT_DISPATCH), it makes sense that the value would be an address of an IDispatch instance, instead of a pointer to a C-style string.

What am I doing wrong? The call to Open() does not seem to affect errno, yet I feel that it may not be working correctly: it returns NULL.

Other areas where I do not know what I am doing are where I try to access the Cells property, interpretation of the RangePtr, and the access of each actual cell.

If anyone that knows about Excel automation (or Office automation), I am open for any ideas.

Thank you

推荐答案

1 。自动化中的每个字符串都必须是BSTR。真的每一个......

2.这种持久的呼唤是正常的。你需要让你的代码稳定。

3.如果单元格为空，应该得到一个空字符串 - 但它是一个字符串。示例：

1. Every string in automation must be a BSTR. Really every...
2. That calls enduring so long is "normal". You need to make your code stable for that.
3. If the cells are empty, should get an empty string - but it is a string. Example:

char *s = "";

最后一条提示：请仔细阅读文档。 VARIANT是一种复杂的类型，不要与BSTR混合。



祝你好运。

One last tip: Read the documentation carefully. A VARIANT is a complex type dont mix it with a BSTR.

Good luck.

我发现了一篇关于Code Project的文章这帮助了我遇到的所有障碍： MS使用C ++进行Office OLE自动化 [ ^ ]



本文讨论了它的附带代码，在我发现这一点之前，这引起了我的一些困惑。用于通过自动化访问Office应用程序的方法应该是三个中最难使用的方法，但是，与#import方法相比，我发现我觉得我可以更好地控制我想要做的事情，因为我实际上可以在调试器中运行时看到Excel对象的属性。 （我没有尝试使用MFC包装器方法，因为我尝试了第二种方法。）

I found an article on Code Project that helped me over all the hurdles I have encountered: MS Office OLE Automation Using C++[^]

The article discusses its accompanying code, which caused some confusion on my part until I figured that out. The method used to access Office applications via Automation is supposed to be the hardest of the three to use, but, compared to the "#import" method, I found that I felt like I had better control of what I wanted to do because I could actually see the properties of the Excel objects while running in the debugger. (I did not try the MFC wrapper method, since I was successful with the second method I tried.)

这篇关于通过Visual C ++ 2005进行MS Excel自动化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！