Reporting Adding CSV Export
The Serene template has a built in Excel export helper that is really useful for ad hoc downloads from user filtered and sorted data from a grid. Sometimes you might want to download this data in a CSV file to feed into other applications, or you might just prefer CSV because Excel is not available.
Follow these steps to add CSV export to your application that you can use alongside, or instead of, the Excel export.
CsvHelper
Add the following files under Modules/Common/Reporting
CsvReportAttribute.cs
using System;
namespace Serenity.Reporting
{
[AttributeUsage(AttributeTargets.Class, AllowMultiple = false)]
public class CsvReportAttribute : Attribute
{
public string Culture { get; set; }
public string Encoding { get; set; }
public string Delimiter { get; set; }
public Char? Escape { get; set; }
public Char? Quote { get; set; }
}
}CsvContentResult.cs
using System;
using Microsoft.AspNetCore.Mvc;
namespace Serenity.Web
{
public static class CsvContentResult
{
public static FileContentResult Create(byte[] data)
{
return Create(data, null);
}
public static FileContentResult Create(byte[] data, string downloadName)
{
var result = new FileContentResult(data, "text/csv");
result.FileDownloadName = downloadName ?? ("report" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".csv");
return result;
}
}
}CsvReportGenerator.cs
using FastMember;
using CsvHelper;
using Serenity.Data;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
namespace Serenity.Reporting
{
public class CsvExportOptions
{
public CsvExportOptions()
{
Culture = System.Globalization.CultureInfo.CurrentCulture;
Delimiter = Culture.TextInfo.ListSeparator;
Escape = '\"';
Quote = '\"';
}
public CultureInfo Culture { get; set; }
public string Delimiter { get; set; }
public Char Escape { get; set; }
public Char Quote { get; set; }
}
public static class CsvReportGenerator
{
public static byte[] ExportToByteArray(List<ReportColumn> columns, IList rows,
CsvExportOptions options = null, Encoding encoding = null)
{
var csvString = ExportToString(columns, rows, options);
return (encoding ?? Encoding.UTF8).GetBytes(csvString);
}
public static string ExportToString(List<ReportColumn> columns, IList rows,
CsvExportOptions options = null)
{
using (var writer = new StringWriter())
{
WriteData(writer, columns, rows, options);
writer.Flush();
return writer.ToString();
}
}
private static Type[] DateTimeTypes = new[]
{
typeof(DateTime),
typeof(DateTime?),
typeof(TimeSpan),
typeof(TimeSpan?)
};
private static string FixFormatSpecifier(string format, Type dataType)
{
if (string.IsNullOrEmpty(format))
return format;
if (format.IndexOf('f') >= 0 &&
Array.IndexOf(DateTimeTypes, dataType) >= 0)
return format.Replace('f', '0');
return format;
}
public static void WriteData(TextWriter textWriter, List<ReportColumn> columns, IList rows,
CsvExportOptions options = null)
{
if (columns == null)
throw new ArgumentNullException("columns");
if (rows == null)
throw new ArgumentNullException("rows");
options = options ?? new CsvExportOptions();
var config = new CsvHelper.Configuration.CsvConfiguration(options.Culture);
if (options.Culture != null)
config.CultureInfo = options.Culture;
if (options.Delimiter != null)
config.Delimiter = options.Delimiter;
config.Escape = options.Escape;
config.Quote = options.Quote;
Field[] fields = null;
TypeAccessor accessor = null;
bool[] invalidProperty = null;
var colCount = columns.Count;
int endCol = colCount;
int endRow = rows.Count + 1;
using (var writer = new CsvWriter(textWriter, config))
{
foreach (var caption in columns.Select(x => (x.Title ?? x.Name)).ToArray())
writer.WriteField(caption);
var dataList = new List<object[]>();
foreach (var obj in rows)
{
var data = new object[colCount];
var row = obj as Row;
if (row != null)
{
if (fields == null)
{
fields = new Field[colCount];
for (var i = 0; i < columns.Count; i++)
{
var n = columns[i].Name;
fields[i] = row.FindFieldByPropertyName(n) ?? row.FindField(n);
}
}
}
else if (obj != null)
{
if (obj is IDictionary || obj is IDictionary<string, object>)
{
}
else if (accessor == null)
{
accessor = TypeAccessor.Create(obj.GetType());
invalidProperty = new bool[colCount];
for (var c = 0; c < colCount; c++)
try
{
if (accessor[obj, columns[c].Name] != null)
{
}
}
catch
{
invalidProperty[c] = true;
}
}
}
for (var c = 0; c < colCount; c++)
{
if (row != null)
{
var field = fields[c];
if (!ReferenceEquals(null, field))
data[c] = field.AsObject(row);
}
else if (obj is IDictionary<string, object>)
{
var n = columns[c].Name;
var dict = obj as IDictionary<string, object>;
object v;
if (dict.TryGetValue(n, out v))
data[c] = v;
}
else if (obj is IDictionary)
{
var n = columns[c].Name;
var dict = obj as IDictionary;
if (dict.Contains(n))
data[c] = dict[n];
}
else if (obj != null)
{
if (!invalidProperty[c])
data[c] = accessor[obj, columns[c].Name];
}
}
dataList.Add(data);
}
if (dataList.Count > 0)
{
foreach (var data in dataList)
{
writer.NextRecord();
foreach (var field in data)
writer.WriteField<object>(field);
}
}
writer.Flush();
}
}
}
}CsvExportHelper.ts
namespace YourAwesomeProject.Common {
export interface CsvExportOptions {
grid: Serenity.DataGrid<any, any>;
service: string;
onViewSubmit: () => boolean;
title?: string;
hint?: string;
separator?: boolean;
}
export namespace CsvExportHelper {
export function createToolButton(options: CsvExportOptions): Serenity.ToolButton {
return {
hint: Q.coalesce(options.hint, 'CSV'),
title: Q.coalesce(options.title, ''),
cssClass: 'export-csv-button',
onClick: function () {
if (!options.onViewSubmit()) {
return;
}
let grid = options.grid;
var request = Q.deepClone(grid.getView().params) as Serenity.ListRequest;
request.Take = 0;
request.Skip = 0;
var sortBy = grid.getView().sortBy;
if (sortBy) {
request.Sort = sortBy;
}
request.IncludeColumns = [];
let columns = grid.getGrid().getColumns();
for (let column of columns) {
request.IncludeColumns.push(column.id || column.field);
}
Q.postToService({ service: options.service, request: request, target: '_blank' });
},
separator: options.separator
};
}
}
}Update ReportController.cs
In the Execute method, change the part with if (report is IDataOnlyReport):
if (report is IDataOnlyReport)
{
if (reportInfo.Type.GetAttribute<CsvReportAttribute>() != null)
{
ext = "csv";
renderedBytes = new ReportRepository().Render((IDataOnlyReport)report);
}
else
{
ext = "xlsx";
renderedBytes = new ReportRepository().Render((IDataOnlyReport)report);
}
}Update ReportRepository.cs
Add reference to System.Reflection:
using System.Reflection;Then add a RenderCsv method:
public byte[] RenderCsv(IDataOnlyReport report, CsvExportOptions options = null, System.Text.Encoding encoding = null)
{
var columns = report.GetColumnList();
var data = new List<object>();
var input = report.GetData();
var list = (input as IEnumerable) ?? new List<object> { input };
foreach (var item in list)
data.Add(item);
var attr = report.GetType().GetCustomAttribute<CsvReportAttribute>();
if (attr != null)
{
options = options ?? new CsvExportOptions();
if (attr.Encoding != null)
encoding = System.Text.Encoding.GetEncoding(attr.Encoding);
if (attr.Delimiter != null)
options.Delimiter = attr.Delimiter;
if (attr.Escape != null)
options.Escape = attr.Escape.Value;
if (attr.Quote != null)
options.Quote = attr.Quote.Value;
}
return CsvReportGenerator.ExportToByteArray(columns, data, options, encoding);
}Inside your XYZGrid.ts file, add:
protected getButtons() {
var buttons = super.getButtons();
buttons.splice(Q.indexOf(buttons, x => x.cssClass === "add-button"), 1);
buttons.push(Common.CsvExportHelper.createToolButton({
grid: this,
onViewSubmit: () => this.onViewSubmit(),
service: XYZService.baseUrl + '/ListCsv',
separator: true
}));
return buttons;
}Then in the corresponding XYZEndpoint.cs add:
Add reference to System:
using System;Add ListCsv method:
public FileContentResult ListCsv(IDbConnection connection, UserListRequest request)
{
var data = List(connection, request).Entities;
var report = new DynamicDataReport(data, request.IncludeColumns, typeof(Columns.XYZColumns));
// options and encoding is optional
var bytes = new ReportRepository().RenderCsv(report, new CsvExportOptions
{
Delimiter = ";"
}, Encoding.UTF8);
return CsvContentResult.Create(bytes, "XYZList_" +
DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".csv");
}Copyright © Serenity Platform 2017-present. All rights reserved.
Documentation | Serene Template | Live Demo | Premium Support | Issues | Discussions
