Month: July 2016

Sitecore Installation Wizard: ItemNameValidation must satisfy the pattern error

Posted on

Background: We’re near for another production deployment, so the new features from the sprints must be deploy to the development (SIT) and UAT environment for testing before it gets to production env to ensure the quality of work.

Our SIT & UAT environments were quite outdated in terms of content. So in order to sync , I have these possible options:

  1. Get the database from Production environment, restore to lower environments
  2. Unicorn
  3. Package <- I choose this way, as this is the fastest route on my situation.

 

During the installation of the package, I encountered this error.

An item name must satisfy the pattern: ^[\w\*\$][\w\s\-\$]*(\(\d{1,}\)){0,1}$ (controlled by the setting ItemNameValidation)

So what I did is to disable the settings for the validation in App_Config\Sitecore.config

 <!-- <setting name="ItemNameValidation" value="^[\w\*\$][\w\s\-\$]*(\(\d{1,}\)){0,1}$" /> --> 

However, the issue still persist. So I tried my brute force to remove the value of the ItemNameValidation.

<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/">
<sitecore>
<settings>
<setting name="ItemNameValidation">
<patch:attribute name="value"></patch:attribute>
</setting>
</settings>
</sitecore>
</configuration> 

Usage: Create a config file and deploy to App_Config\Include folder. Remove this setting after you finished installing all the packages.

Then the issue gone away.

 

 

[Fixed] Index_Update_IndexName=genericsearch_master_index|#Exception: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation

Posted on Updated on

Issue:

 Job started: Index_Update_IndexName=genericsearch_master_index|#Exception: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---&gt; SolrNet.Exceptions.SolrConnectionException: &lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?&gt;
&lt;response&gt;
&lt;lst name=&quot;responseHeader&quot;&gt;&lt;int name=&quot;status&quot;&gt;400&lt;/int&gt;&lt;int name=&quot;QTime&quot;&gt;0&lt;/int&gt;&lt;/lst&gt;&lt;lst name=&quot;error&quot;&gt;&lt;lst name=&quot;metadata&quot;&gt;&lt;str name=&quot;error-class&quot;&gt;org.apache.solr.common.SolrException&lt;/str&gt;&lt;str name=&quot;root-error-class&quot;&gt;org.apache.solr.common.SolrException&lt;/str&gt;&lt;/lst&gt;&lt;str name=&quot;msg&quot;&gt;ERROR: [doc=sitecore://master/{5ff1f382-cae2-4c69-907a-06410e3afb9f}?lang=en&amp;amp;ver=1&amp;amp;ndx=genericsearch_master_index] unknown field 'searchfacet_computed'&lt;/str&gt;&lt;int name=&quot;code&quot;&gt;400&lt;/int&gt;&lt;/lst&gt;
&lt;/response&gt;
---&gt; System.Net.WebException: The remote server returned an error: (400) Bad Request.
at System.Net.HttpWebRequest.GetResponse()
at HttpWebAdapters.Adapters.HttpWebRequestAdapter.GetResponse()
at SolrNet.Impl.SolrConnection.GetResponse(IHttpWebRequest request)
at SolrNet.Impl.SolrConnection.PostStream(String relativeUrl, String contentType, Stream content, IEnumerable`1 parameters)
--- End of inner exception stack trace ---
at SolrNet.Impl.SolrConnection.PostStream(String relativeUrl, String contentType, Stream content, IEnumerable`1 parameters)
at SolrNet.Impl.SolrConnection.Post(String relativeUrl, String s)
at SolrNet.Impl.SolrBasicServer`1.SendAndParseHeader(ISolrCommand cmd)
at Sitecore.ContentSearch.SolrProvider.SolrBatchUpdateContext.AddRange(IEnumerable`1 group, Int32 groupSize)
at Sitecore.ContentSearch.SolrProvider.SolrBatchUpdateContext.Commit()
at Sitecore.ContentSearch.SolrProvider.SolrSearchIndex.PerformRebuild(Boolean resetIndex, Boolean optimizeOnComplete, IndexingOptions indexingOptions, CancellationToken cancellationToken)
at Sitecore.ContentSearch.SolrProvider.SolrSearchIndex.Rebuild(Boolean resetIndex, Boolean optimizeOnComplete)
--- End of inner exception stack trace ---
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at Sitecore.Reflection.ReflectionUtil.InvokeMethod(MethodInfo method, Object[] parameters, Object obj)
at Sitecore.Jobs.JobRunner.RunMethod(JobArgs args)
at (Object , Object[] )
at Sitecore.Pipelines.CorePipeline.Run(PipelineArgs args)
at Sitecore.Jobs.Job.ThreadEntry(Object state)

