All posts by sean

The 10 links I used in everyday VSTO development

There’s a wealth of information out there on VSTO and Office add-in development, but it can be hard to sift the wheat from the chaff. Here are the links I kept close at hand the last time I did Office development.

Docs for the Excel and Word interop namespaces:
http://msdn.microsoft.com/en-us/library/microsoft.office.interop.word.aspx
http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.aspx

3-part series on making stuff for the ribbon:
http://msdn.microsoft.com/en-us/aa338202.aspx
http://msdn.microsoft.com/en-us/aa338199.aspx
http://msdn.microsoft.com/en-us/aa722523.aspx

Argument lists for built-in dialog box objects:
http://msdn.microsoft.com/en-us/library/bb208812.aspx
http://msdn.microsoft.com/en-us/library/bb211087.aspx

http://office.microsoft.com/en-us/excel-help/CH006252819.aspx

There are a surprising number of things that can be done by using the right Excel function instead of writing a bunch of .NET code, and it almost always makes your life easier.

WPF and Silverlight aren’t VSTO-specific, but I used them on a regular basis to make custom views.

Silverlight 3 control reference: http://msdn.microsoft.com/en-us/library/system.windows.controls(VS.95,loband).aspx

WPF control reference: http://msdn.microsoft.com/en-us/library/system.windows.controls.aspx

Better living through user scripts

The best feature of the web is the fact that once a page is served to your client, it’s yours.  There’s no such thing as a closed-source web page, only one that you haven’t looked at.  Better yet, any reasonably modern browser can inject your custom CSS and javascript into a web page after it’s been downloaded, to extend or enhance it any way you see fit.  As a celebration of the open web, here are three client-side additions to JIRA’s Greenhopper that I wrote to make my daily life better.

Don’t Resolve Tasks

Our Greenhopper workflow deals mostly with Stories and Tasks.  Both of those are stored as JIRA issues, so they can both be either Closed or Resolved.  The way we do things, it doesn’t make sense to Resolve a task, only ever Close it.  This short bit of CSS will remove the option to Resolve a task when it gets dragged into the Done column:

.aui-popup .gh-aui ul li
{
  display:none
}
.aui-popup .gh-aui ul :first-child
{
  display:inline-block
}

Highlight Tasks By User

Greenhopper sets different colors on different issue types.  This is nice, but I realized that what I really want is to be able to quickly distinguish tasks by who’s working on them, rather than what they are.  The following javascript will run through the Task Board and assign classes to each subtask based on who’s assigned to it (and run again whenever the page updates from dragging an issues):

function addClasses() {
    var subtasks = document.getElementsByClassName("gh-issue");

    for (var i = 0; i < subtasks.length; i++) {
        var issue_body = subtasks[i].children[0].children[1];
        var assignee = null;
        for (var j = 0; j < issue_body.children.length; j++) {
            if (issue_body.children[j].getAttribute('data-fieldid') == 'assignee') {
                assignee = issue_body.children[j].innerText.replace(' ', '').replace('\n', '').toLowerCase();
            }
        }

        if (assignee != null && subtasks[i].children[0].className.indexOf(assignee) < 0) {
            subtasks[i].children[0].className += " " + assignee;
        }
    }
}

document.addEventListener("DOMNodeInserted", addClasses);
addClasses();

Once that’s in place, I can add a little CSS to style different users.  For example, this turns all issues assigned to me green and bold:

div.gh-issue-inner.seankermes {
  background-color:#384 !important;
  font-weight:bold;
}

Colored Columns

Our Greenhopper task board has five columns at the moment, and there’s enough issues in it that when I scroll down past the column headers I can lose track of which issues are in which column based solely on their position.  This script sets the background color of each column section in the task board to a different color to make it easier:

function colorColumns() {
    var colors = ["#e0e0d7", "#cca36e", "#614a48", "#f5e1a4", "#99948b" ]
    var cols = document.getElementsByClassName("gh-step-col")
    for (var i = 0; i < cols.length; i++) {
        cols[i].style.backgroundColor=colors[i%5]
    }
}
document.addEventListener("DOMNodeInserted", colorColumns);
colorColumns();

All the code here can be installed in your very own browser either by default (Chrome handles .user.js files as extensions) or with browser add-ons like Stylish and Greasemonkey

Students Cannot Live on Bubble Sheets Alone

I originally drafted this as a comment to Claire’s excellent post, but it got long, so I posted it here instead. You should really go read that first.

While the need for education reform is obvious, I think there are two conflicting ideas when you talk about the OECD and PISA and Walter Percy and dogfish. I loved that essay by Percy, and it’s absolutely clear to me that a number of subjects are damaged, in some cases irreparably, by being approached as an object of study. Look at this forum thread: it’s eighteen pages of people slagging many books which are, often rightly, considered some of the best literature has to offer. You might not be able to fix The Fountainhead with a dogfish, but you could certainly do wonders for The Great Gatsby.

