Archive

Archive for the ‘Excel Automation Add-in’ Category

Creating custom Excel Function wizard using VSTO Method2

April 3, 2011 Leave a comment

Word Document Of the tutorial-Introduction

Word Document Of the tutorial-Custom Function Wizard

Read Introduction Tutorial About VSTO

Read part1 Of the tutorial For First Aproach

Download FullCode From Here

In the steps discussed above the main logic was to write the formula on the selected cell.Instead of this we can do the same thing on this way
1. On click event of button in tab Shows the form.
2. Select the cell(Say A1)
3. Read value of the cell(say 5 , means value in the cell A1 is 5)
4. Calculate the factorial for this value (factorial of 5=1*2*3*4*5=120)
5. Write the result on the selected cell (120 instead of writing =fact(A1)).

Implementation Of Approach 2
Step1
Create a new form and design the form (similar to factorial form used on the first part of this tutorial)

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel  Automation And Com Addin

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel Automation And Com Addin


Add a new button on the ribbon and on the click event shows the newly designed form
Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel  Automation And Com Addin

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel Automation And Com Addin



Step2

If we select some cells on excel we need to fill that cellname on the text box. But now we had the chance of opening either form1 or form2.
So the approach to overcome this isdeclare one static variable active formno on the ribbon class and assign it a unique no while showing each form.(say activeformno=1 for myfrm and activeformno=2 for myfrm2)


public static frmFact myfrm;
public static frmFactApproach2 myfrm2;
public static int activeformno;
And on the DoActions function check the active formno first and based on that we shows thecellname on the corresponding textbox

internal void DoActions(string cellValue)
{
try
{
if (activeformno == 1)
{
if (myfrm.ActiveControl.GetType() == typeof(System.Windows.Forms.TextBox))
myfrm.ActiveControl.Text = cellValue;
}
else if (activeformno == 2)
{
if (myfrm2.ActiveControl.GetType() == typeof(System.Windows.Forms.TextBox))
myfrm2.ActiveControl.Text = cellValue;
}
}
catch
{
}
}

Step3 read the value Of selected Cell
On the click event button on the form

string cellName = txtCellName.Text;

string value = readValueOfCell(cellName);

where the function to readvalue of selected cell is

private string readValueOfCell(string cellName)
{
string val = "";
Worksheet activeworksheet = Globals.TesstPAddin.GetActiveWorksheet();
Range FreqRange = activeworksheet.get_Range(cellName, Type.Missing);
val = Convert.ToString(FreqRange.Value);
return val;
}

Step4
Find the factorial based on this no

double factorial = getFactorial(Convert.ToDouble(value));

Step5
and display/write the value in selected cell

Worksheet currentSheet = Globals.TesstPAddin.GetActiveWorksheet();
currentSheet.Cells[Selectedrow, SelectedrCol] = factorial.ToString();
this.Close();

