/// <summary>
/// Обновление Диаграммы
/// </summary>
internal void UpdateDiagramm()
{
Excel.ChartObject shp = SheetUrv11.ChartObjects("Chart 2");
Excel.Chart chartPage = shp.Chart;
Excel.SeriesCollection seriesCol = (Excel.SeriesCollection)chartPage.SeriesCollection();
Excel.FullSeriesCollection fullColl = chartPage.FullSeriesCollection();
Debug.WriteLine(fullColl.Count);
int lastCol = GetLastColumnUrv(SheetUrv11, _rowStart);
int lastRow = GetLastRowUrv11();
int ix = 1;
string letterCost = "G";
fullColl.Item(ix).Name = $"={SheetUrv11.Name}!${letterCost}10";
fullColl.Item(ix).Values = $"={SheetUrv11.Name}!${letterCost}{_rowStart}:${letterCost}{lastRow}";
fullColl.Item(ix).XValues = $"={SheetUrv11.Name}!$C{_rowStart}:$C{lastRow}";
for (int col = 9; col <= lastCol; col += 3)
{
Excel.Range cellFirstCost = SheetUrv11.Cells[_rowStart, col];
string text = cellFirstCost.Value?.ToString() ?? "";
if (string.IsNullOrEmpty(text))
{
continue;
}
letterCost = ExcelHelper.GetColumnLetter(cellFirstCost);
ix++;
if (ix > fullColl.Count)
{
seriesCol.NewSeries();
}
fullColl.Item(ix).Name = $"={SheetUrv11.Name}!${letterCost}10";
fullColl.Item(ix).Values = $"={SheetUrv11.Name}!${letterCost}{_rowStart}:${letterCost}{lastRow}";
fullColl.Item(ix).XValues = $"={SheetUrv11.Name}!$C{_rowStart}:$C{lastRow}";
}
if (ix < fullColl.Count)
{
for (int i = ix + 1; i <= fullColl.Count; i++)
{
fullColl.Item(i).Delete();
}
}
}
public void CreateChart(string pivotTableName, int chartTop, string cTitle)
{
Excel.Worksheet activeSheet = null;
Excel.Range selectedRange = null;
Excel.Shapes shapes = null;
Excel.Chart chart = null;
Excel.ChartTitle chartTitle = null;
try
{
activeSheet = (Excel.Worksheet)(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveSheet;
Excel.Range rangePivot;
rangePivot = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A1", "AA20000");
selectedRange = IdentifyPivotRangesByName(pivotTableName);
shapes = activeSheet.Shapes;
// Width original 255
if (Globals.ExcelVersion == "15.0" || Globals.ExcelVersion == "16.0")
{
// 204 is a nice style with shadow
shapes.AddChart2(Style: 259, XlChartType: Excel.XlChartType.xlColumnClustered,
Left: 10, Top: chartTop, Width: 450,
Height: 210, NewLayout: true).Select();
}
else
{
shapes.AddChart(XlChartType: Excel.XlChartType.xlColumnClustered,
Left: 10, Top: chartTop, Width: 450,
Height: 210).Select();
}
chart = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveChart;
chart.HasTitle = true;
chart.ChartTitle.Text = cTitle;
chart.ChartTitle.Format.TextFrame2.TextRange.Font.Caps = Microsoft.Office.Core.MsoTextCaps.msoNoCaps;
chart.ChartArea.Interior.Color = System.Drawing.Color.FromArgb(242, 244, 244); // Change chart to light gray
// chart.ChartArea.Interior.Color = System.Drawing.Color.FromRgb(0, 255, 0);
// chart.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowValue, true, true, true, true, true, true, true, true, true); // Turn on data labels
chart.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowValue, true, true, true, false, false, true, true, true, true); // Turn on data labels
chart.SetSourceData(selectedRange);
chart.HasLegend = false;
chart.ApplyDataLabels();
if (Globals.ExcelVersion == "15.0" || Globals.ExcelVersion == "16.0")
{
chart.FullSeriesCollection(1).DataLabels.ShowValue = true;
}
else
{
chart.SeriesCollection(1).DataLabels.ShowValue = true;
}
if (pivotTableName == "PivotTableApplicationName")
{
chart.Axes(Excel.XlAxisType.xlCategory).TickLabels.Orientation = Excel.XlOrientation.xlUpward;
}
selectedRange.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
selectedRange.Borders[Excel.XlBordersIndex.xlEdgeRight].ColorIndex = 0;
selectedRange.Borders[Excel.XlBordersIndex.xlEdgeRight].TintAndShade = 0;
selectedRange.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin;
selectedRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
selectedRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].ColorIndex = 0;
selectedRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].TintAndShade = 0;
selectedRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin;
Globals.ChartBottom = (int)chart.ChartArea.Top + (int)chart.ChartArea.Height + 15;
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A2", "A2").Select();
// chart.Export(pivotTableName + ".png");
}
catch (Exception ex)
{
// ex.Data.Add("ExcelHelper", "Error occurred in the CreateChart() method");
ex.Data.Add("ExcelHelper", " Error occurred in the CreateChart() method\r\n" + ex.Message);
throw;
}
finally
{
if (chartTitle != null)
{
Marshal.ReleaseComObject(chartTitle);
}
if (chart != null)
{
Marshal.ReleaseComObject(chart);
}
if (shapes != null)
{
Marshal.ReleaseComObject(shapes);
}
if (selectedRange != null)
{
Marshal.ReleaseComObject(selectedRange);
}
}
}
