gridview,datalist导入数据，导出数据

最新推荐文章于 2018-12-15 09:08:58 发布
最新推荐文章于 2018-12-15 09:08:58 发布
阅读量734 收藏
点赞数
分类专栏： ASP.NET技术 文章标签： asp textbox server string object dataset
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Love1221/article/details/1774547
版权

导入数据，导出数据
------------------------------------------------------------
protected void btnUpload_Click(object sender, EventArgs e)
    {
        if (FileUpload1.PostedFile.FileName.ToString() != "" && FileUpload1.PostedFile.FileName.ToString().Substring((FileUpload1.PostedFile.FileName.ToString().Length - 3), 3) == "xls")
        {
            string strFileName;
            DateTime mydatetime = new DateTime();
            mydatetime = DateTime.Now;
            strFileName = mydatetime.Year.ToString() + mydatetime.Month.ToString() + mydatetime.Day.ToString() +
                mydatetime.Hour.ToString() + mydatetime.Minute.ToString() + mydatetime.Second.ToString() + ".xls";
            string path = HttpContext.Current.Server.MapPath("Temp//");
            FileUpload1.SaveAs(path + strFileName);

            //写一个Updata方法，用来在数据库中添加数据
            Update(path + strFileName);
            Response.Write("<script>alert('导入成功');</script>");
        }
        else
        {
            Label1.Text = "请选择一个Execl文件!";
        }
        BindExecl();
    }

    GetData GD = new GetData();
    string sql;
    DataSet ds;
    /// <summary>
    /// 添加数据
    /// </summary>
    /// <param name="UploadFileName"></param>
    private void Update(string UploadFileName)
    {
        string OleDbstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + UploadFileName + "';Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
        OleDbConnection OleDbcon = new OleDbConnection();
        OleDbcon.ConnectionString = OleDbstr;
        OleDbcon.Open();
        OleDbCommand OleDbcomm = new OleDbCommand("select * from [shopclass$]", OleDbcon);
        OleDbDataAdapter da = new OleDbDataAdapter();
        da.SelectCommand = OleDbcomm;
        DataSet ds = new DataSet();
        DataTable myTable = ds.Tables.Add("shopclass$");
        da.Fill(ds, "shopclass$");
        OleDbcon.Close();
        int shopClassid;
        //int id;
        shopClassid = GD.MaxShopClassId() + 1;
        for (int i = 0; i < myTable.Rows.Count; i++)
        {
            SqlConnection conn = GD.Conn();
            //string sql = "insert into execl(shopClassid,name,father,userId) values('" + myTable.Rows[i][0].ToString() + "','" + myTable.Rows[i][1].ToString() + "','" + myTable.Rows[i][2].ToString() + "'," + 1 + ")";
            sql = "insert into execl(shopClassid,name,father,userId) values('" + shopClassid + "','" + myTable.Rows[i][1].ToString() + "','" + myTable.Rows[i][2].ToString() + "'," + 1 + ")";
            SqlCommand cmd = new SqlCommand(sql, conn);
            //id = shopClassid + 1;
            shopClassid++;
            conn.Open();
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new ApplicationException(e.ToString());
            }
            finally
            {
                conn.Close();               
            }
        }
    }

    /// <summary>
    /// 导出到Execl表事件
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Button1_Click(object sender, EventArgs e)
    {
        Export("application/ms-excel", "宠物类别表.xls");
    }

    /// <summary>
    /// 导出到Execl表方法
    /// </summary>
    /// <param name="FileType"></param>
    /// <param name="FileName"></param>
    private void Export(string FileType, string FileName)
    {
        BindExecl2();

        Response.Charset = "GB2312";
        Response.ContentEncoding = System.Text.Encoding.UTF7;
        Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8).ToString());
        Response.ContentType = FileType;
        this.EnableViewState = false;
        StringWriter tw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(tw);
        //错误提示：只能在执行 Render() 的过程中调用 RegisterForEventValidation;
        //解决方法：EnableEventValidation = "false"
        GridView2.RenderControl(hw);
        Response.Write(tw.ToString());
        Response.End();
    }
    public override void VerifyRenderingInServerForm(Control control)
    {
    }

 

--------------------------------------------------------------------
DataList数据输出到Excel
Repeater、DataList、DataGrid、GridView...还是比较喜欢用轻量级的数据绑定控件，本例事将DataList中的数据导入到Excel，以及一些相关操作；

