最全的MFC操作Excel2007
MFC操作Excel
1.导入相应的类库
1、导入的类：CApplication，CMyFont，CRange，CWorkBook，CWorkBooks，CWorkSheet，
CWorkSheets,CBorders, Cnterior
2、将导入的每个类的头文件中的import "C:\\Program Files
(x86)\\Microsoft Office\\Office12\\EXCEL.EXE"
no_namespace 注释掉
3、在CApplication的头文件上面添加如下代码
#import"C:\\Program Files (x86)\\Common Files\\Microsoft Shared\\OFFICE12\\MSO.DLL" \
rename("RGB", "MSORGB") \
rename("DocumentProperties", "MSODocumentProperties") \ rename("SearchPath","MsoSearchPath") \
rename_namespace("Office")
using namespace Office;
#import"C:\\Program Files (x86)\\Common Files\\Microsoft Shared\\VBA\\VBA6\\VBE6EXT.OLB"
using namespace VBIDE;
#import"C:\\Program Files (x86)\\Microsoft
Office\\Office12\\EXCEL.EXE" \
rename("DialogBox", "ExcelDialogBox") \
rename("RGB","ExcelRGB") \
rename("CopyFile", "ExcelCopyFile") \
rename("ReplaceText", "ExcelReplaceText") \
no_auto_exclude
using namespace Excel;
4、重新对代码进行编译
2.功能代码
ExcelFile.h
#pragma once
#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"
class ExcelFile
{
public:
ExcelFile(void);
~ExcelFile(void);
public:
static BOOL InitApp();
static CApplication s_app;
static BOOL isStart;
static void Quit();
public:
CWorkbook m_book;
TCHAR m_FileName[MAX_PATH];
BOOL m_isOpen;
CWorkbooks m_books;
CWorksheets m_sheets;
CWorksheet m_sheet;
CRange m_curr_range;
public:
BOOL Create(LPTSTR szPathName);
BOOL Open(LPTSTR szPathName);
void Close();
void Save();
void SaveAs(CString pathName);
//加载sheet 通过名字
BOOL LoadSheet(LPTSTR sheetName);
//加载sheet 通过索引位置
BOOL LoadSheet(long index);
//获取制定单元格的字符串值
BOOL GetCellString(LPTSTR content,UINT len,long x,long y);
//获取单元格中的数值
BOOL GetCellNumber(DOUBLE * retVal,long x,long y);
//获取单元格中的整数
BOOL GetCellInt(LONG * retVal,long x,long y);
//获取单元格中的日期
BOOL GetCellDate(SYSTEMTIME* date,long x,long y);
//向单元格中写字符串
BOOL SetCellString(LPTSTR content,long x,long y);
//向单元格中写整数
BOOL SetCellInt(LONG val,long x,long y);
//写浮点数
BOOL SetCellNumber(DOUBLE val,long x,long y);
//写日期
BOOL SetCellDate(SYSTEMTIME date,long x,long y);
//检查一个CELL是否是字符串
BOOL IsCellString(long x,long y);
//检查一个Cell是否是数值
BOOL isCellNumber(long x,long y);
//检查一个CELL是否是日期时间类型
BOOL isCellDate(long x,long y);
//得到当前sheet的总行数
int GetRowCount();
//得到当前sheet的总列数
int GetColumnCount();
//合并单元格
void Merge(int x,int y,int cx,int cy);
//坐标转换
CString IndexToString(long x,long y);
//写公式
BOOL SetFormula(CString formula,long x,long y);
//设置单元格格式
BOOL SetNumberFormat(CString format,long x,long y); //============设置单个单元格字体格式=================
//设置字体颜色
BOOL SetFontColor(unsigned long color,long x,long y); //设置字体大小
BOOL SetFontSize(unsigned char csize,long x,long y); //设置字体
BOOL SetFontFamily(CString strStyle,long x,long y); //设置粗体
BOOL SetBold(BOOL bBold,long x,long y);
//============设置区域格式=========================
//设置区域字体颜色
BOOL SetRangeFontColor(unsigned long color,CString
c1,CString c2);
//设置区域字体大小
BOOL SetRangeFontSize(unsigned char csize,CString
c1,CString c2);
//设置区域字体
BOOL SetRangeFontFamily(CString strStyle,CString
c1,CString c2);
//设置区域粗体
BOOL SetRangeBold(BOOL bBold,CString c1,CString c2);
//合并区域
BOOL Merge(CString c1,CString c2);
//设置区域的水平对其方式
BOOL SetHorizontalAlignment(long mode,CString
c1,CString c2);
//设置区域的垂直对其方式
BOOL SetVerticalAlignment(long mode,CString
c1,CString c2);
//设置列宽
BOOL SetColumnWidth(long width,CString c1,CString c2);
//设置行高
BOOL SetRowHeight(long height,CString c1,CString c2);
//设置边框线
BOOL SetBorders(CString c1,CString c2);
//设置表格底色
BOOL SetColorIndex(unsigned long color,CString
c1,CString c2);
//设置外边框线
BOOL SetAround(CString c1,CString c2);
//添加一个sheet
BOOL AddSheet(CString sName);
ExcelFile.cpp
#include"stdafx.h"
#include"ExcelFile.h"
#include
BOOL ExcelFile::isStart = FALSE;
CApplication ExcelFile::s_app;
COleVariant
covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR);
//程序退出
void ExcelFile::Quit(){
if (ExcelFile::isStart){
ExcelFile::s_app.Quit();
ExcelFile::s_app.ReleaseDispatch();
ExcelFile::s_app = NULL;
ExcelFile::isStart = FALSE;
}
}
//程序初始化
BOOL ExcelFile::InitApp(){
if (ExcelFile::isStart){
return TRUE;
}
if
(!s_app.CreateDispatch(_T("Excel.Application"),NULL)){ return FALSE;
}else{
ExcelFile::isStart = TRUE;
return TRUE;
}
}
ExcelFile::ExcelFile(void)
{
this->m_isOpen = FALSE;
}
ExcelFile::~ExcelFile(void)
{
}
//创建一个文件
BOOL ExcelFile::Create(LPTSTR szPathName){
if (m_isOpen){
return FALSE;
}
m_books.AttachDispatch(ExcelFile::s_app.get_Workboo ks(),TRUE);
LPDISPATCH lpDis = NULL;
lpDis = m_books.Add(covOptional);
if (lpDis){
m_book.AttachDispatch(lpDis);
m_sheets.AttachDispatch(m_book.get_Worksheets(),TRU E);
_tcscpy_s(m_FileName,MAX_PATH,szPathName);
m_isOpen = TRUE;
this->SaveAs(szPathName);
return TRUE;
}
return FALSE;
}
//打开一个文件
BOOL ExcelFile::Open(LPTSTR szPathName){
if (m_isOpen){
return FALSE;
}
m_books.AttachDispatch(ExcelFile::s_app.get_Workboo ks(),TRUE);
LPDISPATCH lpDis = NULL;
lpDis =
m_books.Open(szPathName,covOptional,covOptional,covOpt ional,covOptional,covOptional,covOptional,covOptional, covOptional,covOptional,covOptional,covOptional,covOpt ional,covOptional,covOptional);
if (lpDis){
m_book.AttachDispatch(lpDis);
m_sheets.AttachDispatch(m_book.get_Worksheets(),TRU E);
_tcscpy_s(m_FileName,MAX_PATH,szPathName);
m_isOpen = TRUE;
return TRUE;
}
return FALSE;
}
//关闭当前操作的文件
void ExcelFile::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;
}
}
//保存当前文件
void ExcelFile::Save(){
if (m_isOpen){
m_book.Save();
}
}
//另存为
void ExcelFile::SaveAs(CString pathName){
if (m_isOpen){
m_book.SaveAs(COleVariant(pathName),covOptional,cov Optional,covOptional,covOptional,covOptional,0,covOpti onal,covOptional,covOptional,covOptional,covOptional);
}
}
//加载sheet
BOOL ExcelFile::LoadSheet(LPTSTR sheetName){
if (!m_isOpen){
return FALSE;
}
LPDISPATCH lpDis = 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());
return TRUE;
}else{
return FALSE;
}
}
BOOL ExcelFile::LoadSheet(long index){
if (!m_isOpen){
return FALSE;
}
LPDISPATCH lpDis = 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());
return TRUE;
}else{
return FALSE;
}
}
//获取字符串值
BOOL ExcelFile::GetCellString(LPTSTR content,UINT
len,long x,long y){
COleVariant vResult ;
CString str;
if (m_isOpen){
CRange range;
range.AttachDispatch(m_curr_range.get_Item(COleVari ant(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){
SYSTEMTIME st;
VariantTimeToSystemTime(vResult.date, &st);
CTime tm(st);
str=tm.Format("%Y-%m-%d");
}
//空
if (vResult.vt = VT_EMPTY){
str = "";
}
_tcscpy_s(content,len,str.GetBuffer());
return TRUE;
}else{
return FALSE;
}
}
//向单元格中写字符串
BOOL ExcelFile::SetCellString(LPTSTR content,long x,long y){
if (m_isOpen){
CRange range;
range.AttachDispatch(m_curr_range.get_Item(COleVari ant(x),COleVariant(y)).pdispVal,TRUE);
range.put_Value2(COleVariant(content));
range.ReleaseDispatch();
return TRUE;
}else{
return FALSE;
}
}
//检查一个CELL是否是字符串
BOOL ExcelFile::IsCellString(long x,long y){
if (!m_isOpen){
return FALSE;
}
CRange range;
range.AttachDispatch(m_curr_range.get_Item(COleVariant (x),COleVariant(y)).pdispVal,TRUE);
COleVariant vResult = range.get_Value2();
range.ReleaseDispatch();
if (vResult.vt == VT_BSTR){
return TRUE;
}else{
return FALSE;
}
}
//检查一个Cell是否是数值
BOOL ExcelFile::isCellNumber(long x,long y){ if (!m_isOpen){
return FALSE;
}
CRange range;
range.AttachDispatch(m_curr_range.get_Item(COleVariant (x),COleVariant(y)).pdispVal,TRUE);
COleVariant vResult = 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){
return TRUE;
}else{
return FALSE;
}
}
//检查一个CELL是否是日期时间类型
BOOL ExcelFile::isCellDate(long x,long y){
if (!m_isOpen){
return FALSE;
}
CRange range;
range.AttachDispatch(m_curr_range.get_Item(COleVariant (x),COleVariant(y)).pdispVal,TRUE);
COleVariant vResult = range.get_Value2();
range.ReleaseDispatch();
if (vResult.vt == VT_DATE){
return TRUE;
}else{
return FALSE;
}
}
//获取单元格中的数字
BOOL ExcelFile::GetCellNumber(DOUBLE* retVal,long x,long y){
if (!m_isOpen){
return FALSE;
}
CRange range;
range.AttachDispatch(m_curr_range.get_Item(COleVari ant(x),COleVariant(y)).pdispVal,TRUE);
COleVariant vResult = range.get_Value2();
range.ReleaseDispatch();
if (vResult.vt == VT_R8){
*retVal = vResult.dblVal;
return TRUE;
}
if (vResult.vt == VT_R4){
*retVal = vResult.fltVal;
return TRUE;
}
return FALSE;
}
//获取单元格中的整数
BOOL ExcelFile::GetCellInt(LONG* retVal,long x,long y){ if (!m_isOpen){
return FALSE;
}
CRange range;
range.AttachDispatch(m_curr_range.get_Item(COleVari ant(x),COleVariant(y)).pdispVal,TRUE);
COleVariant vResult = range.get_Value2();
range.ReleaseDispatch();
if (vResult.vt == VT_I4){
*retVal = vResult.lVal;
return TRUE;
if (vResult.vt == VT_INT){
*retVal = vResult.intVal;
return TRUE;
}
return FALSE;
}
//获取单元格中的日期
BOOL ExcelFile::GetCellDate(SYSTEMTIME* date,long x,long y){
if (!m_isOpen){
return FALSE;
}
CRange range;
range.AttachDispatch(m_curr_range.get_Item(COleVari ant(x),COleVariant(y)).pdispVal,TRUE);
COleVariant vResult = range.get_Value2();
range.ReleaseDispatch();
if (vResult.vt == VT_DATE){
VariantTimeToSystemTime(vResult.date, date);
return TRUE;
}
return FALSE;
}
//向单元格中写整数
BOOL ExcelFile::SetCellInt(LONG val,long x,long y){ if (!m_isOpen){
return FALSE;
}
CRange range;
range.AttachDispatch(m_curr_range.get_Item(COleVari ant(x),COleVariant(y)).pdispVal,TRUE);
range.put_Value2(COleVariant((long)val));
range.ReleaseDispatch();
return TRUE;
//写浮点数
BOOL ExcelFile::SetCellNumber(DOUBLE val,long x,long y){
if (!m_isOpen){
return FALSE;
}
CRange range;
range.AttachDispatch(m_curr_range.get_Item(COleVari ant(x),COleVariant(y)).pdispVal,TRUE);
range.put_Value2(COleVariant(double(val)));
range.ReleaseDispatch();
return TRUE;
}
//写日期
BOOL ExcelFile::SetCellDate(SYSTEMTIME time,long x,long y){
if (!m_isOpen){
return FALSE;
}
CRange range;
range.AttachDispatch(m_curr_range.get_Item(COleVari ant(x),COleVariant(y)).pdispVal,TRUE);
COleDateTime dateTime;
dateTime.SetDate(time.wYear,time.wMonth,time.wDay);
COleVariant v_time(dateTime);
v_time.vt = VT_DATE;
range.put_Value2(v_time);
range.ReleaseDispatch();
return TRUE;
}
//得到总行数
int ExcelFile::GetRowCount(){
CRange range;
CRange useRange;
useRange.AttachDispatch(m_sheet.get_UsedRange(),TRU E);
range.AttachDispatch(useRange.get_Rows(),TRUE);
int count = range.get_Count();
useRange.ReleaseDispatch();
range.ReleaseDispatch();
return count;
}
//得到总列数
int ExcelFile::GetColumnCount(){
CRange range;
CRange useRange;
useRange.AttachDispatch(m_sheet.get_UsedRange(),TRU E);
range.AttachDispatch(useRange.get_Columns(),TRUE);
int count = range.get_Count();
useRange.ReleaseDispatch();
range.ReleaseDispatch();
return count;
}
//合并单元格
void ExcelFile::Merge(int x,int y,int cx,int cy){
CRange range;
CRange meRange;
range.AttachDispatch(m_curr_range.get_Item(COleVari
ant(long(x)),COleVariant(long(y))).pdispVal,TRUE);
meRange.AttachDispatch(range.get_Resize(COleVariant (long(cx)),COleVariant(long(cy))));
meRange.Merge(COleVariant(long(0)));
range.ReleaseDispatch();
meRange.ReleaseDispatch();
}
//坐标转换将(2,3)类型的坐标转换为C2类型的坐标
CString ExcelFile::IndexToString(long x,long y){ std::stack
char mod = y%26;
long dis = y/26;
ss.push(mod);
while (dis > 0){
mod = dis%26;
dis = dis/26;
ss.push(mod);
}
CString str = _T("");
while (!ss.empty()){
str.AppendChar(_T('A' - 1 + ss.top()));
ss.pop();
}
CString indexStr;
indexStr.Format(_T("%s%d"),str,x);
return indexStr;
}
//向单元格中写公式
BOOL ExcelFile::SetFormula(CString formula,long x,long y){
if (!m_isOpen){
return FALSE;
}
CRange range;
range.AttachDispatch(m_curr_range.get_Item(COleVari ant(x),COleVariant(y)).pdispVal,TRUE);
range.put_Formula(COleVariant(formula));
range.ReleaseDispatch();
return TRUE;
}
//设置单元格格式
BOOL ExcelFile::SetNumberFormat(CString format,long
x,long y){
if (!m_isOpen){
return FALSE;
}
CRange range;
range.AttachDispatch(m_curr_range.get_Item(COleVari ant(x),COleVariant(y)).pdispVal,TRUE);
range.put_NumberFormat(COleVariant(format));
range.ReleaseDispatch();
return TRUE;
}
//设置字体颜色
BOOL ExcelFile::SetFontColor(unsigned long color,long x,long y){
if (!m_isOpen){
return FALSE;
}
CRange range;
range.AttachDispatch(m_curr_range.get_Item(COleVari ant(x),COleVariant(y)).pdispVal,TRUE);
CExcelFont font = range.get_Font();
font.put_Color(COleVariant(long(color)));
font.ReleaseDispatch();
range.ReleaseDispatch();
return TRUE;
}
//设置字体大小
BOOL ExcelFile::SetFontSize(unsigned char csize,long x,long y){
if (!m_isOpen){
return FALSE;
}
CRange range;
range.AttachDispatch(m_curr_range.get_Item(COleVari ant(x),COleVariant(y)).pdispVal,TRUE);
CExcelFont font = range.get_Font();
font.put_Size(COleVariant(csize));
font.ReleaseDispatch();
range.ReleaseDispatch();
return TRUE;
}
//设置字体样式
BOOL ExcelFile::SetFontFamily(CString strStyle,long
x,long y){
if (!m_isOpen){
return FALSE;
}
CRange range;
range.AttachDispatch(m_curr_range.get_Item(COleVari ant(x),COleVariant(y)).pdispVal,TRUE);
CExcelFont font = range.get_Font();
font.put_Name(COleVariant(strStyle));
font.ReleaseDispatch();
range.ReleaseDispatch();
return TRUE;
}
//设置粗体
BOOL ExcelFile::SetBold(BOOL bBold,long x,long y){
if (!m_isOpen){
return FALSE;
}
CRange range;