Thursday, 24 August 2017 01:39
Merge cells 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; 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 sheet Excel.Worksheet sheet = (Excel.Worksheet)workbook.Sheets["Sheet1"]; //Merge A1-B5 to one cell sheet.get_Range("A1", "B5").Merge(); //Save the file workbook.SaveAs("MergedCells.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:37
Insert hyperlink 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; 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 sheet Excel.Worksheet sheet = (Excel.Worksheet)workbook.Sheets["Sheet1"]; //Define a range object(A1) Excel.Range range = sheet.get_Range("A1", "A1"); //Add a hyperlink sheet.Hyperlinks.Add(range, "https://www.e-iceblue.com/", Type.Missing, "Click to E-iceblue website", "E-iceblue site"); //Save the file workbook.SaveAs("ExcelHyperlink.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:35
Insert background image 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; 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 sheet Excel.Worksheet sheet = (Excel.Worksheet)workbook.Sheets["Sheet1"]; //Set a background image sheet.SetBackgroundPicture(@"C:\Users\Administrator\Desktop\image.jpg"); //Save the file workbook.SaveAs("BackgroundImage.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:33
Create excel chart
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) { //Instantiate the Application object. Excel.Application ExcelApp = Application; //Add a Workbook. Excel.Workbook objBook = ExcelApp.Workbooks.Add(); //Get the active Worksheet Microsoft.Office.Interop.Excel.Worksheet nativeWorksheet = Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet; Microsoft.Office.Tools.Excel.Worksheet sheet = Globals.Factory.GetVstoObject(nativeWorksheet); //Add data for pie chart sheet.Cells[1, 1] = "Quarter"; sheet.Cells[1, 2] = "Sales"; sheet.Cells[2, 1] = "One"; sheet.Cells[2, 2] = 4000; sheet.Cells[3, 1] = "Two"; sheet.Cells[3, 2] = 6000; sheet.Cells[4, 1] = "Three"; sheet.Cells[4, 2] = 10000; sheet.Cells[5, 1] = "Four"; sheet.Cells[5, 2] = 13000; //Chart reference Microsoft.Office.Tools.Excel.Chart chart; //Add a Pie Chart chart = sheet.Controls.AddChart(0, 100, 400, 200, "Sales by quarter"); chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xl3DPie; //Set chart title chart.HasTitle = true; chart.ChartTitle.Text = "Sales by quarter"; //Set region of chart data Microsoft.Office.Interop.Excel.Range chartRange = sheet.get_Range("A2", "B5"); chart.SetSourceData(chartRange); //Get the active workbook Microsoft.Office.Interop.Excel.Workbook workbook = sheet.Application.ActiveWorkbook; //Save the file workbook.SaveAs("3DPieChart.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:30
Copy 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 worksheet = newWorkbook.ActiveSheet; //Set the text of the specified range Excel.Range cells = worksheet.Cells; cells.set_Item(1, 1, "Spire.XLS"); //Copy worksheet worksheet.Copy(worksheet); //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:28
Apply conditional formatting 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 sheet Excel.Worksheet sheet = (Excel.Worksheet)workbook.Sheets["Sheet1"]; //Access cells A1, A2, A3 Excel.Range range1 = sheet.get_Range("A1"); Excel.Range range2 = sheet.get_Range("A2"); Excel.Range range3 = sheet.get_Range("A3"); //Set values range1.Value = 100; range2.Value = 200; range3.Value = 400; //Add a conditional formatting and set its type to CellValue. Excel.XlFormatConditionOperator operate = Excel.XlFormatConditionOperator.xlLess; string formula = "300"; Excel.FormatCondition format = sheet.get_Range("A1", "A3").FormatConditions.Add(Excel.XlFormatConditionType.xlCellValue, operate, formula, missing, missing, missing, missing, missing); format.Font.Color = Color.Red; //Save the file workbook.SaveAs("ConditionalFormatting.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: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