Thursday, 24 August 2017 01:25

Add formula in Excel

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
using Microsoft.Office.Tools.Excel;
using System.Drawing;

namespace VSTO
{
    public partial class ThisAddIn
    {
        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
            //Instantiate the Application object
            Excel.Application excelApp = Application;

            //Add a Workbook
            Excel.Workbook workbook = excelApp.Workbooks.Add();

            //Get the First worksheet
            Excel.Worksheet sheet = (Excel.Worksheet)workbook.Sheets["Sheet1"];

            //Access cells A1, A2, A3
            Excel.Range cell1 = sheet.Range["A1"];
            Excel.Range cell2 = sheet.Range["A2"];
            Excel.Range cell3 = sheet.Range["A3"];

            //Set value
            cell1.Value = 2;
            cell2.Value = 3;

            //Add formula in cell3
            cell3.Formula = "=Sum(A1:A2)";

            //Set the font color
            cell3.Font.Color = Color.Red;

            //Save the file
            workbook.SaveAs("FormulaExcel.xlsx");
        }

        private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
        {
        }

        #region VSTO generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
            this.Startup += new System.EventHandler(ThisAddIn_Startup);
            this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
        }
        
        #endregion
    }
}

Thursday, 24 August 2017 01:23

Add excel worksheet

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
using Microsoft.Office.Tools.Excel;

namespace VSTO
{
    public partial class ThisAddIn
    {
        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
            //Add a new worksheet
            Excel.Workbook newWorkbook = this.Application.Workbooks.Add();
            Excel.Worksheet sheet = newWorkbook.ActiveSheet;

            //Set the worksheet name and the text of the specified range
            sheet.Name = "AddedSheet";
            Excel.Range cell = sheet.Cells;
            cell.set_Item(1, 1, "Spire.XLS");

            //Save
            newWorkbook.Save();

        }

        private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
        {
        }

        #region VSTO generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
            this.Startup += new System.EventHandler(ThisAddIn_Startup);
            this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
        }
        
        #endregion
    }
}

Thursday, 24 August 2017 01:19

Add comment

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
using Microsoft.Office.Tools.Excel;
using System.Drawing;

namespace VSTO
{
    public partial class ThisAddIn
    {
        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
            //Add a new workbook
            Excel.Workbook workbook = this.Application.Workbooks.Add();

            //Get the active sheet
            Excel.Worksheet sheet = workbook.ActiveSheet;

            //Define a range object(A1)
            Excel.Range range = sheet.Range["A1", "A1"];
            range.Value = "Spire.XLS";

            //Add a comment to the range
            Excel.Comment comment = range.AddComment("Spire.XLS for .NET");

            //Set the format of the comment                     
            comment.Shape.Width = 200;
            comment.Shape.Height = 50;    
        
            //Save the file
            workbook.SaveAs("Comment.xlsx");
        }

        private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
        {
        }

        #region VSTO generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
            this.Startup += new System.EventHandler(ThisAddIn_Startup);
            this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
        }
        
        #endregion
    }
}

Wednesday, 23 August 2017 06:06

Set slide style

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using PowerPoint = Microsoft.Office.Interop.PowerPoint;
using Office = Microsoft.Office.Core;

namespace VSTO
{
    public partial class ThisAddIn
    {
        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
            //Create empty presentation
            PowerPoint.Presentation ppt = this.Application.Presentations.Add();

            //Add a blank slide
            PowerPoint.Slide slide = ppt.Slides.Add(1, PowerPoint.PpSlideLayout.ppLayoutBlank);

            //Add shape to slide
            PowerPoint.Shape shape = slide.Shapes.AddShape(Office.MsoAutoShapeType.msoShapeRectangle, 300, 100, 300, 100);

            //Set the style of slide
            shape.Line.Style = Office.MsoLineStyle.msoLineSingle;
            shape.Line.Weight = 5;
            //Border color
            shape.Line.ForeColor.RGB = 0x000000CC;
            //Background color
            shape.Fill.ForeColor.RGB = 0x00000000;

            //Set the style of slide
            slide.BackgroundStyle = Office.MsoBackgroundStyleIndex.msoBackgroundStylePreset1;
            slide.Background.Fill.ForeColor.RGB = 0x00CCFFCC;

