一、加上表頭後變成亂碼
/// DataTable中的數據導出到Excel並下載
/// </summary>
/// <param name="dt">要導出的DataTable</param>
/// <param name="FileType">類型</param>
/// <param name="FileName">Excel的文件名</param>
public void CreateExcel(DataTable dt, string FileType, string FileName)
{
Response.Clear();
Response.Charset = "UTF-8";
Response.Buffer = true;
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
Response.AppendHeader("Content-Disposition", "attachment;filename=\"" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls\"");
Response.ContentType = FileType;
string colHeaders = string.Empty;
string ls_item = string.Empty;
DataRow[] myRow = dt.Select();
int i = 0;
int cl = dt.Columns.Count; //總列數
foreach (DataRow row in myRow)
{
for (i = 0; i < cl; i++)
{
if (i == (cl - 1))
{
ls_item += row[i].ToString() + "\n";
}
else
{
ls_item += row[i].ToString() + "\t";
}
}
Response.Output.Write(ls_item);
ls_item = string.Empty;
}
Response.Output.Flush();
Response.End();
}
調用方法CreateExcel(dt, "application/ms-excel", "demo");
導出的文件中不帶表頭
二、使用wps打開正常
string[] aa=new string[]{"ID","學號","密碼","姓名","性別","手機號碼","電子郵箱","微信號","院系","班級","積分","創建時間"};//列名
int count = ds.Rows.Count;//總條數
NewExportExcelScore(ds, "12345", aa, count);
/// <summary>
/// 導出到excel 已經實現效果
/// </summary>
/// <param name="dt">數據表</param>
/// <param name="fileName">文件名</param>
/// <param name="columnname">列名</param>
/// <param name="count">需要查詢幾列</param>
public static void NewExportExcelScore(DataTable dt, string fileName, string[] columnname, int count)
{
HttpResponse resp = System.Web.HttpContext.Current.Response;
//文件名字
string ExcelName = fileName + DateTime.Now.ToString("yyyyMMddHHmmss");
//編碼
resp.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
//文件名字加格式
resp.AppendHeader("Content-Disposition", "attachment;filename=" + ExcelName + ".xls");
string ls_item = "";
DataRow[] myRow = dt.Select();//可以類似dt.Select("id>10")之形式達到數據篩選目的
int i = 0;
resp.Write("<table border=1><tr style=\" Gray 1px solid;text-align:center\">");
string titleName = null;
//循環出來每列的標題名稱
for (i = 0; i < count; i++)
{
titleName += "<th>" + columnname[i] + "</th>";
}
resp.Write(titleName + "</tr>");
//向HTTP輸出流中寫入取得的數據信息
//逐行處理數據
foreach (DataRow row in myRow)
{
//當前行數據寫入HTTP輸出流，並且置空ls_item以便下行數據
ls_item = "<tr>";
for (i = 0; i < count; i++)
{
if (i == (count - 1))//最後一列，加n
{
ls_item += "<td>" + row[i].ToString() + "</td></tr>";
}
else
{
if (i == 2)
{
ls_item += "<td style='vnd.ms-excel.numberformat:@'>" + " " + row[i].ToString() + "</td>";//到處後已字符形式顯示
}
else
{
ls_item += "<td>" + row[i].ToString() + "</td>";
}
}
}
resp.Write(ls_item);
}
resp.Write("</table>");
resp.End();
}
三、目前用着感覺最好的東西
/// <summary>
/// DataTable通過流導出Excel
/// </summary>
/// <param name="dt">數據源DateSet</param>
/// <param name="columns">中文數組列名</param>
/// <param name="fileName">保存的文件名</param>
/// <param name="pages">(例如：a.xls)</param>
/// <param name="titleName">不知道是啥 可以用null</param>
/// <param name="status">可以直接用null</param>
/// <returns></returns>
public static bool StreamExport(DataTable dt, string[] columns, string fileName, System.Web.UI.Page pages, string titleName, string status)
{
if (dt.Rows.Count > 65535) //總行數大於Excel的行數
{
throw new Exception("預導出的數據總行數大於excel的行數");
}
if (string.IsNullOrEmpty(fileName)) return false;
StringBuilder content = new StringBuilder();
StringBuilder strtitle = new StringBuilder();
content.Append("<html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns='http://www.w3.org/TR/REC-html40'>");
content.Append("<head><title></title><meta http-equiv='Content-Type' content=\"text/html; charset=gb2312\">");
//注意：[if gte mso 9]到[endif]之間的代碼，用於顯示Excel的網格線，若不想顯示Excel的網格線，可以去掉此代碼
content.Append("<!--[if gte mso 9]>");
content.Append("<xml>");
content.Append(" <x:ExcelWorkbook>");
content.Append(" <x:ExcelWorksheets>");
content.Append(" <x:ExcelWorksheet>");
content.Append(" <x:Name>Sheet1</x:Name>");
content.Append(" <x:WorksheetOptions>");
content.Append(" <x:Print>");
content.Append(" <x:ValidPrinterInfo />");
content.Append(" </x:Print>");
content.Append(" </x:WorksheetOptions>");
content.Append(" </x:ExcelWorksheet>");
content.Append(" </x:ExcelWorksheets>");
content.Append("</x:ExcelWorkbook>");
content.Append("</xml>");
content.Append("<![endif]-->");
content.Append("</head><body><table style='border-collapse:collapse;table-layout:fixed;border-collapse:collapse;' border='1' bordercolor='#000' cellpadding='0' cellspacing='0' align='center' >");
if (status == "1")
{
content.Append("<tr><td colspan='" + columns.Length + "' style='font-size:20px; height:60px;' align='center'>");
content.Append(titleName);
content.Append("</td></tr>");
}
//content.Append("<tr><td colspan='" + columns.Length + "' style='height:40px;font-size:30' align='center'>" + fileName + "</td></tr>");
content.Append("<tr>");
if (columns != null)
{
for (int i = 0; i < columns.Length; i++)
{
if (columns[i] != null && columns[i] != "")
{
content.Append("<td align='center' style='height:30px;'><b>" + columns[i] + "</b></td>");
}
else
{
content.Append("<td align='center' style='height:30px;'><b>" + dt.Columns[i].ColumnName + "</b></td>");
}
}
}
else
{
for (int j = 0; j < dt.Columns.Count; j++)
{
content.Append("<td align='center' ><b>" + dt.Columns[j].ColumnName + "</b></td>");
}
}
content.Append("</tr>\n");
for (int j = 0; j < dt.Rows.Count; j++)
{
content.Append("<tr>");
for (int k = 0; k < dt.Columns.Count; k++)
{
object obj = dt.Rows[j][k];
Type type = obj.GetType();
if (type.Name == "Int32" || type.Name == "Single" || type.Name == "Double" || type.Name == "Decimal")
{
double d = obj == DBNull.Value ? 0.0d : Convert.ToDouble(obj);
if (type.Name == "Int32" || (d - Math.Truncate(d) == 0))
content.AppendFormat("<td style='vnd.ms-excel.numberformat:#,##0;height:50px;width:120px' align='center'>{0}</td>", obj);
else
content.AppendFormat("<td style='vnd.ms-excel.numberformat:#,##0.00;height:50px;width:120px' align='center'>{0}</td>", obj);
}
else
content.AppendFormat("<td style='vnd.ms-excel.numberformat:@;height:50px;width:150px' align='center'>{0}</td>", obj);
}
content.Append("</tr>\n");
}
content.Append("</table></body></html>");
content.Replace(" ", "");
pages.Response.Clear();
pages.Response.Buffer = true;
pages.Response.ContentType = "application/ms-excel"; //"application/ms-excel";
pages.Response.Charset = "GB2312";
pages.Response.ContentEncoding = System.Text.Encoding.UTF7;
fileName = System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8);
pages.Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName + ".xls");
pages.Response.Write(content.ToString());
//pages.Response.End(); //注意，若使用此代碼結束響應可能會出現「由於代碼已經過優化或者本機框架位於調用堆棧之上,無法計算表達式的值。」的異常。
HttpContext.Current.ApplicationInstance.CompleteRequest(); //用此行代碼代替上一行代碼，則不會出現上面所說的異常。
return true;
}
使用wps和office低版本試過
四、
/// <summary>
/// 直接由GridView導出Excel
/// </summary>
/// <param name="ctl">控件(一般是GridView)</param>
/// <param name="FileName">導出的文件名</param>
/// <param name="removeIndexs">要移除的列的索引數組(因爲有時我們並不希望把GridView中的所有列全部導出)</param>
/// <param name="pages"></param>
public static void ControlToExcel(System.Web.UI.WebControls.GridView ctl, string FileName, string[] removeIndexs, System.Web.UI.Page pages)
{
if (removeIndexs != null)
{
foreach (string index in removeIndexs)
{
ctl.Columns[int.Parse(index)].Visible = false;
}
}
pages.Response.Charset = "UTF-8";
pages.Response.ContentEncoding = System.Text.Encoding.UTF8;
pages.Response.ContentType = "application/ms-excel";
FileName = System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8);
pages.Response.AppendHeader("Content-Disposition", "attachment;filename=" + "" + FileName + ".xls");
ctl.Page.EnableViewState = false;
System.IO.StringWriter tw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
ctl.RenderControl(hw);
pages.Response.Write(tw.ToString());
HttpContext.Current.ApplicationInstance.CompleteRequest();
}