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 from an Azure web job. Using Microsoft.Interop.Excel, we can create and update the Excel sheet but in Azure web job, we couldn’t use that dll. Instead of Microsoft.Interop dll, we can use the open XML spreadsheet option to create or update the Excel.

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 the Excel.

What are the available options to create or update the 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 OpenXML spreadsheet, since Microsoft.Interop.Excel and OLEDB can not work on Azure web job.

What is 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.

Create Or Update The Excel From Azure Web Job

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.
 
Create Or Update The Excel From Azure Web Job 

Step 3

Provide the name for the web job, select the location, and click “OK” to create a new solution.
 
Create Or Update The Excel From Azure Web Job 

Your project will be created successfully.

Create Or Update The Excel From Azure Web Job 

Step 4

After the project is created, just build the solution to restore the NuGet packages.
 
Create Or Update The Excel From Azure Web Job 

Step 5

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

Required DLL’s

  1. using System.Runtime.InteropServices;  
  2. using DocumentFormat.OpenXml.Packaging;  
  3. using DocumentFormat.OpenXml.Spreadsheet;  
  4. using System.Collections.Generic;  
  5. using DocumentFormat.OpenXml;  

References

Create Or Update The Excel From Azure Web Job 

Here is the sample code for the ExportDataSetToExcel method.

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

  1. //Write the view data to the excel  
  2. private static void ExportDataSetToExcel(DataSet ds, string SheetName, string reportPath) {  
  3.     string result = string.Empty;  
  4.     try {  
  5.         string filePath = reportPath;  
  6.         if (File.Exists(filePath)) {  
  7.             File.Delete(filePath);  
  8.         }  
  9.         if (ds.Tables.Count > 0 && ds.Tables[0] != null || ds.Tables[0].Columns.Count > 0) {  
  10.             System.Data.DataTable table = ds.Tables[0];  
  11.             using(var spreadsheetDocument = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook)) {  
  12.                 // Create SpreadsheetDocument  
  13.                 WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();  
  14.                 workbookPart.Workbook = new Workbook();  
  15.                 var sheetPart = spreadsheetDocument.WorkbookPart.AddNewPart < WorksheetPart > ();  
  16.                 var sheetData = new SheetData();  
  17.                 sheetPart.Worksheet = new Worksheet(sheetData);  
  18.                 Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild < Sheets > (new Sheets());  
  19.                 string relationshipId = spreadsheetDocument.WorkbookPart.GetIdOfPart(sheetPart);  
  20.                 Sheet sheet = new Sheet() {  
  21.                     Id = relationshipId, SheetId = 1, Name = table.TableName  
  22.                 };  
  23.                 sheets.Append(sheet);  
  24.                 //Add header to sheetData  
  25.                 Row headerRow = new Row();  
  26.                 List < String > columns = new List < string > ();  
  27.                 foreach(DataColumn column in table.Columns) {  
  28.                     columns.Add(column.ColumnName);  
  29.                     Cell cell = new Cell();  
  30.                     cell.DataType = CellValues.String;  
  31.                     cell.CellValue = new CellValue(column.ColumnName);  
  32.                     headerRow.AppendChild(cell);  
  33.                 }  
  34.                 sheetData.AppendChild(headerRow);  
  35.                 //Add cells to sheetData  
  36.                 foreach(DataRow row in table.Rows) {  
  37.                     Row newRow = new Row();  
  38.                     columns.ForEach(col => {  
  39.                         Cell cell = new Cell();  
  40.                         //If value is DBNull, do not set value to cell  
  41.                         if (row[col] != System.DBNull.Value) {  
  42.                             cell.DataType = CellValues.String;  
  43.                             cell.CellValue = new CellValue(row[col].ToString());  
  44.                         }  
  45.                         newRow.AppendChild(cell);  
  46.                     });  
  47.                     sheetData.AppendChild(newRow);  
  48.                 }  
  49.                 Console.WriteLine($ "Export {table.Rows.Count} rows of data to excel successfully.");  
  50.                 spreadsheetDocument.Close();  
  51.             }  
  52.         }  
  53.         //string storageConnectionString = ConfigurationManager.ConnectionStrings["AzureWebJobsStorage"].ConnectionString.ToString();  
  54.         //var storageAccount = CloudStorageAccount.Parse(storageConnectionString);  
  55.         //CloudBlobClient cloudBlobClient = storageAccount.CreateCloudBlobClient();  
  56.         //var container = cloudBlobClient.GetContainerReference("emsreportcontainer");  
  57.         //// Write the excel to Azure storage container  
  58.         //using (FileStream fileStream = File.Open(filePath, FileMode.Open))  
  59.         //{  
  60.         // bool exists = container.CreateIfNotExists();  
  61.         // var blob = container.GetBlockBlobReference(SheetName + ".xslx");  
  62.         // blob.DeleteIfExists();  
  63.         // blob.UploadFromStream(fileStream);  
  64.         //}  
  65.     } catch (Exception ex) {  
  66.         Console.WriteLine($ "Export action failed. Error Message: {ex.Message}");  
  67.     }  
  68.     // 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 is need to be uploaded in the web job.

Create Or Update The Excel From Azure Web Job 
 
Create Or Update The Excel From Azure Web Job

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

Step 1

Open the Azure portal website.
 
Create Or Update The Excel From Azure Web Job 

Step 2

Click “App Service” from the left navigation.
 
Create Or Update The Excel From Azure Web Job 

Step 3

On the app service page, click the “Add” button to create a new app service if you don’t have one already.
 
Create Or Update The Excel From Azure Web Job 

Step 4

Then, select the “Web App” from the right-side pane and provide the app name as per your need.
 
Create Or Update The Excel From Azure Web Job 

Step 5

Click on the Create button. It will navigate to the web app creation page form.
 
Create Or Update The Excel From Azure Web Job 

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”.
 
Create Or Update The Excel From Azure Web Job 

Step 7

Then, select your app from the App Services page and click “web jobs” from the left navigation pane.
 
Create Or Update The Excel From Azure Web Job 

Step 8

Click on “Add” from the right pane and 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 on every day 12 PM)
  • CRON Expression à UTC Time (0 45 5 * * *) (IST 12 PM)

CRON Expression Read more.

Create Or Update The Excel From Azure Web Job
 
Create Or Update The Excel From Azure Web Job 

Step 9

The new web job appears on the web page, as shown in the below image.
 
Create Or Update The Excel From Azure Web Job 

Step 10

After creating the web job, select "Always on" from the application settings. Open the app service and select the application settings.
 
Create Or Update The Excel From Azure Web Job 

Step 11

To run the WebJob, right-click its name in the list and click "Run".
 
Create Or Update The Excel From Azure Web Job 

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.
 
Create Or Update The Excel From Azure Web Job 

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 the Excel.