How to read Filtered Excel Data in Datatable Using C# consol

First you need to create OleDBConnection to read data from excel sheet. Once you get the data in DataTable, add filter condition to get filtered data.

try
{
System.Data.OleDb.OleDbConnection MyConnection;
System.Data.DataSet DtSet;
System.Data.OleDb.OleDbDataAdapter MyCommand;
string strHeader7 = "";
strHeader7 = (hdr7) ? "Yes" : "No";
MyConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fn + ";Extended Properties=\"Excel 12.0;HDR=" + strHeader7 + ";IMEX=1\"");
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + wks + "$]", MyConnection);
MyCommand.TableMappings.Add("Table", "TestTable");
DtSet = new System.Data.DataSet();
MyCommand.Fill(DtSet);
DataTable dtObj = DtSet.Tables[0];
string expression = "Date = '1/31/1979' or OrderID = 2";
// string expression = "OrderQuantity = 2 and OrderID = 2";
// Sort descending by column named CompanyName.
string sortOrder = "CompanyName ASC";
DataRow[] foundRows;
// Use the Select method to find all rows matching the filter.
foundRows = table.Select(expression, sortOrder);
// Print column 0 of each returned row.
for (int i = 0; i < foundRows.Length; i++)
Console.WriteLine(foundRows[i][2]);
MyConnection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}

Following link may help you:

http://stackoverflow.com/questions/28705665/how-to-get-only-selected-cells-value-from-filtered-cells-from-excel

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Manas Mohapatra9y ago
First you need to create OleDBConnection to read data from excel sheet. Once you get the data in DataTable, add filter condition to get filtered data.
try
{
    System.Data.OleDb.OleDbConnection MyConnection;
    System.Data.DataSet DtSet;
    System.Data.OleDb.OleDbDataAdapter MyCommand;
    string strHeader7 = "";
    strHeader7 = (hdr7) ? "Yes" : "No";
    MyConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fn + ";Extended Properties=\"Excel 12.0;HDR=" + strHeader7 + ";IMEX=1\"");
    MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + wks + "$]", MyConnection);
    MyCommand.TableMappings.Add("Table", "TestTable");
    DtSet = new System.Data.DataSet();
    MyCommand.Fill(DtSet);
    DataTable dtObj = DtSet.Tables[0];

    string expression = "Date = '1/31/1979' or OrderID = 2";
    // string expression = "OrderQuantity = 2 and OrderID = 2";

    // Sort descending by column named CompanyName.
    string sortOrder = "CompanyName ASC";
    DataRow[] foundRows;

    // Use the Select method to find all rows matching the filter.
    foundRows = table.Select(expression, sortOrder);

    // Print column 0 of each returned row.
    for (int i = 0; i < foundRows.Length; i++)
        Console.WriteLine(foundRows[i][2]);

    MyConnection.Close();
}
catch (Exception ex)
{
    MessageBox.Show(ex.ToString());
}
Following link may help you:

http://stackoverflow.com/questions/28705665/how-to-get-only-selected-cells-value-from-filtered-cells-from-excel