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.
VB.Net
Dim sql As String = "Update [Sheet1$] set name = 'New Name' where id=2"
C#
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.
Default.aspx
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<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
Related Topics