Key Mapper Developer Blog

Strongly typed Windows Forms databinding

keymappersupport@gmail.com

The default way to create Windows Forms DataBindings is like this:

this.txtClientName.DataBindings.Add("Text", client, "Name");

I wanted to be able to specify the properties I want to use with expressions to give me compile-time validation and refactoring - like this:

this.txtClientName.DataBindings.Add<Client>(CurrentClient, tb => tb.Text, c => c.Name);
this.cboGroup.DataBindings.Add<Client, ComboBox>(CurrentClient, cbo => cbo.SelectedItem, c => c.Group);

These extension methods for data binding allow me to do just that:

public static class DataBindingsExtensionMethods
{
    public static Binding Add<T>
        (this ControlBindingsCollection dataBindings,
         object dataSource,
         Expression<Func<Control, object>> controlExpression,
         Expression<Func<T, object>> objectExpression)
    {
        return Add(dataBindings, dataSource, controlExpression, objectExpression, false);
    }

    public static Binding Add<T>
        (this ControlBindingsCollection dataBindings,
         object dataSource,
         Expression<Func<Control, object>> controlExpression,
         Expression<Func<T, object>> objectExpression,
         bool formattingEnabled)
    {
        string controlPropertyName = ProcessExpression(controlExpression.Body);
        string bindingTargetName = ProcessExpression(objectExpression.Body);

        return dataBindings.Add(controlPropertyName, dataSource, bindingTargetName, formattingEnabled);
    }

    public static Binding Add<T, K>
        (this ControlBindingsCollection dataBindings,
         object dataSource,
         Expression<Func<K, object>> controlExpression,
         Expression<Func<T, object>> objectExpression)
    {
        return Add(dataBindings, dataSource, controlExpression, objectExpression, false);
    }

    public static Binding Add<T, K>
        (this ControlBindingsCollection dataBindings,
         object dataSource,
         Expression<Func<K, object>> controlExpression,
         Expression<Func<T, object>> objectExpression,
         bool formattingEnabled
        )
    {
        string controlPropertyName = ProcessExpression(controlExpression.Body);
        string bindingTargetName = ProcessExpression(objectExpression.Body);

        return dataBindings.Add(controlPropertyName, dataSource, bindingTargetName, formattingEnabled);
    }

    private static string ProcessExpression(Expression expression)
    {
        string propertyName;

        if (expression is MemberExpression)
        {
            propertyName = ((MemberExpression) (expression)).Member.Name;
        }
        else if (expression is UnaryExpression)
        {
            propertyName = ((MemberExpression) ((UnaryExpression) (expression)).Operand).Member.Name;
        }
        else
        {
            throw new InvalidOperationException(
                "Unknown expression type error in DataBindingsExtensionMethods: " + expression.GetType());
        }
        return propertyName;
    }
}

Koogra sample

keymappersupport@gmail.com

Koogra is an open-source MIT Licensed library for reading Excel files: it's latest release supports Excel 2007 format.

I use it for an app which imports Excel spreadsheets: using the Office interop libraries is awkward and brittle, and it has to work with an existing install of Office which you can't control versioning or upgrading. So, Koogra. I don't need to write to the spreadsheets, just read them.

There are at least three distinct syntaxes in Koogra:

Excel 2007 syntax
Excel 2003 syntax (more accurately, 97-2003)
The interface syntax which links the two together

For example, finding a worksheet by name:

using Net.SourceForge.Koogra;
using Excel2003 = Net.SourceForge.Koogra.Excel; 
using Excel2007 = Net.SourceForge.Koogra.Excel2007; 

// The Excel 2003 syntax for opening a worksheet 
Excel2003.Workbook workbook = new Excel2003.Workbook(fileName); 
Excel2003.Worksheet worksheet = workbook.Sheets.GetByName(sheetName); 

// The Excel 2007 syntax 
Excel2007.Workbook workbook = new Excel2007.Workbook(fileName); 
Excel2007.Worksheet worksheet = workbook.GetWorksheetByName(sheetName);

// The interface syntax 
IWorkbook workbook; 
if (Path.GetExtension(fileName).ToLower() == ".xlsx") 
{ 
    workbook = new Excel2007.Workbook(fileName); 
} 
else 
{ 
    workbook = new Excel97.Workbook(fileName);
}

IWorksheet sheet = workbook.Worksheets.GetWorksheetByName(sheetName);

The only one I am interested in is the interface syntax, as I have to support both file types, so here is a basic code sample for reading some data from a given sheet in a given file.

