求助：使用ado写excel文件出现问题

[Badwood]

使用ado通过odbc建的数据源对excel进行操作，能够读取，也能够写，但是写完之后文件变得比原来大上近1倍，而且不能再使用(用excel打开会导致程序崩溃)，相同的代码对access就没有任何问题（代码不作任何修改，只是把odbc数据源改成access的驱动），不知道哪位大侠能解决这个问题？谢谢。

[内存泄漏]

把相关数据库操作的代码贴出来看看.....

[Badwood]

我使用了VCKbase.com上面封装的一个ado类，具体类的代码见：
http://www.vckbase.com/code/downcode.asp?id=1926

在我的代码里面是如此调用的：
rsNowDone.Delete(adAffectAll);//此处是删除所有记录，只删除一条也是一样效果。
rsNowDone.Update();
调用代码很简单，但是就是会破坏掉excel文件。其他插入记录也是如此。
需要说明的是，不使用封装的这个类，直接调用ado的接口函数一样会这样。

无法解释的是为什么不改动任何代码（使用同一个exe)，只要把数据链接指向access就能成功？

[ifengfeng]

删除记录你干吗使用RS呀，直接使用_ConnectionPtr Execute就可以了啊。

[Mr-Chen]

先看看这个程序

[masterz]

直接用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;
}

[Promise91]

观注。。。

[qrlvls]

ADO 比较适合表结构不太复杂的场合，否则可能要多花时间去研究各种场合的区别了
用 Automation 则操作起来麻烦一些，速度慢一点