Excel add in development

Amborish.acharya
Posted by in Others category on for Beginner level | Views : 9754 red flag

Excel add in creation in excel 2007 with the help of c#


 Download source code for Excel add in development

Introduction
It is very easy to create an excel add in in c#. You can make any graphical mathematical or utility add in and put it to excel. Excel 2007 supports Ribbon menu which is a new feature exposed by Microsoft. You can make use of all the excel interfaces exposed and create chart tables data manipulation etc. Most of the financial companies now a days make their utility add in to customize excel features. In my writing i will show step by step how easy it is to create a excel add with the help of visual studio. prerequisite : visual studio 2005/2008, Excel 2007, office PIA2007.

Lets do it step wise
Create a new project from your visual studio project ->New project tab. Go to other projects and select Extensibility->shared add in project and make excel as extension. Now it will create a default structure like this:
namespace MyAddin1 { using System; using Extensibility; using System.Runtime.InteropServices; #region Read me for Add-in installation and setup information. // When run, the Add-in wizard prepared the registry for the Add-in. // At a later time, if the Add-in becomes unavailable for reasons such as: // 1) You moved this project to a computer other than which is was originally created on. // 2) You chose 'Yes' when presented with a message asking if you wish to remove the Add-in. // 3) Registry corruption. // you will need to re-register the Add-in by building the MyAddin1Setup project, // right click the project in the Solution Explorer, then choose install. #endregion /// <summary> /// The object for implementing an Add-in. /// </summary> /// <seealso class='IDTExtensibility2' /> [GuidAttribute("90CF0D09-9BD9-4EA5-8ED8-08A78A5201C1"), ProgId("MyAddin1.Connect")] public class Connect : Object, Extensibility.IDTExtensibility2 { /// <summary> /// Implements the constructor for the Add-in object. /// Place your initialization code within this method. /// </summary> public Connect() { } /// <summary> /// Implements the OnConnection method of the IDTExtensibility2 interface. /// Receives notification that the Add-in is being loaded. /// </summary> /// <param term='application'> /// Root object of the host application. /// </param> /// <param term='connectMode'> /// Describes how the Add-in is being loaded. /// </param> /// <param term='addInInst'> /// Object representing this Add-in. /// </param> /// <seealso class='IDTExtensibility2' /> public void OnConnection(object application, Extensibility.ext_ConnectMode connectMode, object addInInst, ref System.Array custom) { applicationObject = application; addInInstance = addInInst; } /// <summary> /// Implements the OnDisconnection method of the IDTExtensibility2 interface. /// Receives notification that the Add-in is being unloaded. /// </summary> /// <param term='disconnectMode'> /// Describes how the Add-in is being unloaded. /// </param> /// <param term='custom'> /// Array of parameters that are host application specific. /// </param> /// <seealso class='IDTExtensibility2' /> public void OnDisconnection(Extensibility.ext_DisconnectMode disconnectMode, ref System.Array custom) { } /// <summary> /// Implements the OnAddInsUpdate method of the IDTExtensibility2 interface. /// Receives notification that the collection of Add-ins has changed. /// </summary> /// <param term='custom'> /// Array of parameters that are host application specific. /// </param> /// <seealso class='IDTExtensibility2' /> public void OnAddInsUpdate(ref System.Array custom) { } /// <summary> /// Implements the OnStartupComplete method of the IDTExtensibility2 interface. /// Receives notification that the host application has completed loading. /// </summary> /// <param term='custom'> /// Array of parameters that are host application specific. /// </param> /// <seealso class='IDTExtensibility2' /> public void OnStartupComplete(ref System.Array custom) { } /// <summary> /// Implements the OnBeginShutdown method of the IDTExtensibility2 interface. /// Receives notification that the host application is being unloaded. /// </summary> /// <param term='custom'> /// Array of parameters that are host application specific. /// </param> /// <seealso class='IDTExtensibility2' /> public void OnBeginShutdown(ref System.Array custom) { } private object applicationObject; private object addInInstance; }


}
Now make a ribbon in excel you have to inherit Connect class from IRibbonExtensibility . To do so go to add reference COM tab and add Microsoft Excel 12.0 object library. Then add these two lines at the top of connect class where the other using directives are used. using Core = Microsoft.Office.Core; using Excel = Microsoft.Office.Interop.Excel; Then inherit connect class from Core.IRibbonExtensibility and right click on that and click on Implement interface explicitly. This will generate some code at the end of connect class like #region IRibbonExtensibility Members public string GetCustomUI(string RibbonID) { throw new Exception("The method or operation is not implemented."); } #endregion Now add a xml file in the project and named it as Ribbon.xml. Go to the property of the xml file and change the build action to embedded resource. Open project property and resource tab and drag the ribbon.xml to that and save the project. Now modify the Ribbon file like this <?xml version="1.0" encoding="utf-8" ?> <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"> <ribbon startFromScratch="false"> <tabs> <tab id="ADDINS_TB" label="TestTab" > <group id="GR_TAB" label="TestGroup"> <button id="BTN_TEST" imagemso="HappyFace" label="TestThis" size="large" supertip="My Test Ribbon" onAction="OnAction" /> </group> </tab> </tabs> </ribbon> </customUI> I am not going into the details of each and every functionality of it. You can get the details from msdn. One call back function is used in this file named OnAction. So write the implementation of OnAction to the connect class like this public void OnAction(Core.IRibbonControl control) { switch (control.Id) { case "BTN_TEST": { MessageBox.Show("Hello world"); } break; } } And at last change the default GetCustomUI(string RibbonID) to public string GetCustomUI(string RibbonID) { return Properties.Resources.Ribbon; } Finally your Excel ribbon is ready. ReBuild the project and there is another project created named MyAddin1Setup. Write click on that and click on install option. It will install the project to your machine. Now if you open excel you can see one add in named TestTab is created. If you click on that there is a button named TestThis with a slimly icon on that. If you click on that one message box will pop up "Hello world". Like this you can create your own add in in Excel
.

Conclusion
For farther communication write me at acharya.amborish@gmail.com

Page copy protected against web site content infringement by Copyscape

About the Author

Amborish.acharya
Full Name: amborish acharya
Member Level: Starter
Member Status: Member
Member Since: 4/21/2009 5:16:07 AM
Country:

http://www.dotnetfunda.com
Sr Software Engineer from bangalore. Experienced in MFC C++ and Dot net

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)