ClosedXML doesn't work within AWS Lambda Serverless App

[augustoproiete]

• [x] Bug

Version of ClosedXML 0.94.2

What is the current behavior?

ASP .NET Core Web API hosted on AWS Lambda using ClosedXML to create an Excel file fails with an exception:

System.DllNotFoundException: Unable to load shared library 'libdl' or one of its dependencies

Exception stack trace:

System.TypeInitializationException: The type initializer for 'Gdip' threw an exception. ---> System.DllNotFoundException: Unable to load shared library 'libdl' or one of its dependencies. In order to help diagnose loading problems, consider setting the LD_DEBUG environment variable: liblibdl: cannot open shared object file: No such file or directory
   at IntPtr Interop+Libdl.dlopen(string fileName, int flag)
   at IntPtr System.Drawing.SafeNativeMethods+Gdip.LoadNativeLibrary()
   at static System.Drawing.SafeNativeMethods+Gdip()
   --- End of inner exception stack trace ---
   at int System.Drawing.SafeNativeMethods+Gdip.GdipGetGenericFontFamilySansSerif(out IntPtr fontfamily)
   at IntPtr System.Drawing.FontFamily.GetGdipGenericSansSerif()
   at FontFamily System.Drawing.FontFamily.get_GenericSansSerif()
   at void System.Drawing.Font.CreateFont(string familyName, float emSize, FontStyle style, GraphicsUnit unit, byte charSet, bool isVertical)
   at Font ClosedXML.Excel.FontBaseExtensions.GetCachedFont(IXLFontBase fontBase, Dictionary<IXLFontBase, Font> fontCache)
   at double ClosedXML.Excel.FontBaseExtensions.GetWidth(IXLFontBase fontBase, string text, Dictionary<IXLFontBase, Font> fontCache)
   at IXLColumn ClosedXML.Excel.XLColumn.AdjustToContents(int startRow, int endRow, double minWidth, double maxWidth)
   at IXLColumns ClosedXML.Excel.XLColumns.AdjustToContents()+(XLColumn c) => { }
   at void System.Collections.Generic.List<T>.ForEach(Action<T> action)
   at IXLColumns ClosedXML.Excel.XLColumns.AdjustToContents()
   at IActionResult Api.Controllers.ValuesController.GetExcelClosedXml() in D:/agent01/w/8c3be5c345025092/src/Api/Controllers/ValuesController.cs:line 92
   at object lambda_method(Closure, object, object[])
   at object Microsoft.Extensions.Internal.ObjectMethodExecutor.Execute(object target, object[] parameters)
   at ValueTask<IActionResult> Microsoft.AspNetCore.Mvc.Internal.ActionMethodExecutor+SyncActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, object controller, object[] arguments)
   at async Task Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeActionMethodAsync()
   at async Task Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.InvokeNextActionFilterAsync()

What is the expected behavior or new feature?

It should... Just Work™ 😃

Did this work in previous versions of our tool? Which versions? Don't know / probably not.

Reproducibility

[HttpGet]
public IActionResult GetExcelClosedXml()
{
    using (var workbook = new XLWorkbook())
    {
        var worksheet = workbook.Worksheets.Add("Items");
        worksheet.Cell("A1").Value = "Name";
        worksheet.Cell("B1").Value = "Value (%)";

        worksheet.Cell("A2").Value = "Item 1";
        worksheet.Cell("B2").Value = 0.35;
        worksheet.Cell("B2").Style.NumberFormat.Format = "0.00%";

        worksheet.Columns().AdjustToContents();

        var memoryStream = new MemoryStream();

        workbook.SaveAs(memoryStream);
        memoryStream.Seek(0, SeekOrigin.Begin);

        return File(memoryStream, contentType: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        fileDownloadName: "items.xlsx");
    }
}

[augustoproiete]

A little more color on this issue:

As can be seen in the stacktrace above, the exception above is being caused by the call to AdjustToContents.

Removing that call, however, produces a corrupt file.

Example of a corrupt file generated by ClosedXML: items.xlsx

---

• [x] I attached a sample spreadsheet.

[Pankraty]

