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 !

Recommended Answers

All 6 Replies

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.

thank u for Your guide !

You're welcome

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.