WFFM

Sitecore WFFM – 401 Unauthorized

Posted on

Issue:

On the form reports, cannot view the forms reports.

Solution:

In IIS, make sure the Authentication of the site for ‘Anonymous Authentication’ is set correct.

  1. Click ‘Edit..’ and see the identity.
  2. If IUSR, make sure that the website root has a permission of the IUSR user.

Advertisements

[Fixed] RebuildSearchIndex|System.Threading.LockRecursionException: Recursive read lock acquisitions not allowed in this mode.

Posted on Updated on

While rebuilding the Search Indexes.

Issue:

Job started: RebuildSearchIndex|System.Threading.LockRecursionException: Recursive read lock acquisitions not allowed in this mode.
at System.Threading.ReaderWriterLockSlim.TryEnterReadLockCore(TimeoutTracker timeout)
at System.Threading.ReaderWriterLockSlim.TryEnterReadLock(TimeoutTracker timeout)
at System.Threading.ReaderWriterLockSlim.TryEnterReadLock(Int32 millisecondsTimeout)
at Sitecore.Search.IndexUpdateContext..ctor(ILuceneIndex index)
at Sitecore.Search.Index.Rebuild()
at Sitecore.Shell.Applications.Search.RebuildSearchIndex.RebuildSearchIndexForm.Builder.Build()|Job ended: RebuildSearchIndex (units processed: )

Screenshot:

Rebuild_Search_Indexes

Solution:

In the Sitecore.Forms.config, there’s a settings:


<!-- CONNECTION STRING
Sets the name of the connection string
-->
<setting name="WFM.ConnectionString" value="reporting" />

Make sure that it is equivalent in the connectionstring.config WFFM connectionstring.


<add name="reporting" connectionString="Data Source=11.2.3.456,1433;Database=sitecore_analytics;Integrated Security=true" /> 

Working rebuild index.JPG

[Custom Fixed] Sitecore WFFM: CheckboxListField.cshtml renders same NAME and ID attribute value on its checkboxes

Posted on Updated on

While we’re doing a regression testing for one of my project, we’ve noticed that the CheckboxListField field renders its checkboxes with the same value for the NAME and ID attributes on its checkboxes for the input HTML tags. Thus, creating a complication on the customization we’ve implemented on the styling of the form.

This is because we just upgraded from Sitecore v.8.1-U2, and started to experience the issue. So,  I did test the issue in a clean copy of v.8.1-U2 and I was able to reproduce the issue.

Describing the concern:

In SC v.8.0 the checkboxes value are incremented correctly.
eg.

Favorite food checkboxlist

In Input tag <input>

  • [ ] Pasta checkbox (id=”wffm6dac667f32104007a914e4628ce2a46d_Sections_0__Fields_0__Value” name=”wffm6dac667f32104007a914e4628ce2a46d.Sections[0].Fields[0].Value”)
  • [ ] Pizza checkbox (id=”wffm6dac667f32104007a914e4628ce2a46d_Sections_0__Fields_1__Value” name=”wffm6dac667f32104007a914e4628ce2a46d.Sections[0].Fields[1].Value”)
  • [ ] Chicken checkbox (id=”wffm6dac667f32104007a914e4628ce2a46d_Sections_0__Fields_2__Value” name=”wffm6dac667f32104007a914e4628ce2a46d.Sections[0].Fields[2].Value”)
BUT in v.8.1-U2

Favorite food checkboxlist

In Input tag <input>

  • [ ] Pasta checkbox (id=”wffm6dac667f32104007a914e4628ce2a46d_Sections_0__Fields_0__Value” name=”wffm6dac667f32104007a914e4628ce2a46d.Sections[0].Fields[0].Value”)
  • [ ] Pizza checkbox (id=”wffm6dac667f32104007a914e4628ce2a46d_Sections_0__Fields_0__Value” name=”wffm6dac667f32104007a914e4628ce2a46d.Sections[0].Fields[0].Value”)
  • [ ] Chicken checkbox (id=”wffm6dac667f32104007a914e4628ce2a46d_Sections_0__Fields_0__Value” name=”wffm6dac667f32104007a914e4628ce2a46d.Sections[0].Fields[0].Value”)

