使用MFC自动化操作Excel文档

最新推荐文章于 2023-06-14 14:46:03 发布

最新推荐文章于 2023-06-14 14:46:03 发布 · 161 阅读

文章标签：

#c/c++ #数据库

本文介绍如何在MFC项目中使用COM接口自动化操作Excel文档，包括创建接口类、封装Excel常用接口以及使用示例代码。重点在于解决在不同版本的Office中找到合适的类型库并正确配置MFC项目。

使用MFC自动化操作Excel文档

项目中要用到MFC操作Excel文件，在网上搜的结果，有把Excel当数据库处理的，也有直接操作Excel的，最终在MSDN上看到的结果，测试后觉得好用些。

使用基本步骤：

1、创建一个支持MFC的C++项目

2、加入一个从Excel的typelib派生的MFC类，下图为typelib列表：

   应用程序                           类型库
   --------------------------------------------------

Microsoft Access 97                   Msacc8.olb
Microsoft Jet Database 3.5         DAO350.dll
Microsoft Binder 97                   Msbdr8.olb
Microsoft Excel 97                     Excel8.olb
Microsoft Graph 97                    Graph8.olb
Microsoft Office 97                    Mso97.dll
Microsoft Outlook 97                 Msoutl97.olb
Microsoft PowerPoint 97             Msppt8.olb

Microsoft Word 97                     Msword8.olb
Microsoft Access 2000                Msacc9.olb
Microsoft Jet Database 3.51        DAO360.dll
Microsoft Binder 2000                 Msbdr9.olb
Microsoft Excel 2000                  Excel9.olb
Microsoft Graph 2000                 Graph9.olb
Microsoft Office 2000                 Mso9.dll
Microsoft Outlook 2000               Msoutl9.olb
Microsoft PowerPoint 2000          Msppt9.olb
Microsoft Word 2000                   Msword9.olb 

Microsoft Access 2002                Msacc.olb
Microsoft Excel 2002                  Excel.exe
Microsoft Graph 2002                 Graph.exe 
Microsoft Office 2002                 MSO.dll
Microsoft Outlook 2002               MSOutl.olb
Microsoft PowerPoint 2002          MSPpt.olb
Microsoft Word 2002                  MSWord.olb

Microsoft Office Access 2003        Msacc.olb
Microsoft Office Excel 2003          Excel.exe
Microsoft Graph 2003                  Graph.exe 
Microsoft Office 2003                  MSO.dll
Microsoft Office Outlook 2003       MSOutl.olb
Microsoft Office PowerPoint 2003   MSPpt.olb
Microsoft Office Word 2003           MSWord.olb

注意：这些类型库的默认位置是 C:/Program Files/Microsoft Office/Office（在 Office 2002 中，相应的路径为 C:/.../Office10；在 Office 2003 中，相应的路径为 C:/.../Office11），Dao350.dll 或 Dao360.dll 以及 Microsoft Office 10 (MSO.dll) 除外。Dao350.dll/Dao360.dll 的默认位置是 C:/Program Files/Common Files/Microsoft Shared/Dao。对于 Office 2002，MSO.dll 的默认位置是 C:/Program Files/Common Files/Microsoft Shared/Office10；对于 Office 2003，其默认位置是 C:/Program Files/Common Files/Microsoft Shared/Office11。

3、选择需要的Excel自动化接口，常用的有_Application/_Workbook/_Worksheet/Workbooks/Worksheet/Range等，接口类为Excel.h，Excel.cpp

（上述三步主要用来生产MFC的Excel接口类，测试结果，VC2005生成的Excel接口不好用，报了一堆的error，重定义什么的。参考网上的意见，用VC6生成了一个Excel接口，然后在VC2005直接使用这个接口了）

4、修改接口类的头文件，定义一个命名空间Excel，将接口类定义及实现包含起来，便以使用

5、在使用Excel接口的地方，引用Excel.h

使用注意事项：

1、在MFC项目中，如果没有启用COM服务库，启用COM服务库，示例如下：

BOOL CAutoProjectApp::InitInstance() { if(!AfxOleInit()) // Your addition starts here { AfxMessageBox("Could not initialize COM dll"); return FALSE; } // End of your addition AfxEnableControlContainer(); . . . }

2、加入必要的头文件，如<afxdisp.h>

3、Excel接口中有许多默认的Optional参数，对应MFC中的定义为：COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);

4、部分Excel自动化接口用到的枚举常量，在MFC导出类中并没有，需要用的时候，可以查阅Excel VAB Reference，直接使用这些枚举常量的对应值。

Excel使用封装类：

为了便以使用，对Excel导出类做了简单的封装，代码如下：

