ASP.NE Excel 數據導入導出

發表日期：2015-12-05 文章編輯：南昌開優網絡瀏覽次數：4483 標簽：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 百度手機網站

欧美日韩一区二区三区四区不卡,日韩欧美视频一区二区三区四区,久久精品欧美一区二区三区不卡,国产精品久久久乱弄

ASP.NE Excel 數據導入導出