Blazor 导出到 Excel

Question

我正在尝试在我的Blazor服务器端应用程序上添加一个导出到Excel的按钮。到目前为止，在搜索互联网后，我已经完成了以下步骤。

我的按钮：

    <div class="row text-right">
                <div class="col-12 p-3">
                    <button class="btn btn-outline-success" @onclick="@(() =>DownloadExcel(formValues.Region, formValues.startDate, formValues.endDate))">
                        Export to Excel&nbsp;
                        <i class="fa fa-file-excel" aria-hidden="true"></i>
                    </button>
               </div>
            </div>

在我的 .razor 页面中，我的方法如下：

    public FileResult DownloadExcel(string Region, DateTime StartDate, DateTime EndDate)
    {
        FileResult ExcelFile = searchService.ExportToExcel(Region, StartDate, EndDate);
        return ExcelFile;
    }

最后，我的服务逻辑是：

        public FileResult ExportToExcel(string Region, DateTime StartDate, DateTime EndDate)
        {
            var queryable = context.AuditCardPinrecords.Where(s => Region == s.RegionRecordId)
                .Where(s => s.AuditComplete == true)
                .Where(s => s.DateTime >= StartDate && s.DateTime <= EndDate).AsQueryable();

            var stream = new MemoryStream();

            using (var package = new ExcelPackage(stream))
            {
                var workSheet = package.Workbook.Worksheets.Add("Sheet1");
                workSheet.Cells.LoadFromCollection(queryable, true);
                package.Save();
            }


            string excelName = $"AuditPinRecords-{DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xlsx";

            return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", excelName); 

        }

我期望的结果是下载Excel文件。不幸的是，在点击按钮时没有任何反应。如有建议，将不胜感激。谢谢！

- Adil15

---

我认为你不能只返回一个FileResult并期望它自动提供给用户。你可以考虑提供一个指向MVC端点的链接，这样用户就可以直接从服务器下载它。 - mason

Answer 1

我有类似的需求，并通过这个项目中的JavaScript拼凑出了如何实现它的方法：https://github.com/timplourde/dcidr-blazor

静态实用服务：

public static class ExcelService
{
    public static byte[] GenerateExcelWorkbook()
    {
        var list = new List<UserInfo>()
        {
            new UserInfo { UserName = "catcher", Age = 18 },
            new UserInfo { UserName = "james", Age = 20 },
        };
        var stream = new MemoryStream();

        // ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        using (var package = new ExcelPackage(stream))
        {
            var workSheet = package.Workbook.Worksheets.Add("Sheet1");

            // simple way
            workSheet.Cells.LoadFromCollection(list, true);

            ////// mutual
            ////workSheet.Row(1).Height = 20;
            ////workSheet.Row(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            ////workSheet.Row(1).Style.Font.Bold = true;
            ////workSheet.Cells[1, 1].Value = "No";
            ////workSheet.Cells[1, 2].Value = "Name";
            ////workSheet.Cells[1, 3].Value = "Age";

            ////int recordIndex = 2;
            ////foreach (var item in list)
            ////{
            ////    workSheet.Cells[recordIndex, 1].Value = (recordIndex - 1).ToString();
            ////    workSheet.Cells[recordIndex, 2].Value = item.UserName;
            ////    workSheet.Cells[recordIndex, 3].Value = item.Age;
            ////    recordIndex++;
            ////}

            return package.GetAsByteArray();
        }
    }
}

public class UserInfo
{
    public string UserName { get; set; }
    public int Age { get; set; }
}

在wwwroot文件夹中创建一个带有site.js文件的js文件夹。

function saveAsFile(filename, bytesBase64) {
    var link = document.createElement('a');
    link.download = filename;
    link.href = "data:application/octet-stream;base64," + bytesBase64;
    document.body.appendChild(link); // Needed for Firefox
    link.click();
    document.body.removeChild(link);
}

在您的 _Host.cshtml 文件中，在 body 部分添加以下脚本。

<script src="~/js/site.js"></script>

在您想从中导出Excel的.razor页面中

@using YOUR_APP_NAME.Services

@inject IJSRuntime js

<Row Class="d-flex px-0 mx-0 mb-1">
    <Button Clicked="@DownloadExcelFile" class="p-0 ml-auto mr-2" style="background-color: transparent" title="Download">
        <span class="fa fa-file-excel fa-lg m-0" style="color: #008000; background-color: white;" aria-hidden="true"></span>
    </Button>
</Row>

@code {
    private void DownloadExcelFile()
    {
        var excelBytes = ExcelService.GenerateExcelWorkbook();
        js.InvokeVoidAsync("saveAsFile", $"test_{DateTime.Now.ToString("yyyyMMdd_HHmmss")}.xlsx", Convert.ToBase64String(excelBytes));
    }
}

- Brent Wilderman

---

非常好的逐步说明。昨天花了大约7个小时来尝试弄清楚这个问题，但现在它可以工作了。谢谢Brent！ - David Hicks

Answer 2

.razor组件不像MVC视图一样是HTTP端点。你的返回FileResult不会触发浏览器下载。

你需要创建一个MVC控制器操作并将用户重定向到那里，或者使用JavaScript调用文件保存操作。然后你需要使用JavaScript Interop来调用JS函数。

window.msSaveBlob = function (payload, filename) {

    const createBlob = data => new Blob([data], { type: "text/csv;charset=utf-8;" });

    const buildDownloadLink = (blob, fileName) => {
        let link = document.createElement("a");
        link.setAttribute("href", URL.createObjectURL(blob));
        link.setAttribute("download", fileName);
        link.style = "visibility:hidden";
        return link;
    };
    const invokeDownload = link => {
        document.body.appendChild(link);
        link.click();
        document.body.removeChild(link);
    };
    const isHtmlDownloadAllowed = document.createElement("a").download !== undefined;
    const isSaveBlobAllowed = navigator.msSaveBlob;

    isSaveBlobAllowed ? navigator.msSaveBlob(createBlob(payload), filename) :
        isHtmlDownloadAllowed ? invokeDownload(buildDownloadLink(createBlob(payload), filename)) :
            console.log("Feature unsupported");

};

当然，也有商业库可帮助处理这些事情。Telerik UI for Blazor Document Processing

- Ed Charbeneau

Answer 3

下面展示的方法适用于Blazor Server和WASM，无需使用JavaScript。我使用了Aspose.Cells，但它并不依赖于任何特定的Excel Nuget包。

控制器：

[HttpGet("~/GetExcelReport")]
public IActionResult ExcelReport(string id)
{
        var license = new Aspose.Cells.License();
        license.SetLicense("Aspose.Total.NET.lic");

        var wb = new Workbook();
        var ws = wb.Worksheets[0];
        var cells = ws.Cells;
        ws.Name = "LW310";

        int rowNo = 0;
        
        cells[rowNo, 0].PutValue("Step");
        cells[rowNo, 1].PutValue("Prompt");
        cells[rowNo, 2].PutValue("Value");

        using (var ms = new MemoryStream())
        {
                wb.Save(ms, SaveFormat.Xlsx);
                ms.Position = 0;

                byte[] data = ms.ToArray();
                string fileName = "LW310_" + String.Format("{0:MM_dd_yyyy_hh_mm_ss_tt}", DateTime.Now) + ".xlsx";

                if (data != null)
                        return File(data, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);
                else
                        return new EmptyResult();
        }
}

剃刀页面：

<button @onclick="ExportExcel">Export Excel</button>

@inject NavigationManager navMgr
async Task ExportExcel() => await Task.Run(() => navMgr.NavigateTo($"./getexcelreport?id={"testId"}", true));

- Stephen Pham