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);
}