Disadvantage of this approach is if we are change the cell values the result willnot getting updated.But we need this kind of approach in some stages
Here we are discussing the way of reading only singe cell value .But there is a chance that user may selects a range of cells and we need to process this range of values.(

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel  Automation And Com Addin

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel Automation And Com Addin

In this case first we need to identify the cell address in text box is a single cell address(Then read the cell value)
Or a Range Of cell address like A1:A12 like in the last image.In this case we need to read the range of cells using the code.

Worksheet activeworksheet = Globals.TesstPAddin.GetActiveWorksheet();

Range FreqRange = activeworksheet.get_Range(cellnames, Type.Missing);
object[,] ExcelCellvalues = FreqRange.get_Value(System.Reflection.Missing.Value) as object[,];

The 2d array ExcelCellvalues contains all valuesin the selected range of cells.(Both null values,strings,doubles etc).So we process these values and store the needed values on to a List or another array like this

List NeededValues = new List();
if (ExcelCellvalues != null)
{
foreach (object obj in ExcelCellvalues)
{
if (obj != null)
{
/*Do the needed checks on data and store it if they are OK*/
NeededValues.Add(obj.ToString());

}

}
}

If you want to get the address of cell & value of selectd cells

Worksheet activeworksheet = Globals.TesstPAddin.GetActiveWorksheet();

Range FreqRange = activeworksheet.get_Range(cellnames, Type.Missing);

foreach (Range cell in FreqRange)
{

String cellAddress = cell.get_Address(false, false, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, false, false).ToString();
String value= cell.Value;

}

If you want to check the validity of name of cells you enterd
This function checks given string is a range addres or not
A1:A13- valid
A1:B12 valid
A1:A-not valid
A1:aa not valid
1A:b1 –not valid

Download FullCode From Here

Check A String is Valid Excel cell Name or not

Advertisements

Creating custom Excel Function wizard using VSTO Method1

April 3, 2011 Leave a comment


Read Introduction Tutorial About VSTO

Download FullCode From Here
Word Document Of the tutorial-Introduction

Word Document Of the tutorial-Custom Function Wizard

    AIM-

This step by step tutorial describes a way to create custom function wizard and use either builtin formulas or UDF’s with them in 2 approaches.

In this tutorial Generate a function wizard using VSTO project and implement the FACT() function as example.(Function used to find the factorial of a number)
Step1: Read All the instructions in Step by Step Tutorail1.By following them its easy for anyone to start a basic VSTO project. I am using a new project o explain this Tutorial.
Chapter1. Working Of Built-In Excel Function Wizard
1. Open the excel Sheet Fill 5,4,3 in cells A1,A2,A3 and select on the cell C1 and click on the function wizard symbol on the top of excel sheet .
2. Select the function FACT() from Math Trig Category. You can see a windows form like structure for finding the Factorial Functionality. Above things you can see in

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel  Automation And Com Addin

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel Automation And Com Addin


3. Now Click On the Cell A1
Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel  Automation And Com Addin

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel Automation And Com Addin

4. And click on the OK button on the form what you can see is you get the result on the cell C1.

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel  Automation And Com Addin

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel Automation And Com Addin

Chapter2 .Implementation Of the same using VSTO.- Approach1
Step1
Add a windows form item to the project (For this right click on projects Add new ItemChoose Windows Form).Name the form press OK.(I am adding a new form frmFact).and create a design like the Fact() FunctionWizard Design .

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel  Automation And Com Addin

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel Automation And Com Addin

Step2
Add Factorial Button to the Ribbon Tab
Open ToolBox .you can see different tools on that .From Office Ribbon Controls tab Choose the button control and drag and drop this button control to the Design Of RibbonTab .Change the propertys Of this button like Text,Name etc by clicking on the property’s(Similarly we are doing for Windows Forms Controls)
Step3
Write click event for the added button-
On theclick event shows the form frmFact and passing the 2 arguments selectedrow and column no and assign them as properties on the form
(These things explain in details on tutorial 1-Step7)
Step 4
Now if we run the project and click on the factorial button we can see the form that you addedon step 2

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel  Automation And Com Addin

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel Automation And Com Addin

Step5
Write the OK Button Click event For getting the result on selected cell .
(See step8 On tutorial 1 If need more explanation).
If you write a content like =Fact(A1) On any cell on the excel sheet we will get the result of Factorial Of Number in the cell A1 means. If cell a1 contains a no 5 then we get factorial of 5 in the selected cell.

Using the same concept If we enter the value of cell name manually on the textbox of our form and click OK button then we get the factorial of the written cell value on the selected cell

private void btnOk_Click(object sender, EventArgs e)
{
Worksheet currentSheet = Globals.HelloAddIn.GetActiveWorksheet();
currentSheet.Cells[Selectedrow, SelectedrCol] = "=FACT("+txtCellNAme.Text+")";
this.Close();
}

Output
Working example:
1. Run project and fill A1=10 A2=9 and A3=8 .
2. Select cell c4 and Click on HelloTAb Factorial Button.
3. Write a3 on the cell

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel  Automation And Com Addin

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel Automation And Com Addin


4. We get factorial of 8 in cell c4
Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel  Automation And Com Addin

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel Automation And Com Addin


Now if you change the value in cell A3 you can see that the value of cell C4 is also getting changed
Note:
So before pressing Ok Button we need to check the cell value enterd is correct or not (if we enter 1a is not correct. Aa is again incorrect..So Check this Code to check a string is valid excel cell Name or not)

Problems with this approach
P1-We need to manually enter the cell value.So chance for error is more.If we want to change the cell name its very complex.
P2- If we are dealing with formulas of range of cells say A1:A3 again this approach is difficult.

What we want is when we select some cells on excel sheet we need to fill the textbox in the form with that selected cell name(either range or a single cell).
Way to overcome the limitation

Add new SheetSelectionChangeEventHandler to the Addin On the HelloAddin.cs Page

private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
this.Application.SheetSelectionChange += new Microsoft.Office.Interop.Excel.AppEvents_SheetSelectionChangeEventHandler(Application_SheetSelectionChange);
}

