Import Excel File into datagridview

Question

I have the following code which will import my excel file. But for some reason it will not show all of the data from the orginal spread sheet.

Here is the original spreadsheet:

Here is the output:

For some reason some of the columns under the benefit period months get lost or do not show up.

 private void btnLoadExcel_Click(object sender, EventArgs e){ string pathConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtPath.Text + ";Extended Properties=\"Excel 8.0;HDR=Yes;\";"; OleDbConnection conn = new OleDbConnection(pathConn); OleDbDataAdapter myDataAdapter = new OleDbDataAdapter("Select * from [" + txtSheet.Text+ "$]",conn); conn.Open(); DataTable dt = new DataTable(); myDataAdapter.Fill(dt); dataGridView1.AutoResizeColumns(); dataGridView1.DataSource = dt; for (int i = 0; i < dataGridView1.Columns.Count - 1; i++) { dataGridView1.Columns[i].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; } dataGridView1.Columns[dataGridView1.Columns.Count - 1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; for (int i = 0; i < dataGridView1.Columns.Count; i++) { int colw = dataGridView1.Columns[i].Width; dataGridView1.Columns[i].AutoSizeMode = DataGridViewAutoSizeColumnMode.None; dataGridView1.Columns[i].Width = colw; } }

JohnG · Accepted Answer · 2017-09-26 23:19:14Z

Copying your code produced the same results you explained. The only change I made was to the pathConn string by adding the parameter IMEX=1 which will set the column types as strings. I am guessing that the columns may have different “types” for different rows in the excel file.

string pathConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtPath.Text + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1;\";";

Hope this helps.

Collectives™ on Stack Overflow

Import Excel File into datagridview

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related