Note: I already raised this concern to Sitecore.

But since we love to play around with the implementation of Sitecore. I managed to find and amend the CheckboxListField.cshtml – \Website\Views\Form\EditorTemplates. 

This is just a workaround while Sitecore haven’t confirmed anything about the issue yet.

In CheckboxListField.cshtml

@using Sitecore.Forms.Mvc.ViewModels.Fields
@using Sitecore.WFFM.Abstractions.Data.Enums
@using Sitecore.Forms.Mvc.Html
@model CheckboxListField

@{
int z = 0;
}

@using (Html.BeginField())
{
var notInline = Model.FormType != FormType.Inline;
<div class="checkbox-wrap">
@foreach (var item in Model.Items)
{
using (Html.BeginTag("div", () => notInline, new { @class = "checkbox" }))
{
var htmlIdForVal = Html.IdFor(x => Model.Value);
var cId = htmlIdForVal.ToString().Replace("Fields_0__Value", "Fields_" + z + "__Value");

var htmlNameForVal = Html.NameFor(x => Model.Value);

using (Html.BeginTag("label", new { @class = notInline ? string.Empty : "checkbox-inline" }))
{

<input type="checkbox" value="@item.Value" id= "@cId" name ="@htmlNameForVal" @(item.Selected ? "checked" : string.Empty) />
@Html.DisplayFor(m => item.Text)
}
}
z++;
}
</div>
}

[Edited 08.04.2016] Important note: Do not update the name attribute, as it would create a complication on the email message when you included the checkbox list field. The problem is that only the first checkbox item (pasta) would show on the email even the 2nd (pizza), 3rd (chicken) checkboxes were checked.

Original value:


@using Sitecore.Forms.Mvc.ViewModels.Fields
@using Sitecore.WFFM.Abstractions.Data.Enums
@using Sitecore.Forms.Mvc.Html
@model CheckboxListField

@using (Html.BeginField())
{
var notInline = Model.FormType != FormType.Inline;
foreach (var item in Model.Items)
{
using (Html.BeginTag("div", () => notInline, new { @class = "checkbox" }))
{
using (Html.BeginTag("label", new { @class = notInline ? string.Empty : "checkbox-inline" }))
{
<input type="checkbox" value="@item.Value" id="@Html.IdFor(x => Model.Value)" name="@Html.NameFor(x => Model.Value)" @(item.Selected? "checked" : string.Empty)/>
@Html.DisplayFor(m => item.Text)
}
}
}
}

Sitecore WFFM : Send Email Message not sending

Posted on Updated on

Today, while I’m doing a regression testing in our development environment, I noticed that one of the forms is not sending an email.

Sitecore version: 8.0 Update-3

I did a quick check on the the Save Action > Send  Email Message.

Sitecore WFFM - Send Email Message not sending.JPG

Root cause:  Remove the extra [] in the ‘To‘ field, to make it work.

From:

[[Email Address]]

To:

[Email Address]

 

[Fixed] Sitecore WFFM : Creates a duplicate field section upon submission when exceeded to 10 sections.

Posted on Updated on

As explained in Stackoverflow > Sitecore WFFM : Creates a duplicate field section upon submission.

I eventually raised the issue to Sitecore, and after quite while they provided a Sitecore bug fix for my issue. I’ve tested the patch and it’s working now.

Thank you for filling up the form

Moreover, the following patch should to be implemented:

  1. Put the attached library into the bin folder. Download here.  (Applicable only for v.8.0)
  2. In the App_Config/Include/Sitecore.Forms.MVC.config file, replace the following line:

From:

<processor patch:before=”processor[@type=’Sitecore.Mvc.Pipelines.Loader.InitializeGlobalFilters, Sitecore.Mvc’]”
type=”Sitecore.Forms.Mvc.Pipelines.AddCustomMetadataProvider, Sitecore.Forms.Mvc” />

To:

