Apply autofilter by column value : EPPLUS C#

See more:

C#

MS-Excel

I am using EPPLUS DLL ver 5.4.0 and need to filter table by a column value (column D). Here is my C# code for creating excel file sheet:

DataTable dt = MappingDataset.loadQuery(dbQuery);
ExcelWorksheet sheet = excelPackage.Workbook.Worksheets.Add("SHEET 1");
sheet.Cells["A1"].LoadFromDataTable(dt, true);
string ct1 = ((dt.Rows.Count + 1) + "");
sheet.Cells["A1:F" ct1].AutoFilter = true;
sheet.Cells["A1:F" ct1].AutoFitColumns();

Column D will be populated with values ('YES','NO). How can I apply auto filter rows having D column value as 'NO' before saving the excel file.

I have tried hiding the rows with D column value as 'YES', but that is not what I am trying to achieve. I don't want to hide the rows, I need to filter the rows with column D value.

//sheet.Cells.Where(cell => cell.Address.StartsWith("D") && cell.Value.ToString().Equals("YES")).Select(cell => cell.Start.Row).ToList().ForEach(r => sheet.Row(r).Hidden = true);

Thanks for support in advance.

What I have tried:

<pre>DataTable dt = MappingDataset.loadQuery(dbQuery);
ExcelWorksheet sheet = excelPackage.Workbook.Worksheets.Add("SHEET 1");
sheet.Cells["A1"].LoadFromDataTable(dt, true);
string ct1 = ((dt.Rows.Count + 1) + "");
sheet.Cells["A1:F" ct1].AutoFilter = true;
sheet.Cells["A1:F" ct1].AutoFitColumns();

//sheet.Cells.Where(cell => cell.Address.StartsWith("D") && cell.Value.ToString().Equals("YES")).Select(cell => cell.Start.Row).ToList().ForEach(r => sheet.Row(r).Hidden = true);