ASP.NE Excel 数据导入导出

发表日期：2015-12-05 文章编辑：南昌开优网络浏览次数：4115 标签：ASP.NET应用

引用

NPOI.dll 和 Ionic.Zip.dll 搜索可以找到

GridView 的方式导出

<asp:GridView ID="datagrid1" runat="server" AutoGenerateColumns="False" EnableModelValidation="True" onrowdatabound="datagrid1_RowDataBound">

<asp:BoundField DataField="id" HeaderText="序号" />

<asp:BoundField DataField="type" HeaderText="类型" />

<asp:BoundField DataField="username" HeaderText="姓名" />

<asp:BoundField DataField="sex" HeaderText="性别" />

</Columns>

</asp:GridView>

CS文件

DAL.SurveyDAL sdal = new DAL.SurveyDAL();

if (!Page.IsPostBack)

{

datagrid1.DataSource = sdal.GetList(""); //绑定列表

datagrid1.DataBind();

}

//导出表格按扭事件

protected void lbtnExecl_Click(object sender, EventArgs e)

{

DGToExcel(datagrid1);

}

// 导成Excel文件

public void DGToExcel(System.Web.UI.Control ctl)

{

HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=Excel.xls");

HttpContext.Current.Response.Charset = "UTF-8";

HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default;

HttpContext.Current.Response.ContentType = "application/ms-excel";

ctl.Page.EnableViewState = false;

System.IO.StringWriter tw = new System.IO.StringWriter();

System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);

ctl.RenderControl(hw);

HttpContext.Current.Response.Write(tw.ToString());

HttpContext.Current.Response.End();

}

// 必须的属性方法

public override void VerifyRenderingInServerForm(Control control)

{

}

//更改GridView 的显示字段的名称

protected void datagrid1_RowDataBound(object sender, GridViewRowEventArgs e)

{

if (e.Row.RowType == DataControlRowType.DataRow)

{

switch (int.Parse(e.Row.Cells[1].Text)) //数据表中的第二个字段 GridView 必须要统一

{

case 0:

e.Row.Cells[1].Text = "黄金储值VIP";

break;

case 1:

e.Row.Cells[1].Text = "普通VIP";

break;

case 2:

e.Row.Cells[1].Text = "尊贵来客";

break;

default:

break;

}

e.Row.Cells[3].Text = (int.Parse(e.Row.Cells[1].Text) == 0) ? "男" : "女";

}

需要注意两个地方：

1.事件必须加

public override void VerifyRenderingInServerForm(Control control)

{

//base.VerifyRenderingInServerForm(control);

}

3、若有分页功能

在<%@ Page Language="C#" ...%>中加上 EnableEventValidation="false"

加了这个必须在导出前取消分页，否则只导出当前页，而不是整个gridview的数据。

使用DataTableRenderToExcel 导入导出Excel数据

数据表和Excel表字段要一致

<div style='color:Red;'>注：请严格按钮模板中的格式进行导入，顺序号重复则更新数据，模板下载：<a href='/userfiles/moban.xls' target='_blank'>点击下载</a></div>

<asp:FileUpload ID="flExcel" runat="server" /> //上传控件

<asp:Button ID="btnExcel" runat="server" Text="导入表格" onclick="btnExcel_Click" /> //导入上传按钮

<asp:Literal ID="litStr" runat="server"></asp:Literal> //显示信息

<br />

<asp:Button ID="btnToExcel" runat="server" Text="导出表格" onclick="btnToExcel_Click" /> //导出按钮

注：表格的导出导入，在表结构中要添加顺序字段，用于判断数据是否存在

//导入表格

protected void btnExcel_Click(object sender, EventArgs e)

{

try

{

#region excel上传

// string tmp_name = Guid.NewGuid().ToString().Substring(0, 4); //保存的文件名，以guid前4位存

string xlsname = Xiaobin.Utility.Tool.Upload(flExcel, new string[] { ".xls", ".xlsx" }, 20, Server.MapPath("/upload/"));

string xls_fullpath = Server.MapPath("/upload/" + xlsname); //完整路径

DataTable dt = Xiaobin.Utility.Tool.RenderDataTableFromExcel(xls_fullpath);

int add = 0; //新增数

int mod = 0; //修改数

foreach (DataRow row in dt.Rows)

{

string numID = row[0].ToString();

string createdate = row[1].ToString();

string channelTitle = row[2].ToString();

string channelName = row[3].ToString();

string modID = row[4].ToString();

string sort = row[5].ToString();

string isshow = row[6].ToString();

if (string.IsNullOrEmpty(id)) //实际用顺序字段判断是否存在

{

continue; //跳出

}

Model.Channel model = cdal.GetModel(int.Parse(id)); //实际用顺序字段判断model是否存在

if (model == null)

{

cdal.Add(new Model.Channel()

{

id = int.Parse(id),

createdate = DateTime.Now,

ChannelName = channelName,

ChannelTitle = channelTitle,

isShow = int.Parse(isshow),

modID = int.Parse(modID),

sort = int.Parse(sort)

});

add++;

}

else

{

model.sort = int.Parse(sort); model.createdate = DateTime.Now; model.ChannelName = channelName; model.ChannelTitle = channelTitle; model.isShow = int.Parse(isshow); model.modID = int.Parse(modID); model.id = int.Parse(id);

cdal.Update(model);

mod++;

}

litStr.Text = "总数：" + dt.Rows.Count + "，新增：" + add + "，修改：" + mod;

#endregion

}

catch (Exception ex)

{

litStr.Text = "出错：" + ex.Message;

}

//导出表格

protected void btnToExcel_Click(object sender, EventArgs e)

{

DataTable dt = cdal.GetList("").Tables[0];

MemoryStream ms = Xiaobin.Utility.Tool.RenderDataTableToExcel(dt) as MemoryStream;

Response.AddHeader("Content-Disposition", string.Format("attachment; filename=Download.xls"));

Response.BinaryWrite(ms.ToArray());

ms.Close();

ms.Dispose();

Xiaobin.Utility.Tool.Alert("导出成功",this.Page);

}

返回列表

上一篇：AspNetPager 伪静态设置下一篇：SiteApp 百度手机网站