none
Apply column filter value through the AutoFilter option in the Excel is not working - C# RRS feed

  • Question

  • Description : 
    In C# application, the AutoFilter drop-down arrows are visible but no rows are currently filtered with value (all rows are visible).

    unable to apply the value in the Filter. For example if i try to apply value "aaa" in the third column - in the result data filter is not applying correctly. it displays the all data without filtering.

    I set SheetProperties.FilterMode as true still filter is not applied


    Unable to upload the images as my account is not yet verified and please verify my account to send the images.

    please find the code snippet below.   

    static void ApplyFilterValue(string fileName)

            {
              using (SpreadsheetDocument spreadsheetDocument =
               SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
                {              
                    SheetData sheetData = new SheetData();
                    Row titleRow = new Row { RowIndex = (UInt32)1 };

                    titleRow.AppendChild(CreateTextCell("A", "Entity Name", 1));
                    titleRow.AppendChild(CreateTextCell("B", "Event Name", 1));
                    titleRow.AppendChild(CreateTextCell("C", "Attribute Name", 1));
                    titleRow.AppendChild(CreateTextCell("D", "Object Name", 1));
                    titleRow.AppendChild(CreateTextCell("E", "Type Name", 1));
                    titleRow.AppendChild(CreateTextCell("F", "Tab Name", 1));
                    titleRow.AppendChild(CreateTextCell("G", "Section Name", 1));

                    // Append Row to SheetData
                    sheetData.AppendChild(titleRow);
                    string[,] ConversionReportItems = new string[,] { {"aaa","bbb","ccc","ddd","eee","fff","ggg" },
                                                                    { "aaa","bbb","aaa","ddd","eee","fff","ggg" },
                                                                    { "aaa","bbb","ccc","ddd","eee","fff","ggg" },
                                                                    { "BBB","bbb","aaa","ddd","eee","fff","ggg" },
                                                                    { "aaa","bbb","ccc","ddd","eee","fff","ggg" },
                                                                    { "aaa","bbb","aaa","ddd","eee","fff","ggg" },
                                                                    { "aaa","bbb","ccc","ddd","eee","fff","ggg" } };
                    for (int i = 0; i < 7; i++)
                    {
                        Row row = new Row { RowIndex = (UInt32)i + 2 };

                        row.AppendChild(CreateTextCell("A", ConversionReportItems[i, 0], i + 2));
                        row.AppendChild(CreateTextCell("B", ConversionReportItems[i, 1], i + 2));
                        row.AppendChild(CreateTextCell("C", ConversionReportItems[i, 2], i + 2));
                        row.AppendChild(CreateTextCell("D", ConversionReportItems[i, 3], i + 2));
                        row.AppendChild(CreateTextCell("E", ConversionReportItems[i, 4], i + 2));
                        row.AppendChild(CreateTextCell("F", ConversionReportItems[i, 5], i + 2));
                        row.AppendChild(CreateTextCell("G", ConversionReportItems[i, 6], i + 2));
                        row.Hidden = false;
                        // Append Row to SheetData
                        sheetData.AppendChild(row);
                    }

                    // Add a WorkbookPart to the document.
                    WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
                    workbookpart.Workbook = new Workbook();

                    // Set the AutoFilter property to a range that is the size of the data
                    // within the worksheet
                    AutoFilter autoFilter1 = new AutoFilter() { Reference = "A1:G9"};

                    FilterColumn filterColumn1 = new FilterColumn() {ColumnId = 2 };

                    Filters filters1 = new Filters() { };
                    Filter filter1 = new Filter() { Val = "aaa" };

                    filters1.Append(filter1);                           

                    filterColumn1.Append(filters1);

                  autoFilter1.Append(filterColumn1);

                    Worksheet sheet1 = new Worksheet();

                    sheet1.Append(sheetData);
                    sheet1.Append(autoFilter1);

                    // Add a WorksheetPart to the WorkbookPart.
                    WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                    worksheetPart.Worksheet = sheet1;

                    worksheetPart.Worksheet.Save();

                    // Add Sheets to the Workbook.
                    Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

                    // Append a new worksheet and associate it with the workbook.
                    Sheet sheet = new Sheet()
                    {
                        Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
                        SheetId = 1,
                        Name = "Conversion Report"
                    };

                    sheets.Append(sheet);

                    // Close the document.
                    spreadsheetDocument.Close();
                }
            }

                                                                                                                                                                    
    Monday, May 6, 2019 7:14 AM

All replies

  • Hi,

    not sure whats wrong, but you could use Open XML Power Tools and compare fitered and non filtered files created with excel.

    Thursday, May 9, 2019 12:32 PM
  • I take a look.  The filter is not dynamic. This means you must hide the rows programmatically. Excel applies the filter if you save not open the file:

    Hide row:  Row row4 = new Row(){ RowIndex = (UInt32Value)4U, Spans = new ListValue<StringValue>() { InnerText = "1:2" }, Hidden = true, DyDescent = 0.25D };

    Thursday, May 9, 2019 12:42 PM