Post
Ask Question

Using Excel as a Database

In this blog, I will show how add data in excel sheet using c# and view the data in excel using GridView by selecting the data in the GridView.

  1. Create an excel file locally (location is local).

  2. Now create a form with the GridView, 2 text boxes and a command button like the following form:

    design

  3.  Here's the code in the form load event:
    1. String filename=@"D:\dem.xlsx";  
    2. String connection=@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+filename+";Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";  
    3. String Command="Select * from [sheets$]";  
    4. OleDbConnection con=new OleDbConnection(connection);  
    5. con.Open();  
    6. OleDbCommand cmd=new OleDbCommand(Command,con);  
    7. OleDbDataAdapter db=new OleDbDataAdapter(cmd);  
    8. DataTable dt=new DataTable();  
    9. db.Fill(dt);  
    10. dataGridView1.DataSource=dt;  
    This code allow us to load the existing excel data into the GridView.

  4. Add the following code in the command button to add the values in the excel sheet.
    1. String filename=@"D:\dem.xlsx"; //your excel location  
    2. String connection=@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+filename+";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;\"";   
    3. String Command="insert into [sheets$](Name,age) values('"+this.textBox1.Text+"','"+this.textBox2.Text+"')";  
    4. OleDbConnection con=new OleDbConnection(connection);  
    5. con.Open();  
    6. OleDbCommand cmd=new OleDbCommand(Command,con);  
    7. cmd.ExecuteNonQuery();  
    8. loaddata();  
  5.  Add the following code in the command button to update the values in the excel sheet.
    1. String filename=@"D:\dem.xlsx";  
    2. String connection=@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+filename+";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;\"";  
    3. String Command="update[sheets$] set age='"+textBox2.Text+"' where Name='"+textBox1.Text+"';";  
    4. OleDbConnection con=new OleDbConnection(connection);  
    5. con.Open();  
    6. OleDbCommand cmd=new OleDbCommand(Command,con);  
    7. cmd.ExecuteNonQuery();  
    8. loaddata();  
  6. Add the following line of code. This code help us to view the grid view data in textboxes.
    1. DataGridViewRow rows=this.dataGridView1.Rows[e.RowIndex];  
    2. textBox1.Text=rows.Cells["Name"].Value.ToString();  
    3. textBox2.Text=rows.Cells["age"].Value.ToString();  
  7. Deletion is not possible in excel through OLEDB. For more details refer the link.

Have fun….!!! Happy coding