Smrf.NodeXL.ExcelTemplate.DynamicFilterDialog.InitializeDynamicFiltersForOneTable C# (CSharp) Метод

    InitializeDynamicFiltersForOneTable
    (
        String sWorksheetName,
        String sTableName,
        GroupBox oGroupBox
    )
    {
        Debug.Assert( !String.IsNullOrEmpty(sWorksheetName) );
        Debug.Assert( !String.IsNullOrEmpty(sTableName) );
        Debug.Assert(oGroupBox != null);
        AssertValid();

        Control.ControlCollection oChildControls = oGroupBox.Controls;
        Int32 iChildControls = oChildControls.Count;

        // Clear the GroupBox of all child controls except the first child,
        // which is a Label that says "no columns."

        for (Int32 i = iChildControls - 1; i > 0; i--)
        {
            oChildControls.RemoveAt(i);
        }

        // Get a collection of dynamic filter parameters, one for each column
        // in the table that can be filtered on.

        ICollection<DynamicFilterParameters> oTableParameters =
            DynamicFilterUtil.GetDynamicFilterParameters(
                m_oWorkbook, sWorksheetName, sTableName
                );

        // If there are no filterable columns, the "no columns" Label should be
        // made visible.  It is now the only child control.

        Debug.Assert(oChildControls.Count == 1);
        Debug.Assert(oChildControls[0] is Label);

        Label oNoFilterLabel = (Label)oChildControls[0];
        oNoFilterLabel.Visible = (oTableParameters.Count == 0);

        // Assuming that there are filterable columns, use the label's
        // coordinates as a starting point for dynamically added controls.

        Int32 iX = oNoFilterLabel.Location.X;
        Int32 iY = oNoFilterLabel.Location.Y;

        // An Excel formula in a new Dynamic Filter column will be used for
        // dynamic filtering.  Here is an example of what the final formula
        // will look like, assuming that non-numeric cells should be filtered.
        // (It's a bit more complicated if non-numeric cells should not be
        // filtered.)
        //
        // = IF(AND([ColumnName1] >= $P$2, [ColumnName1] <= $Q$2,
        // [ColumnName2] >= Misc!$P$3, [ColumnName2] <= Misc!$Q$3), TRUE, FALSE)
        //
        // A StringBuilder is used to collect the conditions between the AND()
        // parentheses, the part that looks like this:
        //
        // [ColumnName1] >= Misc!$P$2, [ColumnName1] <= Misc!$Q$2,
        // [ColumnName2] >= Misc!$P$3, [ColumnName2] <= Misc!$Q$3

        StringBuilder oDynamicFilterConditions = new StringBuilder();

        Int32 iDynamicFilters = 0;

        foreach (DynamicFilterParameters oDynamicFilterParameters in
            oTableParameters)
        {
            switch (oDynamicFilterParameters.GetType().Name)
            {
                case "NumericFilterParameters":
                case "DateTimeFilterParameters":

                    Label oLabel = AddLabelToGroupBox(oDynamicFilterParameters,
                        oGroupBox, iX, iY);

                    PictureBox oHistogram = AddHistogramToGroupBox(
                        sWorksheetName, oDynamicFilterParameters, oGroupBox,
                        iX, ref iY);

                    Debug.Assert(oDynamicFilterParameters is
                        NumericFilterParameters);

                    IDynamicFilterRangeTrackBar oDynamicFilterRangeTrackBar =
                        AddRangeTrackBarToGroupBox(
                            (NumericFilterParameters)oDynamicFilterParameters,
                            sTableName, oGroupBox, oDynamicFilterConditions,
                            iX, ref iY);

                    // Adjust the location and dimension of the controls that
                    // depend on other controls.

                    Rectangle oInternalTrackBarBounds =
                        oDynamicFilterRangeTrackBar.InternalTrackBarBounds;

                    oHistogram.Width = oInternalTrackBarBounds.Width;

                    oHistogram.Left = oDynamicFilterRangeTrackBar.Left
                        + oInternalTrackBarBounds.Left;

                    oLabel.Width = oHistogram.Left - oLabel.Left
                        - LabelRightMargin;

                    oLabel.Height = oHistogram.Height;

                    break;

                default:

                    Debug.Assert(false);
                    break;
            }

            iDynamicFilters++;

            if (iDynamicFilters == MaximumDynamicFiltersPerTable)
            {
                break;
            }
        }

        oGroupBox.Height = iY;

        // Add a TRUE filter condition.  If the conditions are empty (which
        // happens when there are no filterable columns), this results in the
        // following final formula, which works fine:
        //
        // = IF(AND(TRUE), TRUE, FALSE)
        //
        // Otherwise, the added TRUE condition avoids an error caused by the
        // final comma in the final condition, like this:
        //
        // = IF(AND([ColumnName1] >= Misc!$P$2,
        // [ColumnName1] <= Misc!$Q$2, TRUE), TRUE, FALSE)

        oDynamicFilterConditions.Append("TRUE");

        String sDynamicFilterFormula = String.Format(

            "= IF(AND({0}), TRUE, FALSE)"
            ,
            oDynamicFilterConditions
            );

        // Add the dynamic filter formula to the table.

        return ( AddDynamicFilterFormulaToTable(sWorksheetName, sTableName,
            sDynamicFilterFormula) );
    }