MyTest.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="MyTest.aspx.cs" Inherits="MyWeb.MyTest" %>

<!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>DataList数据输出到Excel</title>
</head>
<body>
     <form id="form1" runat="server">
     <div>
         <asp:DataList ID="dlMain" runat="server" OnItemDataBound="dlMain_ItemDataBound" OnCancelCommand="dlMain_CancelCommand" OnDeleteCommand="dlMain_DeleteCommand" OnEditCommand="dlMain_EditCommand" OnPreRender="dlMain_PreRender" OnUpdateCommand="dlMain_UpdateCommand" DataKeyField="IDCard" >
             <HeaderTemplate>
                 <asp:Table ID="tbHeader" runat="server">
                     <asp:TableRow>
                         <asp:TableCell Width="150px">IDCard</asp:TableCell>
                         <asp:TableCell Width="100px">Name</asp:TableCell>
                         <asp:TableCell Width="300px">Password</asp:TableCell>
                         <asp:TableCell Width="100px">Phone</asp:TableCell>
                     </asp:TableRow>
                 </asp:Table>
             </HeaderTemplate>
            <ItemTemplate>
                 <asp:Table ID="tbItem" runat="server">
                     <asp:TableRow>
                         <asp:TableCell Width="150px"><asp:Label ID="lblIDCard" runat="server" Text='<%#Eval("IDCard") %>'></asp:Label></asp:TableCell>
                         <asp:TableCell Width="100px"><%#Eval("name") %></asp:TableCell>
                         <asp:TableCell Width="300px"><%#Eval("pwd") %></asp:TableCell>
                         <asp:TableCell Width="100px"><%#Eval("phone") %></asp:TableCell>
                         <asp:TableCell>
                             <asp:LinkButton ID="lbtnView" runat="server" CommandArgument="ReadOnly" CommandName="Edit">查看</asp:LinkButton>
                         </asp:TableCell>
                         <asp:TableCell><asp:LinkButton ID="lbtnEdit" runat="server" CommandName="Edit" >编辑</asp:LinkButton></asp:TableCell>
                         <asp:TableCell><asp:LinkButton ID="lbtnRemove" runat="server" CommandName="Delete">删除</asp:LinkButton></asp:TableCell>
                     </asp:TableRow>
                 </asp:Table>
            </ItemTemplate>
           <EditItemTemplate>
                <asp:Table ID="tbEditItem" runat="server">
                     <asp:TableRow>
                         <asp:TableCell>IDCard:</asp:TableCell>
                         <asp:TableCell><asp:Label ID="lblIDCard" runat="server" Text='<%#Eval("IDCard") %>'></asp:Label></asp:TableCell>
                     </asp:TableRow>
                     <asp:TableRow>
                         <asp:TableCell>姓名:</asp:TableCell>
                         <asp:TableCell><asp:TextBox ID="txtName" runat="server" Text='<%#Eval("name") %>'></asp:TextBox></asp:TableCell>
                     </asp:TableRow>
                     <asp:TableRow>
                         <asp:TableCell>密码:</asp:TableCell>
                         <asp:TableCell><asp:TextBox ID="txtPwd" runat="server" Text='<%#Eval("pwd") %>' TextMode="password"></asp:TextBox></asp:TableCell>
                     </asp:TableRow>
                     <asp:TableRow>
                         <asp:TableCell>电话:</asp:TableCell>
                         <asp:TableCell><asp:TextBox ID="txtPhone" runat="server" Text='<%#Eval("phone") %>'></asp:TextBox></asp:TableCell>
                     </asp:TableRow>
                </asp:Table>
                 <asp:Table ID="tb" runat="server">
                     <asp:TableRow>
                         <asp:TableCell>
                             <asp:LinkButton ID="lbtnUpdate" runat="server" CommandName="Update">确定</asp:LinkButton>
                             <asp:LinkButton ID="lbtnCancel" runat="server" CommandName="Cancel">取消</asp:LinkButton>
                         </asp:TableCell>
                     </asp:TableRow>
               </asp:Table>
           </EditItemTemplate>
             <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
             <SelectedItemStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
             <AlternatingItemStyle BackColor="White" ForeColor="#284775" />
             <ItemStyle BackColor="#F7F6F3" ForeColor="#333333" />
             <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
         </asp:DataList>
         <asp:LinkButton ID="lbtnOutput" runat="server" OnClick="lbtnOutput_Click">输出到Excel</asp:LinkButton>
         <asp:Label ID="lblMessage" runat="server" Text="" ForeColor="Coral"></asp:Label>
       </div>
     </form>