namespace JustKeepSwimming.Net.Koogra.Example
{
    using System;
    using System.Collections.Generic;
    using System.IO;
    using Net.SourceForge.Koogra;
    using Excel97 = Net.SourceForge.Koogra.Excel;
    using Excel2007 = Net.SourceForge.Koogra.Excel2007;

    public class DataImporter
    {
        public List ImportData(IImportData importData, string fileName)
        {
            // The Koogra Excel library uses 0 based counting
            // unlike some others
            List results = new List();

            IWorkbook workbook;

            if (Path.GetExtension(fileName).ToLower() == ".xlsx")
            {
                workbook = new Excel2007.Workbook(fileName);
            }
            else
            {
                workbook = new Excel97.Workbook(fileName);
            }

            IWorksheets sheets = workbook.Worksheets;
            IWorksheet sheet = null;

            if (string.IsNullOrEmpty(importData.SheetName) == false)
            {
                // GetWorksheetByName returns null if name not found..
                sheet = sheets.GetWorksheetByName(importData.SheetName);
            }

            // .. use the first sheet in the workbook.
            if (sheet == null)
            {
                sheet = sheets.GetWorksheetByIndex(0);
            }

            uint lastRow = sheet.LastRow;
            if (importData.IsLastRowTotal)
            {
                lastRow--;
            }

            for (ushort i = (ushort)(importData.StartRow - 1); i <= lastRow; i++)
            {
                IRow row = sheet.Rows.GetRow(i);
                string name = GetCellValue(row, (uint)importData.NameColumn);
                string amountString = GetCellValue(row, (uint)importData.AmountColumn);

                // Parse as double so if Excel returns 7.0000000E-2
                // for 0.07 it's still recognized.
                double amountDouble;
                if (double.TryParse(amountString, out amountDouble))
                {
                    ImportItem item = new ImportItem(name, amountDouble);
                    results.Add(item);
                }
            }
            return results;
        }

        private string GetCellValue(IRow cells, uint column)
        {
            if (cells != null && cells.GetCell(column) != null)
            {
                object value = cells.GetCell(column).Value;
                if (value != null)
                {
                    return value.ToString().Trim();
                }
            }
            return string.Empty;
        }

        private string GetFormattedValue(IRow cells, uint column)
        {
            if (cells != null && cells.GetCell(column) != null)
            {
                return cells.GetCell(column).GetFormattedValue();
            }
            return string.Empty;
        }
    }
}

User Mappings and Windows 7

keymappersupport@gmail.com

Windows 7 (the RC build, 7100) no longer implements per-user key mappings: as Microsoft never documented it anyway, removing it is their perogative. This is unfortunate, as it's back to the days of Windows 2000 where key mappings apply to all users without possibility of exception and require administrative privileges to be created or removed. I can only speculate whether they were dropped because they were unsupported, or because they just wouldn't work with Fast User Switching, or accidentally, or they were in some way incompatible with the Windows 7 code.

It's a pain for me as KeyMapper expects Windows 7 to behave like Vista (*) and Windows Server 2008 and to implement scancode mappings in HKEY_CURRENT_USER\Keyboard Layout and so the program sets the mappings, we log off and on again, and Windows 7 then ignores the scancode mappings, and the key remapping fails and I get a very polite support email with 'broken' or 'doesn't work' in the subject line.

It's still possible to set mappings, though, in Key Mapper but you have to tell the program to 'show / boot mappings' from the Mappings menu and then authorise the Registry change and then reboot your computer.

So it looks like some new behaviour for KeyMapper under Windows 7 will be required. I might maybe just try and get a recent build of Windows 7 just to see if just maybe the RTM code will implement per-user key mappings before I even think about what to do (and how to deal with UAC).

(*) Because it's 'later' than Vista: Windows 7 was only a gleam when Key Mapper 1.0 was released.

UK Bank Holidays and Working Days in C#

keymappersupport@gmail.com

I needed to calculate working days for a project: easy enough to discount Saturdays and Sundays, but UK Bank Holidays a bit trickier. I looked for existing code but by the time I got to page three of a Google search I realised I was going to have to write my own (ironic, really, as this will probably end out on page 64) so I did it on my own time so I can post it here in the hope of perhaps saving someone else the trouble.

Apart from Easter, they're all straightforward enough - first and last Monday in May, last Monday in August, checking what day of the week Christmas Day, Boxing Day and New Year's Day fall on and what the substitute days will be: I got the code to do Easter Sunday from here and from that getting the Good Friday and Easter Monday holidays are trivial.

