直接用automation操作excel方便多了
int Cxxx::SaveAsExcel(_RecordsetPtr& pRst, LPCTSTR filename)
{
if(NULL==filename)
return __LINE__;
_variant_t covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR);
DWORD dwStartTime = GetTickCount();
_ApplicationPtr app;//("Excel.Application");
HRESULT hr = S_OK;
CLSID clsid;
CLSIDFromProgID(L"Excel.Application", &clsid);
{
app.CreateInstance(clsid);
WorkbooksPtr books;
_WorkbookPtr book;
long lcid =LOCALE_USER_DEFAULT;
SheetsPtr sheets;
app->get_Workbooks(&books);
books->Add(covOptional,lcid,&book);
book->get_Worksheets(&sheets);
IDispatchPtr/*LPDISPATCH*//**/ pDisp;
sheets->get_Item(_variant_t((short)1),&pDisp);
_WorksheetPtr sheet(pDisp);
hr = S_OK;
RangePtr rangetmp;
hr=sheet->get_Range(_variant_t("A1"),_variant_t("A1"),(Range**)&rangetmp);
CRange rg;
rg.AttachDispatch(rangetmp);
rangetmp.Detach();
FieldsPtr fds=pRst->GetFields();
long m_iNumRows = 1, m_iNumCols = 0;
fds->get_Count(&m_iNumCols);
COleSafeArray saRet;
DWORD numElements[2];
numElements[0]= m_iNumRows; //Number of rows in the range.
numElements[1]= m_iNumCols; //Number of columns in the range.
saRet.Create(VT_BSTR, 2, numElements);
//Fill the SAFEARRAY.
long index[2];
long iRow, iCol;
rg = rg.get_Resize(COleVariant(m_iNumRows),
COleVariant(m_iNumCols));
int BrandIndex = -1, ModelIndex = -1, RegionIndex = -1,ProvinceIndex = -1, CityIndex=-1;
for(iRow=0;iRow<=m_iNumRows-1;iRow++)
{
for(iCol=0;iCol<=m_iNumCols-1;iCol++)
{
index[0] = iRow;
index[1] = iCol;
VARIANT v;
VariantInit(&v);
v.vt = VT_BSTR;
BSTR bstrFieldName;
fds->Item[(short)iCol]->get_Name(&bstrFieldName);
if(_wcsicmp(bstrFieldName, L"BRANDID")==0)
{
SysFreeString(bstrFieldName);
v.bstrVal = SysAllocString(L"Brand");
BrandIndex = iCol;
}
else
if(_wcsicmp(bstrFieldName, L"MODELID")==0)
{
SysFreeString(bstrFieldName);
v.bstrVal = SysAllocString(L"Model");
ModelIndex = iCol;
}
else
if(_wcsicmp(bstrFieldName, L"region_id")==0)
{
SysFreeString(bstrFieldName);
v.bstrVal = SysAllocString(L"Region");
RegionIndex = iCol;
}
else
if(_wcsicmp(bstrFieldName, L"province_id")==0)
{
SysFreeString(bstrFieldName);
if(m_bEnglish)
v.bstrVal = SysAllocString(L"Province");
else
v.bstrVal = SysAllocString(L"Ê¡");
ProvinceIndex = iCol;
}
else
if(_wcsicmp(bstrFieldName, L"city_id")==0)
{
SysFreeString(bstrFieldName);
if(m_bEnglish)
v.bstrVal = SysAllocString(L"City");
else
v.bstrVal = SysAllocString(L"³ÇÊÐ");
CityIndex = iCol;
}
else
v.bstrVal = bstrFieldName;
saRet.PutElement(index, v.bstrVal);
SysFreeString(v.bstrVal);
VariantClear(&v);
}
}
rg.put_Value2(COleVariant(saRet));
saRet.Detach();
pRst->MoveLast();
long lrec_count=pRst->RecordCount;
pRst->MoveFirst();
if(lrec_count>0)
{
const int MAX_EXCEL_ROWS = 65536-1;//1 is the header
if(lrec_count>MAX_EXCEL_ROWS)
lrec_count = MAX_EXCEL_ROWS;//due to limitation of excel, truncate output
int nExportedCount = 0;
const int MAX_ARRAY_LENGTH = 1024;
for(;;)
{
m_iNumRows = lrec_count - nExportedCount;
if(m_iNumRows > MAX_ARRAY_LENGTH)
m_iNumRows = MAX_ARRAY_LENGTH;
numElements[0]= m_iNumRows; //Number of rows in the range.
numElements[1]= m_iNumCols; //Number of columns in the range.
COleSafeArray saRet2;
saRet2.Create(VT_VARIANT, 2, numElements);
for(iRow=0;iRow<=m_iNumRows-1;iRow++)
{
for(iCol=0;iCol<=m_iNumCols-1;iCol++)
{
index[0] = iRow;
index[1] = iCol;
_variant_t varfieldvalue = pRst->GetFields()->GetItem(_variant_t((long)iCol))->GetValue();
saRet2.PutElement(index,&varfieldvalue );
}
pRst->MoveNext();
}
RangePtr rang2;
{
CRange rg2;
char bufStartCellName[64];
StringCchPrintf(bufStartCellName,sizeof(bufStartCellName),"A%d",nExportedCount+2);
hr=sheet->get_Range(_variant_t(bufStartCellName),_variant_t(bufStartCellName),(Range**)&rang2);
rg2.AttachDispatch(rang2);
rg2 = rg2.get_Resize(COleVariant(m_iNumRows),COleVariant(m_iNumCols));
rg2.put_Value2(COleVariant(saRet2));
saRet2.Detach();
}
rang2.Detach();
nExportedCount += m_iNumRows;
if(nExportedCount >=lrec_count)
break;
}
}
app->put_AskToUpdateLinks(lcid,VARIANT_FALSE);
app->put_AlertBeforeOverwriting(lcid,VARIANT_FALSE);
app->put_UserControl(VARIANT_FALSE);
app->put_DisplayAlerts(0,VARIANT_FALSE);
_variant_t varfilename(filename);
DeleteFile(filename);
HRESULT hrMethod = book->SaveAs(varfilename,covOptional,covOptional,covOptional,covOptional,covOptional,xlNoChange);
if (FAILED(hrMethod))
{
ISupportErrorInfo *pSupport;
hr = book->QueryInterface(IID_ISupportErrorInfo,(void**)&pSupport);
if (SUCCEEDED(hr)) {
hr = pSupport->InterfaceSupportsErrorInfo(__uuidof(Excel::_Workbook));
if (hr == S_OK) { // can't use SUCCEEDED here! S_FALSE succeeds!
IErrorInfo *pErrorInfo;
hr = GetErrorInfo(0, &pErrorInfo);
if (SUCCEEDED(hr)) {
// FINALLY can call methods on pErrorInfo!
BSTR bsrc,bdesc;
pErrorInfo->GetDescription(&bdesc);
pErrorInfo->GetSource(&bsrc);
_bstr_t bSource(bsrc),bDescription(bdesc);
// ...and handle the error!
pErrorInfo->Release(); // don't forget to release!
}
}
pSupport->Release();
}
}
book->put_Saved(0,VARIANT_TRUE);
book->Close(COleVariant(VARIANT_FALSE));
books->Close();
app->Quit();
Sleep(100);
}
return ERROR_SUCCESS;
}