一,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 = 0; int cl = dt.Columns.Count; for (int j = 0; j < dt.Columns.Count; j++) { ls_item += dt.Columns[j].ColumnName + " "; //栏位:自动跳到下一单元格 } ls_item = ls_item.Substring(0, ls_item.Length - 1) + " "; foreach (DataRow row in myRow) { for (i = 0; i < cl; i++) { if (i == (cl - 1)) { ls_item += row[i].ToString() + " "; } else { ls_item += row[i].ToString() + " "; } } 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 + " "; //栏位:自动跳到下一单元格 } title = title.Substring(0, title.Length - 1) + " "; 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() + " "; //内容:自动跳到下一单元格 } line = line.Substring(0, line.Length - 1) + " "; sw.Write(line); } sw.Close(); fs.Close(); }
前端调用方法:
<a onclick="Import()">导出Excel</a> function Import() { $.post("Import.ashx", {}, function (data) { alert("导出成功"); }); }