Sitecore: Export to CSV/Excel of Web Form For Marketers Form (WFFM) Reports


Once one of our client request us to create a form on their website to store user data and also they ask us to create another page to download form data in CSV/Excel format from front end. The first part of this request (creating form on website) was very easy (Thanks to Sitecore’s Web Forms for Marketers), but WFFM don’t provide out of the box functionality of export data from front end page.

So I started digging in to Sitecore.Forms.Core.dll — one of the assemblies that ships with Web Forms for Marketers and decided to check out how the export functionality of the Form Reports work.

To export Web form for Marketers (WFFM) form data into CSV/Excel file from front end,this is what I did:

First I created a C# class & wrote following code,this class will create XML of complete form report for a given form.

public class CustomExportToExcel
 {
     public string Process(FormItem form, FormPacket Packet)
     {
         Job job = Context.Job;
         if (job != null)
         {
             job.Status.LogInfo(ResourceManager.Localize("EXPORTING_DATA"));
         }
         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, form, xmlElement6);
         this.BuildBody(xmlDocument, form, 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);

        Assert.ArgumentNotNullOrEmpty("testing", "file name");
        if (job != null)
        {
            job.Status.LogInfo(ResourceManager.Localize("DUMPING_DATA"));
        }
        ExportArgs objARgs = new ExportArgs(form, Packet, "export.xls", "application/vnd.ms-excel");
        objARgs.Result = "<?xml version=\"1.0\"?>" + xmlDocument.InnerXml.Replace("xmlns:ss=\"xmlns\"", "");
        return objARgs.Result;
     }
     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 (IForm current in packet.Entries)
        {
            root.AppendChild(this.BuildRow(current, item, doc));
        }
     }
     private XmlElement BuildRow(IForm 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;
         FieldItem field;
         for (int i = 0; i < fields.Length; i++)
         {
             field = fields[i];
             if (exportRestriction.IndexOf(field.ID.ToString()) == -1)
             {
                 IField field2 = entry.Field.FirstOrDefault((IField f) => f.FieldId == field.ID.Guid);
                 XmlElement newChild2 = this.CreateCell("String", (field2 != null) ? field2.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;
      }
 }

After that I created another class to write logic of download excel/csv:


public static class ExportToExcelCommon
{
    public static void ExportToExcelFile(string formID, string filename)
    {
        ItemUri uri = new ItemUri(Sitecore.Data.ID.Parse(formID), Sitecore.Context.Database);
        //1. Get Form
        FormItem form = new FormItem(Database.GetItem(uri));
        string name = form.FormName;

        //2. Data Filters
        List<GridFilter> filters = new List<GridFilter>();
        // 2.a Form filter
        //filters.Add(new GridFilter(Sitecore.Form.Core.Configuration.Constants.DataKey, formID,          GridFilter.FilterOperator.Contains));
       //// 2.b Get archived items
       //filters.Add(new GridFilter(Sitecore.Form.Core.Configuration.Constants.StorageName,     Sitecore.Form.Core.Configuration.Constants.Archive, GridFilter.FilterOperator.Contains));

       filters.Add(new GridFilter("storageName", string.Empty, GridFilter.FilterOperator.Contains));
       filters.Add(new GridFilter("dataKey", formID, GridFilter.FilterOperator.Contains));

       //3. Get all entries
       IEnumerable<IForm> entries = Sitecore.Forms.Data.DataManager.GetForms().GetPage(new PageCriteria(0, 0x7ffffffe), null, filters);

      // 3.a Apply custom filtering on the entries
      //entries = entries.Where(a => a.Timestamp.Date.CompareTo(startDate) >= 0 && a.Timestamp.Date.CompareTo(endDate) <= 0);

      //4. Create a form packet
      FormPacket packet = new FormPacket(entries);

      CustomExportToExcel export = new CustomExportToExcel();
      string result = export.Process(form, packet);
      HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=" + filename + ".xls");
      HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
      HttpContext.Current.Response.Write(result);
      HttpContext.Current.Response.End();
    }
}

And at last created a web form(.aspx) page on website root with name formreports.aspx ,so user can download the form report from this URL.


<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <style type="text/css">
        .ddl-forms {
            padding: 5px 0;
            font-size: 16px;
        }

        .txt-output {
            font-size: 16px;
            padding: 3px 5px;
        }

        .btn-submit {
            font-size: 16px;
            width:88%;
            height:40px;
            
        }
        .left{
            padding-left:90px;
        }
        .clear {
            clear:both;
        }
        .bottom {
            margin-top:15px;
        }
        .error {
            font-size: 16px;
            color:red;
            display:none;
            margin-top:5px;
            font-weight:bold;
        }
        .download-html {
            margin-top:10px;
        }
    </style>
    <script src="//code.jquery.com/jquery-1.11.0.min.js"></script>
</head>
<body>
    <form id="form1" runat="server">
        <div class="container" style="width: 980px; margin: 0 auto; font-size: 14px;">
            <fieldset class="title" style="padding-bottom:35px;">
                <legend><b>Download Web Form For Marketers Report</b>
                </legend>
                


                <div class="left" style="width: 40%; text-align: center; float: left; min-height: 315px; padding-right: 20px;">
                    <fieldset class="title" style="min-height: 315px; padding: 10px 20px;">
                        <legend><b>Select Form</b>
                        </legend>
                        

                        <asp:DropDownList ID="ddlForms" Width="100%" Height="30px" CssClass="ddl-forms" runat="server" AutoPostBack="false">
                            <asp:ListItem Value="0">Select Form</asp:ListItem>
                        </asp:DropDownList>
                        <br class="clearfix">
                    </fieldset>
                </div>
                <div class="right" style="float: left; width: 40%">
                    <fieldset class="title" style="min-height: 140px; text-align: center;">
                        <legend><b>Output File Name</b>
                        </legend>
                        

                        <asp:TextBox ID="txtOutputFileName" Width="88%" Height="28px" CssClass="txt-output" runat="server"></asp:TextBox>
                    </fieldset>
                    <div class="bottom">
                        <fieldset class="title" style="min-height: 140px; text-align:center;">
                            <legend><b>Download Reports</b>
                            </legend>
                            
<asp:Button ID="btnHTMLDownload" CssClass="btn-submit download-html" Text="Download Excel" runat="server" OnClick="btnSubmit_Click" />
                            <asp:Panel ID="pnlError" runat="server" CssClass="error">
                                Please select any form
                            </asp:Panel>
                        </fieldset>
                    </div>
                </div>

                <div class="clear"></div>
            </fieldset>
        </div>

    </form>
    <script type="text/javascript">
        $(document).ready(function () {
            $('.btn-submit').click(function (e) {
                if ($('.ddl-forms').val() == "0") {
                    e.preventDefault();
                    $('.error').show();
                }
                else {
                    $('.error').hide();
                }
            });
        });
    </script>
</body>
   
</html>

In .cs file (formreport.aspx.cs) of above page I have written below code.


protected void Page_Load(object sender, EventArgs e)
{
     if (!IsPostBack)
     {
         var wffmFolderItem = Sitecore.Context.Database.GetItem("/sitecore/system/Modules/Web Forms for Marketers");

         if (wffmFolderItem != null)
         {
             var forms = wffmFolderItem.Axes.GetDescendants().Where(i => i.TemplateID.ToString() == "{FFB1DA32-2764-47DB-83B0-95B843546A7E}");
             foreach (FormItem itm in forms)
             {
                 ddlForms.Items.Add(new ListItem(itm.DisplayName, itm.ID.ToString()));
             }
                 ddlForms.DataBind();
          }
      }
}

protected void btnSubmit_Click(object sender, EventArgs e)
{
    try
    {
        if (ddlForms.SelectedValue.ToString() != "0")
        {
            pnlError.Visible = false;
            string formID = ddlForms.SelectedValue.ToString();
            string fileName = "excel";
            if (!string.IsNullOrEmpty(txtOutputFileName.Text))
            {
                fileName = txtOutputFileName.Text.Replace(".", "_");
            }
            ExportToExcelCommon.ExportToExcelFile(formID, fileName);
        }
     }
     catch (Exception ex)
     {
         Sitecore.Diagnostics.Log.Error("Error in Custom Export To Excel", ex, this);
     }
}

On front end I have provided a drop down with all forms of Site. I Selected a form and enter my output file name in “Output File Name” text box and click “Download Excel” button to download excel report of selected form.

 

Advertisements
This entry was posted in Sitecore and tagged , , , , , , , , , , , . Bookmark the permalink.

2 Responses to Sitecore: Export to CSV/Excel of Web Form For Marketers Form (WFFM) Reports

  1. Larry Reeder says:

    I have a project that could really use this functionality. I tried solving the needed references, but it appears that this was for a version prior to Sitecore XP 8 using MVC. Our intranet is on v8 and it seems “Sitecore.Form.*” is no longer valid and so there’s no reference for it.

    Do you have plans to post a v8 version of this project?

    Thanks for the post and all the helpful info.

  2. Yogesh says:

    Hi Larry,
    Thanks for showing the interest!!
    I’ll try to update it asap for Sitecore XP8 version and will let you know.
    -Regards,
    Yogesh

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