Click here to Skip to main content
15,845,830 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
in here there is problem coming in connection string because i am not using microsoft excel.listbox is coming out as empty .i dont know what is happening there and the connection string made is
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\FFT-VS\Downloads\BKI13122023.xlsx;Extended Properties="Excel 12.0;HDR=NO;IMEX=1"


What I have tried:

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();

           }


       }
Posted

See Working with MS Excel(xls / xlsx) Using MDAC and Oledb[^] for details on how to import Excel worksheets into a DataGridView.
 
Share this answer
 
Comments
Maciej Los 10-Nov-23 17:19pm    
5ed!
First of all, why do you use IMEX=1? This causes Excel to treat all data as a text.
For further details, please read this: Treating data as text - Microsoft.ACE.OLEDB.12.0 - ConnectionStrings.com[^]

Second of all, why do you try to get some portion of data, instead of all (at once)? And... Are you sure that sheet name or named range is written properly?
C#
string str = "select Top " + previewrow + " * from [" + cmb1string + "]";

Take a look at examples:
SQL
SELECT [Column Name One], [Column Name Two] FROM [Sheet One$]

As you can see, excel worksheet name is followed by a "$" and wrapped in "[" "]" brackets.
If you want to start reading data from cell A3 to D5000, use:
SQL
SELECT * FROM [Sheet1$A3:D5000]


Finally, i'd suggest to write DAL[^]. This object should help you to create connection, process CRUD[^] operations and close connection.
 
Share this answer
 
Comments
Richard MacCutchan 11-Nov-23 3:35am    
+5; far more detail than my post.
Maciej Los 13-Nov-23 13:50pm    
Thank you, Richard.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900