none
How do I write to specific cells in Excel? ... C#, OleDb RRS feed

  • Question

  • How do I write to specific cells in Excel?

    This works for writing to cells that have the titles "id" and "name" in the first row.

    string stSheetName = "Sheet1";
    string sql = "Insert into [" + stSheetName + "$] (id,name) values('5','e')";
    myCommand.CommandText = sql;
    myCommand.ExecuteNonQuery();

    This does not work:

    string sql = "Insert into [" + stSheetName + "$A2:B2] SET A2 = '5', B2 = 'e'";


    bhs67

    Saturday, March 28, 2015 1:52 PM

Answers

  • This works for inserting a row without column headers:

        oledbCmd.CommandText = "SELECT * FROM [" + stSheetName + "$]";
        DataTable dtDataTable = new DataTable();
        using (OleDbDataAdapter oledbAdapter = new OleDbDataAdapter(oledbCmd))
            oledbAdapter.Fill(dtDataTable);

        string stRowNumber = (dtDataTable.Rows.Count + 1).ToString();
        string stRowID = "A" + stRowNumber + ":B" + stRowNumber;
        oledbCmd.CommandText = "UPDATE [" + stSheetName + "$" + stRowID + "] SET F1=3, F2='z'";
        oledbCmd.ExecuteNonQuery();

    The dtDataTable fills with the Worksheet contents ... the number of rows plus one indicates the next row to add using UPDATE.


    bhs67

    Wednesday, April 1, 2015 9:57 PM

All replies

  • If you are trying to use sql to insert values in a sheey, I will not recommand this method. 

    1st. while using insert, it will insert values for the columns, if you are trying to insert values for some special cells, you shall use "update"


    Sunday, March 29, 2015 4:27 PM
  • This does not work:

    string sql = "Insert into [" + stSheetName + "$A2:B2] SET A2 = '5', B2 = 'e'";

    Hi bhs67,

    You have syntactic error in this SQL statement. You need to change "Insert into" to "Update", also you can't use the cell address as the parameter, use field number(e.g. F1,F2) instead. The correct SQL statement should be like this:

    string sql = "Update [" + stSheetName + "$A2:B2] SET F1 = '5', F2 = 'e'";

    To update a specified row:

    UPDATE [Sheet1$] SET F2 = ? WHERE F1 = ?


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    • Edited by CaillenModerator Monday, March 30, 2015 8:25 AM
    • Proposed as answer by ryguy72 Wednesday, April 1, 2015 9:32 PM
    Monday, March 30, 2015 8:20 AM
    Moderator
  • This works for inserting a row without column headers:

        oledbCmd.CommandText = "SELECT * FROM [" + stSheetName + "$]";
        DataTable dtDataTable = new DataTable();
        using (OleDbDataAdapter oledbAdapter = new OleDbDataAdapter(oledbCmd))
            oledbAdapter.Fill(dtDataTable);

        string stRowNumber = (dtDataTable.Rows.Count + 1).ToString();
        string stRowID = "A" + stRowNumber + ":B" + stRowNumber;
        oledbCmd.CommandText = "UPDATE [" + stSheetName + "$" + stRowID + "] SET F1=3, F2='z'";
        oledbCmd.ExecuteNonQuery();

    The dtDataTable fills with the Worksheet contents ... the number of rows plus one indicates the next row to add using UPDATE.


    bhs67

    Wednesday, April 1, 2015 9:57 PM