Basics of Reflection in WPF

Reflection in WPF is a good feature to create better visual effects.

Create Visual Brush:
To create a basic reflection in Expression Blend we have to create a Visual Brush, to create one follow the steps.

1. Open Expression blend
2. Drop a image in the window
3. Select the image and Select Tools-> Make Brush Resouce -> Make VisualBrush Resouce -> Click Ok

As you do this a Visual brush of your image will be created that you can apply for any objects.

Lets test it.
Now add a rectangle, Select it and go to properties window, in Burshes Select Fill -> Brush Resouce
You will find the visual brush that is created Select it, the rectangle will fill with the image.
Once we know the steps to create a visual brush we can proceed with the reflection

Reflection:
Once we have the rectangle filled with the visual brush resouce, we get a lot of options how to reflect the image.

I will show you one example to start with

Select the Rectangle
Select Properties -> Transform -> Scale
Set X as -1

You will see a reflected image horizontally, the same reflection you can attain by giving -1 in Y.

Try all the options in Transform Tab and you will come to know about a lot of techniques.

Here for first Image i have made half of the Rectangle Transparent, by modifying a bit in Brushes -> Opacity mask.
For the second I have added 3 rectangles which are reflecting in different angles by modifying scale in Transform Tab, and then making the image 3d and rotating it.

Download Project : 2 Mb

Happy Learning

Video Library WPF

Hi,

I was searching for some good tutorials that would help me to learn WPF basics and here i have found one good WPF video library tutorial.

I just went through the tutorial and found it very helpful and easier to understand. I created the same project and added some zooming effects in it.

Download Project – 2.7 Mb

The zooming effects that i have added is explained in my post Zooming in WPF.

Happy learning

Zooming In WPF

Hi,

Today we shall see how we can zoom an image on hovering a mouse over it.
I will guide you the steps in this sample to acheive zooming in Expression blend.

Download Project 780KB

First, create a new project -> In project window (Window menu -> Project) right click on the project -> Add existing item -> browse a image file and add it.
Click on the image and it will get placed in the window.

Create Project and Add Image

From here i will be showing many steps in a single image, i will be marking the parts where modification is required with a circle, and numbers have been given next to the ovals to follow them in order.
Next, in interaction window ( Window Menu -> Interaction)

1. select the image
2. Cilck “+” button in objects and timeline. Enter OnMouseEnter in Name(Key) TextBox and Click Ok.
3. Move the timeline to 3 milliseconds
4. In Property window, select Transform tab, in it select scale tab
5. Set x to 1.5
6. set y to 1.5

For better understanding, move the yellow timeline from 0 to 3 ms and check how the image scales.
So we have created a timeline for what has to happen when the mouse enters in the image area. Next we have to create one more timeline to zoom out the image when the mouse is moved out of the image area. Lets see how we can do it.

1. select the image
2. Cilck “+” button in objects and timeline. Enter OnMouseLeave in Name(Key) TextBox and Click Ok.
3. In Property window, select Transform tab, in it select scale tab
4. Set x to 1.5
5. set y to 1.5
6. Move the timeline to 3 milliseconds
7. In Property window, select Transform tab, in it select scale tab
8. Set x to 1
9. set y to 1

Now we have timeline for mouse enter and mouse leave events, now we just need to bind this timelines to the actual events, here it goes

1. Open the triggers window
2. Click -trigger to remove the default event
3. Click +Event and a new event is created
4. Select image from the drop down (if image is not available select the image in object and timeline window)
5. Select MouseEnter event from the dropdown
6. Click +
7. Select the timeline to attach the event ( OnMouseEnter)

By this we have attached the mouse enter event with our time line, now we need to attach the mouse leave event with the other timeline.

1. Open the triggers window
2. Click +Event and a new event is created
3. Select image from the drop down (if image is not available select the image in object and timeline window)
4. Select MouseLeave event from the dropdown
5. Click +
6. Select the timeline to attach the event ( OnMouseLeave)

Now save the project and run it. when mouse enters the image area the image zooms in and when mouse leaves the image zooms out.

Hope you got the steps right. For any queries feel free to post a comment.

Happy Learning

Create, Execute and delete Macro in excel using C#

When we need any specific task to be done in excel through our program, we first do the same task manually in excel and record the macro to have a look how the function should be written in our code.

There are some functions those are hard to find in the Interop.Excel dll, for such things microsoft has given helpful functions which will help us to add the macro in the sheet and execute it. This will help us to reuse the same macro that we generate in excel with record macro.

And later on if the macro is still present in generated excel then while opening it prompts enable/disable macro, so to avoid it, we will see how the macro can be deleted through C#.

Here let me explain you with a sample.

Read Only Or Locked Cell In Excel

Lets imagine a case , we have to generate an excel of employees with their name and salary. We want no employee should alter the salary that is enterd in the sheet i.e we want it to locked or to make it read only.

For the above case we will try to acheive executing a macro.

First we will try to see how a cell can be locked in excel and what macro is generated by excel when we record a new macro.