Implementation Of the Event(variable cellvalue contains the name of cell selected).

public void Application_SheetSelectionChange(object Sh, Microsoft.Office.Interop.Excel.Range Target)
{
string cellValue="";

cellValue = Target.get_Address(false, false, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1, false, false).ToString();

}

Now we get the value of cell and what is remaining is fill this value in textbox on the currently opend form(frmFact)
.For doing this
Crate a function on the Hello.cs page say DoActions and call it from the HelloAddin.cs page Application_SheetSelectionChange event


Hello do_actions = new Hello();
do_actions.DoActions(cellValue);

This DoActions checks if the currently active control on the form is a textbox then it fills the textbox with the selected cell value

internal void DoActions(string cellValue)
{
try
{
if (myfrm.ActiveControl.GetType() == typeof(System.Windows.Forms.TextBox))
myfrm.ActiveControl.Text = cellValue;
}
catch
{
}
}

Note : Make sure myfrm is a static object like
public static frmFact myfrm;
So in any duration of the application we can use any controls on frmFact.Or we can assume the frmFact as currently active form
On the click event of factorial ribbon button initialize the form frmFactorial like this

Range selectedCell = Globals.HelloAddIn.Application.ActiveCell;
int selectedRow = selectedCell.Row;
int selectedCol = selectedCell.Column;

myfrm = new frmFact(selectedRow,selectedCol);
myfrm.TopMost = true;
myfrm.Show();

