In this article I will explains with an example, how to export data to Excel sheet using ADO.Net in ASP.Net in C# and VB.Net.
 
 
Concept
Excel File works similar to that of Database where an Excel file can be imagined as Database while the Sheets resemble Tables.
Thus, OLEDB allows us to query Excel files as if it is Database.
 
Connection Strings
The first thing is to build connection strings to Excel files and Excel files are broadly divided into two types and Excel 97-2003 and Excel 2007 and higher.
Excel 97 – 2003 format which uses Microsoft Jet driver and the Excel version is set to 8.0 and Header is set to YES i.e. first row in Excel sheet will be Header Row.
<add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes'" />
 
Excel 2007 format which uses Microsoft Ace driver and the Excel version is set to 12.0 and Header is set to YES i.e. first row in Excel sheet will be Header Row.
<add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=Yes'" />
 
Queries
Create Sheet
The following SQL query creates a new sheet of Excel file.
CREATE TABLE [Customers] (ID VARCHAR(10), Name VARCHAR(50), Country VARCHAR(50))
 
Insert data into Sheet
The following SQL query inserts record in sheet of Excel file.
INSERT INTO [Customers$] (ID , Name , Country) VALUES ('1', 'John Hammond', 'United States')
 
Update data into Sheet
The following SQL query update record in sheet of Excel file.
UPDATE [Customers$] SET [Name] = 'Mudassar Khan', [Country] = 'India' WHERE ID = '1'
 
 
Namespaces
You will need to import the following namespaces.
C#
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
 
VB.Net
Imports System.IO
Imports System.Data
Imports System.Data.OleDb
Imports System.Configuration
 
 
HTML
The following HTML Markup consists of:
Button – For Inserting and Updating data in Excel sheet.
The Buttons have been assigned with the OnClick event handler.
<asp:Button ID="btnInsertExcel03" runat="server" Text="InsertExcel03" OnClick="InsertExcel03" />
<asp:Button ID="btnInsertExcel07" runat="server" Text="InsertExcel07" OnClick="InsertExcel07" />
<asp:Button ID="btnUpdateExcel03" runat="server" Text="UpdateExcel03" OnClick="UpdateExcel03" />
<asp:Button ID="btnUpdateExcel07" runat="server" Text="UpdateExcel07" OnClick="UpdateExcel07" />
 
 
Inserting record in Excel Sheet using ADO.Net
When the Insert buttons are clicked, the InsertExcelRow method is called which accepts path of Excel file as parameter.
Inside the InsertExcelRow method, the Excel file extension is determined and based on the extension the connection string is built by replacing the placeholder and the Excel file is read using the selected OLEDB driver.
Next, the Schema of the Excel file is read and the Name of the specified Sheet is determined.
If it is not present, the sheet has been created and the data is inserted into the newly created Excel sheet.
C#
protected void InsertExcel03(object sender, EventArgs e)
{
    this.InsertExcelRow("D:\\Customers.xls");
}
 
protected void InsertExcel07(object sender, EventArgs e)
{
    this.InsertExcelRow("D:\\Customers.xlsx");
}
 
private void InsertExcelRow(string filePath)
{
    if (File.Exists(filePath))
    {
        string extension = Path.GetExtension(filePath);
        string conString = "";
        switch (extension)
        {
            case ".xls": //Excel 97-03
                conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                break;
            case ".xlsx": //Excel 07
                conString = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                break;
        }
        conString = string.Format(conString, filePath);
        using (OleDbConnection connExcel = new OleDbConnection(conString))
        {
            using (OleDbCommand cmdExcel = new OleDbCommand())
            {
                cmdExcel.Connection = connExcel;
                connExcel.Open();
                //Check if the Sheet Exists.
                using (DataTable dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null))
                {
                    connExcel.Close();
                    DataRow[] dr = dtExcelSchema.Select("TABLE_NAME = 'Customers$'");
 
                    //If not Create the Sheet.
                    if (dr == null || dr.Length == 0)
                    {
                        cmdExcel.CommandText = "CREATE TABLE [Customers$] (ID VARCHAR(10), Name VARCHAR(50), Country VARCHAR(50));";
                        connExcel.Open();
                        cmdExcel.ExecuteNonQuery();
                        connExcel.Close();
                    }
 
                    connExcel.Open();
 
                    //Add New Row to Excel File.
                    cmdExcel.CommandText = "INSERT INTO [Customers$] (ID, Name, Country) VALUES ('1', 'John Hammond', 'United States')";
                    cmdExcel.ExecuteNonQuery();
                    connExcel.Close();
                }
            }
        }
    }
}
 
VB.Net
Protected Sub InsertExcel03(ByVal sender As Object, ByVal e As EventArgs)
    Me.InsertExcelRow("D:\Customers.xls")
End Sub
 
Protected Sub InsertExcel07(ByVal sender As Object, ByVal e As EventArgs)
    Me.InsertExcelRow("D:\Customers.xlsx")
End Sub
 
