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"},
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