            //Save the file
            ppt.SaveAs("Style.pptx");
        }

        private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
        {
        }

        #region VSTO generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
            this.Startup += new System.EventHandler(ThisAddIn_Startup);
            this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
        }
        
        #endregion
    }
}

Wednesday, 23 August 2017 06:04

Replace text in slide

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using PowerPoint = Microsoft.Office.Interop.PowerPoint;
using Office = Microsoft.Office.Core;

namespace VSTO
{
    public partial class ThisAddIn
    {
        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
            //Open ppt document
            PowerPoint.Presentation ppt = Application.Presentations.Open("../../Data/Sample.pptx");

            //Loop through all slides and shapes
            foreach (PowerPoint.Slide slide in ppt.Slides)
            {
                foreach (PowerPoint.Shape shape in slide.Shapes)
                {
                    //Replace the specified text
                    shape.TextFrame.TextRange.Replace("Spire", "Replacement", 0, Office.MsoTriState.msoFalse, Office.MsoTriState.msoFalse);
                }
            }  
   
            //Save the file         
            ppt.SaveAs("Replace.pptx");
        }

        private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
        {
        }

        #region VSTO generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
            this.Startup += new System.EventHandler(ThisAddIn_Startup);
            this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
        }
        
        #endregion
    }
}

Wednesday, 23 August 2017 06:01

Print presentation

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using PowerPoint = Microsoft.Office.Interop.PowerPoint;
using Office = Microsoft.Office.Core;

namespace VSTO
{
    public partial class ThisAddIn
    {
        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
            //Open ppt document
            PowerPoint.Presentation ppt = Application.Presentations.Open("../../Sample.pptx");

            //Fit to page
            ppt.PrintOptions.FitToPage = Office.MsoTriState.msoTrue;

            //Print
            ppt.PrintOut(1, ppt.Slides.Count, "", 0);
        }

        private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
        {
        }

        #region VSTO generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
            this.Startup += new System.EventHandler(ThisAddIn_Startup);
            this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
        }
        
        #endregion
    }
}

Wednesday, 23 August 2017 05:58

Insert charts in slide

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using PowerPoint = Microsoft.Office.Interop.PowerPoint;
using Office = Microsoft.Office.Core;

namespace VSTO
{
    public partial class ThisAddIn
    {
        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
            //Create ppt document
            PowerPoint.Presentation ppt = this.Application.Presentations.Add();

            //Add a blank slide
            PowerPoint.Slide slide = ppt.Slides.Add(1, PowerPoint.PpSlideLayout.ppLayoutBlank);

            //Add chart
            PowerPoint.Shape shape = slide.Shapes.AddChart(Microsoft.Office.Core.XlChartType.xlColumnClustered, 100, 100, 500, 300);

            //Get the chart
            PowerPoint.Chart chart = slide.Shapes[1].Chart;

            //Create instance to excel workbook to work with chart datas
            PowerPoint.ChartData chartData = chart.ChartData;
            Microsoft.Office.Interop.Excel.Workbook dataWorkbook = (Microsoft.Office.Interop.Excel.Workbook)chartData.Workbook;

            //Get the worksheet of chart
            Microsoft.Office.Interop.Excel.Worksheet dataSheet = dataWorkbook.Worksheets[1];

            //Set the range of chart
            Microsoft.Office.Interop.Excel.Range range = dataSheet.Cells.get_Range("A1", "B5");

            //Set the data
            Microsoft.Office.Interop.Excel.ListObject table = dataSheet.ListObjects["Table1"];
            table.Resize(range);
            ((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("A2"))).Value = "January";
            ((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("A3"))).Value = "February";
            ((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("A4"))).Value = "March";
            ((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("A5"))).Value = "April";
            ((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("B2"))).Value = 100;
            ((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("B3"))).Value = 250;
            ((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("B4"))).Value = 300;
            ((Microsoft.Office.Interop.Excel.Range)(dataSheet.Cells.get_Range("B5"))).Value = 400;

            //Set the title of the chart
            chart.ChartTitle.Text = "Monthly Sales Report";

            //Save the file         
            ppt.SaveAs("AddedChart.pptx");
        }

        private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
        {
        }

