Using DataGridView to implement complex filtering for Excel columns

Source: Internet
Author: User

There is a project where the customer asks for a table to have complex filtering based on columns like Excel, and it's best not to change too many of the original usage habits.

Search the Internet, most of the whole column is bound to a ComboBox, covering the column headings, so as to achieve a simple filtering function.

Careful study of the next Excel complex screening, only by DataGridView right-click function is not very good fully realized, so I think with a panel to filter panel.


===================================================================

Create a new project, drag the Form1 into a DataGridView, a panel, a statusstrip.

In the panel, put 2 label,7 button,3 combobox,1 a listbox.

3 ToolStripStatusLabel are placed in the StatusStrip.


Click "Ascending" or "descending" to sort directly according to this column.

In the filter condition, the first ComboBox selects "and" or "or", the second ComboBox is used to show equals, greater than, less than, contains, and so on, and the third ComboBox is used to display all the non-repeating contents of the current column.

Click "Add Condition" to add the above filter to the ListBox, select a condition in the ListBox, click "Remove Condition" to remove the condition.

Finally click "OK" to perform the filter, click "Cancel" to undo the action done by this time.


ToolStripStatusLabel1 is used to display ascending or descending order of a column.

The ToolStripStatusLabel2 is used to display the M data filtered from the N bar data.

TOOLSTRIPSTATUSLABEL3 the statement used to display the filter.


--------------------------------------------------

1. Definition of 5 global variables

        DataTable DT;        DataView DV;        int row_total;//Total row number        int row_select;//filter after number        string[] str_ltb;//storage Filter Condition array

2. Fill in the data with the DataGridView and initialize some of the data

private void Form1_Load (object sender, EventArgs e) {dt = new DataTable (); Dt.            Columns.Add ("ID", Type.GetType ("System.Int32")); Dt. Columns[0].  AutoIncrement = true; The column values are automatically incremented by dt. Columns[0].  AutoIncrementSeed = 1; The starting value dt. Columns[0].  AutoIncrementStep = 1; Step dt.            Columns.Add ("Name", Type.GetType ("System.String")); Dt.            Columns.Add ("Birthday", Type.GetType ("System.String")); Dt.            Columns.Add ("Address", Type.GetType ("System.String")); Dt.            Columns.Add ("Salary", Type.GetType ("System.Int32")); Dt.            Rows.Add (new object[] {null, "Zhang Yi", "1980-01-01", "Beijing", 15000}); Dt.            Rows.Add (new object[] {null, "Zhang Yi", "1981-02-28", "Shanghai", 20000}); Dt.            Rows.Add (new object[] {null, "Zhang San", "1982-03-18", "Beijing", 10000}); Dt.            Rows.Add (new object[] {null, "Zhang Four", "1983-04-01", "Hebei", 5000}); Dt.        Rows.Add (new object[] {null, "Five", "1984-05-14", "Beijing", 8000});    Dt.            Rows.Add (new object[] {null, "Lee", "1985-10-01", "Guangzhou", 11000}); Dt.            Rows.Add (new object[] {null, "Li II", "1986-09-07", "Shanghai", 18000}); Dt.            Rows.Add (new object[] {null, "Lie Triple", "1987-06-10", "Beijing", 20000}); Dt.            Rows.Add (new object[] {null, "John Doe", "1988-04-01", "Guangzhou", 5000}); Dt.            Rows.Add (new object[] {null, "Lee", "1989-03-05", "Beijing", 7000}); Dt.            Rows.Add (new object[] {null, "Wang Yi", "1983-01-09", "Guangzhou", 15000}); Dt.            Rows.Add (new object[] {null, "King II", "1985-12-03", "Shanghai", 30000}); Dt.            Rows.Add (new object[] {null, "Wang San", "1987-08-22", "Beijing", 5000}); Dt.            Rows.Add (new object[] {null, "Wangsi", "1989-01-15", "Hebei", 3000}); Dt.            Rows.Add (new object[] {null, "Harry", "1989-05-20", "Beijing", 11000}); DGV.            DataSource = DT; DGV.            Dock = DockStyle.Fill; row_total = dt.            Rows.Count; DV = dt.            DefaultView; DGV.            DataSource = DV;   Row_select = Row_total;         Do not click on column headings to automatically sort for (int i = 0; i < DGV. Columns.count; i++) {DGV. Columns[i].            SortMode = datagridviewcolumnsortmode.notsortable; } pl_dgv_extend.            Visible = false;            Toolstripstatuslabel1.text = "in ascending order by ID column";            Toolstripstatuslabel2.text = "Find" + Row_select + "in" + Row_total + "bar data";        Toolstripstatuslabel3.text = ""; }

