VSTO Excel and Word Add-In C#

Introduction



The main purpose of this article is to explain how to create simple Excel and Microsoft Word Add-Ins using Visual Studio Tools for Office (VSTO). VSTO is available as an add-in tool with Microsoft Visual Studio. Using Visual Studio we can develop our own custom controls for Office tools like Excel, Word and and so on.

In my demo program I have used Visual Studio 2010 and Office 2007.

This article explains a few basic things to create our own Custom Add-Ins for Excel and Word as follows.

1. Excel Add-Ins

• Add text to any Excel selected active Excel cell.
• Add an image to Excel from our Custom Control.
• Load data from a database and display the search result data in Excel.

2. Word Add-Ins

• Export Word to PDF.
• Add Image to Word Document.
• Add Table to Word document.

Code part

Creating Excel Add-Ins

In my example I have used Visual Studio 2010 and Office 2007.

To create our own Custom Control Add-Ins for Excel.

Step 1

Create a new project and select Office 2007 Excel AddIn as in the following Image. Select your Project Folder and enter your Project Name.



Step 2

Now we can see that the Excel ThisAddIn.Cs file has been created in our project folder and we can find two default methods in this class as in the following image. “ThisAddIn_Startup” In this event we can display our own custom Control Add-Ins to Excel. We can see the details in the code part.



Step 3

Add a new UserControl to your project to create your own Custom Excel Control Add-In.

Right-click your project->Click Add New Item->Add User Control and Name the control as you wish. Add all your Controls and design your user control depending on your requirement.



In my example, I am performing 3 types of actions in User Controls.

1. Add Text: In this button click event I will insert the text from the Text box to the Active Selected Excel Cell. Using “Globals.ThisAddIn.Application.ActiveCell” we can get the current active Excel cell. We store the result in an Excel range and now using the range, value and color we can set our own text and colors to the active Excel Cell.
   

   private void btnAddText_Click(object sender, EventArgs e)
   {
       Excel.Range objRange = Globals.ThisAddIn.Application.ActiveCell;
       objRange.Interior.Color = Color.Pink; //Active Cell back Color
       objRange.Borders.Color = Color.Red;// Active Cell border Color
       objRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
       objRange.Value = txtActiveCellText.Text; //Active Cell Text Add
   
       objRange.Columns.AutoFit();
   }

2. Add Image: using the Open File Dialog we can select our own image that needs to be added to the Excel file. Using the Excel.Shape we can add our selected image to the Excel file.
   

   private void btnAddImage_Click(object sender, EventArgs e)
           {
               OpenFileDialog dlg = new OpenFileDialog();
               dlg.FileName = "*";
               dlg.DefaultExt = "bmp";
               dlg.ValidateNames = true;
   
               dlg.Filter = "Bitmap Image (.bmp)|*.bmp|Gif Image (.gif)|*.gif|JPEG Image (.jpeg)|*.jpeg|Png Image (.png)|*.png";
               if (dlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
               {
   
                   Bitmap dImg = new Bitmap(dlg.FileName);
   
                   Excel.Shape IamgeAdd = Globals.ThisAddIn.Application.ActiveSheet.Shapes.AddPicture(dlg.FileName,
   
           Microsoft.Office.Core.MsoTriState.msoFalse,
   
           Microsoft.Office.Core.MsoTriState.msoCTrue,
   
           20, 30, dImg.Width, dImg.Height);
               }
               System.Windows.Forms.Clipboard.Clear();
           }

3. Search and bind Db Data to Excel: Now we can create our own Custom Search control to be used in Excel to search our data from the database and bind the result to the Excel file.
   
   Creating the table
   

   -- Create Table ItemMaster in your SQL Server - This table will be used for search and bind result to excel.
   
   CREATE TABLE [dbo].[ItemMasters](
   [Item_Code] [varchar](20) NOT NULL,
   [Item_Name] [varchar](100) NOT NULL)
   
   -- insert sample data to Item Master table
   INSERT INTO [ItemMasters] ([Item_Code],[Item_Name])
   VALUES ('Item001','Coke')
   
   INSERT INTO [ItemMasters] ([Item_Code],[Item_Name])
   VALUES ('Item002','Coffee')
   
   INSERT INTO [ItemMasters] ([Item_Code],[Item_Name])
   VALUES ('Item003','Chiken Burger')
   
   INSERT INTO [ItemMasters] ([Item_Code],[Item_Name])
   VALUES ('Item004','Potato Fry')

   In the button search click event we search for the data from the database and bind the result to an Excel cell using “Globals.ThisAddIn.Application.ActiveSheet.Cells”. This will add the result to the active Excel sheet.
   

   private void btnSearch_Click(object sender, EventArgs e)
   {
       try
       {
           System.Data.DataTable dt = new System.Data.DataTable();
   
           String ConnectionString = "Data Source=YOURDATASOURCE;Initial Catalog=YOURDATABASENAME;User id = UID;password=password";
           SqlConnection con = new SqlConnection(ConnectionString);
           String Query = " Select Item_Code,Item_Name FROM ItemMasters Where Item_Name LIKE '" + txtItemName.Text.Trim() + "%'";
           SqlCommand cmd = new SqlCommand(Query, con);
           cmd.CommandType = System.Data.CommandType.Text;
           System.Data.SqlClient.SqlDataAdapter sda = new System.Data.SqlClient.SqlDataAdapter(cmd);
           sda.Fill(dt);
   
           if (dt.Rows.Count <= 0)
           {
               return;
           }
   
           Globals.ThisAddIn.Application.ActiveSheet.Cells.ClearContents();
   
           Globals.ThisAddIn.Application.ActiveSheet.Cells[1, 1].Value2 = "Item Code";
   
           Globals.ThisAddIn.Application.ActiveSheet.Cells[1, 2].Value2 = "Item Name";
   
           for (int i = 0; i <= dt.Rows.Count - 1; i++)
           {
   
               Globals.ThisAddIn.Application.ActiveSheet.Cells[i + 2, 1].Value2 = dt.Rows[i][0].ToString();
   
   
               Globals.ThisAddIn.Application.ActiveSheet.Cells[i + 2, 2].Value2 = dt.Rows[i][1].ToString();
           }
       }
       catch (Exception ex)
       {
       }
   }

Step 4

Now we have created our own User Control to be added to our Excel Add-Ins. To add this user control to our Excel Add-In as we have already seen that the Excel Addin class “ThisAddIn.Cs” has start and stop events. Using the Office “CustomTaskpane” we can add our usercontrol to Excel as an Add-In as in the following.

private Microsoft.Office.Tools.CustomTaskPane customPane;

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
    ShowShanuControl();
}
public void ShowShanuControl()
{
    var txtObject = new ShanuExcelADDIn();
    customPane = this.CustomTaskPanes.Add(txtObject, "Enter Text");
    customPane.Width = txtObject.Width;
    customPane.Visible = true;
}

