Sitecore Item Export via Excel

Posted 06/02/2014 by Keith Ball

A few months ago a client had a request to have an easily distributable form of translatable documents that matched Sitecore fields. This was at a point when the client did not have the time to explore integration translation services like Clay Tablet or Smartling and wanted it to be an internal process. For this we built a tool, called “Copy Deck Templater,” (CDT) that exports existing content or templates to Excel Spreadsheets. Since then it has gone through a few iterations, one of which is allowing the export to send to any file type. Future updates are planned including SPEAK UI updates and Import, for which new articles will be released.


The tool is broken down into three main parts: UI, Pipelines, and major settings.

Major Settings

The CDT has a few major components, including the Control Source, used to allow a custom control in SHEER.
    <controlSources>
      <!-- Copy Deck Templater -->
      <source mode="on" namespace="CopyDeckTemplater.SheerUI.Controls.Accordion" assembly="CopyDeckTemplater" prefix="copydeckaccordion"/>
    </controlSources>

The Custom Handler, used in allowing created files to be downloaded from the server.

    <customHandlers>
      <handler trigger="~/DownloadCopyDeck" handler="/sitecore modules/CopyDeckTemplater/DownloadCopyDeckTemplate.ashx" />
    </customHandlers>

The file types and download content type, used in the file type creation and download.

<settings>
      <setting name="CopyDeckTemplater.SaveFileExtension" value=".xlsx" />
      <setting name="CopyDeckTemplater.DownloadContentType" value="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" />
    </settings>
And finally the provider, which determines what processor is creating the file which allows for multiple file types.
<CopyDeckExportProvider type="CopyDeckTemplater.Providers.ExcelExportProvider, CopyDeckTemplater" />

User Interface

The UI has two main forms. The first allows users to add templates to a list of what will be exported.  These templates will be blank versions of the items so content can be planned for items without the item actually existing in the Content Tree.

Sitecore Export via Copy Deck Generator
 
The second form allows the user to select a content item with filters to grab related items, children, or items selected in fields. 
Sitecore Item Export via Copy Deck Generator 

Once the content has been selected, the user can then save the Copy Deck and download it from the server.

Pipelines

The majority of the conversion between Sitecore Item/Field to Data File is done through processors in an Import (pending) and Export pipeline. Each processor defines what field types it should process and some simple metadata about that field type.

   <CopyDeckExport>
…
        <processor type="CopyDeckTemplater.Pipelines.Export.Excel.Simple, CopyDeckTemplater" method="Process">
          <fieldTypes hint="raw:AddFieldType">
            <fieldType outputType="text" helpText="Single line of text" name="Single-Line Text" />
            <fieldType outputType="text" helpText="Accepts Linebreaks" name="Multi-Line Text" />
            <fieldType outputType="text" helpText="Accepts HTML" name="Rich Text" />
          </fieldTypes>
        </processor>
…
        <processor type="CopyDeckTemplater.Pipelines.Export.Excel.DropDown, CopyDeckTemplater" method="Process">
          <fieldTypes hint="raw:AddFieldType">
            <fieldType outputType="Selection" helpText="Select a value from the drop down" name="Droplink" />
            <fieldType outputType="Selection" helpText="Select a value from the drop down" name="Droptree" />
          </fieldTypes>
        </processor>
…
</CopyDeckExport>

As an example, the “DropDown” processor above performs advanced Excel datasource work to turn cells into drop down lists using the fields DataSource as selections while the “Simple” processor sets the content to the raw string values.
Ex:

        public override void Process(ExcelExportPipelineArgs args)
		{
			base.Process(args);

			if (ShouldProcess)
			{
				SetDataFromConfig(args);
			}
		}

        private void SetDataFromConfig(ExcelExportPipelineArgs args)
		{
			var rowNum = args.CurrentRow;
			var currentSheet = args.CurrentSheet;

			SetConfigData(args);

			if (args.ContainsContent)
			{
				currentSheet.Cells[rowNum, 5].Value = args.ContentField.Value;
			}
			args.Processed = true;
		}
A link to the source code will be added shortly.

Share:

Archive

Syndication