VS2010通过OLE操作Excel2010
来源:互联网 发布:巴克利生涯数据 编辑:程序博客网 时间:2024/05/16 01:10
我使用的语言是C++,网上有许多这方面的例子,但由于VS与Office版本的问题,都需要一些调整,下面是我在使用时遇到的一些问题集解决方法:
操作步骤:
a. project->add class->MFC class from typelib 导入Excel.exe,一般都在C:/Program Files/Microsoft Office/Office14路径下;
b. 选中以下几项_Application,_WorkSheet,_WorkBook,WorkSheets,WorkBooks,Range,然后导入;
c. 导入后在工程中添加CApplication,CWorkSheet,CWorkBook,CWorkSheets,CWorkBooks,CRange这些类;
然后需要把这些类的头文件中的第一句话 #import ".......EXCEL.EXE" nonamespace 删除;
引入之后如果编译遇到错误,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(),解决了!
d.添加头文件OperateExcelFile.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();
- 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();
- };
e.添加cpp文件OperateExcelFile.cpp
- #include "StdAfx.h"
- #include "IllusionExcelFile.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,
- 0,
- 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[2];
- VARIANT val;
- read_address[0] = irow;
- read_address[1] = 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 = 0;
- 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[2];
- VARIANT val;
- read_address[0] = irow;
- read_address[1] = 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[2];
- VARIANT val;
- read_address[0] = irow;
- read_address[1] = 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 -1),COleVariant((long)icolumn -1) );
- 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 -1),COleVariant((long)icolumn -1) );
- 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[64];
- size_t str_len = 0;
- while(icolumn > 0)
- {
- int num_data = icolumn % 26;
- icolumn /= 26;
- if (num_data == 0)
- {
- num_data = 26;
- icolumn--;
- }
- column_name[str_len] = (char)((num_data-1) + '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);
- }
f.在程序中包含OperateExcelFile.h文件就可以定义对象简单操作Excel文件了
http://blog.csdn.net/superbfly/article/details/18040445
- VS2010通过OLE操作Excel2010
- VS2010通过OLE操作Excel2010
- VS2010通过OLE操作Excel2010
- VS2010对Excel2010进行操作
- 通过ole 操作Excel
- VS2010环境使用MFC操作读取excel2010文件
- Perl通过WIN32 OLE来操作EXCEL
- ABAP--通过OLE操作OFFICE(代码)
- ABAP--通过OLE操作OFFICE(代码)
- Delphi中通过OLE来操作Excel
- pb 通过ole操作excel常用函数
- VC配置通过OLE操作EXCEL
- VC++操作excel2010
- java操作excel2010
- vc++操作excel2010
- delphi通过OLE对word进行单元格合并操作
- VS2010 C++ 操作Excel表格的编程实现(OLE/COM)
- 通过 OLE 上传资料
- [c]c语言一些头文件了解和语言编译小case
- 【廖雪峰Python习题集】面向对象高级编程
- C# Signalr集线器入门知识
- jhipser微服务架构介绍
- 浅谈Java中的equals和==
- VS2010通过OLE操作Excel2010
- JAVA与XML转换
- 常用的命令
- Fluid Mask 3.3.16 在 mac OS X Sierra (10.12) 和 High Sierra (10.13)系统中无法启动和不能作为PhotoShop 插件使用问题修复
- Maven之(二)Maven生命周期
- 疯狂Activiti6连载(4)流程引擎数据源配置
- 566. Reshape the Matrix
- 开源数据库迁移工具 – Flyway
- find命令 -- 基于时间查找