Closed XML.Reports Function app file not saving

Archive1 20

I'm trying to write a function app which generates a report using ClosedXML.Reports. The output template is quite simple and only uses the summaryabove template. The function app is able to create a text file and save it to blob storage successfully. But when I try to get it to create a file using ClosedXML.Reports to save to blob storage it fails in the file generation stage. I've not seen many examples online which use ClosedXML.Reports with a function app to save to blob storage, so I'm not sure exactly where I'm going wrong. The template file is being uploaded to Azure as part of the package when I publish from VSCode and I'm only testing in Azure portal using test and run along with log stream messages to understand what's happening internally. Is anyone able to help?

Loknathsatyasaivarma Mahali 430 Reputation points Microsoft Vendor

2025-02-04T00:52:10.47+00:00
Hello @Archive1,

Welcome to the Microsoft Q&A Platform! Thank you for asking your question here.

I understand that you are trying to write a function app that generates a report using ClosedXML.Reports and saves it to Azure Blob Storage. While you are successfully able to create a text file and save it to blob storage, it seems that the function fails during the file generation stage when using ClosedXML.Reports.

First ensure that the template file is correctly uploaded to Azure and is accessible by the function app. You can verify this by checking that the file exists in the blob storage container and that the function app has the necessary permissions to access it.

Then review the log stream messages for any error messages or exceptions that may be thrown during the file generation stage. This can provide valuable insights into the specific issue causing the failure.

Also confirm that the ClosedXML.Reports library is installed correctly and is being utilized properly in your function app. Review your code to ensure that the library is referenced correctly.

At last, consider implementing a try-catch block around the file generation code. This will allow you to catch any exceptions that may occur and provide more detailed error messages, helping you identify the root cause of the problem.

I hope the above provided information will helps in better understanding and solve your queries, if you have any further concerns, please feel free to reach out to us.
Loknathsatyasaivarma Mahali 430 Reputation points Microsoft Vendor

2025-02-07T01:15:52.57+00:00

Hello @Archive1,

This is a friendly follow-up to check if the information provided above helped you understand the issue better and addressed your concerns. If you have any further questions or concerns, please feel free to reach out to us.
Loknathsatyasaivarma Mahali 430 Reputation points Microsoft Vendor

2025-02-10T02:17:45.11+00:00

Hello @Archive1,

We wanted to follow up regarding the issue you recently encountered. As we haven't heard back from you, we wanted to ensure that everything is resolved to your satisfaction. If you still require assistance or have any further questions, please do not hesitate to reach out to us. We are here to help and ensure that your experience with our service is seamless.

Archive1 20

Thanks for your detailed response Loknathsatyasaivarma

The function app is using a system assigned managed identity and has the "Storage Account Contributor" role for the storage account but the template file is not being correctly uploaded to Azure.

The csproj file is set to copytoutputdirectory (see bottom XML) and then the code tries to use the path to generate a new file based on the path /azure-functions-host/Template.xlsx

But it can't find the file at runtime

                       // Load the template
                        var templatePath = AppContext.BaseDirectory + "Template.xlsx";
                        if (!File.Exists(templatePath))
                        {
                            throw new FileNotFoundException($"Could not find document: {templatePath}");
                        }
                        var template = new XLTemplate(templatePath);

  <ItemGroup>
    <None Update="host.json">
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>
    <None Update="local.settings.json">
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
      <CopyToPublishDirectory>Never</CopyToPublishDirectory>
    </None>
    <None Update="Template.xlsx">
      <CopyToOutputDirectory>Always</CopyToOutputDirectory>
    </None>  
  </ItemGroup>

Loknathsatyasaivarma Mahali 430 Reputation points Microsoft Vendor

2025-02-11T06:04:02.82+00:00
Hello @Archive1,

Thanks for sharing the additional information, Based on the information you provided, it seems that the issue might be related to the path of the template file. The code is currently using the path "/azure-functions-host/Template.xlsx" to generate a new file, but it seems that the file is not being found at runtime.

One thing you could try is to use the AppContext.BaseDirectory property to get the base directory of the application and then append the file name to it. This should give you the correct path to the template file. Here's an example:

var templatePath = Path.Combine(AppContext.BaseDirectory, "Template.xlsx");

You could also try to check if the file exists before attempting to load it. This can be done using the File.Exists method. Here's an example:

if (!File.Exists(templatePath)) { throw new FileNotFoundException($"Could not find document: {templatePath}"); }

Additionally:

Also, ensure that the Template.xlsx file is included in your deployment package. You can check this by accessing the Azure portal and navigating to the "App Service Editor" or "Kudu" console to confirm that the file is present in the site/wwwroot directory.

Consider adding logging to your function to capture the value of templatePath and whether the file exists. This can help you diagnose any issues more effectively.

I hope this helps! Let me know if you have any further questions.
Archive1 20 Reputation points

2025-02-11T08:04:03.81+00:00
Thanks for your answer, I tried that but it didn't work so I ended up using using the executionContext to get the FunctionAppDirectory instead and that worked (see below)

So now I'm grappling with trying to get ClosedXML.Reports to use a field which I'm retrieving from another datasource to populate a field in the generated Excel template.

I'm only trying to set one field to start with but the resulting excel file has this repeated horizontally across the row where the single field should be set
Unknown identifier 'HeaderField'

template.AddVariable("HeaderField", headerfield);

string funcAppDirectory = context.FunctionAppDirectory; log.LogInformation($"Function app directory: {funcAppDirectory}"); var templatePath = Path.Combine(funcAppDirectory, "ExcelTemplate/Template.xlsx"); log.LogInformation($"Constructed template path: {templatePath}");
Archive1 20 Reputation points

2025-02-11T09:07:10.57+00:00

Ignore me, I figured that one out too, for this kind of example, the template doesn't need a range or name defined in Excel, it just needs a cell with {{HeaderField}} in plain text and it replaces it as expected.

It throws that error because it found a range when it didn't expect one.

Accepted answer

Loknathsatyasaivarma Mahali 430 Reputation points Microsoft Vendor

2025-02-11T15:15:01.7833333+00:00

Hello @Archive1,

Thank you for your patience and for sharing your feedback on the Q&A community platform. I’m glad to hear that you were able to resolve your issue, and I appreciate you sharing your solution! Your contribution is valuable and can help others in the community facing similar challenges.

As per the Microsoft Q&A community policy, "The question author cannot accept their own answer. They can only accept answers by others"

I’m reposting your solution here so you can mark it as accepted if it resolves your query:

The solution involved here is that template doesn't need a range or name defined in Excel, it just needs a cell with {{HeaderField}} in plain text and it replaces it as expected.

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.
Please sign in to rate this answer.

1 person found this answer helpful.
Archive1 20 Reputation points

2025-02-12T08:34:14.1733333+00:00

Thanks for that.

Also to add, the answer to the original question of how to get the function app to load the file template in the first place by passing the executionContext to the run method.

public async Task<IActionResult> Run( [HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest req, ExecutionContext context, //<<<<< added this ILogger log) ..... // Load the template var templatePath = AppContext.BaseDirectory + "Template.xlsx"; if (!File.Exists(templatePath)) { throw new FileNotFoundException($"Could not find document: {templatePath}"); } var template = new XLTemplate(templatePath);
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Closed XML.Reports Function app file not saving

0 additional answers

Your answer