概要
由于要用到许多表格和图形，所以选择了excel2007,为了更好的操作excel2007,所以选择了vc2008.
本来想用excel12.h，相关方法实现的。后来在发现生成excel12.h比较费时间，参考资源[1]。这里对Excel OLE对象的调用方式采用了MFC的type Lib了。
代码段均在WinXP+SP3Excel2007 VS2008+SP1环境下调试通过。
正文
准备工作
通过type Lib方式引入Excel OLE封装源码文件后，对要用到的头文件做修改。注释import语句，添加Excel对象引用代码（参考资源[2]，[3]），注释掉冲突的代码。
要引用哪对象就添加哪些对象的头文件，一般下面这些头文件是会被用到的。
#include "CApplication.h"
#include "CWorkbooks.h"
#include "CWorkbook.h"
#include "CWorksheets.h"
#include "CWorksheet.h"
#include "CRange.h"
#include "CSheets.h"
详细代码1:CExcelReader.h
#if _MSC_VER > 1000
#pragma once
#endif
// CExcelReader
#include "CApplication.h"
#include "CWorkbooks.h"
#include "CWorkbook.h"
#include "CWorksheets.h"
#include "CWorksheet.h"
#include "CRange.h"
#include "CSheets.h"
using namespace Office;
using namespace Excel ;
class CExcelReader
{
public:
CExcelReader(){ CoInitialize(NULL);};
~CExcelReader(); //
void OpenRead(const CString& file); // open and read the specified file
void Init_App(); // init the excel app
void Quit_App(); // quit the excel App
void Close(); // close the current document
long GetCurrentSheetRowCount();
long GetCurrentSheetColCount();
VARIANT ReadCell( long rowindex, long columnindex); // read the specified cell
CString ReadCellAsString(long rowindex, long columnindex);
double ReadCellAsDouble(long rowindex, long columnindex);
//CString GetFileName() const { return m_filename; }
//************************************
//long GetTotalsheetNumber() const {return m_sheetNumber; }
//************************************
// load a sheet by the name of the sheet
BOOL LoadSheetByName(const CString& sheetname);
//************************************
// load a sheet by the index of the sheet, starts from 1
BOOL LoadSheetByIndex(long index);
private:
CString m_filename; // file name to be opened. the reader should will be associated with one file at a time.
long m_sheetNumber; // numbers of sheets of the excel file
CApplication excelApp; // the excel app
CWorkbooks oBooks; // the work books container
CWorkbook oBook; // the work book
CSheets oSheets; // the sheets container
CWorksheet oSheet; // a single sheet
CRange xlsrange;
};
详细代码2:CExcelReader.cpp
#include "StdAfx.h"
#include "ExcelReader.h"
#include "Shlwapi.h"
#pragma comment(lib, "shlwapi")
void CExcelReader::Init_App()
{
try
{
if ( !excelApp.CreateDispatch(_T("Excel.Application")) ) // attach to the excel application
{
AfxMessageBox(_T("Failed to Start the Excel application"));
return;
}
excelApp.put_Visible(FALSE);
excelApp.put_DisplayAlerts(FALSE);
oBooks = excelApp.get_Workbooks();
}
catch (COleDispatchException* e)
{
AfxMessageBox(e->m_strDescription);
}
catch (COleException* e)
{
TCHAR buff[1024];
memset(buff, 0x00, sizeof(buff));
e->GetErrorMessage(buff, 1024);
AfxMessageBox(buff);
}
}
void CExcelReader::OpenRead( const CString& filename )
{
// check whether the file exists
if ( !PathFileExists(filename) )
{
AfxMessageBox(_T("File does not exist"));
return;
}
if (m_filename.CompareNoCase(filename) == 0)
{
AfxMessageBox(_T("The file has already been opened."));
return;
}
// try to open the file
COleVariant VOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
COleVariant VTrue((short)TRUE);
COleVariant VFalse((short)FALSE);
try
{
// open and read only
oBooks.Open(filename, VFalse, VOptional, VOptional, VOptional, VOptional, VOptional,
VOptional, VOptional, VOptional, VOptional, VOptional, VOptional, VOptional, VOptional);
m_filename = filename;
COleVariant index(short(1));
oBook = oBooks.get_Item(index);
oSheets = oBook.get_Sheets();
m_sheetNumber = oSheets.get_Count();
}
catch (COleDispatchException* e)
{
AfxMessageBox(e->m_strDescription);
return;
}
catch(COleException* e)
{
TCHAR buff[1024];
memset(buff, 0x00, sizeof(buff));
e->GetErrorMessage(buff, 1024);
AfxMessageBox(buff);
}
}
BOOL CExcelReader::LoadSheetByName( const CString& sheetname )
{
if (oSheets.m_lpDispatch == NULL)
{
return FALSE;
}
try
{
oSheet = oSheets.get_Item(COleVariant(sheetname));
}
catch(COleDispatchException* e)
{
AfxMessageBox(e->m_strDescription);
return FALSE;
}
return TRUE;
}
BOOL CExcelReader::LoadSheetByIndex(long index )
{
if (oSheets.m_lpDispatch == NULL)
{
return FALSE;
}
try
{
oSheet = oSheets.get_Item(COleVariant(index));
}
catch (COleDispatchException* e)
{
AfxMessageBox(e->m_strDescription);
return FALSE;
}
return TRUE;
}
VARIANT CExcelReader::ReadCell( long rowindex, long columnindex )
{
VARIANT result;
VariantInit(&result);
try
{
xlsrange = oSheet.get_Cells();
VARIANT item;
item = xlsrange.get_Item(COleVariant(rowindex), COleVariant(columnindex));
if (item.vt == VT_DISPATCH)
{
xlsrange.AttachDispatch(item.pdispVal);
if (xlsrange.m_lpDispatch != NULL)
{
result = xlsrange.get_Value2();
}
}
}
catch (COleDispatchException* e)
{
AfxMessageBox(e->m_strDescription);
}
catch(COleException* e)
{
TCHAR buff[1024];
memset(buff, 0x00, sizeof(buff));
e->GetErrorMessage(buff, 1024);
AfxMessageBox(buff);
}
return result;
}
void CExcelReader::Close()
{
oBook.DetachDispatch();
oSheet.DetachDispatch();
xlsrange.DetachDispatch();
oBooks.Close();
oBooks.ReleaseDispatch();
}
void CExcelReader::Quit_App()
{
excelApp.Quit();
}
long CExcelReader::GetCurrentSheetRowCount()
{
if (oSheet.m_lpDispatch == NULL)
{
return -1;
}else
{
try
{
CRange usedrange = oSheet.get_UsedRange(); // get used range
CRange rows = usedrange.get_Rows(); // get used rows
return rows.get_Count();
}
catch (COleDispatchException* e)
{
AfxMessageBox(e->m_strDescription);
return -1;
}
catch(COleException* e)
{
TCHAR buff[256];
memset(buff, 0x00, sizeof(buff));
e->GetErrorMessage(buff, 256);
AfxMessageBox(buff);
return -1;
}
}
}
long CExcelReader::GetCurrentSheetColCount()
{
if (oSheet.m_lpDispatch == NULL)
{
return 0;
}else
{
try
{
CRange usedrange = oSheet.get_UsedRange(); // get used range
CRange cols = usedrange.get_Columns();
return cols.get_Count();
}
catch (COleDispatchException* e)
{
AfxMessageBox(e->m_strDescription);
return -1;
}
catch (COleException* e)
{
TCHAR buff[256];
memset(buff, 0x00, sizeof(buff));
e->GetErrorMessage(buff, 256);
AfxMessageBox(buff);
return -1;
}
}
}
CExcelReader::~CExcelReader()
{
//Close();
//Quit_App();
//CoUninitialize();
}
/*
VARIANT CExcelReader::ReadCell( long rowindex, long columnindex, const VARIANT& vartype )
{
VARIANT result;
VariantInit(&result);
try
{
xlsrange = oSheet.GetCells();
VARIANT item;
item = xlsrange.GetItem(COleVariant(rowindex), COleVariant(columnindex));
if (item.vt == VT_DISPATCH)
{
xlsrange.AttachDispatch(item.pdispVal);
if (xlsrange.m_lpDispatch != NULL)
{
result = xlsrange.GetValue(vartype);
}
}
}
catch (COleDispatchException* e)
{
AfxMessageBox(e->m_strDescription);
}
catch(COleException* e)
{
TCHAR buff[1024];
memset(buff, 0x00, sizeof(buff));
e->GetErrorMessage(buff, 1024);
AfxMessageBox(buff);
}
return result;
}*/
CString CExcelReader::ReadCellAsString( long rowindex, long columnindex )
{
CString result;
VARIANT var = ReadCell(rowindex, columnindex);
if (var.vt == VT_BSTR)
result = var.bstrVal;
else if (var.vt == VT_R8)
result.Format(var.bstrVal);
return result;
}
double CExcelReader::ReadCellAsDouble( long rowindex, long columnindex )
{
double result;
VARIANT var = ReadCell(rowindex, columnindex);
if(var.vt == VT_R8)
result = var.dblVal;
return result;
}
详细代码3:CExcelReader的调用
long iCols=0;
long iRows=0;
CString strMsg;
CExcelReader m_myExcel;
CString strFile;
strFile="E:\\vc_test\\test1.xls";
m_myExcel.OpenRead(strFile);
m_myExcel.LoadSheetByIndex(1);
iRows=m_myExcel.GetCurrentSheetRowCount();
iCols=m_myExcel.GetCurrentSheetColCount();
strMsg.Format(_T("the sheet has data %d rows %d Cols"), iRows, iCols);
AfxMessageBox(strMsg);
m_myExcel.Close();
m_myExcel.Quit_App();
-------辛苦了好多个小时，终于开了一个头。和朋友们分享一下。---如有疑问可以联系我.----toter@163.com
参考资料[1]:
纯C语言集成Excel遇到的问题及解决
http://blog.csdn.net/lee353086/article/details/6117762
参考资料[2]:
vc 如何操作 excel2003（注意一定是2003） 编程
http://zhidao.baidu.com/question/40379986.html?si=1
参考资料[3]:
VC2008操作Excel2007总结
http://blog.csdn.net/lee353086/article/details/6091941
参考资料[4]:
vS80 開啟Excel問題？
http://www.programmer-club.com.tw/ShowSameTitleN/vc/32776.html