CODEWITHSUNDEEP
c#exceldatagridviewrowdatagridviewcolumn
user3193794
user3193794

Reputation: 105

delete some row based on cell value before importing to datagridview using c#

I am trying to import excel file to datagridview from stoarage. With the following code I can successfully uploaded two column from my excel file. But I want to modify my excel file progmamicaly before importing it. In my excel sheet one column contain City name and other column contain population number. I want to delete the row which contain lower that 10,000 people. But I really do not know how to do it. Here is the code by which I imported excel file.

private void button1_Click_1(object sender, EventArgs e)
{
  String name = "Gemeinden_31.12.2011_Vergleich";
  String constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                 @"C:\C# tutorial Backup\Zensus_Gemeinden_org.xlsx" +
                 ";Extended Properties='Excel 12.0 XML;HDR=YES;';";

OleDbConnection con = new OleDbConnection(constr);
OleDbCommand oconn = new OleDbCommand("Select * From [" + name + "$D8:E11300]", con);
con.Open();

OleDbDataAdapter sda = new OleDbDataAdapter(oconn);
DataTable data = new DataTable();
sda.Fill(data);
dataGridView1.DataSource = data;


}

Upvotes: 2

Views: 385

Answers (2)

HiteshAjudiya
HiteshAjudiya

Reputation: 137

I am saying that we need to add filter while retrieving excel data only, so that it will only import require data from excel as below:

private void button1_Click_1(object sender, EventArgs e)
{
    String name = "Gemeinden_31.12.2011_Vergleich";
    String constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                   @"C:\C# tutorial Backup\Zensus_Gemeinden_org.xlsx" +
                   ";Extended Properties='Excel 12.0 XML;HDR=YES;';";

    OleDbConnection con = new OleDbConnection(constr);
    OleDbCommand oconn = new OleDbCommand("Select * From [" + name + "$D8:E11300] Where [population number] > 10000", con);
    con.Open();

    OleDbDataAdapter sda = new OleDbDataAdapter(oconn);
    DataTable data = new DataTable();
    sda.Fill(data);
    dataGridView1.DataSource = data;
    }

Upvotes: 1

HiteshAjudiya
HiteshAjudiya

Reputation: 137

I think you can just add where condition in your select query only.

Like "Where population > 10000

So that it will only retrieve where population is more than 10000

Upvotes: 0

Related Questions