Tag Archives: Excel

A sample Wix installer using the ActiveSetup feature

Windows Users

All users have the software in the case of a local machine install contrary to user install

In this post I will explain the technical details of the Wix sample installer for local machine install of Excel-DNA addins. Wix is a popular free toolset to create .msi installer. I used the ActiveSetup feature to address a “per user” install problem and this is the part I will cover in this post. Therefore, it maybe useful for both who do not know ActiveSetup at all and for people looking out to write their own Wix installer leveraging the ActiveSetup. Even, if I think ActiveSetup should be used only if there is no alternative this barely known feature may save your life so it is good to be aware of its existence. We will use the terminology of the Windows Registry that you can check on the Wikipedia page.

First let us present the problem and why we needed this ActiveSetup feature. Automation addins and by consequent Excel-DNA ones are registered by setting a value OPEN in the current user registry hive (HKCU). This OPEN value contains the path to the packaged Excel addin which a file with .xll extension. If there are two addins to register then the values should be named OPEN, OPEN2, OPEN3… see this StackOverflow discussion or this MS documentation. This is a per user registration, if you create a new user, then he will not have the addin starting with Excel. There is a WixSample available on the Excel-DNA github. This is fine as long as you need only a per user install, however, for enterprise deployment you need most of the time a local machine install. Here starts the troubles: the OPEN value mentioned above cannot be set as a HKLM (machine) subkey only in the current user hive (HKCU). Therefore we have to find a way to setup this OPEN value for all users and this is where the the ActiveSetup comes into play. We have also tried to set the addin in the XLSTART machine directory but this has also drawbacks see this discussion

So what is this ActiveSetup feature? ActiveSetup is a simple yet powerful mechanism built-in in Windows that allows you to execute a custom action at logon for new user. How does it work? Actually this is quite simple. You have to create a subkey in the HKLM hive Software/Microsoft/ActiveSetup/Installed Components. Typically this subkey is a GUID that contains a StubPath REG_EXPAND_SZ value whose data points to the executable of your choice. This value is mirrored in the HKCU registry. Precisely, at logon windows checks if you have this ActiveSetup key in the current user HKCU hive, and if not, the executable of the StubPath is invoked. If the key is present in the HKCU registry then nothing happens. The nice thing is that localization, version and uninstall are handled. Let us detail the uninstall mechanism. Actually, if you need to remove your feature, then you can set the IsInstalled feature to 0 and change the StubPath so that the command there executes a cleaning task of your choice. At logon if the HKCU subkey of active setup was registered previously then the command in the StubPath will be executed. The versioning allows you to reexecute the ActiveSetup command even if the ActiveSetup has already been executed by the user i.e. if the HKLM ActiveSetup key is already mirrored in the HKCU hive. ActiveSetup is poorly (read not) documented but you may find information on the web for example in this good article.

ActiveSetup set in the HKLM registry for the Wix sample installer

ActiveSetup set in the HKLM registry for the Wix sample installer

Ok now, how we will use this for our Wix Install? In addition to the common HKLM registration, our sample builds a .NET40 executable binary called managedOpenKey.exe. This .exe, when invoked with /install parameter, looks at the HKCU hive and creates the OPEN subkey mentioned in the second paragraph, so that the addin will be opened when starting Excel. When the .exe is invoked with /uninstall it will remove the OPEN value, if present, in the HKCU. This is simple: at install/ repair/upgrade our Wix creates the ActiveSetup HKLM key with a StubPath of the form “manageOpenKey.exe /install “. For uninstall, we set the IsInstalled value to zero and change the StubPath to “manageOpenKey.exe /uninstall”. To do so we use some custom actions written in .NET40 of the Wix installer, they must be invoked without impersonation so that we are allowed to write in HKLM.

