Trouble setting up an excel filters RRS feed

  • Question

  • I'm trying to setup a row of filters in the open xml sdk but I'm having issues.  The code runs without any errors, however, when I open my generated spreadsheet, it gives me errors and no autofilters are present.  Here's my code for trying to generate a row of filters.  I specify the worksheet I wish to use.  Then the beginning and ending rows and columns.  I also call a method called "GetColumnName", which returns the alphabetic value of a numeric column (i.e. column 2 would equal "B"), so I can specify my range of cells to place filters on (i.e. "A3:U3"):

    public void Filter(string sheetName, int startRow, int startColumn, int endRow, int endColumn)
          //Get the SheetToDelete from workbook.xml
          Sheet theSheet = Workbook.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).FirstOrDefault();
          // Remove the sheet reference from the workbook.
          if (theSheet != null)
            WorksheetPart worksheetPart = (WorksheetPart)(Workbook.GetPartById(theSheet.Id));
            AutoFilter autoFilter1 = new AutoFilter() { Reference = GetColumnName(startColumn) + startRow + ":" + GetColumnName(endColumn) + endRow };
          // Save the workbook.


    Any idea what I'm doing wrong?


    Monday, August 15, 2011 9:34 PM


  • Hi Goalie35,

    After doing some research, I think I found out the cause of the problem. After appending the autoFilter, it is the last element below the worksheet, please see the xml information:

      <x:pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3" />

      <x:autoFilter ref="A1:B6" />

    So, we need to insert autoFilter element before pageMargins, here is the code snippet for you to reference:

    using System.Linq;
    using System.IO;
    using DocumentFormat.OpenXml.Packaging;
    using DocumentFormat.OpenXml.Spreadsheet;
    namespace ExcelOpenXmlAutoFilter
        class Program
            static void Main(string[] args)
                using (SpreadsheetDocument myDocument = SpreadsheetDocument.Open("copy.xlsx"true))
                    Sheet theSheet = myDocument.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == "Sheet1").FirstOrDefault();
                    if (theSheet != null)
                        WorksheetPart worksheetPart = myDocument.WorkbookPart.GetPartById(theSheet.Id) as WorksheetPart;
                        AutoFilter autoFilter1 = new AutoFilter() { Reference = "A1:B6" };
                        PageMargins pgMargins = worksheetPart.Worksheet.Descendants<PageMargins>().FirstOrDefault();
                        worksheetPart.Worksheet.InsertBefore<AutoFilter>(autoFilter1, pgMargins);

    You need to change


    to be

    PageMargins pgMargins = worksheetPart.Worksheet.Descendants<PageMargins>().FirstOrDefault();
    worksheetPart.Worksheet.InsertBefore<AutoFilter>(autoFilter1, pgMargins);

    Hope this can help you and just feel free to follow up after you have tried.

    Best Regards,

    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Bruce Song Friday, August 26, 2011 8:59 AM
    Thursday, August 18, 2011 8:35 AM