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
}
}
Published in
VSTO
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
}
}
Published in
VSTO
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
}
}
Published in
VSTO
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
}
}
Published in
VSTO
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
}
}
Published in
VSTO
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
}
}
Published in
VSTO
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
}
}
Published in
VSTO
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
}
}
Published in
VSTO
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
}
}
Published in
VSTO
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
}
}
Published in
VSTO