Inserting the data in excel using oledb in C#

Question

0.00/5 (No votes)

See more:

Already I am having a lot of data in Excel. I am trying to insert new data in that Excel, but it's not inserting.
I am having 20 columns, but I am inserting a new row (few columns only contains data) in Excel.

What I have tried:

  using (OleDbConnection objConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath1 + ";Extended Properties='Excel 12.0;HDR=YES;READONLY=FALSE';"))
                {
                    objConn.Open();
                    int totalSheet = 0;
                    DataTable dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    string sheetName = string.Empty;
                    if (dt != null)
                    {
                        var tempDataTable = (from dataRow in dt.AsEnumerable()
                                             where !dataRow["TABLE_NAME"].ToString().Contains("FilterDatabase")
                                             select dataRow).CopyToDataTable();
                        dt = tempDataTable;
                        totalSheet = dt.Rows.Count;
                        sheetName = dt.Rows[0]["TABLE_NAME"].ToString();
                    }

                    OleDbCommand cmd = new OleDbCommand();
                    cmd.Connection = objConn;
                    cmd.CommandType = CommandType.Text;
                    string SqlCommand = "INSERT INTO [" + sheetName + "] (F2,F3) VALUES(3036,'Sweet')";
cmd.CommandText = SqlCommand;
                    cmd.ExecuteNonQuery();
}

Posted 24-Jan-21 20:09pm

Prasath Paramaraj

Updated 13-Jul-23 8:14am

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2021-01-24T20:21:00

Solution 1

Pretty much, you can't - Excel OleDb doesn't support the ALTER TABLE command, and that's how you would have to insert a column.

The SQL INSERT command doesn't deal with columns at all, it will ONLY insert a new row, never a column.

To insert a column, you pretty much need to generate a new Excel file and populate it with your existing and inserted data.

If what you are trying to do is INSERT a new row but only provide data for 2 out of 20 columns, then you need to explain in better detail exactly what happens when you try and how you know it has failed. What does the debugger show you is going on? What does the file look like before and after your app has run?

Posted 24-Jan-21 20:21pm

OriginalGriff

Comments

Prasath Paramaraj 25-Jan-21 2:24am

I am inserting new row only.it's executing successfully, but no rows inserted after opening the Excel sheet.

OriginalGriff 25-Jan-21 2:32am

How do you know it's executing successfully?
How do you know the new row isn't inserted?

I'm not trying to be difficult here, but we can't see what you don't type: so I can't see if there is a try...catch block round that code swallowing an exception, I can't see what the file path is, I can't find out what the call to ExecuteNonQuery is returning, I can't see what file you are opening to check, or how you are checking it.

I'd start with the debugger to make sure that code executes completely, and find out what the query returns, then use the file path content to access the file directly and check it's timestamps, then use Excel to open the file immediately before the code did, take a close look, close it down, and do it again after the code ran.

But I can't tell what you have done, because you aren't telling us any of that! :laugh:

Prasath Paramaraj 25-Jan-21 2:47am

I added try catch block also. ExecuteNonQuery its return 1 value. After my code fully executed only I opened the file, and I am checking the Excel. its not inserted any value. Folder and file are able to access. I tried to update its updating correctly.

using (OleDbConnection objConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath1 + ";Extended Properties='Excel 12.0;HDR=YES;READONLY=FALSE';"))
                {
                    objConn.Open();
                    int totalSheet = 0;
                    DataTable dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    string sheetName = string.Empty;
                    if (dt != null)
                    {
                        var tempDataTable = (from dataRow in dt.AsEnumerable()
                                             where !dataRow["TABLE_NAME"].ToString().Contains("FilterDatabase")
                                             select dataRow).CopyToDataTable();
                        dt = tempDataTable;
                        totalSheet = dt.Rows.Count;
                        sheetName = dt.Rows[0]["TABLE_NAME"].ToString();
                    }

                    OleDbCommand cmd = new OleDbCommand();
                    cmd.Connection = objConn;
                    cmd.CommandType = CommandType.Text;
                    string SqlCommand = "INSERT INTO [" + sheetName + "] (F2,F3) VALUES(3036,'Sweet')";
cmd.CommandText = SqlCommand;
                  var result=  cmd.ExecuteNonQuery();
}

OriginalGriff 25-Jan-21 3:02am

If it's returning "one row affected" and no exceptions, then you need to look very, very closely at the path in your code using the debugger and the target file before and after the ExecuteNonQuery - are it's timestamps getting updated? Where in the file did you look for data?
The return code says something is getting added to something - but we have no idea what to where, and that's the first thing you need to establish.

Sorry, but we can't do anything to help you since we can't access your file system at all.

And you do realize that Excel is not a database? :laugh:

Prasath Paramaraj 25-Jan-21 5:00am

how to attach my Excel sheet here ??