/// <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>
/// 生成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);
}
}