MFC 导入 和导出 Excel

---------------
导入

BOOL CMainFrame::ExcelToADO(char *szPath, char *szTable)
{
    CoInitialize(NULL);

    _Application excelApp;   
    Workbooks books;
    _Workbook book;
    Worksheets sheets;
    _Worksheet sheet;
    Range range;

    Range xlsCells,xlsCol;
    LPDISPATCH lpDisp;   
    COleVariant vResult;
    COleVariant
        covTrue((short)TRUE),
        covFalse((short)FALSE),
        covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);   

    //创建Excel 2000服务器(启动Excel)   
    if (!excelApp.CreateDispatch("Excel.Application",NULL))
    {
        AfxMessageBox("创建Excel服务失败!");
        return FALSE;
    }

    excelApp.SetVisible(FALSE);          //使Excel可见 true
    excelApp.SetUserControl(FALSE);      //允许其它用户控制Excel true

    books.AttachDispatch(excelApp.GetWorkbooks());
    try
    {
        lpDisp = books.Open(szPath,
            covOptional, covOptional, covOptional, covOptional,covOptional,
            covOptional, covOptional, covOptional, covOptional, covOptional,
            covOptional, covOptional);
    }
    catch (...)
    {
        books.Close();
        excelApp.Quit();
       
        CoUninitialize();
        return FALSE;
    }

    //Get work book
    book.AttachDispatch(lpDisp);
    //Get work sheets
    sheets.AttachDispatch(book.GetWorksheets());

    lpDisp=book.GetActiveSheet();
    sheet.AttachDispatch(lpDisp);

//    读取已经使用区域的信息，包括已经使用的行数、列数、起始行、起始列
    Range usedRange;
    usedRange.AttachDispatch(sheet.GetUsedRange());
    range.AttachDispatch(usedRange.GetRows());
    long iRowNum=range.GetCount();                  //已经使用的行数
   
    range.AttachDispatch(usedRange.GetColumns());
    long iColNum=range.GetCount();                  //已经使用的列数
   
    long iStartRow=usedRange.GetRow();              //已使用区域的起始行，从1开始
    long iStartCol=usedRange.GetColumn();            //已使用区域的起始列，从1开始


    //
    CString strValue[7],strTitle[6] = {"用户名","密码","性别","证件号","部门","职位"};
    CTime ct = CTime::GetCurrentTime();
    strValue[6] = ct.Format("%Y-%m-%d");

    //check the title
    int i,j=0;
    for (j=1; j<iColNum; j++)
    {
        range.AttachDispatch(sheet.GetCells());
        range.AttachDispatch(range.GetItem (COleVariant((long)1 ),COleVariant((long)j)).pdispVal );
        vResult =range.GetValue();
        CString  str1=vResult.bstrVal;
        if (str1 != strTitle[j-1] || j>6)
        {
            excelApp.Quit();           
            CoUninitialize();
            return FALSE;
        }
    }

    for ( i=2; i<= iRowNum; i++)
    {
        for ( j=1; j<= iColNum; j++)
        {
            range.AttachDispatch(sheet.GetCells());
            range.AttachDispatch(range.GetItem (COleVariant((long)i),COleVariant((long)j)).pdispVal );
            vResult =range.GetValue();
            CString str;
            if(vResult.vt == VT_BSTR)      //字符串
            {
                str=vResult.bstrVal;
            }
            else if (vResult.vt==VT_R8)    //8字节的数字
            {
                str.Format("%.0f",vResult.dblVal);
            }
            else if(vResult.vt==VT_DATE)    //时间格式
            {
                SYSTEMTIME st;
                VariantTimeToSystemTime(vResult.date, &st);
            }
            else if(vResult.vt==VT_EMPTY)  //单元格空的
            {
                str="";
            }
            strValue[j-1] = str;
        }
        char szSql[256] = {0};
        sprintf(szSql, "select * from userd where xm='%s'", strValue[0]);
        if (!theApp.myado.OpenSheet(szSql))
            continue;
        if (!theApp.myado.BOF())
        {
            sprintf(szSql, "用户名：%s 已存在！", strValue[0]);
            ::MessageBox(NULL, szSql, "导入提示", MB_OK|MB_ICONINFORMATION);
            theApp.myado.CloseSheet();
            continue;
        }

        theApp.myado.AddNewRecodeEx("xm,xb,zjh,bm,zw,sqsj,czy,password,haskey",strValue[0], strValue[2], strValue[3], strValue[4], strValue[5], strValue[6],theApp.m_strName,strValue[1],0);
        theApp.myado.CloseSheet();
    }

    //release
    book.Close(covOptional,COleVariant(szPath),covOptional);
    books.Close();
    excelApp.Quit();

    CoUninitialize();
    return TRUE;
}

---------------
导出

