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

It would be awesome if you could provide a [mcve] (inside the question, people generally won't click external links). — mjwills, Jul 19 '18 at 21:55
my screen went purple! na not really. It doesn't hide the column. — Micah Armantrout, Jul 19 '18 at 21:59

score 10 · Accepted Answer · edited Mar 24 '22 at 14:05

10

I was calling

worksheet.Cells.AutoFitColumns();

after

worksheet.Column(1).Hidden = true;

AutoFitColumns was removing the hide effect.

edited Mar 24 '22 at 14:05

Michael Fayad

1,216
1
17
38

answered Jul 19 '18 at 22:12

Micah Armantrout

6,781
4
40
66

William · Answer 2 · 2019-10-24T21:28:25.830

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.

Thanks for this. As useful as I find EPPlus, the side-effect driven nature of so many things makes it nearly impossible to parse out why things don't work, and why things must be executed in a specific pattern for some features to apply. — Captain Prinny, Jan 03 '20 at 17:47

score 1 · Answer 3 · answered Aug 07 '19 at 15:26

Apparently according to the documentation, you can use AutoFitColumns(Double MinimumWidth, Double MaximumWidth):

Set the column width from the content of the range. Note: Cells containing formulas are ignored if no calculation is made. Wrapped and merged cells are also ignored. Hidden columns are left hidden.

    worksheet.Column(1).Hidden = true;
    worksheet.Cells.AutoFitColumns(8.43,100); // 8.43 = default width of cells

Hide column in EPPlus not working

3 Answers3

Linked