<processor patch:before=”processor[@type=’Sitecore.Mvc.Pipelines.Loader.InitializeGlobalFilters, Sitecore.Mvc’]”
type=”Sitecore.Support.Forms.Mvc.Pipelines.AddCustomMetadataProvider, Sitecore.Support.434449” />

 

Disassembling the Sitecore.Support.434449.dll to understand the root cause a little further

#1. Override the CreateModel of SectionModelBinder


namespace Sitecore.Support.Forms.Mvc.Controllers.ModelBinders
{
public class SectionModelBinder : Sitecore.Forms.Mvc.Controllers.ModelBinders.SectionModelBinder
{
protected override object CreateModel(ControllerContext controllerContext, ModelBindingContext bindingContext, Type modelType)
{
IHasModelFactory hasModelFactory = controllerContext.Controller as IHasModelFactory;
FormModel formModel = (hasModelFactory != null) ? hasModelFactory.ModelFactory.GetModel() : null;
if (formModel == null)
{
return base.CreateModel(controllerContext, bindingContext, modelType);
}
int index = int.Parse(bindingContext.ModelName.Substring(bindingContext.ModelName.IndexOf('[') + 1, bindingContext.ModelName.IndexOf(']') - bindingContext.ModelName.IndexOf('[') - 1));
return formModel.Sections[index];
}
}
} 

#2. Modify the  AddCustomMetadataProvider class > virtual void process

namespace Sitecore.Support.Forms.Mvc.Pipelines
{
internal class AddCustomMetadataProvider
{
[UsedImplicitly]
public virtual void Process(PipelineArgs args)
{
ModelMetadataProviders.Current = new ModelTypeMetadataProvider();
ModelBinders.Binders.Add(typeof(SectionModel), new Sitecore.Support.Forms.Mvc.Controllers.ModelBinders.SectionModelBinder());
ModelBinders.Binders.Add(typeof(FieldModel), new FieldModelBinder());
}
}
}

 

Edited: 08.03.2016

Sitecore.Support.434449.dll can be dowloaded here.

 

Edited: 08.04.2016

In Sitecore version 8.1

The following patch should to be implemented:

  1. Put the attached library into the bin folder. Download here. (Applicable only for v.8.1)
  2. In the App_Config/Include/Sitecore.Forms.MVC.config file, replace the following line:

From:

<processor patch:before=”processor[@type=’Sitecore.Mvc.Pipelines.Loader.InitializeGlobalFilters, Sitecore.Mvc’]”
type=”Sitecore.Forms.Mvc.Pipelines.AddCustomMetadataProvider, Sitecore.Forms.Mvc” />

To:

