概要
网上VC对Excel的操作资料虽然很多,但是很乱,这里做下总结,以后要用了可以参考,免得放在硬盘里时间一长找不到了。本文最后列出了主要的参考源,进一步信息可以从这些网站中获得。
代码段均在WinXP+SP3 Excel2007 VS2008+SP1环境下调试通过。
这里对Excel OLE对象的调用方式采用了MFC的type Lib。
正文
准备工作
通过type Lib方式引入Excel OLE封装源码文件后,对要用到的头文件做修改。注释import语句,添加Excel对象引用代码(参考资源[2]),注释掉冲突的代码。
要引用哪对象就添加哪些对象的头文件,一般下面这些头文件是会被用到的。
#include "CApplication.h"
#include "CWorkbooks.h"
#include "CWorkbook.h"
#include "CWorksheets.h"
#include "CWorksheet.h"
打开Excel App对象
CApplicationapp;
if( !app.CreateDispatch(_T("Excel.Application")))
{
return false;
}
新建Excel文件和Sheet
CWorkbooks books;
CWorkbook book;
CWorksheets sheets;
CWorksheet sheet;
books=app.get_Workbooks();
book=books.Add(covOptional);
sheets=book.get_Sheets();
sheet=sheets.get_Item(COleVariant((short)nSheetNumber));
一般nSheetNumber取值为1,即下面的代码将对第一张Sheet进行操作。
显示Excel报表(控件)
app.put_Visible(TRUE);
app.put_UserControl(TRUE);
打开已存在的Excel文件和Sheet
books= app.get_Workbooks();
book = books.Open(filename.GetBuffer(MAX_PATH),covOptional,covOptional,
covOptional,covOptional,covOptional,covOptional,
covOptional,covOptional,covOptional,covOptional,
covOptional,covOptional,covOptional,covOptional);
sheets =book.get_Worksheets();
sheet=sheets.get_Item(COleVariant((short)nSheetNumber));
读取Excel文件中单元格的内容
CRange range;
range = sheet.get_Range(COleVariant(startRange.GetBuffer(MAX_PATH)),
COleVariant(endRange.GetBuffer(MAX_PATH)));
COleVariantcov = range.get_Value2();
CString str = (LPCWSTR)cov.bstrVal;
其中startRange和endRange为CString实例,是单元格的位置,例如“A1”、“A2”、“B1”等。
写Excel文件中单元格的内容
CRange range;
range=sheet.get_Range(COleVariant(startRange.GetBuffer(MAX_PATH)),COleVariant(endRange.GetBuffer(MAX_PATH)));
range.put_Value2(COleVariant(strValue.GetBuffer(MAX_PATH)));
//font=range.get_Font();
//font.put_Bold(COleVariant((short)TRUE));
//range.put_Formula(COleVariant(_T("=RAND()")));
//range.put_NumberFormat(COleVariant(_T("$0.00")));
//CRange cols;
//cols=range.get_EntireColumn();
//cols.AutoFit();
这里设置了字符串类型,应该还能设置其它类型参数,比如说整型。解开上面代码中的注释,可以尝试下其它功能。参考资源[1]
释放资源
books.Close();
app.Quit();
books.ReleaseDispatch();
app.ReleaseDispatch();
上面这段代码建议有,因为读取Excel文件时,如果没有上面这段代码文件可能会被锁定。
参考资源
[1]《VS80开启Excel问题》
http://www.programmer-club.com.tw/ShowSameTitleN/vc/32776.html
[2]《vc 如何操作excel2003(注意一定是2003)编程》
http://zhidao.baidu.com/question/40379986.html?si=1
[3]《excel,合并了单元格后该如何定位?》
http://zhidao.baidu.com/question/112923672.html?si=3
-----------------------------------------------------------------------------附------------------
Excel在公司用的很多,而这个东西我用的不是很好,就想用程序来处理,遇到很多错误。这几天研究了下OLE操作Excel。
环境:VS2008
SP1+Excel 2007
加入OLE Type
Library
随便建立一个MFC程序,选添加类,Typelib中的MFC类,出来一个对话,可用类型库选“Microsoft Excel 12.0
Object Library”
一般来说,添加下边这些类就够了,当然也可以全部添加
CApplication
CWorkbook
CWorkbooks
CWorksheet
CRange
CWorksheets
CPicture
CPictures
CBorder
CBorders
CFont0
Cnterior
包含头文件的时候,把CApplication.h放到最前面
一个操作的例子
![](http://www.cppblog.com/Images/OutliningIndicators/None.gif)
_variant_t(x)
void CExcelTest2Dlg::OnBnClickedButton1()
![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/InBlock.gif)
![](http://www.cppblog.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
接下来开始编译,哇,一大堆错误...
e:\myprograms3\exceltest2\exceltest2\debug\excel.tlh(1461)
: error C2371: “FontPtr”: 重定义;不同的基类型 等等
把CApplication.h里的#import
"C:\\Program Files\\Microsoft Office\\Office12\\EXCEL.EXE"
no_namespace
改为#import "C:\Program Files\Microsoft Office\OFFICE12\excel.exe"
exclude("IFont", "IPicture") rename("RGB", "ignorethis"), rename("DialogBox",
"ignorethis"), rename("VBE", "GREATWSVBE")
rebuild
all
e:\myprograms3\exceltest2\exceltest2\debug\excel.tlh(2036) : error C2504:
“_IMsoDispObj”: 未定义基类
应该是接口没定义,mso.dll这个接口,在CApplication.h最上面加入#import
"C:\Program Files\Common Files\Microsoft Shared\OFFICE12\mso.dll"
rebuild
all 错误少多了
还有VBE之类的错误,改法:
加入
#import "C:\Program Files\Common
Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB"
raw_interfaces_only, rename("Reference", "ignorethis"), rename("VBE",
"GREATWSVBE")
注意自己机器的路径!
剩下还有一些rename
的警告,可以在相关import后面几上rename("XXX","XXXXX")