Answered by:
How to fetch/filter Exceldata after importing in datagridview from date to date in C#.

Question
-
Hay Guys!
I want to filter Excel data sheet after importing in datagridview between tow period,
(e.g. I have month repport as excel sheet that contains 8 columns and 1700 recordes , I want to show excel sheet from 1/10/2019 to 5/10/2019
I have filter button when I click it it will fiter the sheet and show only the data at 1/10/2019 to 5/10/2019 date.
I have this line in filter button
OleDbCommand command = new OleDbComman("Select *…………………………………., con);
I cann't write an excepression here, I have datagridview, button to select button to select excel path, dateTimePicker1,dateTimePicker2, and filter button.
Thanks!
- Moved by CoolDadTx Friday, November 8, 2019 1:51 PM Winforms related
Answers
-
I always recommend injecting a BindingSource into code working with a DataTable.
Here the first image shows unfiltered data while the second filtered. Note two buttons, filter and remove filter
In the code below DataOperations.ReadDataTable() gets the data into a DataTable. The filtering is done via the two DateTimePickers via the underlying DataTable from the DataSource of the BindingSource via the DefaultView.RowFilter.
public partial class Form1 : Form { private DataOperations _dataOperations = new DataOperations(); private BindingSource _bindingSource = new BindingSource(); public Form1() { InitializeComponent(); Shown += Form1_Shown; } private void Form1_Shown(object sender, EventArgs e) { DataTable customerTable = _dataOperations.ReadDataTable(); _bindingSource.DataSource = customerTable; dataGridView1.DataSource = _bindingSource; } private void FilterButton_Click(object sender, EventArgs e) { ((DataTable) _bindingSource.DataSource).DefaultView.RowFilter = $"OrderDate >= '{dateTimePicker1.Value.ToShortDateString()}' AND " + $"OrderDate <= '{dateTimePicker2.Value.ToShortDateString()}'"; } private void RemoveFilter_Click(object sender, EventArgs e) { ((DataTable) _bindingSource.DataSource).DefaultView.RowFilter = ""; } }
Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
NuGet BaseConnectionLibrary for database connections.
- Proposed as answer by Daniel_Zhang-MSFTMicrosoft contingent staff Monday, November 11, 2019 7:16 AM
- Marked as answer by sara87 Sunday, December 8, 2019 2:29 PM
All replies
-
I always recommend injecting a BindingSource into code working with a DataTable.
Here the first image shows unfiltered data while the second filtered. Note two buttons, filter and remove filter
In the code below DataOperations.ReadDataTable() gets the data into a DataTable. The filtering is done via the two DateTimePickers via the underlying DataTable from the DataSource of the BindingSource via the DefaultView.RowFilter.
public partial class Form1 : Form { private DataOperations _dataOperations = new DataOperations(); private BindingSource _bindingSource = new BindingSource(); public Form1() { InitializeComponent(); Shown += Form1_Shown; } private void Form1_Shown(object sender, EventArgs e) { DataTable customerTable = _dataOperations.ReadDataTable(); _bindingSource.DataSource = customerTable; dataGridView1.DataSource = _bindingSource; } private void FilterButton_Click(object sender, EventArgs e) { ((DataTable) _bindingSource.DataSource).DefaultView.RowFilter = $"OrderDate >= '{dateTimePicker1.Value.ToShortDateString()}' AND " + $"OrderDate <= '{dateTimePicker2.Value.ToShortDateString()}'"; } private void RemoveFilter_Click(object sender, EventArgs e) { ((DataTable) _bindingSource.DataSource).DefaultView.RowFilter = ""; } }
Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
NuGet BaseConnectionLibrary for database connections.
- Proposed as answer by Daniel_Zhang-MSFTMicrosoft contingent staff Monday, November 11, 2019 7:16 AM
- Marked as answer by sara87 Sunday, December 8, 2019 2:29 PM
-
-
Hi!
Thank you for your answer but I have problem with this row, what is DataOperations class and How I can get it?
private DataOperations _dataOperations = new DataOperations();
Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
NuGet BaseConnectionLibrary for database connections.