Solution:

Head to SOLR admin page – http://127.0.0.1:8983/solr, and refresh index that’s throwing an error exception; so in my case genericsearch_master_index.

reload_solr_index

Test again, it should work now.

rebuilding_index_using_indexing_manager

 

Update 14.08.2016 : If the above steps failed. Double check the schema, and see if it’s the latest version and/or has the right settings on it.

[Fixed] Job started: RebuildSearchIndex|System.IO.IOException: Cannot overwrite.

Posted on

Issue:

Job started: RebuildSearchIndex|System.IO.IOException: Cannot overwrite: D:\Instances\8.0-U3\Data\indexes\__system\_655.fdt
at Lucene.Net.Store.FSDirectory.InitOutput(String name)
at Lucene.Net.Store.SimpleFSDirectory.CreateOutput(String name)
at Lucene.Net.Index.FieldsWriter..ctor(Directory d, String segment, FieldInfos fn)
at Lucene.Net.Index.StoredFieldsWriter.InitFieldsWriter()
at Lucene.Net.Index.StoredFieldsWriter.FinishDocument(PerDoc perDoc)
at Lucene.Net.Index.DocumentsWriter.WaitQueue.WriteDocument(DocWriter doc)
at Lucene.Net.Index.DocumentsWriter.WaitQueue.Add(DocWriter doc)
at Lucene.Net.Index.DocumentsWriter.FinishDocument(DocumentsWriterThreadState perThread, DocWriter docWriter)
at Lucene.Net.Index.DocumentsWriter.UpdateDocument(Document doc, Analyzer analyzer, Term delTerm)
at Lucene.Net.Index.IndexWriter.AddDocument(Document doc, Analyzer analyzer)
at Sitecore.Search.IndexUpdateContext.AddDocument(Document document)
at Sitecore.Search.Crawlers.DatabaseCrawler.IndexVersion(Item item, Item latestVersion, IndexUpdateContext context)
at Sitecore.Search.Crawlers.DatabaseCrawler.AddItem(Item item, IndexUpdateContext context)
at Sitecore.Search.Crawlers.DatabaseCrawler.AddTree(Item root, IndexUpdateContext context)
at Sitecore.Search.Crawlers.DatabaseCrawler.AddTree(Item root, IndexUpdateContext context)
at Sitecore.Search.Crawlers.DatabaseCrawler.AddTree(Item root, IndexUpdateContext context)
at Sitecore.Search.Index.Rebuild()
at Sitecore.Shell.Applications.Search.RebuildSearchIndex.RebuildSearchIndexForm.Builder.Build()|Job ended: RebuildSearchIndex (units processed: )

Solution:

Unchecked the Read-only option under the Data folder

until_the_readonly

Test again. hooray!

rebuilding_search_indexes

 

 

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” />

Two (2) mandatory basic steps to tune up your Sitecore CM site, to keep it always up and running.

Posted on Updated on

I learnt these practices in a very hard way. When we had a client that was having the unexpected shutdown, and a slow start up load time issue on their production environment, a month ago.

Steps:

1. Adjust the app pool recycle from every 1740 minutes to off-peak hours of your Geo location daily. (eg. 3:00 AM, 4:000, 5:00 AM)

Open the IIS > Application Pools > Actions pane > Recycle…

app pool recycle settings

2. Adjust the settings for the Idle Time-out to zero (0). This is to make your website load fast.

Open the IIS > Aplication Pools > Actions pane > Advanced settings

Idle time-out

Note: Leaving the Idle Time-out to 20 minutes by default is only best applicable for bulk hosting where you want to lessen the memory usage. When you have 20 minutes without any traffic then the app pool will terminate, and will just start up again on the next visit.

The main problem is that the first visit to an app pool needs to create a new w3wp.exe worker process which is slow because the app pool needs to be created. ASP.Net or another framework needs to be loaded, and then your application needs to be loaded, as well.

Ref:

http://weblogs.asp.net/owscott/why-is-the-iis-default-app-pool-recycle-set-to-1740-minutes

http://stackoverflow.com/questions/198580/what-are-optimal-settings-for-recycling-of-application-pools-in-iis7-in-shared-e

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! 😉