我现在用ado操作access数据库,现在我需要利用ado将access数据库中的表格导出为excel表格。我造了一下利用odbc进行转换的方法,但利用ado转换的方法却很少,希望大家帮帮忙哈!VC/MFC ADO ACCESS EXCEL。如果有实例最好。
解决方案 »
- 既然MFC已经包装了BeginPrint和EndPrint函数,那么CView::OnPreprePrinting的作用到底是什么呢?
- ListView总是夺取了消息,怎么办?
- 非模式对话框无法进PreTranslateMessage,该怎么办?
- vc中设备上下文dc和兼容dc有什么联系?
- 数组的值的获取 与 修改
- 求解在WIN2003 或者IE6SP1下,OnBeforeNavigate2 失效问题
- 怎样向另外一个进程发送Alt+F消息
- 关于office文档自定义属性的难题?
- 新手请教,这段MFC代码有new为何没有delete?
- 我在CMYVIEW里做了个函数A,如何在A中调用CMYVIEW的析构函数?
- 求《Win32多线程程序设计》源码!请发至[email protected],多谢!
- 如何根据别的机子发过来的UDP包知道对方的端口号和IP
如果仅仅是上述功能的话,还不如使用dao,比较简便,dao专门针对access写的,默认就是mdb文件。
用一个读出access文件的数据,另一个执行sql语句写入excle
通过ADO来分别读写excel和access
{
if(NULL==filename)
return __LINE__;
bool bAttachToExistingInstance = false;
_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);
//IUnknown *pUnk;
//HRESULT hr = GetActiveObject(clsid, NULL, (IUnknown**)&pUnk);
//if(SUCCEEDED(hr))
//{
// _Application *pDisp;
// hr = pUnk->QueryInterface(__uuidof(_Application), (void **)&pDisp);
// if(SUCCEEDED(hr))
// {
// app.Attach(pDisp);
// bAttachToExistingInstance = true;
// }
// pUnk->Release();
//} HANDLE hSnap = NULL;
{
if(app==NULL)
{
app.CreateInstance(clsid);
bAttachToExistingInstance = false;
} 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(); Verbose("%s[%d]%s",__FILE__,__LINE__,__FUNCTION__);
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();
if(BrandIndex == iCol &&varfieldvalue.vt!=VT_NULL)
MapID2Name((int)varfieldvalue,m_bEnglish,m_BrandName,sizeof(m_BrandName)/sizeof(m_BrandName[0]),m_mapBrandName,saRet2,index);
else if(ModelIndex == iCol&&varfieldvalue.vt!=VT_NULL)
MapID2Name((int)varfieldvalue,m_bEnglish,m_ModelName,sizeof(m_ModelName)/sizeof(m_ModelName[0]),m_mapModelName,saRet2,index);
else if(RegionIndex == iCol &&varfieldvalue.vt!=VT_NULL)
MapID2Name((int)varfieldvalue,m_bEnglish,m_Region,sizeof(m_Region)/sizeof(m_Region[0]),m_mapRegion,saRet2,index);
else if(ProvinceIndex == iCol && varfieldvalue.vt!=VT_NULL)
MapID2Name((int)varfieldvalue,m_bEnglish,m_Province,sizeof(m_Province)/sizeof(m_Province[0]),m_mapProvince,saRet2,index);
else if(CityIndex == iCol && varfieldvalue.vt!=VT_NULL)
MapID2Name((int)varfieldvalue,m_bEnglish,m_City,sizeof(m_City)/sizeof(m_City[0]),m_mapCity,saRet2,index);
else
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();
long ddechannel =0;
app->Quit();
Sleep(100);
}
return ERROR_SUCCESS;
}