/// <summary> /// 生成Excel文件 /// </summary> /// <param name="dt">数据源</param> /// <param name="fileName">保存的文件名</param> public static void CreateExcel(DataTable dt, string fileName) { var response = HttpContext.Current.Response; response.ContentEncoding = Encoding.GetEncoding("gb2312"); response.AppendHeader("content-disposition", "attachment;filename=\"" + HttpUtility.UrlEncode(fileName, Encoding.UTF8) + ".xls\""); response.ContentType = "application/ms-excel"; var SP = new StrPlus(); SP.Append("<table border='1' cellspacing='0' cellpadding='0'>"); SP.Append("<tr>"); foreach (DataColumn item in dt.Columns) { SP.AppendFormat( "<td style='font-size: 12px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='20'>{0}</td>", item.Caption); } SP.Append("</tr>"); //定义表对象与行对象,同时用DataSet对其值进行初始化 foreach (DataRow row in dt.Rows) { SP.Append("<tr>"); //在当前行中,逐列获得数据,数据之间以\t分割,结束时加回车符\n for (var i = 0; i < dt.Columns.Count; i++) { SP.AppendFormat("<td style='background-color: #E9ECED;font-size: 12px;'>{0}</td>", row[i].ToString()); } SP.Append("</tr>"); } SP.Append("</table>"); response.Write(SP); response.End(); }
/// <summary> /// 创建数据库连接字符串 /// </summary> /// <param name="dataType">数据库类型</param> /// <param name="userID">账号</param> /// <param name="passWord">密码</param> /// <param name="server">服务器地址</param> /// <param name="catalog">表名</param> /// <param name="dataVer">数据库版本</param> /// <param name="connectTimeout">链接超时时间</param> /// <param name="poolMinSize">连接池最小数量</param> /// <param name="poolMaxSize">连接池最大数量</param> /// <param name="port">端口</param> public static string CreateConnString(DataBaseType dataType, string userID, string passWord, string server, string catalog, string dataVer, int connectTimeout = 60, int poolMinSize = 16, int poolMaxSize = 100, string port = "") { switch (dataType) { case DataBaseType.MySql: { return(string.Format( "Data Source='{0}';User Id='{1}';Password='******';Database='{3}';charset='gbk'", server, userID, passWord, catalog)); } case DataBaseType.SqlServer: { string connString; if (userID.IsNullOrEmpty() && passWord.IsNullOrEmpty()) { connString = string.Format("Pooling=true;Integrated Security=True;"); } else { connString = string.Format("User ID={0};Password={1};Pooling=true;", userID, passWord); } connString += string.Format("Data Source={0};Initial Catalog={1};", server, catalog); if (poolMinSize > 0) { connString += string.Format("Min Pool Size={0};", poolMinSize); } if (poolMaxSize > 0) { connString += string.Format("Max Pool Size={0};", poolMaxSize); } if (connectTimeout > 0) { connString += string.Format("Connect Timeout={0};", connectTimeout); } return(connString); } case DataBaseType.OleDb: { string connString; switch (dataVer) { case "3.0": { connString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;"); break; //Extended Properties=Excel 3.0; } case "4.0": { connString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;"); break; //Extended Properties=Excel 4.0; } case "5.0": { connString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;"); break; //Extended Properties=Excel 5.0; } case "95": { connString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;"); break; //Extended Properties=Excel 5.0; } case "97": { connString = string.Format("Provider=Microsoft.Jet.OLEDB.3.51;"); break; } case "2003": { connString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;"); break; //Extended Properties=Excel 8.0; } default: //2007+ { //DR=YES connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;"); break; //Extended Properties=Excel 12.0; } } connString += string.Format("Data Source={0};", GetFilePath(server)); if (!userID.IsNullOrEmpty()) { connString += string.Format("User ID={0};", userID); } if (!passWord.IsNullOrEmpty()) { connString += string.Format("Password={0};", passWord); } return(connString); } case DataBaseType.Xml: { return(server.IsNullOrEmpty() ? string.Empty : GetFilePath(server)); } case DataBaseType.SQLite: { var plus = new StrPlus(); plus.AppendFormat("Data Source={0};Min Pool Size={1};Max Pool Size={2};", GetFilePath(server), poolMinSize, poolMaxSize); if (!passWord.IsNullOrEmpty()) { plus.AppendFormat("Password={0};", passWord); } if (!dataVer.IsNullOrEmpty()) { plus.AppendFormat("Version={0};", dataVer); } return(plus.Value); } case DataBaseType.Oracle: { if (port.IsNullOrEmpty()) { port = "1521"; } return (string.Format( "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={0})(PORT={3})))(CONNECT_DATA=(SERVER=DEDICATED)(SID={4})));User Id={1};Password={2};", server, userID, passWord, port, catalog)); } default: return(string.Empty); } }