Hi so I've only just began creating a exam scheduling system and I have an issue when inserting a Excel table into my data grid view. I've created some rows and columns for time however when i insert new data it pads with empty cells and inserts below instead of going into the existing ones. Any help is appreciated.
string PathConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtPath.Text + ";Extended Properties=\"Excel 8.0; HDR = Yes;\";";
OleDbConnection MyConnection = new OleDbConnection(PathConn);
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", MyConnection);
DataTable dt = new DataTable();
dt.Columns.Add("Time");
DataRow dr = dt.NewRow();
dr["Time"] = "9:00";
dt.Rows.Add(dr);
DataRow dr1 = dt.NewRow();
dr1["Time"] = "10:00";
dt.Rows.Add(dr1);
`etc`
myDataAdapter.Fill(dt);
DataGridView1.DataSource = dt;
It is unclear what you are trying to achieve. In reference to the posted code, you are correct that the table will have “empty” cells for the top two rows except for the first column with the “9:00 and 10:00” values. In addition, there will be “empty” cells in the first column after the first two rows. This is because you are adding the columns AND rows for the “time” values “BEFORE” you are filling it with the data.
I recommend you ADD the data FIRST, then add the “time” column. Then instead of “adding” new rows for the time values… use the “existing” rows that are there from when you read the original data.
Example: first fill the table with the data… myDataAdapter.Fill(dt);… then add the “time” column as the first column in the existing table. It may look something like…
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", MyConnection);
DataTable dt = new DataTable();
myDataAdapter.Fill(dt);
DataColumn timeCol = new DataColumn("Time", typeof(string));
dt.Columns.Add(timeCol);
timeCol.SetOrdinal(0);
dt.Rows[0].SetField("Time", "9:00");
dt.Rows[1].SetField("Time", "10:00");
dataGridView1.DataSource = dt;