3, click DGV column headings, panel display, and according to the column headings in different locations, corresponding to the corresponding location

private void Dgv_columnheadermouseclick (object sender, DataGridViewCellMouseEventArgs e) {int dleft, DT            Op Gets the DGV column header position relative to coordinates Rectangle range = DGV.            Getcelldisplayrectangle (E.columnindex, E.rowindex, false); Calculates the pl_dgv_extend position coordinate dleft = range. Left + DGV.            Left; Dtop = range. Top + DGV. Top + range.            Height; Set the Pl_dgv_extend position beyond the width of the frame, and align to the right of DGV if (Dleft + pl_dgv_extend. Width > this. Width) {pl_dgv_extend. SetBounds (DGV. Width-pl_dgv_extend. Width, Dtop, Pl_dgv_extend. Width, Pl_dgv_extend.            Height); } else {pl_dgv_extend. SetBounds (Dleft, Dtop, Pl_dgv_extend. Width, Pl_dgv_extend.            Height); }//Set Cb_condition drop-down menu content cb_condition.            Items.clear (); for (int i = 0; i < DGV. Rows.Count;                i++) {bool Isfind = false; for (int j = 0; J < Cb_condition. IteMs. Count; J + +) {if (cb_condition. ITEMS[J]. ToString () = = DGV. Rows[i]. Cells[e.columnindex].                        Value.tostring ()) {Isfind = true; j = cb_condition.                    Items.count;//break}} if (!isfind) { Cb_condition. Items.Add (DGV. Rows[i]. Cells[e.columnindex].                Value.tostring ()); }} pl_dgv_extend.            Visible = true; Lb_columnname. Text = DGV. Columns[e.columnindex].            Name; Initializes the selection Cb_andor.            SelectedIndex = 0; Cb_operator.            SelectedIndex = 0; Cb_condition.            Text = ""; Stores the existing filter criteria option if (ltb_condition.            Items.Count = = 0) {STR_LTB = null; } else {STR_LTB = new string[ltb_condition.                Items.Count]; Ltb_condition.            Items.copyto (STR_LTB, 0); }       } 

4, Ascending descending code

private void Bt_asc_click (object sender, EventArgs e)        {            dv. Sort = Lb_columnname. Text + "ASC";            Toolstripstatuslabel1.text = "Press" + Lb_columnname. Text + "column in ascending order";            Pl_dgv_extend. Visible = false;        }        private void Bt_desc_click (object sender, EventArgs e)        {            dv. Sort = Lb_columnname. Text + "desc";            Toolstripstatuslabel1.text = "Press" + Lb_columnname. Text + "column in descending order";            Pl_dgv_extend. Visible = false;        }

5. Add Filter criteria

private void Bt_add_click (object sender, EventArgs e) {//null value to determine if (cb_operator. SelectedItem = = NULL | | Cb_condition. Text.trim () = = "") {MessageBox.Show ("Please select the condition!)                ");            Return } if (Cb_andor. SelectedItem = = null && ltb_condition. Items.Count > 0) {MessageBox.Show ("Please select a condition!                ");            Return            }//literal to operator string Str_andor = "", Str_operator = ""; if (ltb_condition. Items.Count > 0) {if (Cb_andor.                SelectedItem = = null) Str_andor = ""; else if (Cb_andor.                selecteditem.tostring () = = "or") Str_andor = "or";            else Str_andor = "and"; } if (Cb_operator.            selecteditem.tostring () = = "equals") Str_operator = "="; else if (cb_operator.            selecteditem.tostring () = = "Greater than") Str_operator = ">"; else if (cb_operator. Selecteditem.tostring ()= = "greater than equals") Str_operator = ">="; else if (cb_operator.            selecteditem.tostring () = = "less than") Str_operator = "<"; else if (cb_operator.            selecteditem.tostring () = = "less than equals") Str_operator = "<=";            else Str_operator = "like"; The filter condition is added to the listbox if (Cb_operator. selecteditem.tostring () = = "contains") {ltb_condition. Items.Add (Str_andor + lb_columnname. Text + "" + Str_operator + "' *" + cb_condition.            Text.trim () + "*"); } else {ltb_condition. Items.Add (Str_andor + lb_columnname. Text + "" + Str_operator + "'" + cb_condition.            Text.trim () + "'"); }//Initialize filter condition Cb_andor.            SelectedIndex = 0; Cb_operator.            SelectedIndex = 0; Cb_condition.        Text = ""; }

6, remove the filter conditions

private void Bt_remove_click (object sender, EventArgs e) {if (Ltb_condi tion. SelectedItem = = null) {MessageBox.Show ("Select the item you want to remove!)                ");            Return } int rownum = Ltb_condition.            SelectedIndex; Ltb_condition.            Items.removeat (rownum); If the first item is removed and the number of items in the listbox is greater than 0//Remove the IF (ltb_condition. Items.Count > 0 && rownum = = 0) {string first = Ltb_condition. Items[0].                ToString ();                string result; if (first. Substring (0, 2) = = "or") {result = first. Substring (2, first.                LENGTH-3); } else {result = first. Substring (3, first.                LENGTH-4); } ltb_condition.                Items.removeat (0); Ltb_condition.            Items.insert (0, result); }        }

7. Clear all filter conditions

private void Bt_clear_click (object sender, EventArgs e)        {            if (MessageBox.Show ("Do you want to remove all filter criteria?") "," Warning ", messageboxbuttons.yesno) = = Dialogresult.yes)            {                ltb_condition. Items.clear ();            }            Dv. RowFilter = ""; Clears the filter condition            pl_dgv_extend. Visible = false;            Row_select = row_total;            Toolstripstatuslabel2.text = "Find" + Row_select + "in" + Row_total + "bar data";            Toolstripstatuslabel3.text = "";        }

8, confirm, the implementation of the screening process

private void Bt_ok_click (object sender, EventArgs e)        {            if (ltb_condition. Items.Count = = 0)            {                dv. RowFilter = "";                Toolstripstatuslabel3.text = "";            }            else            {for                (int i = 0; i < ltb_condition. Items.Count; i++)                {                    if (i = = 0) Toolstripstatuslabel3.text = ltb_condition. Items[0]. ToString ();                    else Toolstripstatuslabel3.text + = "" + ltb_condition. Items[i]. ToString ();                }                Dv. RowFilter = Toolstripstatuslabel3.text;            }            Pl_dgv_extend. Visible = false;            Row_select = dv. Count;            Toolstripstatuslabel2.text = "found in" + Row_total + "bar data" + Row_select + ";        }

9, Cancel, rollback operation

private void Bt_cancle_click (object sender, EventArgs e)        {//rollback operation            Ltb_condition. Items.clear ();            if (STR_LTB! = null) ltb_condition. Items.addrange (STR_LTB);            Pl_dgv_extend. Visible = false;        }

--------------------------------------------------

The above is all the code, the novice, we will look at it. Let's take a look at the actual running effect.


=========================================================

1. Operating interface


2. Click on the column headings


3. Click on the column headings on the side and the panel automatically adjusts the position

4, by salary ascending order


5. Fill in the filter conditions


6. Fill in two conditions


7, the query results, in the lower status bar display filter entries


8, add some conditions


9. Final effect


Note: The control is hidden because the ToolStripStatusLabel content text exceeds the maximum width, so I lengthen the form a bit. As to how to make it part of the show, not found. In fact, the following state bar can be fully implemented with Panel+label, so that once the text is outside the label range, it can be partially displayed.


        /*************************************************************         *          * from C # beginner--phoenix         * May 22, 2015         * http://blog.csdn.net/phoenix36999 * *          **********************************************************/



Using DataGridView to implement complex filtering for Excel columns

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.