1) Select a cell
2) Enter some data ( that is to be locked for modification)
3) select Data Menu -> Validation
4) In Allow select custom
5) In formula enter “” and click ok
6) Now check altering the data in that cell, it gives a message and does not let the user change the value.

Wow this is what we need. Fine, since we have recorded the macro we will open the visual basic editor and see what macro has been generated, ok here is what i got.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 11/05/2009 by naveen
'
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=""""""
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

now this is for the selection what we have done in the excel, to make more better when executing from external application we will pass a range parameter to the function, so that we can lock the input range of cells, let us also change the macro name to LockRange and change the message that is displayed to the user.

The new macro would look like.

Sub LockRange(oRange As String)
'
' Macro1 Macro
' Macro recorded 11/05/2009 by naveen
'
    Range(oRange).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=""""""
        .ErrorTitle = "Locked Cell"
        .ErrorMessage = "This cell is locked for modification."
        .ShowError = True
    End With
End Sub

Now since we know what macro to use to make the requirement possible, we just need to implement the same in our C# code, Here comes the code.

using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Vbe.Interop;

namespace LockRangeInExcel
{
class Program    
{
static void Main(string[] args)
{
Excel.Application oXL = null;
Excel._Workbook oWB = null;
Excel._Worksheet oSheet = null;
Excel.Range oRng = null;
_VBComponent oModule = null; // VBA Module            
try            
{
//Start Microsoft.Office.Interop.Excel and get Application object.
                oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = true;

//Get a new workbook.
                oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;

//Add table headers going cell by cell.
                oSheet.Cells[1, 1] = "First Name";
oSheet.Cells[1, 2] = "Salary(Read Only)";

//Format A1:D1 as bold, vertical alignment = center.
                oSheet.get_Range("A1", "B1").Font.Bold = true;
oSheet.get_Range("A1", "B1").VerticalAlignment =
Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;

// Create an array to multiple values at once.                
string[,] saNames = new string[5, 2];

saNames[0, 0] = "Naveen";
saNames[0, 1] = "10000";
saNames[1, 0] = "Girish";
saNames[1, 1] = "20000";
saNames[2, 0] = "Nandish";
saNames[2, 1] = "15000";
saNames[3, 0] = "Rashmi";
saNames[3, 1] = "40000";

//Fill A2:B6 with an array of values (First and Salary).
                oSheet.get_Range("A2", "B6").Value2 = saNames;

//AutoFit columns A:D.
                oRng = oSheet.get_Range("A1", "B1");
oRng.EntireColumn.AutoFit();

//Create macro in excel
                CreateMacro(oWB, oModule);

// Run VBA macro "MakeCellReadOnly" on a range                
string oRange = "B2:B6";
LockRange(oWB, oRange);

//For single cell you can use the function as follows                
//oRange = "C7";                
//MakeCellReadOnly(oWB, oRange);                

//Delete the created Macro
                DeleteMacro(oWB, oModule);

//Make sure Microsoft.Office.Interop.Excel is visible and give the user control                
//of Microsoft Microsoft.Office.Interop.Excel's lifetime.
                oXL.Visible = true;
oXL.UserControl = true;
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);

MessageBox.Show(errorMessage, "Error");
}

ReleaseComObj(oSheet);
ReleaseComObj(oWB);
ReleaseComObj(oXL);
}

private static void ReleaseComObj(object o)
{
try            
{
Marshal.ReleaseComObject(o);
}
catch            
{ }
finally            
{
o = null;
}
}

private static void DeleteMacro(Microsoft.Office.Interop.Excel._Workbook oWB, _VBComponent oModule )
{
Object oMissing = System.Reflection.Missing.Value;
oWB.Application.Run("DeleteThisModule"
, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
}

public static void LockRange(Excel._Workbook oWB, string oRange)
{
Object oMissing = System.Reflection.Missing.Value;
//The following command executes the macro by passing the range to the macro
            oWB.Application.Run("LockRange"
, oRange, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
}

public static void CreateMacro(Excel._Workbook oWB,_VBComponent oModule)
{
//Create a macro
            oModule = oWB.VBProject.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule);

//Create macro for locking the cell and delete the module            
string sCode =
"Sub LockRange(oRange As String)\r\n" +
"Range(oRange).Select\r\n" +
"With Selection.Validation \r\n" +
".Delete\r\n" +
".Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _\r\n" +
"xlBetween, Formula1:=\"\"\"\"\"\"\r\n" +
".ErrorTitle = \"Locked Cell\"\r\n" +
".ErrorMessage = \"This cell is locked for modification.\"\r\n" +
".ShowError = True\r\n" +
"End With\r\n" +
"End Sub\r\n" +
"Sub DeleteThisModule()\r\n" +
"Dim vbCom As Object\r\n" +
"Set vbCom = Application.VBE.ActiveVBProject.VBComponents\r\n" +
"vbCom.Remove VBComponent:= vbCom.Item(\"Module1\")\r\n" +
"End Sub\r\n";

//Add the macro in excel workbook that we have created
            oModule.CodeModule.AddFromString(sCode);

}
}
}

