导出Excel后台代码

/// <summary>
        /// 生成Excel并提示下载
        /// </summary>
        /// <param name="page">this.Page</param>
        /// <param name="TemplateName">模版Excel文件名</param>
        /// <param name="FileName">保存到本地的文件名</param>
        /// <param name="Dt">数据源</param>
        public static void OutputExcelX(Page page, string TemplateName, string FileName, DataTable Dt)
        {
            string tfilename = "file" + Comment.Handle.GetRndStr();
            string filePath = page.Server.MapPath("~/Temp/" + tfilename + ".xls");
            //删除已经存在的副本
            try
            {
                File.Delete(tfilename);
            }
            catch
            { }
            //从模版复制副本
            File.Copy(page.Server.MapPath("~/Template/" + TemplateName + ".xls"), filePath);
            //连接Excel
            OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=2;Connect Timeout=0;'");
            conn.Open();

            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandTimeout = 5000;
            //输出内容
            StringBuilder sb = new StringBuilder();
            foreach (DataRow dr in Dt.Rows)
            {
                sb.Clear();
                for (int i = 0; i < Dt.Columns.Count; i++)
                {
                    Type tpdrcolumn = dr[i].GetType();
                    if (tpdrcolumn == System.Type.GetType("System.Int32") || tpdrcolumn == System.Type.GetType("System.Double") || tpdrcolumn == System.Type.GetType("System.Decimal"))
                    {
                        if (i == 0)
                            sb.Append(System.Web.HttpContext.Current.Server.HtmlEncode(dr[i].ToString()));
                        else
                            sb.Append("," + System.Web.HttpContext.Current.Server.HtmlEncode(dr[i].ToString()));
                    }
                    else
                    {
                        if (i == 0)
                            sb.Append("'" + System.Web.HttpContext.Current.Server.HtmlEncode(dr[i].ToString()) + "'");
                        else
                            sb.Append(",'" + System.Web.HttpContext.Current.Server.HtmlEncode(dr[i].ToString()) + "'");
                    }

                        //if (i == 0)
                        //    sb.Append("?");
                        //else
                        //    sb.Append(",?");
                   
                }
                cmd = new OleDbCommand("INSERT INTO [Sheet1$] values(" + sb.ToString() + ")", conn);
                //OleDbParameterCollection oleParam = cmd.Parameters;

                //oleParam.Clear();
                //for (int i = 0; i < Dt.Columns.Count; i++)
                //{
                //    OleDbType oleDbType = GetRefOleDataType(Dt.Columns[i]);

                ////此处是本版本改进中最实用的地方
                //    oleParam.Add(new OleDbParameter("@" + Dt.Columns[i].ColumnName, oleDbType));
                //}
                //for (int i = 0; i < oleParam.Count; i++)
                //{
                //oleParam[i].Value = dr[i];
                //}

                cmd.ExecuteNonQuery();
            }
            conn.Close();

            //输出生成的文件
            FileInfo file = new System.IO.FileInfo(filePath);

            System.Web.HttpContext.Current.Response.Clear();
            System.Web.HttpContext.Current.Response.Buffer = true;
            System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls");
            System.Web.HttpContext.Current.Response.AddHeader("Content-Length", file.Length.ToString());
            System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel";
            System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            System.Web.HttpContext.Current.Response.BinaryWrite(File.ReadAllBytes(filePath));
            System.Web.HttpContext.Current.Response.Flush();
            System.Web.HttpContext.Current.Response.End();

            try
            {
                File.Delete(filePath);
            }
            catch
            {
                try
                {
                    File.Delete(filePath);
                }
                catch
                {
                }
            }
        }

 

 

 ///注：本例在微软无刷新UppdatePanel控件里不能使用，因为本例在UppdatePanel控件里没有回传信息，所以服务器未能收到回传信息导致无法运行；

Comment.OlxOperator.OutputExcelX(this,"employee_r","员工管理",dt);

 

Response.End()出错可以替换为HttpContext.Current.ApplicationInstance.CompleteRequest();