protected virtual void DoNewChart(string dataTableName, SpreadChartSeries[] series, ChartOptions options)
{
options ??= new ChartOptions();
var spread = options.Spreadsheet?.Workbook ?? Workbook;
if (series == null || series.Length <= 0)
{
throw new ArgumentException("At least one series must be provided.", nameof(series));
}
ChartObject chart;
using (new UsingProcessor(() => spread.BeginUpdate(), () => spread.EndUpdate()))
{
(var dataWorksheet, 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 pivot 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 (options.PrimaryAxes != null && options.PrimaryAxes.Length > 0)
{
for (int i = 0; i < Math.Min(chart.PrimaryAxes.Count, options.PrimaryAxes.Length); i++)
{
SetupAxis(options.PrimaryAxes[i], chart.PrimaryAxes[i]);
}
}
if (options.SecondaryAxes != null && options.SecondaryAxes.Length > 0)
{
for (int i = 0; i < Math.Min(chart.SecondaryAxes.Count, options.SecondaryAxes.Length); i++)
{
SetupAxis(options.SecondaryAxes[i], chart.SecondaryAxes[i]);
}
}
for (int i = 0; i < series.Length; i++)
{
var cSeries = series[i];
if (cSeries == null)
{
throw new ArgumentException("Series cannot be null.");
}
if (string.IsNullOrWhiteSpace(cSeries.Values))
{
throw new ArgumentException("Series values are not specified.");
}
TableColumn columnArguments = null;
if (!string.IsNullOrWhiteSpace(cSeries.Arguments))
{
columnArguments = FindTableColumn(table, cSeries.Arguments);
}
var columnValues = FindTableColumn(table, cSeries.Values);
var rangeArguments = columnArguments?.DataRange;
var rangeValues = columnValues.DataRange;
if (cSeries.FromIndex.HasValue)
{
if (cSeries.FromIndex.Value >= table.Rows.Count)
{
cSeries.FromIndex = Math.Max(table.Rows.Count - 1, 0);
}
if (cSeries.FromIndex.Value < 0)
{
cSeries.FromIndex = Math.Max(table.Rows.Count - cSeries.FromIndex.Value, 0);
}
if (rangeArguments != null)
{
var topRowIndex = rangeArguments.TopRowIndex + cSeries.FromIndex.Value;
rangeArguments = dataWorksheet.Range.FromLTRB(rangeArguments.LeftColumnIndex, topRowIndex, rangeArguments.RightColumnIndex, rangeArguments.BottomRowIndex);
}
if (rangeValues != null)
{
var topRowIndex = rangeValues.TopRowIndex + cSeries.FromIndex.Value;
rangeValues = dataWorksheet.Range.FromLTRB(rangeValues.LeftColumnIndex, topRowIndex, rangeValues.RightColumnIndex, rangeValues.BottomRowIndex);
}
}
if (cSeries.ToIndex.HasValue)
{
if (cSeries.ToIndex.Value >= table.Rows.Count)
{
cSeries.ToIndex = Math.Max(table.Rows.Count - 1, 0);
}
if (cSeries.ToIndex.Value < 0)
{
cSeries.ToIndex = Math.Max(table.Rows.Count - cSeries.ToIndex.Value, 0);
}
if (rangeArguments != null)
{
var bottomRowIndex = Math.Min(rangeArguments.TopRowIndex + cSeries.ToIndex.Value, rangeArguments.BottomRowIndex);
rangeArguments = dataWorksheet.Range.FromLTRB(rangeArguments.LeftColumnIndex, rangeArguments.TopRowIndex, rangeArguments.RightColumnIndex, bottomRowIndex);
}
if (rangeValues != null)
{
var bottomRowIndex = Math.Min(rangeValues.TopRowIndex + cSeries.ToIndex.Value, rangeValues.BottomRowIndex);
rangeValues = dataWorksheet.Range.FromLTRB(rangeValues.LeftColumnIndex, rangeValues.TopRowIndex, rangeValues.RightColumnIndex, bottomRowIndex);
}
}
var chartSeries = chart.Series.Add(rangeArguments, rangeValues);
if (!string.IsNullOrWhiteSpace(cSeries.Name))
{
chartSeries.SeriesName.SetValue(cSeries.Name);
}
chartSeries.ChangeType((DevExpress.Spreadsheet.Charts.ChartType)cSeries.Type);
if (chartSeries.Marker != null)
{
chartSeries.Marker.Symbol = (DevExpress.Spreadsheet.Charts.MarkerStyle)cSeries.Markers;
chartSeries.Marker.Size = Utils.ValueInRange(cSeries.MarkerSize, 2, 72);
}
chartSeries.Explosion = Utils.ValueInRange(cSeries.Explosion, 0, 100);
chartSeries.Shape = (DevExpress.Spreadsheet.Charts.BarShape)cSeries.Shape;
chartSeries.Smooth = cSeries.Smooth;
//Trendlines are not rendered in SpreadsheetControl. Code can be used to display trendlines in MS Excel.
/*
* if (cSeries.Trendlines != null && cSeries.Trendlines.Length > 0)
* {
* foreach (var trendline in series.Trendlines)
* {
* if (trendline == null)
* continue;
*
* var chartTrendline = chartSeries.Trendlines.Add(trendline.Type);
* if (trendline.Name != null)
* {
* chartTrendline.AutoName = false;
* chartTrendline.CustomName = trendline.Name;
* }
*
* chartTrendline.DisplayEquation = trendline.DisplayEquation;
* chartTrendline.DisplayRSquare = trendline.DisplayRSquare;
* chartTrendline.Backward = trendline.Backward;
* chartTrendline.Forward = trendline.Forward;
* if (trendline.Intercept.HasValue)
* chartTrendline.Intercept = trendline.Intercept.Value;
* if (trendline.Order.HasValue)
* chartTrendline.Order = trendline.Order.Value;
* chartTrendline.Period = Utils.ValueInRange(trendline.Period, 2, 255);
* if (!string.IsNullOrWhiteSpace(trendline.Font))
* Utils.StringToShapeTextFont(trendline.Font, chartTrendline.Label.Font);
* if (!string.IsNullOrWhiteSpace(trendline.NumberFormat))
* chartTrendline.Label.NumberFormat.FormatCode = trendline.NumberFormat;
* }
* }
*/
//Do as last operation with chartSeries, after that object chartSeries cannot be used.
chartSeries.AxisGroup = (DevExpress.Spreadsheet.Charts.AxisGroup)cSeries.AxisGroup;
}
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;
}
}
chart.Legend.Visible = !options.HideLegend;
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.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);
}
}
public SCSpreadsheet NewChart(string dataTableName, SpreadChartSeries[] series, ChartOptions options = null)
{
ExecuteSynchronized(options, () => DoNewChart(dataTableName, series, options));
return(this);
}
