Create Or Update Excel Sheet From Azure Web Job

In this article, I would like to share the steps and code to create/update an Excel sheet from an Azure web job. Using Microsoft.Interop.Excel, we can create and update the Excel sheet but in Azure web jobs, we couldn’t use that DLL file, instead of Microsoft. Interop dll, we can use the OpenXML spreadsheet option to create or update the Excel from the Azure web job.

Let’s see how to create an Azure web job along with the deployment steps and approaches to create or update Excel.

What are the available options to create or update Excel?

• Using the Microsoft.Interop.Excel
• OLEDB Connection
• OpenXML Spreadsheet
• Microsoft Excel 15.0 Object Library
• Third-party DLLs

Here, I would like to share the code for the OpenXML spreadsheet, since Microsoft.Interop.Excel and OLEDB can not work on Azure web jobs.

What is an Azure Web Job?

Web Jobs is a feature of Azure App Service that enables you to run a program or script in the same context as a web app, API app, or mobile app. There is no additional cost to use Web Jobs.

Steps to Create Azure Web Job

Step 1. Open Visual Studio on your machine (I’m using 2015 VS).

Step 2. Then, select the “Azure Web Job” from Visual Studio.

Step 3. Provide the name for the web job, select the location, and click “OK” to create a new solution.

Your project will be created successfully.

Step 4. After the project is created, just build the solution to restore the NuGet packages.

Step 5. Then, write the code inside the main function. In the sample, I’ve retrieved the data from the SQL DB table using View and now I am updating in the Excel sheet.

Required DLL’s

using System.Runtime.InteropServices;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Collections.Generic;
using DocumentFormat.OpenXml;

References

Here is the sample code for the ExportDataSetToExcel method.

• Pass your dataset on the method
• Sheet Name
• SPath for the Excel sheet and

// Write the view data to the excel
private static void ExportDataSetToExcel(DataSet ds, string SheetName, string reportPath)
{
    string result = string.Empty;
    try
    {
        string filePath = reportPath;
        if (File.Exists(filePath))
        {
            File.Delete(filePath);
        }
        
        if (ds.Tables.Count > 0 && ds.Tables[0] != null && ds.Tables[0].Columns.Count > 0)
        {
            System.Data.DataTable table = ds.Tables[0];
            
            using (var spreadsheetDocument = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
            {
                // Create SpreadsheetDocument
                WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
                workbookPart.Workbook = new Workbook();
                var sheetPart = spreadsheetDocument.WorkbookPart.AddNewPart<WorksheetPart>();
                var sheetData = new SheetData();
                sheetPart.Worksheet = new Worksheet(sheetData);
                Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
                string relationshipId = spreadsheetDocument.WorkbookPart.GetIdOfPart(sheetPart);
                Sheet sheet = new Sheet()
                {
                    Id = relationshipId,
                    SheetId = 1,
                    Name = table.TableName
                };
                sheets.Append(sheet);
                
                // Add header to sheetData
                Row headerRow = new Row();
                List<string> columns = new List<string>();
                
                foreach (DataColumn column in table.Columns)
                {
                    columns.Add(column.ColumnName);
                    Cell cell = new Cell();
                    cell.DataType = CellValues.String;
                    cell.CellValue = new CellValue(column.ColumnName);
                    headerRow.AppendChild(cell);
                }
                
                sheetData.AppendChild(headerRow);
                
                // Add cells to sheetData
                foreach (DataRow row in table.Rows)
                {
                    Row newRow = new Row();
                    
                    columns.ForEach(col =>
                    {
                        Cell cell = new Cell();
                        // If value is DBNull, do not set value to cell
                        if (row[col] != System.DBNull.Value)
                        {
                            cell.DataType = CellValues.String;
                            cell.CellValue = new CellValue(row[col].ToString());
                        }
                        newRow.AppendChild(cell);
                    });
                    
                    sheetData.AppendChild(newRow);
                }
                
                Console.WriteLine($"Export {table.Rows.Count} rows of data to excel successfully.");
                spreadsheetDocument.Close();
            }
        }
        
        // string storageConnectionString = ConfigurationManager.ConnectionStrings["AzureWebJobsStorage"].ConnectionString.ToString();
        // var storageAccount = CloudStorageAccount.Parse(storageConnectionString);
        // CloudBlobClient cloudBlobClient = storageAccount.CreateCloudBlobClient();
        // var container = cloudBlobClient.GetContainerReference("emsreportcontainer");
        // Write the excel to Azure storage container
        // using (FileStream fileStream = File.Open(filePath, FileMode.Open))
        // {
        //     bool exists = container.CreateIfNotExists();
        //     var blob = container.GetBlockBlobReference(SheetName + ".xslx");
        //     blob.DeleteIfExists();
        //     blob.UploadFromStream(fileStream);
        // }
    }
    catch (Exception ex)
    {
        Console.WriteLine($"Export action failed. Error Message: {ex.Message}");
    }
    // return result;
}

Deployment steps

Once you are done with the code changes, build the solution in the release mode and zip the build folder. That zip file needs to be uploaded to the web job.

Follow the below-listed steps to deploy the job on the Azure portal.

Step 1. Open the Azure portal website.

Step 2. Click “App Service” from the left navigation.

Step 3. On the app service page, click the “Add” button to create a new app service if you don’t have one already.

Step 4. Then, select the “Web App” from the right-side pane and provide the app name as per your need.

Step 5. Click on the Create button. It will navigate to the web app creation page form.

Step 6. On the web app form, provide the relevant details such as “App name, Subscription, Resource Group, OS, Publish, App service plan/location, Application insights”.

Step 7. Then, select your app from the App Services page and click “web jobs” from the left navigation pane.

Step 8. Click on “Add” from the right pane provide the below details and select “OK”.

• Name
• File Upload: Upload the webjob.ZIP folder – (A .zipfile that contains your executable or script file as well as any supporting files needed to run the program or script. The supported executable or script file types are listed in the Supported file types).
• Type: Scheduled (I’ve selected scheduled because my job wants to run every day at noon)
• CRON Expression à UTC Time (0 45 5 * * *) (IST 12 PM)

CRON Expression Read more.

Step 9. The new web job appears on the web page, as shown in the below image.

Step 10. After creating the web job, select "Always on" from the application settings. Open the app service and select the application settings.

Step 11. To run the WebJob, right-click its name in the list and click "Run".

Finally, we have created the scheduled web job.

Step 12. After running the job, select the logs where we can see success and failure messages.

Summary

In this article, we have explored how to create an Azure web job, how to deploy the job in Azure, and what are the alternate options to create or update the SQL Database table values in Excel.