private void DrawGraphFromSelectedCells(int graphRow, int graphColumn, string title, string valueName, Worksheet worksheet)
{
bool screenUpdating = Application.ScreenUpdating;
Application.ScreenUpdating = false;
Excel.Application exApp = Globals.ThisAddIn.Application;
Excel.Range selectedRange = exApp.Selection;
long row = selectedRange.Row;
long col = selectedRange.Column;
Worksheet activeWorksheet = Globals.Factory.GetVstoObject(Application.ActiveWorkbook.ActiveSheet);
string name = GetGraphName(activeWorksheet);
Chart chart = activeWorksheet.Controls.AddChart(activeWorksheet.Range["A1"].Resize[_GRAPH_HEIGHT, _GRAPH_WIDTH]
.Offset[graphRow * _GRAPH_HEIGHT, graphColumn * _GRAPH_WIDTH], name);
chart.ChartType = Excel.XlChartType.xlXYScatterLinesNoMarkers;
chart.SetSourceData(selectedRange, Excel.XlRowCol.xlColumns);
chart.HasTitle = true;
chart.ChartTitle.Text = title;
Excel.Axis xAxis = (Excel.Axis)chart.Axes(Excel.XlAxisType.xlCategory);
xAxis.HasTitle = true;
xAxis.AxisTitle.Text = activeWorksheet.Cells[row, col].Text;
Excel.Axis yAxis = (Excel.Axis)chart.Axes(Excel.XlAxisType.xlValue);
yAxis.HasTitle = true;
yAxis.AxisTitle.Text = valueName;
xAxis.MinimumScaleIsAuto = false;
Excel.Name newName = activeWorksheet.Names.Add("firstCol", selectedRange.Resize[selectedRange.Rows.Count, 1], false);
xAxis.MinimumScale = (double)activeWorksheet.Evaluate("MIN(firstCol)");
xAxis.MaximumScale = (double)activeWorksheet.Evaluate("MAX(firstCol)");
newName.Delete();
newName = activeWorksheet.Names.Add("col", selectedRange.Resize[selectedRange.Rows.Count, 1].Offset[missing, 1], false);
double maxValue = (double)activeWorksheet.Evaluate("MAX(col)");
double minValue = (double)activeWorksheet.Evaluate("MIN(col)");
newName.Delete();
for (int i = 2; i < selectedRange.Columns.Count; ++i)
{
newName = activeWorksheet.Names.Add("col", selectedRange.Resize[selectedRange.Rows.Count, 1].Offset[missing, i], false);
double maxValueNow = (double)activeWorksheet.Evaluate("MAX(col)");
double minValueNow = (double)activeWorksheet.Evaluate("MIN(col)");
newName.Delete();
if (maxValueNow > maxValue)
{
maxValue = maxValueNow;
}
if (minValueNow < minValue)
{
minValue = minValueNow;
}
}
yAxis.MinimumScaleIsAuto = false;
yAxis.MinimumScale = minValue;
yAxis.MaximumScale = maxValue;
chart.Location(Excel.XlChartLocation.xlLocationAsObject, worksheet.Name);
activeWorksheet.Activate();
Application.ScreenUpdating = screenUpdating;
}
public override void RealDispose()
{
if (rangeName != null)
{
rangeName.Delete();
}
if (NestedContextItem != null)
{
NestedContextItem.Dispose();
}
ExcelApplication.ReleaseComObject(rangeName);
ExcelApplication.ReleaseComObject(Range);
rangeName = null;
Range = null;
}
public static void DeleteNamesWithExcelApp(String[] tps)
{
Excel.Workbooks xlWorkbooks = null;
Excel.Workbook xlWorkbook = null;
Excel.Names ranges = null;
var xlApp = new Excel.Application();
try
{
foreach (String m in tps)
{
xlWorkbooks = xlApp.Workbooks;
xlWorkbook = xlWorkbooks.Open(m);
ranges = xlWorkbook.Names;
int leftoveritems = ranges.Count;
Excel.Name name = null;
try
{
for (int i = leftoveritems; i >= 1; i--)
{
name = xlWorkbook.Names.Item(i);
name.Delete();
if (name != null)
{
Marshal.ReleaseComObject(name);
}
name = null;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
if (name != null)
{
Marshal.ReleaseComObject(name);
}
}
if (xlWorkbook != null)
{
xlWorkbook.Close(true);
Marshal.ReleaseComObject(xlWorkbook);
xlWorkbook = null;
}
if (xlWorkbooks != null)
{
Marshal.ReleaseComObject(xlWorkbooks);
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
if (ranges != null)
{
Marshal.ReleaseComObject(ranges);
}
if (xlWorkbook != null)
{
Marshal.ReleaseComObject(xlWorkbook);
}
if (xlWorkbooks != null)
{
Marshal.ReleaseComObject(xlWorkbooks);
}
if (xlApp != null)
{
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
xlApp = null;
}
}
}