Before the code listing, a couple of comments:

There is no error handling - i.e. it will break for a year value less than 1 or greater than 9999
If one-off bank holidays are declared, the program won't take account of them. If you absolutely must count every bank holiday including one-offs, you're better off storing the values in a database, allowing extras to be added, and querying the database for the holidays (you could use this program to populate the data)
I've tested them against the new few years of published Bank Holidays, which is good enough for me right now: this project has to be delivered, and as soon as possible. They may not be perfect for all scenarios. The code may nor be correct.

This is the bank holiday code:

namespace JustKeepSwimming.Net
{
    using System;
    using System.Collections.Generic;

    public static class BankHolidays
    {
        public static List<DateTime> GetBankHolidays(int year)
        {
                return GetBankHolidaysForYear(year);
        }
         
        private static List<DateTime> GetBankHolidaysForYear(int year)
        {
            List<DateTime> holidays = new List<DateTime>
                {
                    NewYearsDay(year),
                    GoodFriday(year),
                    EasterMonday(year),
                    MayDayBankHoliday(year),
                    SpringBankHoliday(year),
                    SummerBankHoliday(year),
                    ChristmasDay(year),
                    BoxingDay(year)
                };

            return holidays;
        }

        private static DateTime ChristmasDay(int year)
        {
            DateTime start = new DateTime(year, 12, 25);

            if (start.DayOfWeek == DayOfWeek.Saturday)
            {
                return start.AddDays(2);
            }

            if (start.DayOfWeek == DayOfWeek.Sunday)
            {
                return start.AddDays(1);
            }

            return start;
        }

        private static DateTime BoxingDay(int year)
        {
            DateTime start = new DateTime(year, 12, 26);

            if (start.DayOfWeek == DayOfWeek.Saturday)
            {
                return start.AddDays(2);
            }

            if (start.DayOfWeek == DayOfWeek.Sunday)
            {
                return start.AddDays(2); // The Monday will be the substitute day for Christmas Day
            }

            if (start.DayOfWeek == DayOfWeek.Monday)
            {
                return start.AddDays(1); // The Monday will still be the substitute day for Christmas Day
            }

            return start;
        }

        private static DateTime NewYearsDay(int year)
        {
            DateTime start = new DateTime(year, 1, 1);

            if (start.DayOfWeek == DayOfWeek.Saturday)
            {
                return start.AddDays(2);
            }

            if (start.DayOfWeek == DayOfWeek.Sunday)
            {
                return start.AddDays(1);
            }

            return start;
        }

        private static DateTime GoodFriday(int year)
        {
            return EasterSunday(year).AddDays(-2);
        }

        private static DateTime EasterMonday(int year)
        {
            return EasterSunday(year).AddDays(1);
        }

        private static DateTime SpringBankHoliday(int year)
        {
            // Last Monday in May
            return LastDayOfTheWeekInMonth(year, DayOfWeek.Monday, 5);
        }
        
        private static DateTime SummerBankHoliday(int year)
        {
            // last Monday in August
            return LastDayOfTheWeekInMonth(year, DayOfWeek.Monday, 8);
        }

        private static DateTime LastDayOfTheWeekInMonth(int year, DayOfWeek day, int month)
        {
            DateTime start = FirstDayOfTheWeekInMonth(year, day, month);
            DateTime lastDayInMonth = new DateTime(year, month, DateTime.DaysInMonth(year, month));

            while (start.AddDays(6) < lastDayInMonth)
            {
                start = start.AddDays(7);
            }

            return start;
        }

        private static DateTime FirstDayOfTheWeekInMonth(int year, DayOfWeek day, int month)
        {
            DateTime firstOfMonth = new DateTime(year, month, 1);
            DateTime firstDay = firstOfMonth.AddDays((int) day - (int) firstOfMonth.DayOfWeek);

            if (firstDay < firstOfMonth)
            {
                firstDay = firstDay.AddDays(7);
            }

            return firstDay;
        }
        

        private static DateTime MayDayBankHoliday(int year)
        {
            // First Monday in May
            return FirstDayOfTheWeekInMonth(year, DayOfWeek.Monday, 5);
        }

