|
|
|
Nicholas Hebb
Guest
|
What is the main advantage of using the ExcelWorksheetEvents class over creating your own event handlers? I wrote up the code sample below, and I couldn't tell the difference. Is it provided as a convenience to your customers, or are there other benefits?
#region ---- Addin module code ----------------------------------------
private Excel.Workbook _activeBook = null;
private Excel.Worksheet _sheet = null;
private ExcelWorksheetEvents _xlSheetEvents = null;
private void adxRibbonSinkEvents_OnClick(object sender, AddinExpress.MSO.IRibbonControl control, bool pressed)
{
_sheet = ExcelApp.ActiveSheet as Excel.Worksheet;
if (_sheet == null) { return; }
// Option 1: Use ExcelWorksheetEvents
_xlSheetEvents = new ExcelWorksheetEvents(this);
_xlSheetEvents.ConnectTo(_sheet, true);
// Option 2: Create my own sheet_Change event handler
_sheet.Change += new Excel.DocEvents_ChangeEventHandler(sheet_Change);
_activeBook = (Excel.Workbook)_sheet.Parent;
_activeBook.BeforeClose += new Excel.WorkbookEvents_BeforeCloseEventHandler(_activeBook_BeforeClose);
}
private void adxRibbonUnsinkEvents_OnClick(object sender, AddinExpress.MSO.IRibbonControl control, bool pressed)
{
UnsinkEvents();
}
void sheet_Change(Excel.Range Target)
{
// Option 1: Event handled in ExcelWorksheetEvents.ProcessChange()
// Option 2: Handle event here
string address = "[" + Target.get_AddressLocal(Office.MsoTriState.msoFalse, Office.MsoTriState.msoFalse, Excel.XlReferenceStyle.xlA1, Office.MsoTriState.msoFalse, Type.Missing) + "] ";
System.Diagnostics.Debug.WriteLine("sheet_Change - " + address + Target.Value.ToString());
}
private void UnsinkEvents()
{
// Option 1;
if (_xlSheetEvents != null)
{
_xlSheetEvents.RemoveConnection();
_xlSheetEvents.Dispose();
_xlSheetEvents = null;
}
// Option 2:
if (_sheet != null)
{
_sheet.Change -= sheet_Change;
Marshal.ReleaseComObject(_sheet);
_sheet = null;
}
if (_activeBook != null)
{
_activeBook.BeforeClose -= _activeBook_BeforeClose;
Marshal.ReleaseComObject(_activeBook);
_activeBook = null;
}
}
void _activeBook_BeforeClose(ref bool Cancel)
{
UnsinkEvents();
if (_activeBook != null)
{
_activeBook.BeforeClose -= _activeBook_BeforeClose;
Marshal.ReleaseComObject(_activeBook);
}
}
private void AddinModule_AddinBeginShutdown(object sender, System.EventArgs e)
{
if (_xlSheetEvents != null)
_xlSheetEvents.Dispose();
}
#endregion
#region ---- ExcelWorksheetEvents ----------------------------------------
public override void ProcessChange(object target)
{
string content = string.Empty;
Excel.Range range = target as Excel.Range;
if (range != null)
{
string address = "[" + range.get_AddressLocal(Office.MsoTriState.msoFalse, Office.MsoTriState.msoFalse, Excel.XlReferenceStyle.xlA1, Office.MsoTriState.msoFalse, Type.Missing) + "] ";
System.Diagnostics.Debug.WriteLine("ProcessChange - " + address + range.Value.ToString());
}
}
#endregion
|
|
|
Posted 08 Dec, 2011 01:54:22
|
|
Top
|
|
|
Andrei Smolin
Add-in Express team
Posts: 18810
Joined: 2006-05-11
|
Hi Nicholas,
The class provides a version-independent way to get connected to events in all Excel versions. Additionally, this class allows you to create several sets of event handlers; you connect this or that set to this or that sheet as your business logic requires.
Andrei Smolin
Add-in Express Team Leader |
|
|
Posted 08 Dec, 2011 02:07:20
|
|
Top
|
|
|
Nicholas Hebb
Guest
|
Hi Andrei,
OK, thank you for clarifying that. I was curious about one more thing, though. Why does ExcelWorksheetEvents.ProcessChange(object target) pass target as an object instead of as an Excel.Range? Can it handle changes to other types of Excel objects? |
|
|
Posted 08 Dec, 2011 03:38:49
|
|
Top
|
|
|
Andrei Smolin
Add-in Express team
Posts: 18810
Joined: 2006-05-11
|
Hi Nicholas,
If you pass an "Excel.Range" and the code contains "using Microsoft.Office.Interop.Excel;" you get a compile error. Also, imagine an add-in project that doesn't use interops at all.
Nicholas Hebb writes:
Can it handle changes to other types of Excel objects?
No. This is the Worksheet.Change event.
Andrei Smolin
Add-in Express Team Leader |
|
|
Posted 08 Dec, 2011 03:56:04
|
|
Top
|
|
|
Nicholas Hebb
Guest
|
|
Andrei Smolin writes:
No. This is the Worksheet.Change event.
I was hoping for the long shot that you had figured out how to detect changes to other objects on the worksheet, such as autoshapes. :) |
|
|
Posted 08 Dec, 2011 04:40:19
|
|
Top
|
|
|
Andrei Smolin
Add-in Express team
Posts: 18810
Joined: 2006-05-11
|
Hi Nicholas,
Do you need to know if some properties of the selected autoshape were modified by the user?
Andrei Smolin
Add-in Express Team Leader |
|
|
Posted 09 Dec, 2011 03:57:20
|
|
Top
|
|
|
Nicholas Hebb
Guest
|
|
Actually, now that I think about, what would be more useful would be to detect the SelectionChange for objects other than ranges. But, in the few cases that it would have been useful, I have already started coding around it. I would like to display a TaskPane when shapes are selected. However, I'm committed to releasing in a few weeks, so if there is a way to detect this, it's something I'd need to look at a later date. |
|
|
Posted 09 Dec, 2011 05:16:18
|
|
Top
|
|
|
Andrei Smolin
Add-in Express team
Posts: 18810
Joined: 2006-05-11
|
Hi Nicholas,
Nicholas Hebb writes:
Actually, now that I think about, what would be more useful would be to detect the SelectionChange for objects other than ranges.
This requires checking ExcelApp.Selection in CommandBars.OnUpdate. We are going to publish a new build providing that event in Excel add-ins next week. You'll also need to use this event to check if a shape has been changed.
Andrei Smolin
Add-in Express Team Leader |
|
|
Posted 09 Dec, 2011 06:24:11
|
|
Top
|
|
|
Nicholas Hebb
Guest
|
|
Excellent. I look forward to this. |
|
|
Posted 09 Dec, 2011 06:31:34
|
|
Top
|
|
|
Andrei Smolin
Add-in Express team
Posts: 18810
Joined: 2006-05-11
|
|
|
Posted 19 Dec, 2011 02:55:43
|
|
Top
|
|
Forums
Topics
Search
Help
Login
Register