VC中如何操作Excel文件？

[RiskyWei]

希望在VC里建立一个新Excel文件，并对其进行数据添加、修改以及删除操作，该如何实现？我导入Excel9.olb类型库后，不知道该怎么操作，请大家指点，最好给出示列代码，谢谢了

[snap2008cn]

我想下面的link对你有所帮助
http://www.vckbase.com/document/viewdoc/?id=231

[babam]

微软的网站里有例子啊！既然知道导入excel.olb就应该看见了啊

[yhthy]

下面是包装过的在vc里面操作excel的类。非常好用！
// Excel.h: interface for the CExcel class.
//
//////////////////////////////////////////////////////////////////////

#if !defined(AFX_EXCEL_H__0A939963_A41F_11D4_9EFB_0080C8F5E7B3__INCLUDED_)
#define AFX_EXCEL_H__0A939963_A41F_11D4_9EFB_0080C8F5E7B3__INCLUDED_

#if _MSC_VER > 1000
#pragma once
#endif // _MSC_VER > 1000

#define MAX_DISP_ARGS 20

class CExcel
{
public:
CExcel();
virtual ~CExcel();

BOOL StartExcel(BOOL bShow);
void EndExcel();
BOOL OpenFile(CString strPath);
BOOL NewFile();
BOOL SaveAs(CString strPath);
void SaveFile();
void CloseFile();
BOOL OpenWorksheet(CString strName);
BOOL AddWorksheet(CString strName, CString strBefore="");
void CloseWorksheet();
CString ReadCell(int nRow, int nCol);
BOOL WriteCell(int nRow, int nCol, CString strValue);
void Scroll(long lTopLine);

private:
void ShowExcel(BOOL bShow);

//COM Operation
BOOL SetProperty(IDispatch* pIDispatch, LPOLESTR szMember, const VARIANT& value);
BOOL GetProperty(IDispatch* pIDispatch, LPOLESTR szMember, VARIANT& value);
BOOL IndexedProperty(IDispatch* pIDispatch, LPOLESTR szMember, VARIANT& value);
BOOL Execute(IDispatch* pIDispatch, LPOLESTR szMember, VARIANT* pResult);

private:
IDispatch* m_pExcel;
IDispatch* m_pWorkbooks;
IDispatch* m_pWorkbook;
IDispatch* m_pSheet;
IDispatch* m_pCell;

DISPPARAMS m_dispparams;
};

#endif // !defined(AFX_EXCEL_H__0A939963_A41F_11D4_9EFB_0080C8F5E7B3__INCLUDED_)

[yhthy]

实现：
// Excel.cpp: implementation of the CExcel class.
//
//////////////////////////////////////////////////////////////////////

#include "stdafx.h"
#include "atlbase.h"
#include "Excel.h"

#ifdef _DEBUG
#undef THIS_FILE
static char THIS_FILE[]=__FILE__;
#define new DEBUG_NEW
#endif

//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////

CExcel::CExcel()
{
m_pExcel = NULL;
m_pWorkbooks = NULL;
m_pWorkbook = NULL;
m_pSheet = NULL;
m_pCell = NULL;

m_dispparams.cArgs = 0;
m_dispparams.cNamedArgs = 0;
m_dispparams.rgvarg = new VARIANTARG[MAX_DISP_ARGS];
m_dispparams.rgdispidNamedArgs = new DISPID[MAX_DISP_ARGS+1];
}

CExcel::~CExcel()
{
CloseWorksheet();

CloseFile();

EndExcel();

delete[] m_dispparams.rgdispidNamedArgs;
delete[] m_dispparams.rgvarg;
}

inline BOOL CExcel::SetProperty(IDispatch* pIDispatch, LPOLESTR szMember, const VARIANT& value)
{
HRESULT hr;
DISPID dispid;

m_dispparams.rgvarg[0] = value;
m_dispparams.rgdispidNamedArgs[0] = DISPID_PROPERTYPUT;
m_dispparams.cArgs = 1;
m_dispparams.cNamedArgs = 1;

hr = pIDispatch->GetIDsOfNames(IID_NULL,&szMember,1,LOCALE_USER_DEFAULT,&dispid);

if (SUCCEEDED(hr))
{
hr = pIDispatch->Invoke(dispid,IID_NULL,LOCALE_USER_DEFAULT,
DISPATCH_PROPERTYPUT,&m_dispparams,NULL,NULL,NULL);

if (SUCCEEDED(hr))
{
return TRUE;
}
}

return FALSE;
}

