MFC中导出excel表格

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
_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();