客户端
Excel.aspx<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Excel.aspx.cs" Inherits="studyWebApplication.BLL.office.Excel" %>
<!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>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="gvExcel" runat="server" AutoGenerateColumns="False"
DataSourceID="sdsExcel" BackColor="Red">
<RowStyle BackColor="#CCFFFF" />
<Columns>
<asp:BoundField DataField="ID" HeaderText="ID" SortExpression="ID" />
<asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
<asp:BoundField DataField="Sex" HeaderText="Sex" SortExpression="Sex" />
<asp:BoundField DataField="Birthday" HeaderText="Birthday"
SortExpression="Birthday" />
<asp:BoundField DataField="Address" HeaderText="Address"
SortExpression="Address" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="sdsExcel" runat="server"
ConnectionString="<%$ ConnectionStrings:StudyConnectionString %>"
SelectCommand="SELECT * FROM [ExcelStudy]"></asp:SqlDataSource>
<asp:Button ID="btExport" runat="server" Text="Export"
onclick="btExport_Click" />
</div>
<div>
<asp:FileUpload ID="fuExcel" runat="server" />
<asp:Button ID="btImport" runat="server" Text="Import"
onclick="btImport_Click" />
<br />
<br />
<asp:PlaceHolder ID="phExcel" runat="server"></asp:PlaceHolder>
</div>
</form>
</body>
</html>
客户端后台
Excel.aspx.csusing System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using studyClassLibrary.office;
using System.IO;
namespace studyWebApplication.BLL.office
{
public partial class Excel : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btExport_Click(object sender, EventArgs e)
{
//Excel的格式可以在GRIDVIEW中设置
studyClassLibrary.office.Excel exc = new studyClassLibrary.office.Excel();
exc.exportExcel(gvExcel, "test.xls");
}
public override void VerifyRenderingInServerForm(Control control)
{
//base.VerifyRenderingInServerForm(control);
}
protected void btImport_Click(object sender, EventArgs e)
{
studyClassLibrary.office.Excel Excels = new studyClassLibrary.office.Excel();
ArrayList al = Excels.ExcelSheetName(fuExcel.PostedFile.FileName);
foreach (string str in al)
{
DataTable dt = Excels.ExcelDataSource(fuExcel.PostedFile.FileName, str);
PlaceHolder PHOption = (PlaceHolder)this.FindControl("phExcel");
Label lb = new Label();
lb.ID = "lb" + str;
lb.Text = str;
GridView gvFromExcel = new GridView();
gvFromExcel.Visible = true;
gvFromExcel.ID = "gv" + str;
this.phExcel.Controls.Add(lb);
this.phExcel.Controls.Add(gvFromExcel);
gvFromExcel.DataSource = dt;
gvFromExcel.DataBind();
}
}
}
}
调用函数代码
Excel.csusing System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.IO;
using System.Text;
using System.Data.OleDb;
namespace studyClassLibrary.office
{
public class Excel
{
public void exportExcel(GridView gvExcel, string filename)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=" + filename);
HttpContext.Current.Response.Charset = "";
HttpContext.Current.Response.ContentType = "application/ms-excel";
HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
gvExcel.RenderControl(htmlWrite);
HttpContext.Current.Response.Write(stringWrite.ToString());
HttpContext.Current.Response.End();
}
public DataTable ExcelDataSource(string filepath, string sheetname)
{
string strFileExt = Path.GetExtension(filepath).ToString();
string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;HDR=Yes;IMEX=1;\";";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "]", strConn);
DataTable dt = new DataTable();
oada.Fill(dt);
return dt;
}
public ArrayList ExcelSheetName(string filepath)
{
ArrayList al = new ArrayList();
string strConn;
string strFileExt = Path.GetExtension(filepath).ToString();
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;HDR=Yes;IMEX=1;\";";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataTable sheetNames = conn.GetOleDbSchemaTable
(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
conn.Close();
foreach (DataRow dr in sheetNames.Rows)
{
al.Add(dr[2]);
}
return al;
}
}
}
