How to hide an excel column?

Question

I need to hide an excel column entirely. I used the below code but didn't work:

public void Hide (params string[] columns)
{
    foreach(var column in columns)
    {
        Range range = (Range) oSheet.Columns[column, Type.Missing];
        range.EntireColumn.Hidden = true;
    }
}

What am I missing?

Solution 1

The above snippet is working .. sorry guys! The issue was that, my fellow colleague used to auto-fit all columns, right before saving the file, which will override my settings above. Yes, a function called CloseFile() that does two jobs, formats and then saves .. many responsibilities, eh?

Solution 2

It turns out that hiding a span of rows and columns you specify couldn't be a heck of a lot easier. Here's how you do it, in two easy steps:

1) Name your worksheet:

private Worksheet _xlSheet;

2) Now, name a range, including the first row and column to hide and then the last row and column to hide, like so:

var hiddenRange = yourWorksheet.Range[_xlSheet.Cells[42, 1], _xlSheet.Cells[999, 13]];
hiddenRange.EntireRow.Hidden = true;

This assumes the first row you want to hide is 42, etc. Obviously you will want to change these hardcoded values.

As an example, here's some actual code, using constants and variables instead of hardcoded vals, which responds to a boolean whose value indicates whether the range should be hidden or not:

private bool _hide;
private int _curTopRow;
private static readonly int ITEMDESC_COL = 1;
private static readonly int TOTALS_COL = 16;
. . .
if (_hide)
{
    var hiddenRange = _xlSheet.Range[_xlSheet.Cells[_curTopRow, ITEMDESC_COL], _xlSheet.Cells[_curTopRow+3, TOTALS_COL]];
    hiddenRange.EntireRow.Hidden = true;
}

Note that you need to reference the Microsoft.Office.Interop.Excel assembly.

Comments

[B. Clay Shannon-B. Crow Raven]

Why would autofit and hidden collide? I need to do both - hide certain rows (not columns), but also use autofit.