/// <summary>
/// Implementation to execute when insert action.
/// </summary>
/// <param name="input">stream input</param>
/// <param name="context">Input context</param>
/// <returns>
/// <para>
/// A <see cref="InsertResult"/> reference that contains the result of the operation, to check if the operation is correct, the <b>Success</b>
/// property will be <b>true</b> and the <b>Value</b> property will contain the value; Otherwise, the the <b>Success</b> property
/// will be false and the <b>Errors</b> property will contain the errors associated with the operation, if they have been filled in.
/// </para>
/// <para>
/// The type of the return value is <see cref="InsertResultData"/>, which contains the operation result
/// </para>
/// </returns>
protected override InsertResult InsertImpl(Stream input, IInput context)
{
if (string.IsNullOrEmpty(SheetName))
{
return(InsertResult.CreateErroResult(
"Sheet name can not be null or empty",
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
if (Location == null)
{
return(InsertResult.CreateSuccessResult(new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
if (Style == null)
{
Style = XlsxCellStyle.Default;
}
return(InsertImpl(context, input, SheetName, Data, Location, Style));
}
/// <summary>
/// Implementation to execute when insert action.
/// </summary>
/// <param name="input">stream input</param>
/// <param name="context">Input context</param>
/// <returns>
/// <para>
/// A <see cref="InsertResult"/> reference that contains the result of the operation, to check if the operation is correct, the <b>Success</b>
/// property will be <b>true</b> and the <b>Value</b> property will contain the value; Otherwise, the the <b>Success</b> property
/// will be false and the <b>Errors</b> property will contain the errors associated with the operation, if they have been filled in.
/// </para>
/// <para>
/// The type of the return value is <see cref="InsertResultData"/>, which contains the operation result
/// </para>
/// </returns>
protected override InsertResult InsertImpl(Stream input, IInput context)
{
if (string.IsNullOrEmpty(SheetName))
{
return(InsertResult.CreateErroResult(
"Source sheet name can not be null or empty",
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
if (Destination == null)
{
return(InsertResult.CreateSuccessResult(new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
if (string.IsNullOrEmpty(Destination.WorkSheet))
{
return(InsertResult.CreateErroResult(
"Destination sheet name can not be null or empty",
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
if (SourceRange == null)
{
return(InsertResult.CreateSuccessResult(new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
if (HeaderStyle == null)
{
HeaderStyle = XlsxCellStyle.Default;
}
if (ValueStyle == null)
{
ValueStyle = XlsxCellStyle.Default;
}
return(InsertImpl(context, input, SheetName, SourceRange, Destination, HeaderStyle, ValueStyle));
}
private static InsertResult InsertImpl(IInput context, Stream input, string sheetName, XlsxRange source, QualifiedPointDefinition destination)
{
var outputStream = new MemoryStream();
try
{
using (var excel = new ExcelPackage(input))
{
var sourceWorksheet = excel.Workbook.Worksheets.FirstOrDefault(worksheet => worksheet.Name.Equals(sheetName, StringComparison.OrdinalIgnoreCase));
if (sourceWorksheet == null)
{
return(InsertResult.CreateErroResult(
$"source sheet '{sheetName}' not found",
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
var destinationWorksheet = excel.Workbook.Worksheets.FirstOrDefault(worksheet => worksheet.Name.Equals(destination.WorkSheet, StringComparison.OrdinalIgnoreCase));
if (destinationWorksheet == null)
{
return(InsertResult.CreateErroResult(
$"Destination sheet '{sheetName}' not found",
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
sourceWorksheet.Cells[source.Start.Row, source.Start.Column, source.End.Row, source.End.Column].Copy(destinationWorksheet.Cells[destination.Point.Row, destination.Point.Column]);
excel.SaveAs(outputStream);
return(InsertResult.CreateSuccessResult(new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = outputStream
}));
}
}
catch (Exception ex)
{
return(InsertResult.FromException(
ex,
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
}
private static InsertResult InsertImpl(IInput context, Stream input, string sheetName, XlsxBaseRange location, XlsxShape xlsxShape)
{
var outputStream = new MemoryStream();
try
{
using (var excel = new ExcelPackage(input))
{
var ws = excel.Workbook.Worksheets.FirstOrDefault(worksheet => worksheet.Name.Equals(sheetName, StringComparison.OrdinalIgnoreCase));
if (ws == null)
{
return(InsertResult.CreateErroResult(
$"Sheet '{sheetName}' not found",
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
var shapeName = xlsxShape.Name.HasValue() ? xlsxShape.Name : $"shape{ws.Drawings.Count}";
var shape = ws.Drawings.AddShape(shapeName, eShapeStyle.Rect);
var writer = new OfficeOpenShapeWriter(shape, ws);
writer.SetBorder(xlsxShape.Border);
writer.SetContent(xlsxShape.Content);
writer.SetFont(xlsxShape.Content.Font);
writer.SetPosition(location);
writer.SetSize(xlsxShape.Size);
writer.SetShapeEffects(xlsxShape.ShapeEffects, shapeName);
writer.SetShapeStyle(xlsxShape.ShapeType);
writer.SetText(xlsxShape.Content.Text);
excel.SaveAs(outputStream);
return(InsertResult.CreateSuccessResult(new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = outputStream
}));
}
}
catch (Exception ex)
{
return(InsertResult.FromException(
ex,
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
}
private static InsertResult InsertImpl <T>(IInput context, Stream input, string sheetName, IEnumerable <T> data, XlsxBaseRange location, YesNo showHeaders, XlsxCellStyle style)
{
var outputStream = new MemoryStream();
try
{
using (var excel = new ExcelPackage(input))
{
var ws = excel.Workbook.Worksheets.FirstOrDefault(worksheet => worksheet.Name.Equals(sheetName, StringComparison.OrdinalIgnoreCase));
if (ws == null)
{
return(InsertResult.CreateErroResult(
$"Sheet '{sheetName}' not found",
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
var safeData = data.ToList();
ExcelAddressBase locationAddress = location.ToEppExcelAddress();
ws.Cells[locationAddress.ToString()].LoadFromCollection(safeData, showHeaders == YesNo.Yes);
XlsxCellStyle styleToUse = excel.CreateStyle(style);
var range = ws.Cells[locationAddress.Start.Row, locationAddress.Start.Column, locationAddress.End.Row + safeData.Count - 1, locationAddress.End.Column];
range.StyleName = styleToUse.Name;
excel.SaveAs(outputStream);
return(InsertResult.CreateSuccessResult(new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = outputStream
}));
}
}
catch (Exception ex)
{
return(InsertResult.FromException(
ex,
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
}
private static InsertResult InsertImpl(IInput context, Stream input, string sheetName, XlsxBaseRange location, string file)
{
var outputStream = new MemoryStream();
try
{
using (var excel = new ExcelPackage(input))
{
var ws = excel.Workbook.Worksheets.FirstOrDefault(worksheet => worksheet.Name.Equals(sheetName, StringComparison.OrdinalIgnoreCase));
if (ws == null)
{
return(InsertResult.CreateErroResult(
$"Sheet '{sheetName}' not found",
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
ExcelAddressBase locationAddress = location.ToEppExcelAddress();
//ws.Cells[locationAddress.ToString()].lo(data, true);
var a = LoadXmlFromFile(file, "*");
excel.SaveAs(outputStream);
return(InsertResult.CreateSuccessResult(new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = outputStream
}));
}
}
catch (Exception ex)
{
return(InsertResult.FromException(
ex,
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
}
/// <summary>
/// Implementation to execute when insert action.
/// </summary>
/// <param name="input">stream input</param>
/// <param name="context">Input context</param>
/// <returns>
/// <para>
/// A <see cref="InsertResult"/> reference that contains the result of the operation, to check if the operation is correct, the <b>Success</b>
/// property will be <b>true</b> and the <b>Value</b> property will contain the value; Otherwise, the the <b>Success</b> property
/// will be false and the <b>Errors</b> property will contain the errors associated with the operation, if they have been filled in.
/// </para>
/// <para>
/// The type of the return value is <see cref="InsertResultData"/>, which contains the operation result
/// </para>
/// </returns>
protected override InsertResult InsertImpl(Stream input, IInput context)
{
if (string.IsNullOrEmpty(SheetName))
{
return(InsertResult.CreateErroResult(
"Sheet name can not be null or empty",
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
if (Location == null)
{
return(InsertResult.CreateSuccessResult(new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
if (Picture == null)
{
return(InsertResult.CreateSuccessResult(new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
if (Picture.Show == YesNo.No)
{
return(InsertResult.CreateSuccessResult(new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
return(InsertImpl(context, input, SheetName, Location, Picture));
}
/// <summary>
/// Implementation to execute when insert action.
/// </summary>
/// <param name="input">stream input</param>
/// <param name="context">Input context</param>
/// <returns>
/// <para>
/// A <see cref="InsertResult"/> reference that contains the result of the operation, to check if the operation is correct, the <b>Success</b>
/// property will be <b>true</b> and the <b>Value</b> property will contain the value; Otherwise, the the <b>Success</b> property
/// will be false and the <b>Errors</b> property will contain the errors associated with the operation, if they have been filled in.
/// </para>
/// <para>
/// The type of the return value is <see cref="InsertResultData"/>, which contains the operation result
/// </para>
/// </returns>
protected override InsertResult InsertImpl(Stream input, IInput context)
{
if (string.IsNullOrEmpty(SheetName))
{
return(InsertResult.CreateErroResult(
"Sheet name can not be null or empty",
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
if (Location == null)
{
Location = new XlsxPointRange {
Column = 1, Row = 1
};
}
if (Data == null)
{
return(InsertResult.CreateSuccessResult(new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
if (Styles == null)
{
return(InsertResult.CreateSuccessResult(new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
return(InsertImpl(context, input, SheetName, (XlsxPointRange)Location, Data, Styles));
}
private static InsertResult InsertImpl(IInput context, Stream input, string sheetName, object data, XlsxBaseRange location, XlsxCellStyle style)
{
var outputStream = new MemoryStream();
try
{
using (var excel = new ExcelPackage(input))
{
var ws = excel.Workbook.Worksheets.FirstOrDefault(worksheet => worksheet.Name.Equals(sheetName, StringComparison.OrdinalIgnoreCase));
if (ws == null)
{
return(InsertResult.CreateErroResult(
$"Sheet '{sheetName}' not found",
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
ExcelAddressBase locationAddress = location.ToEppExcelAddress();
XlsxCellStyle safeStyle = excel.CreateStyle(style);
XlsxCellMerge merge = safeStyle.Content.Merge;
string range = merge.Cells == 1
? locationAddress.ToString()
: merge.Orientation == KnownMergeOrientation.Horizontal
? ExcelCellBase.GetAddress(locationAddress.Start.Row, locationAddress.Start.Column, locationAddress.Start.Row, locationAddress.Start.Column + merge.Cells - 1)
: ExcelCellBase.GetAddress(locationAddress.Start.Row, locationAddress.Start.Column, locationAddress.Start.Row + merge.Cells - 1, locationAddress.Start.Column);
ExcelRange cell = ws.Cells[range];
cell.StyleName = locationAddress.End.Row.IsOdd()
? $"{safeStyle.Name}_Alternate"
: safeStyle.Name ?? XlsxBaseStyle.NameOfDefaultStyle;
if (style.Content.Show == YesNo.Yes)
{
if (data != null)
{
cell.Value = safeStyle.Content.DataType.GetFormattedDataValue(data.ToString()).FormattedValue;
if (merge.Cells > 1)
{
cell.Merge = true;
}
}
}
excel.SaveAs(outputStream);
return(InsertResult.CreateSuccessResult(new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = outputStream
}));
}
}
catch (Exception ex)
{
return(InsertResult.FromException(
ex,
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
}
private static InsertResult InsertImpl(IInput context, Stream input, string sheetName, XlsxBaseRange location, QualifiedAggregateDefinition aggregate, XlsxCellStyle style)
{
var outputStream = new MemoryStream();
try
{
using (var excel = new ExcelPackage(input))
{
var ws = excel.Workbook.Worksheets.FirstOrDefault(worksheet => worksheet.Name.Equals(sheetName, StringComparison.OrdinalIgnoreCase));
if (ws == null)
{
return(InsertResult.CreateErroResult(
$"Sheet '{sheetName}' not found",
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
string aggregateWorksheetName = aggregate.WorkSheet;
if (string.IsNullOrEmpty(aggregate.WorkSheet))
{
aggregateWorksheetName = sheetName;
}
var aggregateWorksheet = ws;
bool sameSheetName = aggregateWorksheetName.Equals(sheetName, StringComparison.OrdinalIgnoreCase);
if (!sameSheetName)
{
aggregateWorksheet = excel.Workbook.Worksheets.FirstOrDefault(worksheet => worksheet.Name.Equals(aggregateWorksheetName, StringComparison.OrdinalIgnoreCase));
}
if (aggregateWorksheet == null)
{
return(InsertResult.CreateErroResult(
$"Aggregate sheet '{aggregateWorksheetName}' not found",
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
ExcelAddressBase locationAddress = location.ToEppExcelAddress();
XlsxCellStyle safeStyle = excel.CreateStyle(style);
XlsxCellMerge merge = safeStyle.Content.Merge;
string range = merge.Cells == 1
? locationAddress.ToString()
: merge.Orientation == KnownMergeOrientation.Horizontal
? ExcelCellBase.GetAddress(locationAddress.Start.Row, locationAddress.Start.Column, locationAddress.Start.Row, locationAddress.Start.Column + merge.Cells - 1)
: ExcelCellBase.GetAddress(locationAddress.Start.Row, locationAddress.Start.Column, locationAddress.Start.Row + merge.Cells - 1, locationAddress.Start.Column);
ExcelRange cell = aggregateWorksheet.Cells[range];
cell.StyleName = cell.StyleName = locationAddress.End.Row.IsOdd()
? $"{safeStyle.Name}_Alternate"
: safeStyle.Name ?? XlsxBaseStyle.NameOfDefaultStyle;
if (style.Content.Show == YesNo.Yes)
{
var formula = new XlsxFormulaResolver(aggregate)
{
HasAutoFilter = aggregate.HasAutoFilter, WorkSheet = sheetName
};
cell.Formula = formula.Resolve();
if (merge.Cells > 1)
{
cell.Merge = true;
}
}
excel.SaveAs(outputStream);
return(InsertResult.CreateSuccessResult(new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = outputStream
}));
}
}
catch (Exception ex)
{
return(InsertResult.FromException(
ex,
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
}
private static InsertResult InsertImpl(IInput context, Stream input, string sheetName, XlsxBaseRange location, XlsxPicture xlsxPicture)
{
var outputStream = new MemoryStream();
try
{
using (var excel = new ExcelPackage(input))
{
var ws = excel.Workbook.Worksheets.FirstOrDefault(worksheet => worksheet.Name.Equals(sheetName, StringComparison.OrdinalIgnoreCase));
if (ws == null)
{
return(InsertResult.CreateErroResult(
$"Sheet '{sheetName}' not found",
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
using (var image = xlsxPicture.GetImage())
{
if (image == null)
{
return(InsertResult.CreateErroResult(
"The image could not be loaded, please check that the path is correct",
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
var pictureName = xlsxPicture.Name.HasValue() ? xlsxPicture.Name : $"picture{ws.Drawings.Count}";
foreach (var item in ws.Drawings)
{
var pic = item as ExcelPicture;
if (pic == null)
{
continue;
}
var existPicture = pic.Name.Equals(pictureName, StringComparison.OrdinalIgnoreCase);
if (!existPicture)
{
continue;
}
return(InsertResult.CreateErroResult(
$"There is already an image with the name '{pictureName}' in the collection, please rename the image.",
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
ExcelPicture picture = ws.Drawings.AddPicture(pictureName, image);
var writer = new OfficeOpenPictureWriter(picture, ws);
writer.SetBorder(xlsxPicture.Border);
writer.SetContent(xlsxPicture.Content);
writer.SetPosition(location);
writer.SetSize(xlsxPicture.Size);
writer.SetShapeEffects(xlsxPicture.ShapeEffects, pictureName);
}
excel.SaveAs(outputStream);
return(InsertResult.CreateSuccessResult(new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = outputStream
}));
}
}
catch (Exception ex)
{
return(InsertResult.FromException(
ex,
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
}
private static InsertResult InsertImpl(IInput context, Stream input, string sheetName, XlsxBaseRange location, XlsxMiniChart miniChart)
{
var outputStream = new MemoryStream();
try
{
using (var excel = new ExcelPackage(input))
{
ExcelWorksheet ws = excel.Workbook.Worksheets.FirstOrDefault(worksheet => worksheet.Name.Equals(sheetName, StringComparison.OrdinalIgnoreCase));
if (ws == null)
{
return(InsertResult.CreateErroResult(
$"Sheet '{sheetName}' not found",
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
// Create Minichart
ExcelAddressBase locationAddress = location.ToEppExcelAddress();
ExcelAddressBase dataddress = miniChart.ChartRanges.Data.ToEppExcelAddress();
ExcelSparklineGroup sparkline = ws.SparklineGroups.Add(miniChart.ChartType.Active.ToEppeSparklineType(), ws.Cells[locationAddress.ToString()], ws.Cells[dataddress.ToString()]);
sparkline.DisplayHidden = miniChart.DisplayHidden == YesNo.Yes;
sparkline.ColorSeries.SetColor(miniChart.ChartType.GetMiniChartSerieColor());
sparkline.DisplayEmptyCellsAs = miniChart.EmptyValueAs.ToEppeDisplayBlanksAs();
// Minichart Size
int offsetY = miniChart.ChartSize.VerticalCells == 1 ? 0 : miniChart.ChartSize.VerticalCells - 1;
int offsetX = miniChart.ChartSize.HorizontalCells == 1 ? 0 : miniChart.ChartSize.HorizontalCells - 1;
ExcelCellBase miniChartSizeAddress = location.Expand(miniChart.ChartSize);
ExcelRange miniChartCell = ws.Cells[miniChartSizeAddress.ToString()];
if (offsetX >= 1 || offsetY >= 1)
{
miniChartCell.Merge = true;
}
// Axes
// Horizontal axis
sparkline.DateAxisRange = null;
if (miniChart.ChartAxes.Horizontal.Type == MiniChartHorizontalAxisType.Date)
{
ExcelAddressBase axisRangeType = miniChart.ChartRanges.Axis.ToEppExcelAddress();
sparkline.DateAxisRange = ws.Cells[axisRangeType.ToString()];
}
sparkline.RightToLeft = miniChart.ChartAxes.Horizontal.RightToLeft == YesNo.Yes;
if (miniChart.ChartAxes.Horizontal.Show == YesNo.Yes)
{
sparkline.DisplayXAxis = true;
sparkline.ColorAxis.SetColor(miniChart.ChartAxes.Horizontal.GetColor());
}
// Vertical axis
bool maxVerticalAxisIsAuto = string.IsNullOrEmpty(miniChart.ChartAxes.Vertical.Max) || miniChart.ChartAxes.Vertical.Max.Equals("Automatic", StringComparison.OrdinalIgnoreCase);
sparkline.MaxAxisType = maxVerticalAxisIsAuto
? eSparklineAxisMinMax.Individual
: eSparklineAxisMinMax.Custom;
bool minVerticalAxisIsAuto = string.IsNullOrEmpty(miniChart.ChartAxes.Vertical.Min) || miniChart.ChartAxes.Vertical.Min.Equals("Automatic", StringComparison.OrdinalIgnoreCase);
sparkline.MinAxisType = minVerticalAxisIsAuto
? eSparklineAxisMinMax.Individual
: eSparklineAxisMinMax.Custom;
if (!maxVerticalAxisIsAuto)
{
sparkline.ManualMax = double.Parse(miniChart.ChartAxes.Vertical.Max);
}
if (!minVerticalAxisIsAuto)
{
sparkline.ManualMin = double.Parse(miniChart.ChartAxes.Vertical.Min);
}
// Points
switch (miniChart.ChartType.Active)
{
case MiniChartType.Column:
if (!miniChart.ChartType.Column.Points.Low.IsDefault)
{
sparkline.Low = true;
sparkline.ColorLow.SetColor(miniChart.ChartType.Column.Points.Low.GetColor());
}
if (!miniChart.ChartType.Column.Points.First.IsDefault)
{
sparkline.First = true;
sparkline.ColorFirst.SetColor(miniChart.ChartType.Column.Points.First.GetColor());
}
if (!miniChart.ChartType.Column.Points.High.IsDefault)
{
sparkline.High = true;
sparkline.ColorHigh.SetColor(miniChart.ChartType.Column.Points.High.GetColor());
}
if (!miniChart.ChartType.Column.Points.Last.IsDefault)
{
sparkline.Last = true;
sparkline.ColorLast.SetColor(miniChart.ChartType.Column.Points.Last.GetColor());
}
if (!miniChart.ChartType.Column.Points.Negative.IsDefault)
{
sparkline.Negative = true;
sparkline.ColorNegative.SetColor(miniChart.ChartType.Column.Points.Negative.GetColor());
}
break;
case MiniChartType.Line:
sparkline.LineWidth = double.Parse(miniChart.ChartType.Line.Serie.Width);
if (!miniChart.ChartType.Line.Points.Low.IsDefault)
{
sparkline.Low = true;
sparkline.ColorLow.SetColor(miniChart.ChartType.Line.Points.Low.GetColor());
}
if (!miniChart.ChartType.Line.Points.First.IsDefault)
{
sparkline.First = true;
sparkline.ColorFirst.SetColor(miniChart.ChartType.Line.Points.First.GetColor());
}
if (!miniChart.ChartType.Line.Points.High.IsDefault)
{
sparkline.High = true;
sparkline.ColorHigh.SetColor(miniChart.ChartType.Line.Points.High.GetColor());
}
if (!miniChart.ChartType.Line.Points.Last.IsDefault)
{
sparkline.Last = true;
sparkline.ColorLast.SetColor(miniChart.ChartType.Line.Points.Last.GetColor());
}
if (!miniChart.ChartType.Line.Points.Negative.IsDefault)
{
sparkline.Negative = true;
sparkline.ColorNegative.SetColor(
miniChart.ChartType.Line.Points.Negative.GetColor());
}
if (!miniChart.ChartType.Line.Points.Markers.IsDefault)
{
sparkline.Markers = true;
sparkline.ColorNegative.SetColor(miniChart.ChartType.Line.Points.Markers.GetColor());
}
break;
case MiniChartType.WinLoss:
if (!miniChart.ChartType.WinLoss.Points.Low.IsDefault)
{
sparkline.Low = true;
sparkline.ColorLow.SetColor(miniChart.ChartType.WinLoss.Points.Low.GetColor());
}
if (!miniChart.ChartType.WinLoss.Points.First.IsDefault)
{
sparkline.First = true;
sparkline.ColorFirst.SetColor(miniChart.ChartType.WinLoss.Points.First.GetColor());
}
if (!miniChart.ChartType.WinLoss.Points.High.IsDefault)
{
sparkline.High = true;
sparkline.ColorHigh.SetColor(miniChart.ChartType.WinLoss.Points.High.GetColor());
}
if (!miniChart.ChartType.WinLoss.Points.Last.IsDefault)
{
sparkline.Last = true;
sparkline.ColorLast.SetColor(miniChart.ChartType.WinLoss.Points.Last.GetColor());
}
if (!miniChart.ChartType.WinLoss.Points.Negative.IsDefault)
{
sparkline.Negative = true;
sparkline.ColorNegative.SetColor(miniChart.ChartType.WinLoss.Points.Negative.GetColor());
}
break;
}
excel.SaveAs(outputStream);
return(InsertResult.CreateSuccessResult(new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = outputStream
}));
}
}
catch (Exception ex)
{
return(InsertResult.FromException(
ex,
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
}
private InsertResult InsertImplStrategy(IInsert data, IInput context)
=> data == null?InsertResult.CreateErroResult("Missing data") : data.Apply(ToStream(), context);
private static InsertResult InsertImpl(IInput context, Stream input, string sheetName, XlsxRange source, QualifiedPointDefinition destination, XlsxCellStyle headerStyle, XlsxCellStyle valueStyle)
{
var outputStream = new MemoryStream();
try
{
using (var excel = new ExcelPackage(input))
{
var sourceWorksheet = excel.Workbook.Worksheets.FirstOrDefault(worksheet => worksheet.Name.Equals(sheetName, StringComparison.OrdinalIgnoreCase));
if (sourceWorksheet == null)
{
return(InsertResult.CreateErroResult(
$"source sheet '{sheetName}' not found",
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
var destinationWorksheet = excel.Workbook.Worksheets.FirstOrDefault(worksheet => worksheet.Name.Equals(destination.WorkSheet, StringComparison.OrdinalIgnoreCase));
if (destinationWorksheet == null)
{
return(InsertResult.CreateErroResult(
$"Destination sheet '{sheetName}' not found",
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
var startDestination = destination.Point.Clone();
var x = destination.Point.Column - 1;
var sourceRange = sourceWorksheet.Cells[source.Start.Row, source.Start.Column, source.End.Row, source.End.Column];
for (var column = 0; column < sourceRange.Columns; column++)
{
destination.Point = new XlsxPoint {
Column = x, Row = destination.Point.Row
};
for (var row = 0; row < sourceRange.Rows; row++)
{
destination.Point.Offset(1, 0);
destinationWorksheet.Cells[destination.Point.Row, destination.Point.Column].Value = sourceWorksheet.Cells[source.Start.Row + row, source.Start.Column + column].Value;
}
destination.Point.Offset(0, 1);
}
XlsxCellStyle headerStyleToUse = excel.CreateStyle(headerStyle);
var headerRange = destinationWorksheet.Cells[startDestination.Row, startDestination.Column, startDestination.Row, destination.Point.Column];
headerRange.StyleName = headerStyleToUse.Name;
XlsxCellStyle valueStyleToUse = excel.CreateStyle(valueStyle);
var valueRange = destinationWorksheet.Cells[startDestination.Row + 1, startDestination.Column, destination.Point.Row - 1, destination.Point.Column];
valueRange.StyleName = valueStyleToUse.Name;
excel.SaveAs(outputStream);
return(InsertResult.CreateSuccessResult(new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = outputStream
}));
}
}
catch (Exception ex)
{
return(InsertResult.FromException(
ex,
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
}
private static InsertResult InsertImpl(IInput context, Stream input, string sheetName, XlsxBaseRange location, XlsxChart chart)
{
var outputStream = new MemoryStream();
try
{
using (var excel = new ExcelPackage(input))
{
ExcelWorksheet ws = excel.Workbook.Worksheets.FirstOrDefault(worksheet => worksheet.Name.Equals(sheetName, StringComparison.OrdinalIgnoreCase));
if (ws == null)
{
return(InsertResult.CreateErroResult(
$"Sheet '{sheetName}' not found",
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
// Create Chart
ExcelChart mainchart = null;
var plots = chart.Plots;
foreach (var plot in plots)
{
var series = plot.Series;
foreach (var serie in series)
{
// Create chart
ExcelChart workchart;
if (plot.UseSecondaryAxis.Equals(YesNo.No))
{
if (mainchart == null)
{
mainchart = ws.Drawings.AddChart(serie.Name, 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 sr = workchart.Series.Add(serie.FieldRange.ToString(), serie.AxisRange.ToString());
sr.Header = serie.Name;
}
}
if (mainchart != null)
{
var writer = new OfficeOpenChartWriter(mainchart);
writer.SetSize(chart.Size);
writer.SetPosition(location);
writer.SetContent(chart);
writer.SetBorder(chart.Border);
writer.SetTitle(chart.Title);
writer.SetLegend(chart.Legend);
writer.SetAxes(chart.Axes);
writer.SetPlotArea(chart.Plots);
writer.SetShapeEffects(chart.Effects);
}
excel.SaveAs(outputStream);
return(InsertResult.CreateSuccessResult(new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = outputStream
}));
}
}
catch (Exception ex)
{
return(InsertResult.FromException(
ex,
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
}
private static InsertResult InsertImpl(IInput context, Stream input, string sheetName, XlsxPointRange location, Dictionary <string, object> data, DictionaryStyles styles)
{
var outputStream = new MemoryStream();
try
{
using (var excel = new ExcelPackage(input))
{
var ws = excel.Workbook.Worksheets.FirstOrDefault(worksheet => worksheet.Name.Equals(sheetName, StringComparison.OrdinalIgnoreCase));
if (ws == null)
{
return(InsertResult.CreateErroResult(
$"Sheet '{sheetName}' not found",
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
// cell styles > Headers
XlsxCellStyle headerTextTextStyle = excel.CreateStyle(styles.Headers.Text);
// cell styles > Values
XlsxCellStyle valueTextTextStyle = excel.CreateStyle(styles.Values.Text);
XlsxCellStyle valueDatetimeCellStyle = excel.CreateStyle(styles.Values.DateTime);
XlsxCellStyle valueDecimalCellStyle = excel.CreateStyle(styles.Values.Decimal);
var keys = data.Keys;
foreach (var key in keys)
{
var isOdd = location.Row.IsOdd();
var headerCell = ws.Cells[location.Row, location.Column];
headerCell.StyleName = isOdd ? $"{headerTextTextStyle.Name}_Alternate" : headerTextTextStyle.Name ?? XlsxBaseStyle.NameOfDefaultStyle;
headerCell.Value = key;
var value = data[key];
var valueCell = ws.Cells[location.Row, location.Column + 1];
XlsxCellStyle styleToUse;
switch (value)
{
case string _:
styleToUse = valueTextTextStyle;
break;
case float _:
case double _:
styleToUse = valueDecimalCellStyle;
break;
case DateTime _:
styleToUse = valueDatetimeCellStyle;
break;
default:
styleToUse = valueTextTextStyle;
break;
}
valueCell.StyleName = isOdd ? $"{styleToUse.Name}_Alternate" : styleToUse.Name ?? XlsxBaseStyle.NameOfDefaultStyle;
valueCell.Value = styleToUse.Content.DataType.GetFormattedDataValue(value.ToString()).FormattedValue;
location.Offset(0, 1);
}
excel.SaveAs(outputStream);
return(InsertResult.CreateSuccessResult(new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = outputStream
}));
}
}
catch (Exception ex)
{
return(InsertResult.FromException(
ex,
new InsertResultData
{
Context = context,
InputStream = input,
OutputStream = input
}));
}
}
