界面：
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="index.aspx.cs" Inherits="index" %>
<!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" AutoGenerateColumns="False"
CellPadding="4" ForeColor="#333333" GridLines="None">
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<Columns>
<asp:BoundField DataField="UserId" HeaderText="用户ID" />
<asp:BoundField DataField="UserName" HeaderText="用户名" />
<asp:BoundField DataField="Sex" HeaderText="性别" />
<asp:BoundField DataField="Age" HeaderText="年龄" />
</Columns>
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<EditRowStyle BackColor="#999999" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>
</div>
<asp:Button ID="btnOut" runat="server" Text="导出" onclick="btnOut_Click" />
<asp:Button ID="btnIn" runat="server" Text="导入" onclick="btnIn_Click" />
<input id="File1" type="file" runat="server" /><asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False"
BackColor="White" BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px"
CellPadding="4" ForeColor="Black" GridLines="Vertical">
<RowStyle BackColor="#F7F7DE" />
<Columns>
<asp:BoundField DataField="用户名" HeaderText="用户名" />
<asp:BoundField DataField="性别" HeaderText="性别" />
<asp:BoundField DataField="年龄" HeaderText="年龄" />
</Columns>
<FooterStyle BackColor="#CCCC99" />
<PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
<SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
</form>
</body>
</html>
CS：
<!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" AutoGenerateColumns="False"
CellPadding="4" ForeColor="#333333" GridLines="None">
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<Columns>
<asp:BoundField DataField="UserId" HeaderText="用户ID" />
<asp:BoundField DataField="UserName" HeaderText="用户名" />
<asp:BoundField DataField="Sex" HeaderText="性别" />
<asp:BoundField DataField="Age" HeaderText="年龄" />
</Columns>
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<EditRowStyle BackColor="#999999" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>
</div>
<asp:Button ID="btnOut" runat="server" Text="导出" onclick="btnOut_Click" />
<asp:Button ID="btnIn" runat="server" Text="导入" onclick="btnIn_Click" />
<input id="File1" type="file" runat="server" /><asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False"
BackColor="White" BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px"
CellPadding="4" ForeColor="Black" GridLines="Vertical">
<RowStyle BackColor="#F7F7DE" />
<Columns>
<asp:BoundField DataField="用户名" HeaderText="用户名" />
<asp:BoundField DataField="性别" HeaderText="性别" />
<asp:BoundField DataField="年龄" HeaderText="年龄" />
</Columns>
<FooterStyle BackColor="#CCCC99" />
<PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
<SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
</form>
</body>
</html>
CS：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Excel;
using System.Data.OleDb;
public partial class index : System.Web.UI.Page
{
private const string strConn = "自己写连接字符串";
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindDataToGridView();
}
}
/// <summary>
/// 绑定
/// </summary>
private void BindDataToGridView()
{
SqlConnection conn = new SqlConnection(strConn);
string strSQL = "select * from [User]";
SqlDataAdapter da = new SqlDataAdapter(strSQL, conn);
DataSet ds = new DataSet();
da.Fill(ds, "[User]");
this.GridView1.DataSource = ds;
this.GridView1.DataKeyNames = new string[] { "UserId" };
this.GridView1.DataBind();
}
/// <summary>
/// 导出DataSet到Execl
/// </summary>
private void OutExecl()
{
Excel.Application myExcel = new Excel.Application();
myExcel.Visible = true;
if (myExcel == null)
{
Page.RegisterStartupScript("", "<script>alert('EXCEL无法启动');</script>");
}
Workbook work = myExcel.Application.Workbooks.Add(Type.Missing);
Worksheet sheet = (Worksheet)work.Worksheets[1];
int rowCount = 0;
int columnCount = 0;
columnCount = this.GridView1.Columns.Count;
rowCount = this.GridView1.Rows.Count;
rowCount--;
for (int m = 1; m < columnCount; m++)
{
sheet.Cells[1, m] = this.GridView1.Columns[m].HeaderText;//得到列标题文本
}
for (int i = 0; i <= rowCount; i++) //二维表填充从每一行开始
{
for (int j = 1; j < columnCount; j++) //填充每一行第j列单元格，循环添加行列数据
{
sheet.Cells[i + 2, j] = this.GridView1.Rows[i].Cells[j].Text;
}
}
Page.RegisterStartupScript("", "<script>alert('成功导出！');</script>");
}
/// <summary>
/// 导出
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnOut_Click(object sender, EventArgs e)
{
OutExecl();
}
/// <summary>
/// 导入Excel到DataSet
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
private DataSet InExecl(string filePath)
{
DataSet ds = new DataSet();
string connStr = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + filePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
OleDbConnection myConn = new OleDbConnection(connStr);
string strSQL = " SELECT * FROM [Sheet1$]";
myConn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strSQL, myConn);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet, "[Sheet1$]");
myConn.Close();
return myDataSet;
}
/// <summary>
/// 导入
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnIn_Click(object sender, EventArgs e)
{
DataSet ds = InExecl(this.File1.PostedFile.FileName);
this.GridView2.DataSource = ds;
this.GridView2.DataBind();
}
}
自己在项目中遇到的导入导出在网上搜索资料，然后自己做了一些小修改，提供给用的到的朋友，以上注意的是在DataSet ds = InExecl(this.File1.PostedFile.FileName);那个WEB的上传控件只能得到文件名this.FileUpload1.FileName 这个取的不是全路径，改用HTML的 input file控件 设置为作为服务器端运行
this.File1.PostedFile.FileName;就是全路径了。
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Excel;
using System.Data.OleDb;
public partial class index : System.Web.UI.Page
{
private const string strConn = "自己写连接字符串";
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindDataToGridView();
}
}
/// <summary>
/// 绑定
/// </summary>
private void BindDataToGridView()
{
SqlConnection conn = new SqlConnection(strConn);
string strSQL = "select * from [User]";
SqlDataAdapter da = new SqlDataAdapter(strSQL, conn);
DataSet ds = new DataSet();
da.Fill(ds, "[User]");
this.GridView1.DataSource = ds;
this.GridView1.DataKeyNames = new string[] { "UserId" };
this.GridView1.DataBind();
}
/// <summary>
/// 导出DataSet到Execl
/// </summary>
private void OutExecl()
{
Excel.Application myExcel = new Excel.Application();
myExcel.Visible = true;
if (myExcel == null)
{
Page.RegisterStartupScript("", "<script>alert('EXCEL无法启动');</script>");
}
Workbook work = myExcel.Application.Workbooks.Add(Type.Missing);
Worksheet sheet = (Worksheet)work.Worksheets[1];
int rowCount = 0;
int columnCount = 0;
columnCount = this.GridView1.Columns.Count;
rowCount = this.GridView1.Rows.Count;
rowCount--;
for (int m = 1; m < columnCount; m++)
{
sheet.Cells[1, m] = this.GridView1.Columns[m].HeaderText;//得到列标题文本
}
for (int i = 0; i <= rowCount; i++) //二维表填充从每一行开始
{
for (int j = 1; j < columnCount; j++) //填充每一行第j列单元格，循环添加行列数据
{
sheet.Cells[i + 2, j] = this.GridView1.Rows[i].Cells[j].Text;
}
}
Page.RegisterStartupScript("", "<script>alert('成功导出！');</script>");
}
/// <summary>
/// 导出
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnOut_Click(object sender, EventArgs e)
{
OutExecl();
}
/// <summary>
/// 导入Excel到DataSet
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
private DataSet InExecl(string filePath)
{
DataSet ds = new DataSet();
string connStr = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + filePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
OleDbConnection myConn = new OleDbConnection(connStr);
string strSQL = " SELECT * FROM [Sheet1$]";
myConn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strSQL, myConn);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet, "[Sheet1$]");
myConn.Close();
return myDataSet;
}
/// <summary>
/// 导入
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnIn_Click(object sender, EventArgs e)
{
DataSet ds = InExecl(this.File1.PostedFile.FileName);
this.GridView2.DataSource = ds;
this.GridView2.DataBind();
}
}
自己在项目中遇到的导入导出在网上搜索资料，然后自己做了一些小修改，提供给用的到的朋友，以上注意的是在DataSet ds = InExecl(this.File1.PostedFile.FileName);那个WEB的上传控件只能得到文件名this.FileUpload1.FileName 这个取的不是全路径，改用HTML的 input file控件 设置为作为服务器端运行
this.File1.PostedFile.FileName;就是全路径了。
