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

Charts in Windows Phone 7

Today morning I got a call from SQL Server Guru  Pinal Dave and conversation was as below.

image

 

image

 

image

 

So, I started working on this. I decided to go ahead with hard coded data as below,

 


public class Bloggers
    {
        public string Name { get; set; }
        public double Posts { get; set; }
    }


And function returning data is,

 


public static List<Bloggers> GetMembers()
        {
            List<Bloggers> lstMembers = new List<Bloggers>
                                        {
                                            new Bloggers
                                            {
                                                 Name ="Pinal",
                                                 Posts = 2000

                                            },
                                            new Bloggers
                                            {

                                                 Name ="Debugmode",
                                                 Posts = 400
                                            },

                                             new Bloggers
                                            {

                                                 Name ="Koiarala",
                                                 Posts = 1000


                                            },
                                              new Bloggers
                                            {

                                                 Name ="Mahesh",
                                                 Posts = 1500


                                            },


                                        };
            return lstMembers;
        }



image

 


<Grid x:Name="LayoutRoot">
        <controls:Panorama Title="Bloggers">

            <!--Panorama item one-->
            <controls:PanoramaItem Header="Series Chart">
                <Grid>
                    <charting:Chart x:Name="seriesChart" Background="Black">
                        <charting:ColumnSeries Background="Black" />
                    </charting:Chart>
                </Grid>
            </controls:PanoramaItem>

            <!--Panorama item two-->
            <controls:PanoramaItem Header="Pie Chart">
                <Grid>
                    <charting:Chart x:Name="pieChart"></charting:Chart>
                </Grid>
            </controls:PanoramaItem>
            <!--Panorama item three-->
            <controls:PanoramaItem Header="Scatter Chart">
                <Grid>
                    <charting:Chart x:Name="scatterChart"></charting:Chart>
                </Grid>
            </controls:PanoramaItem>
        </controls:Panorama>
    </Grid>




 

To add chart control on XAML, I added namespace on XAML as below ,

 


 xmlns:charting="clr-namespace:System.Windows.Controls.DataVisualization.Charting;
assembly=System.Windows.Controls.DataVisualization.Toolkit"


And on code behind as below,

 


using System.Windows;
using System.Windows.Controls;
using Microsoft.Phone.Controls;
using System.Windows.Controls.DataVisualization.Charting;
using System.Windows.Data;


Creating Column Series


ColumnSeries series = new ColumnSeries();
            seriesChart.Series.Add(series);
            series.SetBinding(ColumnSeries.ItemsSourceProperty, new Binding());
            series.ItemsSource =  GetMembers();
            series.DependentValuePath = "Posts";
            series.IndependentValuePath = "Name";



There is nothing to get confused in above code. It is creating a Column Series and adding to the chart in the first panorama item. Expected output would be as below ,

image

 

Creating Pie Series



PieSeries pieSeries = new PieSeries();
            pieChart.Series.Add(pieSeries);
            pieSeries.SetBinding(PieSeries.ItemsSourceProperty, new Binding());
            pieSeries.ItemsSource = Model.Factory.GetMembers();
            pieSeries.DependentValuePath = "Posts";
            pieSeries.IndependentValuePath = "Name";



Expected output is as below,

image

 

Creating Scatter Series


ScatterSeries scatterSeries = new ScatterSeries();
scatterChart.Series.Add(scatterSeries);
scatterSeries.SetBinding(ScatterSeries.ItemsSourceProperty, new Binding());
scatterSeries.ItemsSource = Model.Factory.GetMembers();
scatterSeries.DependentValuePath = "Posts";
scatterSeries.IndependentValuePath = "Name";


Expected Output is as below,

image

 

image

 

Fetching Web Roles details using Windows Azure Management API

If you are writing some tool to manage Windows Azure portal then fetching information about Roles may be frequent requirement for you.

In this post, we will discuss the way to Fetch below information about a Web Role or Worker Role using Windows Azure Management API.

  1. RoleName
  2. InstanceName
  3. InstanceStatus

Any client making call to Azure portal using Management API has to authenticate itself before making call. Authenticating being done between Azure portal and client calling REST based Azure Management API through the certificates.

  1. Read here to create certificate for Azure subscription
  2. Read here to upload certificate

Very first let us create class representing Roles


public class RoleInstance
    {

        public string RollName { get; set; }

        public string InstanceName { get; set; }

        public string InstanceStatus { get; set; }
    }

Essentially you need to perform four steps,

1. You need to create a web request to your subscription id.

image

2.  While making request you need to make sure you are calling the correct version and adding the cross ponding certificate of your subscription.

image

3. Get the stream and convert response stream in string

image

You will get the XML response in below format,

image

In returned XML all the Roles and their information’s are returned as below,

image

4. Last step is to parse using LINQ to XML to fetch details of Role

image

For your reference full source code is as below,


using Microsoft.WindowsAzure;
using Microsoft.WindowsAzure.StorageClient;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Net;
using System.Security.Cryptography.X509Certificates;
using System.Xml.Linq;
namespace ConsoleApplication1
{

 class Program
    {
        static void Main(string[] args)

var request = (HttpWebRequest)WebRequest.Create("https://management.core.windows.net/ursubscriptionid
/services/hostedservices/yourhostedservicename?embed-detail=true");
            request.Headers.Add("x-ms-version:2009-10-01");
            CloudStorageAccount cloudStorageAccount = CloudStorageAccount.Parse("DefaultEndpointsProtocol=https;
                                                                            AccountName=debugmodetest9;AccountKey=dfkj");
            CloudBlobClient cloudBlobClient = cloudStorageAccount.CreateCloudBlobClient();
            CloudBlobContainer cloudBlobContainer = cloudBlobClient.GetContainerReference("debugmodestreaming");
            CloudBlob cloudBlob = cloudBlobContainer.GetBlobReference("debugmode.cer");

            byte[] byteData = cloudBlob.DownloadByteArray();
            X509Certificate2 certificate = new X509Certificate2(byteData);
            request.ClientCertificates.Add(certificate);
            var response = request.GetResponse().GetResponseStream();
            var xmlofResponse = new StreamReader(response).ReadToEnd();
            //XDocument doc = XDocument.Parse(xmlofResponse);
            XElement el = XElement.Parse(xmlofResponse);
            XNamespace ns = "http://schemas.microsoft.com/windowsazure";
            var servicesName = from r in el.Descendants(ns + "RoleInstance")
                               select new RoleInstance
                               {
                                   RollName  = r.Element(ns + "RoleName").Value,
                                   InstanceName = r.Element(ns + "InstanceName").Value,
                                   InstanceStatus = r.Element(ns + "InstanceStatus").Value,

                               };

 foreach (var r in servicesName )
            {
                Console.WriteLine(r.InstanceName + r.InstanceStatus);
            }
 Console.ReadKey(true);

}
 }

I hope this post was useful. Thanks for reading  Smile