最全的MFC操作Excel.docx
- 文档编号:9849977
- 上传时间:2023-02-07
- 格式:DOCX
- 页数:33
- 大小:21.10KB
最全的MFC操作Excel.docx
《最全的MFC操作Excel.docx》由会员分享,可在线阅读,更多相关《最全的MFC操作Excel.docx(33页珍藏版)》请在冰豆网上搜索。
最全的MFC操作Excel
MFC操作Excel
1.导入相应的类库
1、导入的类:
CApplication,CMyFont,CRange,CWorkBook,CWorkBooks,CWorkSheet,CWorkSheets,CBorders,Cnterior
2、将导入的每个类的头文件中的import"C:
\\ProgramFiles(x86)\\MicrosoftOffice\\Office12\\EXCEL.EXE"no_namespace注释掉
3、在CApplication的头文件上面添加如下代码
#import"C:
\\ProgramFiles(x86)\\CommonFiles\\MicrosoftShared\\OFFICE12\\MSO.DLL"\
rename("RGB","MSORGB")\
rename("DocumentProperties","MSODocumentProperties")\
rename("SearchPath","MsoSearchPath")\
rename_namespace("Office")
usingnamespaceOffice;
#import"C:
\\ProgramFiles(x86)\\CommonFiles\\MicrosoftShared\\VBA\\VBA6\\VBE6EXT.OLB"
usingnamespaceVBIDE;
#import"C:
\\ProgramFiles(x86)\\MicrosoftOffice\\Office12\\EXCEL.EXE"\
rename("DialogBox","ExcelDialogBox")\
rename("RGB","ExcelRGB")\
rename("CopyFile","ExcelCopyFile")\
rename("ReplaceText","ExcelReplaceText")\
no_auto_exclude
usingnamespaceExcel;
4、重新对代码进行编译
2.功能代码
ExcelFile.h
#pragmaonce
#include"CApplication.h"
#include"CWorkbook.h"
#include"CWorkbooks.h"
#include"CWorksheet.h"
#include"CWorksheets.h"
#include"CRange.h"
#include"CExcelFont.h"
#include"CBorders.h"
#include"Cnterior.h"
classExcelFile
{
public:
ExcelFile(void);
~ExcelFile(void);
public:
staticBOOLInitApp();
staticCApplications_app;
staticBOOLisStart;
staticvoidQuit();
public:
CWorkbookm_book;
TCHARm_FileName[MAX_PATH];
BOOLm_isOpen;
CWorkbooksm_books;
CWorksheetsm_sheets;
CWorksheetm_sheet;
CRangem_curr_range;
public:
BOOLCreate(LPTSTRszPathName);
BOOLOpen(LPTSTRszPathName);
voidClose();
voidSave();
voidSaveAs(CStringpathName);
//加载sheet通过名字
BOOLLoadSheet(LPTSTRsheetName);
//加载sheet通过索引位置
BOOLLoadSheet(longindex);
//获取制定单元格的字符串值
BOOLGetCellString(LPTSTRcontent,UINTlen,longx,longy);
//获取单元格中的数值
BOOLGetCellNumber(DOUBLE*retVal,longx,longy);
//获取单元格中的整数
BOOLGetCellInt(LONG*retVal,longx,longy);
//获取单元格中的日期
BOOLGetCellDate(SYSTEMTIME*date,longx,longy);
//向单元格中写字符串
BOOLSetCellString(LPTSTRcontent,longx,longy);
//向单元格中写整数
BOOLSetCellInt(LONGval,longx,longy);
//写浮点数
BOOLSetCellNumber(DOUBLEval,longx,longy);
//写日期
BOOLSetCellDate(SYSTEMTIMEdate,longx,longy);
//检查一个CELL是否是字符串
BOOLIsCellString(longx,longy);
//检查一个Cell是否是数值
BOOLisCellNumber(longx,longy);
//检查一个CELL是否是日期时间类型
BOOLisCellDate(longx,longy);
//得到当前sheet的总行数
intGetRowCount();
//得到当前sheet的总列数
intGetColumnCount();
//合并单元格
voidMerge(intx,inty,intcx,intcy);
//坐标转换
CStringIndexToString(longx,longy);
//写公式
BOOLSetFormula(CStringformula,longx,longy);
//设置单元格格式
BOOLSetNumberFormat(CStringformat,longx,longy);
//============设置单个单元格字体格式=================
//设置字体颜色
BOOLSetFontColor(unsignedlongcolor,longx,longy);
//设置字体大小
BOOLSetFontSize(unsignedcharcsize,longx,longy);
//设置字体
BOOLSetFontFamily(CStringstrStyle,longx,longy);
//设置粗体
BOOLSetBold(BOOLbBold,longx,longy);
//============设置区域格式=========================
//设置区域字体颜色
BOOLSetRangeFontColor(unsignedlongcolor,CStringc1,CStringc2);
//设置区域字体大小
BOOLSetRangeFontSize(unsignedcharcsize,CStringc1,CStringc2);
//设置区域字体
BOOLSetRangeFontFamily(CStringstrStyle,CStringc1,CStringc2);
//设置区域粗体
BOOLSetRangeBold(BOOLbBold,CStringc1,CStringc2);
//合并区域
BOOLMerge(CStringc1,CStringc2);
//设置区域的水平对其方式
BOOLSetHorizontalAlignment(longmode,CStringc1,CStringc2);
//设置区域的垂直对其方式
BOOLSetVerticalAlignment(longmode,CStringc1,CStringc2);
//设置列宽
BOOLSetColumnWidth(longwidth,CStringc1,CStringc2);
//设置行高
BOOLSetRowHeight(longheight,CStringc1,CStringc2);
//设置边框线
BOOLSetBorders(CStringc1,CStringc2);
//设置表格底色
BOOLSetColorIndex(unsignedlongcolor,CStringc1,CStringc2);
//设置外边框线
BOOLSetAround(CStringc1,CStringc2);
//添加一个sheet
BOOLAddSheet(CStringsName);
//删除一个sheet
BOOLDelSheet(CStringsName);
BOOLDelSheet(longindex);
//重命名一个sheet
BOOLRenameSheet(CStringsName,CStringnewName);
BOOLRenameSheet(longindex,CStringnewName);
};
ExcelFile.cpp
#include"stdafx.h"
#include"ExcelFile.h"
#include
BOOLExcelFile:
:
isStart=FALSE;
CApplicationExcelFile:
:
s_app;
COleVariantcovOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR);
//程序退出
voidExcelFile:
:
Quit(){
if(ExcelFile:
:
isStart){
ExcelFile:
:
s_app.Quit();
ExcelFile:
:
s_app.ReleaseDispatch();
ExcelFile:
:
s_app=NULL;
ExcelFile:
:
isStart=FALSE;
}
}
//程序初始化
BOOLExcelFile:
:
InitApp(){
if(ExcelFile:
:
isStart){
returnTRUE;
}
if(!
s_app.CreateDispatch(_T("Excel.Application"),NULL)){
returnFALSE;
}else{
ExcelFile:
:
isStart=TRUE;
returnTRUE;
}
}
ExcelFile:
:
ExcelFile(void)
{
this->m_isOpen=FALSE;
}
ExcelFile:
:
~ExcelFile(void)
{
}
//创建一个文件
BOOLExcelFile:
:
Create(LPTSTRszPathName){
if(m_isOpen){
returnFALSE;
}
m_books.AttachDispatch(ExcelFile:
:
s_app.get_Workbooks(),TRUE);
LPDISPATCHlpDis=NULL;
lpDis=m_books.Add(covOptional);
if(lpDis){
m_book.AttachDispatch(lpDis);
m_sheets.AttachDispatch(m_book.get_Worksheets(),TRUE);
_tcscpy_s(m_FileName,MAX_PATH,szPathName);
m_isOpen=TRUE;
this->SaveAs(szPathName);
returnTRUE;
}
returnFALSE;
}
//打开一个文件
BOOLExcelFile:
:
Open(LPTSTRszPathName){
if(m_isOpen){
returnFALSE;
}
m_books.AttachDispatch(ExcelFile:
:
s_app.get_Workbooks(),TRUE);
LPDISPATCHlpDis=NULL;
lpDis=m_books.Open(szPathName,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional);
if(lpDis){
m_book.AttachDispatch(lpDis);
m_sheets.AttachDispatch(m_book.get_Worksheets(),TRUE);
_tcscpy_s(m_FileName,MAX_PATH,szPathName);
m_isOpen=TRUE;
returnTRUE;
}
returnFALSE;
}
//关闭当前操作的文件
voidExcelFile:
:
Close(){
if(m_isOpen){
m_curr_range.ReleaseDispatch();
m_curr_range.ReleaseDispatch();
m_book.Close(COleVariant(short(FALSE)),COleVariant(m_FileName),covOptional);
m_book.ReleaseDispatch();
m_book.ReleaseDispatch();
m_curr_range.ReleaseDispatch();
m_sheet.ReleaseDispatch();
m_sheets.ReleaseDispatch();
m_books.ReleaseDispatch();
this->m_isOpen=FALSE;
}
}
//保存当前文件
voidExcelFile:
:
Save(){
if(m_isOpen){
m_book.Save();
}
}
//另存为
voidExcelFile:
:
SaveAs(CStringpathName){
if(m_isOpen){
m_book.SaveAs(COleVariant(pathName),covOptional,covOptional,covOptional,covOptional,covOptional,0,covOptional,covOptional,covOptional,covOptional,covOptional);
}
}
//加载sheet
BOOLExcelFile:
:
LoadSheet(LPTSTRsheetName){
if(!
m_isOpen){
returnFALSE;
}
LPDISPATCHlpDis=NULL;
m_curr_range.ReleaseDispatch();
m_sheet.ReleaseDispatch();
lpDis=m_sheets.get_Item(COleVariant(sheetName));
if(lpDis){
m_sheet.AttachDispatch(lpDis,TRUE);
m_curr_range.AttachDispatch(m_sheet.get_Cells());
returnTRUE;
}else{
returnFALSE;
}
}
BOOLExcelFile:
:
LoadSheet(longindex){
if(!
m_isOpen){
returnFALSE;
}
LPDISPATCHlpDis=NULL;
m_curr_range.ReleaseDispatch();
m_sheet.ReleaseDispatch();
lpDis=m_sheets.get_Item(COleVariant(index));
if(lpDis){
m_sheet.AttachDispatch(lpDis,TRUE);
m_curr_range.AttachDispatch(m_sheet.get_Cells());
returnTRUE;
}else{
returnFALSE;
}
}
//获取字符串值
BOOLExcelFile:
:
GetCellString(LPTSTRcontent,UINTlen,longx,longy){
COleVariantvResult;
CStringstr;
if(m_isOpen){
CRangerange;
range.AttachDispatch(m_curr_range.get_Item(COleVariant(x),COleVariant(y)).pdispVal,TRUE);
vResult=range.get_Value2();
range.ReleaseDispatch();
//字符串
if(vResult.vt==VT_BSTR){
str=vResult.bstrVal;
}
//整数
if(vResult.vt==VT_INT){
str.Format(_T("%d"),vResult.intVal);
}
//8字节的数字
if(vResult.vt==VT_R8){
//AfxMessageBox(_T("r8"));
str.Format(_T("%.4f"),vResult.dblVal);
}
//时间
if(vResult.vt==VT_DATE){
SYSTEMTIMEst;
VariantTimeToSystemTime(vResult.date,&st);
CTimetm(st);
str=tm.Format("%Y-%m-%d");
}
//空
if(vResult.vt=VT_EMPTY){
str="";
}
_tcscpy_s(content,len,str.GetBuffer());
returnTRUE;
}else{
returnFALSE;
}
}
//向单元格中写字符串
BOOLExcelFile:
:
SetCellString(LPTSTRcontent,longx,longy){
if(m_isOpen){
CRangerange;
range.AttachDispatch(m_curr_range.get_Item(COleVariant(x),COleVariant(y)).pdispVal,TRUE);
range.put_Value2(COleVariant(content));
range.ReleaseDispatch();
returnTRUE;
}else{
returnFALSE;
}
}
//检查一个CELL是否是字符串
BOOLExcelFile:
:
IsCellString(longx,longy){
if(!
m_isOpen){
returnFALSE;
}
CRangerange;
range.AttachDispatch(m_curr_range.get_Item(COleVariant(x),COleVariant(y)).pdispVal,TRUE);
COleVariantvResult=range.get_Value2();
range.ReleaseDispatch();
if(vResult.vt==VT_BSTR){
returnTRUE;
}else{
returnFALSE;
}
}
//检查一个Cell是否是数值
BOOLExcelFile:
:
isCellNumber(longx,longy){
if(!
m_isOpen){
returnFALSE;
}
CRangerange;
range.AttachDispatch(m_curr_range.get_Item(COleVariant(x),COleVariant(y)).pdispVal,TRUE);
COleVariantvResult=range.get_Value2();
range.ReleaseDispatch();
if(vResult.vt==VT_INT||vResult.vt==VT_I8||vResult.vt==VT_I4||vResult.vt==VT_R4||vResult.vt==VT_R8){
returnTRUE;
}else{
returnFALSE;
}
}
//检查一个CELL是否是日期时间类型
BOOLExcelFile:
:
isCellDate(longx,longy){
if(!
m_isOpen){
returnFALSE;
}
CRangerange;
range.AttachDispatch(m_curr_range.get_Item(COleVariant(x),COleVariant(y)).pdispVal,TRUE);
COleVariantvResult=range.get_Value2();
range.ReleaseDispatch();
if(vResult.vt==VT_DATE){
returnTRUE;
}else{
returnFALSE;
}
}
//获取单元格中的数字
BOOLExcelFile:
:
GetCellNumber(DOUBLE*retVal,longx,longy){
if(!
m_isOpen){
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- MFC 操作 Excel
![提示](https://static.bdocx.com/images/bang_tan.gif)