程序博客网 > 网络彩票概念股

excel导入与导出数据到excel .net2005

来源：互联网 发布：网络彩票概念股 编辑：程序博客网 时间：2024/04/30 15:18

/*************************************************/

以下是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.Data.OleDb;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            BindData();
        }

    }

    /// <summary>
    /// 查询数据并显示出来
    /// </summary>
    private void BindData()
    {
        try
        {
            SqlConnection cn = new SqlConnection(ConfigurationManager.AppSettings["connectString"]);
            cn.Open();
            SqlDataAdapter sda = new SqlDataAdapter("select * from hhaspx_gz", cn);
            SqlDataAdapter da = new SqlDataAdapter("select * from hhaspxinfo",cn);
            DataSet ds = new DataSet();
            DataSet dss = new DataSet();
            sda.Fill(ds, "hhaspx_gz");
            da.Fill(dss, "hhaspxinfo");
            this.GridView1.DataSource = ds.Tables["hhaspx_gz"];
            this.GridView1.DataKeyNames = new string[] { "hhaspx_id" };
            this.GridView1.DataBind();

            this.GridView2.DataSource = dss.Tables["hhaspxinfo"];
            this.GridView2.DataKeyNames = new string[] { "id"};
            this.GridView2.DataBind();
            cn.Dispose();
        }
        catch (Exception err)
        {
            throw(err);
        }
    }

    /// <summary>
    /// 查询EXCEL电子表格添加到DATASET
    /// </summary>
    /// <param name="filenameurl">服务器路径</param>
    /// <param name="table">表名</param>
    public DataSet ExecleDs(string filenameurl, string table)
    {
        string strConn = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filenameurl + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
        OleDbConnection conn = new OleDbConnection(strConn);
        conn.Open();
        DataSet ds = new DataSet();
        OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);
        odda.Fill(ds, table);
        return ds;
    }
    /// <summary>
    /// excel导入到数据库
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Button1_Click(object sender, EventArgs e)
    {
        if (FileUpload1.HasFile == false)//HasFile用来检查FileUpload是否有指定文件
        {
            Response.Write("<script>alert('请您选择Excel文件')</script> ");
            return;//当无文件时,返回
        }
        string IsXls = System.IO.Path.GetExtension(FileUpload1.FileName).ToString().ToLower();//System.IO.Path.GetExtension获得文件的扩展名
        if (IsXls != ".xls")
        {
            Response.Write("<script>alert('只可以选择Excel文件')</script>");
            return;//当选择的不是Excel文件时,返回
        }
        SqlConnection cn = new SqlConnection(ConfigurationManager.AppSettings["connectString"]);
        cn.Open();
        string filename = DateTime.Now.ToString("yyyymmddhhMMss") + FileUpload1.FileName;              //获取Execle文件名  DateTime日期函数
        string savePath = Server.MapPath(("~//upfiles//") + filename);//Server.MapPath 获得虚拟服务器相对路径
        FileUpload1.SaveAs(savePath);                        //SaveAs 将上传的文件内容保存在服务器上
        DataSet ds = ExecleDs(savePath, filename);           //调用自定义方法
        DataRow[] dr = ds.Tables[0].Select();            //定义一个DataRow数组
        int rowsnum = ds.Tables[0].Rows.Count;
        if (rowsnum == 0)
        {
            Response.Write("<script>alert('Excel表为空表,无数据!')</script>");   //当Excel表为空时,对用户进行提示
        }
        else
        {
            for (int i = 0; i < dr.Length; i++)
            {
                string hhaspx_rq = dr[i]["日期"].ToString();//日期 excel列名【名称不能变,否则就会出错】
                string hhaspx_bh = dr[i]["编号"].ToString();//编号 列名 以下类似
                string hhaspx_xm = dr[i]["姓名"].ToString();
                string hhaspx_xb = dr[i]["性别"].ToString();
                string hhaspx_dx = dr[i]["底薪"].ToString();
                string hhaspx_kh = dr[i]["考核"].ToString();
                string hhaspx_jl = dr[i]["奖励"].ToString();
                string hhaspx_jt = dr[i]["津贴"].ToString();
                string hhaspx_jb = dr[i]["加班"].ToString();
                string hhaspx_zb = dr[i]["值班"].ToString();
                string hhaspx_jx = dr[i]["绩效"].ToString();
                string hhaspx_hj = dr[i]["合计"].ToString();

                string sqlcheck = "select count(*) from hhaspx_gz where hhaspx_rq='" + hhaspx_rq + "'And hhaspx_xm='" + hhaspx_xm + "'";  //检查用户是否存在
                SqlCommand sqlcmd = new SqlCommand(sqlcheck, cn);
                int count = Convert.ToInt32(sqlcmd.ExecuteScalar());
                if (count < 1)
                {
                    string insertstr = "insert into hhaspx_gz (hhaspx_rq,hhaspx_bh,hhaspx_xm,hhaspx_xb," +
                    "hhaspx_dx,hhaspx_kh,hhaspx_jl,hhaspx_jt,hhaspx_jb,hhaspx_zb,hhaspx_jx,hhaspx_hj) "+
                    "values('" + hhaspx_rq + "','" + hhaspx_bh + "','" + hhaspx_xm + "','" + hhaspx_xb + "',"+
                    "'" + hhaspx_dx + "','" + hhaspx_kh + "','" + hhaspx_jl + "','" + hhaspx_jt + "',"+
                    "'" + hhaspx_jb + "','" + hhaspx_zb + "','" + hhaspx_jx + "','" + hhaspx_hj + "')";
                    SqlCommand cmd = new SqlCommand(insertstr, cn);
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (MembershipCreateUserException ex)       //捕捉异常
                    {
                        Response.Write("<script>alert('导入内容:" + ex.Message + "')</script>");
                    }
                }
                else
                {
                    Response.Write("<script>alert('内容重复！禁止导入');location='default.aspx'</script></script> ");
                    continue;
                }
            }
            Response.Write("<script>alert('Excle表导入成功!');location='default.aspx'</script>");
        }
        cn.Close();
    }

    /// <summary>
    /// 导出数据
    /// </summary>
    /// <param name="gd">选择导出是哪个GridView1列表</param>
    private void gridViewExport(GridView gd)
    {
        if (gd.Rows.Count != 0)
        {
            Response.Clear();
            Response.BufferOutput = true;
            //设定输出的字符集
            Response.Charset = "GB2312";
            Response.AppendHeader("Content-Disposition", "attachment;filename=" + System.DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            //设置导出文件的格式
            Response.ContentType = "application/ms-excel";
            //关闭ViewState
            EnableViewState = false;
            System.Globalization.CultureInfo cultureInfo = new System.Globalization.CultureInfo("ZH-CN", true);
            System.IO.StringWriter stringWriter = new System.IO.StringWriter(cultureInfo);
            System.Web.UI.HtmlTextWriter textWriter = new System.Web.UI.HtmlTextWriter(stringWriter);
            gd.RenderControl(textWriter);
            //把HTML写回浏览器
            Response.Write(stringWriter.ToString());
            Response.End();
        }
        else
        {
            //此方式可以避免关闭提示后页面变形
            Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "aler", "<script language=javascript>alert('没有要导出的数据');</script>");
        }
    }
    /// <summary>
    /// 导出数据到excel
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Export_Click(object sender, EventArgs e)
    {
        #region
        //Response.Clear();
        //Response.Buffer = true;
        //Response.Charset = "GB2312"; //设置了类型为中文防止乱码的出现
        //Response.AppendHeader("Content-Disposition", "attachment;filename=" + System.DateTime.Now.ToString("yyMMdd.ss") + ".xls"); //定义输出文件和文件名
        //Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
        //Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
        //this.EnableViewState = false;
        //System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN", true);
        //System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
        //System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
        //this.GridView1.RenderControl(oHtmlTextWriter);
        //Response.Write(oStringWriter.ToString());
        #endregion
        gridViewExport(this.GridView1);
        gridViewExport(this.GridView2);
    }

    /// <summary>
    /// 导出用到的方法
    /// </summary>
    /// <param name="control"></param>
    public override void VerifyRenderingInServerForm(Control control)
    {
        //注释掉下面的代码，否则在asp.net2.0下会报错（注：GridView是asp.net 2.0下的控件，1.1下一些控件也可以导出成Excel或者Word）
        //base.VerifyRenderingInServerForm(control);
    }
}  

