导出Excel (Page类帮助方法)

        /// <summary>   
       /// 将数据表直接导出到Excel文件并下载   
       /// </summary>   
       /// <param name="dt">原数据表</param>   
       /// <param name="headers">要导出的列名的列头</param>   
       /// <param name="columns">要导出的列的列名</param>   
       /// <param name="excelFileName">导出文件的文件名</param>   
       /// <returns>返回错误消息，如果成功导出则返回空字符串</returns>   
       public static string DataTable2Excel(System.Data.DataTable dt, string[] headers, string[] columns, string excelFileName)   
       {   
           // 生成的Excel文件路径   
           string excelPath = System.Configuration.ConfigurationManager.AppSettings["OutPutPath"]; // 取配置文件   
           if ( excelPath == null || excelPath == "" )   
           {   
               excelPath = HttpContext.Current.Request.PhysicalApplicationPath + "\\"; // 默认路径   
           }   
           // 生成唯一文件名   
           string uniqueFileName = Path.GetFileNameWithoutExtension(excelFileName) + DateTime.Now.ToString("yyyyMMddHHmmss") + Path.GetExtension(excelFileName);   
           // 加上路径   
           excelPath += uniqueFileName;   
           if ( dt == null )   
           {   
               return "DataTable不能为空";   
           }   
           int rows = dt.Rows.Count;   
           int cols = headers.Length;   
           StringBuilder sb;   
           string connString;   
           sb = new StringBuilder();   
           connString = string.Format("Provider = Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source={0}", excelPath);   
           //生成创建表的脚本   
           string tableName = dt.TableName;   
           if ( tableName == "" )   
           {   
               tableName = "sheet1";   
           }   
           sb.Append("CREATE TABLE ");   
           sb.Append("[" + tableName + "] ( ");   
           for ( int i = 0; i < cols; i++ )   
           {   
               string datatype;   
               switch ( dt.Columns[columns[i]].DataType.Name.ToLower() )   
               {   
                   case "float": datatype = "float"; break;   
                   case "int32": datatype = "int"; break;   
                   case "double": datatype = "double"; break;   
                   case "decimal": datatype = "float"; break;   
                   //修改记录10   
                   default: datatype = "text"; break;   
               }   
               if ( i < cols - 1 )   
               {   
                   sb.Append(string.Format("[{0}] {1},", headers[i], datatype));   
               }   
               else  
               {   
                   sb.Append(string.Format("[{0}] {1})", headers[i], datatype));   
               }   
           }   
           using ( OleDbConnection objConn = new OleDbConnection(connString) )   
           {   
               OleDbCommand objCmd = new OleDbCommand();   
               objCmd.Connection = objConn;   
               objCmd.CommandText = sb.ToString();   
               if ( objConn != null && objConn.State == ConnectionState.Open )   
               {   
                   objConn.Close();   
               }   
               objConn.Open();   
               objCmd.ExecuteNonQuery();   
               sb.Remove(0, sb.Length);   
               sb.Append("INSERT INTO ");   
               sb.Append("[" + tableName + "] ( ");   
               for ( int i = 0; i < cols; i++ )   
               {   
                   if ( i < cols - 1 )   
                   {   
                       sb.Append("[" + headers[i] + "],");   
                   }   
                   else  
                   {   
                       sb.Append("[" + headers[i] + "]) values (");   
                   }   
               }   
               for ( int i = 0; i < cols; i++ )   
               {   
                   if ( i < cols - 1 )   
                   {   
                       sb.Append("@" + columns[i] + ",");   
                   }   
                   else  
                   {   
                       sb.Append("@" + columns[i] + ")");   
                   }   
               }   
               //建立插入动作的Command   
               objCmd.CommandText = sb.ToString();   
               OleDbParameterCollection param = objCmd.Parameters;   
               for ( int i = 0; i < cols; i++ )   
                {   
                    OleDbParameter onepar = new OleDbParameter();   
                    onepar.ParameterName = "@" + columns[i];   
                    switch ( dt.Columns[columns[i]].DataType.Name.ToLower() )   
                    {   
                        case "float": onepar.OleDbType = OleDbType.Single; break;   
                        case "int32": onepar.OleDbType = OleDbType.Integer; break;   
                        case "double": onepar.OleDbType = OleDbType.Double; break;   
                        case "decimal": onepar.OleDbType = OleDbType.Decimal; break;   
                        //修改记录10   
                        default: onepar.OleDbType = OleDbType.LongVarChar; break;   
                    }   
                    param.Add(onepar);   
                    //param.Add(new OleDbParameter("@" + columns[i], OleDbType.VarChar));   
                }   
                //遍历DataTable将数据插入新建的Excel文件中   
                foreach ( DataRow row in dt.Rows )   
                {   
                    for ( int i = 0; i < param.Count; i++ )   
                    {   
                        param[i].Value = row[columns[i]];   
                    }   
                    objCmd.ExecuteNonQuery();   
                }   
                objConn.Close();   
                System.IO.FileStream xlsStream = new FileStream(excelPath, System.IO.FileMode.Open);   
                int len = (int)xlsStream.Length;   
                byte[] data = new byte[len];   
                xlsStream.Read(data, 0, len);   
                xlsStream.Close();   
                HttpContext.Current.Response.Clear();   
                HttpContext.Current.Response.ClearHeaders();   
                //修改记录10 开始   
                HttpContext.Current.Response.Charset = "UTF-8";   
                HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default;   
                HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(excelFileName));   
                //修改记录10 结束   
                HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";   
                HttpContext.Current.Response.BinaryWrite(data);   
                HttpContext.Current.Response.Flush();   
                File.Delete(excelPath);   
                HttpContext.Current.Response.End();   
                return string.Empty;   
            }   
        }