导出Excel的2个方法
导出到Excel的两种方法
第一种：
1、首先创建Excle模板，另存为 “xml”文件。使用记事本等编辑软件打开文件的代码。然后另存为视图文件“Export.cshtml”;
2、控制器操作
1 public ActionResult Export() 2 { 3 #region Excel下载设置 4 Response.Clear(); 5 Response.ClearContent(); 6 Response.Buffer = true; 7 Response.ContentEncoding = System.Text.Encoding.UTF8; 8 Response.ContentType = "application/ms-excel"; 9 string downloadFileName = "文件名" + ".xls"; 10 if (Request.UserAgent != null && Request.UserAgent.ToLower().IndexOf("msie", System.StringComparison.CurrentCultureIgnoreCase) > -1) 11 { 12 downloadFileName = HttpUtility.UrlPathEncode(downloadFileName); 13 } 14 if (Request.UserAgent != null && Request.UserAgent.ToLower().IndexOf("firefox", System.StringComparison.CurrentCultureIgnoreCase) > -1) 15 { 16 Response.AddHeader("Content-Disposition", "attachment;filename=\"" + downloadFileName + "\""); 17 } 18 else 19 Response.AddHeader("Content-Disposition", "attachment;filename=" + downloadFileName); 20 21 #endregion 22 return View(); 23 }
3、添加一个页面，用来点击导出按钮，触发导出事件
<a type="button" class="btn btn-orange" data-icon='file-excel-o' href="javascript:" onclick="ConfirmAndExport('@(Url.Action("Export"))', '您确定要导出吗？')" >导出</a>
4、jquery：
1 function ConfirmAndExport(url, msg) { 2 $(this).alertmsg('confirm',msg, { 3 okCall: function () { 4 var data = $("#pagerForm", $.CurrentNavtab).serialize(); 5 var inputs = ''; 6 jQuery.each(data.split('&'), function () { 7 var pair = this.split('='); 8 inputs += '<input type="hidden" name="' + pair[0] + '" value="' + pair[1] + '" />'; 9 }); 10 jQuery('<form action="' + url + '" method="post">' + inputs + '</form>').appendTo('body').submit().remove(); 11 } 12 }); 13 return false; 14 }
1 function ConfirmAndExport(url) { 2 alertMsg.confirm("确定要导出当前数据吗？", { 3 okCall: function () { 4 var data = $("#pagerForm", navTab.getCurrentPanel()).serialize(); 5 var inputs = ''; 6 jQuery.each(data.split('&'), function () { 7 var pair = this.split('='); 8 inputs += '<input type="hidden" name="' + pair[0] + '" value="' + pair[1] + '" />'; 9 }); 10 jQuery('<form action="' + url + '" method="post">' + inputs + '</form>').appendTo('body').submit().remove(); 11 } 12 }); 13 return false; 14 }
第二种、使用npoi 页面代码很简单 就是一个触发下载的按钮 js代码也同上 点击按钮，触发js，跳转到控制器。 然后在控制器里调用要给公共方法，如下： 创建一个公共方法供以后使用： /// <summary> /// Excel导出 /// </summary> /// <param name="dt"></param> /// <returns></returns> public static string Export(DataTable dt) { try { IWorkbook workbook = new XSSFWorkbook(); ISheet sheet1 = workbook.CreateSheet("导出记录"); int cellCount = dt.Columns.Count;//列数 IRow rowHead = sheet1.CreateRow(0); //创建表头 //绑定字体样式到表头 IFont headfont = workbook.CreateFont(); headfont.FontName = "黑体"; headfont.Color = HSSFColor.Black.Index; headfont.FontHeightInPoints = 11; //绑定字体到样式上 ICellStyle Headstyle = workbook.CreateCellStyle(); Headstyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中 Headstyle.Alignment = HorizontalAlignment.Center; //横向居中 Headstyle.SetFont(headfont); //边框颜色 Headstyle.BorderBottom = BorderStyle.Thin; Headstyle.BottomBorderColor = HSSFColor.Grey40Percent.Index; Headstyle.BorderLeft = BorderStyle.Thin; Headstyle.LeftBorderColor = HSSFColor.Grey40Percent.Index; Headstyle.BorderRight = BorderStyle.Thin; Headstyle.RightBorderColor = HSSFColor.Grey40Percent.Index; Headstyle.BorderTop = BorderStyle.Thin; Headstyle.TopBorderColor = HSSFColor.Grey40Percent.Index; //创建表头列 for (int j = 0; j < cellCount; j++) { ICell cell = rowHead.CreateCell(j); string[] arr = dt.Columns[j].ColumnName.Split('_'); cell.SetCellValue(arr[0]); cell.CellStyle = Headstyle; if (arr.Length > 1) { sheet1.SetColumnWidth(j, Utils.StrToInt(arr[1], 20) * 256); } else { sheet1.SetColumnWidth(j, 21 * 256); } } rowHead.Height = 25 * 20; //填充内容 //绑定字体样式到表格内容 IFont font = workbook.CreateFont(); //字体样式 font.FontName = "黑体"; font.Color = HSSFColor.Black.Index; font.FontHeightInPoints = 10; ICellStyle style = workbook.CreateCellStyle(); style.SetFont(font); style.WrapText = true;//设置换行这个要先设置 //垂直居中 style.VerticalAlignment = VerticalAlignment.Center; style.Alignment = HorizontalAlignment.Center; //边框样式 style.BorderBottom = BorderStyle.Thin; style.BottomBorderColor = HSSFColor.Grey40Percent.Index; style.BorderLeft = BorderStyle.Thin; style.LeftBorderColor = HSSFColor.Grey40Percent.Index; style.BorderRight = BorderStyle.Thin; style.RightBorderColor = HSSFColor.Grey40Percent.Index; style.BorderTop = BorderStyle.Thin; style.TopBorderColor = HSSFColor.Grey40Percent.Index; for (int i = 0; i < dt.Rows.Count; i++) { IRow row = sheet1.CreateRow((i + 1)); for (int j = 0; j < cellCount; j++) { ICell cell = row.CreateCell(j); cell.SetCellValue(dt.Rows[i][j].ToString()); cell.CellStyle = style; } row.Height = 20 * 20; } string path = Path.Combine("~/Uploads/" + DateTime.Now.Year + "/" + DateTime.Now.Month + "/" + DateTime.Now.Day + "/"); if (!Directory.Exists(HttpContext.Current.Server.MapPath(path))) { Directory.CreateDirectory(HttpContext.Current.Server.MapPath(path)); } string fileName = Guid.NewGuid().ToString() + ".xlsx"; var fullPath = path + fileName; FileStream sw = File.Create(HttpContext.Current.Server.MapPath(fullPath)); workbook.Write(sw); sw.Close(); return fullPath; } catch (Exception ex) { throw ex; return ex.Message; } } 返回下载文件的地址。 那我们如何将List集合转换为DataTable呢？ 接着往下看： public static DataTable List2DataTable<T>(IEnumerable<T> array) { var dt = new DataTable(); //创建表头 foreach (PropertyDescriptor dp in TypeDescriptor.GetProperties(typeof(T))) dt.Columns.Add(dp.Name, dp.PropertyType); foreach (T item in array) { var Row = dt.NewRow(); foreach (PropertyDescriptor dp in TypeDescriptor.GetProperties(typeof(T))) Row[dp.Name] = dp.GetValue(item); dt.Rows.Add(Row); } return dt; } string MapProperty<T>(T t) { var name=new StringBuilder(); var value = new StringBuilder(); PropertyInfo[] propertyInfos = t.GetType().GetProperties(); if(propertyInfos.Length>0) { foreach(var info in propertyInfos) { name.Append(info.Name); name.Append(" = "); name.Append(info.GetValue(t)+"\t"); name.Append(info.PropertyType +"\n"); } } return name.ToString(); }
