Programmatic Execution of an Excel Macro on Remote Machine from a website

I tried searching everywhere for a solution on this issue but found nothing. To give you an idea what I want to achieve here is the scenario.

We have a website where users generate and Excel report using a macro, when I try to run it in my local machine it generates perfectly and runs the macro inside the excel. When I publish it into the server and at the same time I am logged in there (RDP open session) and try to run it from a browser outside that server it is also running as expected. The problem occurs when I am logged off in the server (RDP) then run it in a browser outside the server (ie from my machine) the macro does not run but creates my Excel.

This is the code that I am using

public class Report
{
    protected Workbook Workbook { get; set; }
    protected Application Excel { get; set; }

    public void RunReport()
    {
        // Launch Excel on the server
        Excel = new Application
        {
            DisplayAlerts = false,
            ScreenUpdating = false,
            Visible = false
        };

        // Load the workbook template  
        Workbook = Excel.Workbooks.Open(@"C:\{YourTemplateName}.xlt");

        // You your stuff here
        DoStuff();

        // Execute macros 
        ExecuteMacros();

        Workbook.SaveAs(@"C:\{YourExportedFileName}.xls", XlFileFormat.xlExcel8);

        // Properly Close Excel
        QuitExcel();

    }

    private void QuitExcel()
    {
        if (Workbook != null)
        {
            Workbook.Close(false);
            Marshal.ReleaseComObject(Workbook);
        }

        if (Excel != null)
        {
            Excel.Quit();
            Marshal.ReleaseComObject(Excel);
        }
    }    

    private void ExecuteMacros()
    {
        const string moduleName = "{YourModuleName}";
        const string macroName = "{YourMacroName}";

        bool macroExists = false;
        try
        {
            var macroModule = Workbook.VBProject.VBComponents.Item(moduleName);
            if (macroModule != null)
            {
                int macroStartLine = macroModule.CodeModule.ProcStartLine[macroName, Microsoft.Vbe.Interop.vbext_ProcKind.vbext_pk_Proc];
                macroExists = macroStartLine > 0;
            }
        }
        catch (Exception)
        {
            //no macro found
            macroExists = false;
        }

        if (!macroExists)
        {
            return;
        }

        // VBA code for the dynamic macro that calls 
        var moduleCode = new StringBuilder();
        moduleCode.AppendLine("Public Sub LaunchMacro()");
        moduleCode.AppendLine(string.Format("{0}.{1}", moduleName, macroName));
        moduleCode.AppendLine("End Sub");

        // Add the dynamic macro 
        var workbookMainModule = Workbook.VBProject.VBComponents.Item("ThisWorkbook");
        workbookMainModule.CodeModule.AddFromString(moduleCode.ToString());

        // Execute the dynamic macro
        Microsoft.VisualBasic.Interaction.CallByName(Workbook, "LaunchMacro", Microsoft.VisualBasic.CallType.Method, new object[] { });
    }
}

as explained everything works properly until you log off to the server.  Now after some investigation we notice that the Trust access to the VBA project object model is turned off when you log out of the server which makes your macro useless as it would not run.

It was a bit of a search to come up with this conclusion but nice to know there was a solution and here it is.

Since we know that is being turned off when a user logs out we have to create a method on our application to turn that on so we can run our macro happily and that is done through a registry setting like such.

private static void ModifyExcelSecuritySettings()
{
    // Make sure we have programmatic access to the project to run macros
    using (var key = Microsoft.Win32.Registry.CurrentUser.OpenSubKey(@"Software\Microsoft\Office\14.0\Excel\Security", true))
    {
        if (key != null)
        {
            if ((int)key.GetValue("AccessVBOM", 0) != 1)
            {
                key.SetValue("AccessVBOM", 1);
            }
            key.Close();
        }
    }
}

Just change the path depending on the Excel version you are using.

This is then used before you launch Excel on the server like such.

public void RunReport()
{
    ModifyExcelSecuritySettings();

    // Launch Excel on the server
    Excel = new Application
    {
        DisplayAlerts = false,
        ScreenUpdating = false,
        Visible = false
    };

    // Load the workbook template  
    Workbook = Excel.Workbooks.Open(@"C:\{YourTemplateName}.xlt");

    // You your stuff here
    DoStuff();

    // Execute macros 
    ExecuteMacros();

    Workbook.SaveAs(@"C:\{YourExportedFileName}.xls", XlFileFormat.xlExcel8);

    // Properly Close Excel
    QuitExcel();

}

BTW for those interested the site is deployed in IIS7 using Integrated Application Pool Identity and Passtrough Authentication / Identity Impersonation.