BOOL CMainFrame::ADOToExcel(char *szPath, BOOL bDel)
{
    CoInitialize(NULL);
    _Application app;   
    Workbooks books;
    _Workbook book;
    Worksheets sheets;
    _Worksheet sheet;
    Range range;
    Range xlsCells,xlsCol; 
    COleVariant vResult;
    COleVariant
        covTrue((short)TRUE),
        covFalse((short)FALSE),
        covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);   
    //*****
    //创建Excel 2000服务器(启动Excel)
    if(!app.CreateDispatch("Excel.Application"))
    {
        AfxMessageBox("无法启动Excel服务器!");
        return FALSE;
    }
    app.SetVisible(FALSE);          //使Excel可见
    app.SetUserControl(TRUE);      //允许其它用户控制Excel
    books.AttachDispatch(app.GetWorkbooks(),true); 
    book = books.Add(covOptional);
    sheets.AttachDispatch(book.GetWorksheets(),true);
    sheet.AttachDispatch(sheets.GetItem(_variant_t("sheet1")),true);
    sheet.SetName("用户信息");
    xlsCells.AttachDispatch(sheet.GetCells(),true);
    xlsCells.SetItem(_variant_t((long)1),_variant_t((long)1),_variant_t("用户名"));
    xlsCells.SetItem(_variant_t((long)1),_variant_t((long)2),_variant_t("密码"));
    xlsCells.SetItem(_variant_t((long)1),_variant_t((long)3),_variant_t("性别"));
    xlsCells.SetItem(_variant_t((long)1),_variant_t((long)4),_variant_t("证件号"));
    xlsCells.SetItem(_variant_t((long)1),_variant_t((long)5),_variant_t("部门"));
    xlsCells.SetItem(_variant_t((long)1),_variant_t((long)6),_variant_t("职位"));
   
    _ConnectionPtr   pConn(__uuidof(Connection));  
    _RecordsetPtr   pRst(__uuidof(Recordset));  
    _CommandPtr   pCmd(__uuidof(Command));   
    CString sConnect;
    pConn = theApp.myado.GetConnPtr();
    pCmd->put_ActiveConnection(_variant_t((IDispatch*)pConn));  
    pCmd->CommandText=_bstr_t("select * from userd where haskey=0");  
    pRst=pCmd->Execute(NULL,NULL,adCmdText);
    int i;
    i=2;
    while(!pRst->adoEOF)  
    {  
        xlsCells.SetItem(_variant_t((long)i),_variant_t((long)1),_variant_t((_bstr_t)pRst->GetCollect("xm")));
        xlsCells.SetItem(_variant_t((long)i),_variant_t((long)2),_variant_t((_bstr_t)pRst->GetCollect("password")));
        xlsCells.SetItem(_variant_t((long)i),_variant_t((long)3),_variant_t((_bstr_t)pRst->GetCollect("xb")));
        xlsCells.SetItem(_variant_t((long)i),_variant_t((long)4),_variant_t((_bstr_t)pRst->GetCollect("zjh")));
        xlsCells.SetItem(_variant_t((long)i),_variant_t((long)5),_variant_t((_bstr_t)pRst->GetCollect("bm")));
        xlsCells.SetItem(_variant_t((long)i),_variant_t((long)6),_variant_t((_bstr_t)pRst->GetCollect("zw")));
        pRst->MoveNext();  
        i++;
    }
    pRst->Close();
    if (bDel)
    {
        pCmd->CommandText=_bstr_t("delete from userd where haskey=0");
        pRst=pCmd->Execute(NULL,NULL,adCmdText);
    }     
    pCmd.Release();  
    pRst.Release();  
   
    //得到某列
    xlsCol.AttachDispatch(xlsCells.GetItem(_variant_t((long)2),vtMissing).pdispVal,true);    
    //设置列宽    
    xlsCol.SetColumnWidth(_variant_t((long)12));   
    //得到某列
    xlsCol.AttachDispatch(xlsCells.GetItem(_variant_t((long)4),vtMissing).pdispVal,true);    
    //设置列宽    
    xlsCol.SetColumnWidth(_variant_t((long)12));  
    //得到某列
    xlsCol.AttachDispatch(xlsCells.GetItem(_variant_t((long)7),vtMissing).pdispVal,true);    
    //设置列宽    
    xlsCol.SetColumnWidth(_variant_t((long)15));  
           
    book.SaveAs(COleVariant(szPath),covOptional,covOptional, /
        covOptional,covOptional,covOptional,0,/
        covOptional,covOptional,covOptional,covOptional); 
    book.Close (covOptional,COleVariant(szPath),covOptional);
    books.Close();     
    app.Quit(); 
    CoUninitialize(); 
    return TRUE;

}

===========================================
应当注意的是：在程序退出时记得释放资源，否则会在进程中出现excel.exe，具体退出顺序是，先关闭book，再关闭books，然后quit就可以了，不需要ReleaseDispatch。