This article will cover, How to read data from 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, after doing this the 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 display data into gridview. In Data Source, you can take .xls file path.
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 column called id, name and Address to the Sheet1. Also add some data into the columns. Once these excel files are created, add them to your website folder. To add them to the solution, right click website name > Add Existing Item > Add the excel file.
Step 4: Add a web form called 'excelfile.aspx' into the website folder.
Step 5: Add a GridView and label control to the 'excelfile.aspx' page. We will extract data from the excel file and bind it to the GridView on page_load method.
Step 6: Bind GridView on page load method
<%@ 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>
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</div>
<asp:Label ID="Label1" runat="server" Text="Label"></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("Select * from [Sheet1$]", oledbconn);
OleDbDataAdapter oleda = new OleDbDataAdapter();
oleda.SelectCommand = cmd;
DataSet ds = new DataSet();
oleda.Fill(ds, "first");
GridView1.DataSource = ds.Tables[0].DefaultView;
GridView1.DataBind();
}
catch (OleDbException ex)
{
Label1.Text = ex.Message;
}
finally
{
oledbconn.Close();
}
}
}
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 display data into gridview. In Data Source, you can take .xls file path.
You can follow some steps for reading data from 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 column called id, name and Address to the Sheet1. Also add some data into the columns. Once these excel files are created, add them to your website folder. To add them to the solution, right click website name > Add Existing Item > Add the excel file.
Step 4: Add a web form called 'excelfile.aspx' into the website folder.
Step 5: Add a GridView and label control to the 'excelfile.aspx' page. We will extract data from the excel file and bind it to the GridView on page_load method.
Step 6: Bind GridView on page load method
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>
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</div>
<asp:Label ID="Label1" runat="server" Text="Label"></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("Select * from [Sheet1$]", oledbconn);
OleDbDataAdapter oleda = new OleDbDataAdapter();
oleda.SelectCommand = cmd;
DataSet ds = new DataSet();
oleda.Fill(ds, "first");
GridView1.DataSource = ds.Tables[0].DefaultView;
GridView1.DataBind();
}
catch (OleDbException ex)
{
Label1.Text = ex.Message;
}
finally
{
oledbconn.Close();
}
}
}
Code generate following output
Tidak ada komentar:
Posting Komentar