How to render MVC View on a Modal Popup Window

You might be wondering how to place an MVC View easily on a pop-up window like the image above that’s why you are in this page now?

Well you a bit lucky as I will tell you how easy is this to execute, but first of all let me tell you that I am using the Window Control from Telerik Extensions for ASP.NET MVC to make my life easy and not re-invent the wheel and if you don’t have problems with that then read ahead.

Now lets start.  Lets pretend we want an application to set up a new client Account and what we want is when a user clicks a link create account it will pop up a window for account creation.  So like any other MVC Application you need your Model which in this case we will call “AccountSetupViewModel” which is a model for setting up accounts, a View for the pop up which we can call “NewAccount.cshtml” and Controller which we can call “SetupController”.  We also need some JavaScript file to separate our JavaScript commands to others for a cleaner implementation.

Lets first make our ViewModel in AccountSetupViewModel.cs under Controllers -> ViewModels -> Setup folder, we will make it simple so it will only contain AccountCode and AccountName

public class AccountSetupViewModel
{
    [Required]
    public string AccountCode { get; set; }

    [Required]
    public string AccountName { get; set; }

}

Now lets create a query to execute with firstName and lastName as a parameter and name it as GetAccountViewModel which we will place in  AccountSetupQuery.cs under Controllers -> Queries -> Setup, you can also create an interface for it if you wish.  This method will combine the firstName and lastName and sets the AccountName viewModel.

public AccountSetupViewModel GetAccountViewModel(string firstName, string lastName)
{
    var viewModel = new AccountSetupViewModel();

    viewModel.AccountCode = "RSM";
    viewModel.AccountName = firstName + " " + lastName;

    return viewModel;
}

Now lets create our Controller called SetupController.cs just in the Controllers directory and create a method called “GetNewAccountViewHtml”, this will be the method that will output a JsonResult to render our view

[HttpPost]
public JsonResult GetNewAccountViewHtml(string firstName, string lastName)
{
    string viewHtml = string.Empty;
    var viewModel = accountSetupQuery.GetAccountViewModel(firstName, lastName);

    viewHtml = RenderRazorViewToString("NewAccount", viewModel);

    var hashtable = new Hashtable();
    hashtable["viewHtml"] = viewHtml;

    return Json(hashtable);
}

You notice we have a method called RenderRazorViewToString, like how it’s called its purpose is to Render the MVC Razor View to string.   We will be using the MVC engine to render the view model as HTML so we can easily place it on the pop-up window.  You can place it in the controller but best if there is a separate class for this as you will  definitely reuse this a lot.

private string RenderRazorViewToString(string viewName, object model)
{
    ViewData.Model = model;

    using (var stringWriter = new StringWriter())
    {
        var viewResult = ViewEngines.Engines.FindPartialView(ControllerContext, viewName);
        var viewContext = new ViewContext(ControllerContext, viewResult.View, ViewData, TempData, stringWriter);
        viewResult.View.Render(viewContext, stringWriter);
        viewResult.ViewEngine.ReleaseView(ControllerContext, viewResult.View);

        return stringWriter.GetStringBuilder().ToString();
    }
}

Next lets create a view and call it “NewAccount.cshtml” and place it in Views -> Setup, this is the view that we will be using on the pop-up window.

@model CI5.Web.Mvc.Controllers.ViewModels.Setup.AccountSetupViewModel
@using Telerik.Web.Mvc.UI;
@{
    Layout = null;
}

This is an MVC view in a pop up <br />
Account Code : @Model.AccountCode <br />
Account Name : @Model.AccountName

Now you have all of  the contents you need for that pop-up, now lets create a view to call the pop-up we can use “Default.cshtml” under View -> Setup Folder in this instance.  On your view it will be simple as registering JavaScript on an a link to pop up the window so put this on your view.

<li class="newclient"><a href="#newClient" title="" onclick="javascript:AccountSetupForm.displayPopUpWindow('Raymund', 'Macaalay');">New Client</a></li>

Then using a javascript file that initializes on load of the “_SiteLayout.cshtml“

<script src="@Url.Content("~/Scripts/AccountSetup.js")" type="text/javascript"></script>

which we call “AccountSetup.js” located in Scripts folder we will create a function trigger the Telerik window pop up.

var AccountSetupForm = (function () {
    return {
        init: function () {
        },
        displayPopUpWindow: function (firstName, lastName) {

            var postData = {
                firstName: firstName,
                lastName: lastName
            };

            $.post("/Setup/GetNewAccountViewHtml", postData, function (data) {
                $.telerik.window.create({
                    title: "Sample Window",
                    html: unescape(data.viewHtml),
                    modal: true,
                    resizable: false,
                    visible: false,
                    width: 500,
                    height: 200
                })
            .data('tWindow').center().open();
            });
        }
    };

})();