Step 5

Run your program and now we can see our user control has been added in the Excel File as an Add-In.

Next we will see how to create Add-Ins for Word Documents using a Ribbon Control.

Creating Word Add-Ins:

In my example I have used Visual Studio 2010 and Office 2007.

The following describes how to create our own Custom Control Add-Ins for Word.

Step 1

Create a new project and select Office 2007 Word AddIn as in the following Image. Select your Project Folder and enter your Project Name.



Step 2

Add a new Ribbon Control to your project to create your own Word Control Add-In.

Right-click your project then click Add New Item -> Add Ribbon Control and name the control as you wish.



Add all your controls and design your user control depending on your requirements. By default in our Ribbon Control we can see a “RibbonGroup”. We can add all our controls to the Ribbon Group. Here in my example I have changed the Group Label Text to “SHANU Add-In”. I have added three Ribbon Button Controls to the group. We can add an image to the Ribbon Button Controls and set the properties of the Button Control Size as “RibbobControlSizeLarge”.



Here I have added three Button Controls for export the Word as a PDF, add an image to Word and add a table to the Word file.

Step 3

Export to PDF File Button Click.

Using the “Globals.ThisAddIn.Application.ActiveDocument.ExportAsFixedFormat” we can save the Word document to the PDF file. I have used the Save file dialog to save the PDF file into our selected path.

private void btnPDF_Click(object sender, RibbonControlEventArgs e)
{
    SaveFileDialog dlg = new SaveFileDialog();
    dlg.FileName = "*";
    dlg.DefaultExt = "pdf";
    dlg.ValidateNames = true;
    if (dlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
    {
        Globals.ThisAddIn.Application.ActiveDocument.ExportAsFixedFormat(dlg.FileName, word.WdExportFormat.wdExportFormatPDF, OpenAfterExport: true);
    }
}

Step 4

Here we will add an image to Word. Using the Open File Dialog we can select our own image to be added to the Word file. Using the “Globals.ThisAddIn.Application.ActiveDocument.Shapes.AddPicture” method we can add our selected image to the Word file.

private void btnImage_Click(object sender, RibbonControlEventArgs e)
{
    OpenFileDialog dlg = new OpenFileDialog();
    dlg.FileName = "*";
    dlg.DefaultExt = "bmp";
    dlg.ValidateNames = true;

    dlg.Filter = "Bitmap Image (.bmp)|*.bmp|Gif Image (.gif)|*.gif|JPEG Image (.jpeg)|*.jpeg|Png Image (.png)|*.png";
    if (dlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
    {
        Globals.ThisAddIn.Application.ActiveDocument.Shapes.AddPicture(dlg.FileName);
    }
}

Step 5

Here we will add a table to Word. Using the “Globals.ThisAddIn.Application.ActiveDocument.Tables” method we can add a table to the Word file. In my example I have created a table with 4 columns and 3 rows.

private void button1_Click(object sender, RibbonControlEventArgs e)
{
    Globals.ThisAddIn.Application.ActiveDocument.Tables.Add(Globals.ThisAddIn.Application.ActiveDocument.Range(0, 0), 3, 4);
.ThisAddIn.Application.ActiveDocument.Tables[1].Range.Shading.BackgroundPatternColor = Microsoft.Office.Interop.Word.WdColor.wdColorSeaGreen;
    Globals.ThisAddIn.Application.ActiveDocument.Tables[1].Range.Font.Size = 12;

    Globals.ThisAddIn.Application.ActiveDocument.Tables[1].Rows.Borders.Enable = 1;
}

Step 6

Run your program and now you will see your own Ribbon Control has been added to the Word file as an Add-In.