ACCESS表导入EXCEL，先送100分

Question

请问怎么用VC把一个ACCESS表导入EXCEL中去！！！

Answer 1

将表的内容放到一个TDataSet;调用以下函数即可;//將DS輸出到Execl
int txDSToExecl(TDataSet *ds)
{
        Variant ex,newxls,sh;
        try{
                ex=CreateOleObject("Excel.Application");//啟動Excel
                ex.OlePropertySet("Visible",(Variant)true); //使Excel啟動后可見
                newxls=(ex.OlePropertyGet("Workbooks")).OleFunction("Add");//新建一新工作薄
                sh=newxls.OlePropertyGet("ActiveSheet");
        }catch(...)
        {
                //txMsgInf("啟動Excel出錯，可能由于Excel沒有正确安裝");
                return -1;
        }
        if(ds->IsEmpty())
        {
                return 1;
        }
        ds->First();
        sh.OlePropertyGet("Cells").OlePropertyGet("Font").OlePropertySet("Size",11);
        for(int i=0;i<ds->FieldCount;i++)
        {
                sh.OlePropertyGet("Cells",1,i+1).OlePropertySet("Value",(TVariant)(ds->Fields->Fields[i]->FieldName));

Answer 2

偶用VB把ACCESS和EXECL的数据导来到去

Answer 3

void CLog::OnBaoFile() //数据进文件
{
// TODO: Add your control notification handler code here
// get Filename to save data in
static char BASED_CODE szFilter[] = "Worksheet Files (*.xls)|*.xls||All Files (*.*)|*.*||"; CFileDialog dlgFileOpen(FALSE,"xls",
       NULL,OFN_HIDEREADONLY | OFN_OVERWRITEPROMPT,szFilter);
int structsize=0;
DWORD dwVersion,
  dwWindowsMajorVersion,
  dwWindowsMinorVersion;
//check system version
dwVersion = GetVersion();
dwWindowsMajorVersion = (DWORD)(LOBYTE(LOWORD(dwVersion)));
dwWindowsMinorVersion = (DWORD)(HIBYTE(LOWORD(dwVersion)));
if (dwVersion<0x80000000) 
// if system Windows NT/2000
// display new dialog
structsize =88;
else
// if system is Windows 95/98 
// display old dialog
structsize =76; dlgFileOpen.m_ofn.lStructSize=structsize;
TCHAR lpstrFilename[MAX_PATH] = "";
dlgFileOpen.m_ofn.lpstrFile=lpstrFilename; if(dlgFileOpen.DoModal()!=IDOK)
{
return;
}
CString m_file=dlgFileOpen.GetPathName();
// create excel table
try{
 HRESULT hr;
 CString m_filename[3]={"e:\\templ1.xls","e:\\templ2.xls",
"e:\\templ3.xls"};
   COleVariant
                  covTrue((short)TRUE),
                  covFalse((short)FALSE),
                  covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);      int index=0;
 index=m_tab.GetCurSel();
 POSITION pos =m_list.GetFirstSelectedItemPosition();
 int position=(int)pos;
 CString id;
 id=m_id[index].GetAt(position-1);  // _Application app;
 Workbooks books;
 _Workbook book; 
 Worksheets sheets;
 _Worksheet sheet;
 Range range;
 Range cols;
 _Application app;  // app is the Excel 9 _Application object          // Start Excel and get Application object...
         if(!app.CreateDispatch("Excel.Application"))
         {
            AfxMessageBox("Couldn't start Excel.");
         }
         else
         {
            //Make Excel Visible and display a message
//app.SetVisible(TRUE);
books = app.GetWorkbooks();
//book = books.Add (covOptional);
    book=books.Open(m_filename[index], // This is a test workbook.
covOptional,
covOptional,
covOptional,
covOptional,
covOptional,
covOptional,
covOptional,
covOptional,
covOptional,
covOptional,
covOptional,
covOptional);  //Get the first sheet.
sheets =book.GetSheets();
sheet = sheets.GetItem(COleVariant((short)1));
IGetCurrDataPtr pGCD;
hr=pGCD.CreateInstance(CLSID_GetCurrData);
if(FAILED(hr))  _com_raise_error (hr);
CString strSQL;
ADOREAD::_RecordsetPtr pRS;
_variant_t vDate[32];
if(index==0) //故障
{
strSQL="SELECT * FROM TBL_REPORT_3 WHERE ID='";
strSQL+=id+"'";
pRS=pGCD->ReadData(L"APPADM",L"APPADM",L"DSN=APPADM",
           (_bstr_t)strSQL,L"",L"",
   L"",0);
if(FAILED(hr))  _com_raise_error (hr);
if(pRS->EndOfFile==VARIANT_FALSE)
{
for(int i=0;i<30;i++)
{
vDate[i]=pRS->Fields->GetItem((short)i)->Value;
}
}
CString pos[30]={"B4","F4","I4","B13","E13","H13","J13",
 "D7","E7","D8","E8","D9","E9","D10",
 "E10","D12","E12","H7","I7","H8",
 "I8","H9","I9","H10","I10","H11",
 "I11","H12","I12"};
for(int i=1;i<30;i++)
{
range = sheet.GetRange(COleVariant(pos[i-1]), COleVariant(pos[i-1]));
range.SetValue(vDate[i]);
}
VARIANT vFileName;
vFileName.vt=VT_BSTR;
vFileName.bstrVal=(_bstr_t)m_file;
book.SaveAs(vFileName, // This is a test workbook.
covOptional,
covOptional,
covOptional,
covOptional,
covOptional,
0,
covOptional,
covOptional,
covOptional,
covOptional);
app.SetVisible(TRUE); 
//完成
}else if(index==1) //明细
{
strSQL="SELECT * FROM TBL_REPORT_2 WHERE ID='";
strSQL+=id+"'";
pRS=pGCD->ReadData(L"APPADM",L"APPADM",L"DSN=APPADM",
           (_bstr_t)strSQL,L"",L"",
   L"",0);
if(FAILED(hr))  _com_raise_error (hr);
CString pos[29]={"A5","B5","C5","D5","E5","F5","G5",
 "H5","I5","J5","K5","L5","M5","N5",
 "O5","P5","Q5","R5","S5","T5",
 "U5","V5","W5","X5","Y5","Z5",
 "AA5","AB5","AC5"};
while(pRS->EndOfFile==VARIANT_FALSE)
{
for(int i=0;i<32;i++)
{
vDate[i]=pRS->Fields->GetItem((short)i)->Value;
}
range=sheet.GetRange(COleVariant("A5"), COleVariant("AC5")); 
range.Activate();
range.Insert(COleVariant((short)2));
for(i=4;i<31;i++)
{
range = sheet.GetRange(COleVariant(pos[i-4]), COleVariant(pos[i-4]));
range.SetValue(vDate[i-1]);
}
pRS->MoveNext();
}
VARIANT vFileName;
vFileName.vt=VT_BSTR;
vFileName.bstrVal=(_bstr_t)m_file;
book.SaveAs(vFileName, // This is a test workbook.
covOptional,
covOptional,
covOptional,
covOptional,
covOptional,
0,
covOptional,
covOptional,
covOptional,
covOptional);
app.SetVisible(TRUE); 
//完成
}else if(index==2)
{
}      
         }
}catch(_com_error &e)
{
AfxMessageBox(e.Description());
}
}

Answer 4

我刚好开发过这个东东
你要的话就留下EMAIL
我本来有一篇文章的
但现在不知去哪儿了
是用DAO写的，很简单的

Answer 5

VC既能读写ACCESS 又能读写 EXCEL,
这还有什么好说的?

Answer 6

何必呢？SQL Server2000有现成的数据转换工具，很简单

Answer 7

没必要这么复杂吧，我是用SELECT  ....   into  就可以直接导入的，一个ADOCOMMAND就可以搞定，
ADO直接支持文件型数据库的导来导去，也可以直接导到文本文件和HTML文件里面。