MFC中导出excel表格

张拓1070410059

已于 2022-10-18 14:43:16 修改

阅读量2.1k

点赞数

分类专栏： MFC 文章标签： mfc c++

于 2013-07-30 12:30:22 首次发布

除特殊说明外，原创文章禁止转载。原创文章已同步到 https://www.alom.com.cn/。

本文链接：https://blog.csdn.net/defaultbyzt/article/details/9626331

版权

MFC 专栏收录该内容

77 篇文章 5 订阅

订阅专栏

MFC中导出excel表格 | 火苗999℃的博客

MFC中导出excel表格

_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 ;
    }
    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("参与人二"));
    xlsCells.SetItem(_variant_t((long)1),_variant_t((long)7),_variant_t("时间"));
    xlsCells.SetItem(_variant_t((long)1),_variant_t((long)8),_variant_t("说明"));

    CRecordsetObject<CManagelogInfoQueryView> Object;

    try
    {
        Object->m_strFilter.Format("TTNAME <> NULL order by ManageTime desc");
        Object->Open(CRecordset::dynaset);
        
        for (int nIndexInsert = 2  ;!Object->IsEOF(); Object->MoveNext())
        {
            CString szTime = "";
            szTime = CPublic::TimeToString(Object->m_ManageTime);
            CString strControl = "";
            CString str = "";
            xlsCells.SetItem(_variant_t((long)nIndexInsert),_variant_t((long)1),_variant_t(Object->m_DoorName));
            xlsCells.SetItem(_variant_t((long)nIndexInsert),_variant_t((long)2),_variant_t(Object->m_GunArkNumber));
            xlsCells.SetItem(_variant_t((long)nIndexInsert),_variant_t((long)3),_variant_t(Object->m_GunBitNumber));
            xlsCells.SetItem(_variant_t((long)nIndexInsert),_variant_t((long)4),_variant_t(strControl));
            xlsCells.SetItem(_variant_t((long)nIndexInsert),_variant_t((long)5),_variant_t(Object->m_OpenUName));
            xlsCells.SetItem(_variant_t((long)nIndexInsert),_variant_t((long)6),_variant_t(Object->m_UName));
            xlsCells.SetItem(_variant_t((long)nIndexInsert),_variant_t((long)7),_variant_t(szTime));
            xlsCells.SetItem(_variant_t((long)nIndexInsert),_variant_t((long)8),_variant_t(str));   
            ++nIndexInsert;
            
        }
        
    }
    catch (CDBException* pe) 
    {
        CPublic::LogToFile("开柜日志信息-" + pe->m_strError, _T("People.cpp"), 1602, CPublic::ErrorLog);        
        pe->Delete();
    }
    catch (...)
    {
        CPublic::LogToFile("其它异常", _T("yfsgaDlg.cpp"), 1573, CPublic::ErrorLog);
    }

    const int MAX_LINE = 8;
    int nWidth[MAX_LINE] = {20, 20, 20, 20, 20, 20 ,30, 60};
    for (int nIndex = 0; nIndex < MAX_LINE; ++nIndex)
    {
        //得到某列
        xlsCol.AttachDispatch(xlsCells.GetItem(_variant_t((long)(nIndex+1)),vtMissing).pdispVal,true);    
        //设置列宽    
        xlsCol.SetColumnWidth(_variant_t((long)nWidth[nIndex]));   
    }
    CTime currTime = CTime::GetCurrentTime();
    strTime = currTime.Format("%Y%m%d%H%M%S");
    CString szFileName = "日志"+ strTime +".xls";

    book.SaveAs(COleVariant(szSavePath + szFileName),covOptional,covOptional, \
        covOptional,covOptional,covOptional,0, \
        covOptional,covOptional,covOptional,covOptional); 
    book.Close (covOptional,COleVariant(szSavePath + szFileName),covOptional);
    books.Close();     
    app.Quit();