1.获取数据并存储为List集合
1)这里的list集合可以是List<Model> 也可以是List<dynamic>
2.将list数据转换为JSON数据
var json = Newtonsoft.Json.JsonConvert.SerializeObject(listTrack); DataTable dtData = null; if (listTrack.Count > 0) { dtData = Newtonsoft.Json.JsonConvert.DeserializeObject<DataTable>(json); }
3.实现导出功能
1)这里可以对列进行重命名,如注释
public static void MonthOutExcel(DataTable dtData) { if (dtData == null) { _ReturnStatus = -1; _ReturnMessage = "数据集为空!"; return; } string shtnl = ""; shtnl = "<table border='1' cellspacing='1' cellpadding='1'>"; shtnl = shtnl + "<thead>"; //dtData.Columns["Id"].ColumnName = "序号"; //dtData.Columns["ProjectType"].ColumnName = "项目类型"; for (int j = 0; j < dtData.Columns.Count; j++) { shtnl = shtnl + "<th>" + dtData.Columns[j] + "</th>"; } shtnl = shtnl + "</thead><tbody>"; for (int i = 0; i < dtData.Rows.Count; i++) { shtnl = shtnl + "<tr>"; for (int j = 0; j < dtData.Columns.Count; j++) { shtnl = shtnl + "<td align='center'>" + dtData.Rows[i][j] + "</td>"; } shtnl = shtnl + "</tr>"; } shtnl = shtnl + "</tbody></table>"; string FileName = "综合查询" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls"; ExportExcel("application/ms-excel", FileName, shtnl); }
4.设置EXCEL表头格式
public static void ExportExcel(string FieldType, string FileName, string dt) { System.Web.HttpContext.Current.Response.Charset = "utf-8"; System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8).ToString()); System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); System.Web.HttpContext.Current.Response.ContentType = FieldType; StringWriter tw = new StringWriter(); System.Web.HttpContext.Current.Response.Output.Write(dt); System.Web.HttpContext.Current.Response.Flush(); System.Web.HttpContext.Current.Response.End(); }
5.最后<a href=""></a> 即可调用并下载EXCEL