CODEWITHSUNDEEP
c#datagridview
Marios Epiphaniou
Marios Epiphaniou

Reputation: 23

Read excel file into datagridview c#

i am trying to read a specific excel file into my datagridview named as stats_table but most of the times the entire system crashes or is not doing anything at all. The code i am using under the specific button to import is the following :

private void Predict_Click(object sender, EventArgs e)
{
    string path = @"C:\Users\epifa\Desktop\Master\2nd semester\esports\all_cards.xlsx";
    string constr = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = "+ path + " Extended Properties=\"Excel 12.0 Macro; HDR = YES";
    OleDbConnection con = new OleDbConnection(constr);
    DataTable dt = new DataTable();
    Stats_table.DataSource = dt;
}

can you please advise?

Upvotes: 1

Views: 2291

Answers (3)

Marios Epiphaniou
Marios Epiphaniou

Reputation: 23

after many trials i finally got it,

OleDbConnection conn = new OleDbConnection();
conn.ConnectionString = @"Provider = Microsoft.ACE.OLEDB.12.0; Data Source =  C:\Users\epifa\Desktop\Master\2nd semester\esports\all_cards.xlsx" + @"; Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1;ImportMixedTypes=Text;TypeGuessRows=0""";
OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", conn);
DataSet stats = new DataSet();
OleDbDataAdapter adapter = new OleDbDataAdapter(command);
adapter.Fill(stats);
Stats_table.DataSource = stats.Tables[0];

thank you anyway :)

Upvotes: 1

JohnG
JohnG

Reputation: 9479

As you commented… "and then since i know that the connection is working" … this is debatable. However, assuming you have a valid connection… the code does not appear to do anything “with” that connection…Example, on the line…

OleDbConnection con = new OleDbConnection(constr);

This line of code may well succeed, but you are not doing ANYTHING with the connection…, like “filling” the data table with data from the excel workbook.

After this line of code, a NEW EMPTY DataTable is created and then this “empty” table is set as a DataSource to the grid. I am not sure what else you would expect other than an “empty” grid.

Lastly, it appears the connection string is missing some syntax and will probably fail because of this. Below are two examples that will open XLS and XLSX files. Bear in mind you need to know the name of the worksheet you want to fill the table with.

To open an XLSX file with a worksheet named “sheet1”…

private void btn_OpenXLSX_Click(object sender, EventArgs e) {
  string path = @"path_to_the_XLSX_file";
  string worksheetName = "sheet1";
  string constr = "Provider = Microsoft.Ace.OLEDB.12.0; Data Source = " + path + "; Extended Properties=\"Excel 12.0; HDR = YES;Imex=1;\";";
  OleDbConnection con = new OleDbConnection(constr);
  OleDbDataAdapter myDataAdapter = new OleDbDataAdapter("Select * from [" + worksheetName + "$]", con);
  DataTable dt = new DataTable();
  myDataAdapter.Fill(dt);
  Stats_table.DataSource = dt;
}

To open an XLS file with a worksheet named “sheet1”…

private void btn_OpenXLS_Click(object sender, EventArgs e) {
  string path = @"path_to_the_XLS_file";
  string worksheetName = "sheet1";
  string constr = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + path + "; Extended Properties=\"Excel 8.0; HDR = YES;Imex=1;\";";
  OleDbConnection con = new OleDbConnection(constr);
  OleDbDataAdapter myDataAdapter = new OleDbDataAdapter("Select * from [" + worksheetName + "$]", con);
  DataTable dt = new DataTable();
  myDataAdapter.Fill(dt);
  Stats_table.DataSource = dt;
}

I hope that makes sense and helps.

Upvotes: 0

Daniel
Daniel

Reputation: 9849

you need to find out what is going on.

Obviously a Exception is happening.

You can catch the exception to know what is going on like:

private void Predict_Click(object sender, EventArgs e)
{
    try 
    {
        string path = @"C:\Users\epifa\Desktop\Master\2nd semester\esports\all_cards.xlsx";
        string constr = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = "+ path + " Extended Properties=\"Excel 12.0 Macro; HDR = YES";
        OleDbConnection con = new OleDbConnection(constr);
        DataTable dt = new DataTable();
        Stats_table.DataSource = dt;
    } catch (Exception e) {
        Console.WriteLine(e.Message);
    }
}

After you know what the problem is you can go on fixing it.

Upvotes: 0

Related Questions