We wished to allow the usage of the addin without a reboot. Therefore, at install/repair/upgrade the msi quietly invokes the same manageOpenKey.exe so that, for the current user performing the install we do not need the reboot and wait for the active setup to create the HKCU OPEN value. But there is a trick…. We must force the mirroring of the ActiveSetup key in the HKCU registry. Indeed, take the following example: user 1 installs the addin; the OPEN key is registered because we have invoked the .exe directly in the .msi. Now user1 logs off and user2 logs on, ActiveSetup is triggered for him and the addin is registered and everyone is happy. But now user2 decides to uninstall the addins and does so. The user1 when he logs back and opens Excel will have an error message saying that the addin is not found. What happened? The HKCU key for ActiveSetup was not mirrored in the user1 HKCU therefore ActiveSetup does not consider that the feature was installed for him then it does not trigger the StubPath command for cleaning environment when the IsInstalled has been set to false. Tricky, isn’t it? These manual creation of HKCU key are made by C# custom actions that should be impersonated because you want to write your key in the HKCU of the user who made the install not the LocalAdministrator that runs the custom action not impersonated such as those mentioned in previous paragraph.

There is also a trick regarding the OS bitness. If you do not take care and write the ActiveSetup in the HKLM hive within a 32bit process on a 64bit OS then your active setup will be written under /Wow6432 path. However, when performing the HKCU mirroring mentioned above the .NET API will write in HKCU/Software/Microsoft/Active Setup because it is not supposed to have a Wow6432 key in HKCU. However, there is Wow6432 HKCU key used only by ActiveSetup and you should be cautious. Indeed, the ActiveSetup in /Wow6432 and the one directly under HKLM/Software/Microsoft do not interact. Therefore, in our Wix CustomActions we have to check OS bitness and write the HKLM part in the proper slot with the following code snippet.

To conclude, I would like to thank my colleague Sébastien Cadorel who helped me a lot to write down this install sample. If you encounter any troubles with this install sample feel free to submit a bug or to pull request on the github.

An unexpected quadratic cost in Excel Interop

My current task at work is the development of an excel addin. This addin is developed in C# .NET and is based on the Excel Interop assemblies, we also use Excel DNA for the packaging and the User Defined functions. While developing a new feature I stumbled upon a technical oddity of Excel Interop which I will describe to you in this post.

Let me start this post by reminding you that a range in Excel is not necessarily a block of contiguous cells. Indeed, try it yourself by starting excel right now. Then you can select a range, keep the Ctrl button of your keyboard press on and select an other block of contiguous cell. Then, you have several cells selected that you can name (as shown in the screenshot). Finally, you have created a named range with non-contiguous cells.

ranges

Having said that, let us assume that for our addin we need to reference programmatically the range of all lines of the form, with usual excel notations, Ax:Cx where x describes a set of row indices. 

Then we need to use the method Application.Union of Micorsoft.Office.Interop.Excel and finally produce few lines of code that  looks like that.

In the chart above we have monitored the time execution of the BuildUnionAll method for different values of the parameter count.

linear

Remark: in the case of BuildUnionAll there is no need to use a loop and the Union method, we could have just ask for the range “A1:Ccount”. Note also that for small unions you may also use a syntax with semicolon to separate contiguous cells blocks e.g. worsheet.Range[“A1:C3;A8:C12”]. However, this does not work for large ranges made of multiple non-adjacent cells blocks.

So far so good, we see an almost linear curve, which is natural regarding to what we were expecting.

Now, change a little bit our expectation to something quite different and more realistic where we would truly need the Application.Union method. Then let us say that we would like to have the union Ax:Cx mentioned above but for x odd index only. We want the IEnumerable<int> to have the same size than before, so let us use the method in the code below.

Similarly, we monitor the performance curve.

quadratic

Argghhh!!! we get an awful quadratic cost which makes our approach completely broken for large unions. This quadratic cost was unexpected. Actually I did not found documentation on this (the msdn doc on Excel.Interop is really minimalist). However, it seems that the rule is that the Union method has a linear cost depending on the number of Areas in the input ranges. The Areas member of a Range is the collection containing the blocks of contiguous cells of the range.

This experimental rule, leads us to review our algorithm in a different way. If the cost of an Union is important (linear) when there are many areas in a range. Then we will try to minimize this situation: we will let our algorithm perform the union preferably on ranges having fewer areas. Once again, the basic techniques from school bring a good solution and we will design a very simple recursive algorithm based on the divide and conquer approach, inspired for the merge sort algorithm.

To the end, we recover an almost linear curve. The asymptotic complexity here (if the rows to pick are not adjacent to each other) equals the merge sort one which is O(n.ln(n)).

divideAndConquer