|
I've added the following code in SheetChange function, to retrieve the data entered in excel cells.
void _stdcall SheetChange(IDispatch *, struct Excel::Range *target)
{
_bstr_t result= target->GetAddress(1,1,Excel::XlReferenceStyle::xlR1C1,1,1);
string str=result;
std::cout << "str is: " << str << "\n";
int index1 = str.find("!R");
int index2 = str.find("C");
string rowIndex = str.substr(index1+2);
string colIndex = str.substr(index2+1);
rowIndex = str.substr(index1+2);
colIndex = str.substr(index2+1);
int rIndex = atoi(rowIndex.c_str());
int cIndex = atoi(colIndex.c_str());
Excel::RangePtr rangePtr = target->Worksheet->Cells;
_variant_t vItem = rangePtr->Item[rIndex][cIndex];
_bstr_t bstrText( vItem );
cout << bstrText;
}
bstrText contains data in excel cell. where as its not retrieving the automatic changes.
for example, C1 cell contains the function =SUM(A1,B1), A1 = 10, B1=20 then C1=30
if I changed value in A1 to 15, at a time there will changes in two cell, i.e, A1(15) and C1(35).
I'm able to get the A1 value but not C1. How can I trace C1 value (automatic changes).
Please guide me to solve this.
Thanks in advance.
|
|
|
|
|
Has C1 actually changed? I would say not. It's formula has stayed the same. The documentation says that the SheetChange event is fired when a cell is changed by the user - you only change 1 cell, which is A1.
To catch when cells are recalculated, there's a Calculate event.
Java, Basic, who cares - it's all a bunch of tree-hugging hippy cr*p
|
|
|
|
|
C1 is getting changed as soon as A1 or B1 gets changed.
To catch when cells are recalculated, I added SheetCalculate event as below
_ATL_FUNC_INFO SheetCalculateInfo = { CC_CDECL, VT_EMPTY, 0, { VT_DISPATCH, VT_DISPATCH } };
SINK_ENTRY_INFO(1, __uuidof(Excel::AppEvents), 0x00000a34, &ExcelAppEventHandler::SheetCalculate, &SheetCalculateInfo)
void _stdcall SheetCalculate(IDispatch *iDispatch)
{
cout << "Inside SheetCalculate" ;
}
When I changed function related cells, SheetCalculate function is not getting fired.
I think declaration of _ATL_FUNC_INFO SheetCalculateInfo is incorrect.
Please correct me to fire SheetCalculate function, to read the automatic cell changes.
|
|
|
|
|
SheetCalculate is a WorkBook event. You've put the SheetCalculate event signature in an Application event handler, judging by the mention of AppEvent all over the code. You would need a separate WorkBook event handler that you register (with DispEventAdvise) with the workbook you're interested in.
Java, Basic, who cares - it's all a bunch of tree-hugging hippy cr*p
|
|
|
|
|
Thank you for your support. Can you give sample code around this.
and also it would be great if you can point me to complete documentation on interfacing Excel
|
|
|
|
|
Workbook SheetCalculate event handler:
_ATL_FUNC_INFO SheetCalculateInfo = { CC_CDECL, VT_EMPTY, 1, { VT_DISPATCH } };
class ExcelBookEventHandler : public IDispEventSimpleImpl<1, ExcelBookEventHandler, &__uuidof(Excel::WorkbookEvents)>
{
public:
BEGIN_SINK_MAP(ExcelBookEventHandler)
SINK_ENTRY_INFO(1, __uuidof(Excel::WorkbookEvents), 0x0000061b, &ExcelBookEventHandler::SheetCalculate, &SheetCalculateInfo)
END_SINK_MAP()
void _stdcall SheetCalculate(IDispatch *)
{
std::cout << "ExcelBookEventHandler::SheetCalculate\n";
}
};
Then given I have an Excel Application interface pointer called xl, I use this to register the event handler:
Excel::_WorkbookPtr wb = xl->Workbooks->Add();
ExcelBookEventHandler calc;
calc.DispEventAdvise(wb);
The same dispatch loop is used as I used before.
NarVish wrote: it would be great if you can point me to complete documentation on interfacing Excel
Not sure there IS any. The Excel help available through the VBA IDE tells you all about the Excel object model, and that's all you really need to know, except for some details (like dispids, etc) that I get by opening the Excel type library with the OLE/COM Object Viewer.
Java, Basic, who cares - it's all a bunch of tree-hugging hippy cr*p
|
|
|
|
|
Thank you for your post.
SheetCalculate is getting fired whenever there is change in function related cells. My aim is to get the function result in excel. To get that I'm trying by calling different functions of iDispatch. But couldn't able to get the expected result.
void _stdcall SheetCalculate(IDispatch *iDispatch)
{
////////////*******GetIDsOfNames*******////////////
static DISPID dispid = 0;
wchar_t *ucName = L"Evaluate"; //I tried by giving "Run" and "Calculate" values in place of "Evaluate"
HRESULT result = iDispatch->GetIDsOfNames(IID_NULL, &ucName, 1,LOCALE_SYSTEM_DEFAULT, &dispid);
////////////*******Invoke*******////////////
VARIANT varResult;
DISPID dispid;
DISPID dispidNamed = DISPATCH_METHOD;
EXCEPINFO excep;
UINT uArgErr;
DISPPARAMS dispparams;
dispparams.cNamedArgs = 0;
dispparams.cArgs = 2;
dispparams.rgdispidNamedArgs = &dispidNamed;
HRESULT result = iDispatch->Invoke( odispid, IID_NULL, LOCALE_SYSTEM_DEFAULT,
DISPATCH_METHOD, &dispparams, &varResult, &excep, &uArgErr);
////////////*******GetTypeInfo*******////////////
ITypeInfo* pTypeInfo;
HRESULT result= iDispatch->GetTypeInfo(0,LOCALE_SYSTEM_DEFAULT,&pTypeInfo);
////////////*******GetTypeInfoCount*******////////////
UINT pct1;
HRESULT result= iDispatch->GetTypeInfoCount(&pct1);
////////////*******QueryInterface*******////////////
HRESULT result = iDispatch->QueryInterface(IID_IDispatch,(void**)&iDispatch);
}
I followed the below link to get some help.
"http://up.m-e-c.biz/up/books/Excel%20add-in%20C++.pdf"
|
|
|
|
|
Hi Stuart Dootson,
I implemented the cell change event functionality in C#.Net. Due to environmental dependencies, our project was shifted to VC++. and I'm new to C++. Please bear with me to solve the above post. A million thanks in advance...
|
|
|
|
|
Which event gets fired when a new Excel application is created. I handled NewWorkbook event of AppEvents. It gets triggered only when I create new excel workbook.
Please let me know which event handles the new Excel application instance.
Thanks in advance.
|
|
|
|
|
No such thing, AFAIK. What's the use case you're trying to solve?
Java, Basic, who cares - it's all a bunch of tree-hugging hippy cr*p
|
|
|
|
|
my application reads data from Excel applications, which are opened. and it runs 24*7.. If any new excel application instance gets created and data entered by user, I need to read data from the new excel instance. So, I would like to handle the new excel application instance. from that event I can attach sheetchange and sheetCalculate events.
|
|
|
|
|
Is there any alternative idea to solve the above issue.
|
|
|
|
|
In my class, I have two classes ExcelAppEventHandler and ExcelBookEventHandler
AppHandler contains Sheetchange,WorkbookNewSheet and NewWorkbook events
BookHandler contains SheetCalculate event.
When I create a new worksheet, workbooknewsheet event occurs. in the new worksheet if I add some value, SheetChange event gets fired. Same way SheetCalculate should also be fired whenever calculations happend in the new sheet. classes are give below.
1. ExcelBookEventHandler bookHandler(done,xl);
2. bookHandler.DispEventAdvise(book);
1 and 2 statements are not working to call SheetCalculate event. Please let me know how to call SheetCalculate event from workbooknewsheet event.
class ExcelAppEventHandler : public IDispEventSimpleImpl<1, ExcelAppEventHandler, &__uuidof(Excel::AppEvents)>
{
public:
ExcelAppEventHandler(bool& doneFlag) : done_(doneFlag)
{
done_ = false;
}
BEGIN_SINK_MAP(ExcelAppEventHandler)
SINK_ENTRY_INFO(1, __uuidof(Excel::AppEvents), 0x0000061c, &ExcelAppEventHandler::SheetChange, &SheetChangeInfo)
SINK_ENTRY_INFO(1, __uuidof(Excel::AppEvents), 0x00000625, &ExcelAppEventHandler::WorkbookNewSheet, &WorkbookNewSheetInfo)
SINK_ENTRY_INFO(1, __uuidof(Excel::AppEvents), 0x0000061d, &ExcelAppEventHandler::NewWorkbook, &NewWorkbookInfo)
END_SINK_MAP()
void _stdcall SheetChange(IDispatch *iDispatch, struct Excel::Range *target)
{
cout<<"Inside Sheet Change"<< endl;
}
void _stdcall NewWorkbook(Excel::_Workbook *book)
{
ExcelBookEventHandler bookHandler(done,xl);
bookHandler.DispEventAdvise(book);
}
void _stdcall WorkbookNewSheet(Excel::_Workbook *book, IDispatch *sh)
{
ExcelBookEventHandler bookHandler(done,xl);
bookHandler.DispEventAdvise(book);
}
}
class ExcelBookEventHandler : public IDispEventSimpleImpl<1, ExcelBookEventHandler, &__uuidof(Excel::WorkbookEvents)>
{
public:
ExcelBookEventHandler(bool& doneFlag,Excel::_ApplicationPtr xlApp) : done_(doneFlag),xlapPtr_(xlApp)
{
done_ = false;
}
BEGIN_SINK_MAP(ExcelBookEventHandler)
SINK_ENTRY_INFO(1, __uuidof(Excel::WorkbookEvents), 0x0000061b, &ExcelBookEventHandler::SheetCalculate, &SheetCalculateInfo)
END_SINK_MAP()
void _stdcall SheetCalculate(IDispatch *pDisp)
{
cout<< "Inside SheetCalculate";
}
}
|
|
|
|
|
Your code looks OK to me - I'm just not sure what you mean by "how to call SheetCalculate event from workbooknewsheet event".
Java, Basic, who cares - it's all a bunch of tree-hugging hippy cr*p
|
|
|
|
|
If I add new workbook sheet, WorkbookNewSheet() event gets fired. If I enter some data in newly added sheet, SheetChange event is getting fired.
Likewise, if I add some function like =sum(10,20) in newly added sheet, SheetCalculate function should get fired. But its not happening. Had I missed any bind between appEventHandler and bookEventHandler?
Expected output is SheetCalculate (event of workbook) should get fired whenever there is a change in function result of the newly added WorkBookSheet.
Please let me know if my question is not clear.
|
|
|
|
|
This (complete) program code successfully registers event handlers which get called by Excel:
#include <iostream>
#include <atlbase.h>
#include <atlcom.h>
#import "libid:00020813-0000-0000-C000-000000000046" auto_search no_dual_interfaces rename("DialogBox", "excelDialogBox") rename("RGB", "excelRGB") rename("DocumentProperties", "excelDocumentProperties") rename("SearchPath", "excelSearchPath") rename("CopyFile", "excelCopyFile") rename("ReplaceText", "excelReplaceText")
_ATL_FUNC_INFO SheetChangeInfo = { CC_CDECL, VT_EMPTY, 2, { VT_DISPATCH, VT_DISPATCH } };
_ATL_FUNC_INFO AfterCalculateInfo = { CC_CDECL, VT_EMPTY, 0, { } };
class ExcelAppEventHandler : public IDispEventSimpleImpl<1, ExcelAppEventHandler, &__uuidof(Excel::AppEvents)>
{
public:
ExcelAppEventHandler(bool& doneFlag) : done_(doneFlag) { done_ = false; }
BEGIN_SINK_MAP(ExcelAppEventHandler)
SINK_ENTRY_INFO(1, __uuidof(Excel::AppEvents), 0x0000061c, &ExcelAppEventHandler::SheetChange, &SheetChangeInfo)
SINK_ENTRY_INFO(1, __uuidof(Excel::AppEvents), 0x00000a34, &ExcelAppEventHandler::AfterCalculate, &AfterCalculateInfo)
END_SINK_MAP()
void _stdcall SheetChange(IDispatch *, struct Excel::Range *)
{
std::cout << "ExcelAppEventHandler::SheetChange\n";
}
void _stdcall AfterCalculate()
{
std::cout << "ExcelAppEventHandler::AfterCalculate\n";
}
private:
bool& done_;
};
_ATL_FUNC_INFO SheetCalculateInfo = { CC_CDECL, VT_EMPTY, 1, { VT_DISPATCH } };
class ExcelBookEventHandler : public IDispEventSimpleImpl<1, ExcelBookEventHandler, &__uuidof(Excel::WorkbookEvents)>
{
public:
BEGIN_SINK_MAP(ExcelBookEventHandler)
SINK_ENTRY_INFO(1, __uuidof(Excel::WorkbookEvents), 0x0000061b, &ExcelBookEventHandler::SheetCalculate, &SheetCalculateInfo)
END_SINK_MAP()
void _stdcall SheetCalculate(IDispatch *)
{
std::cout << "ExcelBookEventHandler::SheetCalculate\n";
}
};
int _tmain(int argc, _TCHAR* argv[])
{
CoInitializeEx(0, COINIT_MULTITHREADED);
{
Excel::_ApplicationPtr xl;
if (SUCCEEDED(xl.GetActiveObject(__uuidof(Excel::Application))))
{
Excel::_WorkbookPtr wb = xl->Workbooks->Add();
ExcelBookEventHandler bookHandler;
bookHandler.DispEventAdvise(wb);
Excel::_WorksheetPtr ws = wb->Worksheets->Item[1];
if (ws)
std::cout << ws->Name << std::endl;
bool done = false;
ExcelAppEventHandler handler(done);
if (SUCCEEDED(handler.DispEventAdvise(xl)))
{
MSG msg;
while (!done && GetMessage(&msg, NULL, 0, 0) > 0) {
TranslateMessage(&msg);
DispatchMessage(&msg);
}
handler.DispEventUnadvise(xl);
}
}
}
CoUninitialize();
return 0;
}
Java, Basic, who cares - it's all a bunch of tree-hugging hippy cr*p
|
|
|
|
|
Please excuse me if this has been covered and I have failed to understand. I have a C++/MFC app that reads from a database of locations, retrieves the UK/Irish ordnance survey grid reference and, amongst other things, works out the proximity of chosen sites one from another. The app is not intended to be of GPS accuracy, but rather to allow broad analysis of spatial relationships between archaeological sites. I would like to be able to interface with the terrain maps in Google earth but, as an amateur, have not been able to find any information other than an article posted here for a C# app - unfortunately I don't know C#.
Could anyone be so kind as to point me towards information on how one can interface a C++ app with GE to allow for a visual representation of data? i.e. using grid refs to plot out localities in a given area on a GE terrain map. Or, alternatively (and almost as helpful, but not quite!) tell me if this is not possible and thus save me further banging of the head on the keyboard.
Best thanks
Brian
|
|
|
|
|
Hi, i have some data in a buffer that i need to write to a file:
writing:
for(unsigned int i = 4; i<(nReceivedBytes-2); i++)
{
//_tprintf(_T("%c"), DataBuf.buf[i]);
myfile.open ("file.doc", ios::out | ios::app);
myfile<<(DataBuf.buf[i]);
myfile.close();
}
The data is in unicode (2 bytes), the first four bytes show the length of the data package and the last two are zero. The file wont open with openoffice and when i open it with notepad++ it displays some characters but most of them are NUL. What could be the problem?
Thank you
|
|
|
|
|
yeah1000 wrote: myfile.open ("file.doc", ios::out | ios::app);
What is the type of myfile object - std::ofstream or std::wofstream?
When you write unicode data you should use std::wofstream.
Nuri Ismail
|
|
|
|
|
It was ofstream, i changed it to "wofstream myfile;" but it did't help 
The data arrives over TCP, using WSABUF which i understand is actually of type char. Should i be able to write the data to file two bytes at a time so it could be opened?
|
|
|
|
|
yeah1000 wrote: The file wont open with openoffice and when i open it with notepad++ it displays some characters but most of them are NUL. What could be the problem?
For application to know that your file is a unicode one, the first two bytes of the file needs to be 0xff and 0xfe. If the first two bytes doesn't indicates any of the encoding codes, it will consider the file and ASCII file. I guess thats what happend in your case. Check http://en.wikipedia.org/wiki/Byte-order_mark[^] for more details.
|
|
|
|
|
Openoffice sometimes gives me the choice to choose in which format the file is, but when i select unicode then the file still is unreadable. Is there no way to write the file in binary format (as with binarywriter in c# for example) so that it wouldnt matter in which format it is?
|
|
|
|
|
yeah1000 wrote: (as with binarywriter in c# for example)
What difference does the file have when a string is written in c# using binary mode and in non binary mode?
|
|
|
|
|
Unicode aside, the only data that is going to exist in your file is DataBuf.buf[last_value_of_i]. You should probably be opening and closing the file outside of the for() loop.
"Old age is like a bank account. You withdraw later in life what you have deposited along the way." - Unknown
"Fireproof doesn't mean the fire will never come. It means when the fire comes that you will be able to withstand it." - Michael Simmons
|
|
|
|
|
Filemode is append, it does not help if i place it outside of the for cycle.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
