Excel OleConnection Insert Statements

Question

How would insert a row into an excel document. This is what I have.

String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
    "Data Source=" + file_path + ";Extended Properties=Excel 8.0;";

        DataTable dt = new DataTable();

        DataSet ds = new DataSet();
        OleDbConnection conn = new OleDbConnection(connString);

        OleDbCommand cmd = new OleDbCommand(str_insert, conn);

        cmd.CommandTimeout = 3600;
        try
        {

            cmd.Connection = conn;
            DataSet active_ds = new DataSet();
            cmd.CommandText = str_insert;
            if (conn.State != ConnectionState.Open)
                conn.Open();

            foreach (DataRow dr in details_dt.Rows)
            {
                cmd.CommandText = "INSERT INTO [DETAILS$](Ipt, Ipt Ownr, Order, Order Delivery Date, Assembly, Description, Component, User, Name, Change Code, Reason Code, Value New, Value Old,"
                + "Date, Time, Auth Doc, Plant, Mfg Source, Order Type, Model, Effy) " + "VALUES(" + dr.ItemArray[0].ToString() + "," + dr.ItemArray[1].ToString() + "," + dr.ItemArray[2].ToString() + "," + dr.ItemArray[3].ToString() + "," +
                dr.ItemArray[4].ToString() + "," + dr.ItemArray[5].ToString() + "," + dr.ItemArray[6].ToString() + "," + dr.ItemArray[7].ToString() + "," + dr.ItemArray[8].ToString() + "," + dr.ItemArray[9].ToString() + "," + dr.ItemArray[10].ToString()
                + "," + dr.ItemArray[11].ToString() + "," + dr.ItemArray[12].ToString() + "," + dr.ItemArray[13].ToString() + "," + dr.ItemArray[14].ToString() + "," + dr.ItemArray[15].ToString() + "," + dr.ItemArray[16].ToString() + "," + dr.ItemArray[17].ToString()
                + "," + dr.ItemArray[18].ToString() + "," + dr.ItemArray[19].ToString() + "," + dr.ItemArray[20].ToString() + "," + dr.ItemArray[21].ToString() + ")";
                    cmd.ExecuteNonQuery();

            }

What version of Excel? Are you getting errors? Are you open the connection fine? — Gratzy, Sep 24 '09 at 14:15
Or are you just re-asking this question http://stackoverflow.com/questions/1468076/c-accessing-excel-worksheet/1468153#1468153 — Gratzy, Sep 24 '09 at 14:17

score 0 · Accepted Answer · answered Oct 17 '09 at 13:59

It was strange but there is a difference in the excel files that I was able to access. I was able to access the .xls but not the .xlsx files. I was generating an excel file that was in XML and unable to access this with OleDB so I Simply changed the file format and it worked.

score 0 · Answer 2 · answered Oct 17 '09 at 16:12

This connection string will allow you to access Excel 2007 .xlsx files...

OleDbConnection xlconnection = new OleDbConnection();

xlconnection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + @";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";

Excel OleConnection Insert Statements

2 Answers2