根据高级查询导出excel

protected void ImageButton7_Click(object sender, ImageClickEventArgs e)
        {
            string D_where = string.Empty;
            if (string.IsNullOrEmpty(txtStart.Value) == false)
            {
                D_where += " and to_char(S_IN_DATE,'yyyy-mm-dd')>='" + txtStart.Value + "'";
            }
            if (string.IsNullOrEmpty(txtEnd.Value) == false)
            {
                D_where += " and to_char(S_IN_DATE,'yyyy-mm-dd')<='" + txtEnd.Value + "'";
            }
            if (DropDownList1.SelectedValue != "0000" && DropDownList1.SelectedValue != "")
            {
                D_where += " and F_SALEOUT_O='" + DropDownList1.Items[DropDownList1.SelectedIndex].Text + "'";
            }
            if (DropDownList2.SelectedValue != "0000" && DropDownList2.SelectedValue != "")
            {
                D_where += " and F_SALEOUT_T='" + DropDownList2.Items[DropDownList2.SelectedIndex].Text + "'";

            }


            string sql = @"select XS_DATE 销售日期,F_ORDERID 服务单号,G_NAME 被保人姓名,G_CARDNO 被保人身份证号,F_ENSURE 被保金额,F_SERVICE 服务费用,MEDICAL_COST 医疗成本,case when (F_SERVICE * 0.942) - (F_ENSURE * (MEDICAL_COST / 100)) - 400 - ((F_ENSURE * 60) / 3000)>0 then (F_SERVICE * 0.942) - (F_ENSURE * (MEDICAL_COST / 100)) - 400 - ((F_ENSURE * 60) / 3000) else 0 end  合作推广费用小,(1 - (1 / (F_ENSURE / 3000))) * ((F_SERVICE * 0.942) - (F_ENSURE * (MEDICAL_COST / 100))) + 200 合作推广费用大,((1 - (1 / (F_ENSURE / 3000))) * ((F_SERVICE * 0.942) - (F_ENSURE * (MEDICAL_COST / 100))) + 200)-(case when (F_SERVICE * 0.942) - (F_ENSURE * (MEDICAL_COST / 100)) - 400 - ((F_ENSURE * 60) / 3000)>0 then (F_SERVICE * 0.942) - (F_ENSURE * (MEDICAL_COST / 100)) - 400 - ((F_ENSURE * 60) / 3000) else 0 end) 差价
  FROM ACCEPTANCE A, U_PROFIT U, Y_ORDERS O,MEDICALTYPE M
 WHERE A.U_PROFIT_ID = U.ID AND O.ID = A.ORDER_ID and O.f_saleout_stype=m.medical_id and  S_STUSES in (2) " + D_where + "";
            DataTable dt = DBUtility.DBHelper.GetDataTable(sql);

            string sql2 = @"select  sum(F_ENSURE) a,sum(F_SERVICE) b,sum(case when (F_SERVICE * 0.942) - (F_ENSURE * (MEDICAL_COST / 100)) - 400 - ((F_ENSURE * 60) / 3000)>0 then (F_SERVICE * 0.942) - (F_ENSURE * (MEDICAL_COST / 100)) - 400 - ((F_ENSURE * 60) / 3000) else 0 end) c,sum((1 - (1 / (F_ENSURE / 3000))) * ((F_SERVICE * 0.942) - (F_ENSURE * (MEDICAL_COST / 100))) + 200) d,sum(((1 - (1 / (F_ENSURE / 3000))) * ((F_SERVICE * 0.942) - (F_ENSURE * (MEDICAL_COST / 100))) + 200)-(case when (F_SERVICE * 0.942) - (F_ENSURE * (MEDICAL_COST / 100)) - 400 - ((F_ENSURE * 60) / 3000)>0 then (F_SERVICE * 0.942) - (F_ENSURE * (MEDICAL_COST / 100)) - 400 - ((F_ENSURE * 60) / 3000) else 0 end)) e
  FROM ACCEPTANCE A, U_PROFIT U, Y_ORDERS O,MEDICALTYPE M
 WHERE A.U_PROFIT_ID = U.ID AND O.ID = A.ORDER_ID and O.f_saleout_stype=m.medical_id and  S_STUSES in (2) " + D_where + "";
            DataTable num = DBUtility.DBHelper.GetDataTable(sql2);

            DataRow dr = dt.NewRow();
            dr[dt.Columns.Count - 9] = "合计(￥)：";
            dr[dt.Columns.Count - 6] = num.Rows[0]["a"].ToString();
            dr[dt.Columns.Count - 5] = num.Rows[0]["b"].ToString();
            dr[dt.Columns.Count - 3] = num.Rows[0]["c"].ToString();
            dr[dt.Columns.Count - 2] = num.Rows[0]["d"].ToString();
            dr[dt.Columns.Count - 1] = num.Rows[0]["e"].ToString();
            dt.Rows.Add(dr);

            ExcelImport(dt, @"HZFYTGDZDExl");
           

          
        }
        private StringWriter GetStringWriter(DataTable dt)
        {
            StringWriter sw = new StringWriter();
            //读列名   
            foreach (DataColumn dc in dt.Columns)
                sw.Write(dc.ColumnName + "\t");

            //读列值   
            //重新的一行   
            sw.Write(sw.NewLine);
            if (dt != null)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        sw.Write(dr[i].ToString() + "\t");
                    }
                    sw.Write(sw.NewLine);
                }
            }
            sw.Close();

            return sw;
        }

        protected void ExcelImport(DataTable dt, string ExportFileName)
        {
            StringWriter sw = GetStringWriter(dt);
            //当前编码   
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            //把输出的文件名进行编码   
            string fileName = HttpUtility.UrlEncode(ExportFileName, System.Text.Encoding.UTF8);
            //文件名   
            string str = "attachment;filename=" + fileName + ".xls";
            //  sw.ContentType = "application/vnd.ms-excel";
            //把文件头输出，此文件头激活文件下载框   
            HttpContext.Current.Response.AppendHeader("Content-Disposition", str);//http报头文件   
            HttpContext.Current.Response.ContentType = "application/ms-excel";
            this.Page.EnableViewState = false;

            Response.Write(sw);
            Response.End();
        }

 

DataTable.NewRow()用来创建一个新的Row，为什么还要加回自己dt.Rows.Add()?

2014-08-27 20:08

既然DataRow类型本身的构造函数是不可public访问的，因此必须得像下面这样来创建一个DataRow:

DataTable dt = new DataTable("t1");
DataRow dr = dt.NewRow();
dt.Rows.Add(dr);