$(document).ready(function () {
    AccountSetupForm.init();
});

So for a full view on how this is structured please refer to the image below.  I highlighted what was used on the codes above.

Single ClientValidationFunction for Multiple Dynamic CustomValidator and CheckBox

I have been using CheckBoxes and CustomValidators for quite sometime now but never did I know that I will be presented with an issue that I never encountered before and that issue is regarding is dynamic CheckBoxes and dynamic CustomValidators.  To give you an idea on what I am talking about, if you had developed a website that has that “Agree to Terms and Conditions” required before continuing then your guess is a bit near the only difference is making the items dynamic where these questions are pulled from the database, that database table also defines whether the tick is required or not.

For the first scenario (non dynamic) its easy as you only need these controls

<asp:CheckBox ID="CheckBox1" runat="server" />
<asp:CustomValidator ID="CustomValidator1" runat="server" ErrorMessage="CustomValidator"
ClientValidationFunction="ValidateCheckbox"></asp:CustomValidator>

and this javascript to be consumed by the CustomValidator

<script language="JavaScript" type="text/javascript">
function ValidateCheckbox(source, args) {
    args.IsValid = args.IsValid = document.getElementById('<%= CheckBox1.ClientID %>').checked;
}
</script> 

But what if you have 50 checkboxes will you create one script for each one of them? Wouldn’t it be nice to just pass a parameter to your javascipt? By default you cannot do that as the javascript that the CustomValidator uses only 2 parameters which is the source and args.  But don’t worry we can use RegisterExpandoAttribute Method to extend an objects attribute.

So what does that method do? The answer is simple it registers a name/value pair as a custom attribute of the specified control.   Having that in mind we use it in the CustomValidator and extend its attributes to have the ClientID of the checkbox you want to validate. So for clarity refer to the example below.

So lets say we have the following controls that is dynamically driven by the GridVeiw

<asp:GridView ID="myGridView" runat="server" AutoGenerateColumns="False" OnRowDataBound="myGridView_RowDataBound">
    <Columns>
        <asp:TemplateField>
            <ItemTemplate>
                <asp:Label ID="lblQuestionItem" runat="server" Text='<%# Bind("QuestionItem") %>'
                    CssClass="Normal"></asp:Label>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField>
            <ItemTemplate>
                <asp:HiddenField ID="hdnIsRequired" runat="server" Value='<%# Bind("IsRequired") %>' />
                <asp:CheckBox ID="chkAnswer" runat="server" CssClass='<%# Bind("ControlClass") %>' />
                <asp:CustomValidator ID="ctmCHKAnswer" ClientValidationFunction="ValidateCurrentCheckbox"
                    runat="server" ErrorMessage="*"></asp:CustomValidator>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

Now from the sample above we defined a CustomValidator and that’s the object that we will extend the attributes to contain the chkAnswer‘s client ID, to do that here is the code behind, also remember that you need to do that on a per row basis as well thats why it is in myGridView_RowDataBound, and here is how to achieve it.

protected void myGridView_RowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        HiddenField hdnIsRequired = (HiddenField)e.Row.FindControl("hdnIsRequired");
        CheckBox chkAnswer = (CheckBox)e.Row.FindControl("chkAnswer");
        CustomValidator ctmCHKAnswer = (CustomValidator)e.Row.FindControl("ctmCHKAnswer");

        Page.ClientScript.RegisterExpandoAttribute(ctmCHKAnswer.ClientID, "ClientID", chkAnswer.ClientID, false);
        ctmCHKAnswer.Enabled = bool.Parse(hdnIsRequired.Value);
    }
}

If you noticed we extended ctmCHKAnswer with the “ClientID” attribute that comes from chkAnswer.ClientID.  So when you fire the JavaScript you can then now use the source parameter and use source.ClientID to get the checkbox client ID you need.

<script language="JavaScript" type="text/javascript">
function ValidateCurrentCheckbox(source, args) {
    var checkbox = document.getElementById(source.ClientID)
    args.IsValid = checkbox.checked;
}
</script>

How to enable code blocks in Sharepoint 2010 (Works also in 2007)

If you have a developer background, your company uses Sharepoint and they want you add a lot of functionalities to that Sharepoint instance, by nature as a programmer you will try to modify the aspx files and use your choice of .Net language.  So you started to download Sharepoint desinger and have a look on what’s running under the hood and how to modify it.

After downloading Sharepoint designer it also occured to you that you can create custom forms as when you right click on Site pages, you can add an aspx file.

