【MFC/C++操作Excel】Excel篇

MFC操作Excel

下面的操作基于Excel2003

一．初始化操作

1.导入类库

点击查看->建立类向导-> Add Class...\From a type Library...-> C:\Program Files\Microsoft Office\Office\EXCEL.EXE，接下来就可以看到导入的类excel.h, excel.cpp。

2.初始化COM

找到App的InitInstance()函数，在其中添加 AfxOleInit()函数的调用，如：

[cpp]view plaincopy

1. if (!AfxOleInit())
2. {
3. AfxMessageBox("注册COM出错!");
4. return FALSE;
5. }

二．我自己写的Excel操作类

ExcelOperate.h

[cpp]view plaincopy

1. #include "atlbase.h"
2. #include "excel.h"
3. using namespace myexcel;
4. class CExcelOperate
5. {
6. private:
7. myexcel::_Application m_ecApp;
8. Workbooks m_ecBooks;
9. _Workbook m_ecBook;
10. Worksheets m_ecSheets;
11. _Worksheet m_ecSheet;
12. myexcel::Range m_ecRange;
13. VARIANT ret;//保存单元格的值
14. public:
15. CExcelOperate();
16. virtual ~CExcelOperate();
17. public:
18. //操作
19. //**********************创建新EXCEL*******************************************
20. BOOL CreateApp();
21. BOOL CreateWorkbooks(); //创建一个新的EXCEL工作簿集合
22. BOOL CreateWorkbook(); //创建一个新的EXCEL工作簿
23. BOOL CreateWorksheets(); //创建一个新的EXCEL工作表集合
24. BOOL CreateWorksheet(short index); //创建一个新的EXCEL工作表
25. BOOL CreateSheet(short index);
26. BOOL Create(short index = 1); //创建新的EXCEL应用程序并创建一个新工作簿和工作表
27. void ShowApp(); //显示EXCEL文档
28. void HideApp(); //隐藏EXCEL文档
29. //**********************打开文档*********************************************
30. BOOL OpenWorkbook(CString fileName, short index = 1);
31. BOOL Open(CString fileName); //创建新的EXCEL应用程序并打开一个已经存在的文档。
32. BOOL SetActiveWorkbook(short i); //设置当前激活的文档。
33. //**********************保存文档*********************************************
34. BOOL SaveWorkbook(); //Excel是以打开形式，保存。
35. BOOL SaveWorkbookAs(CString fileName);//Excel以创建形式，保存。
36. BOOL CloseWorkbook();
37. void CloseApp();
38. //**********************读信息********************************
39. BOOL GetRangeAndValue(CString begin, CString end);//得到begin到end的Range并将之间的值设置到ret中
40. void GetRowsAndCols(long &rows, long &cols);//得到ret的行，列数
41. BOOL GetTheValue(int rows, int cols, CString &dest);//返回第rows，cols列的值，注意只返回文本类型的，到dest中
42. BOOL SetTextFormat(CString &beginS, CString &endS);//将beginS到endS设置为文本格式(数字的还要用下面的方法再转一次)
43. BOOL SetRowToTextFormat(CString &beginS, CString &endS);//将beginS到endS(包括数字类型)设置为文本格式
44. };

ExcelOperate.cpp

[cpp]view plaincopy

