Hide column in EPPlus not working

Question

I want to hide a column in excel using EPPlus nuget package and its not working (It didn't hide the column)

this is what I have found that is supposed to do the trick. Maybe there is a bug ?

worksheet.Column(1).Hidden = true;

I am using version 4.5.2.1

Project URL https://github.com/JanKallman/EPPlus

Answer 1

I was calling

worksheet.Cells.AutoFitColumns();

after

worksheet.Column(1).Hidden = true;

AutoFitColumns was removing the hide effect.

Answer 2

I'm pretty sure it's a bug.

As of this writing (2019-10-24), there's code in AutoFitColumns that tries to avoid doing auto-fit for hidden columns, but it's executed after a helper method (SetMinWidth) is invoked that goes through all the columns setting a minimum width, and the Width setter includes the side effect of setting _hidden to false if the width is non-zero, which it is when using the zero-parameter overload of AutoFitColumns.

If you use the 1- or 2-parameter overloads of AutoFitColumns to pass in a minimum width of zero, the columns will remain hidden, but your empty columns will be zero-width, so it's not really a workaround.

Here's an extension method that I'm using as a workaround, for now:

static void AutoFitColumnsAndRehide(this ExcelRangeBase range)
{
    range.Reset();

    var hiddenColumns = range
        .Select(cell => cell.Start.Column)
        .Distinct()
        .Select(range.Worksheet.Column)
        .Where(column => column.Hidden)
        .ToList();

    range.AutoFitColumns();

    foreach (var column in hiddenColumns)
    {
        column.Hidden = true;
    }
}

This can obviously be adjusted for the 1- and 2-parameter overloads, as well.