Articles in this section
Category / Section

How to create Excel file in Azure functions?

3 mins read

Syncfusion Excel (XlsIO) library is a .NET Excel library used to create, read, and edit Excel documents. Using this library, you can create Excel files in Azure functions.

Steps to create Excel file in Azure functions programmatically:

Step 1: Create a new Azure function project.

Create a Azure function project.

Create a new Azure function project

Step 2: Select framework Azure Functions v1 (.NET Framework) and select HTTP trigger as follows.

Select Framework for Azure function

Select framework and HTTP trigger

Step 3: Install the Syncfusion.XlsIO.WinForms NuGet package as a reference to your .NET Framework applications from NuGet.org.

Add XlsIO reference to the project

Install NuGet package

Step 4: Include the following namespaces in Function1.cs file.

C#

using System.IO;
using Syncfusion.XlsIO;
using System.Net.Http.Headers;

 

Step 5: Add the following code snippet in Run method of Function1 class to open and modify Excel document in Azure functions and return the output document to client end.

C#

Stream stream = req.Content.ReadAsStreamAsync().Result;
//Instantiate the spreadsheet creation engine
using (ExcelEngine excelEngine = new ExcelEngine())
{
    //Instantiate the Excel application object
    IApplication application = excelEngine.Excel;
 
    //Assigns default application version
    application.DefaultVersion = ExcelVersion.Excel2013;
 
    //A new workbook is created equivalent to creating a new workbook in Excel
    //Create a workbook with 1 worksheet
IWorkbook workbook = application.Workbooks. Open(stream); 
 
    //Access a worksheet from workbook
    IWorksheet worksheet = workbook.Worksheets[0];
 
    //Adding text data
    worksheet.Range["A1"].Text = "Month";
    worksheet.Range["B1"].Text = "Sales";
    worksheet.Range["A6"].Text = "Total";
 
    //Adding DateTime data
    worksheet.Range["A2"].DateTime = new DateTime(2015, 1, 10);
    worksheet.Range["A3"].DateTime = new DateTime(2015, 2, 10);
    worksheet.Range["A4"].DateTime = new DateTime(2015, 3, 10);
 
    //Applying number format for date value cells A2 to A4
    worksheet.Range["A2:A4"].NumberFormat = "mmmm, yyyy";
 
    //Auto-size the first column to fit the content
    worksheet.AutofitColumn(1);
 
    //Adding numeric data
    worksheet.Range["B2"].Number = 68878;
    worksheet.Range["B3"].Number = 71550;
    worksheet.Range["B4"].Number = 72808;
 
    //Adding formula
    worksheet.Range["B6"].Formula = "SUM(B2:B4)";
 
    MemoryStream memorystream = new MemoryStream();
 
    //Saving the workbook to stream in XLSX format
    workbook.Version = ExcelVersion.Excel2013;
    workbook.SaveAs(memorystream);
 
    //Create the response to return
    HttpResponseMessage response = new HttpResponseMessage(HttpStatusCode.OK);
 
    //Set the Excel document content response
    response.Content = new ByteArrayContent(memorystream.ToArray());
 
    //Set the contentDisposition as attachment
    response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
    {
        FileName = "Output.xlsx"
    };
    //Set the content type as xlsx format mime type
    response.Content.Headers.ContentType = new System.Net.Http.Headers.MediaTypeHeaderValue("application/vnd.openxmlformats-officedocument.spreadsheet.excel");
    //Return the response with output Excel stream
    return response;
}

 

Step 6: Right-click the project and select Publish. Then, create a new profile in the Publish Window.

Publish window screenshot

Publish Window

Step 7: Create App service using Azure subscription and select a hosting plan.

App Service screenshot

Create App Service

Note:

Syncfusion XlsIO library will work from basic hosting plan (B1). So, select the hosing plan as needed. Syncfusion XlsIO library will not work if the hosting plan is Consumption.

 

Configure hosting plan

Configure Hosting Plan

Step 8: After creating the profile, click the Publish button.

Publish Profile

Publish

Step 9: Now, go to Azure portal and select the App Services. After running the service, click Get function URL by copying it. Then, paste it to the below client sample (Which will request the Azure Function, to create Excel file in Azure functions). You will get the Excel document as below.

Output Excel document

Output Excel document

A complete Azure function sample can be downloaded from Create Excel from Azure Function.zip.

Steps to post the request to Azure function with template Excel document:

  1. Create simple console application to request the Azure function API.
  2. Add the following code snippet into Main method to request the Azure function with template Excel document and gets the modified Excel document.

C#

   //Open the required template Excel file
    Stream fileStream = File.Open(@"../../Data/Input.xlsx", FileMode.Open);
 
    //Create memory stream to save the template
    MemoryStream inputStream = new MemoryStream();
 
    //Copy the file stream into memory stream
    fileStream.CopyTo(inputStream);
 
    //Dispose the file stream
    fileStream.Dispose();
    try
    {
        //Create HttpWebRequest with hosted azure function URL
        HttpWebRequest req = (HttpWebRequest)WebRequest.Create(" Your Azure Function Url");
 
        //Set request method as POST
        req.Method = "POST";
 
        //Get the request stream to strore the Excel document stream
        Stream stream = req.GetRequestStream();
 
        //Write the Excel document stream into request stream
        stream.Write(inputStream.ToArray(), 0, inputStream.ToArray().Length);
 
        //Gets the responce from the Azure Function request.
        HttpWebResponse res = (HttpWebResponse)req.GetResponse();
 
        //Create file stream to save the output Excel file
        FileStream outStream = File.Create("Sample.xlsx");
 
        //Copy the responce stream into file stream
        res.GetResponseStream().CopyTo(outStream);
 
        //Dispose the input stream
        inputStream.Dispose();
 
        //Dispose the file stream
        outStream.Dispose();
    }
    catch (Exception ex)
    {
        throw;
    }
 
    //Launch the output document
    System.Diagnostics.Process.Start("Sample.xlsx");
}

 

Console application can be downloaded from DownloadCreatedExcel.zip.

Know more about Syncfusion Excel (XlsIO) library through the documentation, where you can find features like charts, drawing objects, chart to image conversion and worksheet to image conversion etc. with respective code examples.

Refer here to explore the rich set of Syncfusion Excel (XlsIO) library features.

Note:

Starting with v16.2.0.x, if you reference Syncfusion assemblies from trial setup or from the NuGet feed, include a license key in your projects. Refer the link to learn about generating and registering Syncfusion license key in your application to use the components without trail message.

 

Did you find this information helpful?
Yes
No
Help us improve this page
Please provide feedback or comments
Comments (0)
Please sign in to leave a comment
Access denied
Access denied