</body>
</html>

MyTest.aspx.cs

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data.SqlClient;

namespace MyWeb
{
     public partial class MyTest : System.Web.UI.Page
     {
         private SqlConnection cn = sqlCon.con();
         private bool mbMainBind = false;

         protected void Page_Load(object sender, EventArgs e)
         {
             lblMessage.Text = "";
         }

         protected void dlMain_PreRender(object sender, EventArgs e)
         {
             if (!IsPostBack || mbMainBind) BindData();
             SetEditControls();
         }

         #region 函数

         private void SetEditControls()
         {
             if (dlMain.EditItemIndex == -1 || dlMain.Items.Count == 0) return;
             bool bReadOnly = Convert.ToBoolean(ViewState["EditReadOnly"]);
             DataListItem item = dlMain.Items[dlMain.EditItemIndex];
             SetChildControlsEnabled(item.FindControl("tbEditItem"), !bReadOnly);
             LinkButton lbtnUpdate = (LinkButton)item.FindControl("lbtnUpdate");
             lbtnUpdate.Enabled = !bReadOnly;

             TextBox txtPwd = (TextBox)item.FindControl("txtPwd");
             txtPwd.Attributes.Add("value", txtPwd.Text);

         }

         public static void SetChildControlsEnabled(Control ctlParent, bool bEnabled)
         {
             foreach (Control ctl in ctlParent.Controls)
             {
                 if (ctl is WebControl) ((WebControl)ctl).Enabled = bEnabled;
                 SetChildControlsEnabled(ctl, bEnabled);
             }
         }

         private void BindData()
         {
             cn.Open();
             string sSQL = "SELECT IDCard,name,pwd,phone FROM basic";
             SqlCommand cmd = new SqlCommand(sSQL, cn);
             SqlDataReader reader = cmd.ExecuteReader();
             dlMain.DataSource = reader;
             dlMain.DataBind();
             cn.Close();
         }

         #endregion

         protected void lbtnOutput_Click(object sender, EventArgs e)
         {
             Response.Clear();
             Response.Buffer = true;
             Response.Charset = "GB2312";
             Response.AppendHeader("Content-Disposition", "attachment;filename=Output.xls");
             Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
             Response.ContentType = "application/ms-excel";
             this.EnableViewState = false;
             System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN", true);
             StringWriter oStringWriter = new StringWriter(myCItrad);
             HtmlTextWriter oHtmlTextWriter = new HtmlTextWriter(oStringWriter);
             this.dlMain.RenderControl(oHtmlTextWriter);
             Response.Write(oStringWriter.ToString());
             Response.End();

         }

         #region 事件

         protected void dlMain_ItemDataBound(object sender, DataListItemEventArgs e)
         {
             if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem||e.Item.ItemType==ListItemType.EditItem)
             {
                 Label lblIDCard = (Label)e.Item.FindControl("lblIDCard");
                 lblIDCard.Attributes.Add("style", "vnd.ms-excel.numberformat:@");              
             }
             if (e.Item.ItemType == ListItemType.Item||e.Item.ItemType==ListItemType.AlternatingItem)
             {
                 LinkButton lbtnRemove = (LinkButton)e.Item.FindControl("lbtnRemove");
                 lbtnRemove.Attributes.Add("onclick", "return confirm('确定要删除该记录?!');");
             }
         }

         protected void dlMain_CancelCommand(object source, DataListCommandEventArgs e)
         {
             dlMain.EditItemIndex = -1;
             mbMainBind = true;
         }

         protected void dlMain_DeleteCommand(object source, DataListCommandEventArgs e)
         {
             cn.Open();
             string sIDCard = dlMain.DataKeys[e.Item.ItemIndex].ToString();
             string sSQL = "DELETE FROM basic WHERE IDCard=@IDCard";
             SqlCommand cmd = new SqlCommand(sSQL, cn);
             cmd.Parameters.AddWithValue("@IDCard", sIDCard);
             try
             {
                 cmd.ExecuteNonQuery();
                 lblMessage.Text = "删除成功！";
             }
             catch (Exception ex)
             {
                 lblMessage.Text = ex.Message;
             }
             finally
             {
                 cn.Close();
             }
             mbMainBind = true;
         }

