Search

Tuesday, February 3, 2015
0 comments

Computer Programming: How to insert data into excel file in ASP.NET, Example

8:54 AMTuesday, February 3, 2015
This article will cover, How to insert data into Excel spreadsheet using ASP.NET. First we need to connect Microsoft Excel workbook using the OLEDB.NET data provider. Add excel file path to connectionString. Now, your connectionstring will be look like

string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("first.xls") + ";" + "Extended Properties=Excel 4.0;";

Here, you can connect .xls file using Microsoft.Jet.OLEDB.4.0 provider also insert data in it. In Data Source, you can take .xls file path.

You can follow some steps for inserting data into excel file.

Step 1: Open Visual Studio > File > New >Website, Under Templates, click ASP.NET WebSite and choose Visual C# as the language. Select a system location and click Ok.

Step 2: We will create excel sheet and add them to the website folder. Excel sheet will be created in Office 2003(first.xls).

Step 3: Add three columns called id, name and Address to the Sheet1. Also add some data into the columns. Once this excel file is created, add it to your website folder. To add them to the solution, right click website name > Add Existing Item > Add the excel file.

Insert data into excel file using asp.net

Step 4: Add a web form called 'excelfile.aspx' into the website folder.
Step 5: Add a label control to the 'excelfile.aspx' page.
Step 6: Insert Data into excel file using OLEDB

Complete code is

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="excelfile.aspx.cs" Inherits="excelfile" %>

<!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 runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    </div>
    <asp:Label ID="Label1" runat="server"></asp:Label>
    </form>
</body>
</html>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Configuration;
using System.Data;

public partial class excelfile : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("first.xls") + ";" + "Extended Properties=Excel 4.0;";

        OleDbConnection oledbconn = new OleDbConnection(connString);
        try
        {
            oledbconn.Open();
            OleDbCommand cmd = new OleDbCommand("insert into [Sheet1$] values('2','Jacob','USA')", oledbconn);

          int a=  cmd.ExecuteNonQuery();
          if (a>0)
          {

              Label1.Text = "Data Inserted";
          }

            
        }
        catch (OleDbException ex)
        {
           Label1.Text = ex.Message;
        }
        finally
        {
            oledbconn.Close();
        }
    

    }
}

Code generate following output

Data Inserted into excel file in asp.net
Data Inserted into excel file in asp.net

Protected by Copyscape Online Copyright Protection Software

0 comments :

Post a Comment

 
Toggle Footer
Top