        private static DateTime EasterSunday(int year)
        {
            // Tidied up a little from code at 
            // http://bloggingabout.net/blogs/jschreuder/archive/2005/06/24/7019.aspx

            int g = year % 19;
            int c = year / 100;
            int h = (c - c / 4 - (8 * c + 13) / 25 + 19 * g + 15) % 30;
            int i = h - h / 28 * (1 - h / 28 * (29 / (h + 1)) * ((21 - g) / 11));
            int day = i - ((year + year / 4 + i + 2 - c + c / 4) % 7) + 28;

            int month = 3;
            if (day > 31)
            {
                month++;
                day -= 31;
            }

            return new DateTime(year, month, day);
        }
    }
}

Given that, the working day calculation seems easy enough, although the implementation will vary depending on specific requirements: Do you start from the start date, or end at the end? Is your count inclusive or exclusive or neither? (i.e. does the starting day and/or ending day or neither count in the calculation?)

For this example implementation, the start date does not count, and it is inclusive of the end date.

As the time of the dates is immaterial - it doesn't affect the result - we discard it, then add a minute and a day to start, and two minutes to end.
This avoids issues with DateTimes not having a time, and calculates correctly for the parameters of this implementation.

public static int GetWorkingDays(DateTime start, DateTime end)
{
     start = start.Date.AddDays(1).AddMinutes(1);
     end = end.Date.AddMinutes(2);

     int workingDays = 0;
     int currentYear = start.Year;
     List<DateTime> holidays = BankHolidays.GetBankHolidays(currentYear);
     while (start < end)
        {
            if (start.DayOfWeek != DayOfWeek.Saturday
                 && start.DayOfWeek != DayOfWeek.Sunday
                 && holidays.Contains(start.Date) == false)
                  {
                      workingDays++;
                  }
               start = start.AddDays(1);
               if (start.Year != currentYear)
                {
                    currentYear = start.Year;
                    holidays = BankHolidays.GetBankHolidays(currentYear);
                }
            }

            return workingDays;
}

Toggling Insert on a keyboard with no Insert key

keymappersupport@gmail.com

If you find yourself using a keyboard without an Insert key (eg recent Mac keyboards) but Overtype mode has enabled itself somehow, then it's useful to remember that the 0 key on the Numberpad is a surrogate Insert key, but only when Num Lock is turned off. If you have NumLock set On and the NumLock key disabled, though, this is a small problem: KeyMapper lets you toggle the value of Num Lock from the Toggle Lock Key menu, so you can set it off temporarily, switch back to Insert mode using Numberpad/0, then toggle Num Lock on again.

Removing a watermark from a Word document in C#

keymappersupport@gmail.com

If you have a Word Document based on a template with a watermark on it and you want to remove the watermark in the new document, calling the WordBasic RemoveWatermark method seems the easiest way to go: except it's a COM object so you have to use a bit of late-binding trickery..

string templateLocation = "c:\template-dir\template.dot";

object wordFalse = false;

object wordTemplateLocation = (object)templateLocation;
object wordNewDocument = (object)Word.WdNewDocumentType.wdNewBlankDocument;

wordApp = new Word.ApplicationClass();

doc = wordApp.Documents.Add(ref wordTemplateLocation, ref wordFalse, ref wordNewDocument, ref wordFalse);

doc.ActiveWindow.Activate(); // Otherwise Word complains a document is not active

object oWordBasic = wordApp.WordBasic;

oWordBasic.GetType().InvokeMember("RemoveWatermark", BindingFlags.InvokeMethod,
Type.DefaultBinder, oWordBasic, new object[]{});

Remapping Pause and Num Lock

keymappersupport@gmail.com

The Num Lock key (along with the Insert key) is one of the keys most likely to be disabled by any scancode mapping program: it is hardly ever used and the effects of accidental pressing it are confusing and annoying, to users and support teams. There are 133 637 results when searching the Microsoft support site for Num Lock. Keyboard design is staunchly conservative, though, and the 101/102 keyboard keys have remained essentially unchanged since introduced by IBM in 1984.

One of the reasons for the conservatism is that keyboards use numeric codes - scancodes - to identify which physical key has been pressed. These scancodes include one bit to identify the key itself, and an extra bit to show if the key is extended. This derives from the extension of the original 83/84 PC/AT keyboard to the 101 key keyboard: in some cases where the functionality was the same, rather than assign a whole scancode to the extra keys, an extra bit was added. For example the Enter and Return keys both have a scancode of 28 (0x1C), while the Enter key (the one on the numeric keypad) has the extended bit set; similarly the Left arrow key and the number 4 on the numeric keypad (which is Left with Num Lock set off) both have a scancode of 75 (0x4B), but the arrow key has the extended bit set. This was presumably for backward compatibility with systems which didn't expect the extra extended bit.