Now you added your new file and excited to do coding so you tried modify your new aspx file to look like this:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<%@ Page Language="C#" %>
<html dir="ltr" xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">
<meta name="WebPartPageExpansion" content="full" />
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled 1</title>
</head>

<body>

<form id="form1" runat="server">
</form>
<script runat="server"> 
protected void Page_Load(object sender, EventArgs e)
{
	Response.Write("Enable Code Blocks");
}
</script>
</body>

</html>

Then you tried to browse on it and this appears:

An error occurred during processing of “YOUR PAGE URL”. Code blocks are not allowed in this file.

Your first reaction is WTF, you let me do the coding but you it wont allow me to run it.

To be able to code in Sharpoint you need to enable it on web config.  To enable it go to your WSS Virtual Directory.  The default directory is C:\inetpub\wwwroot\wss\VirtualDirectories\80 and modify your web.config (back it up first).

Now under Configuration –> Sharepoint –> SafeMode –> PageParserPaths add a new section for PageParserPath.

To enable code blocks on a Folder do a wildcard like such

<PageParserPath VirtualPath="/TeamSite/CustomForms/*" CompilationMode="Always" AllowServerSideScript="true"  IncludeSubFolders="true"/>

Otherwise, indicate a file like such

<PageParserPath VirtualPath="/TeamSite/CustomForms/Test.aspx" CompilationMode="Always" AllowServerSideScript="true" /> 

Now there are some additional properties you can assign like CompilationMode, AlowServerSideScript and IncludeSubFolders and I guess the two latter parts are self explanatory but for CompilationMode you have the following options:

• Always – The default value, which compiles the page always
• Auto – Page will not be compiled if possible
• Never – The page will not be dynamically compiled

Once you have done that the piece of code above should work, and be able to treat that aspx page like a normal aspx page on your web project.

Simple CAPTCHA, Create your own in C#

Have you ever wondered how to create a Captcha for your web forms, well its really simple and it wont take more than 100+ lines of code.  This sample captcha that I will demostrate will have random Font Type, Font Style, Font Color, Font Rotation, Background Style and Background Colour.   Here are some samples of what this can generate:

Now without too much explanation, here it goes.

Step 1 : In your web project add a Generic Handler thats the file with the.ahsx extension, Ill just use this for this demo for simplicity and also it will compile on demand like a normal aspx file.  Its up to you if you want to create a dll which is better so you can reuse it in any projects you want.  To do that right click on the project on your Solution Explorer.

Then choose your file type, in this sample its a Generic Handler

Step 2 : Once added you can start coding, here are the sample codes which you can freely copy.

