Modify Excel file from ASP.NET

The following ASP.NET program modify the data in the excel file using OLEDB connection . In order to run this program you have to have an existing excel file with two column named as ID and NAME in the specified location of the connection string.

connStr = "provider=Microsoft.Jet.OLEDB.4.0;Data Source='ExcelFile.xls';Extended Properties=Excel 8.0;";

Data Source=ExcelFile.xls : you have to specify the Excel file location.

For updating data into an Excel file using OLEDB , we have to create an update statement like in SQL operations.

Dim sql As String = "Update [Sheet1$] set name = 'New Name' where id=2"
string sql = "Update [Sheet1$] set name = 'New Name' where id=1";

NOTE: While updating the Excel file, the updating column data should exist in the excel file.


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" ""> <html xmlns=""> <head id="Head1" runat="server"> <title>Untitled Page</title> </head> <body> <form id="form1" runat="server"> <div> <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Button" Width="103px" /> </div> <asp:Label ID="Label1" runat="server" Text="Message : "></asp:Label>   </form> </body> </html>
Full Source | C#
using System; using System.Data; using System.Data.OleDb ; public partial class _Default : System.Web.UI.Page { protected void Button1_Click(object sender, EventArgs e) { try { string connStr = "provider=Microsoft.Jet.OLEDB.4.0;Data Source='your-excel-file.xls';Extended Properties=Excel 8.0;"; OleDbConnection MyConnection; OleDbCommand MyCommand = new OleDbCommand(); MyConnection = new OleDbConnection(connStr); MyConnection.Open(); MyCommand.Connection = MyConnection; string sql = "Update [Sheet1$] set name = 'New Name' where id=1"; MyCommand.CommandText = sql; MyCommand.ExecuteNonQuery(); MyConnection.Close(); Label1.Text = "data updated successfully !! "; } catch (Exception ex) { Label1.Text = ex.ToString(); } } }
Full Source | VB.NET
Imports System Imports System.Data Imports System.Data.OleDb Partial Class _Default Inherits System.Web.UI.Page Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Try Dim connStr As String = "provider=Microsoft.Jet.OLEDB.4.0;Data Source='your-excel-file.xls';Extended Properties=Excel 8.0;" Dim MyConnection As OleDbConnection Dim MyCommand As New OleDbCommand() MyConnection = New OleDbConnection(connStr) MyConnection.Open() MyCommand.Connection = MyConnection Dim sql As String = "Update [Sheet1$] set name = 'New Name' where id=2" MyCommand.CommandText = sql MyCommand.ExecuteNonQuery() MyConnection.Close() Label1.Text = "data updated successfully !! " Catch ex As Exception Label1.Text = ex.ToString() End Try End Sub End Class

Click the following links to see full source code

C# Source Code
VB.NET Source Code