摘要:使用Microsoft Office所带的控件来实现代码操作excel表格。
最近由于工作需要,不得不操作Excel,包括基本的数据输入输出、字体设置、颜色设置、单元格的合并、填充色的设置,当然还包括图表的生成以及渐近线的操作等。折腾了许久,项目终于完成了^_^。现在将学习操作Excel的点点滴滴记录下来和大家一同分享,也作为自己的一次总结。
1、Excel的对象模型(有人称其为层次结构)
打开一个Excel工作表,点击“工具”->“宏”->“Visual Basic 编辑器”选项打开VB的编辑器,打开帮助文档,里面“Microsoft Excel Visual Basic 参考”下的“Microsoft Excel 对象模型”展示了完整的Excel的层次结构,是不是有点类似于MFC的继承图表啊?利用帮助文档我们可以找到一些需要的知识,下面介绍一些类:
_Application:表示整个的Excel应用程序,包含一个工作簿集合
Workbooks:工作簿集合,包含N个工作簿(Workbook)
_Workbook:工作簿,包含一个工作表(sheets)集合
Worksheets:工作表集合,包含N个工作表
_Worksheet:工作表,也就是我们在Excel中看到的Sheet1、Sheet2、Sheet3,它是我们操作Excel的基本单位
Range:这是单元格的集合,我们知道Excel是由一个个的单元格组成的,通过Range来操作单元格
Font:用于设置单元格的字体、颜色、字号、粗体设置
Interior:设置底色
Boards:设置区域内所有单元格的边框,如果要设置一组区域的外边框的话用Rang->BorderAround设置
下面用一个具体的例子来说明怎么通过MFC来操作Excel
2、Excel库的插入
在我们MFC的工程中,按Ctrl+W打开MFC类向导对话框,点击“Add Class...”->“From a type Library...”,找到你所使用的excel类型库,我使用的在目录C:/Program Files/Microsoft Office/OFFICE11下的“EXCEL.EXE”文件,查找时文件类型选“All Files”,然后添加我们所需要的类,通常以上列举的前6类是必须的,其它的需要时再添加。我不认为全部添加是一种好的做法,一个我觉得很乱,另外生成的excel.cpp文件会很大。我建议浏览一遍这些类,这样当你做些操作时可以更清楚的知道需要添加哪些类。添加完需要的类后,我们就可以来做一些基本的操作了。
3、Com支持库的初始化
通常在App的InitInstance()里面加入初始化和关闭COM库的操作,在DoModal()调用之前加入初始化的代码:
- if(CoInitialize(NULL)!=0)
- {
- AfxMessageBox("初始化COM支持库失败!");
- exit(1);
- }
在return之前加入CoUninitialize(); 关闭CON库。
4、代码演示一些基本的操作
首先别忘了包含头文件“excel.h”,若用到_variant_t()时,需要包含头文件“comdef.h”和“comutil.h”,否则会出现错误:
“error C2065: '_variant_t' : undeclared identifier”。
下面的代码包括了一些基本的操作:
- //变量的定义
- _Applicationapp;
- Workbooksbooks;
- _Workbookbook;
- Worksheetssheets;
- _Worksheetsheet;
- Rangerange;
- LPDISPATCHlpDisp;
- COleVariantvResult;
- CStringstr="";
- COleVariant
- covTrue((short)TRUE),
- covFalse((short)FALSE),
- covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR);
- //创建Excel2003服务器(启动Excel)
- if(!app.CreateDispatch("Excel.Application",NULL))
- {
- AfxMessageBox("CreateExcelservicefailure!");
- return;
- }
- //设置为FALSE时,后面的app.Quit();注释要打开
- //否则EXCEL.EXE进程会一直存在,并且每操作一次就会多开一个进程
- app.SetVisible(TRUE);
- books.AttachDispatch(app.GetWorkbooks(),true);
- /*
- *打开一个工作簿。
- *Excel2000只需要13个参数就行,Excel2003需要15个参数
- */
- lpDisp=books.Open("E://test.xls",
- covOptional,covOptional,covOptional,covOptional,
- covOptional,covOptional,covOptional,covOptional,
- covOptional,covOptional,covOptional,covOptional,
- covOptional,covOptional);
- ASSERT(lpDisp);
- book.AttachDispatch(lpDisp);
- //得到Worksheets
- sheets.AttachDispatch(book.GetWorksheets(),true);
- //得到Worksheet
- sheet.AttachDispatch(sheets.GetItem(_variant_t((short)(1))));
- //得到全部Cells
- range.AttachDispatch(sheet.GetCells(),true);
- //往单元格A1里写入字符串数据,就像操作矩阵一样,第1行第1列
- range.SetItem(_variant_t((LONG)1),_variant_t((LONG)1),_variant_t("helloword!"));
- //往单元格A2里写入时间数据
- range.AttachDispatch(sheet.GetRange(_variant_t("A2"),_variant_t("A2")),true);
- range.SetValue2(_variant_t("2011/02/15"));
- //往单元格A3~A6里写入浮点数据
- range.AttachDispatch(sheet.GetRange(_variant_t("A3"),_variant_t("A6")),true);
- range.SetValue2(_variant_t((double)3.14));
- //设置单元格的列宽为12
- range.AttachDispatch(sheet.GetRange(_variant_t("A1"),_variant_t("A1")),true);
- range.SetColumnWidth(_variant_t((long)12));
- //所有单元格居中显示
- range.AttachDispatch(sheet.GetCells(),true);
- range.SetHorizontalAlignment(_variant_t((long)-4108));//-4108:居中,-4131:靠左,-4152:靠右
- range.SetVerticalAlignment(_variant_t((long)-4108));//-4108:居中,-4160:靠上,-4107:靠下
- //读取单元格的数据,第4行第1列
- range.AttachDispatch(range.GetItem(_variant_t((long)(4)),_variant_t((long)(1))).pdispVal);
- vResult=range.GetValue(covOptional);
- switch(vResult.vt)
- {
- caseVT_BSTR://字符串
- str=vResult.bstrVal;
- break;
- caseVT_R8://8字节的数字
- str.Format("%f",vResult.dblVal);
- break;
- caseVT_DATE://时间格式
- SYSTEMTIMEst;
- VariantTimeToSystemTime(vResult.date,&st);
- break;
- caseVT_EMPTY://单元格空的
- str="";
- break;
- }
- //MessageBox(str);
- Fontft;//要插入excel类库里面的Font类,下面类似
- range.AttachDispatch(sheet.GetRange(_variant_t("A3"),_variant_t("A5")),true);
- ft.AttachDispatch(range.GetFont());
- ft.SetName(_variant_t("华文行楷"));//字体
- ft.SetSize(_variant_t((long)12));//字号
- //ft.SetColorIndex(_variant_t((long)3));//字的颜色:红色
- ft.SetColor(_variant_t((long)RGB(255,0,0)));
- ft.SetBold(_variant_t((long)1));//1:粗体,0:非粗体
- Interiorit;//底色设置
- range.AttachDispatch(sheet.GetRange(_variant_t("C3"),_variant_t("E6")),true);
- it.AttachDispatch(range.GetInterior());
- it.SetColorIndex(_variant_t((long)20));//底色设置为浅青色
- Bordersborders;//先设置区域内所有单元格的边框
- borders=range.GetBorders();
- borders.SetColorIndex(_variant_t((long)1));
- borders.SetLineStyle(_variant_t((long)1));
- borders.SetWeight(_variant_t((long)2));
- //然后设置外边框
- //LineStyle=线型(1~13)Weight=线宽ColorIndex=线的颜色(-4105为自动,1为黑色)
- range.BorderAround(_variant_t((long)9),_variant_t((long)1),_variant_t((long)1),vtMissing);
- range.AttachDispatch(sheet.GetRange(_variant_t("C8"),_variant_t("D9")),true);
- //合并单元格
- range.Merge(_variant_t((long)0));
- book.Save();//保存Excel的内容
- //app.SetDisplayAlerts(false);//不弹出对话框询问是否保存
- //app.Quit();//退出
- //释放对象
- range.ReleaseDispatch();
- sheet.ReleaseDispatch();
- sheets.ReleaseDispatch();
- book.ReleaseDispatch();
- books.ReleaseDispatch();
- app.ReleaseDispatch();
- .h文件:
- #include"comdef.h"
- #include"excel.h"
- classExcelFile
- {
- public:
- voidShowInExcel(boolbShow);
- CStringGetCell(intiRow,intiColumn);
- intGetCellInt(intiRow,intiColumn);
- intGetRowCount();
- intGetColumnCount();
- boolLoadSheet(intiIndex);
- CStringGetSheetName(intiIndex);
- staticvoidInitExcel();
- staticvoidReleaseExcel();
- intGetSheetCount();
- boolOpen(CStringFileName);
- ExcelFile();
- virtual~ExcelFile();
- protected:
- private:
- static_Applicationm_ExcelApp;
- Workbooksm_Books;
- _Workbookm_Book;
- Worksheetsm_sheets;
- _Worksheetm_sheet;
- Rangem_Rge;
- };
- .cpp文件:
- ExcelFile::ExcelFile()
- {
- }
- ExcelFile::~ExcelFile()
- {
- m_Rge.ReleaseDispatch();
- m_sheet.ReleaseDispatch();
- m_sheets.ReleaseDispatch();
- m_Book.ReleaseDispatch();
- m_Books.ReleaseDispatch();
- }
- voidExcelFile::InitExcel()
- {
- //创建Excel2000服务器(启动Excel)
- if(!m_ExcelApp.CreateDispatch("Excel.Application",NULL))
- {
- AfxMessageBox("创建Excel服务失败!");
- exit(1);
- }
- }
- voidExcelFile::ReleaseExcel()
- {
- m_ExcelApp.ReleaseDispatch();
- }
- boolExcelFile::Open(CStringFileName)
- {
- //打开excel文件
- //利用模板文件建立新文档
- m_Books.AttachDispatch(m_ExcelApp.GetWorkbooks(),true);
- LPDISPATCHlpDis=NULL;
- lpDis=m_Books.Add(_variant_t(FileName));//如何判断文件是否打开?
- if(lpDis)
- {
- m_Book.AttachDispatch(lpDis);
- //得到Worksheets
- m_sheets.AttachDispatch(m_Book.GetWorksheets(),true);
- returntrue;
- }
- returnfalse;
- }
- intExcelFile::GetSheetCount()
- {
- returnm_sheets.GetCount();
- }
- CStringExcelFile::GetSheetName(intiIndex)
- {
- _Worksheetsheet;
- sheet.AttachDispatch(m_sheets.GetItem(_variant_t((long)iIndex)),true);
- CStringname=sheet.GetName();
- sheet.ReleaseDispatch();
- returnname;
- }
- boolExcelFile::LoadSheet(intiIndex)
- {
- LPDISPATCHlpDis=NULL;
- m_Rge.ReleaseDispatch();
- m_sheet.ReleaseDispatch();
- lpDis=m_sheets.GetItem(_variant_t((long)iIndex));
- if(lpDis)
- {
- m_sheet.AttachDispatch(lpDis,true);
- m_Rge.AttachDispatch(m_sheet.GetCells(),true);
- returntrue;
- }
- returnfalse;
- }
- intExcelFile::GetColumnCount()
- {
- Rangerange;
- RangeusedRange;
- usedRange.AttachDispatch(m_sheet.GetUsedRange(),true);
- range.AttachDispatch(usedRange.GetColumns(),true);
- intcount=range.GetCount();
- usedRange.ReleaseDispatch();
- range.ReleaseDispatch();
- returncount;
- }
- intExcelFile::GetRowCount()
- {
- Rangerange;
- RangeusedRange;
- usedRange.AttachDispatch(m_sheet.GetUsedRange(),true);
- range.AttachDispatch(usedRange.GetRows(),true);
- intcount=range.GetCount();
- usedRange.ReleaseDispatch();
- range.ReleaseDispatch();
- returncount;
- }
- CStringExcelFile::GetCell(intiRow,intiColumn)
- {
- Rangerange;
- range.AttachDispatch(m_Rge.GetItem(COleVariant((long)iRow),COleVariant((long)iColumn)).pdispVal,true);
- COleVariantvResult=range.GetValue2();
- CStringstr;
- if(vResult.vt==VT_BSTR)//字符串
- {
- str=vResult.bstrVal;
- }
- elseif(vResult.vt==VT_INT)
- {
- str.Format("%d",vResult.pintVal);
- }
- elseif(vResult.vt==VT_R8)//8字节的数字
- {
- str.Format("%f",vResult.dblVal);
- //str.Format("%.0f",vResult.dblVal);
- //str.Format("%1f",vResult.fltVal);
- }
- elseif(vResult.vt==VT_DATE)//时间格式
- {
- SYSTEMTIMEst;
- VariantTimeToSystemTime(vResult.date,&st);
- }
- elseif(vResult.vt==VT_EMPTY)//单元格空的
- {
- str="(NULL)";
- }
- range.ReleaseDispatch();
- returnstr;
- }
- intExcelFile::GetCellInt(intiRow,intiColumn)
- {
- Rangerange;
- range.AttachDispatch(m_Rge.GetItem(COleVariant((long)iRow),COleVariant((long)iColumn)).pdispVal,true);
- COleVariantvResult=range.GetValue2();
- intnum;
- num=(int)vResult.date;
- range.ReleaseDispatch();
- returnnum;
- }
- voidExcelFile::ShowInExcel(boolbShow)
- {
- m_ExcelApp.SetVisible(bShow);
- }