尝试添加Excel工作表 [英] trying to add a excel worksheet

问题描述

我有一个OLE Autmation c ++项目，我正在使用#import语句与Excel应用程序进行交互。

我在当前（活动）工作表之后添加新工作表时遇到问题。



以下调用将添加一个新标签：

Add（vtMissing，vtMissing，（long）1，（long）xlWorksheet）



但是我不知道第二个参数应该是新表格附加的内容。



excel对象模型参考声明它应该是：



一个对象，指定添加新工作表之后的工作表。



这是什么意思？有什么想法吗？

I have an OLE Autmation c++ project where i'm using #import statements to interact with an Excel application.
I'm having trouble adding a new worksheet AFTER the current (active) worksheet.

the following call will prepend a new tab:
Add(vtMissing, vtMissing, (long)1, (long)xlWorksheet)

but I don't know what the 2nd argument should be for the new sheet to append.

The excel object model reference states it should be:

"An object that specifies the sheet after which the new sheet is added."

What does this mean? Anyone have any ideas?

推荐答案

试着用一些东西：



Excel :: _ ApplicationPtr XL; < br $> b $ b ....



Excel :: _ WorkbookPtr工作簿= XL->工作簿 - >添加（Excel :: xlWorksheet）;

Excel :: _ WorksheetPtr工作表= XL-> ActiveSheet;

工作表 - >名称=最后一页;



worksheet = XL-> Worksheets-> Add（）; //添加工作表!!

工作表 - >名称=其他页面;



工作表= XL->工作表 - > ;添加（）;

工作表 - >名称=某些页面;



工作表 - > SaveAs（c：\\ \\\test.xls）;

工作簿 - >关闭（）;

XL->退出（）;



更多信息请参见：

http ：//stackoverflow.com/questions/5308464/create-multiple-excel-sheet-in-vc [ ^ ]





或者你可以阅读以下文章，我希望它会对你有所帮助：



通过C ++访问Excel电子表格 [ ^ ]



http://www.maths.manchester.ac.uk/~ahazel/EXCEL_C++.pdf [ ^ ]



http://blogs.technet.com/b/heyscriptingguy/archive/2005/12/15/how-can-i-add-additional-worksheets-to-an-excel-workbook.aspx [ ^ ]



http://msdn.microsoft.com/en-us/library/ff196568.aspx [ ^ ]

Try to use something:

Excel::_ApplicationPtr XL;
....

Excel::_WorkbookPtr workbook = XL->Workbooks->Add(Excel::xlWorksheet);
Excel::_WorksheetPtr worksheet = XL->ActiveSheet;
worksheet->Name = "last page";

worksheet = XL->Worksheets->Add(); // adding worksheets!!
worksheet->Name = "other page";

worksheet = XL->Worksheets->Add();
worksheet->Name = "some page";

worksheet->SaveAs("c:\\test.xls");
workbook->Close();
XL->Quit();

More see here:
http://stackoverflow.com/questions/5308464/create-multiple-excel-sheet-in-vc[^]


Or you can read following articles, I hope it will help you:

Accessing Excel Spreadsheets via C++[^]

http://www.maths.manchester.ac.uk/~ahazel/EXCEL_C++.pdf[^]

http://blogs.technet.com/b/heyscriptingguy/archive/2005/12/15/how-can-i-add-additional-worksheets-to-an-excel-workbook.aspx[^]

http://msdn.microsoft.com/en-us/library/ff196568.aspx[^]

您需要提供函数有效工作表对象，而不是索引到Sheets集合。



启动一个新的excel实例并记录2个宏，我们得到以下代码：



1. Sheet2和amp;之间添加的工作表Sheet3（右键单击表3，添加新工作表）

Rather than indexes into the Sheets collection, you need to supply the function valid worksheet objects.

Firing up a new instance of excel and recording 2 macros, we get the following code:

1. Sheet added between Sheet2 & Sheet3 (right-click sheet 3, add new worksheet)

Sheets("Sheet3").Select
Sheets.Add

2.在所有工作表结束后添加工作表

2. Sheet added at the end, after all sheets

Sheets.Add After:=Sheets(Sheets.Count)

一个c ++片段我只是在一起实现＃2：

（记住）我们必须以相反的顺序传递args用于COM调用）

A c++ snippet I just bashed together that achieves #2:
(remembering that we have to pass the args in reverse order for COM calls)

// Get Workbooks collection
IDispatch *pXlBooks;
pXlBooks = getIDispatchVal(pXlApp, L"WorkBooks");

// Call Workbooks.Add() to get a new workbook...
 IDispatch *pXlBook;
 pXlBook = getIDispatchVal(pXlBooks, L"Add");

 IDispatch *pXlSheets;
 pXlSheets = getIDispatchVal(pXlBook, L"Worksheets");


 int curBookSheetCount = getCount(pXlSheets);
 IDispatch *aftrSheet = getItem(pXlSheets, curBookSheetCount);
 IDispatch *pNewSheet;
 {
     VARIANT result, var1,var2;
     VariantInit(&result);

     var1.pdispVal = NULL;
     var1.vt = VT_NULL;

     var2.pdispVal = aftrSheet;
     var2.vt = VT_DISPATCH;

     AutoWrap(DISPATCH_METHOD, &result, pXlSheets, L"Add", 2, var2, var1);
     pNewSheet = result.pdispVal;
 }

我的几个辅助函数：

A couple of my helper functions:

int getCount(IDispatch *pCollection)
{
    VARIANT result;
    VariantInit(&result);
    result.pdispVal = NULL;
    AutoWrap(DISPATCH_PROPERTYGET, &result, pCollection, L"Count", 0);
    return result.lVal;
}

IDispatch *getItem(IDispatch *pCollection, int index)
{
    VARIANT result, param1;
    VariantInit(&result);
    result.pdispVal = NULL;
    param1.vt = VT_I4;
    param1.lVal = index;
    AutoWrap(DISPATCH_PROPERTYGET, &result, pCollection, L"Item", 1, param1);
    return(result.pdispVal);
}

// EDIT: I forgot this one.
IDispatch *getIDispatchVal(IDispatch *pObject, wchar_t *valName)
{
    VARIANT result;
    VariantInit(&result);
    AutoWrap(DISPATCH_PROPERTYGET, &result, pObject, valName, 0);
    return result.pdispVal;
}

您可能会遇到很多此类问题。使用自动化时，最好在VBA中制作小型测试功能，只是为了了解它的工作原理。之后在C ++中做同样的事情。通过这样做，您将更快地理解文档，类型库以及完全正确的步骤。

You will likely have many such type of questions. When working with automation, is better to make small test functions in VBA, just to see how it works. And after that do the same thing in C++. By doing like this you will understand much faster the documentation, the type library and what are exactly exactly the steps to do.

这篇关于尝试添加Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！