Keyboard drivers can change or interpret these codes themselves - and often do: although keyboard power and standby keys have been assigned specific scancodes, the actual operations are controlled by the keyboard driver; this means that a) the power keys usually can't be disabled using scancode mapping and b) it isn't possible to assign an existing key to these functions. Scancode mapping programs like KeyMapper usually allow the selection of the key to be disabled by reading the keypress: Num Lock, unlike all other keys, reports itself differently on different keyboards. Most keyboards use the usual code - 69 (0x45) - to identify the key with the extended bit set off, but some set the extended bit on. This can cause confusion, as systems expect the code of 69 with extended on to represent the Euro symbol, on keyboards which have that. Using that key code in a scancode mapping also fails to correctly map Num Lock: scancode of 69 without the extended bit set must be used in mappings no matter what the keyboard reports as the scancode.

The Num Lock key is also involved in the most complicated remapping scenario, the Pause key. At first I couldn't figure out how to remap the Pause key at all, and thought that it couldn't be done. Then I stumbled across a blog post which indicated it was possible and provided me with enough information to work out how to implement it.

When the Pause key is pressed, internally this generates multiple keystrokes, specifically Ctrl and Num Lock. This is explained by another blog post, coincidentally posted within two days of the other one.

in the original PC/XT keyboard layout, there was no Break key. The key sequence for Break was Ctrl+ScrollLock. (And for completeness, the key sequence for Pause was Ctrl+NumLock.) Even though the enhanced keyboard moved the Pause and Break functions to their own key, pressing the Pause key internally generated scan codes that simulated a press of Ctrl+NumLock. In other words, when you pressed Pause, the keyboard hardware actually tells the computer, "The user pressed the Ctrl key and then pressed the NumLock key."

That's not quite the whole story, though - so systems can distinguish this from a regular Ctrl-NumLock, a different extended value is used - instead of 224 (0xe0) the keyboard will report 225 (0xE1). This is as far as I know the only time this extra extended value is used.

This complicated the UI for KeyMapper: should a user want to disable or remap the Pause key, then Num Lock will still fire each time it's pressed: to remap Pause to a single keystroke, Num Lock must be disabled as otherwise Num Lock will be toggled each time Pause is pressed.

However, it also introduced an interesting new possibility: if Num Lock is remapped to another key then that key will also be 'pressed' when the remapped Pause is pressed: this means the Pause key can be remapped to a keyboard command which requires two key strokes, for example Windows-L (the shortcut to Lock Computer), or Right-Alt and 4 for the Euro symbol. These require the Pause key to be remapped to the first key in the sequence and Num Lock to be remapped to the second.

User Key Mappings

keymappersupport@gmail.com

One thing that distinguishes Key Mapper from other scancode mapping programs is that it lets you map or disable keys on a per-user basis: when Microsoft originally implemented scancode mappings in Windows 2000, they stated in the "disadvantages" section:

The mappings stored in the registry work at system level and apply to all users. These mappings cannot be set to work differently depending on the current user.

This is because the mappings are stored in HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Keyboard Layout which needs Administrative access to change and is only loaded at boot time.

In Windows XP, though, per-user mappings were quietly introduced, with no fanfare or documentation: scancode mappings set in the HKEY_CURRENT_USER\Keyboard Layout key are recognised, and apply to an individual user profile. This means that mappings can be added or removed by logging off and logging back on again - still inconvenient, but less so than a full reboot: it also means that mappings can be set up users without Administrative rights. (Mappings set in HKEY_LOCAL_MACHINE are overridden by those in HKEY_CURRENT_USER).

It's possible that Microsoft kept this quiet because user mappings are incompatible with Fast User Switching: when you switch to an account that's already logged on, the mappings are not reloaded. It's also possible that because they kept it quiet, the Fast User Switching development team didn't realise that user mappings should be reloaded when switching users. Boot mappings persist through Fast User Switching.

While this is a possible disadvantage to using user mappings, most people probably don't use more than one account on their computer anyway, and in computers attached to a domain (i.e. corporate PCs) which may often be used by different people Fast User Switching isn't available anyway.

There are some other advantages to user mappings:

They don't require Administrative rights to be set or removed.
Different users can have different mappings - one can have Caps Lock disabled but Num Lock enabled, another can have them the other way round
Keys can be mapped on shared computers without affecting all users

There is yet another place scancode mappings can be set - in the HKEY_USERS\.DEFAULT\Keyboard Layout key. These apply at the login prompt, but are then removed when logged in.