public static string GetDateFormat(DataType dataType, IDataExportSettings settings, string displayFormat)
{
if (!string.IsNullOrEmpty(displayFormat))
{
var dfmt = _formatRegex.Match(displayFormat).Groups[1].Value;
if (dfmt == "d")
{
return(BuildShortDateTimeFormat(settings.Culture, DataType.Date));
}
else if (dfmt == "D")
{
return(BuildLongDateTimeFormat(settings.Culture, DataType.Date));
}
else if (dfmt == "f")
{
return(BuildShortDateTimeFormat(settings.Culture, DataType.DateTime));
}
else if (dfmt == "F")
{
return(BuildLongDateTimeFormat(settings.Culture, DataType.DateTime));
}
return(dfmt);
}
return(BuildShortDateTimeFormat(settings.Culture, dataType));
}
public static string GetExcelDisplayFormat(IDataExportSettings settings, string displayFormat)
{
if (_formatRegex.IsMatch(displayFormat))
{
return(_formatRegex.Replace(displayFormat, m => {
var format = m.Groups[1].Value;
var type = char.ToUpperInvariant(format[0]);
var digits = (format.Length > 1)
? int.Parse(format.Substring(1))
: type == 'D' ? 1 : 2;
if (type == 'D')
{
return new string('0', digits);
}
var floatFormat = "#0." + new string('0', digits);
if (type == 'C')
{
return settings.Culture.NumberFormat.CurrencySymbol + floatFormat;
}
return floatFormat;
}));
}
return(displayFormat);
}
/// <summary>
/// Exports the specified data to the stream with the specified formats.
/// </summary>
/// <param name="data">The fetched data.</param>
/// <param name="stream">The stream.</param>
/// <param name="settings">The settings.</param>
public void Export(IEasyDataResultSet data, Stream stream, IDataExportSettings settings)
{
ExportAsync(data, stream, settings)
.ConfigureAwait(false)
.GetAwaiter()
.GetResult();
}
/// <summary>
/// Asynchronical version of <see cref="HtmlDataExporter.Export(IEasyDataResultSet,Stream, IDataExportSettings)" /> method.
/// </summary>
/// <param name="data">The fetched data.</param>
/// <param name="stream">The stream.</param>
/// <param name="settings">The settings.</param>
/// <returns>Task.</returns>
public async Task ExportAsync(IEasyDataResultSet data, Stream stream, IDataExportSettings settings)
{
// do not close stream
var writer = new StreamWriter(stream, new UTF8Encoding(false));
await ExportAsync(data, writer, settings);
}
/// <summary>
/// Asynchronical version of <see cref="ExcelDataExporter.Export(IEasyDataResultSet,Stream, IDataExportSettings)" /> method.
/// </summary>
/// <param name="data">The fetched data.</param>
/// <param name="stream">The stream.</param>
/// <param name="settings">The settings.</param>
/// <param name="ct">The cacnellation token.</param>
/// <returns>Task.</returns>
public async Task ExportAsync(IEasyDataResultSet data, Stream stream, IDataExportSettings settings, CancellationToken ct = default)
{
var mappedSettings = MapSettings(settings);
// predefined formatters
var predefinedFormatters = GetPredefinedFormatters(data.Cols, settings);
// replace forbidden symbols
var r = new Regex(@"[\/\*\?:\[\]]");
var result = r.Replace(mappedSettings.Title ?? "", "");
var sheetName = !string.IsNullOrWhiteSpace(result)
? result
: "Sheet 1";
var wb = new XLWorkbook();
var ws = wb.Worksheets.Add(sheetName);
var startLetter = 'B';
var startNum = 2;
var cellNum = startNum;
if (mappedSettings.ShowDatasetInfo)
{
if (!string.IsNullOrWhiteSpace(mappedSettings.Title))
{
ws.Cell($"{startLetter}{cellNum}").Value = mappedSettings.Title;
cellNum++;
}
if (!string.IsNullOrWhiteSpace(mappedSettings.Description))
{
ws.Cell($"{startLetter}{cellNum}").Value = mappedSettings.Description;
cellNum++;
}
}
var ignoredCols = GetIgnoredColumns(data, settings);
// filling cols
if (settings.ShowColumnNames)
{
var colCellLetter = startLetter;
for (int i = 0; i < data.Cols.Count; i++)
{
if (ignoredCols.Contains(i))
{
continue;
}
var colName = data.Cols[i].Label;
ws.Cell($"{colCellLetter}{cellNum}").Value = colName;
colCellLetter++;
}
cellNum++;
}
var endHeaderNum = cellNum;
var endCellLetter = startLetter;
Task WriteRowAsync(EasyDataRow row, bool isExtra = false, CancellationToken cancellationToken = default)
/// <summary>
/// Asynchronical version of <see cref="HtmlDataExporter.Export(IEasyDataResultSet,Stream, IDataExportSettings)" /> method.
/// </summary>
/// <param name="data">The fetched data.</param>
/// <param name="stream">The stream.</param>
/// <param name="settings">The settings.</param>
/// <param name="ct">The cancellation token.</param>
/// <returns>Task.</returns>
public async Task ExportAsync(IEasyDataResultSet data, Stream stream, IDataExportSettings settings, CancellationToken ct = default)
{
// do not close stream
var writer = new StreamWriter(stream, new UTF8Encoding(false));
await ExportAsync(data, writer, settings, ct);
}
private static List <int> GetIgnoredColumns(IEasyDataResultSet data, IDataExportSettings settings)
{
var result = new List <int>();
for (int i = 0; i < data.Cols.Count; i++)
{
var add = settings?.ColumnFilter?.Invoke(data.Cols[i]);
if (add.HasValue && !add.Value)
{
result.Add(i);
}
}
return(result);
}
public static string GetFormattedValue(object val, DataType dataType, IDataExportSettings settings, string displayFormat)
{
if (val == null)
{
return("");
}
if (val is DateTime)
{
DateTime dt = (DateTime)val;
if (!string.IsNullOrEmpty(displayFormat))
{
return(string.Format(settings.Culture, displayFormat, val));
}
var format = BuildShortDateTimeFormat(settings.Culture, dataType);
return(dt.ToString(format, CultureInfo.InvariantCulture));
}
else if (val is DateTimeOffset)
{
DateTimeOffset dt = (DateTimeOffset)val;
if (!string.IsNullOrEmpty(displayFormat))
{
return(string.Format(settings.Culture, displayFormat, val));
}
var format = BuildShortDateTimeFormat(settings.Culture, dataType);
return(dt.ToString(format, CultureInfo.InvariantCulture));
}
else if (val is float || val is double || val is int || val is decimal)
{
if (!string.IsNullOrEmpty(displayFormat))
{
return(string.Format(settings.Culture, displayFormat, val));
}
return(string.Format(settings.Culture, "{0}", val));
}
return(val.ToString());
}
private static PdfDataExportSettings MapSettings(IDataExportSettings settings)
{
if (settings is PdfDataExportSettings)
{
return(settings as PdfDataExportSettings);
}
var result = PdfDataExportSettings.Default;
result.Title = settings.Title;
result.Description = settings.Description;
result.ShowDatasetInfo = settings.ShowDatasetInfo;
result.Culture = settings.Culture;
result.ShowColumnNames = settings.ShowColumnNames;
result.RowFilter = settings.RowFilter;
result.ColumnFilter = settings.ColumnFilter;
return(result);
}
private CsvDataExportSettings MapSettings(IDataExportSettings settings)
{
if (settings is CsvDataExportSettings)
{
return(settings as CsvDataExportSettings);
}
var result = CsvDataExportSettings.Default;
result.Title = settings.Title;
result.Description = settings.Description;
result.ShowDatasetInfo = settings.ShowDatasetInfo;
result.Culture = settings.Culture;
result.ShowColumnNames = settings.ShowColumnNames;
result.RowFilter = settings.RowFilter;
result.ColumnFilter = settings.ColumnFilter;
result.BeforeRowAdded = settings.BeforeRowAdded;
return(result);
}
/// <summary>
/// Asynchronical version of <see cref="PdfDataExporter.Export(IEasyDataResultSet,Stream, IDataExportSettings)" /> method.
/// </summary>
/// <param name="data">The fetched data.</param>
/// <param name="stream">The stream.</param>
/// <param name="settings">The settings.</param>
/// <returns>Task.</returns>
public async Task ExportAsync(IEasyDataResultSet data, Stream stream, IDataExportSettings settings)
{
var mappedSettings = MapSettings(settings);
var document = new Document();
document.Info.Title = settings.Title;
ApplyStyles(document, mappedSettings);
var section = document.AddSection();
if (settings.ShowDatasetInfo)
{
// TODO: render paragrap with info here
if (!string.IsNullOrWhiteSpace(mappedSettings.Title))
{
var p = section.AddParagraph();
p.Format.Alignment = ParagraphAlignment.Center;
p.Format.Font.Bold = true;
p.AddText(mappedSettings.Title);
}
if (!string.IsNullOrWhiteSpace(mappedSettings.Description))
{
var p = section.AddParagraph();
p.Format.Alignment = ParagraphAlignment.Left;
p.AddText(mappedSettings.Description);
}
}
section.AddParagraph();
// Create the item table
var table = section.AddTable();
table.Style = "Table";
table.Borders.Color = Color.FromRgb(0, 0, 0);
table.Borders.Width = 0.25;
table.Borders.Left.Width = 0.5;
table.Borders.Right.Width = 0.5;
table.Rows.LeftIndent = 0;
// filling columns
//ignored columns
var ignoredCols = GetIgnoredColumns(data, settings);
int colsCount = 0;
for (int i = 0; i < data.Cols.Count; i++)
{
if (ignoredCols.Contains(i))
{
continue;
}
var column = table.AddColumn(Unit.FromCentimeter(3));
column.Format.Alignment = ParagraphAlignment.Center;
colsCount++;
}
// filling rows
if (settings.ShowColumnNames)
{
var row = table.AddRow();
row.HeadingFormat = true;
row.Format.Alignment = ParagraphAlignment.Center;
row.Format.Font.Bold = true;
row.Shading.Color = Color.FromRgb(0, 191, 255);
for (int i = 0; i < data.Cols.Count; i++)
{
if (ignoredCols.Contains(i))
{
continue;
}
var colName = data.Cols[i].Label;
row.Cells[i].AddParagraph(colName);
row.Cells[i].Format.Font.Bold = false;
row.Cells[i].Format.Alignment = ParagraphAlignment.Center;
row.Cells[i].VerticalAlignment = VerticalAlignment.Center;
}
table.SetEdge(0, 0, colsCount, 1, Edge.Box, BorderStyle.Single, 0.75, Color.Empty);
}
// filling rows
var rows = data.Rows.Where(row => {
var add = settings?.RowFilter?.Invoke(row);
if (add.HasValue && !add.Value)
{
return(false);
}
return(true);
}).ToList();
Task WriteRowAsync(EasyDataRow row, bool isExtra = false)
{
var pdfRow = table.AddRow();
pdfRow.TopPadding = 1.5;
for (int i = 0; i < row.Count; i++)
{
if (ignoredCols.Contains(i))
{
continue;
}
var col = data.Cols[i];
var dfmt = col.DisplayFormat;
var type = col.Type;
var s = Utils.GetFormattedValue(row[i], type, mappedSettings, dfmt);
pdfRow.Cells[i].Shading.Color = Color.FromRgb(255, 255, 255);
pdfRow.Cells[i].VerticalAlignment = VerticalAlignment.Center;
pdfRow.Cells[i].Format.Alignment = MapAlignment(col.Style.Alignment);
pdfRow.Cells[i].Format.FirstLineIndent = 1;
pdfRow.Cells[i].Format.Font.Bold = isExtra;
pdfRow.Cells[i].AddParagraph(s);
table.SetEdge(0, 1, colsCount, 1,
Edge.Box, BorderStyle.Single, 0.75);
}
return(Task.CompletedTask);
}
Func <EasyDataRow, Task> WriteExtraRowAsync = (extraRow) => WriteRowAsync(extraRow, true);
foreach (var row in rows)
{
if (mappedSettings.BeforeRowAdded != null)
{
await mappedSettings.BeforeRowAdded(row, WriteExtraRowAsync);
}
await WriteRowAsync(row);
}
if (mappedSettings.BeforeRowAdded != null)
{
await mappedSettings.BeforeRowAdded(null, WriteExtraRowAsync);
}
// rendering pdf
var pdfRenderer = new PdfDocumentRenderer(true);
pdfRenderer.Document = document;
pdfRenderer.RenderDocument();
using (MemoryStream memoryStream = new MemoryStream()) {
pdfRenderer.PdfDocument.Save(memoryStream, false);
memoryStream.Seek(0, SeekOrigin.Begin);
await memoryStream.CopyToAsync(stream).ConfigureAwait(false);
}
}
private async Task ExportAsync(IEasyDataResultSet data, TextWriter writer, IDataExportSettings settings)
{
var mappedSettings = MapSettings(settings);
if (data == null)
{
return;
}
if (settings.ShowDatasetInfo)
{
if (!string.IsNullOrEmpty(mappedSettings.Title))
{
await writer.WriteLineAsync($"{mappedSettings.CommentCharacter} ").ConfigureAwait(false);
await writer.WriteLineAsync($"{mappedSettings.CommentCharacter} {mappedSettings.Title}").ConfigureAwait(false);
await writer.WriteLineAsync($"{mappedSettings.CommentCharacter} ").ConfigureAwait(false);
}
if (!string.IsNullOrEmpty(mappedSettings.Description))
{
await writer.WriteLineAsync($"{mappedSettings.CommentCharacter} ").ConfigureAwait(false);
await writer.WriteLineAsync($"{mappedSettings.CommentCharacter} {mappedSettings.Description}").ConfigureAwait(false);
await writer.WriteLineAsync($"{mappedSettings.CommentCharacter} ").ConfigureAwait(false);
}
}
//ignored columns
var ignoredCols = GetIgnoredColumns(data, settings);
//creating header
if (mappedSettings.ShowColumnNames)
{
var val = new StringBuilder();
for (int i = 0; i < data.Cols.Count; i++)
{
if (ignoredCols.Contains(i))
{
continue;
}
string colName = data.Cols[i].Label;
DataType type = data.Cols[i].Type;
if (i > 0)
{
val.Append(mappedSettings.Separator);
}
val.Append(GetFormattedValue(colName, DataType.String, mappedSettings, null));
}
await writer.WriteLineAsync(val.ToString()).ConfigureAwait(false);
}
async Task WriteRowAsync(EasyDataRow row, bool isExtra = false)
{
var rowContent = new StringBuilder();
for (int i = 0; i < row.Count; i++)
{
if (ignoredCols.Contains(i))
{
continue;
}
string dfmt = data.Cols[i].DisplayFormat;
DataType type = data.Cols[i].Type;
if (i > 0)
{
rowContent.Append(mappedSettings.Separator);
}
rowContent.Append(GetFormattedValue(row[i], type, mappedSettings, dfmt));
}
await writer.WriteLineAsync(rowContent.ToString()).ConfigureAwait(false);
}
Func <EasyDataRow, Task> WriteExtraRowAsync = (extraRow) => WriteRowAsync(extraRow, true);
foreach (var row in data.Rows)
{
var add = settings?.RowFilter?.Invoke(row);
if (add.HasValue && !add.Value)
{
continue;
}
if (mappedSettings.BeforeRowAdded != null)
{
await mappedSettings.BeforeRowAdded(row, WriteExtraRowAsync);
}
await WriteRowAsync(row, false);
}
if (mappedSettings.BeforeRowAdded != null)
{
await mappedSettings.BeforeRowAdded(null, WriteExtraRowAsync);
}
await writer.FlushAsync().ConfigureAwait(false);
}
/// <summary>
/// Asynchronical version of <see cref="CsvDataExporter.Export(IEasyDataResultSet,Stream, IDataExportSettings)" /> method.
/// </summary>
/// <param name="data">The data reader.</param>
/// <param name="stream">The stream.</param>
/// <param name="settings">The settings.</param>
/// <returns>Task.</returns>
public async Task ExportAsync(IEasyDataResultSet data, Stream stream, IDataExportSettings settings)
{
var writer = new StreamWriter(stream, new UTF8Encoding(false));
await ExportAsync(data, writer, settings).ConfigureAwait(false);
}
/// <summary>
/// Asynchronical version of <see cref="ExcelDataExporter.Export(IEasyDataResultSet,Stream, IDataExportSettings)" /> method.
/// </summary>
/// <param name="data">The fetched data.</param>
/// <param name="stream">The stream.</param>
/// <param name="settings">The settings.</param>
/// <returns>Task.</returns>
public async Task ExportAsync(IEasyDataResultSet data, Stream stream, IDataExportSettings settings)
{
var mappedSettings = MapSettings(settings);
// replace forbidden symbols
var r = new Regex(@"[\/\*\?:\[\]]");
var result = r.Replace(mappedSettings.Title ?? "", "");
var sheetName = !string.IsNullOrWhiteSpace(result)
? result
: "Sheet 1";
var wb = new XLWorkbook();
var ws = wb.Worksheets.Add(sheetName);
var startLetter = 'B';
var startNum = 2;
var cellNum = startNum;
if (mappedSettings.ShowDatasetInfo)
{
if (!string.IsNullOrWhiteSpace(mappedSettings.Title))
{
ws.Cell($"{startLetter}{cellNum}").Value = mappedSettings.Title;
cellNum++;
}
if (!string.IsNullOrWhiteSpace(mappedSettings.Description))
{
ws.Cell($"{startLetter}{cellNum}").Value = mappedSettings.Description;
cellNum++;
}
}
var ignoredCols = GetIgnoredColumns(data, settings);
// filling cols
if (settings.ShowColumnNames)
{
var colCellLetter = startLetter;
for (int i = 0; i < data.Cols.Count; i++)
{
if (ignoredCols.Contains(i))
{
continue;
}
var colName = data.Cols[i].Label;
ws.Cell($"{colCellLetter}{cellNum}").Value = colName;
colCellLetter++;
}
cellNum++;
}
var endHeaderNum = cellNum;
var endCellLetter = startLetter;
Task WriteRowAsync(EasyDataRow row, bool isExtra = false)
{
var rowCellLetter = startLetter;
for (int i = 0; i < row.Count; i++)
{
if (ignoredCols.Contains(i))
{
continue;
}
var dfmt = data.Cols[i].DisplayFormat;
var type = data.Cols[i].Type;
ws.Cell($"{rowCellLetter}{cellNum}").Value = row[i] ?? "";
if (isExtra)
{
ws.Cell($"{rowCellLetter}{cellNum}").Style.Font.Bold = true;
}
rowCellLetter++;
}
endCellLetter = --rowCellLetter;
cellNum++;
return(Task.CompletedTask);
}
Func <EasyDataRow, Task> WriteExtraRowAsync = (extraRow) => WriteRowAsync(extraRow, true);
foreach (var row in data.Rows)
{
var add = settings?.RowFilter?.Invoke(row);
if (add.HasValue && !add.Value)
{
continue;
}
if (mappedSettings.BeforeRowAdded != null)
{
await mappedSettings.BeforeRowAdded(row, WriteExtraRowAsync);
}
await WriteRowAsync(row);
}
if (mappedSettings.BeforeRowAdded != null)
{
await mappedSettings.BeforeRowAdded(null, WriteExtraRowAsync);
}
cellNum--;
// Setup formats
var letter = startLetter;
for (int i = 0; i < data.Cols.Count; i++)
{
if (ignoredCols.Contains(i))
{
continue;
}
var col = data.Cols[i];
var type = col.Type;
var dfmt = col.DisplayFormat;
var colRange = ws.Range($"{letter}{endHeaderNum}:{letter}{cellNum}");
var dataType = MapDataType(type);
colRange.DataType = dataType;
if (dataType == XLDataType.DateTime)
{
var format = Utils.GetDateFormat(type, mappedSettings, dfmt);
colRange.Style.DateFormat.Format = format;
}
else if (!string.IsNullOrEmpty(dfmt))
{
var format = Utils.GetExcelDisplayFormat(mappedSettings, dfmt);
colRange.Style.NumberFormat.Format = format;
}
colRange.Style.Alignment.Horizontal = MapAlignment(col.Style.Alignment);
letter++;
}
// Setup styles
var rngTable = ws.Range($"{startLetter}{startNum}:{endCellLetter}{cellNum}");
var rowNum = 1;
if (mappedSettings.ShowDatasetInfo)
{
if (!string.IsNullOrWhiteSpace(mappedSettings.Title))
{
rngTable.Cell(rowNum, 1).Style.Font.Bold = true;
rngTable.Cell(rowNum, 1).Style.Fill.BackgroundColor = XLColor.CornflowerBlue;
rngTable.Cell(rowNum, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
rngTable.Row(rowNum).Merge();
rowNum++;
}
if (!string.IsNullOrWhiteSpace(mappedSettings.Description))
{
rngTable.Cell(rowNum, 1).Style.Font.Bold = true;
rngTable.Cell(rowNum, 1).Style.Fill.BackgroundColor = XLColor.White;
rngTable.Cell(rowNum, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
rngTable.Row(rowNum).Merge();
rowNum++;
}
}
if (settings.ShowColumnNames)
{
var rngHeaders = rngTable.Range($"{startLetter}{rowNum + 1}:{endCellLetter}{rowNum + 1}"); // The address is relative to rngTable (NOT the worksheet)
rngHeaders.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
rngHeaders.Style.Font.Bold = true;
rngHeaders.Style.Fill.BackgroundColor = XLColor.Aqua;
}
rngTable.Style.Border.BottomBorder = XLBorderStyleValues.Thin;
rngTable.Style.Border.OutsideBorder = XLBorderStyleValues.Thick;
ws.Columns(2, 2 + (int)(endCellLetter - startLetter)).AdjustToContents();
using (MemoryStream memoryStream = new MemoryStream()) {
wb.SaveAs(memoryStream);
memoryStream.Seek(0, SeekOrigin.Begin);
await memoryStream.CopyToAsync(stream).ConfigureAwait(false);
}
}
private async Task ExportAsync(IEasyDataResultSet data, TextWriter writer, IDataExportSettings settings, CancellationToken ct)
{
var mappedSettings = MapSettings(settings);
if (data == null)
{
return;
}
// predefined formatters
var predefinedFormatters = GetPredefinedFormatters(data.Cols, settings);
if (settings.ShowDatasetInfo)
{
if (!string.IsNullOrEmpty(mappedSettings.Title))
{
await writer.WriteLineAsync($"{mappedSettings.CommentCharacter} ").ConfigureAwait(false);
await writer.WriteLineAsync($"{mappedSettings.CommentCharacter} {mappedSettings.Title}").ConfigureAwait(false);
await writer.WriteLineAsync($"{mappedSettings.CommentCharacter} ").ConfigureAwait(false);
}
if (!string.IsNullOrEmpty(mappedSettings.Description))
{
await writer.WriteLineAsync($"{mappedSettings.CommentCharacter} ").ConfigureAwait(false);
await writer.WriteLineAsync($"{mappedSettings.CommentCharacter} {mappedSettings.Description}").ConfigureAwait(false);
await writer.WriteLineAsync($"{mappedSettings.CommentCharacter} ").ConfigureAwait(false);
}
}
//ignored columns
var ignoredCols = GetIgnoredColumns(data, settings);
//creating header
if (mappedSettings.ShowColumnNames)
{
var val = new StringBuilder();
for (int i = 0; i < data.Cols.Count; i++)
{
if (ignoredCols.Contains(i))
{
continue;
}
string colName = data.Cols[i].Label;
DataType type = data.Cols[i].Type;
if (i > 0)
{
val.Append(mappedSettings.Separator);
}
val.Append(GetFormattedValue(colName, DataType.String, mappedSettings, null));
}
await writer.WriteLineAsync(val.ToString()).ConfigureAwait(false);
}
async Task WriteRowAsync(EasyDataRow row, bool isExtra = false, CancellationToken cancellationToken = default)
private async Task ExportAsync(IEasyDataResultSet data, TextWriter writer, IDataExportSettings settings)
{
var mappedSettings = MapSettings(settings);
if (data == null)
{
return;
}
await writer.WriteLineAsync("<!DOCTYPE HTML PUBLIC ''-//W3C//DTD HTML 4.0 Transitional//EN''>").ConfigureAwait(false);
await writer.WriteLineAsync("<html>").ConfigureAwait(false);
await writer.WriteLineAsync("<head>").ConfigureAwait(false);
await writer.WriteLineAsync("<meta http-equiv=Content-Type content=\"text/html;charset=utf-8\">").ConfigureAwait(false);
await writer.WriteLineAsync("<meta name=ProgId content=Excel.Sheet/>").ConfigureAwait(false);
await writer.WriteLineAsync("<meta name=Generator content=\"Microsoft Excel 11\">").ConfigureAwait(false);
await writer.WriteLineAsync("<style>").ConfigureAwait(false);
await writer.WriteLineAsync(" tr {vertical-align:top;}").ConfigureAwait(false);
await writer.WriteLineAsync(" td br, td p, td ul, td ol, td li {mso-data-placement:same-cell;}").ConfigureAwait(false);
await writer.WriteLineAsync(" .eq-title {"
+ $" font: bold {mappedSettings.FontSize + 4}pt {mappedSettings.FontFamily}"
+ "}").ConfigureAwait(false);
await writer.WriteLineAsync(" .eq-extra-row {"
+ $" font-weight: bold"
+ "}").ConfigureAwait(false);
await writer.WriteLineAsync(" .eq-desc {"
+ $" font: {mappedSettings.FontSize + 1}pt {mappedSettings.FontFamily}"
+ "}").ConfigureAwait(false);
await writer.WriteLineAsync(" .eq-result-set {").ConfigureAwait(false);
await writer.WriteLineAsync(string.Format(" border-color: {0};", mappedSettings.TableBorderColor)).ConfigureAwait(false);
await writer.WriteLineAsync(string.Format(" background-color: {0};", mappedSettings.TableBgColor)).ConfigureAwait(false);
await writer.WriteLineAsync(string.Format(" font-size: {0}.0pt;", mappedSettings.FontSize)).ConfigureAwait(false);
await writer.WriteLineAsync(string.Format(" font-family: {0};", mappedSettings.FontFamily)).ConfigureAwait(false);
await writer.WriteLineAsync(string.Format(" padding: 0;")).ConfigureAwait(false);
await writer.WriteLineAsync(" }");
await writer.WriteLineAsync(" .eq-result-set thead tr {").ConfigureAwait(false);
await writer.WriteLineAsync(string.Format(" color: {0};", mappedSettings.HeaderFgColor)).ConfigureAwait(false);
await writer.WriteLineAsync(string.Format(" background-color: {0};", mappedSettings.HeaderBgColor)).ConfigureAwait(false);
await writer.WriteLineAsync(string.Format(" font-weight: {0};", mappedSettings.HeaderFontWeight)).ConfigureAwait(false);
await writer.WriteLineAsync(" }");
await writer.WriteLineAsync("</style>").ConfigureAwait(false);
await writer.WriteLineAsync("<body>").ConfigureAwait(false);
if (mappedSettings.ShowDatasetInfo)
{
if (!string.IsNullOrEmpty(mappedSettings.Title))
{
await writer.WriteLineAsync($"<div class=\"eq-title\">{mappedSettings.Title}</div>").ConfigureAwait(false);
}
if (!string.IsNullOrEmpty(mappedSettings.Description))
{
await writer.WriteLineAsync($"<div class=\"eq-desc\">{mappedSettings.Description}</div>").ConfigureAwait(false);
}
}
await writer.WriteLineAsync("<table class=\"eq-result-set\" border=\"1\" cellspacing=\"0\">").ConfigureAwait(false);
//ignored columns
var ignoredCols = GetIgnoredColumns(data, settings);
//creating header
if (mappedSettings.ShowColumnNames)
{
await writer.WriteLineAsync("<thead>").ConfigureAwait(false);
await writer.WriteLineAsync("<tr>").ConfigureAwait(false);
for (int i = 0; i < data.Cols.Count; i++)
{
if (ignoredCols.Contains(i))
{
continue;
}
string colName = data.Cols[i].Label;
await writer.WriteLineAsync(string.Format("<th>{0}</th>", colName)).ConfigureAwait(false);
}
await writer.WriteLineAsync("</tr>").ConfigureAwait(false);
await writer.WriteLineAsync("</thead>").ConfigureAwait(false);
}
await writer.WriteLineAsync("<tbody>");
int a = 0;
async Task RenderRowAsync(EasyDataRow row, bool isExtra = false)
{
await writer.WriteLineAsync($"<tr {(isExtra ? "class=\"eq-extra-row\"" : "")}>").ConfigureAwait(false);
for (int i = 0; i < row.Count; i++)
{
if (ignoredCols.Contains(i))
{
continue;
}
string dfmt = data.Cols[i].DisplayFormat;
DataType type = data.Cols[i].Type;
string s = GetFormattedValue(row[i], type, mappedSettings, dfmt);
if (mappedSettings.FixHtmlTags)
{
s = FixHtmlTags(s);
}
await writer.WriteLineAsync(string.Format("<td>{0}</td>", s)).ConfigureAwait(false);
}
await writer.WriteLineAsync("</tr>").ConfigureAwait(false);
}
Func <EasyDataRow, Task> RenderExtraRowAsync = (extraRow) => RenderRowAsync(extraRow, true);
foreach (var row in data.Rows)
{
var add = settings?.RowFilter?.Invoke(row);
if (add.HasValue && !add.Value)
{
continue;
}
if (mappedSettings.BeforeRowAdded != null)
{
await mappedSettings.BeforeRowAdded(row, RenderExtraRowAsync);
}
await RenderRowAsync(row);
a++;
}
if (mappedSettings.BeforeRowAdded != null)
{
await mappedSettings.BeforeRowAdded(null, RenderExtraRowAsync);
}
await writer.WriteLineAsync("</tbody>").ConfigureAwait(false);
await writer.WriteLineAsync("</table>").ConfigureAwait(false);
await writer.WriteLineAsync("</body>").ConfigureAwait(false);
await writer.WriteLineAsync("</html>").ConfigureAwait(false);
await writer.FlushAsync().ConfigureAwait(false);
}
private async Task ExportAsync(IEasyDataResultSet data, TextWriter writer, IDataExportSettings settings, CancellationToken ct)
{
var mappedSettings = MapSettings(settings);
if (data == null)
{
return;
}
// predefined formatters
var predefinedFormatters = GetPredefinedFormatters(data.Cols, settings);
await writer.WriteLineAsync("<!DOCTYPE HTML PUBLIC ''-//W3C//DTD HTML 4.0 Transitional//EN''>").ConfigureAwait(false);
await writer.WriteLineAsync("<html>").ConfigureAwait(false);
await writer.WriteLineAsync("<head>").ConfigureAwait(false);
await writer.WriteLineAsync("<meta http-equiv=Content-Type content=\"text/html;charset=utf-8\">").ConfigureAwait(false);
await writer.WriteLineAsync("<meta name=ProgId content=Excel.Sheet/>").ConfigureAwait(false);
await writer.WriteLineAsync("<meta name=Generator content=\"Microsoft Excel 11\">").ConfigureAwait(false);
await writer.WriteLineAsync("<style>").ConfigureAwait(false);
await writer.WriteLineAsync(" tr {vertical-align:top;}").ConfigureAwait(false);
await writer.WriteLineAsync(" td br, td p, td ul, td ol, td li {mso-data-placement:same-cell;}").ConfigureAwait(false);
await writer.WriteLineAsync(" .eq-title {"
+ $" font: bold {mappedSettings.FontSize + 4}pt {mappedSettings.FontFamily}"
+ "}").ConfigureAwait(false);
await writer.WriteLineAsync(" .eq-extra-row {"
+ $" font-weight: bold"
+ "}").ConfigureAwait(false);
await writer.WriteLineAsync(" .eq-desc {"
+ $" font: {mappedSettings.FontSize + 1}pt {mappedSettings.FontFamily}"
+ "}").ConfigureAwait(false);
await writer.WriteLineAsync(" .eq-result-set {").ConfigureAwait(false);
await writer.WriteLineAsync(string.Format(" border-color: {0};", mappedSettings.TableBorderColor)).ConfigureAwait(false);
await writer.WriteLineAsync(string.Format(" background-color: {0};", mappedSettings.TableBgColor)).ConfigureAwait(false);
await writer.WriteLineAsync(string.Format(" font-size: {0}.0pt;", mappedSettings.FontSize)).ConfigureAwait(false);
await writer.WriteLineAsync(string.Format(" font-family: {0};", mappedSettings.FontFamily)).ConfigureAwait(false);
await writer.WriteLineAsync(string.Format(" padding: 0;")).ConfigureAwait(false);
await writer.WriteLineAsync(" }");
await writer.WriteLineAsync(" .eq-result-set thead tr {").ConfigureAwait(false);
await writer.WriteLineAsync(string.Format(" color: {0};", mappedSettings.HeaderFgColor)).ConfigureAwait(false);
await writer.WriteLineAsync(string.Format(" background-color: {0};", mappedSettings.HeaderBgColor)).ConfigureAwait(false);
await writer.WriteLineAsync(string.Format(" font-weight: {0};", mappedSettings.HeaderFontWeight)).ConfigureAwait(false);
await writer.WriteLineAsync(" }");
await writer.WriteLineAsync("</style>").ConfigureAwait(false);
await writer.WriteLineAsync("<body>").ConfigureAwait(false);
if (mappedSettings.ShowDatasetInfo)
{
if (!string.IsNullOrEmpty(mappedSettings.Title))
{
await writer.WriteLineAsync($"<div class=\"eq-title\">{mappedSettings.Title}</div>").ConfigureAwait(false);
}
if (!string.IsNullOrEmpty(mappedSettings.Description))
{
await writer.WriteLineAsync($"<div class=\"eq-desc\">{mappedSettings.Description}</div>").ConfigureAwait(false);
}
}
await writer.WriteLineAsync("<table class=\"eq-result-set\" border=\"1\" cellspacing=\"0\">").ConfigureAwait(false);
//ignored columns
var ignoredCols = GetIgnoredColumns(data, settings);
//creating header
if (mappedSettings.ShowColumnNames)
{
await writer.WriteLineAsync("<thead>").ConfigureAwait(false);
await writer.WriteLineAsync("<tr>").ConfigureAwait(false);
for (int i = 0; i < data.Cols.Count; i++)
{
if (ignoredCols.Contains(i))
{
continue;
}
string colName = data.Cols[i].Label;
await writer.WriteLineAsync(string.Format("<th>{0}</th>", colName)).ConfigureAwait(false);
}
await writer.WriteLineAsync("</tr>").ConfigureAwait(false);
await writer.WriteLineAsync("</thead>").ConfigureAwait(false);
}
await writer.WriteLineAsync("<tbody>");
int a = 0;
async Task RenderRowAsync(EasyDataRow row, bool isExtra = false, CancellationToken cancellationToken = default)
/// <summary>
/// Asynchronical version of <see cref="PdfDataExporter.Export(IEasyDataResultSet,Stream, IDataExportSettings)" /> method.
/// </summary>
/// <param name="data">The fetched data.</param>
/// <param name="stream">The stream.</param>
/// <param name="settings">The settings.</param>
/// <param name="ct">The cancellation token.</param>
/// <returns>Task.</returns>
public async Task ExportAsync(IEasyDataResultSet data, Stream stream, IDataExportSettings settings, CancellationToken ct = default)
{
var mappedSettings = MapSettings(settings);
var document = new Document();
document.Info.Title = settings.Title;
ApplyStyles(document, mappedSettings);
var section = document.AddSection();
if (settings.ShowDatasetInfo)
{
// TODO: render paragrap with info here
if (!string.IsNullOrWhiteSpace(mappedSettings.Title))
{
var p = section.AddParagraph();
p.Format.Alignment = ParagraphAlignment.Center;
p.Format.Font.Bold = true;
p.AddText(mappedSettings.Title);
}
if (!string.IsNullOrWhiteSpace(mappedSettings.Description))
{
var p = section.AddParagraph();
p.Format.Alignment = ParagraphAlignment.Left;
p.AddText(mappedSettings.Description);
}
}
section.AddParagraph();
// Create the item table
var table = section.AddTable();
table.Style = "Table";
table.Borders.Color = Color.FromRgb(0, 0, 0);
table.Borders.Width = 0.25;
table.Borders.Left.Width = 0.5;
table.Borders.Right.Width = 0.5;
table.Rows.LeftIndent = 0;
// predefined formatters
var predefinedFormatters = GetPredefinedFormatters(data.Cols, settings);
// filling columns
//ignored columns
var ignoredCols = GetIgnoredColumns(data, settings);
int colsCount = 0;
for (int i = 0; i < data.Cols.Count; i++)
{
if (ignoredCols.Contains(i))
{
continue;
}
var column = table.AddColumn(Unit.FromCentimeter(3));
column.Format.Alignment = ParagraphAlignment.Center;
colsCount++;
}
// filling rows
if (settings.ShowColumnNames)
{
var row = table.AddRow();
row.HeadingFormat = true;
row.Format.Alignment = ParagraphAlignment.Center;
row.Format.Font.Bold = true;
row.Shading.Color = Color.FromRgb(0, 191, 255);
for (int i = 0; i < data.Cols.Count; i++)
{
if (ignoredCols.Contains(i))
{
continue;
}
var colName = data.Cols[i].Label;
row.Cells[i].AddParagraph(colName);
row.Cells[i].Format.Font.Bold = false;
row.Cells[i].Format.Alignment = ParagraphAlignment.Center;
row.Cells[i].VerticalAlignment = VerticalAlignment.Center;
}
table.SetEdge(0, 0, colsCount, 1, Edge.Box, BorderStyle.Single, 0.75, Color.Empty);
}
// filling rows
var rows = data.Rows.Where(row => {
var add = settings?.RowFilter?.Invoke(row);
if (add.HasValue && !add.Value)
{
return(false);
}
return(true);
}).ToList();
Task WriteRowAsync(EasyDataRow row, bool isExtra = false, CancellationToken cancellationToken = default)