         protected void dlMain_EditCommand(object source, DataListCommandEventArgs e)
         {
             dlMain.EditItemIndex = e.Item.ItemIndex;
             if (e.CommandArgument.ToString() == "ReadOnly")
                 ViewState["EditReadOnly"] = true;
             else
                 ViewState["EditReadOnly"] = false;
             mbMainBind = true;
         }

         protected void dlMain_UpdateCommand(object source, DataListCommandEventArgs e)
         {
             string sIDCard = dlMain.DataKeys[e.Item.ItemIndex].ToString();

             TextBox txtName = (TextBox)e.Item.FindControl("txtName");
             TextBox txtPwd = (TextBox)e.Item.FindControl("txtPwd");
             TextBox txtPhone = (TextBox)e.Item.FindControl("txtPhone");

             string sName = txtName.Text.Trim();
             string sPwd = FormsAuthentication.HashPasswordForStoringInConfigFile(txtPwd.Text.Trim(), "MD5");
             string sPhone = txtPhone.Text.Trim();

             cn.Open();
             string sSQL = "UPDATE basic SET name=@name,pwd=@pwd,phone=@phone WHERE IDCard=@IDCard";
             SqlCommand cmd = new SqlCommand(sSQL, cn);
             cmd.Parameters.AddWithValue("@name", sName);
             cmd.Parameters.AddWithValue("@pwd", sPwd);
             cmd.Parameters.AddWithValue("@phone", sPhone);
             cmd.Parameters.AddWithValue("@IDCard", sIDCard);

             try
             {
                 cmd.ExecuteNonQuery();
                 lblMessage.Text = "更新成功！";
                 dlMain.EditItemIndex = -1;
             }
             catch (Exception ex)
             {
                 lblMessage.Text = ex.Message;
             }
             finally
             {
                 cn.Close();
             }
             mbMainBind = true;
         }

         #endregion

     }
}


数据库设计：

create database information
use information
create table [basic]
(
[IDCard] nvarchar(18) primary key,
[name] nvarchar(50),
[pwd] nvarchar(50),
[phone] nvarchar(20)
)
go
 
 

