Hi All
I Importing a Excel File to a DataGrid .
I want to filter some rows have the code of "91" and "90" for i.e. in the Column of "A" :
so , I added manually , A column in the DataGridView , name "Column1" . now when i imported the excel file to this datagridview , the cell rows that have the value of for e.g. 90 and 91 , insert a value for instance 1 to the rows of Column1, the value of 100 and 101 , insert 2 and so on .
code for Column1 Value of Column A in excelFile
1 -------------> 90
1 -------------> 91
2 -------------> 100
2 -------------> 101
How can i do this ...
(excuse me for my bad english !)
thanx for help !

7 Years
Discussion Span
Last Post by sknake

Can you upload a project and sample excel file? I'm having trouble envisioning the task you're describing...


thanks , I Upload the file in this Link

When I want to Load App. , I want to insert a Suitable Value to the Rows of Column1 .


thanks , I Upload the file in this Link

When I want to Load App. , I want to insert a Suitable Value to the Rows of Column1 .

I played around with this a little bit and it has taken me a little time to figure out some things, but I think understand what you are trying to do now. Unfortunately, I have some other errands to attend to in a moment, but here are some tips for whoever endeavors...

  • Remove the path from the filename in the conect string
  • Find the spreadsheet in the root folder of the extract, and copy this file into the debug folder because the one currently in the debug folder is NOT a good sample!
  • The imported values in the grid translate to double types
  • Column1 can probably be populated with a modulo algorythm

My arabic is a little rusty so I created a new project.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;

namespace daniweb.bank
  public partial class Form1 : Form
    const string fileName = @"..\..\Test.xls";
    private DataTable dt;

    public static string BuildExcelConnectionString(string Filename, bool FirstRowContainsHeaders)
      return string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='{0}';Extended Properties=\"Excel 8.0;HDR={1};\"",
                          Filename.Replace("'", "''"),
                          FirstRowContainsHeaders ? "Yes" : "No");

    public Form1()

    private void Form1_Load(object sender, EventArgs e)


    private void button1_Click(object sender, EventArgs e)
      if (!File.Exists(fileName))
        MessageBox.Show("Cannot find file");

      string connStr = BuildExcelConnectionString(fileName, true);
      using (OleDbConnection conn = new OleDbConnection(connStr))
        using (OleDbCommand cmd = new OleDbCommand("Select * From [Sheet1$]", conn))
          using (OleDbDataReader dr = cmd.ExecuteReader())
            if (dt != null)
            dt = new DataTable();

      dt.Columns.Add(new DataColumn("FilterColumn", typeof(int)));
      foreach (DataRow row in dt.Rows)
        int filterCode = default(int);

        if (row["Code"] == DBNull.Value)
          filterCode = -1;
          int code = Convert.ToInt32(row["Code"]);
          if ((code >= 90) && (code < 100))
            filterCode = 1;
          else if ((code >= 100) && (code < 110))
            filterCode = 2;

        row["FilterColumn"] = filterCode;

      dataGridView1.DataSource = dt;

You can do math on the "Code" to automatically step the filter column by 1 when the value increments by 10. I manually defined the ranges since I wasn't sure if that was the direction you wanted to take.


You're welcome

Please mark this thread as solved if you have found an answer to your question and good luck!

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.