dataset导出 excel 并给工作簿指定名称

Question

我已知如何导出excel.现在主要是想给导出的excel 的工作簿指定一个名称。

Answer 1

Response.ContentType   =   "application/vnd.ms-excel";  
        Response.Charset   ="";  
        this.EnableViewState   =   false;  
        System.IO.StringWriter     sw   =   new   System.IO.StringWriter();  
        System.Web.UI.HtmlTextWriter   hw   =   new   HtmlTextWriter(sw);  
        this.DataGrid1.RenderControl(hw);  
        Response.Write(sw.ToString());  
        Response.End();

Answer 2

你的意思是给导出的Excel命名吗？？

Answer 3

/// <summary>
        /// 根据Table生成Excel文件并下载
        /// </summary>
        /// <param name="Response">Response</param>
        /// <param name="excel">文件完整路径</param>
        /// <param name="tb">Table</param>
        /// <param name="tbname">TableName</param>
        public static void TableToExcel(HttpResponse Response, String excel, DataTable tb, String tbname)
        {
            try
            {
                OleDbConnection connex = GetConnFromExcel(excel); //获取到excel文件的oledb连接
                connex.Open();
                #region 生成Excel文件
                String ct = "CREATE TABLE " + tbname + " (";                //以下生成一个sql命令向excel中插入一个表
                foreach (DataColumn clmn in tb.Columns)
                {
                    switch (clmn.DataType.Name)                                     //根据不同数据类型分别处理
                    {
                        case "Decimal":
                            ct += clmn.ColumnName + " Decimal,";
                            break;
                        case "Double":
                            ct += clmn.ColumnName + " Double,";
                            break;
                        case "DateTime":
                            ct += clmn.ColumnName + " String,";
                            break;
                        default:
                            ct += clmn.ColumnName + " Text,";
                            break;
                    }
                }
                ct = ct.Substring(0, ct.Length - 1) + ")";
                OleDbCommand cmd1 = new OleDbCommand(ct, connex);
                cmd1.ExecuteNonQuery();                                                              //向excel中插入一个表
                System.Threading.Thread.Sleep(5);                                              //文件被创建后保证能够正确读写，睡眠5毫秒。
                foreach (DataRow r in tb.Rows)                                                     //下面向excel中一行一行写入数据
                {
                    string fs = "", vs = "";
                    foreach (DataColumn clmn in tb.Columns)
                    {
                        fs += clmn.ColumnName + ",";
                        if (r[clmn.ColumnName] == DBNull.Value)
                        {
                            vs += "null,";
                            continue;
                        }
                        switch (clmn.DataType.Name)                                                   //根据不同数据类型分别处理
                        {
                            case "Decimal":
                                vs += ((decimal)r[clmn.ColumnName]).ToString("0.00") + ",";
                                break;
                            case "Double":
                                vs += ((double)r[clmn.ColumnName]).ToString("0.00") + ",";
                                break;
                            case "DateTime":
                                vs += "'" + ((DateTime)r[clmn.ColumnName]).ToShortDateString() + "',";
                                break;
                            default:
                                vs += "'" + ReplaceSqlInverted(r[clmn.ColumnName].ToString()) + "',";
                                break;
                        }
                    }
                    string sqlstr = "insert into [" + tbname + "$] (" + fs.Substring(0, fs.Length - 1) + ") values (" + vs.Substring(0, vs.Length - 1) + ")";
                    OleDbCommand cmd = new OleDbCommand(sqlstr, connex);
                    cmd.ExecuteNonQuery();                                            //向excel中插入数据 
                    System.Threading.Thread.Sleep(1);                          //保证数据能够正确写入，睡眠1毫秒。
                }
                connex.Close();
                System.Threading.Thread.Sleep(5);                             //保证文件正确关闭，睡眠5毫秒。
                #endregion                OutFile(Response, excel);                                               //下载文件
            }
            catch (System.Threading.ThreadAbortException thex)
            {
                ApplicationLog.WriteError(thex.ToString());
            }
            catch (Exception ex)
            {
                ApplicationLog.WriteError(ex.ToString());
                throw ex;
            }
        }        /// <summary>
        /// 文件下载，仅供Excel文件导出使用。
        /// </summary>
        /// <param name="Response"></param>
        /// <param name="upfilepath"></param>
        private static void OutFile(HttpResponse Response, String upfilepath)
        {
            try
            {
                System.IO.FileInfo fi = new System.IO.FileInfo(upfilepath);
                //Response.Clear();
                Response.Buffer = false;
                Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.HtmlDecode(upfilepath.Substring(upfilepath.LastIndexOf(@"\") + 1)));
                Response.AppendHeader("Content-Length", fi.Length.ToString());
                Response.ContentEncoding = System.Text.Encoding.UTF8;
                Response.ContentType = "application/ms-excel";                                 //设置输出文件类型为excel文件。
                Response.WriteFile(upfilepath);
                System.IO.File.Delete(upfilepath);
                Response.Flush();
                Response.End();
                //Response.Write("<script>window.close()</script>");
            }
            catch (Exception ex)
            {
                ApplicationLog.WriteError(ex.ToString());
            }
        }