Re: About autoFilter in Excel

Hi,

 

For a given table in excel, after adding an auto filter following xml are generated.

·         In the sheet.xml autoFilter tag is created.

<autoFilter ref="B2:D5">

    <filterColumn colId="0">

      <filters>

        <filter val="b"/>

        <filter val="c"/>

      </filters>

    </filterColumn>

    <filterColumn colId="1">

      <filters>

        <filter val="e"/>

      </filters>

    </filterColumn>

</autoFilter>

It represents the table to which autoFileter is applied.  AutoFilters specify criteria for which cells in a table should be displayed.  filterColumn has zero based index colId, in the above xml snippet, colId="0" defines filter condition for the first column (B2). The rows not satisfying the filter condition will be hidden.

 

Similarly filter conditions for other columns are represented.

 

·         In workbook a definedName will be created. This represents the table selected for filter.

<definedNames>

<definedName name="_xlnm._FilterDatabase" localSheetId="0" hidden="1">Sheet1!$B$2:$D$5</definedName>

</definedNames>

Names can be defined and assigned to a cell location or range or to a formula or constant value.

hidden="1", Specifies a boolean value that indicates whether the defined name is hidden in the user interface.

               

Basically defined Names are used instead of a cell or a range in formulas.  A defined name in a formula can make it easier to understand the purpose of the formula. For example, the

formula =SUM(FirstQuarterSales) might be easier to identify than =SUM(C20:C30).

 

Names can be scoped to either the entire workbook (default) or just the local sheet. Names scoped to the local sheet cannot be referenced from other sheets. Names scoped to the workbook can be referenced from any sheet.

 

Sandeep

Sandeep,

So what change will remove these filters and show all the hidden rows?

regards,

Bibek

Hi Bibek

Like I answered on your other thread:
Removing the autoFilter elements from a SpreadsheetML's worksheet will only remove the detail about the filter - this does not change the visibility of the cells that may have been hidden by Excel as a result of the filter used.

The easiest way to understand the changes necessary is to take the document with filters and save it as a different file without any filters.  Then use the Open XML SDK 2.0 Productivity Tool to Compare the files - you will then see each component that needs to change (i.e. The AutoFilter, SheetProperty's FilterMode, and hidden attribute against cells).