asp.net6 表转换为excel的方法

原文

不使用客户端与服务端的库和包，只用一段代码解决.

 @using System.Data
 @model DataTable
 <table id="tblExport" class="table table-hover table-responsive  table-striped">
     @*table content*@ 
 </table>

 <input type="button" onclick="tableToExcel('tblExport', 'W3C Example Table')" value="Export to Excel">
 @section scripts{ 
     <script src="http://code.jquery.com/jquery-latest.min.js" type="text/javascript"></script>
     <script type="text/javascript">
         var tableToExcel = (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 = 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))
             }
         })()
     </script>
 }

[ 注意 ] 通过这种方法会导出一个xls文件，打开之后会得到excel的警告，选择启用编辑即可.

使用 ‘FileSaver.js’ 和 ‘TableExport’ 插件来实现转换.

右键 wwwroot 文件夹, 选择添加客户端库, 输入 FileSaver.js 然后选择安装这个库.相同方法安装TableExport插件.

然后用下面的代码导出excel.

@using System.Data
 @model DataTable
 <table id="tblExport" class="table table-hover table-responsive  table-striped">
     @*table content*@ 
 </table>
 @section scripts{
     <link rel="stylesheet" href="https://netdna.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
     <link rel="stylesheet" href="~/lib/TableExport/css/tableexport.min.css" />
     <script src="~/lib/jquery/dist/jquery.min.js"></script>
     <script src="~/js/Blob.js"></script>
     <script src="~/js/xls.core.min.js"></script>
     <script src="~/lib/FileSaver.js/FileSaver.min.js"></script>
     <script src="~/lib/TableExport/js/tableexport.min.js"></script>
     <script type="text/javascript">
         $(function () {
             var tables = $("#tblExport").tableExport({
                 bootstrap: true,
                 headings: true,
                 footers: true,
                 formats: ["xlsx", "xls", "csv", "txt"],
                 fileName: "MyExcel",
                 position: "top",
                 ignoreRows: null,
                 ignoreCols: null,
                 ignoreCSS: ".tableexport-ignore",
                 emptyCSS: ".tableexport-empty",
                 trimWhitespace: true
             });
         });
     </script> 
 }

你还需要自己在js目录下新建一个 Blob.js 和 xls.core.min.js文件，将下面的代码复制进去 : Blob.js and xls.core.min.js。

使用 ClosedXML 包. 这是一个服务端的库.

通过 NuGet 安装ClosedXML.

创建一个Action，将Dataable导出到Excel，代码如下所示:


//index page: display the table data.
 public IActionResult ExportExcel()
 {
     var custTable = CreateDataTable();
     return View(custTable);
 }
 public IActionResult ExportDataTabletoExcel()
 {
     var dt = CreateDataTable();
     using (XLWorkbook wb = new XLWorkbook())
     {
         wb.Worksheets.Add(dt);
         using (MemoryStream stream = new MemoryStream())
         {
             wb.SaveAs(stream);
             return File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Grid.xlsx");
         }
     }
 }
 public DataTable CreateDataTable()
 {
     // Create a new DataTable.    
     DataTable custTable = new DataTable("Customers");
     DataColumn dtColumn;
     DataRow myDataRow;
     ... //add columns and rows.
     return custTable;
 }

view page里的代码:

 @using System.Data
 @model DataTable
 <table id="tblExport" class="table table-hover table-responsive  table-striped">
     @*table content*@ 
 </table>

 <a href='@Url.Action("ExportDataTabletoExcel","Home")'> export to excel</a>
posted @ 2021-07-12 15:29  _zxmax  阅读(233)  评论(0)    收藏  举报