Hello, Sitecore enthusiasts out there! 👋😊
In this blog, I will show you a use case of how to fetch all cloud assets into an Excel sheet, specifically from Azure cloud storage. We'll cover setting up all the necessary fields required for creating entities in Sitecore Content Hub, such as title, filename, FinalLifeCycleStatusToAsset, ContentRepositoryToAsset, and file URL, which are mandatory. Additionally, I'll introduce the optional field, AssetTypeToAsset. For my scenario, I've already created an asset type named 'AzureAsset' in Sitecore Content Hub. This allows us to associate all fetched Azure blobs with this asset type, enabling better filtering and easy identification of assets uploaded through the Excel sheet, which contains data fetched from Azure. This method serves as a foundational example of the various use cases and efficiencies Sitecore Content Hub can offer.
To create the Excel sheet, I developed a console application and added the necessary blob storage NuGet packages for my use case.
The process unfolds in three stages:
1. Establishing a connection with the Azure storage account.
2. Fetching all blobs, including those from the hierarchy, and then processing them into a list. For each blob, I set its title, filename, FinalLifeCycleStatusToAsset, ContentRepositoryToAsset, and file URL (which will have the public URL), along with AssetTypeToAsset.
3. Creating a worksheet named 'M.Asset', which is crucial for creating entities in Sitecore Content Hub. This template is utilized by the import functionality of Sitecore Content Hub, specifying the file path of the Excel where I want to save it.
Code Implementation:
using System;
using System.Collections.Generic;
using System.IO;
using System.Threading.Tasks;
using Azure;
using Azure.Storage.Blobs;
using Azure.Storage.Blobs.Models;
using Azure.Storage.Sas;
using ClosedXML.Excel;
using Serilog;
namespace WebApp
{
internal static class ExportAzureBlobs
{
private static readonly List<BlobList> Blobs = new List<BlobList>();
public static async Task GetAzureAsset()
{
string connectionString = "your-connection-string";
string containerName = "your-container-name";
var allBlobItems = new List<BlobList>();
try
{
BlobServiceClient blobServiceClient = new BlobServiceClient(connectionString);
BlobContainerClient containerClient = blobServiceClient.GetBlobContainerClient(containerName);
Console.WriteLine("Successfully connected to Azure Blob Storage.");
// List all blobs in the container
var pages = containerClient.GetBlobs().AsPages(default, pageSizeHint: 100);
foreach (Page<BlobItem> page in pages)
{
foreach (BlobItem item in page.Values)
{
allBlobItems = ProcessBlobs(item, containerClient, containerName);
}
}
await ListAssetsInExcel(allBlobItems);
Log.Information("Completed processing all blobs in container: {ContainerName}", containerName);
Console.WriteLine("Completed processing all blobs in container: {ContainerName}", containerName);
}
catch (Exception ex)
{
Log.Error(ex, "Error occurred while processing Azure blobs.");
}
Console.WriteLine("Completed processing all blobs in container: {0}", containerName);
Console.WriteLine("Press any key to exit...");
Console.ReadKey();
}
public static List<BlobList> ProcessBlobs(BlobItem blobItem, BlobContainerClient containerClient, string containerName)
{
var blobClient = containerClient.GetBlobClient(blobItem.Name);
BlobSasBuilder sasBuilder = new BlobSasBuilder()
{
BlobContainerName = containerName,
BlobName = blobItem.Name,
Resource = "b", // "b" for blob
StartsOn = DateTimeOffset.UtcNow,
ExpiresOn = DateTimeOffset.UtcNow.AddHours(24)
};
sasBuilder.SetPermissions(BlobSasPermissions.Read);
Uri sasUri = blobClient.GenerateSasUri(sasBuilder);
Log.Information("Processing blob: {BlobName} with SAS URI: {SasUri}", blobItem.Name, sasUri);
string filepath = sasUri.ToString();
string filename = Path.GetFileNameWithoutExtension(blobClient.Name);
Blobs.Add(new BlobList
{
Title = filename,
Filename = filename,
FinalLifeCycleStatusToAsset = "M.Final.LifeCycle.Status.Approved",
ContentRepositoryToAsset = "M.Content.Repository.Standard",
File = filepath,
AssetTypeToAsset = "M.AssetType.AzureAsset"
});
return Blobs;
}
public static async Task ListAssetsInExcel(List<BlobList> Blobs)
{
string excelFilePath = @"C:\test\AzureAssets.xlsx";
using (var workbook = new XLWorkbook())
{
var worksheet = workbook.Worksheets.Add("M.Asset");
var currentRow = 1;
worksheet.Cell(currentRow, 1).Value = "Title";
worksheet.Cell(currentRow, 2).Value = "FileName";
worksheet.Cell(currentRow, 3).Value = "FinalLifeCycleStatusToAsset";
worksheet.Cell(currentRow, 4).Value = "ContentRepositoryToAsset";
worksheet.Cell(currentRow, 5).Value = "File";
worksheet.Cell(currentRow, 6).Value = "AssetTypeToAsset";
foreach (var blob in Blobs)
{
currentRow++;
worksheet.Cell(currentRow, 1).Value = blob.Title;
worksheet.Cell(currentRow, 2).Value = blob.Filename;
worksheet.Cell(currentRow, 3).Value = blob.FinalLifeCycleStatusToAsset;
worksheet.Cell(currentRow, 4).Value = blob.ContentRepositoryToAsset;
worksheet.Cell(currentRow, 5).Value = blob.File;
worksheet.Cell(currentRow, 6).Value = blob.AssetTypeToAsset;
}
workbook.SaveAs(excelFilePath);
}
Console.WriteLine($"Excel file saved to {excelFilePath}");
}
}
public class BlobList
{
public string Title { get; set; }
public string Filename { get; set; }
public string FinalLifeCycleStatusToAsset { get; set; }
public string ContentRepositoryToAsset { get; set; }
public string File { get; set; }
public string AssetTypeToAsset { get; set; }
}
}using System.Threading.Tasks;
using System;
using Serilog;
namespace WebApp
{
public class Program
{
static async Task Main(string[] args)
{
try
{
Log.Logger = new LoggerConfiguration()
.MinimumLevel.Debug()
.WriteTo.Console()
.WriteTo.File("logs/AzureImporterLog.txt")
.CreateLogger();
Log.Information("Exporting of Azure blobs data into Excel Sheet has Started");
await ExportAzureBlobs.GetAzureAsset();
Log.Information("Exporting of Azure blobs data into Excel Sheet has ended");
}
catch (Exception ex)
{
Console.WriteLine($"Exception Occurred: Unable to connect! {ex.Message}");
}
}
}
}
Conclusion:
Steps to Upload Your Downloaded Excel Sheet to Sitecore Content Hub:
- Logging into your Sitecore Content Hub account and then navigate to the Create Page.
- Initiate the Import: Click on the "➕Add" button and choose the "📤Upload Excel" option. This will allow you to upload the Excel file you've prepared.
- Upload Your Excel File: Select the Excel file created by following this guide. Then, a job will run in the background to upload all your assets from the M.Asset sheet to Sitecore Content Hub with the defined fields.
