private async void Grid_SelectionChanged(object sender, Syncfusion.UI.Xaml.CellGrid.Helpers.SelectionChangedEventArgs e)
{
await Task.Run(() =>
{
try
{
var rangeList = spreadsheet.ActiveGrid.SelectedRanges;
var excelRange = GridExcelHelper.ConvertGridRangeToExcelRange(rangeList.First(), spreadsheet.ActiveGrid);
var cells = spreadsheet.ActiveSheet.Range[excelRange];
double sum = 0;
double avg = 0;
double count = 0;
foreach (var cell in cells)
{
double value = 0;
double.TryParse(cell.Value, out value);
if (value > 0)
{
sum += value;
count++;
}
}
avg = sum / count;
lblsum.Invoke(() => { lblsum.Content = $"{sum}"; });
lblaverage.Invoke(() => { lblaverage.Content = $"{avg}"; });
lblcount.Invoke(() => { lblcount.Content = $"{count}"; });
}
catch (Exception ex)
{
}
});
}
private static void CreateChartSeries(ChartArea chartArea, IChart iChart)
{
ChartAxis commonaxis = new ChartAxis()
{
Orientation = Orientation.Vertical, OpposedPosition = true
};
chartArea.Axes.Add(commonaxis);
for (int count = 0; count < iChart.Series.Count; count++)
{
IChartSerie XlsIOChartSerie = iChart.Series[count];
ChartSeries UIChartSeries = new ChartSeries()
{
ShowEmptyPoints = false
};
SetSerieType(UIChartSeries, XlsIOChartSerie);
DataTable ct = new DataTable();
DataTable vt = new DataTable();
if (XlsIOChartSerie.CategoryLabels != null)
{
#region Discontinuous range implementation
string r = (XlsIOChartSerie as ChartSerieImpl).StrRefFormula;
if (r != null)
{
r = r.TrimStart('(');
r = r.TrimEnd(')');
string[] strRange = r.Split(',');
int i = 0;
foreach (string str in strRange)
{
i++;
ct.Columns.Add("Column" + i, typeof(string));
vt.Columns.Add("Column" + i, typeof(string));
}
List <string> labels = new List <string>();
List <string> values = new List <string>();
foreach (string str in strRange)
{
int startIndex = str.IndexOf('!');
string str1 = str.Substring(startIndex + 1);
str1 = str1.Replace(@"$", string.Empty);
GridRangeInfo range = GridExcelHelper.ConvertExcelRangeToGridRange(str1);
labels.Add(XlsIOChartSerie.CategoryLabels.Worksheet[range.Top, range.Left].Value);
values.Add(XlsIOChartSerie.Values.Worksheet[range.Top + count + 1, range.Left].Value);
}
ct.Rows.Add(labels.ToArray());
vt.Rows.Add(values.ToArray());
}
#endregion
else
{
ct = XlsIOChartSerie.CategoryLabels.Worksheet.ExportDataTable(XlsIOChartSerie.CategoryLabels, ExcelExportDataTableOptions.ComputedFormulaValues);
vt = XlsIOChartSerie.Values.Worksheet.ExportDataTable(XlsIOChartSerie.Values, ExcelExportDataTableOptions.ComputedFormulaValues);
}
}
else if (XlsIOChartSerie.NameRange != null)
{
ct = XlsIOChartSerie.Values.Worksheet.ExportDataTable(XlsIOChartSerie.NameRange, ExcelExportDataTableOptions.ComputedFormulaValues);
vt = XlsIOChartSerie.Values.Worksheet.ExportDataTable(XlsIOChartSerie.Values, ExcelExportDataTableOptions.ComputedFormulaValues);
}
else if (iChart.DataRange != null)
{
ct = XlsIOChartSerie.Values.Worksheet.ExportDataTable(iChart.DataRange, ExcelExportDataTableOptions.ComputedFormulaValues);
vt = XlsIOChartSerie.Values.Worksheet.ExportDataTable(XlsIOChartSerie.Values, ExcelExportDataTableOptions.ComputedFormulaValues);
}
List <ChartDataPoint> data = ConvertDataTableToDataPoints(ct, vt, iChart.IsSeriesInRows);
UIChartSeries.DataSource = data;
UIChartSeries.BindingPathX = "X";
UIChartSeries.BindingPathsY = new List <string> {
"Y"
};
UIChartSeries.Label = XlsIOChartSerie.Name;
UIChartSeries.Resolution = 5;
UIChartSeries.UseOptimization = true;
UIChartSeries.ShowSmartLabels = false;
#region Pie Chart
if (UIChartSeries.Type == ChartTypes.Pie)
{
System.Drawing.Color serieborder = new System.Drawing.Color();
System.Drawing.Color seriecolor = new System.Drawing.Color();
UIChartSeries.ColorEach = true;
UIChartSeries.Palette = ChartColorPalette.Custom;
Brush[] brushes = new Brush[iChart.Workbook.Palette.Length];
if (iChart.ChartType == ExcelChartType.Pie_3D)
{
int i = 0;
foreach (Syncfusion.XlsIO.IChartDataPoint dataPoint in XlsIOChartSerie.DataPoints)
{
if (dataPoint.DataFormat.HasInterior && !dataPoint.DataFormat.Interior.UseAutomaticFormat)
{
seriecolor = dataPoint.DataFormat.Interior.ForegroundColor;
brushes[i] = new SolidColorBrush(Color.FromRgb(seriecolor.R, seriecolor.G, seriecolor.B));
i++;
UIChartSeries.CustomPalette = brushes;
}
}
}
else
{
serieborder = XlsIOChartSerie.SerieFormat.LineProperties.LineColor;
if (XlsIOChartSerie.SerieFormat.AreaProperties.UseAutomaticFormat == false)
{
seriecolor = XlsIOChartSerie.SerieFormat.Fill.ForeColor;
}
UIChartSeries.Interior = new SolidColorBrush(Color.FromRgb(seriecolor.R, seriecolor.G, seriecolor.B));
}
if (XlsIOChartSerie.SerieFormat.HasLineProperties)
{
UIChartSeries.StrokeThickness = GetLineWidth(XlsIOChartSerie.SerieFormat.LineProperties.LineWeight);
}
else
{
UIChartSeries.StrokeThickness = 0;
}
}
#endregion
#region Line and Scatter Chart
else if (UIChartSeries.Type == ChartTypes.Line || UIChartSeries.Type == ChartTypes.Scatter || UIChartSeries.Type == ChartTypes.FastScatter)
{
if (UIChartSeries.Type == ChartTypes.Scatter || UIChartSeries.Type == ChartTypes.FastScatter)
{
UIChartSeries.BindingPathX = "XDouble";
}
if (XlsIOChartSerie.SerieFormat.IsMarkerSupported)
{
if (!XlsIOChartSerie.SerieFormat.IsAutoMarker)
{
System.Drawing.Color markercolor = XlsIOChartSerie.SerieFormat.MarkerBackgroundColor;
UIChartSeries.Interior = new SolidColorBrush(Color.FromRgb(markercolor.R, markercolor.G, markercolor.B));
UIChartSeries.Stroke = new SolidColorBrush(Color.FromRgb(markercolor.R, markercolor.G, markercolor.B));
}
}
if (!XlsIOChartSerie.SerieFormat.LineProperties.IsAutoLineColor)
{
System.Drawing.Color lineColor = XlsIOChartSerie.SerieFormat.LineProperties.LineColor;
UIChartSeries.Stroke = new SolidColorBrush(Color.FromRgb(lineColor.R, lineColor.G, lineColor.B));
}
UIChartSeries.ColorEach = false;
UIChartSeries.LegendIcon = ChartLegendIcon.HorizontalLine;
UIChartSeries.StrokeThickness = GetLineWidth(XlsIOChartSerie.SerieFormat.LineProperties.LineWeight) + 1;
}
#endregion
#region Other Charts
else
{
UIChartSeries.ColorEach = false;
System.Drawing.Color fcolor;
if (XlsIOChartSerie.SerieFormat.AreaProperties != null)
{
if (XlsIOChartSerie.SerieFormat.AreaProperties.UseAutomaticFormat == false)
{
fcolor = XlsIOChartSerie.SerieFormat.Fill.ForeColor;
Color fclr = Color.FromRgb(fcolor.R, fcolor.G, fcolor.B);
UIChartSeries.Interior = new SolidColorBrush(fclr);
UIChartSeries.ColorEach = false;
}
}
}
#endregion
CreateDataLabels(UIChartSeries, XlsIOChartSerie);
CreateChartSecondaryAxis(UIChartSeries, XlsIOChartSerie, chartArea, iChart);
chartArea.Series.Add(UIChartSeries);
}
System.Drawing.Color areaColor = iChart.ChartArea.Fill.ForeColor;
chartArea.Background = new SolidColorBrush(Color.FromRgb(areaColor.R, areaColor.G, areaColor.B));
if (!iChart.PlotArea.Interior.UseAutomaticFormat)
{
System.Drawing.Color plotColor = iChart.PlotArea.Fill.ForeColor;
chartArea.GridBackground = new SolidColorBrush(Color.FromRgb(plotColor.R, plotColor.G, plotColor.B));
}
CreateChartTitle(chartArea, iChart);
CreateChartLegend(chartArea, iChart);
CreateChartPrimaryAxis(chartArea, iChart);
}