//ExcelUtil.h #include "stdafx.h" #include <io.h> #include "../ExcelWrapper/excel.h" using namespace Excel; namespace Excel { class ExcelUtil { private: CString out_file_name; public: CString GetOutFileName() { return out_file_name; }; void SetOutFileName(CString new_file_name) { if (new_file_name.GetLength()>0) out_file_name = new_file_name; } private: _Application m_excelApp; Workbooks m_excelBooks; _Workbook m_excelBook; Worksheets m_excelSheets; _Worksheet m_excelSheet; Range m_excelRange; public: void CreateExcel() { COleException pError; if (!m_excelApp.CreateDispatch("Excel.Application", &pError)) { pError.ReportError(); return; } else { m_excelBooks = m_excelApp.GetWorkbooks(); m_excelBook = m_excelBooks.Add(COleVariant((short)TRUE)); m_excelSheet = m_excelBook.GetActiveSheet(); } } void OpenExcel(CString fileName) { COleException pError; if (!m_excelApp.CreateDispatch("Excel.Application", &pError)) { pError.ReportError(); return; } else { COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR); m_excelBooks = m_excelApp.GetWorkbooks(); m_excelBook = m_excelBooks.Open(fileName,covOptional, covOptional, covOptional, covOptional, covOptional, covOptional, covOptional, covOptional, covOptional, covOptional, covOptional, covOptional, covOptional, covOptional); m_excelSheet = m_excelBook.GetActiveSheet(); out_file_name = fileName; } } bool SaveExcel() { if (out_file_name.GetLength()==0) { AfxMessageBox("ExcelÂ·¾¶Î´Ö¸¶¨,ÎÞ·¨±£´æ"); return false; } else { TRY { if (_access(out_file_name, 0) == 0) remove(out_file_name); COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR); m_excelBook.SaveAs(COleVariant(out_file_name), covOptional, covOptional, covOptional, covOptional, covOptional, 0,covOptional, covOptional, covOptional, covOptional, covOptional); } CATCH (CException, e) { return false; } END_CATCH } return false; } void CloseExcel() { COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR); m_excelBook.Close(COleVariant((short)FALSE), covOptional, covOptional); m_excelBooks.Close(); m_excelApp.Quit(); } // <summary> // È¡µÃÌØ¶¨µÄWorksheet //</summary> // <param name="SheetName">worksheetµÄÃû×Ö</param> //<returns></returns> _Worksheet GetSheet(CString SheetName) { bool has_the_sheet = false; Worksheets sheets = m_excelBook.GetWorksheets(); for (int si=1; si<=sheets.GetCount(); si++) { _Worksheet sheet = sheets.GetItem(COleVariant((long)si)); if (sheet.GetName().Compare(SheetName) == 0) { has_the_sheet = true; break; } } if (!has_the_sheet) { AfxMessageBox("SheetÃû×Ö²»¶Ô£¬Çë¼ì²é"); return NULL; } TRY { m_excelSheet = sheets.GetItem(COleVariant(SheetName)); } CATCH (CException, e) { return NULL; } END_CATCH return m_excelSheet; } /// <summary> /// È¡µÃÌØ¶¨µÄWorksheet /// </summary> /// <param name="index">worksheetµÄË÷Òý£¬´Ó1¿ªÊ¼</param> /// <returns></returns> _Worksheet GetSheet(int index) { Worksheets sheets = m_excelBook.GetWorksheets(); int sheet_num = sheets.GetCount(); if (index <= 0 || index > sheet_num) { char buf[BUFSIZ] = ""; sprintf_s(buf, "Ë÷Òý±ØÐë´óÓÚ0ÇÒÐ¡ÓÚµÈÓÚ%d", sheet_num); AfxMessageBox(buf); return NULL; } TRY { m_excelSheet = sheets.GetItem(COleVariant((long)(index))); } CATCH (CException, e) { return NULL; } END_CATCH return m_excelSheet; } /// <summary> /// Ìí¼ÓÌØ¶¨µÄWorksheet /// </summary> /// <param name="SheetName">ÐÂÌí¼ÓµÄworksheetµÄÃû×Ö</param> /// <returns>³É¹¦·µ»Ø´´½¨µÄworksheet£¬·ñÔò¿ØÖÆÌ¨´òÓ¡ÏàÓ¦µÄ³ö´íÐÅÏ¢</returns> _Worksheet AddSheet(CString SheetName) { if( SheetName.Trim().GetLength() == 0 ) { AfxMessageBox("sheetÃû×Ö·Ç·¨"); return NULL; } bool has_the_sheet = false; Worksheets sheets = m_excelBook.GetWorksheets(); for (int si=1; si<=sheets.GetCount(); si++) { _Worksheet sheet = sheets.GetItem(COleVariant((long)si)); if (sheet.GetName().Compare(SheetName) == 0) { has_the_sheet = true; break; } } if( has_the_sheet ) { AfxMessageBox("excelÎÄ¼þÖÐÒÑÓÐ¸Ãsheet"); return NULL; } _Worksheet new_sheet; TRY { COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR); new_sheet = sheets.Add(covOptional, covOptional, COleVariant((short)1), covOptional); new_sheet.SetName(SheetName); } CATCH (CException, e) { return NULL; } END_CATCH return new_sheet; } /// <summary> /// È¡µÃsheetµÄÊýÁ¿ /// </summary> /// <returns>sheetµÄÊýÁ¿</returns> int GetSheetNum() { Worksheets sheets = m_excelBook.GetWorksheets(); return sheets.GetCount(); } //²Ù×÷µ¥Ôª¸ñ/ºÏ²¢µ¥Ôª¸ñ /// <summary> /// ·µ»ØÌØ¶¨µÄµ¥Ôª¸ñ /// </summary> /// <param name="ColNum">ÁÐºÅ,´Ó1¿ªÊ¼,²»´óÓÚ255</param> /// <param name="RowNum">ÐÐºÅ,´Ó1¿ªÊ¼,²»´óÓÚ255</param> /// <returns></returns> CString GetCell(int ColNum, int RowNum) { char buf[BUFSIZ] = ""; //µ¥Ôª¸ñÁÐºÅÊý×é CString m_colString[] = { "", "A","B","C","D","E", "F","G","H","I","J", "K","L","M","N","O", "P","Q","R","S","T", "U","V","W","X","Y", "Z" }; int row = RowNum; if (ColNum <= 0 || ColNum > 255) { AfxMessageBox("ÐÐºÅ´íÎó"); return ""; } if (ColNum <= 26) { sprintf_s(buf, "%s%d", m_colString[ColNum], RowNum); return CString(buf); } else { int i0 = 0, i1 = 0; i0 = (int)(ColNum / 26); i1 = ColNum % 26; if (i1 != 0) { sprintf_s(buf, "%s%s%d", m_colString[i0], m_colString[i1], row); return CString(buf); } else //Èç¹ûColNum = 52¡¢78¡¢104 { sprintf_s(buf, "%s%s%d", m_colString[i0-1], m_colString[i1+26], row); return CString(buf); } } } //È¡µÃÒ»¸öµ¥Ôª¸ñ void SetRange(int ColNum, int RowNum) { COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR); m_excelRange = m_excelSheet.GetRange(COleVariant(GetCell(ColNum, RowNum)), covOptional); } //È¡µÃÒ»¸öµ¥Ôª¸ñÇøÓò void SetRange(int StartColNum, int StartRowNum, int EndColNum, int EndRowNum) { m_excelRange = m_excelSheet.GetRange(COleVariant(GetCell(StartColNum, StartRowNum)), COleVariant(GetCell(EndColNum, EndRowNum))); } //ºÏ²¢Ò»¸öµ¥Ôª¸ñÇøÓò void Merge(int startRowIndex, int startColIndex, int endRowIndex, int endColIndex) { SetRange(startColIndex, startRowIndex, endColIndex, endRowIndex); m_excelRange.Merge(COleVariant((short)FALSE)); } //µ¥Ôª¸ñ¸³Öµ/µ¥Ôª¸ñÇøÓò¸³Öµ void SetCellValue(CString value) { if (m_excelRange == NULL) { AfxMessageBox("Ã»ÓÐÉè¶¨µ¥Ôª¸ñ"); return; } m_excelRange.SetValue2(COleVariant(value)); } void SetCellValue(int row, int col, CString value) { SetRange(col, row); m_excelRange.SetNumberFormatLocal(COleVariant("@")); m_excelRange.SetValue2(COleVariant(value)); } void SetCellStringFormat() { m_excelRange.SetNumberFormatLocal(COleVariant("@")); } void SetCellValue( int startRow,int startCol, int endRow, int endCol, CString value ) { Merge(startRow, startCol, endRow, endCol); m_excelRange.SetNumberFormatLocal(COleVariant("@")); m_excelRange.SetValue2(COleVariant(value)); } CString GetCellValue( int row, int col ) { SetRange(col, row); return (CString)m_excelRange.GetText(); } //ÉèÖÃµ¥Ôª¸ñÑùÊ½ void SetBoldFontAndSize( int size ) { Font font = m_excelRange.GetFont(); font.SetBold(COleVariant((short)TRUE)); font.SetSize(COleVariant((short)size)); } //ÉèÖÃ±¨±í±í¸ñÎª×îÊÊÓ¦¿í¶È //ÉèÖÃ±¨±í±í¸ñÎª×îÊÊÓ¦¿í¶È void SetAutoFitColumns( int startRow, int startCol, int endRow, int endCol ) { SetRange(startCol, startRow, endCol, endRow); Range cols = m_excelRange.GetEntireColumn(); cols.AutoFit(); } void SetCenterAlign() { m_excelRange.SetHorizontalAlignment(COleVariant((short)0)); } void SetBorder() { Borders borders = m_excelRange.GetBorders(); borders.SetLineStyle(COleVariant((short)1)); Border border = borders.GetItem(7); border.SetWeight(COleVariant((short)4)); border = borders.GetItem(8); border.SetWeight(COleVariant((short)4)); border = borders.GetItem(10); border.SetWeight(COleVariant((short)4)); border = borders.GetItem(9); border.SetWeight(COleVariant((short)4)); } }; }

（代码应用的excel.h为MFC导出类）

更多示例代码，参考：

http://support.microsoft.com/kb/178783/zh-cn

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q179706&ID=KB;EN-US;Q179706

http://www.usewide.com/forums/topics.aspx?ID=86

内容参考来源于MSDN