Excel with Macros

Currently I am using Excel 2010. So we are going to create simple macros here.

What is Macro ?
A very simple answer is,
Through macros you don’t need to perform same task or action on same type of data. Macros are the built-in feature of Excel that enables user to record different tasks and apply them over on some another portion of the data. It works on the back-end, recording each and every mouse click, keystrokes that you do while performing the task. Down the line if you want to done with the task in a same way, you will just have to play the recorded Macro back.


Suppose I have a worksheet in excel and I want to display product "Grade" and "Remark" according to product "Quality" column. If product quality greater than or equal to 70 then Grade should be "A" and Remark should be "Better" automatically, as smart macros fired in background.



 Exploring step by step :

 Step1 : Go to Start -> Microsoft Excel 2010 , blank worksheet will be open for you.

 Step2 : Click on View -> Micros , See below image

 


Step3 : Click on Record Macros, pop up window will be open, you can set your macro name,
shortcut key , store macro in dropdown option and description and click Ok. See below image

Step4 : Now set fx function on Grade column as =IF(B2>=70,"A","B") and set Remark column as
 =IF(C2="A","Better","Average").

Step5 : Click on IF function and click OK button to set condition for Grade column , See below Image.

Step5 : Click on IF function and click OK button to set condition for Remark column , See below Image.

   Save Excel file Type as Excel - Macro Enabled Workbook (*.xlsm).

  Step6 : Get Grade and Remark column value by giving input to Quality column through UI.
  -> Go to Start open  Microsoft Visual Studio 2010.
  -> Click on New Project you can choose Windows Or Web base application. I am exploring with windows base. See below Image :

Step7 : Go to Solution Explorer -> Right Click On References -> Add Microsoft Excel Lib reference from COM, See below Image :

Step8 : Adding Dropdown list (selecting input for Excel) , and two Text Boxes (to getting output from Grade and Remark column from Excel) See below Form1 Image :

Step8 : Adding Dropdown list collection value.
Right click on DropdownList -> Click on Properties -> Items property and add Items as,
See below Image :

Step8 : Double click on Dropdownlist. Add below code to Dropdown selected index changed event.

Code:

private void cboInputBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            ExcelApplicationClass excelApp = new ExcelApplicationClass();
            ExcelWorkbook workbook = (Excel.Workbook)excelApp.Workbooks.Add(Missing.Value);
            ExcelWorksheet worksheet;

            // Opening excel file

            //workbook = excelApp.Workbooks.Open(@"C:\Book1.xslx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            workbook = excelApp.Workbooks.Open(@"C:\testing proj\Book3.xlsm", 0, false, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            
            // Getting first Worksheet

            worksheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);

            // Setting cell values


            ((Excel.Range)worksheet.Cells["2", "B"]).Value2 = cboInputBox.SelectedItem.ToString();
            txtGrade.Text = ((Excel.Range)worksheet.Cells["2", "C"]).Value2.ToString();
            txtRemark.Text = ((Excel.Range)worksheet.Cells["2", "D"]).Value2.ToString();
            
            workbook.Save();
            workbook.Close(0, 0, 0);
            excelApp.Quit();
        }

Run The Application and select Input value from Dropdownlist you will get output at Grade and Remark column from excel. Also open an excel to see all values are getting reflect.

Copy Full Code :

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;

namespace ExcelOperation
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
           
        }

        private void cboInputBox_SelectedIndexChanged(object sender, EventArgs e)
        {
            ExcelApplicationClass excelApp = new ExcelApplicationClass();
            ExcelWorkbook workbook = (Excel.Workbook)excelApp.Workbooks.Add(Missing.Value);
            ExcelWorksheet worksheet;

            // Opening excel file

            //workbook = excelApp.Workbooks.Open(@"C:\Book1.xslx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            workbook = excelApp.Workbooks.Open(@"C:\testing proj\Book3.xlsm", 0, false, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            
            // Getting first Worksheet

            worksheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);

            // Setting cell values


            ((Excel.Range)worksheet.Cells["2", "B"]).Value2 = cboInputBox.SelectedItem.ToString();
            txtGrade.Text = ((Excel.Range)worksheet.Cells["2", "C"]).Value2.ToString();
            txtRemark.Text = ((Excel.Range)worksheet.Cells["2", "D"]).Value2.ToString();
            
            workbook.Save();
            workbook.Close(0, 0, 0);
            excelApp.Quit();
        }


    }
}

Out keyword in C#

What is out.. ? Out keyword is like ref but here you must not initialize the variable before passing to calling method. ,

Purpose of this keyword when you want to returned one or more than one value from method.
Simply we explicitly copies the reference or address from calling method store at stack and again rewritting it to that references or addresses and return back to calling method from method called.

Here are some snaps, that will explore the work flow of Out keyword.

Look at Snap No.1 :

  Typically program will start to execute from Main method, here i have taken three variables and i will pass  these variable to calling method with out keyword, means i am passing addresses through argument to called method parameter.


Look at Snap No.2 :

Here method is going to call with out keyword ...

 Look at Snap No.3 :

 Here we explicitly copying reference or addresses from calling method to method parameter  para_One,para_Two and para_Three.

Picturized memory locations for para as :

     para_One                 para_Two                  para_Three

null	null	null
20001	20002	20003

 Look at Snap No.4 :

 I have assigned text to para_One,para_Two and para_Three.

 Look at Snap No.5 :

 Greetings !!! all values from method got returned and rewritten to address [20001], [20002], [20003] which are previously passed through calling method. 

 What we did ? Simply copied that text to address loaction.

 arg_One                       arg_Two                  arg_Three

argument 'arg_one'add = 20001 ..          value part	argument 'arg_one'add = 20002..          value part	argument 'arg_one'add = 20003 ..              value part
20001    add part	20002    add part	20003    add part

   Look at Snap No.6 :

  OutPut:

Full Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

            namespace ConsoleApplication1
             {
              class out_keyword
               {
                public static void method(out string para_One,out string para_Two,out string para_Three)
                 {
para_One = "argument 'arg_One' address = 20001 from calling method and" + Environment.NewLine + "copied into called method parameter 'para_One' and rewritten to arg_One";
para_Two = "argument 'arg_Two' address = 20002 from calling method and" + Environment.NewLine + "copied into called method parameter 'para_Two' and rewritten to arg_Two";
para_Three = "argument 'arg_Three' address = 20003 from calling method" + Environment.NewLine + "copied into called method parametere 'para_Three' and rewritten to arg_Three";   
                 }
        public static void Main(string[] args)
        {
            string arg_One, arg_Two, arg_Three;

            method(out arg_One, out arg_Two, out arg_Three);
            Console.WriteLine("{0}\n\n{1}\n\n{2}",arg_One,arg_Two,arg_Three);
            Console.ReadKey();
        }

    }
}