导出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; } }