Creating and Updating EXCEL file in Windows Azure Web Role using Open XML SDK

Dhananjay Kumar
May 19, 2012
20.6k
0
0
Article

Problem Statement

You need to create and upload EXCEL file in Windows Azure Web Role. Since there is no MS Office present on AZURE VM so you cannot use Office InterOP Dll. So you are left with option of Open XML SDK to create and update Excel file.

Solution Approach

Create and update Excel file using Open XML SDK
Upload Excel Template in Azure BLOB
Download Excel template in azure web role local storage
Read and update excel file from azure web role local storage
Upload updated excel in Azure BLOB.

Create a Local Storage in Azure Web Role

local storage in windows azure

I have created local storage called ExcelStorage. We will download Template Excel file in this local memory to update the records.

Uploading template in BLOB

I have created an Excel file called TestBLOB.xlsx as below template and uploaded in a container called debugmodestreaming

Excel file in windows azure

There are two columns in the excel file. I am going to update these two columns. You can have any number of columns. I have uploaded this excel file manually using Storage Explorer tool.

Include below Namespaces to work with BLOB, local storage and Open XML SDK,

using System;
using System.Collections.Generic;
using System.Linq;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using Microsoft.WindowsAzure;
using Microsoft.WindowsAzure.StorageClient;
using Microsoft.WindowsAzure.ServiceRuntime;


Download template from BLOB and save on web role local storage

             LocalResource myConfigStorage = RoleEnvironment.GetLocalResource("ExcelStorage");          
             account = CloudStorageAccount.Parse
                     (RoleEnvironment.GetConfigurationSettingValue("DataString"));
             blobClient = account.CreateCloudBlobClient();
             container = blobClient.GetContainerReference("debugmodestreaming");
             blob = container.GetBlobReference("TestBLOB.xlsx");          
             blob.DownloadToFile(myConfigStorage.RootPath +"dj.xlsx");

In above code,

Creating reference of local storage ExcelStorage . In previous step we created this local storage.
DataString is name of the connection string for Azure storage. I assume you know to create connection string for azure storage
Debugmodestreaming is name of the container.
Reading excel template file TestBLOB.xlsx and saving it to file called dj.xlsx on web role local storage.

Creating Data to be saved on Excel file

List<Student> GetData()
        {
            List<Student> lstStudents = new List<Student> {
                 new Student { Name = "Dj ", RollNumber = "1"},
                 new Student { Name ="Dhananjay Kumar", RollNumber="2"},
                 new Student { Name = "Dj ", RollNumber = "1"},
                 new Student { Name = "Dj ", RollNumber = "1"},
                 new Student { Name = "Dj ", RollNumber = "1"},
                 new Student { Name = "Dj ", RollNumber = "1"},
                 new Student { Name = "Dj ", RollNumber = "1"},
                 new Student { Name = "Dj ", RollNumber = "1"},
                 new Student { Name = "Dj ", RollNumber = "1"},
                 new Student { Name = "Dj ", RollNumber = "1"},
                 new Student { Name = "Dj ", RollNumber = "1"},
                 new Student { Name = "Dj ", RollNumber = "1"},
                 new Student { Name = "Dj ", RollNumber = "1"},
                 new Student { Name = "Dj ", RollNumber = "1"},
                 new Student { Name = "Dj ", RollNumber = "1"},
                 new Student { Name = "Dj ", RollNumber = "1"}

            };
            return lstStudents;
        }
    }
    class Student
    {
        public string Name { get; set; }
        public string RollNumber { get; set; }
    }

I have created a class called Student and some dummy data to be saved in the Excel file.

Writing to Excel file using Open XML SDK

Note: Writing to EXCEL file code I binged and got it from somewhere. I want to thank to the real author of this code. I am sorry that could not locate his/her blog url and name. But this code is from that author. Thanks

var result = GetData();
  int index = 2;
  using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(myConfigStorage.RootPath + "dj.xlsx", true))
{
WorkbookPart workbookPart = myWorkbook.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
  foreach (var a in result)
{
  string territoryName = a.Name;
  string salesLastYear = a.RollNumber;
Row contentRow = CreateContentRow(index, territoryName, salesLastYear);
index++;
sheetData.AppendChild(contentRow);
}
workbookPart.Workbook.Save();
}

