Content Item Report with Sitecore API

Working with items

I have interacted with Sitecore customers where some of their needs on Sitecore reports have been similar: to have a tabular report tool. Whether it’s for checking content item inventory, a clean-up analysis, or identifying when it was last updated and by whom. There’s already several ways to create a report like this, with external Sitecore modules such as Sitecore Powershell Extensions. But for the curious developer, a good Sitecore API practice comes in handy and can help us exercise our minds on how to solve this need with a simple utility ASPX page, where a Sitecore power user can get access to it and use it in a simplistic way.

Exercise requirements

1. Data needs to come from the database, not from the Sitecore index

2. The utility page must ask:

From what database it should read the data
What is the Sitecore root item path, from where it should start looking Sitecore items
The path of the item’s template it inherits from

3. Export the data in CSV format

4. No code behind, for easy push/deployment to any environment without impacting its operation

Solution

<%@ Page language="C#" EnableEventValidation="false" AutoEventWireup="true" EnableViewState="true" %>
<%@ Import Namespace="System.Collections.Generic" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Linq" %>
<%@ Import Namespace="Sitecore" %>
<%@ Import Namespace="Sitecore.Data" %>
<%@ Import Namespace="Sitecore.Data.Items" %>
<%@ Import Namespace="Sitecore.Data.Fields" %>

<!DOCTYPE html>

<script runat="server">

protected void btnExport_Click(object sender, EventArgs e)
{
    try
    {
        var database = Sitecore.Data.Database.GetDatabase(databaseName.Text);
        var rootItem = database.GetItem(sitecorePathText.Text);
        if (rootItem == null)
        {
            results.Text = "Root item not found";
            return;
        }

        var items = rootItem.Axes.GetDescendants();
             Sitecore.Data.Templates.Template templateItem = Sitecore.Data.Managers.TemplateManager.GetTemplate(templateName.Text, database);
        if (templateItem == null)
        {
            results.Text = "Filter template not found";
            return;
        }
        var filteredItems = items.Where(item => item.DescendsFrom(templateItem.ID));
        var fields = templateItem.GetFields(true).ToList();
          
        var exportedText = new System.Text.StringBuilder();               

        //exporting headers
        exportedText.Append("Item full path");
        foreach (Sitecore.Data.Templates.TemplateField field in fields)
        {
            exportedText.Append("," + field.Name);
        }
        exportedText.AppendLine();

        foreach (Item filteredItem in filteredItems)
        {
            exportedText.Append(filteredItem.Paths.FullPath);
            foreach (Sitecore.Data.Templates.TemplateField field in fields)
            {
                exportedText.Append("," + PreFormatCSVValue(filteredItem.Fields[field.ID].Value));
            }
            exportedText.AppendLine();
        }
             results.Text = string.Empty;
        SaveToCsvFile(exportedText.ToString(), templateName.Text + " items report");
    }
    catch (Exception ex)
    {
        results.Text = ex.Message + Environment.NewLine + ex.StackTrace;
    }
}

private string PreFormatCSVValue(string value)
{
       if (!string.IsNullOrEmpty(value) && value.IndexOf(",") >= 0)
       {
             return "\"" + value + "\"";
       }
       return value;
}

private void SaveToCsvFile(string contents, string fileName)
{
    Response.Clear();
    Response.ContentType = "text/csv";
    Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName + ".csv");
    Response.Write(contents);
    Response.End();
}

</script>

<html lang="en">
<head>
  <meta charset="utf-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>Content Item Report</title>
  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet"
    integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">
  <style>
    main { padding: 1rem; }
    form { max-width: 1024px; }
  </style>
</head>
<body>
  <main>
    <div>
      <h3>Content Item Report</h3><br />
      <form runat="server" id="exportForm">
        <div class="mb-3">
          <label for="databaseName" class="form-label"><b>Database name:</b> <span class="small">(can be core, master or web!)</span></label>
          <asp:TextBox CssClass="form-control" ID="databaseName" runat="server" Columns="120" TextMode="SingleLine" Text="master" />
        </div>
        <div class="mb-3">
          <label for="sitecorePathText" class="form-label"><b>Sitecore root path:</b> <span class="small">(you can use Sitecore IDs with curly brackets ({}) too!)</span></label>
          <asp:TextBox CssClass="form-control" ID="sitecorePathText" runat="server" Columns="120" TextMode="SingleLine" Text="" />
        </div>
        <div class="mb-3">
          <label for="templateName" class="form-label"><b>Template FULL name filter:</b> <span class="small">(Template path, without "/sitecore/templates/")</span></label>
          <asp:TextBox CssClass="form-control" ID="templateName" runat="server" Columns="120" TextMode="SingleLine" Text="System/Templates/Standard template" />
        </div><br />
        <asp:Button ID="btnExport" CssClass="btn btn-primary" Text="Export to CSV" runat="server" OnClick="btnExport_Click" />
      </form><br />
      <div class="mb-3">
        <asp:Literal ID="results" runat="server" Text=""></asp:Literal>
      </div>
    </div>
  </main>
</body>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.min.js"
  integrity="sha384-cVKIPhGWiC2Al4u+LWgxfKTRIcfu0JTxR+EQDz/bgldoEyl4H0zUF0QKbrJ0EcQF" crossorigin="anonymous">
</script>
</html>

We set up all this code in an ASPX page, and we drop it into /Sitecore/admin folder on our Sitecore instance and test it:

SitecoreAPI1 SitecoreAPI2

This is just the tip of the iceberg in terms of report functionality, as there are many areas of improvement! But we’ll get into that in another post.

Happy coding!