VSTO Excel and Word Add-In C#

아티클
1/17/2024

You can download the source code from this link Source Code Link

Introduction

https://code.msdn.microsoft.com/site/view/file/141290/1/1.JPG

*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 our demo program We have used Visual Studio 2010 and Office 2007.*

Building the Sample

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

https://code.msdn.microsoft.com/site/view/file/141291/1/2.JPG

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

Description

Creating Excel Add-Ins

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

Step 1

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

https://code.msdn.microsoft.com/site/view/file/141292/1/3.JPG

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.

https://code.msdn.microsoft.com/site/view/file/141293/1/4.JPG

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.

https://code.msdn.microsoft.com/site/view/file/141294/1/5.JPG

In our example,We are performing 3 types of actions in User Controls.

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();    
}

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();   
        }

**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=";   
        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 user control 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.

https://code.msdn.microsoft.com/site/view/file/141295/1/6.JPG

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.

https://code.msdn.microsoft.com/site/view/file/141296/1/7.JPG

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”.

https://code.msdn.microsoft.com/site/view/file/141297/1/8.JPG

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.

https://code.msdn.microsoft.com/site/view/file/141298/1/9.JPG

You can download the source code from this link Source Code Link

다음을 통해 공유

VSTO Excel and Word Add-In C#

Introduction

Building the Sample

추가 리소스