Array formulas in Excel

Perhaps one of the most useful yet least known features of Excel is its array formulas. Essentially, array formulas are standard Excel expressions which are evaluated over a range of cells, rather than just a single cell.

In my case, I needed to aggregate the status of various DevDoctor projects, tracked in an Excel workbook:

Each row against a status (D5:D9) must aggregate a count of that status from a “status” column (here, column F). Using standard formulas alone, this is not possible (unless you resort to VBA scripting); however, using array formulas, this becomes a breeze.

The formula in cell D5 (counting the projects in Pending status) is:

=SUM(IF((F$12:F$42=C5),1,0))

When you press CTRL+SHIFT+ENTER on this formula, Excel converts it to an array formula, and indicates this with curly braces:

The expression is slightly strange, with a conditional being evaluated over a range of cells (F$12:F$42), rather than the usual single cell. In effect Excel calculates the inner IF expression once for each cell in the range, and the outer SUM expression then produces the required summation.

Subversion made easy: VisualSVN Server

Subversion is fairly straightforward to install and maintain, but VisualSVN Server makes the process on Windows almost trivial:

  1. Download
  2. Run Installer
  3. err, that’s it

It bundles Apache and the SVN server, and comes with a nifty Admin console which makes it easy to change repo permissions. It even provides pretty repo browsing via XSLT in the web browser.

IISAdmin – multiple sites on Windows XP

The inability to run multiple concurrent websites from IIS under Windows XP is a pain. I need to run Server 2003 and IIS 6 to do that, and live with the resulting slowdown

IISAdmin Screenshot

There is a handy free utility, IISAdmin, which mitigates the worst aspect of the IIS 5 limitation: the need to reconfigure the Default Web Site each time I want to work with a different site. IISAdmin allows you to create new websites, each permanently pointing to a different set of files on disk. The limitation (probably in IIS 5 on XP) is that only a single site can be started at a time.

Work around limitations of VS 2005 Deployment Projects with Windows Services

I needed to deploy a Windows Service using an MSI. The project was built in VS 2005 using .Net 2.0; so far, so good.

The problem arose when I wanted to have the Service write to its own Event Log, rather than the default Application log. The standard Installer class from System.Configuration.Install, which otherwise does a fine job of installing the Windows Service, proved less than ideal because it automatically creates an EventLogSource with the same name as the Service executable, and sets its event log to be Application.

The solution lies in the way in which the Installer class contains a series of other installers in the Installer.Installers collection, which in turn contain other installer objects.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration.Install;
using System.Diagnostics;

namespace MyService
{
  [RunInstaller(true)]
  public partial class ProjectInstaller : Installer
  {
    public ProjectInstaller()
    {
      InitializeComponent();

      //
      // ProjectInstaller
      //
      this.Installers.AddRange(new System.Configuration.Install.Installer[] {
            this.serviceProcessInstaller1,
            this.serviceInstaller1});
    }

    private void serviceInstaller1_BeforeInstall(object sender, InstallEventArgs e)
    {
      RemoveEventLogInstallers(this, 0);
    }

    ///
    /// Recursive method to remove EventLogInstaller Installers from the Installers collection.
    /// This is needed to avoid automatic setting up of the event logs
    private static void RemoveEventLogInstallers(Installer installer, int depth)
    {
      Console.Out.WriteLine(String.Format("Number of installers at depth {0}: {1}", installer.Installers.Count, depth));
      for (int i = installer.Installers.Count - 1; i >= 0; --i)
      {
        Installer childInstaller = installer.Installers[i];
        Console.Out.WriteLine(String.Format("Installer {0} at depth {1} is of type: {2}", i, depth, installer.GetType().Name));

        RemoveEventLogInstallers(childInstaller, depth + 1); //recurse

        if (childInstaller is EventLogInstaller)
        {
          Console.Out.WriteLine(String.Format("Removing Installer {0} at depth {1} because it is of type: {2}", i, depth, childInstaller.GetType().Name));
          installer.Installers.Remove(childInstaller);
        }

      }
    }

    private void serviceInstaller1_BeforeUninstall(object sender, InstallEventArgs e)
    {
      RemoveEventLogInstallers(this, 0);
    }
  }
}

Search recursively through the entire tree of Installer objects and remove any installer whose type is EventLogInstaller, and the problem is solved! Make sure to do this before Install and before Uninstall, otherwise the (un)installation does not complete successfully.

Calling Javascript from C#

Call JavaScript from C# code, passing parameters and returning results.

I came across a need to call JavaScript methods on HTML code inside a web page from a C# WinForms application. How could that language gap be bridged?

It turns out that Type.InvokeMember() does the trick when using a [Internet Explorer] WebBrowser control to load a web page.

using mshtml;
...
private AxSHDocVw.AxWebBrowser axWebBrowser1;
...
///
 /// Retrieves a reference to the HTML element containing a DIV. /// JavaScript methods are implemented on this element itself. /// 

/// A reference to the HTML element holding the JavaScript methods
private HTMLDivElementClass GetContainer()
{
 HTMLDivElementClass container = null;
 IHTMLDocument2 doc = axWebBrowser1.Document as IHTMLDocument2;

 if (null != doc)
 {
  foreach (IHTMLElement element in doc.all)
  {
   if (element.id == "My_Container")
   {
    container = element as HTMLDivElementClass;
    break;
   }
  }
 }

 return container;
}

///
 /// Gets the text from the container /// 

/// A string containing the text of the container
private string GetText()
{
 string result = null;
 HTMLDivElementClass div = GetContainer();
 if (null != div)
 {
  Type type = div.GetType();
  result = (string) type.InvokeMember(
    "GetText",
    BindingFlags.InvokeMethod,
    null,
    div,
    null);
 }   

 return result;
}

The HTML page loaded in the WebBrowser control has a DIV element with an id of “My_Container”, attached to which is a method called GetText(), which returns some arbitrary text.

Having acquired a reference to the DIV on which the JavaScript method is declared (using GetContainer()), the JavaScript method is called using the InvokeMember() method of the Type class instance; the return value is cast to a string.

Discussion

There is every reason why this should NOT work, but the implementers of the WebBrowser control decided that JavaScript methods living within the DOM should be accessible as first class object members at runtime (in this case, via IDispatch). Nice!

Result

C# code can call arbitrary JavaScript within an HTML page! The only limitation is that the return types of the JavaScript methods can be only simple types (even DateTime is too complex).