How can I write to multiple specific cells in Excel with C#, OleDb

Question

How can I write to multiple specific cells in Excel?

I am using below query but getting the error.

Insert into [Stream data (EB)$ E4:E5:E6:E7:E8:E9:E10:E11:E12] VALUES ( '316.053233674335','52.3573941952819','7101.3066268303','57.0126104422724','0.0191207063146601','3443.063240521','526.095739836848','0.0971761602066454','2.74351548746162')

Error: ''Stream data [EB]' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.'

I have also tried with different sheet name.

Insert into [Streamdata$E4:E5:E6:E7:E8:E9:E10:E11:E12] VALUES ( '316.053233674335','52.3573941952819','7101.3066268303','57.0126104422724','0.0191207063146601','3443.063240521','526.095739836848','0.0971761602066454','2.74351548746162')

but getting error in this case too. Error: The Microsoft Access database engine could not find the object 'Streamdata$E4:E5:E6:E7:E8:E9:E10:E11:E12'. Make sure the object exists and that you spell its name and the path name correctly. If 'Streamdata$E4:E5:E6:E7:E8:E9:E10:E11:E12' is not a local object, check your network connection or contact the server administrator.'

Answer 1

@Nitin Sharma [NTSA], Welcome to Microsoft Q&A, based on my test, I reproduced your problem. According to my research, I find that Oledb only support rows inserting instead of columns inserting. Here is a code example you could refer to.

string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
+ path + ";Extended Properties=\"Excel 12.0 Xml; HDR=NO\";";
            OleDbConnection conn = new OleDbConnection(connectionString);
            string stSheetName = "Sheet1";
            conn.Open();
            string cmd = " insert into [" + stSheetName + "$" + "](F1,F2,F3,F4,F5,F6) values('3.1645','3.5566','4.331','3.1645','3.5566','4.331')";
            OleDbCommand ole=new OleDbCommand(cmd, conn);

            ole.ExecuteNonQuery();
            conn.Close();

Note: we need to ensure that the first row of column has the data otherwise the code could not detect the columns F1,F2 etc.. Tested result:

Update for adding value in column:

string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
+ path + ";Extended Properties=\"Excel 12.0 Xml; HDR=NO\";";
            OleDbConnection conn = new OleDbConnection(connectionString);
            string stSheetName = "Sheet1";
            conn.Open();
            string[] arr = { "test1", "test2", "test3", "test4" };

            OleDbCommand ole = new OleDbCommand() ;
            ole.Connection=conn;
            foreach (var item in arr)
            {
              
                string cmd = string.Format("insert into [{0}$](F3) values('{1}')", stSheetName, item);
                ole.CommandText = cmd;
                ole.ExecuteNonQuery();

            }

            conn.Close();

Tested result:

Hope my code could help you. Best Regards, Jack If the answer is helpful, please click "Accept Answer" and upvote it. Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.  

Answer 2

Hi Nitin,

As per my understanding of your query, I've written a VBA code that might work. So maybe try it once and let me know if it works.

Sub WriteToCells()
    'Define the worksheet and cells to write to
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Dim cellE4 As Range
    Set cellE4 = ws.Range("E4")
    Dim cellE5 As Range
    Set cellE5 = ws.Range("E5")
    
    'Write values to cells
    cellE4.Value = 316.053233674335
    cellE5.Value = 52.3573941952819
    
    'Repeat for other cells as needed
    
End Sub

You can modify and make changes to this code to write values to the specific cells you need. Just update the worksheet name and cell ranges accordingly to match your data, and add your additional lines of code for each cell you want to write to.

I hope this works for you. I'd be really glad if you can "Accept Answer".

Best Regards.