public HttpResponseMessage ExportFormData([FromBody] FormViewExportRequest request)
{
var entries = this.dataProvider.GetEntries(request.FormId, request.StartDate, request.EndDate);
string formName = "sample";
string fileName = string.Format("Export_{0}_{1}.csv", formName, DateTime.Now.ToString("yyyyMMdd_HHmmss"));
Stream stream = null;
if (request.ExportOption.Equals("excel", StringComparison.OrdinalIgnoreCase))
{
stream = this.exportService.CreateExcel(request, entries.ToList());
stream.Seek(0, SeekOrigin.Begin);
}
else
{
stream = new MemoryStream();
StreamWriter writer = new StreamWriter(stream);
writer.Write(CreateReport(request, entries.ToList()));
writer.Flush();
stream.Position = 0;
}
HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK);
result.Content = new StreamContent(stream);
result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream");
result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
{
FileName = fileName
};
return(result);
}
/// <summary>
/// Exports to CSV.
/// </summary>
/// <param name="request">The request.</param>
/// <param name="entries">The entries.</param>
/// <returns></returns>
public string ExportToCsv(FormViewExportRequest request, List <FormEntry> entries)
{
StringBuilder sb = new StringBuilder();
string headerRow = string.Empty;
headerRow = string.Join(",", request.Fields.ToArray());
sb.AppendLine(headerRow);
for (int k = 0; k < entries.Count; k++)
{
// Row Tag
StringBuilder row = new StringBuilder();
for (int j = 0; j < request.Fields.Count; j++)
{
// Cell Tags
var field = entries.ToList()[k].Fields.FirstOrDefault(t => t.FieldName == request.Fields[j]);
string value = string.Empty;
if (field == null)
{
if (request.Fields[j].Equals("Created"))
{
value = entries.ToList()[k].Created.ToString();
}
else
{
value = "-";
}
}
else if (field.ValueType.Equals("System.Collections.Generic.List`1[Sitecore.ExperienceForms.Data.Entities.StoredFileInfo]", StringComparison.OrdinalIgnoreCase))
{
if (field.Value.Contains(','))
{
foreach (var file in field.Value.Split(',').Where(t => !string.IsNullOrEmpty(t)))
{
value += Sitecore.Web.WebUtil.GetFullUrl(string.Format(FileDownloadPattern, file));
}
}
else
{
value = Sitecore.Web.WebUtil.GetFullUrl(string.Format(FileDownloadPattern, field.Value));
}
}
else
{
value = field.Value;
}
row.Append(value);
row.Append(",");
}
sb.AppendLine(row.ToString());
}
return(sb.ToString());
}
/// <summary>
/// Exports to XML.
/// </summary>
/// <param name="request">The request.</param>
/// <param name="entries">The entries.</param>
/// <returns>XML in string</returns>
public string ExportToXml(FormViewExportRequest request, List <List <string> > entries)
{
var xDocument = new XDocument();
var root = new XElement("root");
foreach (var row in entries)
{
var entry = new XElement("entry");
for (int i = 0; i < request.Fields.Count; i++)
{
entry.Add(new XElement(request.Fields[i].Trim().Replace(" ", "").ToLower(), row[i]));
}
root.Add(entry);
}
xDocument.Add(root);
return(xDocument.ToString());
}
/// <summary>
/// Creates the excel.
/// </summary>
/// <param name="request">The request.</param>
/// <param name="entries">The entries.</param>
/// <returns></returns>
public Stream CreateExcel(FormViewExportRequest request, List <FormEntry> entries)
{
int rowNumber = 1;
MemoryStream excelStream = new MemoryStream();
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(excelStream, SpreadsheetDocumentType.Workbook))
{
var worksheetPart = this.CreateWorkSheet(spreadsheetDocument, "FormExport");
SheetData sheetData = new SheetData();
this.AddRow(sheetData, rowNumber++, request.Fields.ToArray(), new Dictionary <int, int>());
List <object> columnValues = new List <object>();
for (int k = 0; k < entries.Count; k++)
{
columnValues.Clear();
for (int j = 0; j < request.Fields.Count; j++)
{
// Cell Tags
var field = entries.ToList()[k].Fields.FirstOrDefault(t => t.FieldName == request.Fields[j]);
string value = string.Empty;
if (field == null)
{
if (request.Fields[j].Equals("Created"))
{
value = entries.ToList()[k].Created.ToString();
}
else
{
value = "-";
}
}
else if (field.ValueType.Equals("System.Collections.Generic.List`1[Sitecore.ExperienceForms.Data.Entities.StoredFileInfo]", StringComparison.OrdinalIgnoreCase))
{
if (field.Value.Contains(','))
{
foreach (var file in field.Value.Split(',').Where(t => !string.IsNullOrEmpty(t)))
{
value += Sitecore.Web.WebUtil.GetFullUrl(string.Format(FileDownloadPattern, file));
}
}
else
{
value = Sitecore.Web.WebUtil.GetFullUrl(string.Format(FileDownloadPattern, field.Value));
}
}
else
{
value = field.Value;
}
columnValues.Add(value);
}
this.AddRow(sheetData, rowNumber++, columnValues.ToArray(), new Dictionary <int, int>());
}
worksheetPart.Worksheet.Append(sheetData);
return(excelStream);
}
}
private string CreateReport(FormViewExportRequest request, List <FormEntry> entries)
{
string result = string.Empty;
var response = new FormsViewerResponse();
response.Entries = new List <List <string> >();
foreach (var entry in entries)
{
List <string> rowData = new List <string>();
foreach (var header in request.Fields)
{
var field = entry.Fields.FirstOrDefault(t => t.FieldName == header);
string value = string.Empty;
if (field == null)
{
if (header.Equals("Created"))
{
value = entry.Created.ToString();
}
else
{
value = "-";
}
}
else if (field.ValueType.Equals("System.Collections.Generic.List`1[Sitecore.ExperienceForms.Data.Entities.StoredFileInfo]", StringComparison.OrdinalIgnoreCase))
{
if (field.Value.Contains(','))
{
foreach (var file in field.Value.Split(',').Where(t => !string.IsNullOrEmpty(t)))
{
value += string.Format("<a target=\"_blank\" href=\"{0}\">Download</a><br>", Sitecore.Web.WebUtil.GetFullUrl(string.Format(FileDownloadPattern, file)));
}
}
else
{
value = string.Format("<a target=\"_blank\" href=\"{0}\">Download</a>", Sitecore.Web.WebUtil.GetFullUrl(string.Format(FileDownloadPattern, field.Value)));
}
}
else
{
value = field.Value;
}
rowData.Add(value);
}
response.Entries.Add(rowData);
}
if (request.ExportOption.Equals("xml", StringComparison.OrdinalIgnoreCase))
{
return(exportService.ExportToXml(request, response.Entries.ToList()));
}
else if (request.ExportOption.Equals("csv", StringComparison.OrdinalIgnoreCase))
{
return(exportService.ExportToCsv(request, entries.ToList()));
}
return(result);
}
