Filter Rows based Importing Excel file to a DataGrid ...

[s_mostafa_h]

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 !

[sknake]

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

[s_mostafa_h]

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 .

[DdoubleD]

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

[sknake]

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()
    {
      InitializeComponent();
    }

    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");
        return;
      }

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

      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;
        else
        {
          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.

[s_mostafa_h]

thank u for Your guide !

[sknake]

You're welcome

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