inline BOOL CExcel::GetProperty(IDispatch* pIDispatch, LPOLESTR szMember, VARIANT& value)
{
HRESULT hr;
DISPID dispid;

m_dispparams.cArgs = 0;
m_dispparams.cNamedArgs = 0;

hr = pIDispatch->GetIDsOfNames(IID_NULL,&szMember,1,LOCALE_USER_DEFAULT,&dispid);

if (SUCCEEDED(hr))
{
hr = pIDispatch->Invoke(dispid,IID_NULL,LOCALE_USER_DEFAULT,
DISPATCH_PROPERTYGET,&m_dispparams,&value,NULL,NULL);

if (SUCCEEDED(hr))
{
return TRUE;
}
}

return FALSE;
}

inline BOOL CExcel::IndexedProperty(IDispatch* pIDispatch, LPOLESTR szMember, VARIANT& value)
{
HRESULT hr;
DISPID dispid;

hr = pIDispatch->GetIDsOfNames(IID_NULL,&szMember,1,LOCALE_USER_DEFAULT,&dispid);

if (SUCCEEDED(hr))
{
hr = pIDispatch->Invoke(dispid,IID_NULL,LOCALE_USER_DEFAULT,
DISPATCH_PROPERTYGET,&m_dispparams,&value,NULL,NULL);

if (SUCCEEDED(hr))
{
return TRUE;
}
}

return FALSE;
}

inline BOOL CExcel::Execute(IDispatch* pIDispatch, LPOLESTR szMember, VARIANT* pResult)
{
HRESULT hr;
DISPID dispid;

hr = pIDispatch->GetIDsOfNames(IID_NULL,&szMember,1,LOCALE_USER_DEFAULT,&dispid);

if (SUCCEEDED(hr))
{
EXCEPINFO excep;
excep.pfnDeferredFillIn = NULL;
unsigned int uiArgErr;

hr = pIDispatch->Invoke(dispid,IID_NULL,LOCALE_USER_DEFAULT,
DISPATCH_METHOD,&m_dispparams,pResult,&excep,&uiArgErr);

if (SUCCEEDED(hr))
{
return TRUE;
}
else
{
// free exception structure information
SysFreeString(excep.bstrSource);
SysFreeString(excep.bstrDescription);
SysFreeString(excep.bstrHelpFile);

return FALSE;
}
}
else
{
return FALSE;
}
}

BOOL CExcel::StartExcel(BOOL bShow)
{
EndExcel();

CLSID clsExcel;

/* Obtain the CLSID that identifies EXCEL.APPLICATION
* This value is universally unique to Excel versions 5 and up, and
* is used by OLE to identify which server to start. We are obtaining
* the CLSID from the ProgID.
*/
HRESULT hr;
if (FAILED((hr=CLSIDFromProgID(L"Excel.Application", &clsExcel))))
{
AfxMessageBox(_T("Cannot start Excel."));
return FALSE;
}

// start a new copy of Excel, grab the IDispatch interface
if (FAILED((hr=CoCreateInstance(clsExcel, NULL, CLSCTX_LOCAL_SERVER,
IID_IDispatch, reinterpret_cast<void**>(&m_pExcel)))))
{
AfxMessageBox(_T("Cannot start Excel."));
return FALSE;
}

ShowExcel(bShow);

return TRUE;
}

void CExcel::EndExcel()
{
if (m_pExcel!=NULL)
{
m_dispparams.cArgs = 0;
m_dispparams.cNamedArgs = 0;
Execute(m_pExcel,L"Quit",NULL);

m_pExcel->Release();
m_pExcel = NULL;
}

return;
}