Only one point to be noted is, we are opening file to write from local storage. Dj.xlsx is the file we saved on local storage from BLOB.

Uploading updated Excel file back to BLOB

            blob.UploadFile(myConfigStorage.RootPath + "dj.xlsx");
            blob.Properties.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            blob.SetProperties();
            BlobRequestOptions options = new BlobRequestOptions();
            options.AccessCondition = AccessCondition.None;

Again we are reading updated excel sheet from local storage and uploading it to BLOB. Make sure content type is set properly.

This is all we need to do to work with Excel file in Windows Azure.

For your reference full source code at one place is as below,

using System;
using System.Collections.Generic;
using System.Linq;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using Microsoft.WindowsAzure;
using Microsoft.WindowsAzure.StorageClient;
using Microsoft.WindowsAzure.ServiceRuntime;

namespace WebRole1
{
public partial class _Default : System.Web.UI.Page
{

        private static CloudStorageAccount account;
        private static CloudBlobClient blobClient;
        private static CloudBlobContainer container;
        private static CloudBlob blob;
        protected void Button1_Click1(object sender, EventArgs e)
        {
            LocalResource myConfigStorage = RoleEnvironment.GetLocalResource("ExcelStorage");
            account = CloudStorageAccount.Parse
                    (RoleEnvironment.GetConfigurationSettingValue("DataString"));
            blobClient = account.CreateCloudBlobClient();
            container = blobClient.GetContainerReference("debugmodestreaming");
            blob = container.GetBlobReference("TestBLOB.xlsx");
            blob.DownloadToFile(myConfigStorage.RootPath + "dj.xlsx");
            var result = GetData();
            int index = 2;
            using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(myConfigStorage.RootPath + "dj.xlsx", true))
            {
                WorkbookPart workbookPart = myWorkbook.WorkbookPart;
                WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
                SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
                foreach (var a in result)

                {
                    string territoryName = a.Name;
                    string salesLastYear = a.RollNumber;
                    Row contentRow = CreateContentRow(index, territoryName, salesLastYear);
                    index++;
                    sheetData.AppendChild(contentRow);
                }
                workbookPart.Workbook.Save();
            }
            blob.UploadFile(myConfigStorage.RootPath + "dj.xlsx");
            blob.Properties.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            blob.SetProperties();
            BlobRequestOptions options = new BlobRequestOptions();
            options.AccessCondition = AccessCondition.None;
        }
        string[] headerColumns = new string[] { "A", "B" };
        Row CreateContentRow(int index, string territory, string salesLastYear)
        {
            Row r = new Row();
            r.RowIndex = (UInt32)index;
            Cell firstCell = CreateTextCell(headerColumns[0], territory, index);
            r.AppendChild(firstCell);
            Cell c = new Cell();
            c.CellReference = headerColumns[1] + index;
            CellValue v = new CellValue();
            v.Text = salesLastYear.ToString();
            c.AppendChild(v);
            r.AppendChild(c);
            return r;
        }
        Cell CreateTextCell(string header, string text, int index)
        {
            Cell c = new Cell();
            c.DataType = CellValues.InlineString;
            c.CellReference = header + index;
            InlineString inlineString = new InlineString();
            Text t = new Text();
            t.Text = text;
            inlineString.AppendChild(t);
            c.AppendChild(inlineString);
            return c;
        }
        List<Student> GetData()
       {
            List<Student> lstStudents = new List<Student> {
                 new Student { Name = "Dj ", RollNumber = "1"},
                 new Student { Name ="Dhananjay Kumar", RollNumber="2"},
                 new Student { Name = "Dj ", RollNumber = "1"},
                 new Student { Name = "Dj ", RollNumber = "1"},

new Student { Name = "Dj ", RollNumber = "1"},

                 new Student { Name = "Dj ", RollNumber = "1"},
                 new Student { Name = "Dj ", RollNumber = "1"},
                 new Student { Name = "Dj ", RollNumber = "1"},

                 new Student { Name = "Dj ", RollNumber = "1"},
                 new Student { Name = "Dj ", RollNumber = "1"},
                 new Student { Name = "Dj ", RollNumber = "1"},
                 new Student { Name = "Dj ", RollNumber = "1"},
                 new Student { Name = "Dj ", RollNumber = "1"}
            };