======================================================================

/*************************************************/

以下是aspx代码

/*************************************************/

<%@ Page Language="C#"  AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!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>Excel导入导出SQL数据库</title>
</head>
<body style="text-align: center">
<br /><br /><br /><br />
    <form id="form1" runat="server">
    <div>
        <table style="width: 576px; border-collapse: separate; text-align: center">
            <tr>
                <td colspan="3">
                    &nbsp;Excel导入导出SQL数据库</td>
            </tr>
            <tr>
                <td colspan="3" style="text-align: left">
                    <asp:FileUpload ID="FileUpload1" runat="server" Width="305px" />
                    &nbsp; &nbsp;
                    <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="导入SQL" />&nbsp; &nbsp;
                    <asp:Button ID = "Export" runat="server" Text = "导出Excel" OnClick="Export_Click" />
                    </td>
            </tr>
            <tr>
                <td colspan="3">
                    <asp:GridView ID="GridView1" runat="server" Height="133px" Width="100%">
                    </asp:GridView>
                </td>
            </tr>
            <tr>
                <td colspan="3">
                    <asp:GridView ID="GridView2" runat="server" Height="133px" Width="100%">
                    </asp:GridView>
                </td>
            </tr>
            <tr>
                <td>
                </td>
                <td>
                </td>
                <td style="width: 189px">
                </td>
            </tr>
        </table>
   
    </div>
    </form>
</body>
</html>

网络彩票概念股 网络彩票概念股
原创粉丝点击
热门IT博客
php 数组合并 不覆盖php 数组合并 不覆盖
大数据的利与弊大数据的利与弊
linux 脚本
褚遂良行书字帖知乎
ps for mac 序列号
自行车配件网上淘宝店
网络教育文凭社会认可
数据库投影
dreamweaver如何使用js
淘宝如何做虚拟产品
linux获取网卡mac地址
软件开发技术文档
吉永小百合知乎
基于icmp协议网络编程
你的名字 知乎
运营数据分析报告ppt
安卓旗舰机 知乎
做淘宝快递怎么便宜
开源视频监控系统java
3dm 知乎
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 娄师德 另娄文字图片 另娄图片第29页 沉清辞娄紫茵 娄瑾玉邵瑀辰免费阅读 另娄图片88 深清辞娄紫茵 娄双大道准确通车时间 娄飞雪 娄醴高速公路 娄家嫡女娄雪飞 娄一晨 婁 沈清辞娄紫茵全文免费阅读 人娄一败涂地手机版下载 娄爷我错了全文免费阅读 娄圭 娄姓起名 姓娄的男孩名字 娄姓女孩起名 娄姓取名 娄姓 娄婧 冰冷少帅荒唐娄 vide0s9ratistv另娄 娄字怎么读 娄底 娄底新化 娄底涟源 娄底双峰 湖南娄底 娄底市 娄底房价 娄底旅游 娄底二手房 娄底邮编 长沙娄底 娄底景点 娄底房产 娄底站 娄底人才网

程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里