c++操作excel文件

HRESULT AutoWrap(int autoType, VARIANT *pvResult, IDispatch *pDisp, LPOLESTR ptName, int cArgs...)
{

va_list marker;
va_start(marker, cArgs);

if(!pDisp) {
MessageBox(NULL, "NULL IDispatch passed to AutoWrap()", "Error", 0x10010);
_exit(0);
}

// Variables used...
DISPPARAMS dp = { NULL, NULL, 0, 0 };
DISPID dispidNamed = DISPID_PROPERTYPUT;
DISPID dispID;
HRESULT hr;
char 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)) {
  sprintf(buf, "IDispatch::GetIDsOfNames(\"%s\") failed w/err 0x%08lx", szName, hr);
  MessageBox(NULL, buf, "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)) {
  sprintf(buf, "IDispatch::Invoke(\"%s\"=%08lx) failed w/err 0x%08lx", szName, dispID, hr);
  MessageBox(NULL, buf, "AutoWrap()", 0x10010);
  _exit(0);
  return hr;
}
// End variable-argument section...
va_end(marker);

delete [] pArgs;

return hr;
}

//=====================

int DoExcleFile(RETURNSET * pReturn,PROVALUE * pFldNames)
{
// 初始化COM库
CoInitialize(NULL);

// 获得EXCEL的CLSID
CLSID clsid;
HRESULT hr = CLSIDFromProgID(L"Excel.Application", &clsid);

if(FAILED(hr)) {
::MessageBox(NULL, "CLSIDFromProgID() 函数调用失败！", "错误", 0x10010);
return -1;
}

// 创建实例
IDispatch *pXlApp;
hr = CoCreateInstance(clsid, NULL, CLSCTX_LOCAL_SERVER, IID_IDispatch, (void **)&pXlApp);

if(FAILED(hr)) {
::MessageBox(NULL, "请检查是否已经安装EXCEL！", "错误", 0x10010);
return -2;
}

/*-------------------------------------
// 显示，将Application.Visible属性置1
VARIANT x;
x.vt = VT_I4;
x.lVal = 1;
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlApp, L"Visible",1, x);
-------------------------------------*/

//不显示打开？
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlApp, L"Visible",1,0);

// 获取Workbooks集合
IDispatch *pXlBooks;
{
  VARIANT result;
  VariantInit(&result);
  AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"Workbooks", 0);
  pXlBooks = result.pdispVal;
}

// 调用Workbooks.Add()方法，创建一个新的Workbook
IDispatch *pXlBook;
{
  VARIANT result;
  VariantInit(&result);
  AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBooks, L"Add", 0);
  pXlBook = result.pdispVal;
}

//创建数据数组
VARIANT arr;
WCHAR szTmp[256];
arr.vt = VT_ARRAY | VT_VARIANT;
SAFEARRAYBOUND sab[2];

int columnNum = pFldNames->size();
int lineNum = pReturn->size();
sab[0].lLbound = 1; sab[0].cElements = lineNum+1;
sab[1].lLbound = 1; sab[1].cElements = columnNum;
arr.parray = SafeArrayCreate(VT_VARIANT, 2, sab);

for (int i=0;i<lineNum+1;i++)
{
  RECORDSET *pRecord = NULL;
  if (i != 0)
   pRecord = pReturn->at(i-1);

  for (int j=0;j<columnNum;j++)
  {
   string strtemp;
   if (i == 0)
    strtemp = _com_util::ConvertBSTRToString(_bstr_t(pFldNames->at(j)));
   else if (pReturn != NULL)
   {
    _variant_t var = pRecord->at(j);
    if (var.vt == VT_NULL)
     strtemp = "";
    else
     strtemp = (LPCTSTR)(_bstr_t)var;
   }

   VARIANT tmp;
   tmp.vt = VT_BSTR;
   int   nLen   =   strlen(strtemp.c_str())+1;
   MultiByteToWideChar(CP_ACP,0,strtemp.c_str(),nLen,szTmp,256);
   tmp.bstrVal = SysAllocString(szTmp);

   // 添加数据到数组中
   long indices[] = {i+1,j+1};
   SafeArrayPutElement(arr.parray, indices, (void *)&tmp);
  }

}

// 从Application.ActiveSheet属性获得Worksheet对象
IDispatch *pXlSheet;
{
  VARIANT result;
  VariantInit(&result);
  AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"ActiveSheet", 0);
  pXlSheet = result.pdispVal;
}

// 选择一个columnNum*（lineNum+1）大小的Range
IDispatch *pXlRange;
{
  VARIANT parm;
  parm.vt = VT_BSTR;

  CString strset;
  if(columnNum>=26) // 因为前26列用A～Z表示，大于26列都是用两个字母表示的，如从27列开始是AA、AB...
   strset.Format(_T("A1:%c%c%d"), columnNum/26+64, columnNum%26+64, lineNum+1);
  else
   strset.Format(_T("A1:%c%d"), columnNum+64, lineNum+1);

  WCHAR wBuf[100];
  ::MultiByteToWideChar(CP_ACP,0,strset.GetBuffer(),strset.GetLength()+1,wBuf,100);
  parm.bstrVal = ::SysAllocString(wBuf);

  VARIANT result;
  VariantInit(&result);
  AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1, parm);
  VariantClear(&parm);

pXlRange = result.pdispVal;
}

// 用我们的数组填充这个Range
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlRange, L"Value", 1, arr);

// 接下来我们该保存文件了，利用Worksheet.SaveAs()方法(我这里忽略了其他所有参数，除了文件名)
{
VARIANT filename;
filename.vt = VT_BSTR;

  int index = GetFileIndex();
  if (index == 0)
   return -1;

  char pathBuf[MAX_PATH];
  DWORD dw = GetCurrentDirectory(MAX_PATH,pathBuf);
  if (dw == 0)return -1;
  strcat(pathBuf,"http://www.cnblogs.com/justinpanrobot/admin/file://temp/");

  char fileBuf[30]="";
  sprintf_s(fileBuf,"http://www.cnblogs.com/justinpanrobot/admin/file://dtmp%25d.xls%22,index/);
  strcat(pathBuf,fileBuf);

  WCHAR szTmp[256];
  int nlen = strlen(pathBuf)+1;
  MultiByteToWideChar(CP_ACP,0,pathBuf,nlen,szTmp,256);
  filename.bstrVal = SysAllocString(szTmp);
  AutoWrap(DISPATCH_METHOD, NULL, pXlSheet, L"SaveAs", 1, filename);
}

// 退出，调用Application.Quit()方法
AutoWrap(DISPATCH_METHOD, NULL, pXlApp, L"Quit", 0);

// 释放所有的接口以及变量
pXlRange->Release();
pXlSheet->Release();
pXlBook->Release();
pXlBooks->Release();
pXlApp->Release();
VariantClear(&arr);

// 注销COM库
CoUninitialize();

return 0;
}

posted @ 2009-01-06 11:23 justinpan 阅读(3758) 评论(0) 编辑收藏举报

小小

飞人

c++操作excel文件

公告