C# NPOI导出EXCEL并实现在WEB端下载

1.后端Datatable转EXCEL代码如下：

public byte[] DataTableToExcel(DataTable data)
        {

            ISheet sheet = null;
            IWorkbook workbook = new XSSFWorkbook();
            try
            {
                if (workbook != null)
                {
                    sheet = workbook.CreateSheet("Sheet1");
                }

                //读取标题  
                IRow rowHeader = sheet.CreateRow(0);
                for (int i = 0; i < data.Columns.Count; i++)
                {
                    ICell cell = rowHeader.CreateCell(i);
                    cell.SetCellValue(data.Columns[i].ColumnName);
                }

                //读取数据  
                for (int i = 0; i < data.Rows.Count; i++)
                {
                    IRow rowData = sheet.CreateRow(i + 1);
                    for (int j = 0; j < data.Columns.Count; j++)
                    {
                        ICell cell = rowData.CreateCell(j);
                        cell.SetCellValue(data.Rows[i][j].ToString());
                    }
                }
                NpoiMemoryStream ms = new NpoiMemoryStream();
                ms.AllowClose = false;
                workbook.Write(ms);
                ms.Flush();
                ms.Seek(0, SeekOrigin.Begin);
                var result = ms.ToArray();
                ms.AllowClose = true;
                workbook.Close();
                ms.Dispose();
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

 

1.将上述代码取到的数据转为File传回前端

var file = excelHelper.DataTableToExcel(data);
return File(file, "application/mx-excel", "fileContents.xlsx");

 

3.前端拿到数据生成超链接下载excel：

ar xhr = new XMLHttpRequest();
                    xhr.open("POST", "/Test/xxx?param='test'" , true);
                    xhr.responseType = "blob";
                    xhr.onload = function (oEvent) {
                        var content = xhr.response;
                        if (content.type == 'application/json') {
                            const fr = new FileReader(); //FileReader可以读取Blob内容
                            fr.readAsText(content, 'utf-8');
                            fr.onload = function (e) {
                                let resData = JSON.parse(fr.result);  // 解析对象成功，说明是json数据
                                if (resData.success == "false") {
                                    that.$message.error('导出失败' + resData.errorMessage);
                                }
                            }
                        }
                        else {
                            var elink = document.createElement('a');
                            elink.download = new Date().getTime() + "导出.xlsx";
                            elink.style.display = 'none';

                            var blob = new Blob([content]);
                            elink.href = URL.createObjectURL(blob);

                            document.body.appendChild(elink);
                            elink.click();
                            document.body.removeChild(elink);
                            that.$message.success('导出成功');
                        }
                        $('#loading').hide();
                        $('#kv-explorer').val("");
                    };
                    xhr.send();