<%@ WebHandler Language="C#" %>
using System;
using System.Web;
using System.Drawing;
using System.IO;
using System.Web.SessionState;
using System.Drawing.Imaging;
using System.Drawing.Text;
using System.Drawing.Drawing2D;
public class Captcha : IHttpHandler, IReadOnlySessionState
{
public void ProcessRequest(HttpContext context)
{
int iHeight = 80;
int iWidth = 190;
Random oRandom = new Random();

int[] aBackgroundNoiseColor = new int[] { 150, 150, 150 };
int[] aTextColor = new int[] { 0, 0, 0 };
int[] aFontEmSizes = new int[] { 15, 20, 25, 30, 35 };

string[] aFontNames = new string[]
{
 "Comic Sans MS",
 "Arial",
 "Times New Roman",
 "Georgia",
 "Verdana",
 "Geneva"
};
FontStyle[] aFontStyles = new FontStyle[]
{  
 FontStyle.Bold,
 FontStyle.Italic,
 FontStyle.Regular,
 FontStyle.Strikeout,
 FontStyle.Underline
};
HatchStyle[] aHatchStyles = new HatchStyle[]
{
 HatchStyle.BackwardDiagonal, HatchStyle.Cross, HatchStyle.DashedDownwardDiagonal, HatchStyle.DashedHorizontal,
 HatchStyle.DashedUpwardDiagonal, HatchStyle.DashedVertical, HatchStyle.DiagonalBrick, HatchStyle.DiagonalCross,
 HatchStyle.Divot, HatchStyle.DottedDiamond, HatchStyle.DottedGrid, HatchStyle.ForwardDiagonal, HatchStyle.Horizontal,
 HatchStyle.HorizontalBrick, HatchStyle.LargeCheckerBoard, HatchStyle.LargeConfetti, HatchStyle.LargeGrid,
 HatchStyle.LightDownwardDiagonal, HatchStyle.LightHorizontal, HatchStyle.LightUpwardDiagonal, HatchStyle.LightVertical,
 HatchStyle.Max, HatchStyle.Min, HatchStyle.NarrowHorizontal, HatchStyle.NarrowVertical, HatchStyle.OutlinedDiamond,
 HatchStyle.Plaid, HatchStyle.Shingle, HatchStyle.SmallCheckerBoard, HatchStyle.SmallConfetti, HatchStyle.SmallGrid,
 HatchStyle.SolidDiamond, HatchStyle.Sphere, HatchStyle.Trellis, HatchStyle.Vertical, HatchStyle.Wave, HatchStyle.Weave,
 HatchStyle.WideDownwardDiagonal, HatchStyle.WideUpwardDiagonal, HatchStyle.ZigZag
};

//Get Captcha in Session
string sCaptchaText = context.Session["Captcha"].ToString();

//Creates an output Bitmap
Bitmap oOutputBitmap = new Bitmap(iWidth, iHeight, PixelFormat.Format24bppRgb);
Graphics oGraphics = Graphics.FromImage(oOutputBitmap);
oGraphics.TextRenderingHint = TextRenderingHint.AntiAlias;

//Create a Drawing area
RectangleF oRectangleF = new RectangleF(0, 0, iWidth, iHeight);
Brush oBrush = default(Brush);

//Draw background (Lighter colors RGB 100 to 255)
oBrush = new HatchBrush(aHatchStyles[oRandom.Next(aHatchStyles.Length-1)], Color.FromArgb((oRandom.Next(100, 255)), (oRandom.Next(100, 255)), (oRandom.Next(100, 255))), Color.White);
oGraphics.FillRectangle(oBrush, oRectangleF);

System.Drawing.Drawing2D.Matrix oMatrix = new System.Drawing.Drawing2D.Matrix();
int i = 0;
for (i = 0; i <= sCaptchaText.Length - 1; i++)
{
 oMatrix.Reset();
 int iChars = sCaptchaText.Length;
 int x = iWidth / (iChars + 1) * i;
 int y = iHeight / 2;

 //Rotate text Random
 oMatrix.RotateAt(oRandom.Next(-40, 40), new PointF(x, y));
 oGraphics.Transform = oMatrix;

 //Draw the letters with Randon Font Type, Size and Color
 oGraphics.DrawString
 (
 //Text
 sCaptchaText.Substring(i, 1),
 //Random Font Name and Style
 new Font(aFontNames[oRandom.Next(aFontNames.Length - 1)], aFontEmSizes[oRandom.Next(aFontEmSizes.Length-1)], aFontStyles[oRandom.Next(aFontStyles.Length - 1)]),  
 //Random Color (Darker colors RGB 0 to 100)
 new SolidBrush(Color.FromArgb(oRandom.Next(0, 100), oRandom.Next(0, 100), oRandom.Next(0, 100))),
 x,
 oRandom.Next(10, 40)
 );
 oGraphics.ResetTransform();
}

MemoryStream oMemoryStream = new MemoryStream();
oOutputBitmap.Save(oMemoryStream, System.Drawing.Imaging.ImageFormat.Png);
byte[] oBytes = oMemoryStream.GetBuffer();

oOutputBitmap.Dispose();
oMemoryStream.Close();

context.Response.BinaryWrite(oBytes);
context.Response.End();
}
public bool IsReusable
{
get
{
 return false;
}
}
}

There are some commented explanation along the way so you wont get lost, but you can still ask me if you really need.

Step 3 : Use that Captcha.  On your aspx add the following as an image like such

<asp:Image ID="imgCaptcha" ImageUrl="Captcha.ashx" runat="server" />

If you noticed its like your using it as an image, this is because its an image rendered by that handler which you can see on the bottom part of the captcha code

context.Response.BinaryWrite(oBytes);

Step 4 : Assign a text on that captcha on your code behind

private void SetCaptchaText()
{
 Random oRandom = new Random();
 int iNumber = oRandom.Next(100000, 999999);
 Session["Captcha"] = iNumber.ToString();
}

Note : You may notice that I am using only numbers in this regard, this would be easy so users cannot be confused with l and 1, 0 and o, and other similar looking characters.
Note: By setting the session you are also setting the text to render on the captcha and if you look at the captcha codes it is in this section

string sCaptchaText = context.Session["Captcha"].ToString();

Step 5 : Compare that to value to what the users had keyed in to your text box

if (Session["Captcha"].ToString() != txtCaptcha.Text.Trim())
 {
 Response.Redirect("Failed.aspx");
 }
 else
 {
 Response.Redirect("Success.aspx");
 }

Thats it, Congratulations you have created your own captcha