0

I have an excel validation program that imports excel files and places the values on a DataGridView. I want to validate columns January to December which I already did but I want to validate them by their columns name because the code I'm using is static and would only validate from Column Index 4 above (January is Column Index 4).

Here is an image for better visualization:

enter image description here

As you can see in the image, January is Column Index 4. (0 would be Type).

Here are my codes that validates per month:

public static void Validate_Month(DataGridView dataGridView, int month, int select, string kpisel)
    {
        try
        {
            int kpi = 2;
            int category = 3;
            decimal num;

            FileStream fs = new FileStream(@"C:\brandon\InvalidColumnsByMonth.txt", FileMode.OpenOrCreate, FileAccess.Write);
            StreamWriter sw = new StreamWriter(fs);

            sw.BaseStream.Seek(0, SeekOrigin.End);

            StringBuilder sb = new StringBuilder();
            if (dataGridView.ColumnCount > 3)
            {
                sw.WriteLine("----------------------------");
                sw.WriteLine("");

                if (dataGridView.Columns.Contains("Category"))
                {


                    for (int h = select; h <= month; h++)
                    {
                        for (int i = 0; i < dataGridView.RowCount; i++)
                        {
                            if ((dataGridView[2, i].Value.ToString() == kpisel || kpisel == "" || kpisel == null) && !Decimal.TryParse(dataGridView[h, i].Value.ToString(), out num))
                            {
                                if (dataGridView[h, i].Value.ToString() == null || dataGridView[h, i].Value.ToString() == "")
                                {

                                }
                                else
                                {
                                    sb.AppendLine("[KPI]: " + dataGridView.Rows[i].Cells[kpi].Value.ToString());
                                    sb.AppendLine("[Category]: " + dataGridView.Rows[i].Cells[category].Value.ToString());
                                    sb.AppendLine("[Month]:" + dataGridView.Columns[h].Name.ToUpper());
                                    sb.AppendLine("[VALUE]:  " + dataGridView[h, i].Value.ToString() + "");
                                    sb.AppendLine("");

                                    sw.WriteLine("[KPI]: " + dataGridView.Rows[i].Cells[kpi].Value.ToString());
                                    sw.WriteLine("[Category]: " + dataGridView.Rows[i].Cells[category].Value.ToString());
                                    sw.WriteLine("[Month]:" + dataGridView.Columns[h].Name.ToUpper());
                                    sw.WriteLine("[VALUE]: {" + dataGridView[h, i].Value.ToString() + "}");
                                    sw.WriteLine("");
                                }
                            }
                        }
                    }
                }
                else if (!dataGridView.Columns.Contains("Category"))
                {
                    for (int h = select - 1; h <= month; h++)
                    {
                        for (int i = 0; i < dataGridView.RowCount; i++)
                        {


                            if ((dataGridView[2, i].Value.ToString() == kpisel || kpisel == "" || kpisel == null) && !Decimal.TryParse(dataGridView[h, i].Value.ToString(), out num))
                            {
                                if (dataGridView[h, i].Value.ToString() == null || dataGridView[h, i].Value.ToString() == "")
                                {

                                }
                                else
                                {
                                    sb.AppendLine("[KPI]: " + dataGridView.Rows[i].Cells[kpi].Value.ToString());
                                    sb.AppendLine("[Category]: " + dataGridView.Rows[i].Cells[category].Value.ToString());
                                    sb.AppendLine("[Month]:" + dataGridView.Columns[h].Name.ToUpper());
                                    sb.AppendLine("[VALUE]:  " + dataGridView[h, i].Value.ToString() + "");
                                    sb.AppendLine("");

                                    sw.WriteLine("[KPI]: " + dataGridView.Rows[i].Cells[kpi].Value.ToString());
                                    sw.WriteLine("[Category]: " + dataGridView.Rows[i].Cells[category].Value.ToString());
                                    sw.WriteLine("[Month]:" + dataGridView.Columns[h].Name.ToUpper());
                                    sw.WriteLine("[VALUE]: {" + dataGridView[h, i].Value.ToString() + "}");
                                    sw.WriteLine("");

                                }
                            }
                        }
                    }
                }
                if (sb.Length != 0)
                {
                    MessageBox.Show(sb.ToString());
                    //Process.Start(@"C:\brandon\InvalidColumnsByMonth.txt");
                }

                else
                {
                    int h = select;

                    if (dataGridView.Columns.Contains("Category"))
                    {
                        MessageBox.Show("No errors in month of " + dataGridView.Columns[h].Name + ".");
                    }
                    else if (!dataGridView.Columns.Contains("Category"))
                    {
                        MessageBox.Show("No errors in month of " + dataGridView.Columns[h - 1].Name + ".");
                    }
                }
            }

            sw.WriteLine("----------------------------");
            sw.Flush();
            sw.Close();
        }
        catch(Exception e)
        {
            MessageBox.Show(e.ToString());
        }
    }