1. CExcelOperate::CExcelOperate()
2. {
3. }
4. CExcelOperate::~CExcelOperate()
5. {
6. }
7. BOOL CExcelOperate::CreateApp()
8. {
9. //if (FALSE == m_wdApp.CreateDispatch("Word.Application"))
10. COleException pe;
11. if (!m_ecApp.CreateDispatch(_T("Excel.Application"), &pe))
12. {
13. AfxMessageBox("Application创建失败，请确保安装了word 2000或以上版本!", MB_OK|MB_ICONWARNING);
14. pe.ReportError();
15. throw &pe;
16. return FALSE;
17. }
18. return TRUE;
19. }
20. BOOL CExcelOperate::CreateWorkbooks() //创建一个新的EXCEL工作簿集合
21. {
22. if (FALSE == CreateApp())
23. {
24. return FALSE;
25. }
26. m_ecBooks = m_ecApp.GetWorkbooks();
27. if (!m_ecBooks.m_lpDispatch)
28. {
29. AfxMessageBox("WorkBooks创建失败!", MB_OK|MB_ICONWARNING);
30. return FALSE;
31. }
32. return TRUE;
33. }
34. BOOL CExcelOperate::CreateWorkbook() //创建一个新的EXCEL工作簿
35. {
36. if(!m_ecBooks.m_lpDispatch)
37. {
38. AfxMessageBox("WorkBooks为空!", MB_OK|MB_ICONWARNING);
39. return FALSE;
40. }
41. COleVariant vOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
42. m_ecBook = m_ecBooks.Add(vOptional);
43. if(!m_ecBook.m_lpDispatch)
44. {
45. AfxMessageBox("WorkBook为空!", MB_OK|MB_ICONWARNING);
46. return FALSE;
47. }
48. /*
49. //得到document变量
50. m_wdDoc = m_wdApp.GetActiveDocument();
51. if (!m_wdDoc.m_lpDispatch)
52. {
53. AfxMessageBox("Document获取失败!", MB_OK|MB_ICONWARNING);
54. return FALSE;
55. }
56. //得到selection变量
57. m_wdSel = m_wdApp.GetSelection();
58. if (!m_wdSel.m_lpDispatch)
59. {
60. AfxMessageBox("Select获取失败!", MB_OK|MB_ICONWARNING);
61. return FALSE;
62. }
63. //得到Range变量
64. m_wdRange = m_wdDoc.Range(vOptional,vOptional);
65. if(!m_wdRange.m_lpDispatch)
66. {
67. AfxMessageBox("Range获取失败!", MB_OK|MB_ICONWARNING);
68. return FALSE;
69. }
70. */
71. return TRUE;
72. }
73. BOOL CExcelOperate::CreateWorksheets() //创建一个新的EXCEL工作表集合
74. {
75. if(!m_ecBook.m_lpDispatch)
76. {
77. AfxMessageBox("WorkBook为空!", MB_OK|MB_ICONWARNING);
78. return FALSE;
79. }
80. m_ecSheets = m_ecBook.GetSheets();
81. if(!m_ecSheets.m_lpDispatch)
82. {
83. AfxMessageBox("WorkSheets为空!", MB_OK|MB_ICONWARNING);
84. return FALSE;
85. }
86. return TRUE;
87. }
88. BOOL CExcelOperate::CreateWorksheet(short index) //创建一个新的EXCEL工作表
89. {
90. if(!m_ecSheets.m_lpDispatch)
91. {
92. AfxMessageBox("WorkSheets为空!", MB_OK|MB_ICONWARNING);
93. return FALSE;
94. }
95. m_ecSheet = m_ecSheets.GetItem(COleVariant(index));
96. if(!m_ecSheet.m_lpDispatch)
97. {
98. AfxMessageBox("WorkSheet为空!", MB_OK|MB_ICONWARNING);
99. return FALSE;
100. }
101. return TRUE;
102. }
103. BOOL CExcelOperate::CreateSheet(short index)
104. {
105. if(CreateWorksheets() == FALSE)
106. {
107. return FALSE;
108. }
109. if(CreateWorksheet(index) == FALSE)
110. {
111. return FALSE;
112. }
113. return TRUE;
114. }
115. BOOL CExcelOperate::Create(short index) //创建新的EXCEL应用程序并创建一个新工作簿和工作表
116. {
117. if(CreateWorkbooks() == FALSE)
118. {
119. return FALSE;
120. }
121. if(CreateWorkbook() == FALSE)
122. {
123. return FALSE;
124. }
125. if(CreateSheet(index) == FALSE)
126. {
127. return FALSE;
128. }
129. return TRUE;
130. }
131. void CExcelOperate::ShowApp() //显示WORD文档
132. {
133. m_ecApp.SetVisible(TRUE);
134. }
135. void CExcelOperate::HideApp() //隐藏word文档
136. {
137. m_ecApp.SetVisible(FALSE);
138. }
139. //**********************打开文档*********************************************
140. BOOL CExcelOperate::OpenWorkbook(CString fileName, short index)
141. {
142. if(!m_ecBooks.m_lpDispatch)
143. {
144. AfxMessageBox("WorkSheets为空!", MB_OK|MB_ICONWARNING);
145. return FALSE;
146. }
147. //COleVariant vFileName(_T(fileName));
148. COleVariant VOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
149. m_ecBook = m_ecBooks.Open(fileName,VOptional, VOptional, VOptional, VOptional,VOptional, VOptional, VOptional, VOptional,VOptional, VOptional, VOptional, VOptional, VOptional, VOptional);
150. if(!m_ecBook.m_lpDispatch)
151. {
152. AfxMessageBox("WorkSheet获取失败!", MB_OK|MB_ICONWARNING);
153. return FALSE;
154. }
155. if(CreateSheet(index) == FALSE)
156. {
157. return FALSE;
158. }
159. return TRUE;
160. }
161. BOOL CExcelOperate::Open(CString fileName) //创建新的EXCEL应用程序并打开一个已经存在的文档。
162. {
163. if(CreateWorkbooks() == FALSE)
164. {
165. return FALSE;
166. }
167. return OpenWorkbook(fileName);
168. }
169. /*BOOL CExcelOperate::SetActiveWorkbook(short i) //设置当前激活的文档。
170. {
171. }*/
172. //**********************保存文档*********************************************
173. BOOL CExcelOperate::SaveWorkbook() //文档是以打开形式，保存。
174. {
175. if (!m_ecBook.m_lpDispatch)
176. {
177. AfxMessageBox("Book获取失败!", MB_OK|MB_ICONWARNING);
178. return FALSE;
179. }
180. m_ecBook.Save();
181. return TRUE;
182. }
183. BOOL CExcelOperate::SaveWorkbookAs(CString fileName)//文档以创建形式，保存。
184. {
185. if (!m_ecBook.m_lpDispatch)
186. {
187. AfxMessageBox("Book获取失败!", MB_OK|MB_ICONWARNING);
188. return FALSE;
189. }
190. COleVariant vTrue((short)TRUE),
191. vFalse((short)FALSE),
192. vOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
193. COleVariant vFileName(_T(fileName));
194. m_ecBook.SaveAs(
195. vFileName, //VARIANT* FileName
196. vOptional, //VARIANT* FileFormat
197. vOptional, //VARIANT* LockComments
198. vOptional, //VARIANT* Password
199. vOptional, //VARIANT* AddToRecentFiles
200. vOptional, //VARIANT* WritePassword
201. 0, //VARIANT* ReadOnlyRecommended
202. vOptional, //VARIANT* EmbedTrueTypeFonts
203. vOptional, //VARIANT* SaveNativePictureFormat
204. vOptional, //VARIANT* SaveFormsData
205. vOptional, //VARIANT* SaveAsAOCELetter
206. vOptional //VARIANT* ReadOnlyRecommended
207. /* vOptional, //VARIANT* EmbedTrueTypeFonts
208. vOptional, //VARIANT* SaveNativePictureFormat
209. vOptional, //VARIANT* SaveFormsData
210. vOptional //VARIANT* SaveAsAOCELetter*/
211. );
212. return TRUE;
213. }
214. BOOL CExcelOperate::CloseWorkbook()
215. {
216. COleVariant vTrue((short)TRUE),
217. vFalse((short)FALSE),
218. vOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
219. m_ecBook.Close(vFalse, // SaveChanges.
220. vTrue, // OriginalFormat.
221. vFalse // RouteDocument.
222. );
223. m_ecBook = m_ecApp.GetActiveWorkbook();
224. if(!m_ecBook.m_lpDispatch)
225. {
226. AfxMessageBox("Book获取失败!", MB_OK|MB_ICONWARNING);
227. return FALSE;
228. }
229. if(CreateSheet(1) == FALSE)
230. {
231. return FALSE;
232. }
233. return TRUE;
234. }
235. void CExcelOperate::CloseApp()
236. {
237. SaveWorkbook();
238. COleVariant vTrue((short)TRUE),
239. vFalse((short)FALSE),
240. vOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
241. //m_ecDoc.Save();
242. m_ecApp.Quit();
243. if(m_ecRange.m_lpDispatch)
244. m_ecRange.ReleaseDispatch();
245. if(m_ecSheet.m_lpDispatch)
246. m_ecSheet.ReleaseDispatch();
247. if(m_ecSheets.m_lpDispatch)
248. m_ecSheets.ReleaseDispatch();
249. if(m_ecBook.m_lpDispatch)
250. m_ecBook.ReleaseDispatch();
251. if(m_ecBooks.m_lpDispatch)
252. m_ecBooks.ReleaseDispatch();
253. if(m_ecApp.m_lpDispatch)
254. m_ecApp.ReleaseDispatch();
255. }
256. BOOL CExcelOperate::GetRangeAndValue(CString begin, CString end)
257. {
258. if(!m_ecSheet.m_lpDispatch)
259. {
260. AfxMessageBox("Sheet获取失败!", MB_OK|MB_ICONWARNING);
261. return FALSE;
262. }
263. m_ecRange = m_ecSheet.GetRange(COleVariant(begin), COleVariant(end));
264. if(!m_ecRange.m_lpDispatch)
265. {
266. AfxMessageBox("Range获取失败!", MB_OK|MB_ICONWARNING);
267. return FALSE;
268. }
269. ret = m_ecRange.GetValue2();//得到表格中的值
270. return TRUE;
271. }
272. void CExcelOperate::GetRowsAndCols(long &rows, long &cols)
273. {
274. COleSafeArray sa(ret);
275. sa.GetUBound(1,&rows);
276. sa.GetUBound(2,&cols);
277. }
278. //只返回CString类型的，其他类型概视为错误
279. BOOL CExcelOperate::GetTheValue(int rows, int cols, CString &dest)
280. {
281. long rRows, rCols;
282. long index[2];
283. VARIANT val;
284. COleSafeArray sa(ret);
285. sa.GetUBound(1,&rRows);
286. sa.GetUBound(2,&rCols);
287. if(rows < 1 || cols < 1 || rRows < rows || rCols < cols)
288. {
289. AfxMessageBox("出错点1");
290. return FALSE;
291. }
292. index[0]=rows;
293. index[1]=cols;
294. sa.GetElement(index,&val);
295. if(val.vt != VT_BSTR)
296. {
297. CString str;
298. str.Format("出错点2, %d",val.vt);
299. AfxMessageBox(str);
300. return FALSE;
301. }
302. dest = val.bstrVal;
303. return TRUE;
304. }
305. //将beginS到endS之间设置为文本格式
306. BOOL CExcelOperate::SetTextFormat(CString &beginS, CString &endS)
307. {
308. if(GetRangeAndValue(beginS, endS))
309. {
310. m_ecRange.Select();
311. m_ecRange.SetNumberFormatLocal(COleVariant("@"));
312. return TRUE;
313. }
314. return FALSE;
315. }
316. //将beginS到endS之间(必须是一列)设置为真正的文本格式
317. BOOL CExcelOperate::SetRowToTextFormat(CString &beginS, CString &endS)
318. {
319. if(GetRangeAndValue(beginS, endS))
320. {
321. m_ecRange.Select();
322. Range m_tempRange = m_ecSheet.GetRange(COleVariant(beginS), COleVariant(beginS));
323. if(!m_tempRange.m_lpDispatch) return FALSE;
324. COleVariant vTrue((short)TRUE),
325. vFalse((short)FALSE);
326. //int tempArray[2] = {1, 2};
327. COleSafeArray saRet;
328. DWORD numElements = {2};
329. saRet.Create(VT_I4, 1, &numElements);
330. long index = 0;
331. int val = 1;
332. saRet.PutElement(&index, &val);
333. index++;
334. val = 2;
335. saRet.PutElement(&index, &val);
336. //m_tempRange.GetItem(COleVariant((short)5),COleVariant("A"));
337. m_ecRange.TextToColumns(m_tempRange.GetItem(COleVariant((short)1),COleVariant((short)1)), 1, 1, vFalse, vTrue, vFalse, vFalse, vFalse, vFalse, vFalse, saRet, vFalse, vFalse, vTrue);
338. m_tempRange.ReleaseDispatch();
339. return TRUE;
340. }
341. return FALSE;
342. }