In one of our project, we had requirement to download custom column in csv through XForm. XForm itself has built-in feature to download csv file but it downloads all columns and with existing built-in column such as PostedDate.
Client wanted to download only selected columns even though we were recording lots of information in XForm.
I have built small Dojo Control which enables you to filter data by date using built-in dojo date control and includes columns only that are pre-defined during development. We can extend control to allow to provide additional columns on the fly.
How it is look likes
How to define property
[BackingType(typeof(PropertyXFormCustomColumnDownloadButton))]
[EditorDescriptor(EditorDescriptorType = typeof(XFormCustomColumnDownloadButtonEditorDescriptor))]
[XFormReferencedProperty("Form", "RewardNappyPromoData", "FirstName", "LastName", "AddressLine1", "AddressLine2", "Suburb", "City", "Option")]
[Display(Order = 66)]
public virtual XFormCustomColumnExport XFormDownload { get; set; }
PropertyXFormCustomColumnDownloadButton
This is backing type class to enable the storage of value. We are not saving any value for this as this is just control to download existing records. But we can extend this to store list of columns that need to be exported.
[PropertyDefinitionTypePlugIn(Description = "A property to show button to custom download xfrom column", DisplayName = "Download XForm Data")]
public class PropertyXFormCustomColumnDownloadButton : PropertyString
{
public override Type PropertyValueType
{
get { return typeof(XFormCustomColumnExport); }
}
public override object SaveData(PropertyDataCollection properties)
{
return String;
}
public override object Value
{
get { return new XFormCustomColumnExport(); }
set { base.Value = value; }
}
public override IPropertyControl CreatePropertyControl()
{
//No support for legacy edit mode
return null;
}
}
XFormCustomColumnDownloadButtonEditorDescriptor
This is editor descriptor to let EPiServer know what Js file to load and how to show the control. And set any predefined values that need to be passed to JS file so we can pass back those value from js to server during export.
/// <summary>
/// Register an editor for XForm custom column download
/// </summary>
[EditorDescriptorRegistration(TargetType = typeof(XFormCustomColumnExport))]
public class XFormCustomColumnDownloadButtonEditorDescriptor : EditorDescriptor
{
public const string Form_ID = "FormId";
public const string Included_Columns = "IncludedColumns";
public const string XForm_Export_Url = "XFormCustomColumnExportUrl";
public const string Export_FileName = "ExportFileName";
public XFormCustomColumnDownloadButtonEditorDescriptor()
{
ClientEditingClass = "template.editors.XFormCustomColumnDownload";
}
protected override void SetEditorConfiguration(ExtendedMetadata metadata)
{
var xFormReferencedPropertyAtt =
metadata.Attributes.FirstOrDefault(f => f.GetType() == typeof(XFormReferencedPropertyAttribute)) as
XFormReferencedPropertyAttribute;
if (xFormReferencedPropertyAtt != null)
{
/* Load xform property to get xform id */
var propertyData = metadata.Model as PropertyData;
if (propertyData != null)
{
/* Load the Referenced XForm property to fetch the id */
var xForm = propertyData.Parent.GetPropertyValue<XForm>(xFormReferencedPropertyAtt.XFormReferencedPropertyName);
if (xForm != null)
{
EditorConfiguration[Form_ID] = xForm.Id.ToString();
}
if (xFormReferencedPropertyAtt.IncludedColumns != null &&
xFormReferencedPropertyAtt.IncludedColumns.Any())
{
EditorConfiguration[Included_Columns] = xFormReferencedPropertyAtt.IncludedColumns;
}
EditorConfiguration[Export_FileName] = xFormReferencedPropertyAtt.ExportFileName;
}
}
EditorConfiguration[XForm_Export_Url] = ConfigurationManager.AppSettings[XForm_Export_Url];
base.SetEditorConfiguration(metadata);
}
}
XFormReferencedPropertyAttribute
This is additional attribute to pass the file name to be exported, Referenced XForm property name from which values will be extracted and columns that need to extracted.
[AttributeUsage(AttributeTargets.Property)]
public class XFormReferencedPropertyAttribute : Attribute
{
public string ExportFileName;
public string XFormReferencedPropertyName { get; set; }
public IList<string> IncludedColumns { get; set; }
public XFormReferencedPropertyAttribute(string xFormReferncedPropertyName, string exportFileName = null, params string[] includedColumns)
{
ExportFileName = exportFileName;
XFormReferencedPropertyName = xFormReferncedPropertyName;
IncludedColumns = includedColumns;
}
}
XFormCustomColumnExport
This is just place holder property type so EPiServer does not mess up any existing type. I tried to use String but some time it will mess up any existing property with type String.
/// <summary>
/// This class is used for Dojo XFormCustomColumnDownload Control
/// The reason for using seperate class it to make sure dojo control does not mess up other existing types
/// </summary>
public class XFormCustomColumnExport
{
}
Adding Js File and Updating module.config
We need to created Js File inside ClientResources -> Scripts -> Editorsand need to update module.config to let EPiServer know form where to fetch the file.
JS File for Custom XForm Download With Date Controls
template.editors.XFormCustomColumnDownload
The structure of Dojo Control name is as follows template.editors.XFormCustomColumnDownload
The template in name is from module.config, the editors is the folder inside the ClientResources->Scripts folder and XFormCustomColumnDownload is the name of js file.
/*
Dojo widget for downloading custom column of xfrom.
*/
define([
"dojo/_base/declare",
"dojo/io/iframe",
"dijit/_CssStateMixin",
"dijit/_Widget",
"dijit/_TemplatedMixin",
"dijit/_WidgetsInTemplateMixin",
"dijit/form/DateTextBox",
"epi/shell/widget/_ValueRequiredMixin"
],
function (
declare,
iframe, /* iframe is required to post download csv file request, Ajax request is not able to download file*/
_CssStateMixin,
_Widget,
_TemplatedMixin,
_WidgetsInTemplateMixin,
DateTextBox,
_ValueRequiredMixin
) {
return declare("template.editors.XFormCustomColumnDownload",
[_Widget, _TemplatedMixin, _WidgetsInTemplateMixin, _CssStateMixin, _ValueRequiredMixin],
{
templateString:
"<div data-dojo-attach-point=\"containerNode\" class=\"dijit dijitReset dijitInline dijitLeft\">\
<form id=\"frmXFormExtract\" Method=\"POST\" />\
<input type=\"text\" data-dojo-id=\"startDate\" data-dojo-attach-event=\"onChange:onStartDateChange\" data-dojo-type=\"dijit/form/DateTextBox\" />\
<input type=\"text\" data-dojo-id=\"endDate\" data-dojo-attach-event=\"onChange:onEndDateChange\" data-dojo-type=\"dijit/form/DateTextBox\" />\
<button type=\"button\" data-dojo-attach-event=\"onclick:onExportButtonClick\" class=\"\">Export</button>\
</div> ",
formId: null,
exportFileName: null,
xFormCustomColumnExportUrl: null,
includedColumns: null,
baseClass: "XFormCustomColumnDownload",
helptext: "This will download xform values as csv file",
_deferred: null,
intermediateChanges: false,
onExportButtonClick: function (e) {
// Cancelling is requried for dojo iframe otherwise dojo iframe will never send any request after 1st request
// http://stackoverflow.com/questions/13854908/dojo-io-iframe-send-does-not-send-a-request-on-second-time-onwards-in-dojo-1-8
if (this._deferred) {
this._deferred.cancel();
}
// Dojo iframe sends merged array every time user clicks the export button
// https://dojotoolkit.org/reference-guide/1.10/dojo/io/iframe.html
// Iframe use was required to allow download of file otherwise ajax wont download file
this._deferred = dojo.io.iframe.send({
url: this.xFormCustomColumnExportUrl,
form: "frmXFormExtract",
content: {
formId: this.formId,
exportFileName: this.exportFileName,
StartDate: startDate.toLocaleString(),
EndDate: endDate.toLocaleString(),
IncludedColumns: this.includedColumns
}
});
},
onStartDateChange: function (e) {
startDate.setValue(e);
},
onEndDateChange: function (e) {
endDate.setValue(e);
},
destroy: function () {
},
postMixInProperties: function() {
if (this.params.xFormCustomColumnExportUrl) {
this.xFormCustomColumnExportUrl = this.params.xFormCustomColumnExportUrl;
}
if (this.params.formId) {
this.formId = this.params.formId;
}
if (this.params.exportFileName) {
this.exportFileName = this.params.exportFileName;
}
if (this.params.includedColumns) {
this.includedColumns = this.params.includedColumns;
}
},
postCreate: function () {
// call base implementation
this.inherited(arguments);
}
});
});
module.config
<?xml version="1.0" encoding="utf-8"?>
<module>
<assemblies>
<!-- This adds the current site into the modules" -->
<add assembly="EPiServer.Reference.Commerce.Site" />
</assemblies>
<dojoModules>
<add name="template" path="Scripts" />
</dojoModules>
<clientResources>
</clientResources>
<dojo>
</dojo></module>
ExportDataController
I have Got ExportDataController under XFormCustomColumnDataExport Area. I am passing relative url to Js File through XFormCustomColumnDownloadButtonEditorDescriptor.
/// <summary>
/// This function is called by dojo control. Url is also provided in app setting
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public FileResult Export(ExportDataModel model)
{
if(model == null || model.FormId == Guid.Empty)
throw new NullReferenceException("Form Id is required");
var xForm = XForm.CreateInstance(new Guid(model.FormId.ToString()));
if(xForm == null)
throw new ContentNotFoundException($"xForm with formId:{model.FormId} could not found");
/* Fetch all the data within date period */
var postedData = xForm.GetPostedData(model.StartDate ?? (DateTime) SqlDateTime.MinValue,
model.EndDate ?? (DateTime)SqlDateTime.MaxValue);
Func<List<string>, string> csvSafeRow = (rowValues) =>
{
return rowValues.Aggregate(string.Empty, (a, b) => $"{a},{b}", s => s.Trim(','));
};
/* Adding header */
StringBuilder exportedData = new StringBuilder(csvSafeRow(model.UniqueIncludedColumns.Select(s => s.AsSafeCsvString()).ToList()));
exportedData.AppendLine();
/* Fetch the required column data and append to csv string builder*/
foreach (var record in postedData)
{
var newRow = new List<string>();
foreach (var header in model.UniqueIncludedColumns)
newRow.Add(record.GetValue(header).AsSafeCsvString());
exportedData.Append(csvSafeRow(newRow));
exportedData.AppendLine();
}
string fileName = string.IsNullOrWhiteSpace(model.ExportFileName) ? "XForm" : model.ExportFileName;
return File(Encoding.ASCII.GetBytes(exportedData.ToString()), "text/csv", $"{fileName}.csv");
}
Helper function
/// <summary>
/// Removes the New Line(\n,\r) from value and wraps string with Quotes to make it safe for csv file
/// </summary>
/// <param name="value"></param>
/// <returns></returns>
public static string AsSafeCsvString(this string value)
{
if (string.IsNullOrWhiteSpace(value))
return string.Empty;
return $"\"{value}\"".Replace("\r\n", " ").Replace("\n", " ").Replace("\r", " ");
}
