Insert data in Excel database

Question

Hi all,

I am developing an application which uses Excel file as database. I have 3 on dimensional arrays as Name,Age,Address and a set of Excel files in a folder. All the excel files have three columns as Name(Column A),Age(Column B),Address(Column C), I have not defined names in excel files. I want to open all the excel files one by one and insert the Name,Age,Address in each file respectively. Below is the Code i am using to insert data in one single file but it doesnot work.

        private void button1_Click(object sender, EventArgs e)
        {
            
            OleDbCommand com = new OleDbCommand();
            OleDbDataAdapter excelDataAdapter = new OleDbDataAdapter();
            string excelConnStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=D:\\Jimmits data\\C# Test\\New Microsoft Office Excel Worksheet.xlsx; Extended Properties =Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(excelConnStr);
            for (int i = 1; i <= 10; i++)
            {
                com.CommandText = "Insert into [Sheet1$] (A2,B2,C2) values('" + textBox1.Text + "','" + textBox1.Text + "','" + textBox1.Text + "')";
                //com.CommandText = "Update [Sheet1$] set Name='"+textBox1.Text+"'where Name=''";
                com.Connection = conn;
                conn.Open();
                com.ExecuteNonQuery();
                conn.Close();
            }
            MessageBox.Show("Records inserted sucessfully.");
        }

Kindly, help me to make this possible.

Thanks,

Jimmit Raval

---

Jimmit Raval

Answer 1

Jimmit,

How you insert the data without  putting the column name into excel file , I suggest use excel introp, below link may help you.

http://support.microsoft.com/kb/306023

http://www.clear-lines.com/blog/post/Write-data-to-an-Excel-worksheet-with-C-fast.aspx

Answer 2

Since you are using ADO.NET to insert values into excel sheet, compiler assumes that A2, B2 and C2 columns are already present in the excel sheet. If they are not present, your code will not work. So, may be you can insert the columns first and then insert the values like below,

OleDbDataAdapter excelDataAdapter = new OleDbDataAdapter();
string excelConnStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=c:\\Temp\\dummy.xlsx; Extended Properties =Excel 8.0;";
OleDbConnection conn = new OleDbConnection(excelConnStr);
OleDbCommand com = conn.CreateCommand();
conn.Open();
for (int i = 1; i <= 10; i++)
{
    com.CommandText = "CREATE TABLE [Sheet1$] (A2 char(255), B2 char(255), c2 char(255))";
    com.ExecuteNonQuery();
    com.CommandText = com.CommandText = "Insert into [Sheet1$] (A2,B2,C2) values('" + textBox1.Text + "','" + textBox1.Text + "','" + textBox1.Text + "')";Query();
}
conn.Close();

I hope this helps.

---

Please mark this post as answer if it solved your problem. Happy Programming!

Answer 3

I don't think it is OK to insert values to specified cells.

Try to use Update statement instead of Insert statement.

Answer 4

Hi,

I have excel database file. Here are the detailed steps i want to do

1. I want to find the last empty cell in the first column

2. Then i want to insert the records in that row

3. In future, if i want to update those records, i will insert that unique ID and search for the records and display all the values from excel to the form in textboxes and allow the user to update changes

4. Then when user updates any of the records and saves the changes it should be updated in the same row and should not be saved as a new record.

Could you suggest how to proceed.

Thanks,

Jimmit

---

Jimmit Raval