Be sure to checkout my other articles on Excel Ribbon development. The following 3 part tutorial shows you how to create a client/server CRUD application using ADO.Net, C# and Ribbon objects
Create an Excel Windows Application - PART 1
Create a table in SQL Server to store the data. - PART 1
Create and configure an ADO.NET Data Source - PART 2
Add code to load, update and save data back to the data store using a ribbon in C#. - PART 3
In the previous example (How-To program with Excel and C#), I demonstrated how to program in Excel 2007 using a Workbook Project in Visual Studio 2008. This example will use the Addin Project in Visual Studio 2008 to create a ribbon that can be inserted into any Excel file automatically.
Getting Started
If you are familiar with VS2008, start by creating a Microsoft Office 2007 Excel Addin Project. If you don't have the 2007 Office VSTO templates, you can download them from the Microsoft Download site. I won't include a link so not to end up with a potentially broken link in the future.
If you are new to VS2008, start by creating a project. Just do File->New-> Project. Expand the C# node in the Project Types(if you are using the C# settings) and expand the Office 2007 node of the VSTO and select the Excel 2007 Add-in template.
You can name your project anything you like. I named mine TestAddin. Also choose a location where to create the project or use the default location. Accept the other defaults.
New Excel 2007 Addin Project
Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel, VBA, and .NET (2nd Edition) (Addison-Wesley Microsoft Technology Series)Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel, VBA, and .NET (2nd Edition) (Addison-Wesley Microsoft Technology Series)
Buy Now
Kindle Keyboard 3G, Free 3G + Wi-Fi, 6" E Ink DisplayKindle Keyboard 3G, Free 3G + Wi-Fi, 6" E Ink Display
Buy Now
Add Form for Dialogbox
In this step we are going to add a Windows Form to the project.
Right-Click the Project in the Solution Explorer window, click Add->Windows Form. You can name it anything you like. For the purposes of this example, I will name mine "HW".
Once the form is created in the editor, I will add a TextBox, Label and Button from the Toolbox. If you are new the Visual Studio, you can drag and drop them from the Toolbox palette.
Select the Textbox component and change the following properties in the Properties window:
Change the Name property to "txtName" and;
Change the Caption of the Label to something like "Enter Your Name".
For the Button, change its Caption to "Send to Excel".
In the next section I will add code to the button to take the value entered in the TextBox and append that value the "Hello World " String and insert the value into the "A1" cell on Sheet1 of an Excel file or the ActiveSheet
Dialogbox Windows Form
Dialogbox Windows Form
If I can get you to focus on the code in the Button1_Click method, the following code will create an Excel object "excelObj" and will activate the HW form by calling the "Activate" property:
Microsoft.Office.Interop.Excel.Application excelObj;
//Make sure it is active to load HW form
this.Activate ( );
will allow us to get a handle on the opened Excel file in our application. The next bit of code will assign the active object (Excel.Application) to the excelObj object.
excelObj = (Microsoft.Office.Interop.Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
Once we have a handle on our Excel file, we can start accessing the workbook and worksheet. In order to access a worksheet, we will need to first access the workbook where the worksheet is located. You will be able to do that with the following bit of code:
//Get the Active workbook
Microsoft.Office.Interop.Excel.Workbook wb;
wb = excelObj.ActiveWorkbook;
In the next section of code, I have provided two options for accessing a Worksheet. You only need to use one of the two depending on your needs. In the first option, the code will allow you access the ActiveSheet, which is usually the first sheet in a workbook.
Microsoft.Office.Interop.Excel.Worksheet sheet =
(Microsoft.Office.Interop.Excel.Worksheet)this.ActiveSheet;
The second option allows you to get a specific worksheet through the available Worksheet collection "Microsoft.Office.Interop.Excel.Sheets". You only need to implement one of the two options.
Microsoft.Office.Interop.Excel.Sheets sheets = (Microsoft.Office.Interop.Excel.Sheets)wb.Worksheets;
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item("Sheet1");
The rest of the code in the button will get a handle on a cell (or cells) with the get_Range method in Worksheet class. You will need to cast it to the Range class. The code below will demonstrate how this is done. In the following example, I am only accessing the "A1" cell and left the second Range parameter empty "System.Reflection.Missing.Value" but I could have specified a second value to select a range of cells.
Microsoft.Office.Interop.Excel.Range afield = (Microsoft.Office.Interop.Excel.Range)
sheet.get_Range("A1",System.Reflection.Missing.Value);
To finish up, you are going to add the following code to actually insert a value into the select cell (Range). In my example the value to be inserted is "Hello World " + the value from the "name" field.
afield.set_Value(System.Reflection.Missing.Value, "Hello World " + this.name.Text);
Finally call "this.hide" to close the form.
//Hide the dialogbox
this.Hide();
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace TestAddin
{
public partial class HW : Form
{
public HW()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
//This is the Excel file that is already open so we don't need to re-open it
Microsoft.Office.Interop.Excel.Application excelObj;
//Make sure it is active
this.Activate ( );
excelObj = (Microsoft.Office.Interop.Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
//Get the Active workbook
Microsoft.Office.Interop.Excel.Workbook wb;
wb = excelObj.ActiveWorkbook;
//To get the top sheet (e.g. Sheet1) or the Active Sheet use this syntax
//Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)this.ActiveSheet;
//Get a handle on all the worksheets in the Workbook
Microsoft.Office.Interop.Excel.Sheets sheets = (Microsoft.Office.Interop.Excel.Sheets)wb.Worksheets;
//Get a specific sheet in the Workbook
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item("Sheet1");
//To get a cell or group of cells, you can use the following synatx
Microsoft.Office.Interop.Excel.Range afield = (Microsoft.Office.Interop.Excel.Range)sheet.get_Range("A1",
System.Reflection.Missing.Value);
//Set the value of the A1 cell equal to "Hello World" plus the value in the name field in the dialogbox
afield.set_Value(System.Reflection.Missing.Value, "Hello World " + this.name.Text);
//Hide the dialogbox
this.Hide();
}
}
}
Add Ribbon
For the next piece of the puzzle you will add a Ribbon Object; modify the default Group by changing its name and add a button. We finish this part by adding some code to open the HW form.
Right -Click on the Solution, in my example this would be TestAddin. In the context menu, select "Add-> New Item". In the "New Item" dialog box, select the "Ribbon (Visual Designer)" template. You can give any name you like. I named mine Hello.cs
When the Ribbon is created and the Visual Designer appears, select the Group1 control and change its name to "Hello there" or some other arbitrary name in the Properties View.
Next expand the "Office Ribbon Controls" in the Toolbox and drag a button onto the Group Control. Name the button "Click say Hello" or anything else that you like.
New Ribbon Item
New Ribbon Item
Ribbon Visual Designer
Ribbon Visual Designer
So far so good. Now Double Click on the Button Control and the Code Behind Editor will appear where you will add the code to open the dialogbox: "helloForm".
In the button1_Click method add the following code:
//Declare a dialogbox object
HW helloForm = new HW();
//Call the Show method to load the form
helloForm.Show();
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Tools.Ribbon;
namespace TestAddin
{
public partial class Hello : OfficeRibbon
{
public Hello()
{
InitializeComponent();
}
private void Hello_Load(object sender, RibbonUIEventArgs e)
{
}
private void button1_Click(object sender, RibbonControlEventArgs e)
{
//Declare a dialogbox object
HW helloForm = new HW();
//Call the Show method to load the form
helloForm.Show();
}
}
}
Text Output
Ok, finally click F5 to launch the Ribbon app and Excel. Click on the "Addin" Menu and in the Addin Ribbon click on the "Say Hello" button to launch the "helloForm" form.
Enter your Name in the TextBox and Click on the "Send to Excel" button.
Addin Menu
Addin Menu
Click to say Hello Button
Click to say Hello Button
Dialogbox
Dialogbox
Sample Output
If everything according to plan, you should see something like this.
0 carutan:
Post a Comment