void CExcel::ShowExcel(BOOL bShow)
{
VARIANT value;
value.vt = VT_BOOL;

if (bShow)
{
value.boolVal = VARIANT_TRUE;
}
else
{
value.boolVal = VARIANT_FALSE;
}

SetProperty(m_pExcel,L"Visible",value);
}

BOOL CExcel::OpenFile(CString strPath)
{
CloseFile();

VARIANT result;

if (! GetProperty(m_pExcel,L"Workbooks",result))
{
CString strError;
strError.Format(_T("Cannot open %s."),strPath);
AfxMessageBox(strError);
return FALSE;
}

m_pWorkbooks = result.pdispVal;

m_dispparams.cArgs = 1;
m_dispparams.cNamedArgs = 0;
m_dispparams.rgvarg[0].vt = VT_BSTR;
m_dispparams.rgvarg[0].bstrVal = SysAllocString(CComBSTR(strPath));

if (! Execute(m_pWorkbooks,L"Open",&result))
{
SysFreeString(m_dispparams.rgvarg[0].bstrVal);

CString strError;
strError.Format(_T("Cannot open %s."),strPath);
AfxMessageBox(strError);
return FALSE;
}

SysFreeString(m_dispparams.rgvarg[0].bstrVal);
m_pWorkbook = result.pdispVal;

return TRUE;
}

[yhthy]

实现 part2：

BOOL CExcel::NewFile()
{
CloseFile();

VARIANT result;

if (! GetProperty(m_pExcel,L"Workbooks",result))
{
CString strError;
strError.Format(_T("Cannot add workbooks"));
AfxMessageBox(strError);
return FALSE;
}

m_pWorkbooks = result.pdispVal;

m_dispparams.cArgs = 0;
m_dispparams.cNamedArgs = 0;

if (! Execute(m_pWorkbooks,L"Add",&result))
{
CString strError;
strError.Format(_T("Cannot add new work book"));
AfxMessageBox(strError);
return FALSE;
}

m_pWorkbook = result.pdispVal;

return TRUE;
}

void CExcel::SaveFile()
{
if (m_pWorkbook!=NULL)
{
m_dispparams.cArgs = 0;
m_dispparams.cNamedArgs = 0;
Execute(m_pWorkbook,L"Save",NULL);
}
}

void CExcel::CloseFile()
{
if (m_pWorkbook!=NULL)
{
VARIANT value;
value.vt = VT_BOOL;
value.boolVal = VARIANT_TRUE;
SetProperty(m_pWorkbook,L"Saved",value);

m_dispparams.cArgs = 0;
m_dispparams.cNamedArgs = 0;
Execute(m_pWorkbook,L"Close",NULL);

m_pWorkbook->Release();
m_pWorkbook = NULL;
}

if (m_pWorkbooks!=NULL)
{
m_dispparams.cArgs = 0;
m_dispparams.cNamedArgs = 0;
Execute(m_pWorkbooks,L"Close",NULL);

m_pWorkbooks->Release();
m_pWorkbooks = NULL;
}
}

BOOL CExcel::SaveAs(CString strPath)
{
if (m_pWorkbook!=NULL)
{
m_dispparams.cArgs = 1;
m_dispparams.cNamedArgs = 0;
m_dispparams.rgvarg[0].vt = VT_BSTR;
m_dispparams.rgvarg[0].bstrVal = SysAllocString(CComBSTR(strPath));
BOOL ret = Execute(m_pWorkbook,L"SaveAs",NULL);
SysFreeString(m_dispparams.rgvarg[0].bstrVal);
if ( ret == FALSE )
AfxMessageBox("Cannot save file");
return ret;
}
return FALSE;
}

