这是帮别人做项目遇到的一个问题，的那个是纠结了老长时间，本以为是一件很轻松的事。。。
首先，这里采用了OLE来对Excel进行操作，网上其实有大把的例子，虽然都可以运行，但是并不能满足项目要求，其实我要实现的无非就是增查删改功能。
与网上操作步骤一样，首先就是去OFFICE里面将几个重要的类添加进去。网上有一篇文章其实封装的不错了，只是没有删除与新建功能，这里添加了以上几个功能。下面进入正题。
a. project->add class->MFC class from typelib （项目->添加类->Typelib中的MFC类）,来源里面请选择“注册表” 然后再可选择的类型库中选择“Microsoft Excel ****”这样容易不出错。
b. 选中以下几项_Application,_WorkSheet,_WorkBook,WorkSheets,WorkBooks,Range，然后导入；
c. 导入后自动在工程中添加CApplication,CWorkSheet,CWorkBook,CWorkSheets,CWorkBooks,CRange这些类；然后需要把这些类的头文件中的第一句话 #import “…….EXCEL.EXE” nonamespace 删除；
d.应用程序类CWinApp的InitInstance函数中调用AfxOleInit(); 来初始化COM组件，否则无法打开COM服务器
引入之后如果编译遇到错误，Not enough actual parameters for macro ‘DialogBoxW’. 让人头疼！
解决方法是在CRange类中，
VARIANT DialogBox()
{
VARIANT result;
InvokeHelper(0xf5, DISPATCH_METHOD, VT_VARIANT, (void*)&result, NULL);
return result;
}
DialogBox()前面添加下划线变成_DialogBox()，解决了！
下面就是对Excel操作的函数封装了。
BasicExcelOperate.h
#pragma once//OLE的头文件
#include "CRange.h"
#include "CWorkbook.h"
#include "CWorkbooks.h"
#include "CWorksheet.h"
#include "CWorksheets.h"
#include "CApplication.h"///
///用于OLE的方式的EXCEL读写，
class IllusionExcelFile
{public: //构造函数和析构函数
IllusionExcelFile();
virtual ~IllusionExcelFile();
void CreateExcelFile(CString filename);
void ClearExcelValue(long iRow, long iColumn);protected:
///打开的EXCEL文件名称
CString open_excel_file_; ///EXCEL BOOK集合，（多个文件时）
CWorkbooks excel_books_;
///当前使用的BOOK，当前处理的文件
CWorkbook excel_work_book_;
///EXCEL的sheets集合
CWorksheets excel_sheets_;
///当前使用sheet
CWorksheet excel_work_sheet_;
///当前的操作区域
CRange excel_current_range_; ///是否已经预加载了某个sheet的数据
BOOL already_preload_;
///Create the SAFEARRAY from the VARIANT ret.
COleSafeArray ole_safe_array_;protected: ///EXCEL的进程实例
static CApplication excel_application_;
public: ///
void ShowInExcel(BOOL bShow); ///检查一个CELL是否是字符串
BOOL IsCellString(long iRow, long iColumn);
///检查一个CELL是否是数值
BOOL IsCellInt(long iRow, long iColumn); ///得到一个CELL的String
CString GetCellString(long iRow, long iColumn);
///得到整数
int GetCellInt(long iRow, long iColumn);
///得到double的数据
double GetCellDouble(long iRow, long iColumn); ///取得行的总数
int GetRowCount();
///取得列的总数
int GetColumnCount(); ///使用某个shet，shit，shit
BOOL LoadSheet(long table_index,BOOL pre_load = FALSE);
///通过名称使用某个sheet，
BOOL LoadSheet(LPCTSTR sheet,BOOL pre_load = FALSE);
///通过序号取得某个Sheet的名称
CString GetSheetName(long table_index); ///得到Sheet的总数
int GetSheetCount(); ///打开文件
BOOL OpenExcelFile(LPCTSTR file_name);
///关闭打开的Excel 文件，有时候打开EXCEL文件就要
void CloseExcelFile(BOOL if_save = FALSE);
//另存为一个EXCEL文件
void SaveasXSLFile(const CString &xls_file);
///取得打开文件的名称
CString GetOpenFileName();
///取得打开sheet的名称
CString GetLoadSheetName(); ///写入一个CELL一个int
void SetCellInt(long irow, long icolumn,int new_int);
///写入一个CELL一个string
void SetCellString(long irow, long icolumn,CString new_string);public:
///初始化EXCEL OLE
static BOOL InitExcel();
///释放EXCEL的 OLE
static void ReleaseExcel();
///取得列的名称，比如27->AA
static char *GetColumnName(long iColumn);protected: //预先加载
void PreLoadSheet();
};
BasicExcelOperate.c
#include "MyExcel.h"COleVariant
covTrue((short)TRUE),
covFalse((short)FALSE),
covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR); //
CApplication IllusionExcelFile::excel_application_;IllusionExcelFile::IllusionExcelFile():
already_preload_(FALSE)
{}IllusionExcelFile::~IllusionExcelFile()
{
//
CloseExcelFile();
}//初始化EXCEL文件，
BOOL IllusionExcelFile::InitExcel()
{ CoUninitialize();
if(CoInitialize(NULL)==S_FALSE)
{
AfxMessageBox(_T("初始化COM支持库失败！"));
return FALSE;
}
//创建Excel 2000服务器(启动Excel)
if (!excel_application_.CreateDispatch(_T("Excel.Application"),NULL))
{
AfxMessageBox(_T("创建Excel服务失败,你可能没有安装EXCEL，请检查!"));
return FALSE;
} excel_application_.put_DisplayAlerts(FALSE);
return TRUE;
}//
void IllusionExcelFile::ReleaseExcel()
{
excel_application_.Quit();
excel_application_.ReleaseDispatch();
excel_application_=NULL;
}//打开excel文件
BOOL IllusionExcelFile::OpenExcelFile(LPCTSTR file_name)
{
//先关闭
CloseExcelFile(); //利用模板文件建立新文档
excel_books_.AttachDispatch(excel_application_.get_Workbooks(),true); LPDISPATCH lpDis = NULL;
lpDis = excel_books_.Add(COleVariant(file_name));
if (lpDis)
{
excel_work_book_.AttachDispatch(lpDis);
//得到Worksheets
excel_sheets_.AttachDispatch(excel_work_book_.get_Worksheets(),true); //记录打开的文件名称
open_excel_file_ = file_name; return TRUE;
} return FALSE;
}//关闭打开的Excel 文件,默认情况不保存文件
void IllusionExcelFile::CloseExcelFile(BOOL if_save)
{
//如果已经打开，关闭文件
if (open_excel_file_.IsEmpty() == FALSE)
{
//如果保存,交给用户控制,让用户自己存，如果自己SAVE，会出现莫名的等待
if (if_save)
{
ShowInExcel(TRUE);
}
else
{
//
excel_work_book_.Close(COleVariant(short(FALSE)),COleVariant(open_excel_file_),covOptional);
excel_books_.Close();
} //打开文件的名称清空
open_excel_file_.Empty();
} excel_sheets_.ReleaseDispatch();
excel_work_sheet_.ReleaseDispatch();
excel_current_range_.ReleaseDispatch();
excel_work_book_.ReleaseDispatch();
excel_books_.ReleaseDispatch();
}void IllusionExcelFile::SaveasXSLFile(const CString &xls_file)
{
excel_work_book_.SaveAs(COleVariant(xls_file),
covOptional,
covOptional,
covOptional,
covOptional,
covOptional,
,
covOptional,
covOptional,
covOptional,
covOptional,
covOptional);
return;
}int IllusionExcelFile::GetSheetCount()
{
return excel_sheets_.get_Count();
}CString IllusionExcelFile::GetSheetName(long table_index)
{
CWorksheet sheet;
sheet.AttachDispatch(excel_sheets_.get_Item(COleVariant((long)table_index)),true);
CString name = sheet.get_Name();
sheet.ReleaseDispatch();
return name;
}//按照序号加载Sheet表格,可以提前加载所有的表格内部数据
BOOL IllusionExcelFile::LoadSheet(long table_index,BOOL pre_load)
{
LPDISPATCH lpDis = NULL;
excel_current_range_.ReleaseDispatch();
excel_work_sheet_.ReleaseDispatch();
lpDis = excel_sheets_.get_Item(COleVariant((long)table_index));
if (lpDis)
{
excel_work_sheet_.AttachDispatch(lpDis,true);
excel_current_range_.AttachDispatch(excel_work_sheet_.get_Cells(), true);
}
else
{
return FALSE;
} already_preload_ = FALSE;
//如果进行预先加载
if (pre_load)
{
PreLoadSheet();
already_preload_ = TRUE;
} return TRUE;
}//按照名称加载Sheet表格,可以提前加载所有的表格内部数据
BOOL IllusionExcelFile::LoadSheet(LPCTSTR sheet,BOOL pre_load)
{
LPDISPATCH lpDis = NULL;
excel_current_range_.ReleaseDispatch();
excel_work_sheet_.ReleaseDispatch();
lpDis = excel_sheets_.get_Item(COleVariant(sheet));
if (lpDis)
{
excel_work_sheet_.AttachDispatch(lpDis,true);
excel_current_range_.AttachDispatch(excel_work_sheet_.get_Cells(), true); }
else
{
return FALSE;
}
//
already_preload_ = FALSE;
//如果进行预先加载
if (pre_load)
{
already_preload_ = TRUE;
PreLoadSheet();
} return TRUE;
}//得到列的总数
int IllusionExcelFile::GetColumnCount()
{
CRange range;
CRange usedRange;
usedRange.AttachDispatch(excel_work_sheet_.get_UsedRange(), true);
range.AttachDispatch(usedRange.get_Columns(), true);
int count = range.get_Count();
usedRange.ReleaseDispatch();
range.ReleaseDispatch();
return count;
}//得到行的总数
int IllusionExcelFile::GetRowCount()
{
CRange range;
CRange usedRange;
usedRange.AttachDispatch(excel_work_sheet_.get_UsedRange(), true);
range.AttachDispatch(usedRange.get_Rows(), true);
int count = range.get_Count();
usedRange.ReleaseDispatch();
range.ReleaseDispatch();
return count;
}//检查一个CELL是否是字符串
BOOL IllusionExcelFile::IsCellString(long irow, long icolumn)
{
CRange range;
range.AttachDispatch(excel_current_range_.get_Item (COleVariant((long)irow),COleVariant((long)icolumn)).pdispVal, true);
COleVariant vResult =range.get_Value2();
//VT_BSTR标示字符串
if(vResult.vt == VT_BSTR)
{
return TRUE;
}
return FALSE;
}//检查一个CELL是否是数值
BOOL IllusionExcelFile::IsCellInt(long irow, long icolumn)
{
CRange range;
range.AttachDispatch(excel_current_range_.get_Item (COleVariant((long)irow),COleVariant((long)icolumn)).pdispVal, true);
COleVariant vResult =range.get_Value2();
//好像一般都是VT_R8
if(vResult.vt == VT_INT || vResult.vt == VT_R8)
{
return TRUE;
}
return FALSE;
}//
CString IllusionExcelFile::GetCellString(long irow, long icolumn)
{ COleVariant vResult ;
CString str;
//字符串
if (already_preload_ == FALSE)
{
CRange range;
range.AttachDispatch(excel_current_range_.get_Item (COleVariant((long)irow),COleVariant((long)icolumn)).pdispVal, true);
vResult =range.get_Value2();
range.ReleaseDispatch();
}
//如果数据依据预先加载了
else
{
long read_address[];
VARIANT val;
read_address[] = irow;
read_address[] = icolumn;
ole_safe_array_.GetElement(read_address, &val);
vResult = val;
} if(vResult.vt == VT_BSTR)
{
str=vResult.bstrVal;
}
//整数
else if (vResult.vt==VT_INT)
{
str.Format(_T("%d"),vResult.pintVal);
}
//8字节的数字
else if (vResult.vt==VT_R8)
{
str.Format(_T("%0.0f"),vResult.dblVal);
}
//时间格式
else if(vResult.vt==VT_DATE)
{
SYSTEMTIME st;
VariantTimeToSystemTime(vResult.date, &st);
CTime tm(st);
str=tm.Format("%Y-%m-%d"); }
//单元格空的
else if(vResult.vt==VT_EMPTY)
{
str="";
} return str;
}double IllusionExcelFile::GetCellDouble(long irow, long icolumn)
{
double rtn_value = ;
COleVariant vresult;
//字符串
if (already_preload_ == FALSE)
{
CRange range;
range.AttachDispatch(excel_current_range_.get_Item (COleVariant((long)irow),COleVariant((long)icolumn)).pdispVal, true);
vresult =range.get_Value2();
range.ReleaseDispatch();
}
//如果数据依据预先加载了
else
{
long read_address[];
VARIANT val;
read_address[] = irow;
read_address[] = icolumn;
ole_safe_array_.GetElement(read_address, &val);
vresult = val;
} if (vresult.vt==VT_R8)
{
rtn_value = vresult.dblVal;
} return rtn_value;
}//VT_R8
int IllusionExcelFile::GetCellInt(long irow, long icolumn)
{
int num;
COleVariant vresult; if (already_preload_ == FALSE)
{
CRange range;
range.AttachDispatch(excel_current_range_.get_Item (COleVariant((long)irow),COleVariant((long)icolumn)).pdispVal, true);
vresult = range.get_Value2();
range.ReleaseDispatch();
}
else
{
long read_address[];
VARIANT val;
read_address[] = irow;
read_address[] = icolumn;
ole_safe_array_.GetElement(read_address, &val);
vresult = val;
}
//
num = static_cast<int>(vresult.dblVal); return num;
}void IllusionExcelFile::SetCellString(long irow, long icolumn,CString new_string)
{
COleVariant new_value(new_string);
CRange start_range = excel_work_sheet_.get_Range(COleVariant(_T("A1")),covOptional);
CRange write_range = start_range.get_Offset(COleVariant((long)irow -),COleVariant((long)icolumn -) );
write_range.put_Value2(new_value);
start_range.ReleaseDispatch();
write_range.ReleaseDispatch();}void IllusionExcelFile::SetCellInt(long irow, long icolumn,int new_int)
{
COleVariant new_value((long)new_int); CRange start_range = excel_work_sheet_.get_Range(COleVariant(_T("A1")),covOptional);
CRange write_range = start_range.get_Offset(COleVariant((long)irow -),COleVariant((long)icolumn -) );
write_range.put_Value2(new_value);
start_range.ReleaseDispatch();
write_range.ReleaseDispatch();
}//
void IllusionExcelFile::ShowInExcel(BOOL bShow)
{
excel_application_.put_Visible(bShow);
excel_application_.put_UserControl(bShow);
}//返回打开的EXCEL文件名称
CString IllusionExcelFile::GetOpenFileName()
{
return open_excel_file_;
}//取得打开sheet的名称
CString IllusionExcelFile::GetLoadSheetName()
{
return excel_work_sheet_.get_Name();
}//取得列的名称，比如27->AA
char *IllusionExcelFile::GetColumnName(long icolumn)
{
static char column_name[];
size_t str_len = ; while(icolumn > )
{
int num_data = icolumn % ;
icolumn /= ;
if (num_data == )
{
num_data = ;
icolumn--;
}
column_name[str_len] = (char)((num_data-) + 'A' );
str_len ++;
}
column_name[str_len] = '\0';
//反转
_strrev(column_name); return column_name;
}//预先加载
void IllusionExcelFile::PreLoadSheet()
{ CRange used_range; used_range = excel_work_sheet_.get_UsedRange(); VARIANT ret_ary = used_range.get_Value2();
if (!(ret_ary.vt & VT_ARRAY))
{
return;
}
//
ole_safe_array_.Clear();
ole_safe_array_.Attach(ret_ary);
}void IllusionExcelFile::ClearExcelValue(long iRow, long iColumn)
{ CRange start_range = excel_work_sheet_.get_Range(COleVariant(_T("A1")),covOptional);
CRange write_range = start_range.get_Offset(COleVariant((long)iRow -),COleVariant((long)iColumn -) );
write_range.Clear();
start_range.ReleaseDispatch();
write_range.ReleaseDispatch();}void IllusionExcelFile::CreateExcelFile(CString filename)
{
CApplication app;
CWorkbook book;
CWorkbooks books;
CWorksheet sheet;
CWorksheets sheets;
CRange range;
CFont font;
CRange cols;
LPDISPATCH lpDisp; COleVariant covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR);
if (!app.CreateDispatch(_T("Excel.Application")))
{
AfxMessageBox(_T("无法创建Excel应用！")); }
books = app.get_Workbooks();
//打开Excel，其中pathname为Excel表的路径名 book = books.Add(covOptional);
sheets = book.get_Worksheets();
book.SaveAs(COleVariant(filename),
covOptional,
covOptional,
covOptional,
covOptional,
covOptional,
,
covOptional,
covOptional,
covOptional,
covOptional,
covOptional);
book.Close(COleVariant(short(FALSE)),COleVariant(filename),covOptional);
books.Close();
//app.put_UserControl(TRUE);
range.ReleaseDispatch();
sheet.ReleaseDispatch();
sheets.ReleaseDispatch();
book.ReleaseDispatch();
books.ReleaseDispatch();
app.ReleaseDispatch(); }
最后编译时可能会提示你忘记添加#include “StdAfx.h”，只要在 BasicExcelOperate.c文件的最上方添加#include “StdAfx.h”，就可以了
参考：http://blog.csdn.net/superbfly/article/details/18040445
