数据导出为Excel文件
//一个有数据的DataTable
DataTable dt = getData();
StringBuilder data_list = new StringBuilder();
string excelName = "Excel文件名称";
if (dt.Rows.Count > 0)
{
data_list.Append("<table border='1'>");
data_list.Append(" <tr><td>数据时间</td><td>类型</td></tr>");
for (int i = 0; i < dt.Rows.Count; i++)
{
data_list.Append("<tr>");
data_list.Append("<td>时间值</td>");
data_list.Append("<td>类型值</td>");
data_list.Append("</tr>");
}
}
string content = data_list.ToString();
saveAsExcel(content, excelName); return "";
需要引用的方法有
internal Exception error = null;
#region 将html内容另存为Excel
/// <summary>
/// 将html内容另存为Excel
/// </summary>
/// <param name="content">html格式的内容字符串</param>
/// <param name="xlsFile">要保存的Excel文件名</param>
/// <returns></returns>
internal bool saveAsExcel(string content, string xlsFile)
{
try
{
string html = "<meta http-equiv=\"Content-Type\" content=\"text/html; charset=gb2312\">" + content;
System.Web.HttpContext curContext = System.Web.HttpContext.Current;
curContext.Response.Buffer = true;
curContext.Response.Clear();
curContext.Response.Charset = "GB2312";
if (curContext.Request.UserAgent.Contains("MSIE") || curContext.Request.UserAgent.Contains("msie"))
{
// 如果客户端使用 Microsoft Internet Explorer,则需要编码
// 如果使用 fileName =Server.UrlEncode(fileName); 则会出现上文中出现的情况
xlsFile = toHexString(xlsFile);
}
curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + xlsFile + ".xls");
curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
curContext.Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
curContext.Response.Write(html);
curContext.Response.Flush();
curContext.Response.Clear();
//curContext.Response.End();
curContext.ApplicationInstance.CompleteRequest();
}
catch (Exception ex)
{
error = ex;
return false;
}
return true;
}
// 为字符串中的非英文字符编码
string toHexString(string s)
{
char[] chars = s.ToCharArray();
StringBuilder builder = new StringBuilder();
for (int index = 0; index < chars.Length; index++)
{
bool bEncode = needToEncode(chars[index]);
if (bEncode)
{
string encodedString = toHexString(chars[index]);
builder.Append(encodedString);
}
else
{
builder.Append(chars[index]);
}
}
return builder.ToString();
}
//指定 一个字符是否应该被编码
bool needToEncode(char chr)
{
string reservedChars = "$-_.+!*'(),@=&";
if (chr > 127)
return true;
if (char.IsLetterOrDigit(chr) || reservedChars.IndexOf(chr) >= 0)
return false;
return true;
}
// 为非英文字符串编码
string toHexString(char chr)
{
UTF8Encoding utf8 = new UTF8Encoding();
byte[] encodedBytes = utf8.GetBytes(chr.ToString());
StringBuilder builder = new StringBuilder();
for (int index = 0; index < encodedBytes.Length; index++)
{
builder.AppendFormat("%{0}", Convert.ToString(encodedBytes[index], 16));
}
return builder.ToString();
}
#endregion
菜鸟总结 多存疏漏 感谢指正