none
How do we check proper result in every column in excel data or data table using C# RRS feed

  • Question

  • I have data in excel file or data table.

    example data is below

    Mark1    Mark2   Mark3 Mark4

    -------   --------  -------  ------

    100       200       300     400

    120        140      160      180

    210        220      230      240

    260        270      290      295

    When i pass the parameter value like EmpMark => 220

    So, EmpMark "220" should check in all the columns like mark1,mark2,mark3,mark4,....

    if value found in the corresponding column then loop should terminate and identify the columns and rows of the value "220"

    Final result would be for "220"

    Result column is ==> Mark2

    Result Row is ==> 3

    How do we do this in c#..?

    Monday, April 6, 2020 3:07 AM

Answers

  • Hi Gani tpt,

    If you want to traverse the columns in the table in order to find the first matching item, you can refer to the following code.

        static DataTable dt = new DataTable("test");
    
        static void Main(string[] args)
        {
            // Code that create a table
            // ...
            // Code omitted
            // ...
    
            Search(dt, "220");
            Console.ReadKey();
        }
    
        static void Search(DataTable dt, string num)
        {
            int rowindex;
            foreach (DataColumn dc in dt.Columns)
            {
                rowindex = 0;
                foreach (DataRow dr in dt.Rows)
                {
                    rowindex++;
                    var field = dr[dc].ToString();
                    if (field == num)
                    {
                        Console.WriteLine($"Result column is ==> {dc.ColumnName}, Result Row is ==> {rowindex}");
                        return;
                    }
                }
            }
        }

    Hope this can help you.

    Regards,

    Kyle


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Gani tpt Friday, April 10, 2020 7:34 AM
    Monday, April 6, 2020 8:32 AM
  • Hi Gani,

    Maybe you can convert the excel file to a Datatable first.

    Please refer to this:

        private void btnExceltoDatatable_Click(object sender, EventArgs e)
        {
            string filepath = @"C:\Users\username\Desktop\test.xlsx";
    
            DataTable dt = new DataTable();
    
            using (var odConnection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+ filepath +";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';"))
            {
                odConnection.Open();
    
                using (OleDbCommand cmd = new OleDbCommand())
                {
                    cmd.Connection = odConnection;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "SELECT * FROM [Sheet1$]";
                    using (OleDbDataAdapter oleda = new OleDbDataAdapter(cmd))
                    {
                        oleda.Fill(dt);
                    }
                }
                odConnection.Close();
            }
    
            // Call Search
            Search(dt, "220");
        }

    If it shows any error about "OLEDB", plesae try to install the "AccessDatabaseEngine" from this link.

    Regards,

    Kyle


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Gani tpt Friday, April 10, 2020 7:35 AM
    Friday, April 10, 2020 2:24 AM

All replies

  • Hi Gani tpt,

    If you want to traverse the columns in the table in order to find the first matching item, you can refer to the following code.

        static DataTable dt = new DataTable("test");
    
        static void Main(string[] args)
        {
            // Code that create a table
            // ...
            // Code omitted
            // ...
    
            Search(dt, "220");
            Console.ReadKey();
        }
    
        static void Search(DataTable dt, string num)
        {
            int rowindex;
            foreach (DataColumn dc in dt.Columns)
            {
                rowindex = 0;
                foreach (DataRow dr in dt.Rows)
                {
                    rowindex++;
                    var field = dr[dc].ToString();
                    if (field == num)
                    {
                        Console.WriteLine($"Result column is ==> {dc.ColumnName}, Result Row is ==> {rowindex}");
                        return;
                    }
                }
            }
        }

    Hope this can help you.

    Regards,

    Kyle


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Gani tpt Friday, April 10, 2020 7:34 AM
    Monday, April 6, 2020 8:32 AM
  • Thanks kyle.

    If it is excel table data, how could we proceed..?

    Note : The main intention is, the value should check right from the first column is the starting point..


    • Edited by Gani tpt Friday, April 10, 2020 1:48 AM First column is starting point
    Friday, April 10, 2020 1:47 AM
  • Hi Gani,

    Maybe you can convert the excel file to a Datatable first.

    Please refer to this:

        private void btnExceltoDatatable_Click(object sender, EventArgs e)
        {
            string filepath = @"C:\Users\username\Desktop\test.xlsx";
    
            DataTable dt = new DataTable();
    
            using (var odConnection = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+ filepath +";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';"))
            {
                odConnection.Open();
    
                using (OleDbCommand cmd = new OleDbCommand())
                {
                    cmd.Connection = odConnection;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "SELECT * FROM [Sheet1$]";
                    using (OleDbDataAdapter oleda = new OleDbDataAdapter(cmd))
                    {
                        oleda.Fill(dt);
                    }
                }
                odConnection.Close();
            }
    
            // Call Search
            Search(dt, "220");
        }

    If it shows any error about "OLEDB", plesae try to install the "AccessDatabaseEngine" from this link.

    Regards,

    Kyle


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Gani tpt Friday, April 10, 2020 7:35 AM
    Friday, April 10, 2020 2:24 AM
  • Thanks....
    Friday, April 10, 2020 7:35 AM