        #region VSTO generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
            this.Startup += new System.EventHandler(ThisAddIn_Startup);
            this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
        }
        
        #endregion
    }
}

Wednesday, 23 August 2017 05:56

Add text to slide

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using PowerPoint = Microsoft.Office.Interop.PowerPoint;
using Office = Microsoft.Office.Core;
using System.Drawing;

namespace VSTO
{
    public partial class ThisAddIn
    {
        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
            //Create empty presentation
            PowerPoint.Presentation ppt = this.Application.Presentations.Add();

            //Add a blank slide
            PowerPoint.Slide slide = ppt.Slides.Add(1, PowerPoint.PpSlideLayout.ppLayoutBlank);

            //Add a textbox
            PowerPoint.Shape shape = slide.Shapes.AddTextbox(Office.MsoTextOrientation.msoTextOrientationHorizontal, 100, 100, 300, 200);
            
            //Add text
            PowerPoint.TextRange textRange = shape.TextFrame.TextRange;
            textRange.Text = "Spire.Presentation for .NET";
           
            //Set text style
            textRange.Font.Color.RGB = 0x00CC3333;
            textRange.Font.Bold = Office.MsoTriState.msoTrue;
            textRange.Font.Size = 20;

            //Save the file
            ppt.SaveAs("AddedText.pptx");
        }

        private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
        {
        }

        #region VSTO generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
            this.Startup += new System.EventHandler(ThisAddIn_Startup);
            this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
        }
        
        #endregion
    }
}

Wednesday, 23 August 2017 05:53

Add table to slide

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using PowerPoint = Microsoft.Office.Interop.PowerPoint;
using Office = Microsoft.Office.Core;

namespace VSTO
{
    public partial class ThisAddIn
    {
        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
            //Create empty presentation
            PowerPoint.Presentation ppt = this.Application.Presentations.Add();

            //Add a blank slide
            PowerPoint.Slide slide = ppt.Slides.Add(1, PowerPoint.PpSlideLayout.ppLayoutBlank);

            //Add table to slide
            PowerPoint.Shape shape = slide.Shapes.AddTable(6, 6, 0, 0, 800, 400);
            PowerPoint.Table table = shape.Table;

            //Loop the table to fill text
            for (int i = 1; i <= table.Rows.Count; i++)
            {
                for (int j = 1; j <= table.Columns.Count; j++)
                {                    
                    PowerPoint.TextFrame tf = table.Cell(i, j).Shape.TextFrame;
                    tf.TextRange.Text = "Row" + i.ToString() + "Col" + j.ToString();
                    tf.TextRange.ParagraphFormat.Alignment = PowerPoint.PpParagraphAlignment.ppAlignCenter;
                }
            }

            //Save the file
            ppt.SaveAs("Table.pptx");
        }

        private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
        {
        }

        #region VSTO generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
            this.Startup += new System.EventHandler(ThisAddIn_Startup);
            this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
        }
        
        #endregion
    }
}

Wednesday, 23 August 2017 05:50

Add shapes to slide

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using PowerPoint = Microsoft.Office.Interop.PowerPoint;
using Office = Microsoft.Office.Core;

namespace VSTO
{
    public partial class ThisAddIn
    {
        private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
            //Create ppt document
            PowerPoint.Presentation ppt = Globals.ThisAddIn.Application.Presentations.Add();

            //Add a blank slide
            PowerPoint.Slide slide = ppt.Slides.Add(1, PowerPoint.PpSlideLayout.ppLayoutBlank);

            //Add rectangle shape
            slide.Shapes.AddShape(Office.MsoAutoShapeType.msoShapeRectangle, 50, 100, 100, 100);

            //Add arrow shape
            slide.Shapes.AddShape(Office.MsoAutoShapeType.msoShapeRightArrow, 200, 100, 100, 100);

            //Add triangle shape
            slide.Shapes.AddShape(Office.MsoAutoShapeType.msoShapeRightTriangle, 350, 100, 100, 100);

            //Save the file
            ppt.SaveAs("Shape.pptx");
        }

        private void ThisAddIn_Shutdown(object sender, System.EventArgs e)
        {
        }

        #region VSTO generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
            this.Startup += new System.EventHandler(ThisAddIn_Startup);
            this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);
        }
        
        #endregion
    }
}

Page 155 of 157