Archive

Archive for April, 2011

Check The String is a valid Excel CellName or not using c#

April 3, 2011 Leave a comment

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.

public bool checkcellRangeValidity(string inputrange)
{ bool valid = true;
string[] parts = inputrange.Split(':');
if (parts.Length == 2)
{
if (!((validcell(parts[0])) && (validcell(parts[1]))))
valid = false;
}
else //only one part or more parts on both sides of ':'
{
valid = false;
}
return valid;
}

Give string is a valid cell or not
A1: valid
A-not valid
1 not valid
1A not valid

public bool validcell(string inputcell)
{
bool valid = true;

int count = 0;
int digitscount = 0;
int lettercount = 0;

foreach (char c in inputcell)
{
if (char.IsDigit(c))
digitscount++;

if (char.IsLetter(c))
lettercount++;

if (!char.IsLetterOrDigit(c))
{
valid = false;
break;
}

if (count == 0) //means the very first char should be letter
{
count++;
if (!char.IsLetter(c))
{
valid = false;
break;
}

}
}

if (valid)
{ //chk only if the conditions are true till
if (!((digitscount >= 1) && (lettercount >= 1)))
{
// the cell is lagging either a no or a letter

valid = false;
}
}

return valid;
}

Categories: C# Programming Tips Tags:

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

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