none
checking excel row values and return column header in c# winforms RRS feed

  • Question

  • Hi, I currently have program that checks a date that is selected in MonthCalendar and uses it to search through the first column [date] for a matching value before performing a function. This function is to check the rest of the columns among the same row for a certain Value and then returning the column headers that have that Value. How do I do this?

    private void button1_Click(object sender, EventArgs e)
            {
                string date = monthCalendar1.SelectionRange.Start.ToShortDateString();
    
                string CSVFilePathName = @"pathname.csv";
                string[] Lines = File.ReadAllLines(CSVFilePathName);
                string[] Fields;
                Fields = Lines[0].Split(new char[] { ',' });
                int Cols = Fields.GetLength(0);
                DataTable dt5 = new DataTable();
                for (int i = 0; i < Cols; i++)
                    dt5.Columns.Add(Fields[i].ToLower(), typeof(string));
                DataRow Row;
                for (int i = 1; i < Lines.GetLength(0); i++)
                {
                    Fields = Lines[i].Split(new char[] { ',' });
                    Row = dt5.NewRow();
                    for (int f = 0; f < Cols; f++)
                        Row[f] = Fields[f];
                    dt5.Rows.Add(Row);
                }
    
                for (int i = 0; i < 29; i++)
                {
                    Object o = dt5.Rows[i]["date"];
                    if (o.ToString() == date)
                    {
                        (INSERT CODE HERE TO CHECK REST OF COLUMNS ON SAME ROW AND RETURN COLUMN HEADER)  
                    }
                }
            }

    • Edited by Dankoh97 Tuesday, November 14, 2017 12:24 AM
    Monday, November 13, 2017 1:26 AM

Answers

  • Hi Dankoh97,

    >>This function is to check the rest of the columns among the same row for a certain Value and then returning the column headers that have that Value. How do I do this?

    According to your description, it seems that you want to retrieve the special row's column name and related value. if so, you could storage related content into a dictionary variable. please refer to the following code.

    If I misunderstand your issue, please describe it in detailed and feel free let me know.

    private void button1_Click(object sender, EventArgs e)
            {
                string date = monthCalendar1.SelectionRange.Start.ToShortDateString();
    
                string CSVFilePathName = @"D:\Data\CSV\Test3.csv";
                string[] Lines = File.ReadAllLines(CSVFilePathName);
                string[] Fields;
                Fields = Lines[0].Split(new char[] { ',' });
                int Cols = Fields.GetLength(0);
                DataTable dt5 = new DataTable();
                for (int i = 0; i < Cols; i++)
                    dt5.Columns.Add(Fields[i].ToLower(), typeof(string));
                DataRow Row;
                for (int i = 1; i < Lines.GetLength(0); i++)
                {
                    Fields = Lines[i].Split(new char[] { ',' });
                    Row = dt5.NewRow();
                    for (int f = 0; f < Cols; f++)
                        Row[f] = Fields[f];
                    dt5.Rows.Add(Row);
                }
                Dictionary<string, object> dic = new Dictionary<string, object>();
                for (int i = 0; i < 29; i++)
                {
                    Object o = dt5.Rows[i]["date"];
                    if (o.ToString() == date)
                    {
                        //(INSERT CODE HERE TO CHECK REST OF COLUMNS ON SAME ROW AND RETURN COLUMN HEADER)  
                        foreach (DataColumn col in dt5.Columns)
                        {
                            if(col.Caption != "date")
                            { 
                                dic[col.Caption] = dt5.Rows[i][col.Caption];
                            }                    }
                        break;
                    }
                }
    
            }

    Best regards,

    Cole Wu


    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 Dankoh97 Wednesday, December 6, 2017 1:06 AM
    Tuesday, November 14, 2017 5:44 AM
    Moderator

