Sitecore WFFM: Customizing Form Reports > Export to Excel – Part 2/2

Posted on Updated on

If you’re interested to check the background of the customization. See part 1 of “Disassembling the ExportFormDataToExcelhere.

The code that invokes the export to CSV file is located in the Sitecore.WFFM.Services.dll assembly.

h.JPG

Open the namespace Sitecore.WFFM.Services.Pipelines.ExportToExcel > ExportFormDataToExcel

This is the untouched version/raw.

using Sitecore.Diagnostics;
using Sitecore.Form.Core.Utility;
using Sitecore.Forms.Core.Data;
using Sitecore.Jobs;
using Sitecore.Security.Accounts;
using Sitecore.WFFM.Analytics.Model;
using Sitecore.WFFM.Core.Resources;
using Sitecore.WFFM.Speak.ViewModel;
using System;
using System.Linq;
using System.Xml;

namespace Sitecore.WFFM.Services.Pipelines.ExportToExcel
{
 public class ExportFormDataToExcel
 {
 public void Process(FormExportArgs args)
 {
 Job job = Context.Job;
 if (job != null)
 {
 job.Status.LogInfo(ResourceManager.Localize("EXPORTING_DATA"));
 }
 string text = args.Parameters["contextUser"];
 Assert.IsNotNullOrEmpty(text, "contextUser");
 using (new UserSwitcher(text, true))
 {
 XmlDocument xmlDocument = new XmlDocument();
 XmlElement xmlElement = xmlDocument.CreateElement("ss:Workbook");
 XmlAttribute xmlAttribute = xmlDocument.CreateAttribute("xmlns");
 xmlAttribute.Value = "urn:schemas-microsoft-com:office:spreadsheet";
 xmlElement.Attributes.Append(xmlAttribute);
 XmlAttribute xmlAttribute2 = xmlDocument.CreateAttribute("xmlns:o");
 xmlAttribute2.Value = "urn:schemas-microsoft-com:office:office";
 xmlElement.Attributes.Append(xmlAttribute2);
 XmlAttribute xmlAttribute3 = xmlDocument.CreateAttribute("xmlns:x");
 xmlAttribute3.Value = "urn:schemas-microsoft-com:office:excel";
 xmlElement.Attributes.Append(xmlAttribute3);
 XmlAttribute xmlAttribute4 = xmlDocument.CreateAttribute("xmlns:ss");
 xmlAttribute4.Value = "urn:schemas-microsoft-com:office:spreadsheet";
 xmlElement.Attributes.Append(xmlAttribute4);
 XmlAttribute xmlAttribute5 = xmlDocument.CreateAttribute("xmlns:html");
 xmlAttribute5.Value = "http://www.w3.org/TR/REC-html40";
 xmlElement.Attributes.Append(xmlAttribute5);
 xmlDocument.AppendChild(xmlElement);
 XmlElement xmlElement2 = xmlDocument.CreateElement("Styles");
 xmlElement.AppendChild(xmlElement2);
 XmlElement xmlElement3 = xmlDocument.CreateElement("Style");
 XmlAttribute xmlAttribute6 = xmlDocument.CreateAttribute("ss", "ID", "xmlns");
 xmlAttribute6.Value = "xBoldVerdana";
 xmlElement3.Attributes.Append(xmlAttribute6);
 xmlElement2.AppendChild(xmlElement3);
 XmlElement xmlElement4 = xmlDocument.CreateElement("Font");
 XmlAttribute xmlAttribute7 = xmlDocument.CreateAttribute("ss", "Bold", "xmlns");
 xmlAttribute7.Value = "1";
 xmlElement4.Attributes.Append(xmlAttribute7);
 XmlAttribute xmlAttribute8 = xmlDocument.CreateAttribute("ss", "FontName", "xmlns");
 xmlAttribute8.Value = "verdana";
 xmlElement4.Attributes.Append(xmlAttribute8);
 xmlElement3.AppendChild(xmlElement4);
 xmlElement3 = xmlDocument.CreateElement("Style");
 xmlAttribute6 = xmlDocument.CreateAttribute("ss", "ID", "xmlns");
 xmlAttribute6.Value = "xVerdana";
 xmlElement3.Attributes.Append(xmlAttribute6);
 xmlElement2.AppendChild(xmlElement3);
 xmlElement4 = xmlDocument.CreateElement("Font");
 xmlAttribute8 = xmlDocument.CreateAttribute("ss", "FontName", "xmlns");
 xmlAttribute8.Value = "verdana";
 xmlElement4.Attributes.Append(xmlAttribute8);
 xmlElement3.AppendChild(xmlElement4);
 XmlElement xmlElement5 = xmlDocument.CreateElement("Worksheet");
 XmlAttribute xmlAttribute9 = xmlDocument.CreateAttribute("ss", "Name", "xmlns");
 xmlAttribute9.Value = "Sheet1";
 xmlElement5.Attributes.Append(xmlAttribute9);
 xmlElement.AppendChild(xmlElement5);
 XmlElement xmlElement6 = xmlDocument.CreateElement("Table");
 XmlAttribute xmlAttribute10 = xmlDocument.CreateAttribute("ss", "DefaultColumnWidth", "xmlns");
 xmlAttribute10.Value = "130";
 xmlElement6.Attributes.Append(xmlAttribute10);
 xmlElement5.AppendChild(xmlElement6);
 this.BuildHeader(xmlDocument, args.Item, xmlElement6);
 this.BuildBody(xmlDocument, args.Item, args.Packet, xmlElement6);
 XmlElement xmlElement7 = xmlDocument.CreateElement("WorksheetOptions");
 XmlElement newChild = xmlDocument.CreateElement("Selected");
 XmlElement xmlElement8 = xmlDocument.CreateElement("Panes");
 XmlElement xmlElement9 = xmlDocument.CreateElement("Pane");
 XmlElement xmlElement10 = xmlDocument.CreateElement("Number");
 xmlElement10.InnerText = "1";
 XmlElement xmlElement11 = xmlDocument.CreateElement("ActiveCol");
 xmlElement11.InnerText = "1";
 xmlElement9.AppendChild(xmlElement11);
 xmlElement9.AppendChild(xmlElement10);
 xmlElement8.AppendChild(xmlElement9);
 xmlElement7.AppendChild(xmlElement8);
 xmlElement7.AppendChild(newChild);
 xmlElement5.AppendChild(xmlElement7);
 args.Result = "<?xml version=\"1.0\"?>" + xmlDocument.InnerXml.Replace("xmlns:ss=\"xmlns\"", "");
 }
 }

private void BuildHeader(XmlDocument doc, FormItem item, XmlElement root)
 {
 XmlElement xmlElement = doc.CreateElement("Row");
 string exportRestriction = FormRegistryUtil.GetExportRestriction(item.ID.ToString(), string.Empty);
 if (exportRestriction.IndexOf("created") == -1)
 {
 XmlElement newChild = this.CreateHeaderCell("String", "Created", doc);
 xmlElement.AppendChild(newChild);
 }
 FieldItem[] fields = item.Fields;
 for (int i = 0; i < fields.Length; i++)
 {
 FieldItem fieldItem = fields[i];
 if (exportRestriction.IndexOf(fieldItem.ID.ToString()) == -1)
 {
 XmlElement newChild2 = this.CreateHeaderCell("String", fieldItem.FieldDisplayName, doc);
 xmlElement.AppendChild(newChild2);
 }
 }
 root.AppendChild(xmlElement);
 }

private XmlElement CreateHeaderCell(string sType, string sValue, XmlDocument doc)
 {
 XmlElement xmlElement = doc.CreateElement("Cell");
 XmlAttribute xmlAttribute = doc.CreateAttribute("ss", "StyleID", "xmlns");
 xmlAttribute.Value = "xBoldVerdana";
 xmlElement.Attributes.Append(xmlAttribute);
 XmlElement xmlElement2 = doc.CreateElement("Data");
 XmlAttribute xmlAttribute2 = doc.CreateAttribute("ss", "Type", "xmlns");
 xmlAttribute2.Value = sType;
 xmlElement2.Attributes.Append(xmlAttribute2);
 xmlElement2.InnerText = sValue;
 xmlElement.AppendChild(xmlElement2);
 return xmlElement;
 }

private void BuildBody(XmlDocument doc, FormItem item, FormPacket packet, XmlElement root)
 {
 foreach (IFormData current in packet.Entries)
 {
 root.AppendChild(this.BuildRow(current, item, doc));
 }
 }

private XmlElement BuildRow(IFormData entry, FormItem item, XmlDocument xd)
 {
 XmlElement xmlElement = xd.CreateElement("Row");
 string exportRestriction = FormRegistryUtil.GetExportRestriction(item.ID.ToString(), string.Empty);
 if (exportRestriction.IndexOf("created") == -1)
 {
 XmlElement newChild = this.CreateCell("String", entry.Timestamp.ToLocalTime().ToString("G"), xd);
 xmlElement.AppendChild(newChild);
 }
 FieldItem[] fields = item.Fields;
 for (int i = 0; i < fields.Length; i++)
 {
 FieldItem field = fields[i];
 if (exportRestriction.IndexOf(field.ID.ToString()) == -1)
 {
 IFieldData fieldData = entry.Fields.FirstOrDefault((IFieldData f) => f.FieldId == field.ID.Guid);
 XmlElement newChild2 = this.CreateCell("String", (fieldData != null) ? fieldData.Value : string.Empty, xd);
 xmlElement.AppendChild(newChild2);
 }
 }
 return xmlElement;
 }

private XmlElement CreateCell(string sType, string sValue, XmlDocument doc)
 {
 XmlElement xmlElement = doc.CreateElement("Cell");
 XmlAttribute xmlAttribute = doc.CreateAttribute("ss", "StyleID", "xmlns");
 xmlAttribute.Value = "xVerdana";
 xmlElement.Attributes.Append(xmlAttribute);
 XmlElement xmlElement2 = doc.CreateElement("Data");
 XmlAttribute xmlAttribute2 = doc.CreateAttribute("ss", "Type", "xmlns");
 xmlAttribute2.Value = sType;
 xmlElement2.Attributes.Append(xmlAttribute2);
 xmlElement2.InnerText = sValue;
 xmlElement.AppendChild(xmlElement2);
 return xmlElement;
 }
 }
}

 

