Gathering the result set from Cosmos DB and writing that data into an excel file using the Azure Function App

This blog will show you how to gather the result set from cosmos DB and write that data into an excel file using the azure function app.

Gathering the result set from Cosmos DB :

First, we must install a package named Microsoft.Azure.Cosmos(3.23.0) to use cosmos DB components in the code. The result set will be in a list of JSON objects. So, we have to install the Newtonsoft.json package also.

These are the codes used to gather the result set from cosmos DB.

public class GetDataCosmosDB{
public CosmosClient cosmosClient;
public Microsoft.Azure.Cosmos.Database database;
public Container container;
public class Root{
public List<Dictionary<string, object>> Data { get; set; }}
public async Task<List<person>> QueryItemsAsync(
string EndpointUri,
string PrimaryKey,
string databaseId,
string containerId,
string sdate,
string edate ,ILogger log)
{
try{
//Cosmos DB Credentials
this.cosmosClient = new CosmosClient(EndpointUri,PrimaryKey);
person po = new person();
var date1 = DateTime.Today;
var starttime = sdate+date1.ToString((" HH:mm:ss"));
var date2 = date1.Date.AddHours(23).AddMinutes(59).AddSeconds(59);
var endtime = edate+date2.ToString((" HH:mm:ss"));
var sqlQueryText1 = "SELECT c.id,c.hostName,c.systemUser,c.ipAddress,c.createdDate FROM c WHERE c.createdDate between'" + starttime + "' and '" + endtime + "'";
//Cosmos Db Container credetials
var query = this.cosmosClient.GetContainer(databaseId, containerId);
log.LogInformation("Query is excuting in COSMOS DB");
//resultset gather from Cosmos Db
var resultSet = query.GetItemQueryIterator<person>(new QueryDefinition(sqlQueryText1));
List<person> results = new List<person>();
while (resultSet.HasMoreResults){
try{
var response = await resultSet.ReadNextAsync();
results.AddRange(response.ToList());//resultset to list
}
catch (Exception ex) { log.LogError(ex.Message); }
}
log.LogInformation("Data is ready to Load into Excel");
return results;
}
catch (CosmosException cosmos_DB_ex{
log.LogError(cosmos_DB_ex.Message);
return null;
}}}
public class person
{
[JsonProperty(PropertyName = "id")]
public string id { get; set; }
[JsonProperty(PropertyName = "hostName")]
public string hostName { get; set; }
[JsonProperty(PropertyName = "systemUser")]
public string systemUser { get; set; }
[JsonProperty(PropertyName = "ipAddress")]
public string ipAddress { get; set; }
[JsonProperty(PropertyName = "createdDate")]}

After gathering the result set from Cosmos DB, the result set will be in a list of JSON objects.

List<person> peopleData = await dataCosmosDB.QueryItemsAsync(payloadRequest.endpoint,
payloadRequest.primarykey,
payloadRequest.databaseid,payloadRequest.containerid);

We have to create an excel file to write data gathered from cosmos DB.

Create an Excel file and add a header :

Step 1: Open Project Add COM reference and search Microsoft Excel 16.0 Object Library.

Step 2: Select Microsoft Excel 16.0 Object Library and click OK.

Step 3: Add Microsoft.Office.Interop.Excel namespace in the program to use excel based function.

using Microsoft.Office.Interop.Excel;

Step 4: Create a file directory using the CreateDirectory method.

string fileDirectory = "C:\\demo";
Directory.CreateDirectory(fileDirectory);

Step 5: To create an excel file, we have to create an excel application object, excel workbook object, and excel worksheet object and add a name to the worksheet.

Application application = new Application();
Workbook workbook = application.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Worksheet worksheet1 = workbook.Worksheets[1];
worksheet1.Name = "Report against System user";

Step 6: Add headers in the created worksheet.

string[] report2Header = { "S.no", "System User", "IP Address Used" };
for (i = 0, column = 1; i < report2Header.Length; i++, column++)
{worksheet1.Cells[1, column] = report2Header[i];
worksheet1.Cells[1, column].Font.Bold = true;
worksheet1.Cells.HorizontalAlignment = XlHAlign.xlHAlignLeft;
worksheet1.Cells.VerticalAlignment = XlVAlign.xlVAlignCenter;}

Step 7: Save the excel file using excel workbook object.

string filepath = filedirectory + "\\demo.xlsx";
workbook.Save(filepath);

Step 8: Close the excel workbook, excel application objects and releasing COM objects to ignore performance issues.

workbook.Close();
application.Quit();
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(application);

By following the above steps, an excel file with a single worksheet and headers will be created.

After creating the excel file, we have to write data into the excel file by following steps.

Writing result set data into the excel sheet:

Step 1: Declare a string array to store values, the size of the array must be the number of properties present in a single JSON object.

string values = new string[5];

Step 2: we have to create an excel application object, excel workbook object, and excel worksheet object to open the excel file we have created before with the header.

Application application = new Application();
Workbook workbook = application.Workbooks.Open(filepath);
Worksheet worksheet = workbook.Worksheets[1];

Step 3: Using for each loop, we have to assign values of corresponding properties in JSON object list what we have got as a result set from CosmosDB and write those values one by one into the excel sheet.

foreach (var obj in json){
values[0] = obj.id;
values[1] = obj.hostName;
values[2] = obj.systemUser;
values[3] = obj.ipAddress;
values[4] = obj.createdDate;
int i, row, column;
row = (worksheet.UsedRange.Rows.Count) + 1;
for (i = -1, column = 1; i < values.Length; i++, column++){
if (column == 1){worksheet.Cells[row, column] = row — 1;}
else{worksheet.Cells[row, column] = values[i];}}}
worksheet.Columns.AutoFit();
worksheet.Rows.AutoFit();

Step 4: After writing the data into the excel file, we have to save and close the excel file and application and release the COM objects.

workbook.Save();
workbook.Close();
application.Quit();
Marshal.ReleaseComObject(workbook);
Marshal.ReleaseComObject(application);

Conclusion:

Finally, we have gathered the result set and wrote and saved those data into the excel file using the function app. I hope this article will be helpful to you.

About Us

Bi3 has been recognized for being one of the fastest-growing companies in Australia. Our team has delivered substantial and complex projects for some of the largest organizations around the globe and we’re quickly building a brand that is well known for superior delivery.

Website: https://bi3technologies.com/

Follow us on,
LinkedIn: https://www.linkedin.com/company/bi3technologies
Instagram: https://www.instagram.com/bi3technologies/
Twitter: https://twitter.com/Bi3Technologies