protected virtual void DoNewSimpleChart(string dataTableName, string[] arguments, string[] values, SimpleChartOptions options) { options ??= new SimpleChartOptions(); var spread = options.Spreadsheet?.Workbook ?? Workbook; ChartObject chart; using (new UsingProcessor(() => spread.BeginUpdate(), () => spread.EndUpdate())) { var table = FindDataTable(); int chartSheetIndex = -1; if (!string.IsNullOrWhiteSpace(options.ChartSheetName)) { for (int i = 0; i < spread.ChartSheets.Count; i++) { var sheet = spread.ChartSheets[i]; if (string.Compare(sheet.Name, options.ChartSheetName, true) == 0) { if (options.Replace) { chartSheetIndex = sheet.Index; spread.ChartSheets.Remove(sheet); } else { throw new Exception($"Cannot create chart sheet table: sheet '{options.ChartSheetName}' already exists."); } break; } } } var chartSheet = spread.ChartSheets.Add(options.ChartSheetName, (DevExpress.Spreadsheet.Charts.ChartType)(options.ChartType ?? ChartType.ColumnClustered)); if (chartSheetIndex >= 0 && chartSheetIndex < spread.ChartSheets.Count) { chartSheet.Move(chartSheetIndex); } chart = chartSheet.Chart; if (options.Style.HasValue) { chart.Style = (DevExpress.Spreadsheet.Charts.ChartStyle)options.Style.Value; } //Commented code can be used and will produce chart with fill visible in MS Excel but not rendered in SpreadsheetControl if (!string.IsNullOrWhiteSpace(options.BackColor)) { var backColor = Utils.ColorFromString(options.BackColor); //var foreColor = Utils.ColorFromString(ForeColor); /* * if (backColor != Color.Empty && foreColor != Color.Empty) * { * chart.PlotArea.Fill.SetNoFill(); * * if (FillPattern.HasValue) * chart.Fill.SetPatternFill(foreColor, backColor, FillPattern.Value); * else if (GradientType.HasValue) * { * chart.Fill.SetGradientFill(GradientType.Value, backColor, foreColor); * chart.Fill.GradientFill.Angle = GradientAngle; * } * else * chart.Fill.SetSolidFill(backColor); * } * else */ if (backColor != Color.Empty) { chart.PlotArea.Fill.SetNoFill(); /* * if (FillPattern.HasValue) * chart.Fill.SetPatternFill(Color.Black, backColor, FillPattern.Value); * else */ chart.Fill.SetSolidFill(backColor); } } /* * else if (FillPattern.HasValue) * { * chart.PlotArea.Fill.SetNoFill(); * chart.Fill.SetPatternFill(Color.Beige, Color.White, FillPattern.Value); * } */ chart.Options.DisplayBlanksAs = (DevExpress.Spreadsheet.Charts.DisplayBlanksAs)(options.DisplayBlanksAs ?? DisplayBlanksAs.Zero); if (values != null && values.Length > 0) { TableColumn columnArguments = null; for (int i = 0; i < values.Length; i++) { var value = values[i]; if (arguments != null && i < arguments.Length) { columnArguments = FindTableColumn(table, arguments[i]); } //If there are less Arguments than Values - propagate last Arguments to remaining Values. var columnValues = FindTableColumn(table, value); var seriesName = options.SeriesNames != null && i < options.SeriesNames.Length ? options.SeriesNames[i] : value; var series = chart.Series.Add(columnArguments?.DataRange, columnValues.DataRange); if (!string.IsNullOrWhiteSpace(seriesName)) { series.SeriesName.SetValue(seriesName); } if (options.SeriesTypes != null && i < options.SeriesTypes.Length) { series.ChangeType((DevExpress.Spreadsheet.Charts.ChartType)options.SeriesTypes[i]); } if (options.SeriesMarkers != null && i < options.SeriesMarkers.Length) { series.Marker.Symbol = (DevExpress.Spreadsheet.Charts.MarkerStyle)options.SeriesMarkers[i]; } //Do as last operation with series, after that object series cannot be used. if (options.AxisGroups != null && i < options.AxisGroups.Length) { series.AxisGroup = (DevExpress.Spreadsheet.Charts.AxisGroup)options.AxisGroups[i]; } } } chart.Legend.Position = (DevExpress.Spreadsheet.Charts.LegendPosition)(options.LegendPosition ?? LegendPosition.Bottom); if (!string.IsNullOrWhiteSpace(options.Title)) { chart.Title.SetValue(options.Title); if (!string.IsNullOrWhiteSpace(options.TitleFont)) { Utils.StringToShapeTextFont(options.TitleFont, chart.Title.Font); } chart.Title.Visible = true; } if (options.VaryColors) { foreach (var view in chart.Views) { view.VaryColors = true; } } if (options.HideLegend) { chart.Legend.Visible = false; } if (!string.IsNullOrWhiteSpace(options.LegendFont)) { Utils.StringToShapeTextFont(options.LegendFont, chart.Legend.Font); } if (options.DataLabelsShowCategoryName) { foreach (var view in chart.Views) { view.DataLabels.ShowCategoryName = true; } } if (options.DataLabelsShowPercent) { foreach (var view in chart.Views) { view.DataLabels.ShowPercent = true; } } if (options.AxisTitles != null && options.AxisTitles.Length > 0) { for (int i = 0; i < Math.Min(options.AxisTitles.Length, chart.PrimaryAxes.Count); i++) { chart.PrimaryAxes[i].Title.SetValue(options.AxisTitles[i]); } } if (options.SecondaryAxisTitles != null && options.SecondaryAxisTitles.Length > 0) { for (int i = 0; i < Math.Min(options.SecondaryAxisTitles.Length, chart.SecondaryAxes.Count); i++) { chart.SecondaryAxes[i].Title.SetValue(options.SecondaryAxisTitles[i]); } } if (options.AxisNumberFormats != null && options.AxisNumberFormats.Length > 0) { for (int i = 0; i < Math.Min(options.AxisNumberFormats.Length, chart.PrimaryAxes.Count); i++) { chart.PrimaryAxes[i].NumberFormat.FormatCode = options.AxisNumberFormats[i]; } for (int i = 0; i < Math.Min(options.AxisNumberFormats.Length, chart.SecondaryAxes.Count); i++) { chart.SecondaryAxes[i].NumberFormat.FormatCode = options.AxisNumberFormats[i]; } } if (!string.IsNullOrWhiteSpace(options.AxisFont)) { for (int i = 0; i < chart.PrimaryAxes.Count; i++) { Utils.StringToShapeTextFont(options.AxisFont, chart.PrimaryAxes[i].Font); } for (int i = 0; i < chart.SecondaryAxes.Count; i++) { Utils.StringToShapeTextFont(options.AxisFont, chart.SecondaryAxes[i].Font); } } if (options.MajorTickMarks != null && options.MajorTickMarks.Length > 0) { for (int i = 0; i < Math.Min(options.MajorTickMarks.Length, chart.PrimaryAxes.Count); i++) { chart.PrimaryAxes[i].MajorTickMarks = (DevExpress.Spreadsheet.Charts.AxisTickMarks)options.MajorTickMarks[i]; } for (int i = 0; i < Math.Min(options.MajorTickMarks.Length, chart.SecondaryAxes.Count); i++) { chart.SecondaryAxes[i].MajorTickMarks = (DevExpress.Spreadsheet.Charts.AxisTickMarks)options.MajorTickMarks[i]; } } if (options.MinorTickMarks != null && options.MinorTickMarks.Length > 0) { for (int i = 0; i < Math.Min(options.MinorTickMarks.Length, chart.PrimaryAxes.Count); i++) { chart.PrimaryAxes[i].MinorTickMarks = (DevExpress.Spreadsheet.Charts.AxisTickMarks)options.MinorTickMarks[i]; } for (int i = 0; i < Math.Min(options.MinorTickMarks.Length, chart.SecondaryAxes.Count); i++) { chart.SecondaryAxes[i].MinorTickMarks = (DevExpress.Spreadsheet.Charts.AxisTickMarks)options.MinorTickMarks[i]; } } if (options.AxisPositions != null && options.AxisPositions.Length > 0) { for (int i = 0; i < Math.Min(options.AxisPositions.Length, chart.PrimaryAxes.Count); i++) { chart.PrimaryAxes[i].Position = (DevExpress.Spreadsheet.Charts.AxisPosition)options.AxisPositions[i]; } } if (options.SecondaryAxisPositions != null && options.SecondaryAxisPositions.Length > 0) { for (int i = 0; i < Math.Min(options.SecondaryAxisPositions.Length, chart.SecondaryAxes.Count); i++) { chart.SecondaryAxes[i].Position = (DevExpress.Spreadsheet.Charts.AxisPosition)options.SecondaryAxisPositions[i]; } } if (options.ShowMajorGridLines) { for (int i = 0; i < chart.PrimaryAxes.Count; i++) { chart.PrimaryAxes[i].MajorGridlines.Visible = true; } for (int i = 0; i < chart.SecondaryAxes.Count; i++) { chart.SecondaryAxes[i].MajorGridlines.Visible = true; } } if (options.ShowMinorGridLines) { for (int i = 0; i < chart.PrimaryAxes.Count; i++) { chart.PrimaryAxes[i].MinorGridlines.Visible = true; } for (int i = 0; i < chart.SecondaryAxes.Count; i++) { chart.SecondaryAxes[i].MinorGridlines.Visible = true; } } } if (options.CopyToBook || options.TargetBook != null) { if (options.CopyToBookSize != null && options.CopyToBookSize.Length != 2) { throw new Exception("Invalid size of the image to copy to Book. Must be 2-elements integer array."); } if (options.CopyToBookSize != null) { var imageSize = new Size(options.CopyToBookSize[0], options.CopyToBookSize[1]); CopyImageToBook(chart.GetImage(imageSize).NativeImage, options.CopyToBookScale, options.CopyToBookScale, options); } else { CopyImageToBook(chart.GetImage().NativeImage, options.CopyToBookScale, options.CopyToBookScale, options); } } Table FindDataTable() { Worksheet sheet = null; Table table = null; if (!string.IsNullOrWhiteSpace(options.DataSheetName)) { sheet = spread.Worksheets[options.DataSheetName]; if (sheet == null) { throw new Exception($"Cannot find sheet '{options.DataSheetName}'."); } } if (!string.IsNullOrWhiteSpace(dataTableName)) { if (sheet != null) { table = sheet.Tables.Where(t => string.Compare(t.Name, dataTableName, true) == 0).FirstOrDefault(); } else { foreach (var worksheet in spread.Worksheets) { table = worksheet.Tables.Where(t => string.Compare(t.Name, dataTableName, true) == 0).FirstOrDefault(); if (table != null) { break; } } } } if (table == null) { throw new Exception($"Cannot find table '{dataTableName}'."); } return(table); }
public SCSpreadsheet NewSimpleChart(string dataTableName, string[] arguments, string[] values, SimpleChartOptions options = null) { ExecuteSynchronized(options, () => DoNewSimpleChart(dataTableName, arguments, values, options)); return(this); }