예제 #1
0
        /// <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));
        }
예제 #2
0
        /// <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));
        }
예제 #3
0
        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
                }));
            }
        }
예제 #4
0
        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
                }));
            }
        }
예제 #5
0
        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
                }));
            }
        }
예제 #6
0
        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
                }));
            }
        }
예제 #7
0
        /// <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));
        }
예제 #8
0
        /// <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));
        }
예제 #9
0
        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
                }));
            }
        }
예제 #10
0
        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
                }));
            }
        }
예제 #11
0
        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
                }));
            }
        }
예제 #12
0
        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
                }));
            }
        }
예제 #13
0
 private InsertResult InsertImplStrategy(IInsert data, IInput context)
 => data == null?InsertResult.CreateErroResult("Missing data") : data.Apply(ToStream(), context);
예제 #14
0
        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
                }));
            }
        }
예제 #15
0
        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
                }));
            }
        }
예제 #16
0
        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
                }));
            }
        }