Love1221
关注
专栏目录
DEV GridControl导入导出Excel文件 - 简单表格数据导入(NPOI方式三)
BaiCai的CSDN
08-02 837
1、命名空间 using System.IO; using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; 2、代码 #region NPOI方式 /// <summary> /// 本机无需安装Microsoft Excel ...
ASP.NET利用DataList实现从购物车添加商品
weixin_42978759的博客
12-08 2089
目录前言新的改变功能快捷键合理的创建标题，有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居右SmartyPants创建一个自定义列表如何创建一个注脚注释也是必不可少的KaTeX数学公式新的甘特图功能，丰富你的文章UML 图表FLowchart流程图导出与导入导出导入 前言 最近在练习ASP.NET，创建一个购物网站时用到...
poi导出数据详细教程
DT-TK
06-13 668
导出通用工具类Util import java.io.OutputStream; import java.lang.reflect.Method; import java.net.URLEncoder; import java.util.Collection; import java.util.Iterator; import javax.servlet.http.HttpServ...
DataReader与DataSet，DataGrid与DataList
weixin_30586257的博客
08-21 89
DataReader与DataSet有什么区别？ DataSet表示一个数据集，是数据在内存中的缓存。 可以包括多个表 DataSet 连接数据库时是非面向连接的。把表全部读到Sql中的缓冲池，并断开于数据库的连接 DataReader 连接数据库时是面向连接的。读表时，只能向前读取，读完数据后有用户决定是否断开连接。 分布式系统的数据可能会用dataset...
asp.net 数据导出到Excel
dengliuzhong1023的博客
12-12 85
在做asp.net程序时涉及到数据显示的时候多数会要求打印，而网页上的打印格式往往又不能满足需求，经常用的方法就是导入到Excel以后再进行打印。（仿佛这已经是老生常谈）今天在网上搜了一段打印的代码，觉得不错，需要打印的朋友可以看看。 网上好些代码的原理大致与此类似，同样都存在一个问题，就是： 类型“GridView”的控件“ctl00_center_GridView1”必须放在具有...
从通讯录中导入联系人并去重
人生若只如初见
04-21 9293
首先给出实现完的界面图 上面是给出来的实现结果图。 从本地联系人中导出联系人，需要使用ContentProvider来获取通讯录中的联系人。 从通讯录中获取联系人方法如下： /** 得到手机通讯录联系人信息 **/ private void getPhoneContacts() { ContentResolver resolver = ContactsAct
dataGridView导出Excel时报错：未将对象引用到对象实例
景月娇-Kathy
12-15 1640
错误提示： 查询一些机房重构的博客，导致这个问题出现可能是一下几种原因： 所设置的变量为空值或没有取到值，一般在传递参数的时候出现这个问题，也会在使用DateGird或gridview或datalist等数据空间时出现。 控件名称与codebehind里面的没有对应 未用new初始化对象 在程序中所引用的控件不存在 还查找其他一些地方：未将对象引用到对象实例，出现这种问题，一般是些什么原...
.NET 导出Excel
weixin_30851867的博客
07-26 57
ExportData类： View Code /// <summary> /// xxx /// 导出报表数据存入word或excel文件 /// 2012-03-21 /// </summary> public class ExportData { #region 构造函数 ...
ASP.NET下将Excel表格中的数据规则的导入数据库思路分析及实现
10-27
通过本分析，可以看出***在处理Excel数据导入导出方面提供了充分的技术支持，尽管没有内建直接读取Excel的功能，但借助额外的库和组件，开发者可以构建起强大的数据交换功能。同时，考虑到实际工作中的不同需求，...
ASP.NET高级编程：校园BBS里的数据绑定.pptx
05-25
XML文档可以导入DataSet对象，而DataSet对象中的数据也可以导出为XML文档。 ;;7;查看用户信息表中前5条记录信息？;9;10;11;案例解析; 单值数据绑定主要应用在数据绑定控件上。ASP.NET对具有 DataBinding 事件的对象...
第7章 数据绑定控件
05-27
实例225 将GridView控件的数据导出到Excel 并进行格式化 355 实例226 将Excel中数据导入到Aceess 并绑定GridView 356 实例227 将GridView中绑定的Access数据 生成XML 358 实例228 嵌套购物车（可增减商品数并显示 ...
asp.net 导出excel
Devil Angel
10-09 752
asp.net 导出excel的个人使用方式
已有打开的与此命令相关联的 DataReader，必须首先将它关闭
11-01 3390
原因：同时打开建立两个DataReader,而采用同一个SqlConnection(或OleDbConnection );Command(或OleDbCommand )会造成这种结果。解决方法: 两个DataReader用不同的SqlConnection，Command;注意: DataReader依托于SqlConnection,如果SqlConnection关闭了,DataReader
图片边框样式
06-23 3291
1:/*图片边框 */{border-top-width: 4px;border-right-width: 4px;border-bottom-width: 4px;border-left-width: 4px;border-top-style: solid;border-right-style: solid;border-bottom-style: solid;border-left-sty
GridView中点击删除按钮弹出对话框
06-29 2192
 点击删除按钮弹出对话框: asp:GridView ID="TopicView" Width="100%" runat="server" AutoGenerateColumns="False"                    BorderColor="#3B6BD1" BorderWidth="1px" BackColor="White"                    
维持Session时间，页面定时刷新
09-01 1529
由于session的时间维持的比较短，在这里我用定时刷新的方法向服务器端发送请求。以保存session的状态。 a.aspx    function makeRequest()     {        var guid = rnd();                //alert(guid);                //记住给个随机数，不然的话有缓存        var
过滤SQL关键字,过滤脏话!
06-13 1393
//替代SQL//输入时替代        public string  InCheckStr(string str)        {            if (str != null)            {                int i;                string fy_in, fy_out;                fy_in = "exec|in
无代码操作：GridView数据导入导出与高级功能示例
在本文档中，我们将深入探讨GridView控件在ASP.NET Web开发中的数据导入导出功能以及相关操作。GridView是ASP.NET中用于显示和处理大量数据的一种常见的表格控件，它提供了丰富的功能，包括但不限于： 1. **无代码...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值