界面层
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="GridviewExportExcel.aspx.cs" Inherits="GridviewExcel" EnableEventValidation="false" %>
<!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>Gridview Excel</title>
<link href="~/CSS/Gridview.css" type="text/css" rel="Stylesheet" />
</head>
<body>
<form id="form1" runat="server">
<div id="container">
<asp:GridView ID="GridView1" BorderColor="Black" OnRowDataBound="GridView1_RowDataBound" runat="server" AutoGenerateColumns="False" Font-Size="12px" Width="530px" AllowSorting="True">
<Columns>
<asp:BoundField DataField="EmpID" HeaderText="编号" />
<asp:BoundField DataField="EmpRealName" HeaderText="姓名" />
<asp:BoundField DataField="EmpSex" HeaderText="性别" />
<asp:BoundField DataField="EmpAddress" HeaderText="住址" />
</Columns>
<HeaderStyle BackColor="Azure" Font-Size="12px" HorizontalAlign="Center" />
<RowStyle HorizontalAlign="Center" />
<PagerStyle HorizontalAlign="Center" />
</asp:GridView>
<asp:Button ID="Button1" runat="server" Text="导 出 EXCEL 表 格" Height="34px" OnClick="Button1_Click" Width="263px" /></div>
</form>
</body>
</html>
代码层
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
/// <summary>
/// Author:伊人 Blog:http://shop68840868.taobao.com
Gridview从Excel中读取数据
/// </summary>
public partial class GridviewReadExcel : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
/// <summary>
/// 在 GridView 控件中的某个行被绑定到一个数据记录时发生。此事件通常用于在某个行被绑定到数据时修改该行的内容。
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
foreach (TableCell tc in e.Row.Cells)
{
tc.Attributes["style"] = "border-color:Black";
}
if (e.Row.RowIndex != -1)
{
int id = GridView1.PageIndex * GridView1.PageSize + e.Row.RowIndex + 1;
e.Row.Cells[0].Text = id.ToString();
}
}
/// <summary>
/// Inport
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Button1_Click(object sender, EventArgs e)
{
GridView1.DataSource = createDataSource();
GridView1.DataBind();
}
/// <summary>
/// 以Excel为数据源获取数据集
/// </summary>
/// <returns></returns>
private DataSet createDataSource()
{
string strCon;
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("~/Files/Employee.xls") + ";Extended Properties=Excel 8.0;";
OleDbConnection con = new OleDbConnection(strCon);
OleDbDataAdapter da = new OleDbDataAdapter("select * from [Employee$]", con);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
}