/// <summary>
/// opening the workBook from the excel file.
/// </summary>
/// <param name="filename"></param>
/// <param name="password"></param>
/// <returns></returns>
public void OpenFile(string filename, string password)
{
esh_filename = filename;
if (password.Length > 0)
{
esh_password = password;
}
try
{
this.excelWorkbook = this.excelApplication.Workbooks.Open(filename,
esh_update_links, esh_read_only, esh_format, esh_password, esh_write_res_password,
esh_ignore_read_only_recommend, esh_origin, esh_delimiter, esh_editable, esh_notify,
esh_converter, esh_add_to_mru, esh_local, esh_corrupt_load);
}
catch (Exception ee)
{
if ((ee.Message).Contains("could not be found"))
{
throw (new FileNotFoundException(ee.Message));
}
else
{
throw (new Exception("Unknown error while opening the file"));
}
}
}
/// <summary>
/// opening the workBook from the excel file.
/// </summary>
/// <param name="filename"></param>
/// <param name="password"></param>
/// <returns></returns>
public void OpenFile(string filename, string password)
{
esh_filename = filename;
if (password.Length > 0)
{
esh_password = password;
}
try
{
this.excelWorkbook = this.excelApplication.Workbooks.Open(filename,
esh_update_links, esh_read_only, esh_format, esh_password, esh_write_res_password,
esh_ignore_read_only_recommend, esh_origin, esh_delimiter, esh_editable, esh_notify,
esh_converter, esh_add_to_mru, esh_local, esh_corrupt_load);
}
catch (Exception ee)
{
if ((ee.Message).Contains("could not be found"))
{
throw (new FileNotFoundException(ee.Message));
}
else
{
throw (new Exception("Unknown error while opening the file"));
}
}
}
/// <summary>
/// Updates the chart.
/// </summary>
/// <param name="chartDataObject">The chart data object.</param>
public void UpdateChart(ChartDataObject chartDataObject)
{
Chart chart = null;
Shape shape = null;
Microsoft.Office.Interop.Excel.Workbook Workbook = null;
Microsoft.Office.Interop.Excel.Application Application = null;
try
{
Selection selection = this.Application.ActiveWindow.Selection;
if (selection.Type == PpSelectionType.ppSelectionShapes && selection.ShapeRange.HasChart == Office.MsoTriState.msoTrue)
{
int count = selection.ShapeRange.Count;
for (int i = 0; i < count; i++)
{
//Loop through all shapes
shape = selection.ShapeRange[i + 1];
if (shape.HasChart == Office.MsoTriState.msoTrue)
{
//Set the first chart in the selection
chart = shape.Chart;
if (chart != null)
{
break;
}
}
}
if (chart == null)
{
return;
}
chart = AssignExternalDataToChart(chartDataObject, chart, shape);
chart.ChartData.Activate();
}
}
finally
{
//Close the Excel Workbook and clean up resources
if (chart != null && chart.ChartData != null)
{
Workbook = chart.ChartData.Workbook as Microsoft.Office.Interop.Excel.Workbook;
if (Workbook != null)
{
Application = Workbook.Application;
}
if (Application != null && Workbook != null)
{
//Quit Excel Application
Application.Quit();
Application = null;
Workbook = null;
//Force GC, this is recommended for releasing resources since,
//quitting Excel doesnt ensure resources are cleaned immediatly
GC.Collect();
GC.WaitForPendingFinalizers();
//Repeated calls is necessary is because memory for the Excel
//would have survived for the first pass
//Hence second try to recalim completely.
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
}
}
/// <summary>
/// Inserts the chart.
/// </summary>
/// <param name="chartDataObject">The chart data object.</param>
public void InsertChart(ChartDataObject chartDataObject)
{
Chart chart = null;
Shape shape = null;
Microsoft.Office.Interop.Excel.Workbook Workbook = null;
Microsoft.Office.Interop.Excel.Application Application = null;
try
{
//Add the new slide
Slide slide = this.Application.ActivePresentation.Slides.Add(this.Application.ActivePresentation.Slides.Count + 1, PpSlideLayout.ppLayoutChart);
//Insert the chart
shape = slide.Shapes.AddChart(Office.XlChartType.xlLine);
chart = shape.Chart;
//Clear the chart contents
chart.ChartArea.ClearContents();
object[] Values = new object[chartDataObject.XValues.Count()];
for (int i = 0; i < chartDataObject.Data.Rows.Count; i++)
{
for (int j = 0; j < chartDataObject.XValues.Length; j++)
{
Values[j] = chartDataObject.Data.Rows[i][chartDataObject.XValues[j].ToString()];
}
SeriesCollection SeriesCollection = chart.SeriesCollection() as SeriesCollection;
//Create new series based on the data
Series Series = SeriesCollection.NewSeries();
Series.Name = chartDataObject.Data.Rows[i][chartDataObject.SeriesName].ToString();
Series.XValues = chartDataObject.XValues;
Series.Values = Values;
}
//Activate the chart to refresh with the fresh data.
chart.ChartData.Activate();
}
finally
{
//Close the Excel Workbook and clean up resources.
if (chart != null && chart.ChartData != null)
{
Workbook = chart.ChartData.Workbook as Microsoft.Office.Interop.Excel.Workbook;
if (Workbook != null)
{
Application = Workbook.Application;
}
if (Application != null && Workbook != null)
{
//Quit Excel Application
Application.Quit();
Application = null;
Workbook = null;
//Force the GC, this is recommended for releasing the resources.
//Quitting Excel doesnt ensure resources are cleaned immediatly
GC.Collect();
GC.WaitForPendingFinalizers();
//Repeated calls is necessary is because the memory for the Excel
//would have survived for the first pass.
//Hence the second try to recalim completely.
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
}
}
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
EnsurePowerPointIsRunning(true, true);
//Instantiate slide object
Microsoft.Office.Interop.PowerPoint.Slide objSlide = null;
//Access the first slide of presentation
objSlide = objPres.Slides[1];
//Select firs slide and set its layout
objSlide.Select();
objSlide.Layout = Microsoft.Office.Interop.PowerPoint.PpSlideLayout.ppLayoutBlank;
//Add a default chart in slide
objSlide.Shapes.AddChart(Microsoft.Office.Core.XlChartType.xl3DColumn, 20F, 30F, 400F, 300F);
//Access the added chart
Microsoft.Office.Interop.PowerPoint.Chart ppChart = objSlide.Shapes[1].Chart;
//Access the chart data
Microsoft.Office.Interop.PowerPoint.ChartData chartData = ppChart.ChartData;
//Create instance to Excel workbook to work with chart data
Microsoft.Office.Interop.Excel.Workbook dataWorkbook = (Microsoft.Office.Interop.Excel.Workbook)chartData.Workbook;
//Accessing the data worksheet for chart
Microsoft.Office.Interop.Excel.Worksheet dataSheet = dataWorkbook.Worksheets[1];
//Setting the range of chart
Microsoft.Office.Interop.Excel.Range tRange = dataSheet.Cells.get_Range("A1", "B5");
//Applying the set range on chart data table
Microsoft.Office.Interop.Excel.ListObject tbl1 = dataSheet.ListObjects["Table1"];
tbl1.Resize(tRange);
//Setting values for categories and respective series data
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("A2"))).FormulaR1C1 = "Bikes";
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("A3"))).FormulaR1C1 = "Accessories";
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("A4"))).FormulaR1C1 = "Repairs";
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("A5"))).FormulaR1C1 = "Clothing";
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("B2"))).FormulaR1C1 = "1000";
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("B3"))).FormulaR1C1 = "2500";
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("B4"))).FormulaR1C1 = "4000";
((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("B5"))).FormulaR1C1 = "3000";
//Setting chart title
ppChart.ChartTitle.Font.Italic = true;
ppChart.ChartTitle.Text = "2007 Sales";
ppChart.ChartTitle.Font.Size = 18;
ppChart.ChartTitle.Font.Color = Color.Black.ToArgb();
ppChart.ChartTitle.Format.Line.Visible = Microsoft.Office.Core.MsoTriState.msoTrue;
ppChart.ChartTitle.Format.Line.ForeColor.RGB = Color.Black.ToArgb();
//Accessing Chart value axis
Microsoft.Office.Interop.PowerPoint.Axis valaxis = ppChart.Axes(Microsoft.Office.Interop.PowerPoint.XlAxisType.xlValue, Microsoft.Office.Interop.PowerPoint.XlAxisGroup.xlPrimary);
//Setting values axis units
valaxis.MajorUnit = 2000.0F;
valaxis.MinorUnit = 1000.0F;
valaxis.MinimumScale = 0.0F;
valaxis.MaximumScale = 4000.0F;
//Accessing Chart Depth axis
Microsoft.Office.Interop.PowerPoint.Axis Depthaxis = ppChart.Axes(Microsoft.Office.Interop.PowerPoint.XlAxisType.xlSeriesAxis, Microsoft.Office.Interop.PowerPoint.XlAxisGroup.xlPrimary);
Depthaxis.Delete();
//Setting chart rotation
ppChart.Rotation = 20; //Y-Value
ppChart.Elevation = 15; //X-Value
ppChart.RightAngleAxes = false;
// Save the presentation as a PPTX
objPres.SaveAs("VSTOSampleChart", Microsoft.Office.Interop.PowerPoint.PpSaveAsFileType.ppSaveAsDefault, MsoTriState.msoTrue);
//Close Workbook and presentation
dataWorkbook.Application.Quit();
objPres.Application.Quit();
}
