CVS文件导入导出

 

public partial class Salepromotion_SendCoupon : System.Web.UI.Page
{
    DevExpress.Xpo.Session s;

    protected void Page_Init(object sender, EventArgs e)
    {
        s = EC.Module.XpoHelper.GetNewSession();
    }
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    protected void ASPxButton1_Click(object sender, EventArgs e)
    {
        Response.BufferOutput = false;
        Response.Cache.SetCacheability(HttpCacheability.NoCache);
        Stream stream = Request.Files[0].InputStream;
        StreamReader sdr = new StreamReader(stream, Encoding.Default);

        using (CsvReader csv = new CsvReader(sdr, true))
        {
            InPutCoupon(csv);
        }
    }

    private void ShowClientMsgFlow(string msg, string color)
    {
        Response.Write("<div style='color:" + color + ";font-size:14px; padding-left:30px;'>" + HttpUtility.HtmlEncode(msg).Replace(Environment.NewLine, "<br/>") + "</div>");
        Response.Write("<script stype='text/javascript'>window.scrollTo(0,999999);</script>");
        Response.Flush();
    }

    void InPutCoupon(CsvReader csv)
    {
        string FileName = Request.Files[0].FileName.Substring(Request.Files[0].FileName.LastIndexOf('\\') + 1);
        ViewState["Name"] = Request.Files[0].FileName.Substring(Request.Files[0].FileName.LastIndexOf('\\') + 1);
        //判断改文件名是否存在过
        string Sql = "select  count(*) from [SendCouponLog] where FileNames='" + FileName + "'";
        Object ob = MSSqlHelper.GetScalar(Sql);
        if (ob != null)
        {
            if (Convert.ToInt32(ob.ToString()) > 0)
            {
                ShowClientMsgFlow("该文件名已经导入过", "");
                return;
            }
        }

        int i = 2;
        string strcount = "";
        int count = 0;
        List<string> list = new List<string>();
        try
        {
            foreach (var item in csv)
            {
                string str = "";
                str += "正在导入第" + i + "行...\r\n";

                if (item[0] != "" && item[1] != "")
                {
                    if (item[0] == "")
                    {
                        str += "第" + i + "行手机号码不能为空！\r\n";
                    }
                    else if (item[0].ToString().Length > 11)
                    {
                        str += "第" + i + "行手机号码格式不正确！\r\n";
                    }
                    else if (item[1] == "")
                    {
                        str += "第" + i + "行优惠券类型不能为空！\r\n";
                    }
                    else
                    {
                        try
                        {
                            Coupon c = s.FindObject<Coupon>(DevExpress.Data.Filtering.CriteriaOperator.Parse("Oid=?", item[1]));
                            if (c == null)
                            {
                                str += "第" + i + "行优惠券类型错误 不能生成！\r\n";
                                i = i + 1;
                                list.Add(str);
                                continue;
                            }
                            else
                            {
                                CouponDetail coupondetail = new CouponDetail(s);
                                if (c.CoupontimeType == CouponTimeType.按时间范围)
                                {
                                    str += "第" + i + "行优惠券类型不为发放后多少天生效的 不能生成！\r\n";
                                }
                                if (c.CoupontimeType == CouponTimeType.发放后多少天有效)
                                {
                                    Sql = "INSERT INTO  [dbo].[SendCouponLog]([CreatedDate],[CreatedUser],coupon ,[MobilePhone],[FileNames],[Number]) VALUES";
                                    Sql += "('" + DateTime.Now + "','" + ECMembership.Current.LoginUserInfo.LoginName + "'," + item[1] + ",'" + item[0] + "','" + FileName + "','')";
                                   
                                }
                                try
                                {
                                    MSSqlHelper.Execute(Sql);
                                    if (!str.Equals("正在导入第" + i + "行...\r\n"))
                                    {
                                        str += "导入第" + i + "行失败！\r\n\r\n";
                                        i = i + 1;
                                        list.Add(str);
                                        continue;
                                    }
                                    else
                                    {
                                        strcount += item[0] + "\r\n";
                                        str += "导入第" + i + "行成功！\r\n\r\n";
                                        i += 1;
                                        count += 1;
                                        list.Add(str);

                                    }
                                }
                                catch
                                {
                                    str += "第" + i + "生成失败!\r\n";
                                }

                            }
                        }
                        catch (Exception)
                        {
                            str += "第" + i + "行优惠券类型错误 不能生成！\r\n";
                        }
                    }
                }
                else
                {
                    str += "第" + i + "行 手机号码或优惠券类型不能为空，不用导入！\r\n\r\n";
                    i += 1;
                    list.Add(str);
                    continue;
                }
            }
            if (list.Count > 0)
            {
                this.Button1.Visible = true;
                string ss = "";
                foreach (string item in list)
                {
                    ss += item;
                }
                this.ASPxButton1.Enabled = true;
                this.ASPxButton1.Text = "上传";
                throw new EC.Common.ECException(ss);
            }
            else
            {
                throw new EC.Common.ECException("没有发现要导入的记录！");
            }
        }
        catch (EC.Common.ECException ex)
        {
            this.ASPxButton1.Enabled = true;
            ShowClientMsgFlow(ex.Message, "");
            ShowClientMsgFlow("本次共导入" + count + "行\r\n" + strcount, "");
        }
        catch (Exception exx)
        {
            this.ASPxButton1.Enabled = true;
            ShowClientMsgFlow(exx.Message, "");
            ShowClientMsgFlow("本次共导入" + count + "行\r\n" + strcount, "");
        }
       
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        string[] ExportHead = { "手机号码", "号码" };
        CSVWriter csvWriter = new CSVWriter();
        csvWriter.WriteHead(ExportHead);
        string Sql = "  select Oid, coupon ,[MobilePhone] from [SendCouponLog] where FileNames='" + ViewState["Name"].ToString() + "'";
        DataTable dt = MSSqlHelper.GetDataSet(Sql).Tables[0];
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            string Number = "";
            for (int j = 0; j < 1; j++)
            {
                Number = "A" + Guid.NewGuid().ToString("N").ToUpper().Substring(0, 9);
                Sql = "insert into [CouponDetail] ([CreateTime],[Coupon],[Status],[Number]) values (GETDATE()," + dt.Rows[i]["coupon"] + ",0,'" + Number + "')";
                try
                {
                    MSSqlHelper.Execute(Sql);
                    string Update = "update [SendCouponLog] set [Number]='" + Number + "'  where oid=" + dt.Rows[i]["Oid"].ToString();
                    MSSqlHelper.Execute(Update);

                }
                catch
                {
                    j = 0;
                }
            }
            string[] ExportContent = { dt.Rows[i]["MobilePhone"].ToString(), Number };
            csvWriter.WriteRow(ExportContent);

        }
        string CVSCotent = csvWriter.GetCSVContent();

        Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode("生成后" + ViewState["Name"].ToString()));
        Response.ContentType = "application/ms-excel";
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        Response.Write(CVSCotent);
        Response.End();
    }

}