VS2005操作excel所需的类及文件

最新推荐文章于 2019-03-19 13:23:11 发布

清水迎朝阳

最新推荐文章于 2019-03-19 13:23:11 发布

阅读量6.7k

点赞数

分类专栏： EXCEL 文章标签： excel path mfc null application dll

EXCEL 专栏收录该内容

8 篇文章 1 订阅

订阅专栏

1. VS2005添加COM

Project-> addclass -> MFC -> MFC CLASS FROM TypeLib

选择：_Application/_Workbook/_Worksheet/Workbooks/Worksheet/Range

点击finish 后生成

CApplication.h

CWorkbook.h

CWorkbooks.h

CWorksheet.h

CWorksheets.h

CRange.h

删掉这些文件，导入excel9.h，excel9.cpp

2.	在对应的程序文件中添加头文件#include "excel9.h"
在程序中启用COM服务库

 // 初始化 OLE 库
	if (!AfxOleInit())
	{
		AfxMessageBox(L"Could not initialize COM dll");
		return FALSE;
	}
   if (CoInitialize(NULL) != S_OK)        
return -1;


下面是一个处理EXCEL的程序例子，可直接调用：
void OnBnClickedButtonCaln()
{
	// TODO: 在此添加控件通知处理程序代码
	// TODO: Add your control notification handler code here 

	Range m_ExlRge;
	_Worksheet m_ExlSheet;
	Worksheets m_ExlSheets; 
	_Workbook m_ExlBook; 
	Workbooks m_ExlBooks; 
	_Application m_ExlApp; 


	//用m_ExlApp对象创建Excel2003进程 

	if(!m_ExlApp.CreateDispatch(_T("Excel.Application"),NULL)) 
	{ 
		AfxMessageBox(_T("创建Excel服务失败!")); 
		return; 
	} 

	//设置为可见 
	m_ExlApp.SetVisible(TRUE); 

	///下面得到应用程序所在的路径/// 
	CString theAppPath,theAppName; 
//	char Path[MAX_PATH]; 

//	GetModuleFileName(NULL,Path,MAX_PATH);//得到应用程序的全路径 
	GetModuleFileName(NULL,_T("D:\\1.xlt"),MAX_PATH);//得到应用程序的全路径 
	//theAppPath=(CString)Path; 
	theAppPath=_T("D:\\1.xlt");

	theAppName=AfxGetApp()->m_pszAppName; 
	theAppName+=_T(".exe"); 

	//把最后的文件名去掉 
	int length1,length2; 

	length1=theAppPath.GetLength(); 
	length2=theAppName.GetLength(); 

	theAppPath.Delete(length1-length2,length2); 
	 


	CString TempPath=_T(""); 

	//TempPath=theAppPath+_T("Template.xls");//EXCEL模板的路径 
	TempPath=theAppPath;

	m_ExlBooks.AttachDispatch(m_ExlApp.GetWorkbooks(),TRUE); 

	m_ExlBook.AttachDispatch(m_ExlBooks.Add((COleVariant)TempPath),TRUE);//加载EXCEL模板 

	m_ExlSheets.AttachDispatch(m_ExlBook.GetSheets(),TRUE);//加载Sheet页面 

	//添加新的Sheet页面 
	m_ExlSheets.Add(vtMissing,vtMissing,COleVariant((long)1),vtMissing); 

	//删除第二个Sheet页面 
	m_ExlSheet.AttachDispatch(m_ExlSheets.GetItem(COleVariant((long)2)),TRUE); 
	m_ExlSheet.Delete(); 

	//把第一个Sheet页面的名字改变为TestSheet 
	m_ExlSheet.AttachDispatch(m_ExlSheets.GetItem(COleVariant((long)1)),TRUE); 
	m_ExlSheet.SetName(_T("TestSheet")); 


	///合并第一行单元格A1至D1// 

	//加载要合并的单元格 
	m_ExlRge.AttachDispatch(m_ExlSheet.GetRange(COleVariant(_T("A1")),COleVariant(_T("D1"))),TRUE); 

	m_ExlRge.Merge(COleVariant((long)0)); 

	设置表格内容 

	m_ExlRge.AttachDispatch(m_ExlSheet.GetCells(),TRUE);//加载所有单元格 

	m_ExlRge.SetItem(COleVariant((long)1),COleVariant((long)1),COleVariant(_T("数学系研究生课程统计"))); 

	m_ExlRge.SetItem(COleVariant((long)2),COleVariant((long)1),COleVariant(_T("课程名"))); 
	m_ExlRge.SetItem(COleVariant((long)2),COleVariant((long)2),COleVariant(_T("课时"))); 
	m_ExlRge.SetItem(COleVariant((long)2),COleVariant((long)3),COleVariant(_T("难度"))); 
	m_ExlRge.SetItem(COleVariant((long)2),COleVariant((long)4),COleVariant(_T("教学方式"))); 

	m_ExlRge.SetItem(COleVariant((long)3),COleVariant((long)1),COleVariant(_T("泛函分析"))); 
	m_ExlRge.SetItem(COleVariant((long)3),COleVariant((long)2),COleVariant(_T("60"))); 
	m_ExlRge.SetItem(COleVariant((long)3),COleVariant((long)3),COleVariant(_T("普通"))); 
	m_ExlRge.SetItem(COleVariant((long)3),COleVariant((long)4),COleVariant(_T("老师讲课"))); 

	m_ExlRge.SetItem(COleVariant((long)4),COleVariant((long)1),COleVariant(_T("微分流形"))); 
	m_ExlRge.SetItem(COleVariant((long)4),COleVariant((long)2),COleVariant(_T("40"))); 
	m_ExlRge.SetItem(COleVariant((long)4),COleVariant((long)3),COleVariant(_T("变态难"))); 
	m_ExlRge.SetItem(COleVariant((long)4),COleVariant((long)4),COleVariant(_T("自学"))); 

	m_ExlRge.SetItem(COleVariant((long)5),COleVariant((long)1),COleVariant(_T("二阶椭圆型方程与方程组"))); 
	m_ExlRge.SetItem(COleVariant((long)5),COleVariant((long)2),COleVariant(_T("60"))); 
	m_ExlRge.SetItem(COleVariant((long)5),COleVariant((long)3),COleVariant(_T("很难"))); 
	m_ExlRge.SetItem(COleVariant((long)5),COleVariant((long)4),COleVariant(_T("讨论"))); 


	m_ExlRge.AttachDispatch(m_ExlSheet.GetUsedRange());//加载已使用的单元格 

	m_ExlRge.SetWrapText(COleVariant((long)1));//设置单元格内的文本为自动换行 

	//设置齐方式为水平垂直居中 
	//水平对齐：默认＝1,居中＝-4108,左＝-4131,右＝-4152 
	//垂直对齐：默认＝2,居中＝-4108,左＝-4160,右＝-4107 
	m_ExlRge.SetHorizontalAlignment(COleVariant((long)-4108)); 
	m_ExlRge.SetVerticalAlignment(COleVariant((long)-4108)); 

	///设置整体的字体、字号及颜色// 

	Font ft; 

	ft.AttachDispatch(m_ExlRge.GetFont()); 

	ft.SetName(COleVariant(_T("宋体")));//字体 
	ft.SetColorIndex(COleVariant((long)11));//字的颜色 
	ft.SetSize(COleVariant((long)12));//字号 

	///设置标题字体及颜色// 

	m_ExlRge.AttachDispatch(m_ExlSheet.GetRange(COleVariant(_T("A1")),COleVariant(_T("D1")))); 

	ft.AttachDispatch(m_ExlRge.GetFont()); 

	ft.SetBold(COleVariant((long)1));//粗体 
	ft.SetSize(COleVariant((long)13)); 
	ft.SetColorIndex(COleVariant((long)2)); 

//	CellFormat cf; 
//	cf.AttachDispatch(m_ExlRge.GetCells()); 

	//设置底色/ 

	Interior it; 

	it.AttachDispatch(m_ExlRge.GetInterior()); 

	it.SetColorIndex(COleVariant((long)11));//标题底色 

	表格内容的底色 

	m_ExlRge.AttachDispatch(m_ExlSheet.GetRange(COleVariant(_T("A2")),COleVariant(_T("D5")))); 

	it.AttachDispatch(m_ExlRge.GetInterior()); 

	it.SetColorIndex(COleVariant((long)15)); 


	//为表格设置边框/ 

	Range UnitRge; 
	CString CellName; 

	for(int i=1;i<=4;i++) 
	{ 
		for(int j=1;j<=4;j++) 
		{     
			CellName.Format(_T("%c%d"),j+64,i);//单元格的名称 

			UnitRge.AttachDispatch(m_ExlRge.GetRange(COleVariant(CellName),COleVariant(CellName)));//加载单元格 

			//LineStyle=线型 Weight=线宽 ColorIndex=线的颜色(-4105为自动) 
			UnitRge.BorderAround(COleVariant((long)1),(long)2,((long)-4105),vtMissing);//设置边框 
		} 
	} 



	//释放对象（相当重要！） 
	m_ExlRge.ReleaseDispatch(); 
	m_ExlSheet.ReleaseDispatch(); 
	m_ExlSheets.ReleaseDispatch(); 
	m_ExlBook.ReleaseDispatch(); 
	m_ExlBooks.ReleaseDispatch(); 
	//m_ExlApp一定要释放，否则程序结束后还会有一个Excel进程驻留在内存中，而且程序重复运行的时候会出错 
	m_ExlApp.ReleaseDispatch(); 

	//退出程序 m_ExlApp.Quit(); 


}