Private Sub InsertExcelRow(ByVal filePath As String)
    If File.Exists(filePath) Then
        Dim extension As String = Path.GetExtension(filePath)
        Dim conString As String = ""
 
        Select Case extension
            Case ".xls" 'Excel 97-03
                conString = ConfigurationManager.ConnectionStrings("Excel03ConString").ConnectionString
            Case ".xlsx" 'Excel 07
                conString = ConfigurationManager.ConnectionStrings("Excel07ConString").ConnectionString
        End Select
 
        conString = String.Format(conString, filePath)
        Using connExcel As OleDbConnection = New OleDbConnection(conString)
            Using cmdExcel As OleDbCommand = New OleDbCommand()
                cmdExcel.Connection = connExcel
                connExcel.Open()
                'Check if the Sheet Exists.
                Using dtExcelSchema As DataTable = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
                    connExcel.Close()
                    Dim dr As DataRow() = dtExcelSchema.Select("TABLE_NAME = 'Customers$'")
 
                    'If not Create the Sheet.
                    If dr Is Nothing Or Else dr.Length = 0 Then
                        cmdExcel.CommandText = "CREATE TABLE [Customers] (ID VARCHAR(10), Name VARCHAR(50), Country VARCHAR(50));"
                        connExcel.Open()
                        cmdExcel.ExecuteNonQuery()
                        connExcel.Close()
                    End If
 
                    'Add New Row to Excel File.
                    connExcel.Open()
                    cmdExcel.CommandText = "INSERT INTO [Customers$] (ID, Name, Country) VALUES ('1', 'John Hammond', 'United States')"
                    cmdExcel.ExecuteNonQuery()
                    connExcel.Close()
                End Using
            End Using
        End Using
    End If
End Sub
 
 
Updating record in Excel Sheetusing ADO.Net
When the Update buttons are clicked, the UpdateExcelRow method is called which accepts path of Excel file as parameter.
Inside the UpdateExcelRow method, the Excel file extension is determined and based on that the connection string is built by replacing the placeholder and the Excel file is read using the selected OLEDB driver.
Next, the Schema of the Excel file is read and the Name of the specified Sheet is determined.
Finally, the data is updated into the Excel sheet based on the update query.
C#
protected void UpdateExcel03(object sender, EventArgs e)
{
    this.UpdateExcelRow("D:\\Customers.xls");
}
 
protected void UpdateExcel07(object sender, EventArgs e)
{
    this.UpdateExcelRow("D:\\Customers.xlsx");
}
 
private void UpdateExcelRow(string filePath)
{
    if (File.Exists(filePath))
    {
        string extension = Path.GetExtension(filePath);
        string conString = "";
        switch (extension)
        {
            case ".xls": //Excel 97-03
                conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                break;
            case ".xlsx": //Excel 07
                conString = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                break;
        }
        conString = string.Format(conString, filePath);
        using (OleDbConnection connExcel = new OleDbConnection(conString))
        {
            using (OleDbCommand cmdExcel = new OleDbCommand())
            {
                cmdExcel.Connection = connExcel;
                connExcel.Open();
                //Check if the Sheet Exists.
                using (DataTable dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null))
                {
                    connExcel.Close();
                    DataRow[] dr = dtExcelSchema.Select("TABLE_NAME = 'Customers$'");
                    if (dr != null || dr.Length > 0)
                    {
                        connExcel.Open();
                        cmdExcel.CommandText = "UPDATE [Customers$] SET [Name] = 'Mudassar Khan', [Country] = 'India' WHERE [ID] = '1'";
                        cmdExcel.ExecuteNonQuery();
                        connExcel.Close();
                    }
                }
            }
        }
    }
}
 
VB.Net
Protected Sub UpdateExcel03(ByVal sender As Object, ByVal e As EventArgs)
    Me.UpdateExcelRow("D:\Customers.xls")
End Sub
 
Protected Sub UpdateExcel07(ByVal sender As Object, ByVal e As EventArgs)
    Me.UpdateExcelRow("D:\Customers.xlsx")
End Sub
 
Private Sub UpdateExcelRow(ByVal filePath As String)
    If File.Exists(filePath) Then
        Dim extension As String = Path.GetExtension(filePath)
        Dim conString As String = ""
        Select Case extension
            Case ".xls" 'Excel 97-03
                conString = ConfigurationManager.ConnectionStrings("Excel03ConString").ConnectionString
            Case ".xlsx" 'Excel 07
                conString = ConfigurationManager.ConnectionStrings("Excel07ConString").ConnectionString
        End Select
        conString = String.Format(conString, filePath)
        Using connExcel As OleDbConnection = New OleDbConnection(conString)
            Using cmdExcel As OleDbCommand = New OleDbCommand()
                cmdExcel.Connection = connExcel
                connExcel.Open()
                'Check if the Sheet Exists.
                Using dtExcelSchema As DataTable = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
                    connExcel.Close()
                    Dim dr As DataRow() = dtExcelSchema.Select("TABLE_NAME = 'Customers$'")
                    If dr Is Not Nothing Or Else dr.Length > 0 Then
                        connExcel.Open()
                        cmdExcel.CommandText = "UPDATE [Customers$] SET [Name] = 'Mudassar Khan', [Country] = 'India' WHERE [ID] = '1'"
                        cmdExcel.ExecuteNonQuery()
                        connExcel.Close()
                    End If
                End Using
            End Using
        End Using
    End If
End Sub
 
 
Possible Errors
The following error occurs when trying to export data to Excel Sheet while making use of Excel 2007 or higher files using OLEDB connection.
For more details, please refer The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.
 
 
Screenshots
Inserted Record in Excel
Insert and Update Data to Excel Sheet using ADO.Net and C# and VB.Net
 
Record after updated in Excel
Insert and Update Data to Excel Sheet using ADO.Net and C# and VB.Net
 
 
Downloads