C# 导出excel

  /// <summary>
        /// DataTable导出到Excel
        /// </summary>
        /// <param name="dt">DataTable类型的数据源</param>
        /// <param name="FileType">文件类型</param>
        /// <param name="FileName">文件名</param>
        public void CreateExcel(DataTable dt, string FileType, string FileName)
        {
            Response.Clear();
            Response.Charset = "UTF-8";
            Response.Buffer = true;
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            Response.AppendHeader("Content-Disposition", "attachment;filename=\"" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls\"");
            Response.ContentType = FileType;
            string colHeaders = string.Empty;
            string ls_item = string.Empty;
            DataRow[] myRow = dt.Select();
            int i = 0;
            int cl = dt.Columns.Count;
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                ls_item += dt.Columns[j].ColumnName + "\t"; //栏位:自动跳到下一单元格
            }
            ls_item = ls_item.Substring(0, ls_item.Length - 1) + "\n";
            foreach (DataRow row in myRow)
            {
                for (i = 0; i < cl; i++)
                {
                    if (i == (cl - 1))
                    {
                        ls_item += row[i].ToString() + "\n";
                    }
                    else
                    {
                        ls_item += row[i].ToString() + "\t";
                    }
                }
                Response.Output.Write(ls_item);
                ls_item = string.Empty;
            }
            Response.Output.Flush();
            Response.End();
        }

调用方法:

            string sql = "SELECT hy_no '促销员编号',UserName '促销员姓名',in_date '日期',(SELECT jpname FROM BasJp WHERE fid=t1.Jp_id) '奖品名称',jf '积分',amount '数量',linktell '联系方式',linkaddr '联系地址',CASE WHEN status = 1 THEN '待确认' WHEN status = 4 THEN '已确认' ELSE '其他' END '状态'   FROM hy_djjl t1 order BY in_date DESC";
            DataSet ds = DbHelperSQL.Query(sql);
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                CreateExcel(dt, "application/ms-excel", "兑奖记录-" + DateTime.Now.ToString("yyyy-MM-dd HHmmss") + ".xls");//调用函数
            }

 

posted @ 2018-09-10 15:24  韩梦芫  阅读(233)  评论(0编辑  收藏  举报