It is a known issue (see #1020, #1107, #1121, #1251, #1257). AdjustToContent relies on System.Drawing.Common and unless it becomes truly cross-platform or we migrate to some other graphic library AdjustToContent will not work properly.

As for the file being corrupted, it seems very strange as the file you attached is not even considered a valid zip archive: Try use this library (https://github.com/ClosedXML/ClosedXML.Extensions.WebApi) to create a response and see if it helps.

[augustoproiete]

It is a known issue (see #1020, #1107, #1121, #1251, #1257)...

Thank you @Pankraty. Is there any desire to remove the dependency on System.Drawing.Common and replace it with a cross-platform library (i.e. Something that is in the maintainers roadmap or that would be considered in a PR)?

Alternatively, is there any desire to isolate these calls somehow, and have a switch to turn these calls off - limiting the features available but at least having basic features working without throwing exceptions?

[augustoproiete]

As for the file being corrupted, it seems very strange as the file you attached is not even considered a valid zip archive

Thanks. I'll investigate further. It could be something else on AWS corrupting the file - i.e. not related to ClosedXML.

I tried ClosedXML.Extensions.WebApi and it didn't work as per described in ClosedXML.Extensions.WebApi/#1, but I don't believe it's meant to work with ASP .NET Core Web API anyway.

[Lonli-Lokli]

Issue with Drawing on lambda can be fixed by adding two refs into net core project

1. https://www.nuget.org/packages/System.Drawing.Common/ >=4.5.0
2. https://www.nuget.org/packages/runtime.linux-x64.CoreCompat.System.Drawing/

[augustoproiete]

Thanks @Lonli-Lokli I assume you mean adding those dependencies in to the ClosedXML assembly rather than on my on project, right? Just for the sake of it, I did test adding those to my own project, and it didn't make any difference... I still got the same errors in AWS.

[augustoproiete]

As for the file being corrupted, it seems very strange as the file you attached is not even considered a valid zip archive

Thanks. I'll investigate further. It could be something else on AWS corrupting the file - i.e. not related to ClosedXML.

@Pankraty: Just to circle back on the corrupted file issue. As suspected, it is not related to ClosedXML. AWS's API Gateway was corrupting the file by not treating it as binary.

For the benefit of people in the future that may find this issue via search, the fix was to set binary media types in AWS's API Gateway for the file types I'm using:

• application/octet-stream
• application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
• multipart/form-data
• */*

[Lonli-Lokli]

@caioproiete Actually I mean to your project. Is it .net core 2.x?

Issue with missed libgdi on Linux can be resolved with this usecase - it allows to search for missed libraries in application folder, not only in the system. Do you see Linux libraries on output before uploading to aws?

[Zev-Engineer]

The issue surfaces even when the user does not manually call AdjustToContents. I am working on an Azure Functions project as well, and part of the project involves saving some data as an Excel file to later send in an email. The code was written based on this example: https://github.com/closedxml/closedxml/wiki/Adding-DataTable-as-Worksheet

The following line causes Visual Studio to throw an exception: wb.Worksheets.Add(data); // data is a DataTable, previously created and filled

Here is the exception message: Exception while executing function: SendEmail. System.Drawing.Common: System.Drawing is not supported on this platform.

Is there a recommended solution for this issue?

Edit: Adding the dataTable throws an exception; however, adding a new worksheet and directly editing its cells works. I have gone with the latter implementation for my project, but this is still an issue for anyone who attempts to add a dataTable directly- and is definitely an issue where adjusting cells to content is desired.

[amanbedi18]

@Zev-Engineer We faced the same issue in .NET Core based Azure functions. Tried "wb.Worksheet(i).Cell(int row, int column).InsertTable(DataTable data);" & it worked! You/others can use the below code to populate excel sheet directly from data table without manually creating each cell for the table as well as preventing the system.drawing dependency:

        for (int i = 1; i <= dataSet.Tables.Count; i++)
        {
            wb.Worksheets.Add(dataSet.Tables[i - 1].TableName);
            wb.Worksheet(i).Cell(1, 1).InsertTable(dataSet.Tables[i - 1]);
        }

        wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        wb.Style.Font.Bold = true;
        wb.Worksheets.ToList().ForEach(ws =>
        {
            ws.Tables.First().SetShowAutoFilter(false);
            //// cannot use auto resize functionality due to app service sandbox restriction preventing 
            //// call to system.drawing dll.
            ////ws.Columns().AdjustToContents();
            ws.Rows().First().Style.Font.FontColor = XLColor.WarmBlack;
        });

Hope this helps!

[ljani]

If I understood correctly, .NET 7 will completely remove cross-platform support for System.Drawing.Common:

Alternatively, you can enable support for non-Windows platforms in .NET 6 by setting the System.Drawing.EnableUnixSupport runtime configuration switch to true in the runtimeconfig.json file:

{
    "configProperties": {
        "System.Drawing.EnableUnixSupport": true
    }
}

This configuration switch was added to give cross-platform apps that depend heavily on this package time to migrate to more modern libraries. However, non-Windows bugs will not be fixed. In addition, this switch has been removed in .NET 7.

The source has some recommended alternatives:

ImageSharp SkiaSharp Microsoft.Maui.Graphics