一、加上表头后变成乱码
/// 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();
}