前台拖一个Gridview，在拖一个导出excel的按钮，给这个按钮添加事件
后台代码：
using BLL;
using Model;
namespace Web
{
public partial class ExcelOperate : System.Web.UI.Page
{
private StudentBLL bll = new StudentBLL();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Bind();
}
}
//绑定数据
private void Bind()
{
GridView1.DataSource = bll.GetAllStu(null);
GridView1.DataBind();
}
#regIOn 导出到Excel
//导出excel
protected void btnExcelout_Click(object sender, EventArgs e)
{
string style = @"<style> .text { mso-number-format:\@; } </script> "; //设置格式
Response.ClearContent();
Response.ContentEncoding = Encoding.GetEncoding("gbk");
Response.AddHeader("content-disposition", "attachment;filename=ouput.xls");
Response.ContentType = "application/excel";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
GridView1.RenderControl(htw);
Response.Write(style);//注意
Response.Write(sw.ToString());
Response.End();
}
//注意：必须覆盖此方法
public override void VerifyRenderingInServerForm(Control control)
{
//base.VerifyRenderingInServerForm(control);
}
//解决数字字符串显示不完全
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
// e.Row.Cells[3].Attributes.Add("class", "text");//在数据绑定中设置格式
//哪一列需要显示文本的，加上下面这句话即可
e.Row.Cells[3].Attributes.Add("style", "vnd.ms-excel.numberformat:@");
}
#endregion
}
}
页面效果：
导入Excel并保存到数据库：
前台需要拖一个FileUpload上传控件，一个导入excel按钮，给其添加事件：
//导入excel数据
protected void btnExcelIn_Click(object sender, EventArgs e)
{
string filepath = string.Empty;
string getErrormg = string.Empty;
DataTable dt=new DataTable();
if (!fuFile.HasFile)
{
Response.Write("<script>alert('请选择你要导入的Excel文件');</script>");
return;
}
//获取文件的后缀名
string fileExt = System.IO.Path.GetExtension(fuFile.FileName);
if (fileExt != ".xls")
{
Response.Write("<script>alert('文件类型错误！');</script>");
return;
}
//获取绝对路径
filepath = fuFile.PostedFile.FileName;
string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";Data Source=" filepath;
OleDbConnection excelCon = new OleDbConnection(conn);
//Excel文件里面工作表名 默认为Sheet1,后面需要加上$符号[工作表名称$]切记，不然会报错
OleDbDataAdapter odda = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", excelCon);
try
{
odda.Fill(dt);
}
catch (Exception ex)
{
Response.Write(ex.Message);
Response.Write("<script>alert('" ex.Message "！')</script>");
}
finally
{
excelCon.Close();
excelCon.Dispose();
}
//将数据写到数据库里面
try
{
for (int i = 0; i < dt.Rows.Count; i )
{
Studnet stu=new Studnet();
stu.C_id = Convert.ToInt32(dt.Rows[i]["c_id"]);
stu.No = dt.Rows[i]["no"].ToString();
stu.Name = dt.Rows[i]["name"].ToString();
stu.Gender = dt.Rows[i]["gender"].ToString() == "男" ? true : false;
stu.Age = Convert.ToInt32(dt.Rows[i]["age"].ToString());
bll.InsertStu(stu);
}
}
catch (Exception ex)
{
getErrormg = ex.Message;
Response.Write(ex.Message);
}
if (getErrormg == "")
{
Response.Write("<script>alert('导入Excel文件成功！')</script>");
Bind();
}
else { Response.Write("<script>alert('导入Excel文件失败！')</script>"); }
}
Excel和导入后的页面效果：
数据库在导入excel数据之前和时候的效果：
这里要注意几个地方，一般导出excel的时候，数字文本会把前面的0都省略掉了，这里需要注意：红色代码片段，导入的时候，也有个***红红红色***标记码块要注意
以下是前台完整代码：
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ExcelOperate.aspx.cs" Inherits="Web.ExcelOperate" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" OnRowDataBound="GridView1_RowDataBound"></asp:GridView>
<br />
<asp:Button ID="btnExcelout" runat="server" OnClick="btnExcelout_Click" Text="导出到Excel" />
<br />
<asp:FileUpload ID="fuFile" runat="server" />
<asp:Button ID="btnExcelIn" runat="server" OnClick="btnExcelIn_Click" Text="导入Excel数据" />
</div>
</form>
</body>
</html>
以下是后台完整代码：
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using BLL;
using Model;
namespace Web
{
public partial class ExcelOperate : System.Web.UI.Page
{
private StudentBLL bll = new StudentBLL();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Bind();
}
}
//绑定数据
private void Bind()
{
GridView1.DataSource = bll.GetAllStu(null);
GridView1.DataBind();
}
#region 导出到excel
//导出excel
protected void btnExcelout_Click(object sender, EventArgs e)
{
string style = @"<style> .text { mso-number-format:\@; } </script> "; //设置格式
Response.ClearContent();
Response.ContentEncoding = Encoding.GetEncoding("gbk");
Response.AddHeader("content-disposition", "attachment;filename=ouput.xls");
Response.ContentType = "application/excel";
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
GridView1.RenderControl(htw);
Response.Write(style);//注意
Response.Write(sw.ToString());
Response.End();
}
//注意：必须覆盖此方法 ***红红红色***
public override void VerifyRenderingInServerForm(Control control)
{
//base.VerifyRenderingInServerForm(control);
}
//解决数字字符串显示不完全 ***红红红色******红红红色******红红红色***
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
//在数据绑定中设置格式
//哪一列需要显示文本的，加上下面这句话即可
e.Row.Cells[3].Attributes.Add("style", "vnd.ms-excel.numberformat:@");
}
#endregion
//导入excel数据
protected void btnExcelIn_Click(object sender, EventArgs e)
{
string filepath = string.Empty;
string getErrormg = string.Empty;
DataTable dt=new DataTable();
if (!fuFile.HasFile)
{
Response.Write("<script>alert('请选择你要导入的Excel文件');</script>");
return;
}
//获取文件的后缀名
string fileExt = System.IO.Path.GetExtension(fuFile.FileName);
if (fileExt != ".xls")
{
Response.Write("<script>alert('文件类型错误！');</script>");
return;
}
//获取绝对路径
filepath = fuFile.PostedFile.FileName;
string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";Data Source=" filepath;
OleDbConnection excelCon = new OleDbConnection(conn);
//默认为Sheet1,后面需要加上$符号表面为什么名称，[表名称$]，切记不然会报错
OleDbDataAdapter odda = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", excelCon);
try
{
odda.Fill(dt);
}
catch (Exception ex)
{
Response.Write(ex.Message);
Response.Write("<script>alert('" ex.Message "！')</script>");
}
finally
{
excelCon.Close();
excelCon.Dispose();
}
//将数据写到数据库里面
try
{
for (int i = 0; i < dt.Rows.Count; i )
{
Studnet stu=new Studnet();
stu.C_id = Convert.ToInt32(dt.Rows[i]["c_id"]);
stu.No = dt.Rows[i]["no"].ToString();
stu.Name = dt.Rows[i]["name"].ToString();
stu.Gender = dt.Rows[i]["gender"].ToString() == "男" ? true : false;
stu.Age = Convert.ToInt32(dt.Rows[i]["age"].ToString());
bll.InsertStu(stu);
}
}
catch (Exception ex)
{
getErrormg = ex.Message;
Response.Write(ex.Message);
}
if (getErrormg == "")
{
Response.Write("<script>alert('导入Excel文件成功！')</script>");
Bind();
}
else { Response.Write("<script>alert('导入Excel文件失败！')</script>"); }
}
}
}
