EPPlus autofilter only working on last cell

user_1489378 0
Viewed 18616

I would like each cell in the header to contain an autofilter. Below is the code I'm trying to use however the autofilter only gets set on the last cell specified.

For example, if I comment out the autofilter command for K1, the spreadsheet will be created with C1 being the only cell with an autofilter. 

        //Headers
        ws.Cells["A1"].Value = "ChannelCode";
        ws.Cells["A1"].AutoFilter = true;
        ws.Cells["B1"].Value = "DrmTerrDesc";
        ws.Cells["B1"].AutoFilter = true;
        ws.Cells["C1"].Value = "IndDistrnId";
        ws.Cells["C1"].AutoFilter = true;
        ws.Cells["D1"].Value = "StateCode";
        ws.Cells["D1"].AutoFilter = true;
        ws.Cells["E1"].Value = "ZipCode";
        ws.Cells["E1"].AutoFilter = true;
        ws.Cells["F1"].Value = "EndDate";
        ws.Cells["F1"].AutoFilter = true;
        ws.Cells["G1"].Value = "EffectiveDate";
        ws.Cells["G1"].AutoFilter = true;
        ws.Cells["H1"].Value = "LastUpdateId";
        ws.Cells["H1"].AutoFilter = true;
        ws.Cells["I1"].Value = "ErrorCodes";
        ws.Cells["I1"].AutoFilter = true;
        ws.Cells["J1"].Value = "Status";
        ws.Cells["J1"].AutoFilter = true;
        ws.Cells["K1"].Value = "Id";
        ws.Cells["K1"].AutoFilter = true;
epplus excel .net c#
4 Answers

If you are trying to do something like this:

var colNames = new List<string>() { "MyCol1", "MyCol2", "MyCol3" };
var row = 3;
var startCol = 5;
var lastCol = startCol + colNames.Count - 1;
 var i = startCol; // col index

foreach (var colName in colNames)
{
   sheetObj.Cells[row, i].Value = colName;
   sheetObj.Cells[row, i++].AutoFilter= true;
}

... it wont work because of what to me seems to be a buggy behaviour in the implementation of .AutoFilter. Even this wont work either:

foreach (var colName in colNames)
   sheetObj.Cells[row, i++].Value = colName;

using ExcelRange range = sheetObj.Cells[row, startCol, row, lastCol];
range.AutoFilter = true;

So what I found it worked to me was:

foreach (var colName in colNames)
    sheetObj.Cells[row, i++].Value = colName;

sheetObj.Cells[row, startCol, row, lastCol].AutoFilter = true;

And in case you want to make some other settings to the cells, you could do this:

var colNames = new List<string>() { "MyCol1", "MyCol2", "MyCol3" };
var row = 3;
var startCol = 5;
var lastCol = startCol + colNames.Count - 1;
var i = startCol; // col index

foreach (var colName in colNames)
    sheetObj.Cells[row, i++].Value = colName;

using ExcelRange range = sheetObj.Cells[row, startCol, row, lastCol];
range.Style.Font.Size = 16;
range.Style.Font.Color.SetColor(Color.Red);
range.AutoFitColumns();
range.Style.Font.Bold = true;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.LightBlue);

sheetObj.Cells[row, startCol, row, lastCol].AutoFilter = true;
user_75863010
Related