【解決方法】C#でWindowsフォームを使用してExcelシートデータをdatagridviewにインポートする方法

ここでは、Microsoft Excelを使用していないため、接続文字列に問題が発生します。リストボックスが空として表示されます。そこで何が起こっているのかわかりませんが、作成された接続文字列は次のとおりです。

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\FFT-VS\Downloads\BKI13122023.xlsx;Extended Properties="Excel 12.0;HDR=NO;IMEX=1"

私が試したこと:

private void btn_ImpData_Click(object sender, EventArgs e)
       {
           openFileDialog1.Filter = "Excel files (*.xls*, *.xlsx)|*.xls*; *.xlsx";
           openFileDialog1.ShowDialog();

       }

 public void ImportFromExcel(string filename, DataGridView rgvGrid)
        {
            string sConnectionString = "";
            string[] Arr = null;
            Arr = filename.Split('.');
            string _OriginalType = "", _Type = "";
            try
            {
                _OriginalType = checkfiletype(filename);
                _Type = checkfileext(filename);

                sConnectionString = connectionstringwithoutheader(_Type, filename, filename, filename);

                if (_OriginalType == "html")
                {
                    htmlformatrendering_tempfile(filename, rgvGrid);
                    htmlformatrendering_base_Preview(Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData) + "\\IIMS\\data\\temp.html", rgvGrid, 1000);
                }
                else if (_OriginalType == ".xls" || _OriginalType == ".xlsx")
                {

                    lst_Sheets.Items.Clear();
                    filllistbox(lst_Sheets, sConnectionString);
                    fillGrid_reader_preview(lst_Sheets.SelectedItem.ToString().Trim(), rgvGrid, 1000, sConnectionString);
                }

                //end
                //sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
                //                   filename + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
                //OleDbConnection cn1 = new OleDbConnection(sConnectionString);
                //cn1.Open();

            }
            catch (Exception ex)
            {
                ////sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties='Excel 12.0 Xml;HDR=YES';";
            }
}

public void filllistbox(ListBox lst1, string connectionstring)
      {
          try
          {
              lst1.Items.Clear();


              conn = new OleDbConnection(connectionstring);
              if (conn.State == ConnectionState.Closed)
              {
                  conn.Open();
              }
              string[] restrictionForTable = new string[] { null, null, null, "TABLE" };
              DataTable dataTable1 = conn.GetSchema("TABLES", restrictionForTable);

              //counting number of rows in numberOfRows
              int numberOfRows = dataTable1.Rows.Count;
              object[] obj = new object[99];

              // adding number of tables in combobox
              for (int i = 0; i < numberOfRows; i++)
              {
                  obj = dataTable1.Rows[i].ItemArray;
                  lst1.Items.Add((string)obj[2]);
              }

              string str = (string)lst1.SelectedItem;
              lst1.SelectedIndex = 0;
              // cmb1.Text = (string)cmb1.SelectedItem;
          }
          catch (System.NullReferenceException)
          {
              //tmp1.logfileentry("Error Code :filllistbox: FFC-0053N - " + "Null Exception");

          }
          catch (Exception ex1)
          {
              //tmp1.logfileentry("Error Code :filllistbox: FFC-0053E - " + ex1.ToString());

          }
          finally
          {
              conn.Close();
          }

      }

public void fillGrid_reader_preview(string cmb1string, DataGridView dgv1, int previewrow, string connectionstring)
       {
           OleDbCommand command1 = new OleDbCommand();
           OleDbDataReader reader = null;
           try
           {
               dgv1.Columns.Clear();
               dgv1.Rows.Clear();
               dgv1.DataSource = null;
               conn = new OleDbConnection(connectionstring);
               if (conn.State == ConnectionState.Closed)
               {
                   conn.Open();
               }

               string str = "select Top " + previewrow + " * from [" + cmb1string + "]";
               command1.CommandText = str;

               if (conn.State == ConnectionState.Closed)
               {
                   conn.Open();
               }

               command1.Connection = conn;


               reader = command1.ExecuteReader(CommandBehavior.CloseConnection);


               for (int i = 0; i < reader.FieldCount; i++)
               {
                   dgv1.Columns.Add(Convert.ToString(i + 1), Convert.ToString(i + 1));
               }

               // adding number of tables in combobox
               int row1 = 0;

               while (reader.Read())
               {
                   if (dgv1.Rows.Count < previewrow)
                   {
                       dgv1.Rows.Add();

                       for (int is12 = 0; is12 < reader.FieldCount; is12++)
                       {

                           dgv1[is12, row1].Value = reader[is12].ToString();
                       }
                       row1++;
                   }
                   else
                   {
                       break;
                   }

               }


           }
           catch (System.NullReferenceException)
           {
               //tmp1.logfileentry("Error Code :fillGrid_reader_preview: FFC-0047N - " + "Null Exception");

           }
           catch (Exception ex1)
           {
               //tmp1.logfileentry("Error Code :fillGrid_reader_preview: FFC-0047E - " + ex1.ToString());


           }
           finally
           {
               reader.Dispose();
               command1.Dispose();
               conn.Close();

           }


       }