Here's the method that calls for Validate_Month method

    public static void Validate(CheckBox cb, DataGridView dataGridView1, String month, int i, ComboBox cboColumn)
    {
        if (cb.Checked == true && dataGridView1.Columns.Contains(month) )
        {
            if (cboColumn.SelectedItem != null)
            {
                ExcelMethods.Validate_Month(dataGridView1, 4 + i, 4 + i, cboColumn.SelectedItem.ToString());
            }

            else
            {
                MessageBox.Show("Select a KPI!", "No KPI Selected!", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
    }

And lastly, the dynamic btnValidate for each Tabpage on the checkbox.

 btnValidate.Click += (s, e) =>         //btnValidate Event
                        {
                            Form1.Validate(chkJan, dataGridView, "January", 0, cbo);
                            Form1.Validate(chkFeb, dataGridView, "February", 1, cbo);
                            Form1.Validate(chkMar, dataGridView, "March", 2, cbo);
                            Form1.Validate(chkApr, dataGridView, "April", 3, cbo);
                            Form1.Validate(chkMay, dataGridView, "May", 4, cbo);
                            Form1.Validate(chkJun, dataGridView, "June", 5, cbo);
                            Form1.Validate(chkJul, dataGridView, "July", 6, cbo);
                            Form1.Validate(chkAug, dataGridView, "August", 7, cbo);
                            Form1.Validate(chkSep, dataGridView, "September", 8, cbo);
                            Form1.Validate(chkOct, dataGridView, "October", 9, cbo);
                            Form1.Validate(chkNov, dataGridView, "November", 10, cbo);
                            Form1.Validate(chkDec, dataGridView, "December", 11, cbo);
                        };
Shan Coralde
  • 214
  • 2
  • 15
  • That looks like the code that should do what you want. What is happening or not happening instead? – Christopher Feb 08 '18 at 00:39
  • The problem is the code _already_ does what I want but what if the Excel file has a different format but also contains months January to December. Say, January Column Index starts at 1, then it will only validate months April onwards. – Shan Coralde Feb 08 '18 at 00:49
  • 1
    A simple array that tells you wich month is in wich row should do it. You can build that array based on the Column names everytime you read in a new Excel file.Here is a decent example of this issues: https://stackoverflow.com/questions/258793/how-to-parse-a-month-name-string-to-an-integer-for-comparison-in-c – Christopher Feb 08 '18 at 00:52
  • Care to explain it in the answers? – Shan Coralde Feb 08 '18 at 01:36
  • 1
    If your grids data source is a `DataTable`, and one if the column names is “January”… you should be able to reference that column with its name. Example: `dataGridView1.Rows[YourRowIndex].Cells["January"].Value.ToString()`… I can only assume the columns in the Excel file have a column header name, otherwise how would you know which month it was? Using the above example will allow the “January” to be in any column number, however the code above will crash if it doesn’t exist. – JohnG Feb 08 '18 at 01:36
  • @JohnG, Yes, it does have a header name. – Shan Coralde Feb 08 '18 at 01:55
  • 1
    Sorry for my poorly worded comment. You should be able to reference the column as shown: `dataGridView1.Rows[YourRowIndex].Cells["January"].Value.ToString()`. This way the “January” column can be anywhere. You just need to make sure it exists before you try to reference it. What if the Excel file does not have a “January” column?... Better to display a message to the user than blow up. – JohnG Feb 08 '18 at 02:00
  • Thanks as always, @JohnG. I can access them all now regardless of column index value. – Shan Coralde Feb 08 '18 at 08:42

1 Answers1

1

What you need is a way to map each Column to a specific month and vice versa. Once you figure that out, you just need to use the map during processing.

Asuming the month is never mispelled, make a simple 12 element array. The index corresponds to wich "Month of the Year" you are looking at. The value is the Column that month can be found in.

Then just itterate over the headers. each time you find a month, write down wich column that month corresponds too.

For your "January is the 4th row" example, the array would look like this: 

{4, 5, 6, 7, ... }

If the Januray is the 1st row, it would look like:

{1, 2, 3, 4, 5,...}

If the order of months is inverted (December to Janurary) with December in the first row, it would look like this:

 { 12, 11, 10, 9, ... }

When doing the actually processing you use the Mapping Array and the Column ID's you extracted.

If you need to support slight mispellings and internationalized spelling, you will have to include a proper Parse function instead of just doing a string comparision to figure out wich column is wich month.

Christopher
  • 9,634
  • 2
  • 17
  • 31