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

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

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

Create a Local Storage in Azure Web Role

image

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

image

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,

  1. Creating reference of local storage ExcelStorage . In previous step we created this local storage.
  2. DataString is name of the connection string for Azure storage. I assume you know to create connection string for azure storage Smile
  3. Debugmodestreaming is name of the container.
  4. 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"},
                 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 hope this post was useful. Thanks for reading  Smile

 

About these ads

One thought on “Creating and updating EXCEL file in Windows Azure Web Role using Open XML SDK

  1. Pingback: Monthly Report August 2011: Total Posts 17 « debug mode……

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s