Closed xml downloaded excel file is corrupted when downloading from AWS Lambda

[hareeshmakani]

I have created a simple .net6 web api which uses closed xml to download the excel file. When tested in local it's working as expected. But after deploying on AWS and trying to access the file getting the error file is corrupted.

I have used open xml which is another framework to generate excel file. I could see the same issue with this one as well. Need help to fix this issue.

Here is the controller code.

public ActionResult ExportExcel()
{
    var empData = GetEmpdata();
    using (XLWorkbook wb = new XLWorkbook())
    {
        var sheet1 = wb.AddWorksheet(empData, "Employee Records");

        sheet1.Column(1).Style.Font.FontColor = XLColor.Red;
        sheet1.Columns(2,4).Style.Font.FontColor = XLColor.Blue;

        sheet1.Row(1).CellsUsed().Style.Fill.BackgroundColor = XLColor.Black;
        sheet1.Row(1).Style.Font.FontColor = XLColor.White;

        sheet1.Row(1).Style.Font.Bold = true;
        sheet1.Row(1).Style.Font.Shadow = true;
        sheet1.Row(1).Style.Font.Underline = XLFontUnderlineValues.Single;
        sheet1.Row(1).Style.Font.VerticalAlignment = XLFontVerticalTextAlignmentValues.Superscript;
        sheet1.Row(1).Style.Font.Italic = true;

        sheet1.Rows(2, 3).Style.Font.FontColor = XLColor.AshGrey;

        using (MemoryStream ms = new MemoryStream())
        {
            wb.SaveAs(ms);

            var file =  File(ms.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Sample.xlsx");

            return file;
        }
    }
}

private DataTable GetEmpdata()
{
    DataTable dt = new DataTable()
    {
        TableName = "EmpData"
    };

    dt.Columns.Add("Code", typeof(int));
    dt.Columns.Add("Name", typeof(string));
    dt.Columns.Add("Email", typeof(string));
    dt.Columns.Add("Phone", typeof(string));
    dt.Columns.Add("Designation", typeof(string));

    var _list = new List<Employee>
    {
        new Employee
        {
            Code = 1,
            Designation = "Manager",
            Email = "[email protected]",
            Name = "John",
            Phone = "12345"
        }
    };

    if(_list.Count > 0 )
    {
        _list.ForEach(item =>
        {
            dt.Rows.Add(item.Code,item.Name,item.Email,item.Phone,item.Designation);
        });
    }
    return dt;
}

public class Employee
{
    public int Code { get; set; }
    public string? Name { get; set; }
    public string? Email { get; set; }
    public string? Phone { get; set; }
    public string? Designation { get; set; }
}

Nuget packages

<PackageReference Include="Amazon.Lambda.AspNetCoreServer.Hosting" Version="1.6.1" />
<PackageReference Include="ClosedXML" Version="0.102.2" />

Program class
builder.Services.AddAWSLambdaHosting(LambdaEventSource.ApplicationLoadBalancer);

[ashishdhingra]

@hareeshmakani Kindly confirm if you have tried Enabling binary support using the API Gateway console.