Edited version / Customized. One thing I’ve noticed about the existing code from Sitecore is that you cannot just override a certain method inside of this class. So I created an identical copy of it and added my custom logic.


using Sitecore;
using Sitecore.Diagnostics;
using Sitecore.Form.Core.Utility;
using Sitecore.Forms.Core.Data;
using Sitecore.Jobs;
using Sitecore.Security.Accounts;
using Sitecore.WFFM.Analytics.Model;
using Sitecore.WFFM.Core.Resources;
using Sitecore.WFFM.Services.Pipelines;
using Sitecore.WFFM.Services.Pipelines.ExportToExcel;
using Sitecore.WFFM.Speak.ViewModel;
using System;
using System.Globalization;
using System.Linq;
using System.Xml;

namespace Sitecore.Blacksmith.Pipelines.ExportFormData
{
 public class CustomExportFormDataToExcel
 {
 public void Process(FormExportArgs args)
 {
 Job job = Context.Job;
 if (job != null)
 {
 job.Status.LogInfo(ResourceManager.Localize("EXPORTING_DATA"));
 }
 string text = args.Parameters["contextUser"];
 Assert.IsNotNullOrEmpty(text, "contextUser");
 using (new UserSwitcher(text, true))
 {
 XmlDocument xmlDocument = new XmlDocument();
 XmlElement xmlElement = xmlDocument.CreateElement("ss:Workbook");
 XmlAttribute xmlAttribute = xmlDocument.CreateAttribute("xmlns");
 xmlAttribute.Value = "urn:schemas-microsoft-com:office:spreadsheet";
 xmlElement.Attributes.Append(xmlAttribute);
 XmlAttribute xmlAttribute2 = xmlDocument.CreateAttribute("xmlns:o");
 xmlAttribute2.Value = "urn:schemas-microsoft-com:office:office";
 xmlElement.Attributes.Append(xmlAttribute2);
 XmlAttribute xmlAttribute3 = xmlDocument.CreateAttribute("xmlns:x");
 xmlAttribute3.Value = "urn:schemas-microsoft-com:office:excel";
 xmlElement.Attributes.Append(xmlAttribute3);
 XmlAttribute xmlAttribute4 = xmlDocument.CreateAttribute("xmlns:ss");
 xmlAttribute4.Value = "urn:schemas-microsoft-com:office:spreadsheet";
 xmlElement.Attributes.Append(xmlAttribute4);
 XmlAttribute xmlAttribute5 = xmlDocument.CreateAttribute("xmlns:html");
 xmlAttribute5.Value = "http://www.w3.org/TR/REC-html40";
 xmlElement.Attributes.Append(xmlAttribute5);
 xmlDocument.AppendChild(xmlElement);
 XmlElement xmlElement2 = xmlDocument.CreateElement("Styles");
 xmlElement.AppendChild(xmlElement2);
 XmlElement xmlElement3 = xmlDocument.CreateElement("Style");
 XmlAttribute xmlAttribute6 = xmlDocument.CreateAttribute("ss", "ID", "xmlns");
 xmlAttribute6.Value = "xBoldVerdana";
 xmlElement3.Attributes.Append(xmlAttribute6);
 xmlElement2.AppendChild(xmlElement3);
 XmlElement xmlElement4 = xmlDocument.CreateElement("Font");
 XmlAttribute xmlAttribute7 = xmlDocument.CreateAttribute("ss", "Bold", "xmlns");
 xmlAttribute7.Value = "1";
 xmlElement4.Attributes.Append(xmlAttribute7);
 XmlAttribute xmlAttribute8 = xmlDocument.CreateAttribute("ss", "FontName", "xmlns");
 xmlAttribute8.Value = "verdana";
 xmlElement4.Attributes.Append(xmlAttribute8);
 xmlElement3.AppendChild(xmlElement4);
 xmlElement3 = xmlDocument.CreateElement("Style");
 xmlAttribute6 = xmlDocument.CreateAttribute("ss", "ID", "xmlns");
 xmlAttribute6.Value = "xVerdana";
 xmlElement3.Attributes.Append(xmlAttribute6);
 xmlElement2.AppendChild(xmlElement3);
 xmlElement4 = xmlDocument.CreateElement("Font");
 xmlAttribute8 = xmlDocument.CreateAttribute("ss", "FontName", "xmlns");
 xmlAttribute8.Value = "verdana";
 xmlElement4.Attributes.Append(xmlAttribute8);
 xmlElement3.AppendChild(xmlElement4);
 XmlElement xmlElement5 = xmlDocument.CreateElement("Worksheet");
 XmlAttribute xmlAttribute9 = xmlDocument.CreateAttribute("ss", "Name", "xmlns");
 xmlAttribute9.Value = "Sheet1";
 xmlElement5.Attributes.Append(xmlAttribute9);
 xmlElement.AppendChild(xmlElement5);
 XmlElement xmlElement6 = xmlDocument.CreateElement("Table");
 XmlAttribute xmlAttribute10 = xmlDocument.CreateAttribute("ss", "DefaultColumnWidth", "xmlns");
 xmlAttribute10.Value = "130";
 xmlElement6.Attributes.Append(xmlAttribute10);
 xmlElement5.AppendChild(xmlElement6);
 this.BuildHeader(xmlDocument, args.Item, xmlElement6);
 this.BuildBody(xmlDocument, args.Item, args.Packet, xmlElement6);
 XmlElement xmlElement7 = xmlDocument.CreateElement("WorksheetOptions");
 XmlElement newChild = xmlDocument.CreateElement("Selected");
 XmlElement xmlElement8 = xmlDocument.CreateElement("Panes");
 XmlElement xmlElement9 = xmlDocument.CreateElement("Pane");
 XmlElement xmlElement10 = xmlDocument.CreateElement("Number");
 xmlElement10.InnerText = "1";
 XmlElement xmlElement11 = xmlDocument.CreateElement("ActiveCol");
 xmlElement11.InnerText = "1";
 xmlElement9.AppendChild(xmlElement11);
 xmlElement9.AppendChild(xmlElement10);
 xmlElement8.AppendChild(xmlElement9);
 xmlElement7.AppendChild(xmlElement8);
 xmlElement7.AppendChild(newChild);
 xmlElement5.AppendChild(xmlElement7);
 args.Result = "<?xml version=\"1.0\"?>" + xmlDocument.InnerXml.Replace("xmlns:ss=\"xmlns\"", "");
 }
 }

private void BuildHeader(XmlDocument doc, FormItem item, XmlElement root)
 {
 XmlElement xmlElement = doc.CreateElement("Row");
 string exportRestriction = FormRegistryUtil.GetExportRestriction(item.ID.ToString(), string.Empty);
 if (exportRestriction.IndexOf("created") == -1)
 {
 XmlElement newChild = this.CreateHeaderCell("String", "Created", doc);
 xmlElement.AppendChild(newChild);
 }
 FieldItem[] fields = item.Fields;
 for (int i = 0; i < fields.Length; i++)
 {
 FieldItem fieldItem = fields[i];
 if (exportRestriction.IndexOf(fieldItem.ID.ToString()) == -1)
 {
 XmlElement newChild2 = this.CreateHeaderCell("String", fieldItem.FieldDisplayName, doc);
 xmlElement.AppendChild(newChild2);
 }
 }
 root.AppendChild(xmlElement);
 }

private XmlElement CreateHeaderCell(string sType, string sValue, XmlDocument doc)
 {
 XmlElement xmlElement = doc.CreateElement("Cell");
 XmlAttribute xmlAttribute = doc.CreateAttribute("ss", "StyleID", "xmlns");
 xmlAttribute.Value = "xBoldVerdana";
 xmlElement.Attributes.Append(xmlAttribute);
 XmlElement xmlElement2 = doc.CreateElement("Data");
 XmlAttribute xmlAttribute2 = doc.CreateAttribute("ss", "Type", "xmlns");
 xmlAttribute2.Value = sType;
 xmlElement2.Attributes.Append(xmlAttribute2);
 xmlElement2.InnerText = sValue;
 xmlElement.AppendChild(xmlElement2);
 return xmlElement;
 }

private void BuildBody(XmlDocument doc, FormItem item, FormPacket packet, XmlElement root)
 {
 foreach (IFormData current in packet.Entries)
 {
 root.AppendChild(this.BuildRow(current, item, doc));
 }
 }

private XmlElement BuildRow(IFormData entry, FormItem item, XmlDocument xd)
 {
 string DATE_PICKER_TEMPLATE_ID = "{09BF916E-79FB-4AE3-B799-659E63C75EA5}"; 

XmlElement xmlElement = xd.CreateElement("Row");
 string exportRestriction = FormRegistryUtil.GetExportRestriction(item.ID.ToString(), string.Empty);
 if (exportRestriction.IndexOf("created") == -1)
 {
 XmlElement newChild = this.CreateCell("String", entry.Timestamp.ToLocalTime().ToString("G"), xd);
 xmlElement.AppendChild(newChild);
 }
 FieldItem[] fields = item.Fields;
 for (int i = 0; i < fields.Length; i++)
 {
 FieldItem field = fields[i];
 if (exportRestriction.IndexOf(field.ID.ToString()) == -1)
 {
 IFieldData fieldData = entry.Fields.FirstOrDefault((IFieldData f) => f.FieldId == field.ID.Guid);

string fieldValue = string.Empty;
 string fieldLinkId = string.Empty;
 if (fieldData != null)
 {
 fieldValue = fieldData.Value;

 fieldLinkId = field.Fields["Field Link"].Value;
 if (fieldLinkId.Equals(DATE_PICKER_TEMPLATE_ID))
 {
 //override assignment
 fieldValue = Sitecore.DateUtil.IsoDateToDateTime(fieldData.Value).ToString("dd/MM/yyyy");
 }

//for testing
 Log.Info(string.Format("[MG] Field Name: {0} | Field Value: {1} | Field ID: {2} | Server Time: {3} | Date Time: {4} | Field Link Id: {5}",
 fieldData.FieldName, fieldValue, fieldData.Id, Sitecore.DateUtil.IsoDateToServerTimeIsoDate(fieldData.Value), Sitecore.DateUtil.IsoDateToDateTime(fieldData.Value), fieldLinkId), this);
 }

XmlElement newChild2 = this.CreateCell("String", fieldValue, xd);

xmlElement.AppendChild(newChild2);
 }
 }
 return xmlElement;
 }

private XmlElement CreateCell(string sType, string sValue, XmlDocument doc)
 {
 XmlElement xmlElement = doc.CreateElement("Cell");
 XmlAttribute xmlAttribute = doc.CreateAttribute("ss", "StyleID", "xmlns");
 xmlAttribute.Value = "xVerdana";
 xmlElement.Attributes.Append(xmlAttribute);
 XmlElement xmlElement2 = doc.CreateElement("Data");
 XmlAttribute xmlAttribute2 = doc.CreateAttribute("ss", "Type", "xmlns");
 xmlAttribute2.Value = sType;
 xmlElement2.Attributes.Append(xmlAttribute2);
 xmlElement2.InnerText = sValue;
 xmlElement.AppendChild(xmlElement2);
 return xmlElement;
 }
 }
}

And a config patch..

<sitecore>
<?xml version="1.0"?>
<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/">
<pipelines>
<!-- EXPORT TO EXCEL -->
<exportToExcel>
<processor patch:instead="processor[@type='Sitecore.WFFM.Services.Pipelines.ExportToExcel.ExportFormDataToExcel, Sitecore.WFFM.Services']" type="Sitecore.Blacksmith.Pipelines.ExportFormData.CustomExportFormDataToExcel, Sitecore.Blacksmith" />
</exportToExcel>
</pipelines>
</sitecore>
</configuration>

Test the result:

exportToExcel customized date of birth result.JPG

 

Enjoy! 😉

Advertisements

2 thoughts on “Sitecore WFFM: Customizing Form Reports > Export to Excel – Part 2/2

    […] See part 2 “Disassembling the ExportFormDataToExcel” here. […]

    Like

    Marvin Glenn Lacuna responded:
    July 16, 2016 at 2:20 pm

    The custom implementation could be altered on your desired fashion. Like for example, moving the DATE_PICKER_TEMPLATE_ID to configuration, changing the date format, or hook another condition for CHECKBOX/RADIO BUTTON and other field types, and that’s all up to you.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s