问题描述
大家好,这是我在这里的第一篇文章.
首先,我要感谢所有花一些时间来查看此主题并尝试提供帮助的人.
我遇到的问题如下:
我已阅读以下Microsoft文章( http://support.microsoft.com/kb/216686 [ ^ ]):
如何在不使用MFC或#import的情况下从C ++自动执行Excel"
那里提供的代码满足了我的需求.
但是,由于数组为15x15,因此指定的范围是常数(L"A1:O15"),
但是我的数组是通过变量设置的(int row,column;),所以我需要动态地计算范围.
本文(AutoWrap())中的Helper函数采用常量字符串(L"A1:O15")并将其转换为range.
我的问题如下:
我如何创建字符串以传递给辅助函数(AutoWrap()),以便获得A1到xy的范围,其中x是最后一列("Z"或"AA"或"AAA"),y是行数;
我已经在行变量中存储了行数,但是无法将列数转换为适当的字符串.
为了澄清,如果我的数组是10x5,如何将字符串L"A1:E10"传递给辅助函数AutoWrap()?
我使用MS Visual C ++ Express加法并在纯Win32 API中工作.
Hello everyone,this is my first post here.
I would like to start by saying thanks to everyone who takes some time to view this thread and try to help.
The problem i have is following:
I have read following Microsoft article(http://support.microsoft.com/kb/216686[^]):
"How to automate Excel from C++ without using MFC or #import"
and code presented there suites my needs.
However,the range specified there is constant (L"A1:O15") since the array is 15x15,
but my array is set through variables( int row,column;) so i need to calculate range dynamically.
Helper function in the article (AutoWrap()) takes constant string(L"A1:O15") and converts it to range.
My question is following:
How do I create string to pass to helper function (AutoWrap()) so I can get range from A1 to xy,where x is last column("Z" or "AA" or "AAA" ) and y is number of rows;
I already have stored number of rows in row variable,but I can''t convert the number of columns to appropriate string.
To clarify,if my array is 10x5 how do i pass string L"A1:E10" to helper function AutoWrap() ?
I use MS Visual C++ Express addition and work in pure Win32 API.
推荐答案
int cntRows = 5;
int cntCols = 10;
wchar_t buffer [30];
swprintf (buffer, L"A1::%c%d", ''A''+cntCols-1, cntRows);
只要您的cntCols小于或等于26,此方法就起作用.要生成诸如AA,AB,AC等的列标记,只需要多做一些工作:
This works as long as your cntCols is lower or equal to 26. To generate column designators like AA, AB, AC etc. it takes just a little more work:
wchar_t colName[3];
if (cntCols <= 26)
swprintf (colName, L"%c", ''A'' + cntCols - 1);
else if (cntCols <= 26*26)
swprintf (colName, L"%c%c", ''A'' + cntCols%26 - 1, ''A'' + cntCols/26 - 1);
else
; // ... error processing
wchar_t buffer [30];
swprintf (buffer, L"A1::%s%d", colName, cntRows);
希望有帮助.
在第二个代码片段中,用wchar_t替换char.
用swprintf替换_stprintf
Hope that helps.
Replace char with wchar_t in the second code snipped.
EDIT 2: Replaced _stprintf by swprintf
Dim rng As Range
Dim iRowCount As Integer, iColCount As Integer
Set rng = ThisWorkbook.Worksheets(1).Range("A1:C15")
'get columns count
iColCount = rng.Cells.Columns.Count
'get rows count
iRowCount = rng.Cells.Rows.Count
'display message
MsgBox "Columns count: " & iColCount & vbCr & "Rows count: " & iRowCount, vbInformation, rng.Address
Set rng = Nothing
更多信息:
如何引用单元格和范围 [ ^ ]
上面的链接与Excel 2003 VBA参考有关,但说明了如何实现所需的功能.
More at:
How to reference Cells and Ranges[^]
The above link is related to Excel 2003 VBA reference, but explain how to achieve what you want.
- 我已经添加了变量row和cols-这些变量控制excel中影响的范围的大小.
- 由于没有使用MS编译器,我不得不在文件中添加一些GUID定义
- 您需要链接ole32和oleaut32库-用于VS的.lib,用于gcc的.a
- nv3表示您可以简单地将缓冲区用于SysAllocString
- 出于完整性考虑,我将整个来源都包括在内
#define UNICODE
#include <windows.h>
#include <stdio.h>
#include <ole2.h> // OLE2 Definitions
const GUID GUID_NULL = { 0, 0, 0, { 0, 0, 0, 0, 0, 0, 0, 0 } };
const GUID IID_IUnknown = {0x00000000, 0x0000, 0x0000, {0xC0, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x46}};
const GUID IID_IDispatch = {0x00020400, 0x0000, 0x0000, {0xC0, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x46}};
int cols=8, rows=16;
// AutoWrap() - Automation helper function...
HRESULT AutoWrap(int autoType, VARIANT *pvResult, IDispatch *pDisp, LPOLESTR ptName, int cArgs...) {
// Begin variable-argument list...
va_list marker;
va_start(marker, cArgs);
if(!pDisp) {
MessageBox(NULL, L"NULL IDispatch passed to AutoWrap()", L"Error", 0x10010);
_exit(0);
}
// Variables used...
DISPPARAMS dp = { NULL, NULL, 0, 0 };
DISPID dispidNamed = DISPID_PROPERTYPUT;
DISPID dispID;
HRESULT hr;
wchar_t buf[200];
char szName[200];
// Convert down to ANSI
WideCharToMultiByte(CP_ACP, 0, ptName, -1, szName, 256, NULL, NULL);
// Get DISPID for name passed...
hr = pDisp->GetIDsOfNames(IID_NULL, &ptName, 1, LOCALE_USER_DEFAULT, &dispID);
if(FAILED(hr)) {
wsprintf(buf, L"IDispatch::GetIDsOfNames(\"%s\") failed w/err 0x%08lx", szName, hr);
MessageBox(NULL, buf, L"AutoWrap()", 0x10010);
_exit(0);
return hr;
}
// Allocate memory for arguments...
VARIANT *pArgs = new VARIANT[cArgs+1];
// Extract arguments...
for(int i=0; i<cArgs; i++) {
pArgs[i] = va_arg(marker, VARIANT);
}
// Build DISPPARAMS
dp.cArgs = cArgs;
dp.rgvarg = pArgs;
// Handle special-case for property-puts!
if(autoType & DISPATCH_PROPERTYPUT) {
dp.cNamedArgs = 1;
dp.rgdispidNamedArgs = &dispidNamed;
}
// Make the call!
hr = pDisp->Invoke(dispID, IID_NULL, LOCALE_SYSTEM_DEFAULT, autoType, &dp, pvResult, NULL, NULL);
if(FAILED(hr)) {
wsprintf(buf, L"IDispatch::Invoke(\"%s\"=%08lx) failed w/err 0x%08lx", szName, dispID, hr);
MessageBox(NULL, buf, L"AutoWrap()", 0x10010);
_exit(0);
return hr;
}
// End variable-argument section...
va_end(marker);
delete [] pArgs;
return hr;
}
int main()
{
// Initialize COM for this thread...
CoInitialize(NULL);
// Get CLSID for our server...
CLSID clsid;
HRESULT hr = CLSIDFromProgID(L"Excel.Application", &clsid);
if(FAILED(hr)) {
MessageBox(NULL, L"CLSIDFromProgID() failed", L"Error", 0x10010);
return -1;
}
// Start server and get IDispatch...
IDispatch *pXlApp;
hr = CoCreateInstance(clsid, NULL, CLSCTX_LOCAL_SERVER, IID_IDispatch, (void **)&pXlApp);
if(FAILED(hr)) {
MessageBox(NULL, L"Excel not registered properly", L"Error", 0x10010);
return -2;
}
// Make it visible (i.e. app.visible = 1)
{
VARIANT x;
x.vt = VT_I4;
x.lVal = 1;
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlApp, L"Visible", 1, x);
}
// Get Workbooks collection
IDispatch *pXlBooks;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"Workbooks", 0);
pXlBooks = result.pdispVal;
}
// Call Workbooks.Add() to get a new workbook...
IDispatch *pXlBook;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBooks, L"Add", 0);
pXlBook = result.pdispVal;
}
// Create a 15x15 safearray of variants...
VARIANT arr;
arr.vt = VT_ARRAY | VT_VARIANT;
{
SAFEARRAYBOUND sab[2];
sab[0].lLbound = 1; sab[0].cElements = rows;
sab[1].lLbound = 1; sab[1].cElements = cols;
arr.parray = SafeArrayCreate(VT_VARIANT, 2, sab);
}
// Fill safearray with some values...
for(int i=1; i<=rows; i++)
{
for(int j=1; j<=cols; j++)
{
// Create entry value for (i,j)
VARIANT tmp;
tmp.vt = VT_I4;
tmp.lVal = i*j;
// Add to safearray...
long indices[] = {i,j};
SafeArrayPutElement(arr.parray, indices, (void *)&tmp);
}
}
// Get ActiveSheet object
IDispatch *pXlSheet;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"ActiveSheet", 0);
pXlSheet = result.pdispVal;
}
// Get Range object for the Range A1:O15...
IDispatch *pXlRange;
{
VARIANT parm;
parm.vt = VT_BSTR;
wchar_t buffer[32];
wsprintf(buffer, L"%c%d:%c%d", 'A'+0, 1, 'A'+cols-1, rows);
parm.bstrVal = SysAllocString(buffer);
//parm.bstrVal = SysAllocString(L"A1:O15");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1, parm);
VariantClear(&parm);
pXlRange = result.pdispVal;
}
// Set range with our safearray...
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlRange, L"Value", 1, arr);
// Wait for user...
MessageBox(NULL, L"All done.", L"Notice", 0x10000);
// Set .Saved property of workbook to TRUE so we aren't prompted
// to save when we tell Excel to quit...
{
VARIANT x;
x.vt = VT_I4;
x.lVal = 1;
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlBook, L"Saved", 1, x);
}
// Tell Excel to quit (i.e. App.Quit)
AutoWrap(DISPATCH_METHOD, NULL, pXlApp, L"Quit", 0);
// Release references...
pXlRange->Release();
pXlSheet->Release();
pXlBook->Release();
pXlBooks->Release();
pXlApp->Release();
VariantClear(&arr);
// Uninitialize COM for this thread...
CoUninitialize();
return 0;
}
这篇关于将整数转换为Excel范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!