In VB.Net the same implementation can be done as

Module Module1
Sub Main()
Dim oXL As New Microsoft.Office.Interop.Excel.Application
Dim oWB As Microsoft.Office.Interop.Excel.Workbook
Dim OSheet As New Microsoft.Office.Interop.Excel.Worksheet
Dim oModule As Microsoft.Vbe.Interop.VBComponent

Try            

'Start Microsoft.Office.Interop.Excel and get Application object.
            oXL.Visible = True            
'Get a new workbook.
            oWB = oXL.Workbooks.Add()
OSheet = oWB.ActiveSheet

'Add table headers going cell by cell.
            OSheet.Cells(1, 1) = "FirstName"
            OSheet.Cells(1, 2) = "Salary(Read Only)"            

'Format A1:D1 as bold, vertical alignment = center.
            OSheet.Range("A1", "B1").Font.Bold = True
            OSheet.Range("A1", "B1").VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter

'Create an array to multiple values at once.            
Dim saNames(5, 2) As String
            saNames(0, 0) = "Naveen"
            saNames(0, 1) = "10000"
            saNames(1, 0) = "Girish"
            saNames(1, 1) = "20000"
            saNames(2, 0) = "Nandish"
            saNames(2, 1) = "15000"
            saNames(3, 0) = "Rashmi"
            saNames(3, 1) = "40000"            

'Fill A2:B6 with an array of values (First and Salary).
            OSheet.Range("A2", "B6").Value2 = saNames

'AutoFit columns A:D.
            OSheet.Range("A1", "B1").EntireColumn.AutoFit()

'Create macro in excel
            CreateMacro(oWB, oModule)

' Run VBA macro "MakeCellReadOnly" on a range            
Dim oRange As String = "B2:B6"
            LockRange(oWB, oRange)

'For single cell you can use the function as follows            
'oRange = "C7";            
'MakeCellReadOnly(oWB, oRange);            

'Delete the created Macro
            DeleteMacro(oWB)

'Make sure Microsoft.Office.Interop.Excel is visible and give the user control            
'of Microsoft Microsoft.Office.Interop.Excel's lifetime.
            oXL.Visible = True
            oXL.UserControl = True            

Catch        

End Try        

End Sub    

Private Sub DeleteMacro(ByVal oWB As Microsoft.Office.Interop.Excel.Workbook)
oWB.Application.Run("DeleteThisModule")
End Sub    

Private Sub LockRange(ByVal oWb As Microsoft.Office.Interop.Excel.Workbook, ByVal oRange As String)
'The following command executes the macro by passing the range to the macro
        oWb.Application.Run("LockRange", oRange)
End Sub    

Public Sub CreateMacro(ByVal oWB As Microsoft.Office.Interop.Excel.Workbook, ByVal oModule 
As Microsoft.Vbe.Interop.VBComponent)
'Create a macro
        oModule = oWB.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule)
'Create macro for locking the cell and delete the module        
Dim sCode As String = "Sub LockRange(oRange As String)" + Environment.NewLine + _
"Range(oRange).Select" + Environment.NewLine + _
"With Selection.Validation " + Environment.NewLine + _
".Delete" + Environment.NewLine + _
".Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:=" + _
"xlBetween, Formula1:=""""""""""" + Environment.NewLine + _
".ErrorTitle = ""Locked Cell""" + Environment.NewLine + _
".ErrorMessage = ""This cell is locked for modification.""" + Environment.NewLine + _
".ShowError = True" + Environment.NewLine + _
"End With" + Environment.NewLine + _
"End Sub" + Environment.NewLine + _
"Sub DeleteThisModule()" + Environment.NewLine + _
"Dim vbCom As Object" + Environment.NewLine + _
"Set vbCom = Application.VBE.ActiveVBProject.VBComponents" + Environment.NewLine + _
"vbCom.Remove VBComponent:= vbCom.Item(""Module1"")" + Environment.NewLine + _
"End Sub"        

'Add the macro in excel workbook that we have created
        oModule.CodeModule.AddFromString(sCode)

End Sub
End Module

To execute the above code, copy paste the whole code in your application. You need to add 2 dll’s to make the compilation successful they are,

• Microsoft.Office.Interop.Excel
• Microsoft.Vbe.Interop

And make sure you have given access to external application to access Excel. For more details visit

Let us brief on the methods that have been used in the above code:

CreateMacro :
Creates a new module in excel vb project and then adds the methods LockRange and DeleteThisModule in the module.

LockRange :
Accepts the range and locks the cells in the range so that the user will not be able to edit the data in those cell.
If tried to modify the data then prompts a proper message to the user.

DeleteMacro :
Deletes the macro that is created in the Excel VB Project, so that when user opens the excel file he will not be prompted with the message “Enable/Disable Macros”.

Happy Learning