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

Question

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!

`async void` is a bug. It's only meant for event handlers in desktop applications. Use `async Task` instead. Methods with `async void` can't be awaited so the code that calls them will complete without waiting for them to finish and probably trigger a garbage collection that will dispose the objects used by the async method. — Panagiotis Kanavos, Mar 28 '22 at 15:08
@PanagiotisKanavos this one https://www.peug.net/en/blazor-create-or-export-your-data-to-excel/ — Adil15, Mar 28 '22 at 15:11
@PanagiotisKanavos I've switched it to async Task but I'm still getting the same error — Adil15, Mar 28 '22 at 15:14
To understand what that JS code does, check [this SO answer](https://stackoverflow.com/questions/68393235/download-file-from-server-with-blazor-app/68398092#68398092) (yes it's mine, but the actual technique is copied from Meziantou's article). That works. I've actually used it in my own Excel generation code, although I use EPPlus. You need to create some tests (even console apps) to find out where the problem is. For starters, extract the Excel generation code to another method or even class — Panagiotis Kanavos, Mar 28 '22 at 15:22
You can call that class from a unit test and save the `byte[]` output to a file. Can you load it? What if you save directly to a file from a unit test, can that file be loaded? Does the `XLSSArray` buffer contain anything? — Panagiotis Kanavos, Mar 28 '22 at 15:25

score 4 · Accepted Answer · answered Mar 28 '22 at 15:34

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

Thank you so much for posting this solution ~ it worked a treat for me! — Jiving Rockabilly, Apr 26 '23 at 15:54

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

1 Answers1