none
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 };
            worksheetPart.Worksheet.Append(autoFilter1);
    
            worksheetPart.Worksheet.Save();
          }
          
    
          // Save the workbook.
          Workbook.Workbook.Save();
        }

     

    Any idea what I'm doing wrong?

    Thanks.


    Monday, August 15, 2011 9:34 PM

Answers

  • 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)
            {
                File.Copy("ExcelFilter.xlsx""copy.xlsx",true);
                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);
                        
                        worksheetPart.Worksheet.Save();
                    }
                    myDocument.WorkbookPart.Workbook.Save();
                }
            }
        }
    }
    

    You need to change

    worksheetPart.Worksheet.Append(autoFilter1);

    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