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;
}