void excelreader::InstanceExcelWorkbook(int visible)
{
// get CLSID of excel
CLSID clsid;
HRESULT hr = CLSIDFromProgID(L"Excel.Application", &clsid);
if (FAILED(hr))
{
throw ExcelException("start excel failed. confirm excel installed");
}
// instance app
// hr = CoCreateInstance(clsid, NULL, CLSCTX_LOCAL_SERVER,
// IID_IDispatch, (void **)&pXlApp);
hr = CoCreateInstance(clsid, NULL, CLSCTX_LOCAL_SERVER, IID_IDispatch, (void **)&pXlApp);
if (FAILED(hr))
{
throw ExcelException("start excel failed. confirm excel installed");
}
// display, set Application.Visible to 1
_variant_t x((long)visible);
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlApp, L"Visible", 1, (VARIANT)x);
// get workbooks
_variant_t result;
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"Workbooks", 0);
pXlBooks = result.Detach().pdispVal;
}
HRESULT CCOMDispatchHelper::GetPropertyItemValues(const IDispatchPtr& pICollection, int iIndex,
_bstr_t& bstrName, _bstr_t& bstrValue)
{
HRESULT hr = E_FAIL;
variant_t result, vIndex;
vIndex.lVal = iIndex;
vIndex.vt = VT_I4;
hr = AutoWrap(DISPATCH_PROPERTYGET|DISPATCH_METHOD, &result, pICollection, L"Item", 1, vIndex);
RETURN_FAILED(hr);
IDispatchPtr spPropItem = result.pdispVal;
variant_t vPropItem;
hr = AutoWrap(DISPATCH_PROPERTYGET|DISPATCH_METHOD, &vPropItem, spPropItem, L"Name", 0);
RETURN_FAILED(hr);
bstrName.Assign(vPropItem.bstrVal);
hr = AutoWrap(DISPATCH_PROPERTYGET|DISPATCH_METHOD, &vPropItem, spPropItem, L"Value", 0);
RETURN_FAILED(hr);
//ComConvertUtils aComConvertUtils;
//TODO look at a better way of casting a variant_t to a PROPVARIANT
bstrValue = (LPCTSTR)PropVariantToString(*(PROPVARIANT*)(VARIANT*)&vPropItem);
return hr;
}
/**
* full one column data
*
* @param colTag column tag, e.g. "A", "AB", "M"
* @param nBeg row begin, base 1
* @prarm data
*/
void excelreader::SetData(const std::wstring& colTag, int nBeg,
const std::list<std::wstring>& data)
{
if (data.size() <= 0)
{
return;
}
int nEnd = nBeg + static_cast<int>(data.size()) - 1;
// create array
CComSafeArray<VARIANT> arr(static_cast<ULONG>(0));
for (std::list<std::wstring>::const_iterator it = data.begin();
it != data.end(); ++it)
{
arr.Add(_variant_t(it->c_str()).Detach(), FALSE);
}
VARIANT vArr;
vArr.vt = VT_ARRAY | VT_VARIANT;
vArr.parray = (SAFEARRAY*)arr;
// select range
WCHAR szTmp1[4];
wsprintfW(szTmp1, L"%i", nBeg);
WCHAR szTmp2[4];
wsprintfW(szTmp2, L"%i", nEnd);
std::wstring sTmp = colTag + szTmp1 + L":" + colTag + szTmp2;
_variant_t parm(sTmp.c_str());
_variant_t result;
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1, parm);
//full the range
AutoWrap(DISPATCH_PROPERTYPUT, NULL, result.pdispVal, L"Value",
1, vArr);
}
int excelreader::colCount() {
_variant_t range;
AutoWrap(DISPATCH_PROPERTYGET, &range, pXlSheet, L"UsedRange", 0);
_variant_t result;
AutoWrap(DISPATCH_PROPERTYGET,&result, range.pdispVal, L"Columns", 0);
_variant_t rows;
AutoWrap(DISPATCH_PROPERTYGET,&rows, result.pdispVal, L"Count", 0);
return rows.intVal;
}
excelreader::~excelreader(void)
{
if (pXlBook != NULL)
{
AutoWrap(DISPATCH_METHOD, NULL, pXlBooks,L"Close", 0);
}
if (pXlApp != NULL)
{
// Application.Quit()
//AutoWrap(DISPATCH_METHOD, NULL, pXlApp, L"Quit", 0);
}
// release all resource
if (pXlSheet != NULL)
{
pXlSheet->Release();
}
if (pXlBook != NULL)
{
pXlBook->Release();
}
if (pXlBooks != NULL)
{
pXlBooks->Release();
}
if (pXlApp != NULL)
{
pXlApp->Release();
}
// logout COM
CoUninitialize();
}
OCVariant *OCVariant::invoke(LPOLESTR method, OCVariant *argchain, bool re)
{
if(!re){
AutoWrap(DISPATCH_METHOD, NULL, method, argchain);
return this;
}else{
OCVariant *r = new OCVariant();
AutoWrap(DISPATCH_METHOD | DISPATCH_PROPERTYGET, &r->v, method, argchain);
return r; // may be called with DISPATCH_PROPERTYGET|DISPATCH_METHOD
// 'METHOD' may be called only with DISPATCH_PROPERTYGET
// but 'PROPERTY' must not be called only with DISPATCH_METHOD
}
}
double excelreader::GetDataAsDouble(int row, int col) {
if (row < 0 || col < 0)
throw ExcelException("cell not exit");
WCHAR szTmp[5];
wsprintfW(szTmp, L"%i", row);
std::wstring sTmp = GetColumnName(col) + szTmp + L":"
+ GetColumnName(col) + szTmp;
_variant_t parm(sTmp.c_str());
_variant_t result;
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range",
1, (VARIANT)parm);
_variant_t data;
AutoWrap(DISPATCH_PROPERTYGET, &data, result.pdispVal, L"Value", 0);
return (double)data;
}
OCVariant *OCVariant::getProp(LPOLESTR prop, OCVariant *argchain)
{
OCVariant *r = new OCVariant();
AutoWrap(DISPATCH_PROPERTYGET, &r->v, prop, argchain); // distinguish METHOD
return r; // may be called with DISPATCH_PROPERTYGET|DISPATCH_METHOD
// 'METHOD' may be called only with DISPATCH_PROPERTYGET
// but 'PROPERTY' must not be called only with DISPATCH_METHOD
}
void excelreader::CreateNewWorkbook()
{
_variant_t result;
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBooks, L"Add", 0);
pXlBook = result.Detach().pdispVal;
GetActiveSheet();
}
/**
* set single unit or a range
* for example:
* SetData(L"D8:D8", L"5");
* SetData(L"E3:G5", L"kkj");
* SetData(L"H9", L"H9");
*
* @param cell mark a range, e.g. "A1:B2".
* for a single unit, use "A1:A1" or "A1".
* @param data all unit of rang set to this value
*/
void excelreader::SetData(const std::wstring& cell,
const std::wstring& data)
{
if (cell.empty())
{
throw ExcelException("cell not exist");
}
_variant_t parm(cell.c_str());
_variant_t result;
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range",
1, (VARIANT)parm);
//full the range
_variant_t vtData(data.c_str());
AutoWrap(DISPATCH_PROPERTYPUT, NULL, result.pdispVal, L"Value",
1, (VARIANT)vtData);
}
long CCOMDispatchHelper::GetCollectionCount(const IDispatchPtr& pICollection)
{
variant_t vCount;
HRESULT hr = AutoWrap(DISPATCH_PROPERTYGET, &vCount, pICollection, L"Count", 0);
if (SUCCEEDED(hr))
{
return vCount.lVal;
}
return -1;
}
void excelreader::SaveAsFile(const std::wstring& filename)
{
if (!CheckFilename(filename))
{
throw ExcelException("save error");
}
_variant_t file(filename.c_str());
AutoWrap(DISPATCH_METHOD, NULL, pXlSheet, L"SaveAs", 1, file);
}
std::string excelreader::GetDataAsString(int row, int col) {
if (row < 0 || col < 0)
throw ExcelException("Cell not exit");
WCHAR szTmp[6];
wsprintfW(szTmp, L"%i", row);
std::wstring sTmp = GetColumnName(col) + szTmp + L":"
+ GetColumnName(col) + szTmp;
_variant_t parm(sTmp.c_str());
_variant_t result;
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range",
1, (VARIANT)parm);
_variant_t data;
AutoWrap(DISPATCH_PROPERTYGET, &data, result.pdispVal, L"Value", 0);
//if (VariantChangeType(&data, &data, 0, VT_BSTR) != S_OK) {
// throw ExcelException("Cannot convert data to string");
//}
//CW2A ansiBuffer((_bstr_t)data);
//CT2A cc((_bstr_t)data);
//std::string s(cc);
return tostr(data);
}
void excelreader::OpenWorkbook(const std::wstring& filename, const std::wstring& sheet)
{
_variant_t file(filename.c_str());
_variant_t result;
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBooks, L"Open", 1,
(VARIANT)file);
pXlBook = result.Detach().pdispVal;
if (sheet.empty())
GetActiveSheet();
else
GetSheet(sheet);
}
HRESULT CCOMDispatchHelper::GetCollectionItem(const IDispatchPtr& pICollection, int iIndex,
IDispatchPtr& spPropItem)
{
HRESULT hr = E_FAIL;
variant_t result, vIndex;
vIndex.lVal = iIndex;
vIndex.vt = VT_I4;
spPropItem = NULL;
hr = AutoWrap(DISPATCH_PROPERTYGET|DISPATCH_METHOD, &result, pICollection, L"Item", 1, vIndex);
if (S_OK == hr)
spPropItem = result.pdispVal;
return hr;
}
HRESULT CCOMDispatchHelper::GetPropertyItemDateTimeValues(const IDispatchPtr& pICollection, int iIndex,
_bstr_t& bstrName, FILETIME& ftValue)
{
HRESULT hr = E_FAIL;
ftValue.dwHighDateTime = 0;
ftValue.dwLowDateTime = 0;
variant_t result;
variant_t vIndex;
vIndex.lVal = iIndex;
vIndex.vt = VT_I4;
hr = AutoWrap(DISPATCH_PROPERTYGET|DISPATCH_METHOD, &result, pICollection, L"Item", 1, vIndex);
RETURN_FAILED(hr);
IDispatchPtr spPropItem = result.pdispVal;
variant_t vPropItem;
hr = AutoWrap(DISPATCH_PROPERTYGET|DISPATCH_METHOD, &vPropItem, spPropItem, L"Name", 0);
RETURN_FAILED(hr);
bstrName.Assign(vPropItem.bstrVal);
_variant_t vtTime;
VariantInit(&vtTime);
hr = AutoWrap(DISPATCH_PROPERTYGET|DISPATCH_METHOD, &vtTime, spPropItem, L"Value", 0);
RETURN_FAILED(hr);
if(vtTime.vt != VT_DATE)
return E_INVALIDARG;
COleDateTime OLETime(vtTime);
SYSTEMTIME sysTime;
if(!OLETime.GetAsSystemTime(sysTime))
return E_FAIL;
::SystemTimeToFileTime(&sysTime, &ftValue);
return hr;
}
OCVariant *OCVariant::putProp(LPOLESTR prop, OCVariant *argchain)
{
AutoWrap(DISPATCH_PROPERTYPUT, NULL, prop, argchain);
return this;
}
void excelreader::GetSheet(const std::wstring& sheetname) {
_variant_t sheet(sheetname.c_str());
_variant_t result;
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"Sheets", 1, (VARIANT)sheet);
pXlSheet = result.Detach().pdispVal;
}
static void RunClick(CASObject& obj, CFlexBridge* pBridge)
{
if ( pBridge )
{
CASObject oRootObj;
pBridge->Root(oRootObj);
// Initialize COM for this thread...
CoInitialize(NULL);
// Get CLSID for our server...
CLSID clsid;
HRESULT hr = CLSIDFromProgID(L"Excel.Application", &clsid);
if(FAILED(hr)) {
::MessageBox(NULL, "CLSIDFromProgID() failed", "Error", 0x10010);
return ;
}
// Start server and get IDispatch...
IDispatch *pXlApp;
hr = CoCreateInstance(clsid, NULL, CLSCTX_LOCAL_SERVER, IID_IDispatch, (void **)&pXlApp);
if(FAILED(hr)) {
::MessageBox(NULL, "Excel not registered properly", "Error", 0x10010);
return;
}
// Make it visible (i.e. app.visible = 1)
/* { */
/* VARIANT x; */
/* x.vt = VT_I4; */
/* x.lVal = 1; */
/* AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlApp, L"Visible", 1, x); */
/* } */
// Get Workbooks collection
IDispatch *pXlBooks;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"Workbooks", 0);
pXlBooks = result.pdispVal;
}
// Call Workbooks.Add() to get a new workbook...
IDispatch *pXlBook;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBooks, L"Add", 0);
pXlBook = result.pdispVal;
}
CASObject oADG = oRootObj.Call("getadgMain");
CASObject oDataProvider = oADG.Call("getdataProvider");
unsigned int iRowMax=1, iColMax=1;
// Create a 15x15 safearray of variants...
VARIANT arr;
arr.vt = VT_ARRAY | VT_VARIANT;
{
SAFEARRAYBOUND sab[2];
sab[0].lLbound = 1;
CASObject oColRowSize;// = oDataProvider.Call("getlength");
CASValue oSizeVal;
oColRowSize = oDataProvider.Call("getlength");
oColRowSize.GetValue(oSizeVal);
if ( oSizeVal.m_nVal )
{
sab[0].cElements = (unsigned long)*oSizeVal.m_nVal;
iRowMax = (unsigned int)*oSizeVal.m_nVal;
}
else
{
sab[0].cElements = 1;
}
//oColRowSize = oADG.Call("getColumns");
//oColRowSize.GetValue(oSizeVal);
oColRowSize = oADG.Call("getcolumns");
oColRowSize.GetValue(oSizeVal);
/* sab[0].cElements = 6; */
sab[1].lLbound = 1;
if ( oSizeVal.m_aVal )
{
sab[1].cElements = (unsigned long)oSizeVal.m_aVal->size() + 1;
iColMax = (unsigned int)oSizeVal.m_aVal->size() +1;
}
else
{
sab[1].cElements = 1;
}
/* sab[1].cElements = 3; */
arr.parray = SafeArrayCreate(VT_VARIANT, 2, sab);
}
// Fill safearray with some values...
for(unsigned int i=1; i<=iRowMax; i++) {
for(unsigned int j=1; j<=iColMax; j++) {
// Create entry value for (i,j)
VARIANT tmp;
tmp.vt = VT_R8;
CASObject oObj = oDataProvider.Call("getItemAt", (int)i-1);
CASValue oVal;
oObj.GetValue(oVal);
const char* col1[3] = {"col1", "col2", "col3"};
char *currcol = NULL;
if ( j - 1 < 3 )
{
currcol = (char*)col1[j-1];
}
else
currcol = (char*)col1[0];
CASObject oMapVal = oObj.GetObjectMap()[currcol];
CASValue oVal1;
oMapVal.GetValue(oVal1);
if ( oVal1.m_nVal )
{
tmp.dblVal = *oVal1.m_nVal;
}
else
{
tmp.vt = VT_BSTR;
std::wstring wstr(oVal1.m_sVal->length(), L'');
std::copy(oVal1.m_sVal->begin(), oVal1.m_sVal->end(), wstr.begin());
tmp.bstrVal = ::SysAllocString(wstr.c_str());
}
//tmp.lVal = i*j;
// Add to safearray...
long indices[] = {i,j};
SafeArrayPutElement(arr.parray, indices, (void *)&tmp);
}
}
// Get ActiveSheet object
IDispatch *pXlSheet;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"ActiveSheet", 0);
pXlSheet = result.pdispVal;
}
// Get Range object for the Range A1:O15...
IDispatch *pXlRange;
{
VARIANT parm;
parm.vt = VT_BSTR;
parm.bstrVal = ::SysAllocString(L"A1:C7");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1, parm);
VariantClear(&parm);
pXlRange = result.pdispVal;
}
// Set range with our safearray...
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlRange, L"Value", 1, arr);
{
//VARIANT parm;
//parm.vt = VT_BSTR;
//parm.bstrVal = ::SysAllocString(L"A1:C7");
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlRange, L"Value", 1, NULL);
//VariantClear(&parm);
LOG("Result vt is " << result.vt);
result.parray->cbElements;
for(unsigned int i1=1; i1<=result.parray->rgsabound[1].cElements; i1++)
{
CASObject oObj = oDataProvider.Call("getItemAt", (int)i1-1);
for(unsigned int j1=1; j1<=result.parray->rgsabound[0].cElements; j1++)
{
// Create entry value for (i,j)
VARIANT tmp;
tmp.vt = VT_I4;
long indices[] = {i1,j1};
SafeArrayGetElement(result.parray, indices, (void *)&tmp);
const char* col1[3] = {"col1", "col2", "col3"};
char *currcol = NULL;
if ( j1 - 1 < 3 )
{
currcol = (char*)col1[j1-1];
}
else
currcol = (char*)col1[0];
//if ( tmp.
oObj.GetObjectMap()[currcol] = tmp.dblVal;
CASValue oItemVal;
oObj.GetValue(oItemVal);
oDataProvider.Call("setItemAt", oObj,(int)i1-1);
//pXlRange = result.pdispVal;
}
}
VariantClear(&result);
}
// Wait for user...
//::MessageBox(NULL, "All done.", "Notice", 0x10000);
// Set .Saved property of workbook to TRUE so we aren't prompted
// to save when we tell Excel to quit...
{
VARIANT x;
x.vt = VT_I4;
x.lVal = 1;
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlBook, L"Saved", 1, x);
}
// Tell Excel to quit (i.e. App.Quit)
AutoWrap(DISPATCH_METHOD, NULL, pXlApp, L"Quit", 0);
// Release references...
pXlRange->Release();
pXlSheet->Release();
pXlBook->Release();
pXlBooks->Release();
pXlApp->Release();
VariantClear(&arr);
// Uninitialize COM for this thread...
CoUninitialize();
}
}
void excelreader::GetActiveSheet()
{
_variant_t result;
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"ActiveSheet", 0);
pXlSheet = result.Detach().pdispVal;
}