What dogfish can’t do is raise PISA scores.

I read over the guidelines and sample questions for the Science section of the PISA. While it’s better than most standardized tests I’ve had the misfortune of encountering during my education, it still runs headfirst into the standard failure modes of the genre. When every question must be asked in a single paragraph, and when the correct answers (of which there are never more than two) must be expressed in a single sentence, the chances for subtlety, poetry and joy must be omitted from the testing pamphlet, if they are even considered.[1] The answers to every question in the exam have already been determined before a single student sits down and unsheathes a #2 pencil. Continue reading Students Cannot Live on Bubble Sheets Alone

A loss of precision, but not a loss of magnitude

Those of us working with relatively high-level languages spend most of our time able to blissfully ignore the mucky little details that go into making a program actually run. C# allows us to not think about allocating and freeing memory, for example, or how our strings are terminated. It’s all too easy to extend this willful blindness to other details, like how numeric types actually work. Can this variable ever have a fractional part? Yes? Double! No? Int! Can it be really big? Long! Done, sorted.

Recently I was writing some functions to do unit conversions. Specifically, I was converting between points (1/72 of an inch) and EMUs (1/914400 of an inch). Part of the point of EMUs is that you can use them for high-precision values without needing floating point arithmetic, so I was storing them in a long. The application in question deals with fractional points, so those went into a double. Now, what’s the largest positive point that we can convert into an EMU without any overflow? If we do a little math we see that there are 12,700 EMUs per point, so the obvious answer should be long.MaxValue / 12700.0, which comes out to a bit over 7.2E14. Excellent. However, if I turn that around and convert that many points back into EMUs, I get -9.2E18. Oh dear. Continue reading A loss of precision, but not a loss of magnitude

T-SQL Tuesday 016: When is yesterday, exactly?

This is my contribution to the “recurring, revolving blog party” that is T-SQL Tuesday. Thanks to this month’s host, Jes Borland, for the topic!

On a consulting project some months ago, our client had a database full of daily financial data. For every day that the markets were open, we had a row with some data about the trades that had happened that day. If we ignore all the sticky details, it looked something like this:

date high low
3/8/2011 10 8
3/9/2011 11.5 9
3/10/2011 10.5 8.5

One of the reports we wanted to generate from this data was a day-on-day comparison, where each day’s high and low were compared with the previous day’s to see how it was changing. The goal was something that looked like:

date today’s high yesterday’s high change today’s low yesterday’s low change
3/9/2011 11.5 10 +1.5 9 8 +1
3/10/2011 10.5 11.5 -1 8.5 9 -.5

Continue reading T-SQL Tuesday 016: When is yesterday, exactly?

Debugging XAML and Prism

The biggest frustration in my Silverlight development is debugging errors in my XAML code.  Since our app uses Prism, there are often three different .xaml files involved in any one change to the app: App.xaml for global styles, Shell.xaml for the main layout and a .xaml file for whatever view I’m working on.  If something goes wrong, knowing what file it’s in is half the battle.

Unfortunately, the errors that Silverlight throws when you have malformed XAML can be pretty obtuse.  Through trial, error and a little intuition I’ve figured out that there are some patterns:

  • If nothing at all renders, the problem is likely in App.xaml.  That gets loaded first, and if something goes wrong then you get bupkus.  Check for syntax errors in App.xaml.
  • If the only thing that renders is the spinny loady thing (I actually don’t know if this is a Silverlight default or a StockTraderRI thing) then I think that usually means the problem is in Shell.xaml.  App.xaml has loaded at this point, but something is keeping the rest of it from going.  Often this can happen if you’re referencing something in App.xaml that’s syntactically valid but semantically nonsense.  Check especially for mis-spelled values inside Setter tags, or badly structured ContentTemplates. Continue reading Debugging XAML and Prism

Importing CSV Data into SQL Server

On a fairly regular basis at work I have to take .csv files with data from a customer’s database and import them into my SQL Server instance. This should be a simple operation, but the design of the native SQL Server Data Import tool makes it much, much more complicated than it needs to be. The biggest pain point by far is having to specify the data types of every single column in the data source by hand. Not only does it assume that everything is a string, forcing me to go through and figure out which types are a date, an int, a float or whatever else I have in the database, but by default changing a column’s type to a decimal uses decimal(18,0), truncating all my floating point values.

It always seemed a bit absurd that I should have to manually specify the types of all my data since I was importing into a database where the data types of the columns are explicitly specified. Once the import tool knew what table I was importing into, shouldn’t it be able to figure out the types for me?

So I wrote a Python script to import data for me. It uses the pymssql module to connect to the database, and the main insert function requires only three arguments: a connection to a database, the name of the table to import into, and an object with a function to return the names of the columns and then be able to iterate over the data. Conveniently, I also wrote a CSVFile class that wraps around a .csv file and provides just that functionality. Continue reading Importing CSV Data into SQL Server