ashx导出dataTable为Excel

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

一，datatable导出Excel，用户可以选择路径，方法如下：

/// <summary>
/// DataTable导出到Excel
/// </summary>
/// <param name="dt">DataTable类型的数据源</param>
/// <param name="FileName">文件名</param>
/// <param name="httpContext">httpcontext</param>
public void CreateExcel(DataTable dt, string FileName, HttpContext httpContext)
{
httpContext.Response.Clear();
httpContext.Response.Charset = "UTF-8";
httpContext.Response.Buffer = true;
httpContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
httpContext.Response.AppendHeader("Content-Disposition", "attachment;filename=\"" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls\"");
httpContext.Response.ContentType = "application/ms-excel";
string colHeaders = string.Empty;
string ls_item = string.Empty;
DataRow[] myRow = dt.Select();
int i = ;
int cl = dt.Columns.Count;
for (int j = ; j < dt.Columns.Count; j++)
{
ls_item += dt.Columns[j].ColumnName + "\t"; //栏位：自动跳到下一单元格
}
ls_item = ls_item.Substring(, ls_item.Length - ) + "\n";
foreach (DataRow row in myRow)
{
for (i = ; i < cl; i++)
{
if (i == (cl - ))
{
ls_item += row[i].ToString() + "\n";
}
else
{
ls_item += row[i].ToString() + "\t";
}
}
httpContext.Response.Output.Write(ls_item);
ls_item = string.Empty;
}
httpContext.Response.Output.Flush();
httpContext.Response.End();
}

前端页面调用方法如下：

<a href="Import.ashx" >导出Excel</a>

二，datatable导出Excel，保存到服务器上。方法如下：

/// <summary>
/// DataTable导出到Excel
/// </summary>
/// <param name="table">DataTable类型的数据源</param>
/// <param name="file">需要导出的文件路径</param>
public void dataTableToCsv(DataTable table, string file)
{
string title = "";
FileStream fs = new FileStream(file, FileMode.OpenOrCreate);
StreamWriter sw = new StreamWriter(new BufferedStream(fs), System.Text.Encoding.Default);
for (int i = 0; i < table.Columns.Count; i++)
{
title += table.Columns[i].ColumnName + "\t"; //栏位：自动跳到下一单元格
}
title = title.Substring(0, title.Length - 1) + "\n";
sw.Write(title);
foreach (DataRow row in table.Rows)
{
string line = "";
for (int i = 0; i < table.Columns.Count; i++)
{
line += row[i].ToString().Trim() + "\t"; //内容：自动跳到下一单元格
}
line = line.Substring(0, line.Length - 1) + "\n";
sw.Write(line);
}
sw.Close();
fs.Close();
}