<processor patch:before=”processor[@type=’Sitecore.Mvc.Pipelines.Loader.InitializeGlobalFilters, Sitecore.Mvc’]”
type=”Sitecore.Support.Forms.Mvc.Pipelines.AddCustomMetadataProvider, Sitecore.Support.434449.81” />

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 = "&amp;amp;amp;amp;amp;amp;amp;lt;?xml version=\"1.0\"?&amp;amp;amp;amp;amp;amp;amp;gt;" + 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 &amp;amp;amp;amp;amp;amp;amp;lt; 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 &amp;amp;amp;amp;amp;amp;amp;lt; fields.Length; i++)
 {
 FieldItem field = fields[i];
 if (exportRestriction.IndexOf(field.ID.ToString()) == -1)
 {
 IFieldData fieldData = entry.Fields.FirstOrDefault((IFieldData f) =&amp;amp;amp;amp;amp;amp;amp;gt; 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 = "&amp;amp;amp;amp;amp;amp;amp;lt;?xml version=\"1.0\"?&amp;amp;amp;amp;amp;amp;amp;gt;" + 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 &amp;amp;amp;amp;amp;amp;amp;lt; 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 &amp;amp;amp;amp;amp;amp;amp;lt; fields.Length; i++)
 {
 FieldItem field = fields[i];
 if (exportRestriction.IndexOf(field.ID.ToString()) == -1)
 {
 IFieldData fieldData = entry.Fields.FirstOrDefault((IFieldData f) =&amp;amp;amp;amp;amp;amp;amp;gt; 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! 😉

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

Posted on Updated on

Recently, I had a simple task.

Test the “Form Reports” and see if it is working properly, do also check the data in the excel & xml files if correct after exported.

Fast forward. I exported it to CSV then I noticed something irrelevant. The ‘Date of Birth’ field uses an ISO date format (what a surprised!?).

form reports exported in CSV.JPG

I did a thorough checking in the WFFM configurations and its settings for the export, but it seems that there was no option to change it to a different date format.

So quickly, I jumped to SQL to understand how it is being inserted.

Using the select query, I was able to had a quick look on the values inserted in the FormFieldValues table/

SELECT * FROM [dbo].[FormFieldValues]

FormFieldValues table result.JPG

Knowing that when using the form designer, content authors have the ability to add/remove fields whenever he/she wanted to.

Important note: Sitecore stores the data in a vertical fashion. In other words, the FieldValue field uses NVARCHAR to accommodate any type of value regardless if this is a date (regardless of any format), a checkbox (true/false) and the rest.

And now we know the problem, how do we resolve this?

First and the easiest route is to query the word ‘ExportToExcel’ in Core database.

query of exportoexcel worl.JPG

Then I navigated to the item itself.

Item Id: E8047E7B-13EF-420F-8A89-5FC01BABEBF4

Item Path: /sitecore/client/Applications/WFFM/Resources/Parameters/ActionControl Parameters/ActionColumn/ActionGroup/ExportToExcel

Scrolling down to the bottom part, in “Action” section > click field, I noticed this value:

javascript:app.exportToExcel()

I search again for the ‘exportToExcel’ keyword, but this time in the files system. I hit the correct .js and .config files.

Physical path: \8.0-U3\Website\sitecore\shell\client\Applications\WFFM\Pages\FormReport\FormReport.js

exporttoExcel method- FormReport js

DLL: Sitecore.WFM.Services.dll

Method: Sitecore.WFFM.Services.Pipelines.ExportToExcel.ExportFormDataToExcel,

exportToExcel config settings.JPG

Raw value:

<!--

Purpose: This include file configures Web Forms for Marketers. The file is mandatory for Web Forms for Marketers to function correctly.

-->
<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/" xmlns:x="http://www.sitecore.net/xmlconfig/">
<sitecore>
<!-- PIPELINES -->
<pipelines>
<!-- EXPORT TO XML -->
<exportToXml>
<processor type="Sitecore.WFFM.Services.Pipelines.ExportToXml.ExportFormDataToXml, Sitecore.WFFM.Services" />
<processor type="Sitecore.WFFM.Services.Pipelines.SaveContent, Sitecore.WFFM.Services" />
</exportToXml>
<!-- EXPORT TO EXCEL -->
<exportToExcel>
<processor type="Sitecore.WFFM.Services.Pipelines.ExportToExcel.ExportFormDataToExcel, Sitecore.WFFM.Services" />
<processor type="Sitecore.WFFM.Services.Pipelines.SaveContent, Sitecore.WFFM.Services" />
</exportToExcel>
<exportToAscx>
<processor type="Sitecore.WFFM.Services.Pipelines.SaveContent, Sitecore.WFFM.Services" />
</exportToAscx>
</pipelines>
<!-- Commands -->
<commands>
<command name="forms:export:completed" type="Sitecore.WFFM.Services.Pipelines.ExportCompleted,Sitecore.WFFM.Services" />
</commands>
<!-- CONTROLLERS -->
<controllers>
<controller type="Sitecore.WFFM.Services.Requests.Controllers.FormReportsController,Sitecore.WFFM.Services">
<param name="formsDataProvider" ref="wffm/formsDataProvider"/>
</controller>
<controller type="Sitecore.WFFM.Services.Requests.Controllers.ExportFormDataController,Sitecore.WFFM.Services">
<param name="formsDataProvider" ref="wffm/formsDataProvider"/>
</controller>
</controllers>
</sitecore>
</configuration>

 

disasemble all the things.jpg

 

See part 2 “Disassembling the ExportFormDataToExcelhere.