C#操纵Excel导入导出要领一,按照项目要求,对Excel操纵有多重要领,,有的是类库本身编程设计格局,有的是JS按照表格的格局样式直接导出。
此刻介绍的是直接下载型:
按照页面显示的表格中Html格局,直接下载到Excel(相当于把整个表格直接拷贝到Excel中)
Html格局如下:
<div id=http://www.mamicode.com/"ta"> <input type=http://www.mamicode.com/"button" class=http://www.mamicode.com/"btn btn-primary btn-mini" onclick=http://www.mamicode.com/"javascript: method1(‘tableExcel‘)" value=http://www.mamicode.com/"导出Excel"> <table id=http://www.mamicode.com/"tableExcel" class=http://www.mamicode.com/"table table-hover"> <thead> <tr> <th>Id</th> <th colspan=http://www.mamicode.com/"2">UserName</th> <th>Email</th> <th>Address</th> <th>Mobile</th> <th>CreateDate</th> <th>操纵</th> </tr> </thead> <tbody> @foreach (var u in ViewBag.Users) { <tr> <td>@u.UserId</td> <td>@u.UserName</td> <td>@u.UserName</td> <td>@u.Email</td> <td>@u.Address</td> <td>@u.Mobile</td> <td>@u.CreateDate</td> <td><a href=http://www.mamicode.com/"@Url.Action("Delete", "Home")[email protected]" onclick=http://www.mamicode.com/"javascript:return p_del()">Delete</a></td> </tr> } </tbody> </table> </div>
图例:
导出JS:
function method1(tableid) {//整个表格拷贝到EXCEL中 //var len = $("table tr").length; //if (len > 1) { // $("tr[id=‘" + (len - 1) + "‘]").remove(); //} //$(‘table>th:last‘).hide(); //$(‘table>td:last‘).hide(); //var len = $("table tr").length; $("table tr th:last-child").remove(); $("table tr td:last-child").remove(); if (getExplorer() == ‘ie‘) { var curTbl =http://www.mamicode.com/ document.getElementById(tableid); var oXL = new ActiveXObject("Excel.Application"); //创建AX东西excel var oWB =http://www.mamicode.com/ oXL.Workbooks.Add(); //获取workbook东西 var xlsheet = oWB.Worksheets(1); //激活当前sheet var sel =http://www.mamicode.com/ document.body.createTextRange(); sel.moveToElementText(curTbl); //把表格中的内容移到TextRange中 sel.select(); //全选TextRange中内容 sel.execCommand("Copy"); //复制TextRange中内容 xlsheet.Paste(); //粘贴到勾当的EXCEL中 oXL.Visible = true; //设置excel可见属性 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); //xls.visible = false; oXL.Quit(); oXL = null; //结束excel进程,退出完成 //window.setInterval("Cleanup();",1); idTmr = window.setInterval("Cleanup();", 1); } } else { tableToExcel(tableid) } } function Cleanup() { window.clearInterval(idTmr); CollectGarbage(); } var tableToExcel =http://www.mamicode.com/ (function () { var uri = ‘data:application/vnd.ms-excel;base64,‘, template = ‘<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table>{table}</table></body></html>‘, base64 = function (s) { return window.btoa(unescape(encodeURIComponent(s))) }, format =http://www.mamicode.com/ function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }) } return function (table, name) { if (!table.nodeType) table =http://www.mamicode.com/ document.getElementById(table) //$(‘table>th:last‘).hide(); //$(‘table>td:last‘).hide(); var ctx = { worksheet: name || ‘Worksheet‘, table: table.innerHTML } window.location.href = uri + base64(format(template, ctx)) parent.$("#ta").load(window.location.href + " #ta"); parent.subWindow_add.Close(); } })()
如果导出呈现乱码问题:
解决方案:
添加