Bus error: Jake Billo's weblog

Adventures in C#: Excel automation

2008-06-16 04:01 PM | 3 comments

So I've been working a fair bit with C# and Excel automation, and I've picked up a few things from the whole experience so far - namely that the thing is really poorly put together and requires a whole lot of work to make things work nicely.

The emphasis there is on nicely. You can hack together something real quick in about 5 minutes that probably almost does what you want, and will be good enough if you just need the job done. However, the Excel COM uses about 3 different standards and each time you get to guess which one you'll need to use.

Over the next few days [or weeks] I'm going to outline a few of the problems I encountered and how I went about fixing them:

Problem: The syntax for inserting a column and changing a column's width is completely different

This might not seem to big of an issue until you realize that:

column.Insert(); exists, but:
column.Width = 10; or column.setWidth(10); or anything like that, does not exist.

The actual solutions to do these tasks are as follows. Note, they reference the variable oSheet is the Excel.Worksheet you're working with. These samples assume a basic knowledge of C# and using the Excel COM.

Inserting a columm:

Excel.Range rng = (Excel.Range)oSheet.Cells[rowNumber, colNumber];
Excel.Range column = rng.EntireColumn;
column.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, false);

This will insert a column to the left of row rowNumber.

Changing a column's width:

((Excel.Range)oSheet.Columns["A", Type.Missing]).ColumnWidth = 10;

This will change column A's width.

Notice that one method uses numeric identifiers for rows, while the other uses a string. Also, interestingly enough, its a bit of a pain to actual get a hold of a column using either method.