How to import excel sheet data to datagridview using windows form in C#

Question

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 8-Nov-23 23:29pm

VINEETH5 JUN2023

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard MacCutchan · Answer 1 · 2023-11-09T03:21:00

Solution 1

See Working with MS Excel(xls / xlsx) Using MDAC and Oledb[^] for details on how to import Excel worksheets into a DataGridView.

Posted 9-Nov-23 3:21am

Richard MacCutchan

Comments

Maciej Los 10-Nov-23 17:19pm

5ed!

Maciej Los · Answer 2 · 2023-11-10T11:40:00

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.