Designing and developing portable Excel add-ins across different versions of Microsoft Office (2000-2016, x86-x64) is an important goal for keeping maintenance costs low whilst enabling you to grow and support as broad a Office customer base as possible. This article describes how to do just that by combining two open source .NET libraries: Excel DNA and Net Office.
Excel DNA is a .NET library whose API’s simplify writing COM-free Add-ins with User Defined Functions, custom Ribbons, RTD components, and VBA script friendly API’s for Office task automation. The project started by Govert van Drimmelen in 2005 has a broad user community across the financial and scientific domains.
Net Office is a .NET library whose API’s allow one to target multiple versions of Office 2000-2016 without referencing the platform specific PIA and VSTO assemblies as well as managing the COM proxy objects. The project started by Sebastian Lange in 2011 also has a large user community across the Office platform. The Net Office API allows one to interact with the Excel object model (e.g. workbooks, worksheets, ranges) in a Office platform independent manner.
Architecture
Implementation
1. Create a .NET class library in your preferred language (e.g. C#, F#, VB.NET).
2. Add references to NuGet packages of Excel DNA and Net Office.
a. ExcelDna.Integration
b. ExcelDna.Addin
c. NetOffice.Excel
3. Define a class that implements ExcelDna.Integration.IExcelAddIn interface as well as the System.IDisposable interface.
a. Implement the Singleton pattern.
public class MyAddIn : IExcelAddIn, IDisposable
{
// singleton variable
private static MyAddIn m_Instance = null;
// instance variables
private object m_ComApplication;
private Application m_ExcelApplication;
private bool m_Disposed = false;
static MyAddIn()
{
m_Instance = new MyAddIn();
}
public MyAddIn() {}
public static MyAddIn Instance { get { return m_Instance; } }
}
b. Implement the AutoOpen and AutoClose methods. These methods are invoked when your Addin is first installed and then un-installed.
c. Implement new OnActivate and OnDeactivate methods. In the former, get a reference to the root Application object from Excel DNA and connect to a Net Office Application wrapper, and voila, you now have programmatic access to the Excel object and event model through a platform independent API.
internal void OnActivate()
{
if (m_ComApplication == null)
{
m_ComApplication = ExcelDnaUtil.Application;
}
if (m_ExcelApplication == null)
{
// glue NetOffice + Excel DNA!
m_ExcelApplication = new Application(null, m_ComApplication);
}
}
internal void OnDeactivate()
{
Dispose();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
d. Implement the Dispose method and release references to the root Net Office and Excel DNA application objects. This will prevent memory leaks and eliminate the “phantom” Excel process after your program exits.
public void Dispose()
{
Dispose(true);
}
public void Dispose(bool disposing)
{
try
{
if (m_Disposed)
{
if (disposing)
{
if (m_ExcelApplication != null)
{
m_ExcelApplication.Quit();
m_ExcelApplication.Dispose();
}
}
if (m_ComApplication != null)
{
Marshal.ReleaseComObject(m_ComApplication);
}
}
}
catch { }
finally
{
m_Disposed = true;
m_ExcelApplication = null;
m_ComApplication = null;
}
}
4. Define a class that either extends ExcelDna.Integration.CustomUI.ExcelRibbon or ExcelDna.Integration.ComAddin.
a. Mark the class with several System.Runtime.InteroperServices attributes.
i. Mark ComVisible as true.
ii. Set the ProgId as some fixed string value.
b. Override the OnConnection and OnDisconnection methods and invoke the Addin’s OnActivate and OnDeactivate methods through the singleton.
[ComVisible(true)]
[ClassInterface(ClassInterfaceType.AutoDispatch)]
[ProgId("Athena.Excel.Sample.MyAddIn")]
public class MyRibbon : ExcelRibbon
{
private MyVBAFacade vbaProxy = new MyVBAFacade();
public override void OnConnection(object comApp, ext_ConnectMode ConnectMode, object objAddin, ref Array custom)
{
base.OnConnection(comApp, ConnectMode, objAddin, ref custom);
if (objAddin != null)
{
dynamic excelAddin = objAddin;
excelAddin.Object = vbaProxy;
}
MyAddin.Instance.OnActivate();
}
public override void OnDisconnection(ext_DisconnectMode RemoveMode, ref Array custom)
{
base.OnDisconnection(RemoveMode, ref custom);
MyAddin.Instance.OnDeactivate();
}
public void OnHello(IRibbonControl control)
{
MessageBox.Show("Hello, world!");
}
}
}
5. Use the Addin.Instance.Application reference to interact with Excel API through Net Office in a platform independent manner that works across Microsoft Office 2000-2016. Example scenarios include the following:
a. Register for Excel events such as file open/close and macro re-calculations.
b. Manipulate Excel cell ranges in response to user events and custom functions.
c. Perform context specific activity based on the active Excel workbook or cell.
d. Prompt the user for input from the Excel range reference dialog box.
e. Set the Excel status bar.
No comments:
Post a Comment