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!

Answer 1

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.

StackOverFlow

Answer 2

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();

Answer 3

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();

I didn't include DataOperations because it really doesn't matter how the data was retrieved. A basic version of DataOperations (not the same one as I deleted the one used here, made it just for this question but after a day figured why keep it) which requires the following (mine) NuGet package for building the connection string but you can discard it and do the connection string.

---

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.

StackOverFlow