C# Blazor Export To Excel File Format or File Extension is not valid-CodePudding

I have a list that I am trying to export to excel using the following code with the ClosedXML nuget package and following a guide online:

 private async TaskClickExportXLS()
        {
            
            await ExportToExcel(js, selectedFlyer.DealNo "_SKY_CAT_Report.xlsx");
        }
        public async Task ExportToExcel(IJSRuntime js, string fileName)
        {
            blockProducts = blocks.SelectMany(b => b.Products).Distinct().ToList();
            var wb = new XLWorkbook();
            

            var ws = wb.Worksheets.Add("Sku Category Report");

            ws.Cell(1, 1).Value = "SKU";
            ws.Cell(1, 2).Value = "Product EN";
            ws.Cell(1, 3).Value = "Product FR";
            ws.Cell(1, 4).Value = "Category";
           
            foreach(var item in blockProducts)
            {
                ws.Cell(1, 1).Value = item.Sku;
                ws.Cell(1, 2).Value = item.ProductEn;
                ws.Cell(1, 3).Value = item.ProductFr;
                ws.Cell(1, 4).Value = item.MainCategory;
            }

            MemoryStream XLSStream = new();
            XLSStream.Position = 0;
            wb.SaveAs(XLSStream);
            
            var XLSSArray = XLSStream.ToArray();

            await js.InvokeVoidAsync("BlazorDownloadFile", fileName, XLSSArray);
        }

I alsocopied this js runtime file from the guide as well:

function BlazorDownloadFile(filename, content) {


    // Create the URL
    const file = new File([content], filename, { type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" });
    const exportUrl = URL.createObjectURL(file);

    // Create the <a> element and click on it
    const a = document.createElement("a");
    document.body.appendChild(a);
    a.href = exportUrl;
    a.download = filename;
    a.target = "_self";
    a.click();
    URL.revokeObjectURL(exportUrl);
}

My file downloads but when it opens I get the "Excel cannot open the file because the file format or extension is not valid". I have went online and did research and the things I've tried are the following - Set the Stream position to 0. Change the type of the file in my openfile.js to the following: type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" This does not seem the fix the issue and my code seems to work fine when debugging. Any help would be appreciated!

CodePudding user response：

I had tried to follow that guide and it did not work for me. What I ended up doing is streaming my workbook (wb) into a byte array and passing it as a parameter to a SaveAs function:

var bytes = new byte[0];
using (var ms = new MemoryStream())
{
     wb.SaveAs(ms);
     bytes = ms.ToArray();
}

await SaveAs(JSRuntime, fileName   ".xlsx", bytes);

This is the SaveAs function which is async Task like the comments were suggesting:

async Task SaveAs(IJSRuntime js, string fileName, byte[] data)
{
    await js.InvokeAsync<object>(
        "BlazorDownloadFile",
        fileName,
        Convert.ToBase64String(data)
    );
}

And here is the js:

function BlazorDownloadFile(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);
}

I believe these are the references I used: https://gist.github.com/danielplawgo/ac4d58837224dba7b6fc51de865b12da https://blazorfiddle.com/s/o8g3elz1