/// <inheritdoc />
/// <summary>
/// Generates output in Office Open Xml Excel format.
/// </summary>
protected override void Execute()
{
MemoryStream stream = null;
try
{
stream = new MemoryStream();
using (var excel = new ExcelPackage(stream))
{
#region destroy stream
stream = null;
#endregion
#region initialize
var items = Table.Fields;
var location = TableLocation;
var x = location.X;
var y = location.Y;
#endregion
#region get target data
var rows = Service.RawDataFiltered;
#endregion
#region add worksheet
var worksheet = excel.Workbook.Worksheets.Add(WorkSheetName);
worksheet.View.ShowGridLines = Table.ShowGridLines == YesNo.Yes;
#endregion
#region add styles
excel.Workbook.Styles.CreateFromModel(Resources.Styles);
#endregion
#region has logo?
worksheet.AppendLogo(Table.Logo);
#endregion
#region add top aggregates
var rowsCount = rows.Length;
var columnHeaders = Table.Headers;
var hasColumnheaders = columnHeaders.Any();
var topAggregates = items.GetRange(KnownAggregateLocation.Top).ToList();
var hasTopAggregates = topAggregates.Any();
if (hasTopAggregates)
{
foreach (var field in topAggregates)
{
var aggregate = field.Aggregate;
var formula = new ExcelFormulaResolver(aggregate)
{
StartRow = hasColumnheaders && Table.ShowColumnHeaders == YesNo.Yes ? 3 : 2,
EndRow = hasColumnheaders && Table.ShowColumnHeaders == YesNo.Yes ? rowsCount + 2 : rowsCount + 1,
HasAutoFilter = Table.AutoFilter,
};
var column = items.IndexOf(field);
var cell = worksheet.Cells[y, x + column];
cell.StyleName = aggregate.Style ?? StyleModel.NameOfDefaultStyle;
var type = aggregate.AggregateType;
if (type == KnownAggregateType.Text)
{
cell.Value = formula.Resolve();
}
else
{
cell.FormulaR1C1 = formula.Resolve();
}
}
}
#endregion
#region add column headers
if (Table.ShowColumnHeaders == YesNo.Yes)
{
if (hasTopAggregates)
{
y++;
}
foreach (var columnHeader in columnHeaders)
{
var cell = worksheet.GetRangeFromModel(y, columnHeader);
cell.Merge = true;
cell.StyleName = columnHeader.Style ?? StyleModel.NameOfDefaultStyle;
cell.Merge = columnHeader.Show == YesNo.Yes;
cell.Value = columnHeader.Show == YesNo.Yes ? columnHeader.Text : string.Empty;
worksheet.AddColumnGroupFromModel(columnHeader);
}
}
#endregion
#region add headers
if (hasColumnheaders)
{
y++;
}
var fieldHeaders = items.GetRange(YesNo.Yes).ToList();
var hasFieldHeaders = fieldHeaders.Any();
foreach (var field in fieldHeaders)
{
var header = field.Header;
var column = items.IndexOf(field);
var cell = worksheet.Cells[y, x + column];
cell.Value = field.Alias;
cell.StyleName = header.Style ?? StyleModel.NameOfDefaultStyle;
var showHyperLink = header.HyperLink.Show == YesNo.Yes;
if (!showHyperLink)
{
continue;
}
string tooltip;
if (header.HyperLink.Tooltip.Equals(KnownHyperLinkTooltip.FieldName.ToString()))
{
tooltip = BaseDataFieldModel.GetFieldNameFrom(header.Parent);
}
else if (header.HyperLink.Tooltip.Equals(KnownHyperLinkTooltip.FieldAlias.ToString()))
{
tooltip = header.Parent.Alias;
}
else
{
tooltip = header.HyperLink.Tooltip;
}
var hyperLinkStyle = header.Style ?? StyleModel.NameOfDefaultStyle;
if (!header.HyperLink.Style.Equals("Current"))
{
hyperLinkStyle = header.HyperLink.Style;
}
var hyperLinkType = header.HyperLink.Current.GetType().Name;
switch (hyperLinkType)
{
case "WebHyperLink":
var webHyperLink = (WebHyperLink)header.HyperLink.Current;
cell.Hyperlink = new ExcelHyperLink(webHyperLink.Address); //, tooltip);
//cell.StyleName = hyperLinkStyle;
break;
}
}
#endregion
#region add data
if (hasFieldHeaders)
{
y++;
}
var conditions = Table.Conditions.Items.ToList();
var hasConditions = conditions.Any();
var fieldDictionary = new Dictionary <BaseDataFieldModel, int>();
for (var row = 0; row < rowsCount; row++)
{
var rowData = rows[row];
Service.SetCurrentRow(row);
for (var col = 0; col < items.Count; col++)
{
Service.SetCurrentCol(col);
var field = items[col];
field.DataSource = rowData;
Service.SetCurrentField(field);
var value = field.Value.GetValue(Provider.SpecialChars);
var valueLenght = value.FormattedValue.Length;
var cell = worksheet.Cells[y + row, x + col];
cell.Value = value.Value;
cell.AddErrorComment(value);
if (hasConditions)
{
foreach (var condition in conditions)
{
if (condition.Active == YesNo.No)
{
continue;
}
var styleToApply = condition.Apply();
if (styleToApply == null)
{
continue;
}
cell.StyleName = styleToApply;
}
}
else
{
cell.StyleName = row.IsOdd()
? $"{value.Style.Name}_Alternate"
: value.Style.Name ?? StyleModel.NameOfDefaultStyle;
}
cell.Style.WrapText = field.FieldType == KnownFieldType.Group;
if (!fieldDictionary.ContainsKey(field))
{
fieldDictionary.Add(field, valueLenght);
}
else
{
var entry = fieldDictionary[field];
if (valueLenght > entry)
{
fieldDictionary[field] = valueLenght;
}
}
}
}
#endregion
#region add bottom aggregates
var fieldsWithBottomAggregates = items.GetRange(KnownAggregateLocation.Bottom).ToList();
var hasBottomAggregates = fieldsWithBottomAggregates.Any();
if (hasBottomAggregates)
{
foreach (var field in fieldsWithBottomAggregates)
{
var aggregate = field.Aggregate;
var formula = new ExcelFormulaResolver(aggregate)
{
EndRow = -1,
StartRow = -rowsCount,
HasAutoFilter = Table.AutoFilter,
};
var column = items.IndexOf(field);
var cell = worksheet.Cells[y + rowsCount, x + column];
cell.StyleName = aggregate.Style ?? StyleModel.NameOfDefaultStyle;
var type = aggregate.AggregateType;
if (type == KnownAggregateType.Text)
{
cell.Value = formula.Resolve();
}
else
{
cell.FormulaR1C1 = formula.Resolve();
}
}
}
#endregion
#region add blocklines
var blocklines = Provider.Input.Model.BlockLines;
var hasBlockLines = blocklines.Any();
if (hasBlockLines)
{
foreach (var blockline in blocklines)
{
if (blockline.Show == YesNo.No)
{
continue;
}
var blocklineLocation = blockline.Location.LocationType.Equals(KnownElementLocation.ByAlignment)
? new Point(1, 1)
: ((CoordenatesModel)blockline.Location.Mode).TableCoordenates;
var dy = 0;
var blocklineY = blocklineLocation.Y;
var blocklineX = blocklineLocation.X;
var keyLines = blockline.Items.Keys;
foreach (var keyLine in keyLines)
{
var line = Resources.Lines.GetBy(keyLine);
if (line.Show == YesNo.No)
{
continue;
}
var times = line.Repeat == 0 ? 1 : line.Repeat;
for (var i = 1; i <= times; i++)
{
int merge;
var dx = 0;
ExcelRange cell;
var lineType = line.LineType;
switch (lineType)
{
case KnownLineType.EmptyLine:
var emptyLine = (EmptyLineModel)line;
merge = emptyLine.Merge;
if (merge > 0)
{
var range = ExcelCellBase.GetAddress(blocklineY + dy, blocklineX, blocklineY + dy, blocklineX + merge - 1);
cell = worksheet.Cells[range];
cell.Merge = true;
}
else
{
cell = worksheet.Cells[blocklineY + dy, blocklineX];
}
cell.StyleName = line.Style;
break;
case KnownLineType.TextLine:
var textLine = (TextLineModel)line;
var itemss = textLine.Items;
foreach (var item in itemss)
{
if (item.Show == YesNo.No)
{
continue;
}
merge = item.Merge;
if (merge > 0)
{
var range =
ExcelCellBase
.GetAddress(
blocklineY + dy,
blocklineX + dx,
blocklineY + dy,
blocklineX + dx + merge - 1);
cell = worksheet.Cells[range];
cell.Merge = true;
}
else
{
cell = worksheet.Cells[blocklineY + dy, blocklineX + dx];
}
cell.StyleName =
string.IsNullOrEmpty(item.Style)
? (item.Owner.Style ?? StyleModel.Default.Name)
: item.Style;
var text = item.Value;
cell.Value = text;
dx++;
if (merge > 0)
{
dx += merge - 1;
}
}
break;
}
dy++;
}
}
}
}
#endregion
#region add autofilter
if (Table.AutoFilter == YesNo.Yes)
{
if (hasFieldHeaders)
{
worksheet.Cells[y - 1, x, y - 1, x + items.Count - 1].AutoFilter = true;
}
else
{
worksheet.Cells[worksheet.Dimension.Address].AutoFilter = true;
}
}
#endregion
#region autofitcolumns?
if (Table.AutoFitColumns == YesNo.Yes)
{
try
{
worksheet.Cells.AutoFitColumns(0);
}
catch (Exception)
{
// ignored
}
worksheet.AutoFitGroupColumns(fieldDictionary, this);
}
#endregion
#region add charts
var genericCharts = Table.Charts;
foreach (var genericChart in genericCharts)
{
if (genericChart.Show == YesNo.No)
{
continue;
}
if (genericChart.ChartType == KnownChartTypes.ChartType)
{
var chart = (ChartModel)genericChart;
ExcelChart mainchart = null;
var plots = chart.Plots;
foreach (var plot in plots)
{
// Calculate y-coordenates
var tableLocation = TableLocation;
tableLocation.Offset(-1, -1);
var dataSerieY = tableLocation.Y;
if (hasTopAggregates)
{
dataSerieY++;
}
if (hasColumnheaders)
{
dataSerieY++;
}
if (hasFieldHeaders)
{
dataSerieY++;
}
// Series data, name
var series = plot.Series;
foreach (var serie in series)
{
var item = items[serie.Field];
if (item == null)
{
continue;
}
var axis = items[serie.Axis];
if (axis == null)
{
continue;
}
// Create chart
ExcelChart workchart;
if (plot.UseSecondaryAxis.Equals(YesNo.No))
{
if (mainchart == null)
{
mainchart = worksheet.Drawings.AddChart(serie.Field, serie.ChartType.ToEppChartType());
mainchart.Name = plot.Name;
}
workchart = mainchart;
}
else
{
workchart = mainchart.PlotArea.ChartTypes.Add(serie.ChartType.ToEppChartType());
workchart.UseSecondaryAxis = true;
workchart.XAxis.Deleted = false;
}
var axisColumnIndex = tableLocation.X + items.IndexOf(axis) + 1;
var fieldColumnIndex = tableLocation.X + items.IndexOf(item) + 1;
var sr = workchart.Series.Add(
ExcelCellBase.GetAddress(dataSerieY + 1, fieldColumnIndex, rowsCount + dataSerieY, fieldColumnIndex),
ExcelCellBase.GetAddress(dataSerieY + 1, axisColumnIndex, rowsCount + dataSerieY, axisColumnIndex));
sr.Header = serie.Name;
}
}
mainchart.FormatFromModel(chart);
}
else
{
var miniChart = (MiniChartModel)genericChart;
// Calculate y-coordenates
var tableLocation = TableLocation;
tableLocation.Offset(-1, -1);
var dataSerieY = tableLocation.Y;
if (hasTopAggregates)
{
dataSerieY++;
}
if (hasColumnheaders)
{
dataSerieY++;
}
if (hasFieldHeaders)
{
dataSerieY++;
}
var item = items[miniChart.Field];
if (item == null)
{
continue;
}
var locationRange = ExcelCellBase.GetAddress(18, 2);
var fieldColumnIndex = tableLocation.X + items.IndexOf(item) + 1;
var dataRange = ExcelCellBase.GetAddress(dataSerieY + 1, fieldColumnIndex, rowsCount + dataSerieY, fieldColumnIndex);
var sparkline = worksheet.SparklineGroups.Add(
miniChart.Type.Active.ToEppeSparklineType(),
worksheet.Cells[locationRange],
worksheet.Cells[dataRange]);
sparkline.DisplayHidden = miniChart.DisplayHidden == YesNo.Yes;
sparkline.ColorSeries.SetColor(miniChart.Type.Column.Serie.GetColor());
sparkline.DisplayEmptyCellsAs = miniChart.EmptyValueAs.ToEppeDisplayBlanksAs();
// Axes
// Horizontal axis
sparkline.RightToLeft = miniChart.Axes.Horizontal.RightToLeft == YesNo.Yes;
if (miniChart.Axes.Horizontal.Show == YesNo.Yes)
{
sparkline.DisplayXAxis = true;
sparkline.ColorAxis.SetColor(miniChart.Axes.Horizontal.GetColor());
}
sparkline.DateAxisRange = null;
var isHorizontalDateAxis = miniChart.Axes.Horizontal.IsDateAxis;
if (isHorizontalDateAxis)
{
var horizontalAxisField = miniChart.Axes.Horizontal.GetAxisField();
if (horizontalAxisField != null)
{
var dateFielColumnIndex = tableLocation.X + items.IndexOf(horizontalAxisField) + 1;
var dateFieldRange = ExcelCellBase.GetAddress(dataSerieY + 1, dateFielColumnIndex, rowsCount + dataSerieY, dateFielColumnIndex);
sparkline.DateAxisRange = worksheet.Cells[dateFieldRange];
}
}
// Vertical axis
var maxVerticalAxisIsAuto = miniChart.Axes.Vertical.Max.Equals("Automatic");
sparkline.MaxAxisType = maxVerticalAxisIsAuto
? eSparklineAxisMinMax.Individual
: eSparklineAxisMinMax.Custom;
var minVerticalAxisIsAuto = miniChart.Axes.Vertical.Min.Equals("Automatic");
sparkline.MinAxisType = minVerticalAxisIsAuto
? eSparklineAxisMinMax.Individual
: eSparklineAxisMinMax.Custom;
if (!maxVerticalAxisIsAuto)
{
sparkline.ManualMax = double.Parse(miniChart.Axes.Vertical.Max);
}
if (!minVerticalAxisIsAuto)
{
sparkline.ManualMin = double.Parse(miniChart.Axes.Vertical.Min);
}
// Points
switch (miniChart.Type.Active)
{
case KnownMiniChartType.Column:
if (!miniChart.Type.Column.Points.Low.IsDefault)
{
sparkline.Low = true;
sparkline.ColorLow.SetColor(miniChart.Type.Column.Points.Low.GetColor());
}
if (!miniChart.Type.Column.Points.First.IsDefault)
{
sparkline.First = true;
sparkline.ColorFirst.SetColor(miniChart.Type.Column.Points.First.GetColor());
}
if (!miniChart.Type.Column.Points.High.IsDefault)
{
sparkline.High = true;
sparkline.ColorHigh.SetColor(miniChart.Type.Column.Points.High.GetColor());
}
if (!miniChart.Type.Column.Points.Last.IsDefault)
{
sparkline.Last = true;
sparkline.ColorLast.SetColor(miniChart.Type.Column.Points.Last.GetColor());
}
if (!miniChart.Type.Column.Points.Negative.IsDefault)
{
sparkline.Negative = true;
sparkline.ColorNegative.SetColor(miniChart.Type.Column.Points.Negative.GetColor());
}
break;
case KnownMiniChartType.Line:
sparkline.LineWidth = double.Parse(miniChart.Type.Line.Serie.Width);
if (!miniChart.Type.Line.Points.Low.IsDefault)
{
sparkline.Low = true;
sparkline.ColorLow.SetColor(miniChart.Type.Line.Points.Low.GetColor());
}
if (!miniChart.Type.Line.Points.First.IsDefault)
{
sparkline.First = true;
sparkline.ColorFirst.SetColor(miniChart.Type.Line.Points.First.GetColor());
}
if (!miniChart.Type.Line.Points.High.IsDefault)
{
sparkline.High = true;
sparkline.ColorHigh.SetColor(miniChart.Type.Line.Points.High.GetColor());
}
if (!miniChart.Type.Line.Points.Last.IsDefault)
{
sparkline.Last = true;
sparkline.ColorLast.SetColor(miniChart.Type.Line.Points.Last.GetColor());
}
if (!miniChart.Type.Line.Points.Negative.IsDefault)
{
sparkline.Negative = true;
sparkline.ColorNegative.SetColor(miniChart.Type.Line.Points.Negative.GetColor());
}
if (!miniChart.Type.Line.Points.Markers.IsDefault)
{
sparkline.Markers = true;
sparkline.ColorNegative.SetColor(miniChart.Type.Line.Points.Markers.GetColor());
}
break;
case KnownMiniChartType.WinLoss:
if (!miniChart.Type.WinLoss.Points.Low.IsDefault)
{
sparkline.Low = true;
sparkline.ColorLow.SetColor(miniChart.Type.WinLoss.Points.Low.GetColor());
}
if (!miniChart.Type.WinLoss.Points.First.IsDefault)
{
sparkline.First = true;
sparkline.ColorFirst.SetColor(miniChart.Type.WinLoss.Points.First.GetColor());
}
if (!miniChart.Type.WinLoss.Points.High.IsDefault)
{
sparkline.High = true;
sparkline.ColorHigh.SetColor(miniChart.Type.WinLoss.Points.High.GetColor());
}
if (!miniChart.Type.WinLoss.Points.Last.IsDefault)
{
sparkline.Last = true;
sparkline.ColorLast.SetColor(miniChart.Type.WinLoss.Points.Last.GetColor());
}
if (!miniChart.Type.WinLoss.Points.Negative.IsDefault)
{
sparkline.Negative = true;
sparkline.ColorNegative.SetColor(miniChart.Type.WinLoss.Points.Negative.GetColor());
}
break;
}
}
}
#endregion
#region add document information
var document = Host.Document;
#region sets document view
worksheet.View.SetDocumentViewFromModel(document);
#endregion
#region sets document metadata
worksheet.Workbook.Properties.SetDocumentMetadataFromModel(document.Metadata);
#endregion
#region sets document header/footer
worksheet.HeaderFooter.SetDocumentHeaderFromModel(document.Header);
worksheet.HeaderFooter.SetDocumentFooterFromModel(document.Footer);
#endregion
#region sets page orientation, margins and size
var printAreaRangeY2 = location.Y;
var repeatRowsRangeY2 = printAreaRangeY2;
if (hasFieldHeaders)
{
printAreaRangeY2++;
}
if (hasTopAggregates)
{
printAreaRangeY2++;
repeatRowsRangeY2++;
}
if (hasBottomAggregates)
{
printAreaRangeY2++;
}
var repeatRowsRange = $"{1}:{repeatRowsRangeY2}";
var printAreaRange =
ExcelCellBase.GetAddress(
1,
1,
printAreaRangeY2 + rowsCount - 1,
location.X + items.Count - 1,
true);
worksheet.PrinterSettings.PaperSize = document.Size.ToEppPaperSize();
worksheet.PrinterSettings.Orientation = document.Orientation.ToEppOrientation();
worksheet.PrinterSettings.SetMarginsFromModel(document.Margins);
worksheet.PrinterSettings.PrintArea = worksheet.Cells[printAreaRange];
worksheet.PrinterSettings.RepeatRows = worksheet.Cells[repeatRowsRange];
#endregion
#endregion
#region add freeze panes
if (Host.Document.View == KnownDocumentView.Normal)
{
if (Table.FreezePanesPoint.X != 1 && Table.FreezePanesPoint.Y != 1)
{
worksheet.View.FreezePanes(Table.FreezePanesPoint.Y, Table.FreezePanesPoint.X);
}
}
#endregion
var range1 = worksheet.Cells["A1:H501"];
var wsPivot = worksheet.Workbook.Worksheets.Add("PivotSimple");
var pivotTable1 = wsPivot.PivotTables.Add(wsPivot.Cells["A1"], range1, "PerEmploee");
pivotTable1.RowFields.Add(pivotTable1.Fields[3]);
var dataField = pivotTable1.DataFields.Add(pivotTable1.Fields[5]);
dataField.Format = "#,##0";
pivotTable1.DataOnRows = true;
var chartp = wsPivot.Drawings.AddChart("PivotChart", eChartType.Pie, pivotTable1);
chartp.SetPosition(1, 0, 4, 0);
chartp.SetSize(600, 400);
//var hl = new ExcelHyperLink("Statistics!A1", "Statistics");
//hl.
//chartp.
//var wsPivot2 = pck.Workbook.Worksheets.Add("PivotDateGrp");
//var pivotTable2 = wsPivot2.PivotTables.Add(wsPivot2.Cells["A3"], dataRange, "PerEmploeeAndQuarter");
//pivotTable2.RowFields.Add(pivotTable2.Fields["Name"]);
////Add a rowfield
//var rowField = pivotTable2.RowFields.Add(pivotTable2.Fields["OrderDate"]);
////This is a date field so we want to group by Years and quaters. This will create one additional field for years.
//rowField.AddDateGrouping(eDateGroupBy.Years | eDateGroupBy.Quarters);
////Get the Quaters field and change the texts
//var quaterField = pivotTable2.Fields.GetDateGroupField(eDateGroupBy.Quarters);
//quaterField.Items[0].Text = "<"; //Values below min date, but we use auto so its not used
//quaterField.Items[1].Text = "Q1";
//quaterField.Items[2].Text = "Q2";
//quaterField.Items[3].Text = "Q3";
//quaterField.Items[4].Text = "Q4";
//quaterField.Items[5].Text = ">"; //Values above max date, but we use auto so its not used
////Add a pagefield
//var pageField = pivotTable2.PageFields.Add(pivotTable2.Fields["Title"]);
////Add the data fields and format them
//dataField = pivotTable2.DataFields.Add(pivotTable2.Fields["SubTotal"]);
//dataField.Format = "#,##0";
//dataField = pivotTable2.DataFields.Add(pivotTable2.Fields["Tax"]);
//dataField.Format = "#,##0";
//dataField = pivotTable2.DataFields.Add(pivotTable2.Fields["Freight"]);
//dataField.Format = "#,##0";
////We want the datafields to appear in columns
//pivotTable2.DataOnRows = false;
#region save
Result.Add(excel.GetAsByteArray());
#endregion
}
}
finally
{
stream?.Dispose();
}
}
/// <summary>
/// Adds aggregate to specified table from export model.
/// </summary>
/// <param name="table">Destination table for headers</param>
/// <param name="model">Export table model</param>
/// <param name="rows">Source data for calculate the aggregate</param>
/// <param name="specialChars">The special chars</param>
/// <param name="aggregateLocation">The aggregate location</param>
/// <returns>
/// table with
/// </returns>
public static PdfPTable AddAggregateByLocation(this PdfPTable table, TableModel model, XElement[] rows, char[] specialChars, KnownAggregateLocation aggregateLocation)
{
SentinelHelper.ArgumentNull(table);
SentinelHelper.ArgumentNull(model);
var hasAnyAggregate = model.Fields.Any(field => field.Aggregate.Show == YesNo.Yes && field.Aggregate.Location == aggregateLocation);
if (!hasAnyAggregate)
{
return(table);
}
var fix = model.Parent.Resources.Fixed;
var rowsCount = rows.Length;
var fields = model.Fields;
var attributes = rows.Attributes().ToList();
foreach (var field in fields)
{
var aggregate = field.Aggregate;
if (aggregate.AggregateType == KnownAggregateType.None)
{
continue;
}
var style = field.Aggregate.GetStyle();
if (field.Aggregate.Show == YesNo.Yes &&
field.Aggregate.Location == aggregateLocation)
{
var formula = new NonTabularFormulaResolver(field.Aggregate);
switch (field.FieldType)
{
#region Field: Field
case KnownFieldType.Field:
BaseDataFieldModel field1 = field;
formula.Data =
attributes.Where(
attr =>
attr.Name.LocalName.ToUpperInvariant()
.Equals(
BaseDataFieldModel.GetFieldNameFrom(field1).ToUpperInvariant()))
.Select(n => n.Value);
break;
#endregion
#region Field: Fixed
case KnownFieldType.Fixed:
var fixedFieldModel = (FixedFieldModel)field;
var piecesModel = fix[fixedFieldModel.Pieces];
BaseDataFieldModel field2 = field;
var dataList =
rows.Select(row => piecesModel.DataSource = row)
.Select(entries => piecesModel.ToDictionary())
.Select(entry => entry[BaseDataFieldModel.GetFieldNameFrom(field2)]);
formula.Data = dataList;
break;
#endregion
#region Field: Gap
case KnownFieldType.Gap:
var datalist = new List <string>();
for (var i = 0; i <= rowsCount - 1; i++)
{
datalist.Add(" ");
}
formula.Data = datalist;
break;
#endregion
#region Field: Group
case KnownFieldType.Group:
var groupFieldModel = (GroupFieldModel)field;
var groupDataList = new List <string>();
foreach (var row in rows)
{
groupFieldModel.DataSource = row;
var group = groupFieldModel.Value.GetValue(specialChars);
groupDataList.Add(@group.FormattedValue);
}
formula.Data = groupDataList;
break;
#endregion
}
table.AddCell(PdfHelper.CreateCell(style.Content.DataType.GetFormattedDataValue(formula.Resolve())));
}
else
{
var emptyStyle = StyleModel.Empty;
emptyStyle.Content = style.Content;
emptyStyle.Content.DataType = new TextDataTypeModel();
////emptyStyle.SetOwner(model.Styles);
table.AddCell(PdfHelper.CreateCell(style.Content.DataType.GetFormattedDataValue(string.Empty)));
}
}
return(table);
}
/// <summary>
/// Returns a reference to object which allow resolve the field aggregate.
/// </summary>
/// <param name="field">The field.</param>
/// <param name="rows">The rows.</param>
/// <returns>
/// A <see cref="T:iTin.Export.ComponentModel.NonTabularFormulaResolver"/> reference which allow resolve the field aggregate.
/// </returns>
private NonTabularFormulaResolver GetFormula(BaseDataFieldModel field, XElement[] rows)
{
var rowsCount = rows.Length;
var attributes = rows.ToList().Attributes();
var aggregate = field.Aggregate;
var formula = new NonTabularFormulaResolver(aggregate);
var name = BaseDataFieldModel.GetFieldNameFrom(field);
switch (field.FieldType)
{
#region Field: Field
case KnownFieldType.Field:
formula.Data = attributes.Where(attr => attr.Name.LocalName.ToUpperInvariant().Equals(name.ToUpperInvariant())).Select(n => n.Value);
break;
#endregion
#region Field: Fixed
case KnownFieldType.Fixed:
var @fixed = (FixedFieldModel)field;
var fixedItem = Fixed[@fixed.Pieces];
var dataList =
rows.Select(row => fixedItem.DataSource = row)
.Select(entries => fixedItem.ToDictionary())
.Select(entry => entry[name]);
formula.Data = dataList;
break;
#endregion
#region Field: Gap
case KnownFieldType.Gap:
var datalist = new List <string>();
for (var i = 0; i <= rowsCount - 1; i++)
{
datalist.Add(" ");
}
formula.Data = datalist;
break;
#endregion
#region Field: Group
case KnownFieldType.Group:
var group = (GroupFieldModel)field;
var values = new List <string>();
foreach (var row in rows)
{
group.DataSource = row;
var value = group.Value.GetValue(Provider.SpecialChars);
values.Add(value.FormattedValue);
}
formula.Data = values;
break;
#endregion
}
return(formula);
}
/// <inheritdoc />
/// <summary>
/// Generates output in Office Open Xml Excel format.
/// </summary>
protected override void Execute()
{
MemoryStream stream = null;
try
{
stream = new MemoryStream();
using (var excel = new ExcelPackage(stream))
{
#region destroy stream
stream = null;
#endregion
#region initialize
var items = Table.Fields;
var location = TableLocation;
var x = location.X;
var y = location.Y;
#endregion
#region get target data
var rows = Service.RawDataFiltered;
#endregion
#region add worksheet
var worksheet = excel.Workbook.Worksheets.Add(WorkSheetName);
//excel.Workbook.Worksheets.Add("ssss");
worksheet.View.ShowGridLines = Table.ShowGridLines == YesNo.Yes;
#endregion
#region sets fields's width
if (Table.AutoFitColumns == YesNo.No)
{
for (var col = 0; col < items.Count; col++)
{
var field = items[col];
var width = field.CalculateWidthValue();
var column = worksheet.Column(x + col);
var isBestFit = width.Equals(double.NaN);
if (isBestFit)
{
column.BestFit = true;
}
else
{
column.Width = width;
}
}
}
#endregion
#region add styles
excel.Workbook.Styles.CreateFromModel(Resources.Styles);
#endregion
#region has logo?
worksheet.AppendLogo(Table.Logo);
#endregion
#region add top aggregates
var rowsCount = rows.Length;
var columnHeaders = Table.Headers;
var hasColumnheaders = columnHeaders.Any();
var topAggregates = items.GetRange(KnownAggregateLocation.Top).ToList();
var hasTopAggregates = topAggregates.Any();
if (hasTopAggregates)
{
if (Table.ShowDataValues == YesNo.Yes)
{
foreach (var field in topAggregates)
{
var aggregate = field.Aggregate;
var formula = new ExcelFormulaResolver(aggregate)
{
StartRow = hasColumnheaders && Table.ShowColumnHeaders == YesNo.Yes ? 3 : 2,
EndRow = hasColumnheaders && Table.ShowColumnHeaders == YesNo.Yes
? rowsCount + 2
: rowsCount + 1,
HasAutoFilter = Table.AutoFilter,
};
var column = items.IndexOf(field);
var cell = worksheet.Cells[y, x + column];
cell.StyleName = aggregate.Style ?? StyleModel.NameOfDefaultStyle;
var type = aggregate.AggregateType;
if (type == KnownAggregateType.Text)
{
cell.Value = formula.Resolve();
}
else
{
cell.FormulaR1C1 = formula.Resolve();
}
}
}
}
#endregion
#region add column headers
if (Table.ShowColumnHeaders == YesNo.Yes)
{
if (hasTopAggregates)
{
y++;
}
foreach (var columnHeader in columnHeaders)
{
var cell = worksheet.GetRangeFromModel(y, columnHeader);
cell.Merge = true;
cell.StyleName = columnHeader.Style ?? StyleModel.NameOfDefaultStyle;
cell.Merge = columnHeader.Show == YesNo.Yes;
cell.Value = columnHeader.Show == YesNo.Yes ? columnHeader.Text : string.Empty;
worksheet.AddColumnGroupFromModel(columnHeader);
}
}
#endregion
#region add headers
if (hasColumnheaders)
{
y++;
}
var fieldHeaders = items.GetRange(YesNo.Yes).ToList();
var hasFieldHeaders = fieldHeaders.Any();
foreach (var field in fieldHeaders)
{
var header = field.Header;
var column = items.IndexOf(field);
var cell = worksheet.Cells[y, x + column];
cell.Value = field.Alias;
cell.StyleName = header.Style ?? StyleModel.NameOfDefaultStyle;
var showHyperLink = header.HyperLink.Show == YesNo.Yes;
if (!showHyperLink)
{
continue;
}
string tooltip;
if (header.HyperLink.Tooltip.Equals(KnownHyperLinkTooltip.FieldName.ToString()))
{
tooltip = BaseDataFieldModel.GetFieldNameFrom(header.Parent);
}
else if (header.HyperLink.Tooltip.Equals(KnownHyperLinkTooltip.FieldAlias.ToString()))
{
tooltip = header.Parent.Alias;
}
else
{
tooltip = header.HyperLink.Tooltip;
}
var hyperLinkStyle = header.Style ?? StyleModel.NameOfDefaultStyle;
if (!header.HyperLink.Style.Equals("Current"))
{
hyperLinkStyle = header.HyperLink.Style;
}
var hyperLinkType = header.HyperLink.Current.GetType().Name;
switch (hyperLinkType)
{
case "WebHyperLink":
var webHyperLink = (WebHyperLink)header.HyperLink.Current;
cell.Hyperlink = new ExcelHyperLink(@"\\Mac\Dropbox\PhotoGrid_1513687137832.jpg"); // ssss!A1", "nando");
cell.StyleName = hyperLinkStyle;
break;
}
}
#endregion
#region add data
if (hasFieldHeaders)
{
y++;
}
var fieldDictionary = new Dictionary <BaseDataFieldModel, int>();
if (Table.ShowDataValues == YesNo.Yes)
{
for (var row = 0; row < rowsCount; row++)
{
var rowData = rows[row];
Service.SetCurrentRow(row);
for (var col = 0; col < items.Count; col++)
{
Service.SetCurrentCol(col);
var field = items[col];
field.DataSource = rowData;
Service.SetCurrentField(field);
var value = field.Value.GetValue(Provider.SpecialChars);
var valueLenght = value.FormattedValue.Length;
var cell = worksheet.Cells[y + row, x + col];
cell.Value = value.Value;
cell.AddErrorComment(value);
cell.Style.WrapText = field.FieldType == KnownFieldType.Group;
cell.StyleName = row.IsOdd()
? $"{value.Style.Name}_Alternate"
: value.Style.Name ?? StyleModel.NameOfDefaultStyle;
if (!fieldDictionary.ContainsKey(field))
{
fieldDictionary.Add(field, valueLenght);
}
else
{
var entry = fieldDictionary[field];
if (valueLenght > entry)
{
fieldDictionary[field] = valueLenght;
}
}
}
}
}
#endregion
#region add bottom aggregates
var fieldsWithBottomAggregates = items.GetRange(KnownAggregateLocation.Bottom).ToList();
var hasBottomAggregates = fieldsWithBottomAggregates.Any();
if (Table.ShowDataValues == YesNo.Yes)
{
if (hasBottomAggregates)
{
foreach (var field in fieldsWithBottomAggregates)
{
var aggregate = field.Aggregate;
var formula = new ExcelFormulaResolver(aggregate)
{
EndRow = -1,
StartRow = -rowsCount,
HasAutoFilter = Table.AutoFilter,
};
var column = items.IndexOf(field);
var cell = worksheet.Cells[y + rowsCount, x + column];
cell.StyleName = aggregate.Style ?? StyleModel.NameOfDefaultStyle;
var type = aggregate.AggregateType;
if (type == KnownAggregateType.Text)
{
cell.Value = formula.Resolve();
}
else
{
cell.FormulaR1C1 = formula.Resolve();
}
}
}
}
#endregion
#region conditions
var cols = items.Count - 1;
var conditions = Table.Conditions.Items.ToList();
foreach (var condition in conditions)
{
if (condition.Active == YesNo.No)
{
continue;
}
var field = items.GetBy(condition.Field);
var col = items.IndexOf(field);
Service.SetCurrentCol(col);
for (var row = 0; row < rowsCount; row++)
{
Service.SetCurrentRow(row);
field.DataSource = rows[row];
Service.SetCurrentField(field);
var conditionResult = condition.Evaluate();
if (!conditionResult.CanApply)
{
continue;
}
var cell = condition.EntireRow == YesNo.Yes
? worksheet.Cells[y + row, x, y + row, x + cols]
: worksheet.Cells[y + row, x + col];
cell.StyleName = conditionResult.Style;
}
}
#endregion
#region add blocklines
var blocklines = Provider.Input.Model.BlockLines;
var hasBlockLines = blocklines.Any();
if (hasBlockLines)
{
foreach (var blockline in blocklines)
{
if (blockline.Show == YesNo.No)
{
continue;
}
var blocklineLocation = blockline.Location.LocationType.Equals(KnownElementLocation.ByAlignment)
? new Point(1, 1)
: ((CoordenatesModel)blockline.Location.Mode).TableCoordenates;
var dy = 0;
var blocklineY = blocklineLocation.Y;
var blocklineX = blocklineLocation.X;
var keyLines = blockline.Items.Keys;
foreach (var keyLine in keyLines)
{
var line = Resources.Lines.GetBy(keyLine);
if (line.Show == YesNo.No)
{
continue;
}
var times = line.Repeat == 0 ? 1 : line.Repeat;
for (var i = 1; i <= times; i++)
{
int merge;
var dx = 0;
ExcelRange cell;
var lineType = line.LineType;
switch (lineType)
{
case KnownLineType.EmptyLine:
var emptyLine = (EmptyLineModel)line;
merge = emptyLine.Merge;
if (merge > 0)
{
var range = ExcelCellBase.GetAddress(blocklineY + dy, blocklineX, blocklineY + dy, blocklineX + merge - 1);
cell = worksheet.Cells[range];
cell.Merge = true;
}
else
{
cell = worksheet.Cells[blocklineY + dy, blocklineX];
}
cell.StyleName = line.Style;
break;
case KnownLineType.TextLine:
var textLine = (TextLineModel)line;
var lineItems = textLine.Items;
foreach (var item in lineItems)
{
if (item.Show == YesNo.No)
{
continue;
}
merge = item.Merge;
if (merge > 0)
{
var range =
ExcelCellBase
.GetAddress(
blocklineY + dy,
blocklineX + dx,
blocklineY + dy,
blocklineX + dx + merge - 1);
cell = worksheet.Cells[range];
cell.Merge = true;
}
else
{
cell = worksheet.Cells[blocklineY + dy, blocklineX + dx];
}
cell.StyleName =
string.IsNullOrEmpty(item.Style)
? (item.Owner.Style ?? StyleModel.Default.Name)
: item.Style;
var text = item.Value;
cell.Value = text;
dx++;
if (merge > 0)
{
dx += merge - 1;
}
}
break;
}
dy++;
}
}
}
}
#endregion
#region add autofilter
if (Table.AutoFilter == YesNo.Yes)
{
if (hasFieldHeaders)
{
worksheet.Cells[y - 1, x, y - 1, x + items.Count - 1].AutoFilter = true;
}
else
{
worksheet.Cells[worksheet.Dimension.Address].AutoFilter = true;
}
}
#endregion
#region autofitcolumns
if (Table.AutoFitColumns == YesNo.Yes)
{
try
{
worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();
}
catch
{
worksheet.AutoFitGroupColumns(fieldDictionary, this);
}
}
#endregion
#region add charts
var genericCharts = Table.Charts;
foreach (var genericChart in genericCharts)
{
if (genericChart.Show == YesNo.No)
{
continue;
}
if (genericChart.ChartType == KnownChartTypes.ChartType)
{
var chart = (ChartModel)genericChart;
ExcelChart mainchart = null;
var plots = chart.Plots;
foreach (var plot in plots)
{
// Calculate y-coordenates
var tableLocation = TableLocation;
tableLocation.Offset(-1, -1);
var dataSerieY = tableLocation.Y;
if (hasTopAggregates)
{
dataSerieY++;
}
if (hasColumnheaders)
{
dataSerieY++;
}
if (hasFieldHeaders)
{
dataSerieY++;
}
// Series data, name
var series = plot.Series;
foreach (var serie in series)
{
var item = items[serie.Field];
if (item == null)
{
continue;
}
var axis = items[serie.Axis];
if (axis == null)
{
continue;
}
// Create chart
ExcelChart workchart;
if (plot.UseSecondaryAxis.Equals(YesNo.No))
{
if (mainchart == null)
{
mainchart = worksheet.Drawings.AddChart(serie.Field, serie.ChartType.ToEppChartType());
mainchart.Name = plot.Name;
}
workchart = mainchart;
}
else
{
workchart = mainchart.PlotArea.ChartTypes.Add(serie.ChartType.ToEppChartType());
workchart.UseSecondaryAxis = true;
workchart.XAxis.Deleted = false;
}
var axisColumnIndex = tableLocation.X + items.IndexOf(axis) + 1;
var fieldColumnIndex = tableLocation.X + items.IndexOf(item) + 1;
var sr = workchart.Series.Add(
ExcelCellBase.GetAddress(dataSerieY + 1, fieldColumnIndex, rowsCount + dataSerieY, fieldColumnIndex),
ExcelCellBase.GetAddress(dataSerieY + 1, axisColumnIndex, rowsCount + dataSerieY, axisColumnIndex));
sr.Header = serie.Name;
}
}
mainchart.FormatFromModel(chart);
}
else
{
var miniChart = (MiniChartModel)genericChart;
// Calculate y-coordenates
var tableLocation = TableLocation;
tableLocation.Offset(-1, -1);
var dataSerieY = tableLocation.Y;
if (hasTopAggregates)
{
dataSerieY++;
}
if (hasColumnheaders)
{
dataSerieY++;
}
if (hasFieldHeaders)
{
dataSerieY++;
}
if (hasBottomAggregates)
{
dataSerieY++;
}
var item = items[miniChart.Field];
if (item == null)
{
continue;
}
Point chartOffset = Point.Empty;
Point chartLocation = Point.Empty;
var locationModel = miniChart.Location;
switch (locationModel.LocationType)
{
case KnownMiniChartElementPosition.ByColumn:
var byColumnMode = (ByColumnLocationModel)locationModel.Mode;
chartOffset = new Point(0, byColumnMode.Location == KnownMiniChartColumnLocation.Bottom ? byColumnMode.Offset : -byColumnMode.Offset);
chartLocation = new Point(chartOffset.X, byColumnMode.Location == KnownMiniChartColumnLocation.Bottom ? rowsCount + dataSerieY + chartOffset.Y + 1 : tableLocation.Y + chartOffset.Y);
break;
case KnownMiniChartElementPosition.ByCoordenates:
chartLocation = ((CoordenatesModel)locationModel.Mode).TableCoordenates;
break;
}
var fieldColumnIndex = tableLocation.X + items.IndexOf(item) + 1;
var dataRange = ExcelCellBase.GetAddress(dataSerieY + 1, fieldColumnIndex, rowsCount + dataSerieY, fieldColumnIndex);
var locationRange = ExcelCellBase.GetAddress(chartLocation.Y < 1 ? 1: chartLocation.Y, locationModel.IsAbosulte ? chartLocation.X : fieldColumnIndex + chartLocation.X);
// Apply Cell size
if (miniChart.CellSize.Width != -1)
{
var minichartColumn = locationModel.IsAbosulte ? chartOffset.X : fieldColumnIndex + chartOffset.X;
worksheet.Column(minichartColumn).Width = miniChart.CellSize.Width;
}
if (miniChart.CellSize.Height != -1)
{
var minichartRow = chartLocation.Y < 1 ? 1 : chartLocation.Y;
worksheet.Row(minichartRow).Height = miniChart.CellSize.Height;
}
// Create Minichart
var sparkline = worksheet.SparklineGroups.Add(
miniChart.Type.Active.ToEppeSparklineType(),
worksheet.Cells[locationRange],
worksheet.Cells[dataRange]);
sparkline.DisplayHidden = miniChart.DisplayHidden == YesNo.Yes;
sparkline.ColorSeries.SetColor(miniChart.Type.GetMiniChartSerieColor());
sparkline.DisplayEmptyCellsAs = miniChart.EmptyValueAs.ToEppeDisplayBlanksAs();
// Axes
// Horizontal axis
sparkline.DateAxisRange = null;
var isHorizontalDateAxis = miniChart.Axes.Horizontal.IsDateAxis;
if (isHorizontalDateAxis)
{
var horizontalAxisField = miniChart.Axes.Horizontal.GetAxisField();
if (horizontalAxisField != null)
{
var dateFielColumnIndex = tableLocation.X + items.IndexOf(horizontalAxisField) + 1;
var dateFieldRange = ExcelCellBase.GetAddress(dataSerieY + 1, dateFielColumnIndex, rowsCount + dataSerieY, dateFielColumnIndex);
sparkline.DateAxisRange = worksheet.Cells[dateFieldRange];
}
}
sparkline.RightToLeft = miniChart.Axes.Horizontal.RightToLeft == YesNo.Yes;
if (miniChart.Axes.Horizontal.Show == YesNo.Yes)
{
sparkline.DisplayXAxis = true;
sparkline.ColorAxis.SetColor(miniChart.Axes.Horizontal.GetColor());
}
// Vertical axis
var maxVerticalAxisIsAuto = miniChart.Axes.Vertical.Max.Equals("Automatic");
sparkline.MaxAxisType = maxVerticalAxisIsAuto
? eSparklineAxisMinMax.Individual
: eSparklineAxisMinMax.Custom;
var minVerticalAxisIsAuto = miniChart.Axes.Vertical.Min.Equals("Automatic");
sparkline.MinAxisType = minVerticalAxisIsAuto
? eSparklineAxisMinMax.Individual
: eSparklineAxisMinMax.Custom;
if (!maxVerticalAxisIsAuto)
{
sparkline.ManualMax = double.Parse(miniChart.Axes.Vertical.Max);
}
if (!minVerticalAxisIsAuto)
{
sparkline.ManualMin = double.Parse(miniChart.Axes.Vertical.Min);
}
// Points
switch (miniChart.Type.Active)
{
case KnownMiniChartType.Column:
if (!miniChart.Type.Column.Points.Low.IsDefault)
{
sparkline.Low = true;
sparkline.ColorLow.SetColor(miniChart.Type.Column.Points.Low.GetColor());
}
if (!miniChart.Type.Column.Points.First.IsDefault)
{
sparkline.First = true;
sparkline.ColorFirst.SetColor(miniChart.Type.Column.Points.First.GetColor());
}
if (!miniChart.Type.Column.Points.High.IsDefault)
{
sparkline.High = true;
sparkline.ColorHigh.SetColor(miniChart.Type.Column.Points.High.GetColor());
}
if (!miniChart.Type.Column.Points.Last.IsDefault)
{
sparkline.Last = true;
sparkline.ColorLast.SetColor(miniChart.Type.Column.Points.Last.GetColor());
}
if (!miniChart.Type.Column.Points.Negative.IsDefault)
{
sparkline.Negative = true;
sparkline.ColorNegative.SetColor(miniChart.Type.Column.Points.Negative.GetColor());
}
break;
case KnownMiniChartType.Line:
sparkline.LineWidth = double.Parse(miniChart.Type.Line.Serie.Width);
if (!miniChart.Type.Line.Points.Low.IsDefault)
{
sparkline.Low = true;
sparkline.ColorLow.SetColor(miniChart.Type.Line.Points.Low.GetColor());
}
if (!miniChart.Type.Line.Points.First.IsDefault)
{
sparkline.First = true;
sparkline.ColorFirst.SetColor(miniChart.Type.Line.Points.First.GetColor());
}
if (!miniChart.Type.Line.Points.High.IsDefault)
{
sparkline.High = true;
sparkline.ColorHigh.SetColor(miniChart.Type.Line.Points.High.GetColor());
}
if (!miniChart.Type.Line.Points.Last.IsDefault)
{
sparkline.Last = true;
sparkline.ColorLast.SetColor(miniChart.Type.Line.Points.Last.GetColor());
}
if (!miniChart.Type.Line.Points.Negative.IsDefault)
{
sparkline.Negative = true;
sparkline.ColorNegative.SetColor(miniChart.Type.Line.Points.Negative.GetColor());
}
if (!miniChart.Type.Line.Points.Markers.IsDefault)
{
sparkline.Markers = true;
sparkline.ColorNegative.SetColor(miniChart.Type.Line.Points.Markers.GetColor());
}
break;
case KnownMiniChartType.WinLoss:
if (!miniChart.Type.WinLoss.Points.Low.IsDefault)
{
sparkline.Low = true;
sparkline.ColorLow.SetColor(miniChart.Type.WinLoss.Points.Low.GetColor());
}
if (!miniChart.Type.WinLoss.Points.First.IsDefault)
{
sparkline.First = true;
sparkline.ColorFirst.SetColor(miniChart.Type.WinLoss.Points.First.GetColor());
}
if (!miniChart.Type.WinLoss.Points.High.IsDefault)
{
sparkline.High = true;
sparkline.ColorHigh.SetColor(miniChart.Type.WinLoss.Points.High.GetColor());
}
if (!miniChart.Type.WinLoss.Points.Last.IsDefault)
{
sparkline.Last = true;
sparkline.ColorLast.SetColor(miniChart.Type.WinLoss.Points.Last.GetColor());
}
if (!miniChart.Type.WinLoss.Points.Negative.IsDefault)
{
sparkline.Negative = true;
sparkline.ColorNegative.SetColor(miniChart.Type.WinLoss.Points.Negative.GetColor());
}
break;
}
}
}
#endregion
#region add document information
var document = Host.Document;
#region sets document view
worksheet.View.SetDocumentViewFromModel(document);
#endregion
#region sets document metadata
worksheet.Workbook.Properties.SetDocumentMetadataFromModel(document.Metadata);
#endregion
#region sets document header/footer
worksheet.HeaderFooter.SetDocumentHeaderFromModel(document.Header);
worksheet.HeaderFooter.SetDocumentFooterFromModel(document.Footer);
#endregion
#region sets page orientation, margins and size
var printAreaRangeY2 = location.Y;
var repeatRowsRangeY2 = printAreaRangeY2;
if (hasFieldHeaders)
{
printAreaRangeY2++;
}
if (hasTopAggregates)
{
printAreaRangeY2++;
repeatRowsRangeY2++;
}
if (hasBottomAggregates)
{
printAreaRangeY2++;
}
var repeatRowsRange = $"{1}:{repeatRowsRangeY2}";
var printAreaRange =
ExcelCellBase.GetAddress(
1,
1,
printAreaRangeY2 + rowsCount - 1,
location.X + items.Count - 1,
true);
worksheet.PrinterSettings.PaperSize = document.Size.ToEppPaperSize();
worksheet.PrinterSettings.Orientation = document.Orientation.ToEppOrientation();
worksheet.PrinterSettings.SetMarginsFromModel(document.Margins);
worksheet.PrinterSettings.PrintArea = worksheet.Cells[printAreaRange];
worksheet.PrinterSettings.RepeatRows = worksheet.Cells[repeatRowsRange];
#endregion
#endregion
#region add freeze panes
if (Host.Document.View == KnownDocumentView.Normal)
{
if (Table.FreezePanesPoint.X != 1 && Table.FreezePanesPoint.Y != 1)
{
worksheet.View.FreezePanes(Table.FreezePanesPoint.Y, Table.FreezePanesPoint.X);
}
}
#endregion
#region save
Result.Add(excel.GetAsByteArray());
#endregion
}
}
finally
{
stream?.Dispose();
}
}
/// <inheritdoc />
/// <summary>
/// Generates the output in tab-separated values format.
/// </summary>
protected override void Execute()
{
// document result
_documentBuilder = new StringBuilder();
// initialize
var fields = Table.Fields;
// get target data
var rows = Service.RawDataFiltered;
// get header styles
var headerStyles = fields.Select(field => Resources.Styles.GetBy(field.Header.Style).Font.FontStyles).ToList();
var valueStyles = fields.Select(field => Resources.Styles.GetBy(field.Value.Style).Font.FontStyles).ToList();
var aggregateStyles = fields.Select(field => Resources.Styles.GetBy(field.Aggregate.Style).Font.FontStyles).ToList();
// Block lines
var blocklines = Provider.Input.Model.BlockLines;
var hasBlockLines = blocklines.Any();
if (hasBlockLines)
{
foreach (var blockline in blocklines)
{
if (blockline.Show == YesNo.No)
{
continue;
}
var keyLines = blockline.Items.Keys;
foreach (var keyLine in keyLines)
{
var line = Resources.Lines.GetBy(keyLine);
if (line.Show == YesNo.No)
{
continue;
}
var times = line.Repeat == 0 ? 1 : line.Repeat;
for (var i = 1; i <= times; i++)
{
var lineType = line.LineType;
switch (lineType)
{
case KnownLineType.EmptyLine:
var emptyLine = (EmptyLineModel)line;
if (emptyLine.Show == YesNo.No)
{
continue;
}
_documentBuilder.AppendLine();
break;
case KnownLineType.TextLine:
var textLine = (TextLineModel)line;
var items = textLine.Items;
var values = new Collection <string>();
foreach (var item in items)
{
if (item.Show == YesNo.No)
{
continue;
}
values.Add(item.Value);
}
_documentBuilder.Append($"### {string.Join("\t", values)}");
_documentBuilder.AppendLine();
break;
}
}
}
}
_documentBuilder.AppendLine();
}
// headers
var idx = 0;
_documentBuilder.Append("|");
var headerValues = fields.Select(field => field.Header.Show == YesNo.No ? string.Empty : ParseField(field.Alias)).ToList();
foreach (var fontStyle in headerStyles)
{
headerValues[idx] = string.Format(fontStyle.ToMarkdownStylePattern(), headerValues[idx]);
idx++;
}
_documentBuilder.Append(string.Join("|", headerValues.ToArray()));
_documentBuilder.Append("|");
_documentBuilder.Append(Table.Output.NewLineDelimiter);
// data styles
if (Table.ShowDataValues == YesNo.Yes)
{
_documentBuilder.Append("|");
var alignments = fields.Select(field => Resources.Styles.GetBy(field.Value.Style).Content.Alignment.Horizontal);
foreach (var alignment in alignments)
{
_documentBuilder.Append(alignment.ToMarkdownTextAlignment());
_documentBuilder.Append("|");
}
_documentBuilder.Append(Table.Output.NewLineDelimiter);
}
// data values
if (Table.ShowDataValues == YesNo.Yes)
{
_documentBuilder.Append("|");
foreach (var row in rows)
{
idx = 0;
var values = new Collection <string>();
foreach (var field in fields)
{
field.DataSource = row;
var value = field.Value.GetValue(Provider.SpecialChars);
var parsedValue = ParseField(value.FormattedValue);
values.Add(parsedValue);
}
foreach (var fontStyle in valueStyles)
{
values[idx] = string.Format(fontStyle.ToMarkdownStylePattern(), values[idx]);
idx++;
}
// add values.
_documentBuilder.Append(string.Join("|", values.ToArray()));
// new line defined in output tag.
_documentBuilder.Append(Table.Output.NewLineDelimiter);
}
}
else
{
_documentBuilder.Append("|");
_documentBuilder.Append(string.Join("|", fields.Select(field => "---")));
_documentBuilder.AppendLine("|");
}
// add bottom aggregates
var fieldsWithBottomAggregates = fields.GetRange(KnownAggregateLocation.Bottom).ToList();
var hasBottomAggregates = fieldsWithBottomAggregates.Any();
if (Table.ShowDataValues == YesNo.Yes)
{
if (hasBottomAggregates)
{
_documentBuilder.Append("|");
idx = 0;
var values = new Collection <string>();
foreach (var field in fieldsWithBottomAggregates)
{
var aggregate = field.Aggregate;
var formula = new NonTabularFormulaResolver(aggregate)
{
Data = rows.Select(attr => attr.Attribute(BaseDataFieldModel.GetFieldNameFrom(field)).Value)
};
values.Add(formula.Resolve());
}
foreach (var fontStyle in aggregateStyles)
{
if (string.IsNullOrEmpty(values[idx]))
{
idx++;
continue;
}
values[idx] = string.Format(fontStyle.ToMarkdownStylePattern(), values[idx]);
idx++;
}
// add values.
_documentBuilder.Append(string.Join("|", values.ToArray()));
// new line defined in output tag.
_documentBuilder.Append(Table.Output.NewLineDelimiter);
}
}
// end of file
_documentBuilder.Append(Table.Output.EndOfFile);
// add document to result list.
Result.Add(Encoding.UTF8.GetBytes(_documentBuilder.ToString()));
}