数据的导入导出操作

一:数据导出EXCEL

1.要有个数据集,也便输出到Excel中:  

        DataSet ds;
        int custId;
        try
        {
            custId = Convert.ToInt32(txtCustId.Text);
        }
        catch
        {
            custId = -1;
        }
        vmc = new VIPManagerClass(connection());
        vmc.get_CustBaseinfo(custId, this.textName.Text.Trim(), this.ddlCusttype.SelectedValue, "", this.ddlCustLevel.SelectedValue, "", "", out ds);
        if (ds.Tables[0].Rows[0][0].ToString() == "0")
        {
            this.gvVip.DataSource = ds.Tables[1];
            this.gvVip.DataBind();
        }
        string endExcel = "";
        try
        {
            endExcel =

                                "<tr><td>用户ID</td><td>用户名</td><td>客户类型</td><td>证件类型</td><td>证件号码</td><td>证件发                               放  机构地址</td><td>客户等级</td><td>性别</td><td>出生日期</td><td>省份</td><td>城市</td><td>联                                系地址</td><td>传真号码</td><td>电话号码</td><td>移动电话                                </td><td>Emial</td><td>QQ</td><td>MSN</td><td>公司名称</td><td>注册时间</td><td>收费开始日期                                    </td><td>收费结束日期</td><td>VIP开始日期</td><td>VIP结束日期</td><td>备注</td></tr>";

            foreach (DataRow dr in ds.Tables[1].Rows)
            {
                endExcel = endExcel + "<tr><td>" + dr["Custid"].ToString() + " </td><td>" + dr["Custname"].ToString() + "</td><td>"+dr["Custtypename"].ToString()+"</td>" + "<td>" + dr["Idtypename"].ToString() + "</td>" + " <td>" + dr["Idno"].ToString() + "</td>" + "<td>" + dr["Idaddr"].ToString() + "</td>" + " <td>" + dr["CustLevelname"].ToString() + "</td>" + " <td>" + dr["sexname"].ToString() + "</td>" + " <td>" + dr["Birthday"].ToString() + "</td>" + " <td>" + dr["ProvinceName"].ToString() + "</td>" + "<td>" + dr["CityName"].ToString() + "</td>" + "<td>" + dr["Addr"].ToString() + "</td>" + " <td>" + dr["Faxno"].ToString() + "</td>" + "<td>" + dr["Telno"].ToString() + "</td>" + " <td>" + dr["Mobileno"].ToString() + "</td>" + "<td>" + dr["Email"].ToString() + "</td>" + " <td>" + dr["Qq"].ToString() + "</td>" + "<td>" + dr["Msn"].ToString() + "</td>" + "<td>" + dr["Company"].ToString() + "</td>" + "<td>" + dr["Regeditdatetime"].ToString() + "</td>" + "<td>" + dr["FeeStartdate"].ToString() + "</td>" + "<td>" + dr["FeeEnddate"].ToString() + "</td>" + "</td>" + "<td>" + dr["VipStartdate"].ToString() + "</td>" + "<td>" + dr["VipEnddate"].ToString() + "</td>" + "<td>" + dr["Remark"].ToString() +"<td></tr>";
            }

            tryToExcel(this.gvVip, "UserDetai.xls", "<div><table>" + endExcel + "</table></div>");
        }
        catch (Exception e1)
        {
            alert(e1.Message, "alert");
        }

//写入到Excel中的方法

 public static void tryToExcel(System.Web.UI.Control ctl, string file_name, string tryexcel)
    {
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.Buffer = true;
        HttpContext.Current.Response.Charset = "gb2312";
        HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + file_name);
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
        HttpContext.Current.Response.ContentType = "application/ms-excel";

        ctl.Page.EnableViewState = false;
        System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("zh-CN", true);
        System.IO.StringWriter tw = new System.IO.StringWriter(myCItrad);
        System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);

        ctl.RenderControl(hw);

        Console.WriteLine(tw.ToString());

        HttpContext.Current.Response.Write(tryexcel);
        HttpContext.Current.Response.End();
    }

3.最后加上

 public override void VerifyRenderingInServerForm(Control control)
    {
    }

和EnableEventValidation="false"

 

二：Excel导入到数据库中

        vmc = new VIPManagerClass(connection());
        DataSet ds1;
        vmc.delete_AllCustVisitinfo(out ds1);
        if (ds1.Tables[0].Rows[0][0].ToString() != "0")
        {
            return;
        }
        string fileType = string.Empty;
        fileType = FileUpload1.FileName.Substring(FileUpload1.FileName.IndexOf(".") + 1);
        if (fileType.Trim().ToLower() != "xls")
        {
            alert("你导入的文件不是Excel文件!", "alert");
            return;
        }
        string fileName = Server.MapPath(FileUpload1.FileName);
        string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fileName + ";" + "Extended Properties=Excel 8.0;";
        OleDbConnection objConn = new OleDbConnection(sConnectionString);
        objConn.Open();

        OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [Sheet1＄]", objConn);
        OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
        objAdapter1.SelectCommand = objCmdSelect;

        DataSet objDataset1 = new DataSet();
        objAdapter1.Fill(objDataset1);
        DataTable dt = objDataset1.Tables[0];
     
        int count = 0;//用来记录出错的条数
        DataSet ds;
        try
        {
            foreach (DataRow myDrv in dt.Rows)
            {
                count++;
                vmc.insert_CustVisitinfo(myDrv[0].ToString(),"", "","","","", Convert.ToString(myDrv[1]), "",Convert.ToString(myDrv[2]),Convert.ToString(myDrv[3]),Convert.ToString(myDrv[4]), Convert.ToString(myDrv[5]), Convert.ToString(myDrv[6]), Convert.ToString(myDrv[7]), Convert.ToString(myDrv[8]), Convert.ToString(myDrv[9]), Convert.ToString(myDrv[10]), Convert.ToString(myDrv[11]), Convert.ToString(myDrv[12]), Convert.ToString(myDrv[13]), Convert.ToString(myDrv[14]),Convert.ToString(myDrv[15]), out ds);
            }
        }
        catch
        {
            alert("第" + count.ToString() + "条数据导入出错", "alert");
            objConn.Close();//关闭EXCEL的连接
        }
        alert("数据导入成功!", "alert");