I have the following function which gets called by clicking a button:

private void SelectExcelFile()
    {
        string fileName;
        System.Data.DataTable dtExcel = new System.Data.DataTable();
        OpenFileDialog dialogBox1 = new OpenFileDialog();
        {
            dialogBox1.Title = "Please choose an Excel file.";
            dialogBox1.DefaultExt = ".xlsx";
            dialogBox1.Filter = "Excel files (.xlsx)|*.xlsx";
        }

        if (!dtExcel.Columns.Contains("stat"))
        {
            DialogResult dialogResult = MessageBox.Show("The selected file is not valid. Please select a new file.", "Warning", MessageBoxButtons.OK);
        }

        if (dialogBox1.ShowDialog() == DialogResult.OK)
        {
            fileName = dialogBox1.FileName;
            string sourceConstr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="   fileName   ";Extended Properties='Excel 12.0 Xml;HDR=YES' ";
            textBox1.Text = dialogBox1.FileName;
            OleDbConnection con = new OleDbConnection(sourceConstr);
            string query = "Select * from [Table1$]";
            OleDbDataAdapter data = new OleDbDataAdapter(query, con);
            data.Fill(dtExcel);
            listView1.Columns.Add("", 30, HorizontalAlignment.Left);
            listView1.Columns.Add("Index", 100, HorizontalAlignment.Left);
            listView1.Columns.Add("Name", 200, HorizontalAlignment.Left);
            listView1.Columns.Add("Description", 300, HorizontalAlignment.Left);
            listView1.Items.Clear();
            for (int i = 0; i < dtExcel.Rows.Count; i  )
            {
                DataRow drow = dtExcel.Rows[i];

                if (drow.RowState != DataRowState.Deleted)
                {
                    ListViewItem lvi = new ListViewItem();
                    {
                        if (drow["stat"].ToString() == "OK")
                            lvi.Checked = true;
                        else
                            lvi.Checked = false;

                        lvi.SubItems.Add(drow["Index"].ToString());
                        lvi.SubItems.Add(drow["Name"].ToString());
                        lvi.SubItems.Add(drow["Description"].ToString());

                        listView1.Items.Add(lvi);
                    }
                }
            }
            toolStripStatusLabel1.Text = "File loaded";
        }
        else
        {
            return;
        }
    }

What I want to do is to make it show a warning message every time the Excel file doesn't contain a column named "stat" so that you could choose another one. In this version however, it shows the message immediately after clicking the button and if the selected file doesn't contain "stat" the program keeps running further so that I get a 'Column 'stat' does not belong to table .' exception.

How can I fix my code so that it runs properly? Any help is appreciated.

CodePudding user response：

between

System.Data.DataTable dtExcel = new System.Data.DataTable();

and

if (!dtExcel.Columns.Contains("stat"))
{
  DialogResult dialogResult = MessageBox.Show("The selected file is not valid. Please select a new file.", "Warning", MessageBoxButtons.OK);
}

You have not added anything to dtExcel there fore it is empty and you will get the error.

Then you continue to process your code with checking dialog result and since you have selected a file it will return DialogResult.OK no matter what.

Logic is like that-> Open FileDialog->if the file is selected return DialogResult.OK else retrun nothing.

File is selected; fill your datatable with excel values (data.Fille(dtExcel)) and then check if your dtExcel has "stat" column

So I would to do like that;

private void SelectExcelFile() {
  string fileName;
  System.Data.DataTable dtExcel = new System.Data.DataTable();
  OpenFileDialog dialogBox1 = new OpenFileDialog(); {
    dialogBox1.Title = "Please choose an Excel file.";
    dialogBox1.DefaultExt = ".xlsx";
    dialogBox1.Filter = "Excel files (.xlsx)|*.xlsx";
    if (dialogBox1.ShowDialog() == DialogResult.OK) {
      fileName = dialogBox1.FileName;
      string sourceConstr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="   fileName   ";Extended Properties='Excel 12.0 Xml;HDR=YES' ";
      textBox1.Text = dialogBox1.FileName;
      OleDbConnection con = new OleDbConnection(sourceConstr);
      string query = "Select * from [Table1$]";
      OleDbDataAdapter data = new OleDbDataAdapter(query, con);
      data.Fill(dtExcel);

      if (!dtExcel.Columns.Contains("stat")) {
        DialogResult dialogResult = MessageBox.Show("The selected file is not valid. Please select a new file.", "Warning", MessageBoxButtons.OK);
      } else {
        listView1.Columns.Add("", 30, HorizontalAlignment.Left);
        listView1.Columns.Add("Index", 100, HorizontalAlignment.Left);
        listView1.Columns.Add("Name", 200, HorizontalAlignment.Left);
        listView1.Columns.Add("Description", 300, HorizontalAlignment.Left);
        listView1.Items.Clear();
        for (int i = 0; i < dtExcel.Rows.Count; i  ) {
          DataRow drow = dtExcel.Rows[i];

          if (drow.RowState != DataRowState.Deleted) {
            ListViewItem lvi = new ListViewItem(); {
              if (drow["stat"].ToString() == "OK")
                lvi.Checked = true;
              else
                lvi.Checked = false;

              lvi.SubItems.Add(drow["Index"].ToString());
              lvi.SubItems.Add(drow["Name"].ToString());
              lvi.SubItems.Add(drow["Description"].ToString());

              listView1.Items.Add(lvi);
            }
          }
        }
        toolStripStatusLabel1.Text = "File loaded";
      }

    } else 
    {
      return;
    }
  }
}