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();