Working MS Excel through Win32 API.
Vasu
How to Connect, insert, retrive, lock cell, change color etc.
Working with sheets (i.e Insert sheet, delete, move, rename sheet)
Define and create names (Insert->name->define).
Help and Suggestions Warm welcome.
Vasu.
Gernot Frisch
Header file:
// GFExcel.h: Schnittstelle für die Klasse GFExcel.
//
//////////////////////////////////////////////////////////////////////
#if
!defined(AFX_GFEXCEL_H__EFBD2F80_D4E8_11D5_83AB_00C04F7DBADE__INCLUDED
_)
#define AFX_GFEXCEL_H__EFBD2F80_D4E8_11D5_83AB_00C04F7DBADE__INCLUDED_
#if _MSC_VER > 1000
#pragma once
#endif // _MSC_VER > 1000
#include <ole2.h>
class GFExcel
{
public:
BOOL New();
void Quit();
BOOL GetCellName(LPSTR name, int x, int y, bool DollarX=false, bool
DollarY=false);
BOOL OpenBook(LPCTSTR file);
BOOL SaveAs(LPCTSTR filename);
BOOL GetSavedState();
void SetSavedState(BOOL Saved);
BOOL CellsSet(int x, int y, LPCTSTR value);
BOOL CellsGet(int x, int y, LPSTR value);
void Close();
int Init(bool Visible=TRUE);
GFExcel();
virtual ~GFExcel();
protected:
HRESULT GFExcel::AutoWrap(int autoType, VARIANT *pvResult, IDispatch
*pDisp, LPOLESTR ptName, int cArgs...);
struct PROPERTIES
{
IDispatch *pXlApp; // Excel.exe
IDispatch *pXlBooks; // Ptr To MainFrame -> Can open new Workbooks
IDispatch *pXlBook; // A Workbook. Has more Sheets (Z-Layers)
IDispatch *pXlSheet; // What U see. Has X and Y
} m_Instance;
BOOL m_NeededCOMInit; // Does App have to open COM - so close it
later
};
#endif //
!defined(AFX_GFEXCEL_H__EFBD2F80_D4E8_11D5_83AB_00C04F7DBADE__INCLUDED
_)
// cpp - file
// GFExcel.cpp: Implementierung der Klasse GFExcel.
//
//////////////////////////////////////////////////////////////////////
#include "stdafx.h"
#include "GFExcel.h"
#include <comdef.h>
//////////////////////////////////////////////////////////////////////
// Konstruktion/Destruktion
//////////////////////////////////////////////////////////////////////
// -------------------------------------------------------------------
--------- //
// Construct
// -------------------------------------------------------------------
--------- //
GFExcel::GFExcel()
{
m_NeededCOMInit=TRUE;
if(CoInitialize(NULL)!=S_OK) m_NeededCOMInit=FALSE;
}
// -------------------------------------------------------------------
--------- //
// Destruct
// -------------------------------------------------------------------
--------- //
GFExcel::~GFExcel()
{
// Uninitialize COM for this thread...
if (m_NeededCOMInit)
CoUninitialize();
}
// -------------------------------------------------------------------
--------- //
// AutoWrap() - Automation helper function...
// -------------------------------------------------------------------
--------- //
HRESULT GFExcel::AutoWrap(int autoType, VARIANT *pvResult, IDispatch
*pDisp, LPOLESTR ptName, int cArgs...)
{
// Begin variable-argument list...
va_list marker;
va_start(marker, cArgs);
if(!pDisp)
{
MessageBox(NULL, "NULL IDispatch passed to AutoWrap()", "Error",
0x10010);
_exit(0);
}
// Variables used...
DISPPARAMS dp =
{
NULL, NULL, 0, 0
};
DISPID dispidNamed = DISPID_PROPERTYPUT;
DISPID dispID;
HRESULT hr;
char buf[200];
char szName[200];
// Convert down to ANSI
WideCharToMultiByte(CP_ACP, 0, ptName, -1, szName, 256, NULL, NULL);
// Get DISPID for name passed...
hr = pDisp->GetIDsOfNames(IID_NULL, &ptName, 1, LOCALE_USER_DEFAULT,
&dispID);
if(FAILED(hr))
{
sprintf(buf, "IDispatch::GetIDsOfNames(\"%s\") failed w/err
0x%08lx", szName, hr);
MessageBox(NULL, buf, "AutoWrap()", 0x10010);
_exit(0);
return hr;
}
// Allocate memory for arguments...
VARIANT *pArgs = new VARIANT[cArgs+1];
// Extract arguments...
for(int i=0; i<cArgs; i++)
{
pArgs[i] = va_arg(marker, VARIANT);
}
// Build DISPPARAMS
dp.cArgs = cArgs;
dp.rgvarg = pArgs;
// Handle special-case for property-puts!
if(autoType & DISPATCH_PROPERTYPUT)
{
dp.cNamedArgs = 1;
dp.rgdispidNamedArgs = &dispidNamed;
}
// Make the call!
hr = pDisp->Invoke(dispID, IID_NULL, LOCALE_SYSTEM_DEFAULT, autoType,
&dp, pvResult, NULL, NULL);
if(FAILED(hr))
{
sprintf(buf, "IDispatch::Invoke(\"%s\"=%08lx) failed w/err 0x%08lx",
szName, dispID, hr);
MessageBox(NULL, buf, "AutoWrap()", 0x10010);
switch(hr)
{
case DISP_E_BADPARAMCOUNT:
MessageBox(NULL, "DISP_E_BADPARAMCOUNT", "Error:", 0x10010); break;
case DISP_E_BADVARTYPE:
MessageBox(NULL, "DISP_E_BADVARTYPE", "Error:", 0x10010); break;
case DISP_E_EXCEPTION:
MessageBox(NULL, "DISP_E_EXCEPTION", "Error:", 0x10010); break;
case DISP_E_MEMBERNOTFOUND:
MessageBox(NULL, "DISP_E_MEMBERNOTFOUND", "Error:", 0x10010);
break;
case DISP_E_NONAMEDARGS:
MessageBox(NULL, "DISP_E_NONAMEDARGS", "Error:", 0x10010); break;
case DISP_E_OVERFLOW:
MessageBox(NULL, "DISP_E_OVERFLOW", "Error:", 0x10010); break;
case DISP_E_PARAMNOTFOUND:
MessageBox(NULL, "DISP_E_PARAMNOTFOUND", "Error:", 0x10010); break;
case DISP_E_TYPEMISMATCH:
MessageBox(NULL, "DISP_E_TYPEMISMATCH", "Error:", 0x10010); break;
case DISP_E_UNKNOWNINTERFACE:
MessageBox(NULL, "DISP_E_UNKNOWNINTERFACE", "Error:", 0x10010);
break;
case DISP_E_UNKNOWNLCID:
MessageBox(NULL, "DISP_E_UNKNOWNLCID", "Error:", 0x10010); break;
case DISP_E_PARAMNOTOPTIONAL:
MessageBox(NULL, "DISP_E_PARAMNOTOPTIONAL", "Error:", 0x10010);
break;
}
// _exit(0);
return hr;
}
// End variable-argument section...
va_end(marker);
delete [] pArgs;
return hr;
}
// -------------------------------------------------------------------
--------- //
// Open a new Excel Window and create a new worksheet
// -------------------------------------------------------------------
--------- //
int GFExcel::Init(bool Visible)
{
// Get CLSID for our server...
CLSID clsid;
HRESULT hr = CLSIDFromProgID(L"Excel.Application", &clsid);
if(FAILED(hr))
{
::MessageBox(NULL, "CLSIDFromProgID() failed", "Error", 0x10010);
return -1;
}
// Start server and get IDispatch...
hr = CoCreateInstance(clsid, NULL, CLSCTX_LOCAL_SERVER,
IID_IDispatch, (void **)&m_Instance.pXlApp);
if(FAILED(hr))
{
::MessageBox(NULL, "Excel not registered properly", "Error",
0x10010);
return -2;
}
// Make it visible (i.e. app.visible = 1)
{
VARIANT x;
x.vt = VT_I4;
x.lVal = 1;
AutoWrap(DISPATCH_PROPERTYPUT, NULL, m_Instance.pXlApp, L"Visible",
1, x);
}
// Get Workbooks collection
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, m_Instance.pXlApp,
L"Workbooks", 0);
m_Instance.pXlBooks = result.pdispVal;
}
New();
return TRUE;
}
// -------------------------------------------------------------------
--------- //
// Open and activate a new Workbook
// -------------------------------------------------------------------
--------- //
BOOL GFExcel::New()
{
// Call Workbooks.Add() to get a new workbook...
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, m_Instance.pXlBooks, L"Add",
0);
m_Instance.pXlBook = result.pdispVal;
}
// Get ActiveSheet object
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, m_Instance.pXlApp,
L"ActiveSheet", 0);
m_Instance.pXlSheet = result.pdispVal;
}
return TRUE;
}
// -------------------------------------------------------------------
--------- //
// Close this document
// -------------------------------------------------------------------
--------- //
void GFExcel::Close()
{
SetSavedState(TRUE);
// Tell Excel to quit (i.e. App.Quit)
AutoWrap(DISPATCH_METHOD, NULL, m_Instance.pXlApp, L"Close", 0);
}
// -------------------------------------------------------------------
--------- //
// Quit this instance - Use Init afterwards for re-use
// -------------------------------------------------------------------
--------- //
void GFExcel::Quit()
{
SetSavedState(TRUE);
// Tell Excel to quit (i.e. App.Quit)
AutoWrap(DISPATCH_METHOD, NULL, m_Instance.pXlApp, L"Quit", 0);
m_Instance.pXlSheet->Release();
m_Instance.pXlBook->Release();
m_Instance.pXlBooks->Release();
m_Instance.pXlApp->Release();
}
// -------------------------------------------------------------------
--------- //
// Set Cell Info
// -------------------------------------------------------------------
--------- //
BOOL GFExcel::CellsSet(int x, int y, LPCTSTR value)
{
_bstr_t str=value;
VARIANT tmp;
tmp.vt=VT_BSTR;
tmp.bstrVal=::SysAllocString(str);
// Get Range object for the Range A1:O15...
char strCell[64];
GetCellName(strCell, x, y);
str=strCell; // BSTR
IDispatch *pXlRange;
{
VARIANT parm;
parm.vt = VT_BSTR;
parm.bstrVal = ::SysAllocString(str);
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, m_Instance.pXlSheet,
L"Range", 1, parm);
VariantClear(&parm);
pXlRange = result.pdispVal;
}
// Set range with our safearray...
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlRange, L"Value", 1, tmp);//
arr);
// Release references...
pXlRange->Release();
return true;
}
// -------------------------------------------------------------------
--------- //
// Get Cell Info
// -------------------------------------------------------------------
--------- //
BOOL GFExcel::CellsGet(int x, int y, LPSTR value)
{
// Get Range object for the Range A1:O15...
_bstr_t str;
char strCell[64];
GetCellName(strCell, x, y);
str=strCell; // BSTR
IDispatch *pXlRange;
{
VARIANT parm;
parm.vt = VT_BSTR;
parm.bstrVal = ::SysAllocString(str);
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, m_Instance.pXlSheet,
L"Range", 1, parm);
VariantClear(&parm);
pXlRange = result.pdispVal;
}
VARIANT tmp;
tmp.vt=VT_BSTR;
AutoWrap(DISPATCH_PROPERTYGET, &tmp, pXlRange, L"Value", 0, 0);
if (tmp.vt==VT_EMPTY)
{
value[0]='\0';
}
else
{
VariantChangeType(&tmp, &tmp, VARIANT_NOUSEROVERRIDE, VT_BSTR);
str=tmp.bstrVal;
strcpy(value, str);
}
// Release references...
pXlRange->Release();
return true;
}
void GFExcel::SetSavedState(BOOL Saved)
{
// Set .Saved property of workbook to 'Saved'
VARIANT x;
x.vt = VT_I4;
x.lVal = Saved ? 1 : 0;
AutoWrap(DISPATCH_PROPERTYPUT, NULL, m_Instance.pXlBook, L"Saved", 1,
x);
}
// -------------------------------------------------------------------
--------- //
// Get Saved State
// -------------------------------------------------------------------
--------- //
BOOL GFExcel::GetSavedState()
{
// Set .Saved property of workbook to 'Saved'
VARIANT x;
x.vt = VT_I4;
AutoWrap(DISPATCH_PROPERTYGET, &x, m_Instance.pXlBook, L"Saved", 0,
0);
return x.boolVal ? TRUE : FALSE;
}
// -------------------------------------------------------------------
--------- //
// What might this do ??
// -------------------------------------------------------------------
--------- //
BOOL GFExcel::SaveAs(LPCTSTR filename)
{
/* // Taken from excel8.cpp
static BYTE parms[] =
VTS_BSTR VTS_VARIANT VTS_VARIANT VTS_VARIANT VTS_VARIANT VTS_VARIANT
VTS_VARIANT VTS_VARIANT VTS_VARIANT;
InvokeHelper(0x11c, DISPATCH_METHOD, VT_EMPTY, NULL, parms,
Filename, &FileFormat, &Password, &WriteResPassword,
&ReadOnlyRecommended, &CreateBackup, &AddToMru, &TextCodepage,
&TextVisualLayout);
*/
_bstr_t str;
str=filename;
VARIANT tmp;
tmp.vt=VT_BSTR;
tmp.bstrVal=::SysAllocString(str);
VARIANT nop;
nop.vt=VT_NULL;
AutoWrap(DISPATCH_METHOD, NULL, m_Instance.pXlBook, L"SaveAs", 9,
nop, nop, nop, nop, nop, nop, nop, nop, tmp);
return TRUE;
}
// -------------------------------------------------------------------
--------- //
// What might this do ??
// -------------------------------------------------------------------
--------- //
BOOL GFExcel::OpenBook(LPCTSTR file)
{
_bstr_t str;
str=file;
VARIANT tmp;
tmp.vt=VT_BSTR;
tmp.bstrVal=::SysAllocString(str);
VARIANT nop;
nop.vt=VT_NULL;
AutoWrap(DISPATCH_METHOD, NULL, m_Instance.pXlBooks, L"Open", 13,
nop, nop, nop, nop, nop, nop, nop, nop, nop, nop, nop, nop, tmp);
return TRUE;
}
// -------------------------------------------------------------------
--------- //
// Cell(3, 2) = C2:C2
// -------------------------------------------------------------------
--------- //
BOOL GFExcel::GetCellName(LPSTR name, int x, int y, bool DollarX, bool
DollarY)
{
char strCell[64], strCell2[64];
strCell[0]='\0';
name[0]='\0';
if (x<1 || y<1) return FALSE;
if(x > 26)
sprintf(strCell2, "%c%c%d", 'A'+(x-1)/26-1, 'A'+(x-1)%26, y);
else
sprintf(strCell2, "%c%d", 'A' + (x-1)%26, y);
if (DollarX) strcat(strCell, "$");
strcat(strCell, strCell2);
strcat(strCell, ":");
if (DollarY) strcat(strCell, "$");
strcat(strCell, strCell2);
strcpy(name, strCell);
return TRUE;
}
--
-Gernot
Post here, don't email. If you feel you have to mail, revert my
forename from:
tonreG.Frisch.at...@invalid.com
________________________________________
Looking for a good game? Do it yourself!
GLBasic - you can do
www.GLBasic.com