BOOL CExcel::OpenWorksheet(CString strName)
{
CloseWorksheet();

VARIANT result;

m_dispparams.cArgs = 1;
m_dispparams.cNamedArgs = 0;
m_dispparams.rgvarg[0].vt = VT_BSTR;
m_dispparams.rgvarg[0].bstrVal = SysAllocString(CComBSTR(strName));

if (! IndexedProperty(m_pWorkbook,L"Worksheets",result))
{
SysFreeString(m_dispparams.rgvarg[0].bstrVal);
CString strError;
strError.Format(_T("Cannot open Worksheet %s."),strName);
AfxMessageBox(strError);
return FALSE;
}

SysFreeString(m_dispparams.rgvarg[0].bstrVal);
m_pSheet = result.pdispVal;


m_dispparams.cArgs = 0;
m_dispparams.cNamedArgs = 0;
Execute(m_pSheet,L"Activate",NULL);

return TRUE;
}

BOOL CExcel::AddWorksheet(CString strName, CString strBefore/*=""*/)
{
VARIANT result;
IDispatch* pWorksheets;

if (! GetProperty(m_pWorkbook,L"Worksheets",result))
{
CString strError;
strError.Format(_T("Cannot open worksheets"));
AfxMessageBox(strError);
return FALSE;
}
pWorksheets = result.pdispVal;

if (!strBefore.IsEmpty())
{
if (!OpenWorksheet(strBefore))
{
pWorksheets->Release();
return FALSE;
}
}
m_dispparams.cArgs = 0;
m_dispparams.cNamedArgs = 0;
Execute(pWorksheets, L"Add", &result);

if (m_pSheet)
m_pSheet->Release();
m_pSheet = result.pdispVal;

// rename
VARIANT value;
value.vt = VT_BSTR;
value.bstrVal = SysAllocString(CComBSTR(strName));
SetProperty(m_pSheet, L"Name", value);
SysFreeString(value.bstrVal);

pWorksheets->Release();

return TRUE;
}

void CExcel::CloseWorksheet()
{
if (m_pSheet!=NULL)
{
m_pSheet->Release();
m_pSheet = NULL;
}
}

CString CExcel::ReadCell(int nRow, int nCol)
{
if (m_pSheet == NULL)
return ("");

VARIANT result;

m_dispparams.cArgs = 2;
m_dispparams.cNamedArgs = 0;
m_dispparams.rgvarg[0].vt = VT_I4;
m_dispparams.rgvarg[0].lVal = nCol;
m_dispparams.rgvarg[1].vt = VT_I4;
m_dispparams.rgvarg[1].lVal = nRow;

if (! IndexedProperty(m_pSheet,L"Cells",result))
{
CString strError;
strError.Format(_T("Cannot open Cell(%d,%d)."),nRow,nCol);
AfxMessageBox(strError);
return("");
}
m_pCell = result.pdispVal;
GetProperty(m_pCell,L"Value",result);
CString strValue = "";
if ( result.vt == VT_BSTR )
{
strValue = result.bstrVal;
SysFreeString(result.bstrVal);
}
m_pCell->Release();

return strValue;
}

BOOL CExcel::WriteCell(int nRow, int nCol, CString strValue)
{
if (m_pSheet == NULL)
return FALSE;

VARIANT result;

m_dispparams.cArgs = 2;
m_dispparams.cNamedArgs = 0;
m_dispparams.rgvarg[0].vt = VT_I4;
m_dispparams.rgvarg[0].lVal = nCol;
m_dispparams.rgvarg[1].vt = VT_I4;
m_dispparams.rgvarg[1].lVal = nRow;

if (! IndexedProperty(m_pSheet,L"Cells",result))
{
CString strError;
strError.Format(_T("Cannot open Cell(%d,%d)."),nRow,nCol);
AfxMessageBox(strError);
return FALSE;
}
m_pCell = result.pdispVal;
VARIANT value;
value.vt = VT_BSTR;
value.bstrVal = SysAllocString(CComBSTR(strValue));
SetProperty(m_pCell,L"Value",value);
SysFreeString(value.bstrVal);
m_pCell->Release();

return TRUE;
}


void CExcel::Scroll(long lTopLine)
{
VARIANT result;

GetProperty(m_pExcel,L"ActiveWindow",result);
IDispatch* pActiveWindow = result.pdispVal;

VARIANT value;
value.vt = VT_I4;
value.lVal = lTopLine;
SetProperty(pActiveWindow,L"ScrollRow",value);

pActiveWindow->Release();

return;
}