c#导出Excel
1、方法一:后台为主要操作
后台方法
using NPOI.SS.UserModel; using NPOI.HSSF.UserModel;
public string ExportList(Report entity) { // 获取导出数据 List<ReportQtyCity> list = ComplaintBusiness.GetComplaintQtyCityExportList(entity); if (list.Count == 0) return "NoData"; var i = 0; //模板路径 string templetFileName = HttpContext.Current.Server.MapPath("~/Templete/模板.xls"); //保存路径 string reportFileName = HttpContext.Current.Server.MapPath("~/Download/Export/" + DateTime.Now.ToString("统计导出报表 yyyyMMddHHmmss") + ".xls"); //读取模板 FileInfo fileInfo = new FileInfo(templetFileName); //如果模板存在,打开并读取 if (fileInfo.Exists) fileInfo.CopyTo(reportFileName, true); else return "NoTemplete"; FileStream file = new FileStream(reportFileName, FileMode.Open, FileAccess.Read); IWorkbook work = new HSSFWorkbook(file); //读取模板文件 string sheetName = "明细"; work.SetSheetName(0, sheetName); ISheet sheet = work.GetSheet(sheetName); IRow row = null; var j = 2; //创建并设置列表格线样式 ICellStyle style = work.CreateCellStyle(); style.BorderBottom = BorderStyle.Thin; style.BorderLeft = BorderStyle.Thin; style.BorderRight = BorderStyle.Thin; style.BorderTop = BorderStyle.Thin; //循环数据 foreach (var item in list) { //从第三行创建行 row = sheet.CreateRow(j); j++; i++; // 创建列,赋值 // 序号 ICell cell0 = row.CreateCell(0); cell0.CellStyle = style; cell0.SetCellValue(i); // 开始时间 ICell cell1 = row.CreateCell(1); cell1.CellStyle = style; if (item.DateStart != null) { cell1.SetCellValue(entity.DateStart.GetValueOrDefault().ToString("yyyy-MM-dd")); } // 结束时间 ICell cell2 = row.CreateCell(2); cell2.CellStyle = style; if (item.DateEnd != null) { cell2.SetCellValue(item.DateEnd.GetValueOrDefault().ToString("yyyy-MM-dd")); } ICell cell3 = row.CreateCell(3); cell3.CellStyle = style; if (!string.IsNullOrEmpty(item.City)) { cell3.SetCellValue(item.City); } ICell cell4 = row.CreateCell(4); cell4.CellStyle = style; if (!string.IsNullOrEmpty(item.CompanyName)) { cell4.SetCellValue(item.CompanyName); } ICell cell5 = row.CreateCell(5); cell5.CellStyle = style; if (!string.IsNullOrEmpty(item.ParkName)) { cell5.SetCellValue(item.ParkName); } } using (FileStream filess = System.IO.File.OpenWrite(reportFileName)) { work.Write(filess); } FileInfo filet = new FileInfo(reportFileName); var msg = filet.FullName; msg = "/Download/Export/" + msg.Substring(msg.LastIndexOf("\\") + 1); work.Close(); Dispose(); return msg; }
前台处理:
// 数据筛选条件 var actionParam = GetActionParam(); $.InvokeAjaxV3({ async: false, url: "Api/V3/Report/Export", data: actionParam, callBack: function (data) { var jsonResult = $.StrToJson(data).Results; if (data == "NoData") { $.messager.alert("导出提示", "暂无数据,请重新选择查询条件"); } else if (data == "NoTemplete") { $.messager.alert("导出提示", "找不到模板,无法导出"); } else location.href = HttpWcf + jsonResult; } });
2、方法二:前台为主要操作
<a href="javascript:void(0)" id="toexcel" class="btn btn-primary btn-sm "> <i class="fa fa-file-excel-o"></i> 导出到EXCEL</a>
<table id="tableExcel" style="display: none"> <thead class="text-nowrap"> </thead> <tfoot></tfoot> </table>
$("#toexcel").on('click', function () { $.InvokeAjaxV3({ url: "Api/V3/Statistic/Export", data: { }, async: false, callBack: function (data) { var obj = $.StrToJson(data).Results; var html = '' + '<tr>' + '<th >企业名称</th>' + '</tr>'; var allHouseArea = 0, allEmployeeCount = 0, allSatisfiedRate = 0; for (var o in obj) { if (obj.hasOwnProperty(o)) { html += '<tr>' + '<td >' + obj[o].CompanyName + '</td>' + '</tr>'; } } $("#tableExcel").html(html); FunTableToExcel('tableExcel'); } });
var idTmr; function getExplorer() { var explorer = window.navigator.userAgent; //ie if (explorer.indexOf("MSIE") >= 0) { return 'ie'; } //firefox else if (explorer.indexOf("Firefox") >= 0) { return 'Firefox'; } //Chrome else if (explorer.indexOf("Chrome") >= 0) { return 'Chrome'; } //Opera else if (explorer.indexOf("Opera") >= 0) { return 'Opera'; } //Safari else if (explorer.indexOf("Safari") >= 0) { return 'Safari'; } } function FunTableToExcel(tableid) { if (getExplorer() == 'ie') { var curTbl = document.getElementById(tableid); var oXL = new ActiveXObject("Excel.Application"); var oWB = oXL.Workbooks.Add(); var xlsheet = oWB.Worksheets(1); var sel = document.body.createTextRange(); sel.moveToElementText(curTbl); sel.select(); sel.execCommand("Copy"); xlsheet.Paste(); oXL.Visible = true; try { var fname = oXL.Application.GetSaveAsFilename("Excel.xls", "Excel Spreadsheets (*.xls), *.xls"); } catch (e) { print("Nested catch caught " + e); } finally { oWB.SaveAs(fname); oWB.Close(savechanges = false); oXL.Quit(); oXL = null; idTmr = window.setInterval("Cleanup();", 1); } } else { tableToExcel(tableid); } } function Cleanup() { window.clearInterval(idTmr); CollectGarbage(); } var tableToExcel = (function () { var uri = 'data:application/vnd.ms-excel;base64,', template = '<html><head><meta charset="UTF-8"></head><body><table border="1px">{table}</table></body></html>', base64 = function (s) { return window.btoa(unescape(encodeURIComponent(s))) }, format = function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }); } return function (table, name) { if (!table.nodeType) table = document.getElementById(table); var ctx = { worksheet: name || 'Worksheet', table: table.innerHTML } window.location.href = uri + base64(format(template, ctx)); } })();
再三须慎意,第一莫欺心