All replies

  • Hi Dankoh97,

    >>This function is to check the rest of the columns among the same row for a certain Value and then returning the column headers that have that Value. How do I do this?

    According to your description, it seems that you want to retrieve the special row's column name and related value. if so, you could storage related content into a dictionary variable. please refer to the following code.

    If I misunderstand your issue, please describe it in detailed and feel free let me know.

    private void button1_Click(object sender, EventArgs e)
            {
                string date = monthCalendar1.SelectionRange.Start.ToShortDateString();
    
                string CSVFilePathName = @"D:\Data\CSV\Test3.csv";
                string[] Lines = File.ReadAllLines(CSVFilePathName);
                string[] Fields;
                Fields = Lines[0].Split(new char[] { ',' });
                int Cols = Fields.GetLength(0);
                DataTable dt5 = new DataTable();
                for (int i = 0; i < Cols; i++)
                    dt5.Columns.Add(Fields[i].ToLower(), typeof(string));
                DataRow Row;
                for (int i = 1; i < Lines.GetLength(0); i++)
                {
                    Fields = Lines[i].Split(new char[] { ',' });
                    Row = dt5.NewRow();
                    for (int f = 0; f < Cols; f++)
                        Row[f] = Fields[f];
                    dt5.Rows.Add(Row);
                }
                Dictionary<string, object> dic = new Dictionary<string, object>();
                for (int i = 0; i < 29; i++)
                {
                    Object o = dt5.Rows[i]["date"];
                    if (o.ToString() == date)
                    {
                        //(INSERT CODE HERE TO CHECK REST OF COLUMNS ON SAME ROW AND RETURN COLUMN HEADER)  
                        foreach (DataColumn col in dt5.Columns)
                        {
                            if(col.Caption != "date")
                            { 
                                dic[col.Caption] = dt5.Rows[i][col.Caption];
                            }                    }
                        break;
                    }
                }
    
            }

    Best regards,

    Cole Wu


    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 Dankoh97 Wednesday, December 6, 2017 1:06 AM
    Tuesday, November 14, 2017 5:44 AM
    Moderator
  • Hi I am getting an error saying "The name 'dic' does not exist in the current context".

    Also I do not want to get both the special row's column name and value. I would only like to Check the entire row for a value (e.g 1) and for all the columns in this row that contain 1, I would like to return the column header. :)

    Wednesday, November 15, 2017 2:01 AM
  • Hi Dankoh97,

    >>Hi I am getting an error saying "The name 'dic' does not exist in the current context".

    Please check if your project have the following line of code, which create a dictionary object.

    Dictionary<string, object> dic = new Dictionary<string, object>();
    >>Also I do not want to get both the special row's column name and value. I would only like to Check the entire row for a value (e.g 1) and for all the columns in this row that contain 1, I would like to return the column header. :)

    If you want to get special column name via rows value, please refer to the following code.

    string date = monthCalendar1.SelectionRange.Start.ToShortDateString();
    
                string CSVFilePathName = @"D:\Data\CSV\Test3.csv";
                string[] Lines = File.ReadAllLines(CSVFilePathName);
                string[] Fields;
                Fields = Lines[0].Split(new char[] { ',' });
                int Cols = Fields.GetLength(0);
                DataTable dt5 = new DataTable();
                for (int i = 0; i < Cols; i++)
                    dt5.Columns.Add(Fields[i].ToLower(), typeof(string));
                DataRow Row;
                for (int i = 1; i < Lines.GetLength(0); i++)
                {
                    Fields = Lines[i].Split(new char[] { ',' });
                    Row = dt5.NewRow();
                    for (int f = 0; f < Cols; f++)
                        Row[f] = Fields[f];
                    dt5.Rows.Add(Row);
                }
                Dictionary<string, object> dic = new Dictionary<string, object>();
    
                string columnName = string.Empty;
                for (int i = 0; i < 29; i++)
                {
                    Object o = dt5.Rows[i]["date"];
                    if (o.ToString() == date)
                    {
    
                        //(INSERT CODE HERE TO CHECK REST OF COLUMNS ON SAME ROW AND RETURN COLUMN HEADER)  
                        foreach (DataColumn col in dt5.Columns)
                        {
                            if (col.Caption != "date")
                            {
                                //dic[col.Caption] = dt5.Rows[i][col.Caption];
                                if (dt5.Rows[i][col.Caption].ToString() == "1")
                                {
                                    columnName = col.Caption;
                                }
                            }
                        }
                        break;
                    }
                }

    Best regards,

    Zhanglong Wu


    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.

    Tuesday, November 21, 2017 6:22 AM
    Moderator