Now what we can see is that
The function is behaving like a normal function wizard of excel Sheet.(check the steps mentioned on the beginning of this article under the title Working Of Excel Function Wizard
Output
1. Open the excel Sheet Fill 10,9,8 in cells A1,A2,A3 and select the cell C3 and click on Factorial Button on our designed tab

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel  Automation And Com Addin

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel Automation And Com Addin

2. Now Click On theCell A1 .(See image).When you click on the cell A1 the value A1 is filled on the textbox. If you click some other cells like A2, A3 etc and see the difference.

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel  Automation And Com Addin

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel Automation And Com Addin

3. And click on the OK button on the form what you can see is you get the result on the cell C3

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel  Automation And Com Addin

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel Automation And Com Addin


4. If you select on the cell C3 again then you can see the value is 3628800.But on the top you can see the text as =fact(A1) (
Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel  Automation And Com Addin

Excel UDf using c# , FunctionWizard with Arguments Description, Excel Automation Addin c#, VSTo Excel Addin. Combine excel Automation And Com Addin

If you update the contents in cellA1 then you can see the content in cell C3 is also changed
Read Part2 Of this Tutorial for Second Approach

Check A String is Valid Excel cell Name or not

Download FullCode From Here

Getting Started with VSTO Programming

April 3, 2011 Leave a comment

Download FullCode Form Here For This Tutorial

Step1
Open New Project in visual studio 2010 Select Projec Type Office and Excel2007 Addin. Give Names to the project and choose the save location

Getting Started With VSTO

Getting Started With VSTO

Step 2
Now the project is created . To create the Ribbon RightClick on ProjectName from SolutionExplorer and click on Add NewItem And Select Ribbon(VisualDesigner) Item in the top pof the list.
Gives a name for the ribbon And click on add button

Getting Started With VSTO

Getting Started With VSTO

Step3
Open ToolBox .you can see different tools on that .From Office Ribbon Controls tab Choose the button control and drag and drop this button control to the Design Of RibbonTab you created on strep2. Change the propertys Of this button like Text,Name etc by clicking on the property’s(Similarly we are doing for Windows Forms Controls)
After Adding buttons the project looks like this

Getting Started With VSTO, Stepbystep tutorial VSTO Project, VSTO Excel Ribbon using c#

Getting Started With VSTO, Stepbystep tutorial VSTO Project, VSTO Excel Ribbon using c#

Step4
Save the Project , build the project and click on the Run Button what you can seen is The Office excel is automatically opened and under Addins Tab you can see the Buttons that you create on step3

Getting Started With VSTO, Stepbystep tutorial VSTO Project, VSTO Excel Ribbon using c#

Getting Started With VSTO, Stepbystep tutorial VSTO Project, VSTO Excel Ribbon using c#

Step5
If you want to change the heading of tab and other stuffs related to this tab Click on the tab and change properties

Getting Started With VSTO, Stepbystep tutorial VSTO Project, VSTO Excel Ribbon using c#

Getting Started With VSTO, Stepbystep tutorial VSTO Project, VSTO Excel Ribbon using c#

And change the Name ThisAddin in solution Explorer under Excel Logo.

Getting Started With VSTO, Stepbystep tutorial VSTO Project, VSTO Excel Ribbon using c#

Getting Started With VSTO, Stepbystep tutorial VSTO Project, VSTO Excel Ribbon using c#

Step6
Adding a windows form to the solution (For this right click on projects Add new ItemChoose Windows Form).Name the form press OK.(I am adding a new form frmTask1 ).Design this form by adding a label(with text ” Enter Some Text”) , a textbox, and a button
Step7
Double click on one of the buttons you created on the ribbon. Now you got the area for writing code
private void FirstButton_Click(object sender, RibbonControlEventArgs e)
{

}

In the click event Area write this code(Make sure namespace Microsoft.Office.Interop.Excel is added on the top of page.If not include the namespace using code)
using Microsoft.Office.Interop.Excel;

frmTask1 myfrm = new frmTask1();
myfrm.Show();

Save the Project Build it and run the application and Now if you click on the Ribbon Button1 you will get the form that you added like

Getting Started With VSTO, Stepbystep tutorial VSTO Project, VSTO Excel Ribbon using c#

Getting Started With VSTO, Stepbystep tutorial VSTO Project, VSTO Excel Ribbon using c#


Get The Selected Row And Column No


Range selectedCell = Globals.HelloAddIn.Application.ActiveCell;
int selectedRow = selectedCell.Row;
int selectedCol = selectedCell.Column;

Pass These 2 values To the form while showing the form

frmTask1 myfrm = new frmTask1(selectedRow,selectedCol);
myfrm.TopMost = true;
myfrm.Show();

And on the form set these 2 values as public variables or properties .So we can use them any time on the form

public partial class frmTask1 : Form
{
public int Selectedrow = 0, SelectedrCol = 0;
public frmTask1(int row,int col)
{
Selectedrow = row;
SelectedrCol = col;
InitializeComponent();
}

}

Step 8
Get the Currently selected workshhet on the windows form

Worksheet activeworksheet = Globals.HelloAddIn.GetActiveWorksheet();

And Add the function GetActiveWorksheet() on to the HelloAddIn.cs page

public Excel.Worksheet GetActiveWorksheet()
{
return (Excel.Worksheet)Application.ActiveSheet;
}

On the form we are entering some text on the textbox and shows that text on the selected cell on the click event of button

private void button1_Click(object sender, EventArgs e)
{
Worksheet activeworksheet = Globals.HelloAddIn.GetActiveWorksheet();
activeworksheet.Cells[Selectedrow, SelectedrCol] = txtTextToShow.Text;
}

Make sure you initialize the values of selected row and selected column while showing the form.
If you add the line
this.Close();

Then the form will also getting closed.

Getting Started With VSTO, Stepbystep tutorial VSTO Project, VSTO Excel Ribbon using c#

Getting Started With VSTO, Stepbystep tutorial VSTO Project, VSTO Excel Ribbon using c#

Getting Started With VSTO, Stepbystep tutorial VSTO Project, VSTO Excel Ribbon using c#

Download FullCode Form Here For This Tutorial

excel automation/Com Addin using c# .screenshots

March 20, 2011 2 comments

The normal screen shot of Office Excel is shown here.You can see the built-in features like Home,Insert,PageLayout etc on the top of the excel sheet..

Excel Automation addin using c#

Excel Automation addin using c#


Now we can add our own tab say sound Vibration Functions –A set of user defined functions for doing some sound related calculations
Excel Automation addin using c#

Excel Automation addin using c#

The main idea behind this product is that create our own formulas say
a_to_v(AccelerationToVelocity)
a_to_d(AccelerationToDisplacement) etc
and register these formulas on excel .Now we can use this formulas from the Insert Function wizard of Office Excel Like accessing the inbuilt formulas like financial, Date&Time etc.

 Excel COM Addin using c#

Excel COM Addin using c#



Working of Some Formulas using these pluggin

Formula1
Select cell A1 and press formula Freq_1_1 and press on OK Button. The cells are filled with results based on the conditions.
Excel Automation Addn using c# and Excel COM Addin using c#

Excel Automation Addn using c# and Excel COM Addin using c#

If you are using normal function wizard for filling multiple cells u need to select the no of cells, presss control+shift+enter keys to get the results filled and again there are some disadvantages like we need to know the no of cells the results may going to fill and make selection accordingly and if the no of cells selected is more like if we want result in 5 columns and we select 10 column then we get #NA in the remaining columns etc…
With this approach all these are getting reduced.
Select cell C1 and press formula Freq_1_1 and press on OK Button with horizontal direction u get calls filled in horizontal direction.
Select cell C3 and press formula Freq_1_1 and press on OK Button with vertical direction and with units u get calls filled in horizontal direction.

Excel Automation Addn using c# and Excel COM Addin using c#

Excel Automation Addn using c# and Excel COM Addin using c#

Formula2
Suppose if we want to calculate the formula on n no of cells .The result is appear like this

Excel Automation Addn using c#

Excel Automation Addn using c#

The same thing can achieve using the ribbon by following 2 simple steps
Select the cell we need the result
Select the formula and select the cells and click oK button

Excel Automation Addn using c# and Excel COM Addin using c#

Excel Automation Addn using c# and Excel COM Addin using c#


Benefits
Save lot of time
Easy maintenance – if u want to remove the cells inbetween it is very difficult using the normal method.but using this pluggin its very easy

Excel Ribbon Using VSTO

January 5, 2011 Leave a comment

The final structre Of registry part of setup application that we created should be like this

And For Excel User Defined Function using class library the structre is something like this

Creating Excel Com Addin uisng VSTO

January 4, 2011 Leave a comment

For creating the excel com addin using VSTO(Visual studio Tools For Office ) the best tutorial souce that i found is Here
>Because of its simplicity..step by step description with screen shots etc..By following the articles in that site any one can create COM Addins using VSTO
But for deployment of the application is not described there for deployment you can reffer this site Here or My own post in this site..

A good blog for all the Vsto related stuffs here….

Creating An Excel Automation Addin using c#

January 4, 2011 Leave a comment

Hi all,

If you search this particular topic on the google or some other search engines ypu may find a no of articles describing the task…here is one link that i feel as an almost complete tutorial for this task

Link1
or
Link2
By following these 2 tutorials any one can achieve the task easily..

But there are some problems that you are going to face while deploying the sample application you just created (The above articles describes about deployment as well but the procedure is complex )..About that part i will explain in another post