Working with Drawing Objects

20 Sep 202324 minutes to read

Form Controls

You can add and manipulate the text Box, option button, check box, and combo box controls into the worksheet. Enable these controls to create forms which are very user friendly.

NOTE

Support for Active X Form controls is not yet available.

This section explains the usage of the following Form Controls.

Text Box
Check Box
Combo Box
Option Button

Text Box

The ITextBoxShape interface represents a text box in a worksheet. Various properties like Horizontal and Vertical Alignment, Alternative Text, Text Rotation, and so on are also supported.

The following code example illustrates how to add and manipulate a text box control.

C# [Cross-platform]
C# [Windows-specific]
VB.NET [Windows-specific]
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //Creates a new Text Box
  ITextBoxShape textbox = sheet.TextBoxes.AddTextBox(2, 2, 30, 200);
  textbox.Text = "Text Box 1";
  textbox = sheet.TextBoxes.AddTextBox(6, 2, 30, 200);
  textbox.Text = "Text Box 2";

  //Reads a Text Box
  ITextBoxShape shape1 = sheet.TextBoxes[0];
  shape1.Text = "TextBox";

  //Format the control
  shape1.Fill.ForeColor = Color.Gold;
  shape1.Fill.BackColor = Color.Black;
  shape1.Fill.Pattern = ExcelGradientPattern.Pat_90_Percent;

  //Remove a Text Box
  ITextBoxShape shape2 = sheet.TextBoxes[1];
  shape2.Remove();

  //Saving the workbook as stream
  FileStream stream = new FileStream("TextBox.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //Creates a new Text Box
  ITextBoxShape textbox = sheet.TextBoxes.AddTextBox(2, 2, 30, 200);
  textbox.Text = "Text Box 1";
  textbox = sheet.TextBoxes.AddTextBox(6, 2, 30, 200);
  textbox.Text = "Text Box 2";

  //Reads a Text Box
  ITextBoxShape shape1 = sheet.TextBoxes[0];
  shape1.Text = "TextBox";

  //Format the control
  shape1.Fill.ForeColor = Color.Gold;
  shape1.Fill.BackColor = Color.Black;
  shape1.Fill.Pattern = ExcelGradientPattern.Pat_90_Percent;

  //Remove a Text Box
  ITextBoxShape shape2 = sheet.TextBoxes[1];
  shape2.Remove();

  workbook.SaveAs("Textbox.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Excel2013
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Creates a new Text Box
  Dim textbox As ITextBoxShape = sheet.TextBoxes.AddTextBox(2, 2, 30, 200)
  textbox.Text = "Text Box 1"
  textbox = sheet.TextBoxes.AddTextBox(6, 2, 30, 200)
  textbox.Text = "Text Box 2"

  'Reads a Text Box
  Dim shape1 As ITextBoxShape = sheet.TextBoxes(0)
  shape1.Text = "TextBox"

  'Format the control
  shape1.Fill.ForeColor = Color.Gold
  shape1.Fill.BackColor = Color.Black
  shape1.Fill.Pattern = ExcelGradientPattern.Pat_90_Percent

  'Remove a Text Box
  Dim shape2 As ITextBoxShape = sheet.TextBoxes(1)
  shape2.Remove()

  workbook.SaveAs("Textbox.xlsx")
End Using

A complete working example to add a text box in C# is present on this GitHub page.

Text Box in Chart

Syncfusion XlsIO supports creating and reading text box within Excel chart. The following code example illustrates this.

C# [Cross-platform]
C# [Windows-specific]
VB.NET [Windows-specific]
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  FileStream inputStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(inputStream, ExcelOpenType.Automatic);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Read the text 
  for (int chartIndex = 0; chartIndex < worksheet.Charts.Count; chartIndex++)
  {
    for (int textBoxIndex = 0; textBoxIndex < worksheet.Charts[chartIndex].TextBoxes.Count; textBoxIndex++)
    {
      String text = worksheet.Charts[chartIndex].TextBoxes[textBoxIndex].Text;
    }
  }

  //Creates a new text box in the chart 
  ITextBoxShape textbox = worksheet.Charts[0].TextBoxes.AddTextBox(2, 2, 80, 300);
  textbox.Text = "Text Box in the chart";

  //Saving the workbook as stream
  FileStream outputStream = new FileStream(Output.xlsx, FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(outputStream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;

  IWorkbook workbook = application.Workbooks.Open("Sample.xlsx", ExcelOpenType.Automatic);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Read the text 
  for (int chartIndex = 0; chartIndex < worksheet.Charts.Count; chartIndex++)
  {
    for (int textBoxIndex = 0; textBoxIndex < worksheet.Charts[chartIndex].TextBoxes.Count; textBoxIndex++)
    {
      String text = worksheet.Charts[chartIndex].TextBoxes[textBoxIndex].Text;
    }
  }

  //Creates a new text box in the chart 
  ITextBoxShape textbox = worksheet.Charts[0].TextBoxes.AddTextBox(2, 2, 80, 300);
  textbox.Text = "Text Box in the chart";

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx

  Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  Dim chartIndex As Integer
  Dim textBoxIndex As Integer

  For chartIndex = 0 To worksheet.Charts.Count - 1 Step chartIndex + 1
    For textBoxIndex = 0 To worksheet.Charts(chartIndex).TextBoxes.Count - 1 Step textBoxIndex + 1
      Dim text As String = worksheet.Charts(chartIndex).TextBoxes(textBoxIndex).Text
    Next
  Next

  Dim textbox As ITextBox = worksheet.Charts(0).TextBoxes.AddTextBox(2, 2, 80, 300)
  textbox.Text = "Text Box in the chart"

  workbook.SaveAs("Output.xlsx")
End Using

Check Box

ICheckBoxShape object represents a check box in a worksheet. The following code example illustrates how to insert and manipulate a check box control.

C# [Cross-platform]
C# [Windows-specific]
VB.NET [Windows-specific]
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //Create a check box with cell link
  ICheckBoxShape checkBoxRed = sheet.CheckBoxes.AddCheckBox(2, 4, 20, 75);
  checkBoxRed.Text = "Red";
  checkBoxRed.CheckState = ExcelCheckState.Unchecked;
  checkBoxRed.LinkedCell = sheet["B2"];
  ICheckBoxShape checkBoxBlue = sheet.CheckBoxes.AddCheckBox(4, 4, 20, 75);
  checkBoxBlue.Text = "Blue";
  checkBoxBlue.CheckState = ExcelCheckState.Checked;
  checkBoxBlue.LinkedCell = sheet["B4"];

  //Read a check box
  checkBoxRed = sheet.CheckBoxes[0];
  checkBoxRed.CheckState = ExcelCheckState.Checked;

  //Remove a check box
  checkBoxBlue = sheet.CheckBoxes[1];
  checkBoxBlue.Remove();

  //Saving the workbook as stream
  FileStream stream = new FileStream("CheckBox.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //Create a check box with cell link
  ICheckBoxShape checkBoxRed = sheet.CheckBoxes.AddCheckBox(2, 4, 20, 75);
  checkBoxRed.Text = "Red";
  checkBoxRed.CheckState = ExcelCheckState.Unchecked;
  checkBoxRed.LinkedCell = sheet["B2"];
  ICheckBoxShape checkBoxBlue = sheet.CheckBoxes.AddCheckBox(4, 4, 20, 75);
  checkBoxBlue.Text = "Blue";
  checkBoxBlue.CheckState = ExcelCheckState.Checked;
  checkBoxBlue.LinkedCell = sheet["B4"];

  //Read a check box
  checkBoxRed = sheet.CheckBoxes[0];
  checkBoxRed.CheckState = ExcelCheckState.Checked;

  //Remove a check box
  checkBoxBlue = sheet.CheckBoxes[1];
  checkBoxBlue.Remove();

  workbook.SaveAs("Checkbox.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Excel2013
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Create a check box with cell link
  Dim checkBoxRed As ICheckBoxShape = sheet.CheckBoxes.AddCheckBox(2, 4, 20, 75)
  checkBoxRed.Text = "Red"
  checkBoxRed.CheckState = ExcelCheckState.Unchecked
  checkBoxRed.LinkedCell = sheet("B2")
  Dim checkBoxBlue As ICheckBoxShape = sheet.CheckBoxes.AddCheckBox(4, 4, 20, 75)
  checkBoxBlue.Text = "Blue"
  checkBoxBlue.CheckState = ExcelCheckState.Checked
  checkBoxBlue.LinkedCell = sheet("B4")

  'Read a check box
  checkBoxRed = sheet.CheckBoxes(0)
  checkBoxRed.CheckState = ExcelCheckState.Checked

  'Remove a check box
  checkBoxBlue = sheet.CheckBoxes(1)
  checkBoxBlue.Remove()

  workbook.SaveAs("Checkbox.xlsx")
End Using

A complete working example to add a check box in C# is present on this GitHub page.

Combo Box

IComboBoxShape object represents a combo box in a worksheet. The following code example illustrates how to insert and manipulate a combo box control.

C# [Cross-platform]
C# [Windows-specific]
VB.NET [Windows-specific]
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //Filling Values
  sheet["A2"].Text = "RGB colors";
  sheet["A3"].Text = "Red";
  sheet["A4"].Text = "Green";
  sheet["A5"].Text = "Blue";
  sheet["B5"].Text = "Selected Index";

  //Create a Combo Box
  IComboBoxShape comboBox1 = sheet.ComboBoxes.AddComboBox(2, 3, 20, 100);
  //Assign a value to the Combo Box
  comboBox1.ListFillRange = sheet["A3:A5"];
  comboBox1.LinkedCell = sheet["C5"];
  comboBox1.SelectedIndex = 2;

  //Create a Combo Box
  IComboBoxShape comboBox2 = sheet.ComboBoxes.AddComboBox(5, 3, 20, 100);
  //Assign a value to the Combo Box
  comboBox2.ListFillRange = sheet["A3:A5"];
  comboBox2.LinkedCell = sheet["C5"];
  comboBox2.SelectedIndex = 1;

  //Read a Combo Box
  comboBox1 = sheet.ComboBoxes[0];
  comboBox1.SelectedIndex = 1;

  //Remove a Combo Box
  comboBox2 = sheet.ComboBoxes[1];
  comboBox2.Remove();

  //Saving the workbook as stream
  FileStream stream = new FileStream("ComboBox.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //Filling Values
  sheet["A2"].Text = "RGB colors";
  sheet["A3"].Text = "Red";
  sheet["A4"].Text = "Green";
  sheet["A5"].Text = "Blue";
  sheet["B5"].Text = "Selected Index";

  //Create a Combo Box
  IComboBoxShape comboBox1 = sheet.ComboBoxes.AddComboBox(2, 3, 20, 100);
  //Assign a value to the Combo Box
  comboBox1.ListFillRange = sheet["A3:A5"];
  comboBox1.LinkedCell = sheet["C5"];
  comboBox1.SelectedIndex = 2;

  //Create a Combo Box
  IComboBoxShape comboBox2 = sheet.ComboBoxes.AddComboBox(5, 3, 20, 100);
  //Assign a value to the Combo Box
  comboBox2.ListFillRange = sheet["A3:A5"];
  comboBox2.LinkedCell = sheet["C5"];
  comboBox2.SelectedIndex = 1;

  //Read a Combo Box
  comboBox1 = sheet.ComboBoxes[0];
  comboBox1.SelectedIndex = 1;

  //Remove a Combo Box
  comboBox2 = sheet.ComboBoxes[1];
  comboBox2.Remove();

  workbook.SaveAs("Combobox.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Excel2013
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Filling Values
  sheet("A2").Text = "RGB colors"
  sheet("A3").Text = "Red"
  sheet("A4").Text = "Green"
  sheet("A5").Text = "Blue"
  sheet("B5").Text = "Selected Index"

  'Create a Combo Box
  Dim comboBox1 As IComboBoxShape = sheet.ComboBoxes.AddComboBox(2, 3, 20, 100)
  'Assign a value to the Combo Box
  comboBox1.ListFillRange = sheet("A3:A5")
  comboBox1.LinkedCell = sheet("C5")
  comboBox1.SelectedIndex = 2

  'Create a Combo Box
  Dim comboBox2 As IComboBoxShape = sheet.ComboBoxes.AddComboBox(5, 3, 20, 100)
  'Assign a value to the Combo Box
  comboBox2.ListFillRange = sheet("A3:A5")
  comboBox2.LinkedCell = sheet("C5")
  comboBox2.SelectedIndex = 1

  'Read a Combo Box
  comboBox1 = sheet.ComboBoxes(0)
  comboBox1.SelectedIndex = 1

  'Remove a Combo Box
  comboBox2 = sheet.ComboBoxes(1)
  comboBox2.Remove()

  workbook.SaveAs("Combobox.xlsx")
End Using

A complete working example to add a combo box in C# is present on this GitHub page.

Option Button

IOptionButtonShape object represents an option button in a worksheet. The following code example illustrates how to insert and manipulate an option button control.

NOTE

XlsIO provides Option button support only for XLSX format.

C# [Cross-platform]
C# [Windows-specific]
VB.NET [Windows-specific]
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //Create an Option Button
  IOptionButtonShape optionButton1 = sheet.OptionButtons.AddOptionButton(2, 3);
  //Assign a value to the Option Button
  optionButton1.Text = "Fed Ex";

  //Format the control
  optionButton1.Fill.FillType = ExcelFillType.SolidColor;
  optionButton1.Fill.ForeColor = Color.Yellow;
  //Change the check state
  optionButton1.CheckState = ExcelCheckState.Checked;

  //Create an Option Button
  IOptionButtonShape optionButton2 = sheet.OptionButtons.AddOptionButton(5, 3);
  //Assign a value to the Option Button
  optionButton2.Text = "DHL";

  //Read an Option Button
  optionButton1 = sheet.OptionButtons[0];
  optionButton1.CheckState = ExcelCheckState.Unchecked;

  //Remove an Option Button
  optionButton2 = sheet.OptionButtons[1];
  optionButton2.Remove();

  //Saving the workbook as stream
  FileStream stream = new FileStream("OptionButton.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //Create an Option Button
  IOptionButtonShape optionButton1 = sheet.OptionButtons.AddOptionButton(2, 3);
  //Assign a value to the Option Button
  optionButton1.Text = "Fed Ex";

  //Format the control
  optionButton1.Fill.FillType = ExcelFillType.SolidColor;
  optionButton1.Fill.ForeColor = Color.Yellow;
  //Change the check state
  optionButton1.CheckState = ExcelCheckState.Checked;

  //Create an Option Button
  IOptionButtonShape optionButton2 = sheet.OptionButtons.AddOptionButton(5, 3);
  //Assign a value to the Option Button
  optionButton2.Text = "DHL";

  //Read an Option Button
  optionButton1 = sheet.OptionButtons[0];
  optionButton1.CheckState = ExcelCheckState.Unchecked;

  //Remove an Option Button
  optionButton2 = sheet.OptionButtons[1];
  optionButton2.Remove();

  workbook.SaveAs("OptionButton.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Excel2013
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Create an Option Button
  Dim optionButton1 As IOptionButtonShape = sheet.OptionButtons.AddOptionButton(2, 3)
  'Assign a value to the Option Button
  optionButton1.Text = "Fed Ex"

  'Format the control
  optionButton1.Fill.FillType = ExcelFillType.SolidColor
  optionButton1.Fill.ForeColor = Color.Yellow
  'Change the check state
  optionButton1.CheckState = ExcelCheckState.Checked

  'Create an Option Button
  Dim optionButton2 As IOptionButtonShape = sheet.OptionButtons.AddOptionButton(5, 3)
  'Assign a value to the Option Button
  optionButton2.Text = "DHL"

  'Read an Option Button
  optionButton1 = sheet.OptionButtons(0)
  optionButton1.CheckState = ExcelCheckState.Unchecked

  'Remove an Option Button
  optionButton2 = sheet.OptionButtons(1)
  optionButton2.Remove()

  workbook.SaveAs("OptionButton.xlsx")
End Using

A complete working example to add option button in C# is present on this GitHub page.

Comments

ICommentShape object represents a comment in a worksheet. You can insert both Regular and Rich Text comments. The following code example illustrates how to insert and manipulate comments.

C# [Cross-platform]
C# [Windows-specific]
VB.NET [Windows-specific]
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //Adding comments to a cell
  sheet.Range["A1"].AddComment().Text = "Comments";

  //Add Rich Text Comments
  IRange range = sheet.Range["A6"];
  range.AddComment().RichText.Text = "RichText";
  IRichTextString richText = range.Comment.RichText;

  //Formatting first 4 characters
  IFont redFont = workbook.CreateFont();
  redFont.Bold = true;
  redFont.Color = ExcelKnownColors.Red;
  richText.SetFont(0, 3, redFont);

  //Saving the workbook as stream
  FileStream stream = new FileStream("Comments.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //Adding comments to a cell
  sheet.Range["A1"].AddComment().Text = "Comments";

  //Add Rich Text Comments
  IRange range = sheet.Range["A6"];
  range.AddComment().RichText.Text = "RichText";
  IRichTextString richText = range.Comment.RichText;

  //Formatting first 4 characters
  IFont redFont = workbook.CreateFont();
  redFont.Bold = true;
  redFont.Color = ExcelKnownColors.Red;
  richText.SetFont(0, 3, redFont);

  workbook.SaveAs("Comments.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Excel2013
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Adding comments to a cell
  sheet.Range("A1").AddComment().Text = "Comments"

  'Add Rich Text Comments
  Dim range As IRange = sheet.Range("A6")
  range.AddComment().RichText.Text = "RichText"
  Dim richText As IRichTextString = range.Comment.RichText

  'Formatting first 4 characters
  Dim redFont As IFont = workbook.CreateFont()
  redFont.Bold = True
  redFont.Color = ExcelKnownColors.Red
  richText.SetFont(0, 3, redFont)

  workbook.SaveAs("Comments.xlsx")
End Using

A complete working example to add comment in C# is present on this GitHub page.

You can also fill the comments with various types of fills by using the IFill interface. Following code example illustrates how to fill the comment shape with a TwoColor gradient.

C# [Cross-platform]
C# [Windows-specific]
VB.NET [Windows-specific]
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //Adding comments to a cell
  sheet.Range["A1"].AddComment().Text = "Comments";

  //Accessing existing comment
  ICommentShape shape = sheet.Range["A1"].Comment;

  //Format the comment
  shape.Fill.TwoColorGradient();
  shape.Fill.GradientStyle = ExcelGradientStyle.Horizontal;
  shape.Fill.GradientColorType = ExcelGradientColor.TwoColor;
  shape.Fill.ForeColorIndex = ExcelKnownColors.Red;
  shape.Fill.BackColorIndex = ExcelKnownColors.White;

  //Saving the workbook as stream
  FileStream stream = new FileStream("FormatComments.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //Adding comments to a cell
  sheet.Range["A1"].AddComment().Text = "Comments";

  //Accessing existing comment
  ICommentShape shape = sheet.Range["A1"].Comment;

  //Format the comment
  shape.Fill.TwoColorGradient();
  shape.Fill.GradientStyle = ExcelGradientStyle.Horizontal;
  shape.Fill.GradientColorType = ExcelGradientColor.TwoColor;
  shape.Fill.ForeColorIndex = ExcelKnownColors.Red;
  shape.Fill.BackColorIndex = ExcelKnownColors.White;

  workbook.SaveAs("FormatComments.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Excel2013
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Adding comments to a cell
  sheet.Range("A1").AddComment().Text = "Comments"

  'Accessing existing comment
  Dim shape As ICommentShape = sheet.Range("A1").Comment

  'Format the comment
  shape.Fill.TwoColorGradient()
  shape.Fill.GradientStyle = ExcelGradientStyle.Horizontal
  shape.Fill.GradientColorType = ExcelGradientColor.TwoColor
  shape.Fill.ForeColorIndex = ExcelKnownColors.Red
  shape.Fill.BackColorIndex = ExcelKnownColors.White

  workbook.SaveAs("FormatComments.xlsx")
End Using

A complete working example to fill comment in C# is present on this GitHub page.

Show or Hide Excel Comments

Comments in an Excel document can be shown or hidden using IsVisible property. The following code example illustrates this.

C# [Cross-platform]
C# [Windows-specific]
VB.NET [Windows-specific]
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Adding comments in the worksheet
  worksheet.Range["E5"].AddComment();
  worksheet.Range["E15"].AddComment();

  //Adding text in comments
  worksheet.Comments[0].Text = "Comment1";
  worksheet.Comments[1].Text = "Comment2";

  //Show comment
  worksheet.Comments[0].IsVisible = true;
  //Hide comment
  worksheet.Comments[1].IsVisible = false;

  //Saving the workbook as stream
  FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Adding comments in the worksheet
  worksheet.Range["E5"].AddComment();
  worksheet.Range["E15"].AddComment();

  //Adding text in comments
  worksheet.Comments[0].Text = "Comment1";
  worksheet.Comments[1].Text = "Comment2";

  //Show comment
  worksheet.Comments[0].IsVisible = true;
  //Hide comment
  worksheet.Comments[1].IsVisible = false;

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Excel2013
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Adding comments in the worksheet
  worksheet.Range("E5").AddComment()
  worksheet.Range("E15").AddComment()

  'Adding text in comments
  worksheet.Comments(0).Text = "Comment1"
  worksheet.Comments(1).Text = "Comment2"

  'Show comment
  worksheet.Comments(0).IsVisible = True
  'Hide comment
  worksheet.Comments(1).IsVisible = False

  workbook.SaveAs("Output.xlsx")
End Using

A complete working example to show or hide comment in C# is present on this GitHub page.

Following code snippets illustrates how to remove all the comments in existing worksheet.

C# [Cross-platform]
C# [Windows-specific]
VB.NET [Windows-specific]
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  FileStream inputStream = new FileStream("Comments.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(inputStream);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Remove all the comments in worksheet
  worksheet.Comments.Clear();

  //Saving the workbook as stream
  FileStream stream = new FileStream("RemoveComments.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.Version = ExcelVersion.Excel2013;
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  IWorkbook workbook = application.Workbooks.Open("Comments.xlsx");
  IWorksheet sheet = workbook.Worksheets[0];

  //Remove all the comments in worksheet
  sheet.Comments.Clear();

  workbook.Version = ExcelVersion.Excel2013;
  workbook.SaveAs("RemoveComments.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  Dim workbook As IWorkbook = application.Workbooks.Open("Comments.xlsx")
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Remove all the comments in worksheet
  sheet.Comments.Clear()

  workbook.Version = ExcelVersion.Excel2013
  workbook.SaveAs("RemoveComments.xlsx")
End Using

A complete working example to remove comment in C# is present on this GitHub page.

Threaded Comments

Threaded comments are a way to add and organize annotations or discussions related to specific cells in a worksheet. IThreadedComment object represents a threaded comment in a worksheet.

Create

The following code explains how to create a threaded comment for a specific cell using AddThreadedComment method.

C# [Cross-platform]
C# [Windows-specific]
VB.NET [Windows-specific]
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  FileStream inputStream = new FileStream("CommentsTemplate.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(inputStream, ExcelOpenType.Automatic);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Add threaded comment
  worksheet.Range["H16"].AddThreadedComment("What is the reason for the higher total amount of \"desk\"  in the west region?", "User1", DateTime.Now);

  //Saving the workbook as stream
  FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Open("CommentsTemplate.xlsx", ExcelOpenType.Automatic);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Add threaded comment
  worksheet.Range["H16"].AddThreadedComment("What is the reason for the higher total amount of \"desk\"  in the west region?", "User1", DateTime.Now);

  //Saving the workbook
  workbook.SaveAs("Ouptput.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine

  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Open("CommentsTemplate.xlsx", ExcelOpenType.Automatic)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Add threaded comment
  worksheet.Range("H16").AddThreadedComment("What is the reason for the higher total amount of ""desk""  in the west region?", "User1", DateTime.Now)

  'Saving the workbook  
  workbook.SaveAs("Output.xlsx")
End Using

A complete working example to create a threaded comment in C# is present on this GitHub page.

Reply

The following code adds a replies to an existing threaded comment using the AddReply method of IThreadedComment.

C# [Cross-platform]
C# [Windows-specific]
VB.NET [Windows-specific]
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  FileStream inputStream = new FileStream("CommentsTemplate.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(inputStream, ExcelOpenType.Automatic);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Add replies to the threaded comement
  worksheet.Range["H16"].ThreadedComment.AddReply("The unit cost of desk is higher compared to other items in the west region. As a result, the total amount is elevated.", "User2", DateTime.Now);
  worksheet.Range["H16"].ThreadedComment.AddReply("Additionally, Wilson sold 31 desks in the west region, which is also a contributing factor to the increased total amount.", "User3", DateTime.Now);

  //Saving the workbook as stream
  FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Open("CommentsTemplate.xlsx", ExcelOpenType.Automatic);
  IWorksheet worksheet = workbook.Worksheets[0];
  
  //Add replies to the threaded comement
  worksheet.Range["H16"].ThreadedComment.AddReply("The unit cost of desk is higher compared to other items in the west region. As a result, the total amount is elevated.", "User2", DateTime.Now);
  worksheet.Range["H16"].ThreadedComment.AddReply("Additionally, Wilson sold 31 desks in the west region, which is also a contributing factor to the increased total amount.", "User3", DateTime.Now);

  //Saving the workbook
  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine

  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Open("CommentsTemplate.xlsx", ExcelOpenType.Automatic)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Add replies to the threaded comement
  worksheet.Range("H16").ThreadedComment.AddReply("The unit cost of desk is higher compared to other items in the west region. As a result, the total amount is elevated.", "User2", DateTime.Now)
  worksheet.Range("H16").ThreadedComment.AddReply("Additionally, Wilson sold 31 desks in the west region, which is also a contributing factor to the increased total amount.", "User3", DateTime.Now)

  'Saving the workbook  
  workbook.SaveAs("Output.xlsx")
End Using

A complete working example to add replies in a existing threaded comment in C# is present on this GitHub page.

The following screenshot represents the output Excel file of threaded comments generated by the XlsIO.

Mark as resolved

The threaded comment discussion can be marked as resolved by enabling the IsResolved property. By default, the value is set to false.

C# [Cross-platform]
C# [Windows-specific]
VB.NET [Windows-specific]
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  FileStream inputStream = new FileStream("CommentsTemplate.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(inputStream, ExcelOpenType.Automatic);
  IWorksheet worksheet = workbook.Worksheets[0];
  
  //Get the collection of threaded comments in the worksheet
  IThreadedComments threadedComments = worksheet.ThreadedComments;

  //Resolve the thread
  threadedComments[0].IsResolved = true;

  //Saving the workbook as stream
  FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Open("CommentsTemplate.xlsx", ExcelOpenType.Automatic);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Get the collection of threaded comments in the worksheet
  IThreadedComments threadedComments = worksheet.ThreadedComments;

  //Resolve the thread
  threadedComments[0].IsResolved = true;

  //Saving the workbook
  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine

  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Open("CommentsTemplate.xlsx", ExcelOpenType.Automatic)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Get the collection of threaded comments in the worksheet
  Dim threadedComments As IThreadedComments = worksheet.ThreadedComments

  'Resolve the thread
  threadedComments[0].IsResolved = True

  'Saving the workbook  
  workbook.SaveAs("Output.xlsx")
End Using

A complete working example to mark a treaded comment as resolved in C# is present on this GitHub page.

Delete

The following code shows how to delete a threaded comment from the collection of threaded comments using the Delete method.

C# [Cross-platform]
C# [Windows-specific]
VB.NET [Windows-specific]
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  FileStream inputStream = new FileStream("CommentsTemplate.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(inputStream, ExcelOpenType.Automatic);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Get the collection of threaded comments in the worksheet
  IThreadedComments threadedComments = worksheet.ThreadedComments;

  //Delete the threaded comment
  threadedComments[0].Delete();

  //Saving the workbook as stream
  FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Open("CommentsTemplate.xlsx", ExcelOpenType.Automatic);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Get the collection of threaded comments in the worksheet
  IThreadedComments threadedComments = worksheet.ThreadedComments;

  //Delete the threaded comment
  threadedComments[0].Delete();

  //Saving the workbook
  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine

  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Open("CommentsTemplate.xlsx", ExcelOpenType.Automatic)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Get the collection of threaded comments in the worksheet
  Dim threadedComments As IThreadedComments = worksheet.ThreadedComments

  'Delete the threaded comment
  threadedComments[0].Delete()

  'Saving the workbook  
  workbook.SaveAs("Output.xlsx")
End Using

Clear

The following code clears all the threaded comments in the worksheet using the Clear method of IThreadedComments.

C# [Cross-platform]
C# [Windows-specific]
VB.NET [Windows-specific]
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  FileStream inputStream = new FileStream("CommentsTemplate.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(inputStream, ExcelOpenType.Automatic);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Get the collection of threaded comments in the worksheet
  IThreadedComments threadedComments = worksheet.ThreadedComments;

  //Clear all the threaded comments
  threadedComments.Clear();

  //Saving the workbook as stream
  FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Open("CommentsTemplate.xlsx", ExcelOpenType.Automatic);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Get the collection of threaded comments in the worksheet
  IThreadedComments threadedComments = worksheet.ThreadedComments;

  //Clear all the threaded comments
  threadedComments.Clear();

  //Saving the workbook
  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine

  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Open("CommentsTemplate.xlsx", ExcelOpenType.Automatic)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

 'Get the collection of threaded comments in the worksheet
  Dim threadedComments As IThreadedComments = worksheet.ThreadedComments

  'Clear all the threaded comments
  threadedComments.Clear()

  'Saving the workbook  
  workbook.SaveAs("Output.xlsx")
End Using

AutoShapes

The IShape interface represents an AutoShape in an Excel workbook.

To learn more about various AutoShape types supported in XlsIO, refer to the AutoShapeType enumeration in API section.

The following code example illustrates how to insert and format AutoShapes.

C# [Cross-platform]
C# [Windows-specific]
VB.NET [Windows-specific]
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Adding an AutoShape
  IShape shape1 = worksheet.Shapes.AddAutoShapes(AutoShapeType.RoundedRectangle, 2, 7, 60, 192);
  IShape shape2 = worksheet.Shapes.AddAutoShapes(AutoShapeType.CircularArrow, 8, 7, 60, 192);

  //Set the value inside the shape
  shape1.TextFrame.TextRange.Text = "AutoShape";

  //Format the shape
  shape1.Fill.ForeColorIndex = ExcelKnownColors.Light_blue;
  shape1.TextFrame.VerticalAlignment = ExcelVerticalAlignment.MiddleCentered;

  //Read an AutoShape
  shape1 = worksheet.Shapes[0];
  shape1.TextFrame.TextRange.Text = "RoundedRectangle";

  //Remove an AutoShape
  shape2 = worksheet.Shapes[1];
  shape2.Remove();

  //Saving the workbook as stream
  FileStream stream = new FileStream("AutoShapes.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Adding an AutoShape
  IShape shape1 = worksheet.Shapes.AddAutoShapes(AutoShapeType.RoundedRectangle, 2, 7, 60, 192);
  IShape shape2 = worksheet.Shapes.AddAutoShapes(AutoShapeType.CircularArrow, 8, 7, 60, 192);

  //Set the value inside the shape
  shape1.TextFrame.TextRange.Text = "AutoShape";

  //Format the shape
  shape1.Fill.ForeColorIndex = ExcelKnownColors.Light_blue;
  shape1.TextFrame.VerticalAlignment = ExcelVerticalAlignment.MiddleCentered;

  //Read an AutoShape
  shape1 = worksheet.Shapes[0];
  shape1.TextFrame.TextRange.Text = "RoundedRectangle";

  //Remove an AutoShape
  shape2 = worksheet.Shapes[1];
  shape2.Remove();

  workbook.SaveAs("Autoshapes.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Excel2013
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Adding an AutoShape
  Dim shape1 As IShape = worksheet.Shapes.AddAutoShapes(AutoShapeType.RoundedRectangle, 2, 7, 60, 192)
  Dim shape2 As IShape = worksheet.Shapes.AddAutoShapes(AutoShapeType.CircularArrow, 8, 7, 60, 192)

  'Set the value inside the shape.
  shape1.TextFrame.TextRange.Text = "AutoShape"

  'Format the shape
  shape1.Fill.ForeColorIndex = ExcelKnownColors.Light_blue
  shape1.TextFrame.VerticalAlignment = ExcelVerticalAlignment.MiddleCentered

  'Read an AutoShape
  shape1 = worksheet.Shapes(0)
  shape1.TextFrame.TextRange.Text = "RoundedRectangle"

  'Remove an AutoShape
  shape2 = worksheet.Shapes(1)
  shape2.Remove()

  workbook.SaveAs("Autoshapes.xlsx")
End Using

A complete working example to add AutoShapes in C# is present on this GitHub page.

Group Shapes

Group shape can be used to work with multiple shapes at a single instant.
For example, you can change positions, size or set colors for all shapes at the same time through a single group shape.

Create Group Shapes

The shapes in the worksheet can be grouped into a single shape by creating group shape in XlsIO using IGroupShape interface.

The following code example illustrates how to create a group shape.

C# [Cross-platform]
C# [Windows-specific]
VB.NET [Windows-specific]
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
FileStream inputStream = new FileStream("GroupShape.xlsx", FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(inputStream);
IWorksheet worksheet = workbook.Worksheets[0];

IShapes shapes = worksheet.Shapes;

// Select the shapes you want to group
IShape[] groupItems = new IShape[] { shapes[0], shapes[1] };

// Group the selected shapes
IGroupShape GroupShape = shapes.Group(groupItems);

FileStream file = new FileStream("GroupShape.xlsx", FileMode.Create, FileAccess.ReadWrite);
workbook.SaveAs(file);
file.Dispose();
workbook.Close();
excelEngine.Dispose();
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("GroupShape.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];

IShapes shapes = worksheet.Shapes;

// Select the shapes you want to group	
IShape[] groupItems = new IShape[] { shapes[0], shapes[1] };

// Group the selected shapes
IGroupShape GroupShape = shapes.Group(groupItems);

workbook.SaveAs("GroupShape.xlsx");
workbook.Close();
excelEngine.Dispose();
Dim excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
Dim workbook As IWorkbook = application.Workbooks.Open("GroupShape.xlsx")
Dim worksheet As IWorksheet = workbook.Worksheets(0)
	
Dim shapes As IShapes = worksheet.Shapes

' Select the shapes you want to group	
Dim groupItems As IShape() = New IShape() {shapes(0), shapes(1)}

' Group the selected shapes
Dim GroupShape As IGroupShape = shapes.Group(groupItems)
	
workbook.SaveAs("GroupShape.xlsx")	
workbook.Close()	
excelEngine.Dispose()

A complete working example to group shapes in C# is present on this GitHub page.

Ungroup shapes

Group shape can be ungrouped, and its inner shapes are added to worksheet as an individual shape.

The following code example illustrates how to ungroup the shape.

C# [Cross-platform]
C# [Windows-specific]
VB.NET [Windows-specific]
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
FileStream inputStream = new FileStream("GroupShape.xlsx", FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(inputStream);
IWorksheet worksheet = workbook.Worksheets[0];

IShapes shapes = worksheet.Shapes;

// Ungroup the selected specified group shape
shapes.Ungroup(shapes[0] as IGroupShape);

FileStream file = new FileStream("GroupShape.xlsx", FileMode.Create, FileAccess.ReadWrite);
workbook.SaveAs(file);
file.Dispose();
workbook.Close();
excelEngine.Dispose();
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("GroupShape.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];

IShapes shapes = worksheet.Shapes;

// Ungroup the selected specified group shape
shapes.Ungroup(shapes[0] as IGroupShape);

workbook.SaveAs("GroupShape.xlsx");
workbook.Close();
excelEngine.Dispose();
Dim excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
Dim workbook As IWorkbook = application.Workbooks.Open("GroupShape.xlsx")
Dim worksheet As IWorksheet = workbook.Worksheets(0)
	
Dim shapes As IShapes = worksheet.Shapes

' Ungroup the selected specified group shape
shapes.Ungroup(TryCast(shapes(0), IGroupShape))
	
workbook.SaveAs("GroupShape.xlsx")	
workbook.Close()	
excelEngine.Dispose()

A complete working example to ungroup shapes in C# is present on this GitHub page.

Ungroup all shapes

When ungrouping the group shape, its immediate inner shapes only be ungrouped. Ungroup the group shape and its all the inner shapes can be possible in XlsIO.
In Ungroup method, isAll boolean value indicates whether the group inner shape will be grouped or not.

The following code example illustrates how to ungroup the group shape and its inner shapes.

C# [Cross-platform]
C# [Windows-specific]
VB.NET [Windows-specific]
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
FileStream inputStream = new FileStream("GroupShape.xlsx", FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(inputStream);
IWorksheet worksheet = workbook.Worksheets[0];

IShapes shapes = worksheet.Shapes;

// Ungroup the selected specified group shape and its inner shapes by specifying isAll property
shapes.Ungroup(shapes[0] as IGroupShape, true);

FileStream file = new FileStream("GroupShape.xlsx", FileMode.Create, FileAccess.ReadWrite);
workbook.SaveAs(file);
file.Dispose();
workbook.Close();
excelEngine.Dispose();
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
IWorkbook workbook = application.Workbooks.Open("GroupShape.xlsx");
IWorksheet worksheet = workbook.Worksheets[0];

IShapes shapes = worksheet.Shapes;

// Ungroup the selected specified group shape and its inner shapes by specifying isAll property
shapes.Ungroup(shapes[0] as IGroupShape, true);

workbook.SaveAs("GroupShape.xlsx");
workbook.Close();
excelEngine.Dispose();
Dim excelEngine As ExcelEngine = New ExcelEngine()
Dim application As IApplication = excelEngine.Excel
Dim workbook As IWorkbook = application.Workbooks.Open("GroupShape.xlsx")
Dim worksheet As IWorksheet = workbook.Worksheets(0)
	
Dim shapes As IShapes = worksheet.Shapes

' Ungroup the selected specified group shape and its inner shapes by specifying isAll property
shapes.Ungroup(TryCast(shapes(0), IGroupShape), True)
	
workbook.SaveAs("GroupShape.xlsx")	
workbook.Close()	
excelEngine.Dispose()

A complete working example to ungroup all shapes in C# is present on this GitHub page.

OLE Objects

IOleObject object represents an OLE Object in a worksheet.

NOTE

XlsIO supports OLE Objects for XLSX format in Windows, ASP.NET, and WPF platforms only.

OLE Objects and Linking Types

XlsIO supports two types of association of the objects:

Linked objects
Embedded objects

1. Linked Objects

Linked objects remains as a separate files. When the file is opened in another machine, then the linked object should be in the same location as created.

The following sample code illustrates how to link an OLE Object to an Excel document.

C# [Cross-platform]
C# [Windows-specific]
VB.NET [Windows-specific]
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Create image stream
  FileStream imageStream = new FileStream("image.png", FileMode.Open);
  //Get image from stream
  Image image = Image.FromStream(imageStream);

  //Add ole object
  IOleObject oleObject = worksheet.OleObjects.AddLink("../../Data/Document.docx", image);

  //Saving the workbook as stream
  FileStream stream = new FileStream("LinkedObjects.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  Image image = Image.FromFile("image.png");
  //Select the object, image for the display icon and the type of the OLEObject to insert
  IOleObject ole2 = sheet.OleObjects.Add("Document.docx", image, OleLinkType.Link);

  workbook.SaveAs("LinkedObjects.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Excel2013
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  Dim image As Image = Image.FromFile("image.png")
  'Select the object, image for the display icon and the type of the OLEObject to insert
  Dim ole2 As IOleObject = sheet.OleObjects.Add("Document.docx", image, OleLinkType.Link)

  workbook.SaveAs("LinkedObjects.xlsx")
End Using

2. Embedded Objects

Embedded objects are stored in the document. When the file is opened in another machine, the embedded object can be viewed without having access to the original data.

The following sample code illustrates how to embed an OLE Object to an Excel document.

C# [Cross-platform]
C# [Windows-specific]
VB.NET [Windows-specific]
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Create file stream and image stream
  FileStream inputStream = new FileStream("Test.pptx", FileMode.Open);
  FileStream imageStream = new FileStream("image.png", FileMode.Open);

  //Get image from stream
  Image image = Image.FromStream(imageStream);

  //Add ole object
  IOleObject oleObject = worksheet.OleObjects.Add(inputStream, image, OleObjectType.PowerPointPresentation);

  //Saving the workbook as stream
  FileStream stream = new FileStream("EmbeddedObjects.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  Image image = Image.FromFile("image.png");
  // Select the object, image for the display icon and the type of the OLEObject to insert
  IOleObject ole1 = sheet.OleObjects.Add("Test.pptx", image, OleLinkType.Embed);

  workbook.SaveAs("EmbeddedObjects.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Excel2013
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  Dim image As Image = Image.FromFile("image.png")
  'Select the object, image for the display icon and the type of the OLEObject to insert
  Dim ole1 As IOleObject = sheet.OleObjects.Add("Test.pptx", image, OleLinkType.Embed)

  workbook.SaveAs("EmbeddedObjects.xlsx")
End Using

The following code example illustrates how to insert and manipulate OLEObjects with their properties through IOleObjects interface.

C# [Cross-platform]
C# [Windows-specific]
VB.NET [Windows-specific]
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Create file stream and image stream
  FileStream inputStream1 = new FileStream("Employee.docx", FileMode.Open);
  FileStream inputStream2 = new FileStream("Customer.docx", FileMode.Open);
  FileStream imageStream = new FileStream("Images.png", FileMode.Open);

  //Get image from stream
  Image image = Image.FromStream(imageStream);

  //Add ole object
  IOleObject oleObject1 = worksheet.OleObjects.Add(inputStream1, image, OleObjectType.WordDocument);
  IOleObject oleObject2 = worksheet.OleObjects.Add(inputStream2, image, OleObjectType.WordDocument);

  //Displays image as icon
  oleObject1.DisplayAsIcon = true;

  //Set the location of the OleObject
  oleObject1.Location = worksheet["K8"];

  //Reads icon as a image
  Image image1 = oleObject1.Picture;

  //Reads OleObject as a IPictureShape
  IPictureShape shape = oleObject1.Shape;

  //Set the size of the OleObject
  oleObject1.Size = new Size(30, 30);

  //Remove OleObjects
  oleObject2 = worksheet.OleObjects[1];
  oleObject2.Shape.Remove();

  //Saving the workbook as stream
  FileStream stream = new FileStream("OleObjects.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //Icon image
  Image image = Image.FromFile("Images.png");

  //Select the object, image for the display icon and the type of the OLEObject to insert
  IOleObject oleObject1 = sheet.OleObjects.Add("Employee.docx", image, OleLinkType.Embed);

  //Select the object, image for the display icon and the type of the OLEObject to insert
  IOleObject oleObject2 = sheet.OleObjects.Add("Customer.docx", image, OleLinkType.Embed);

  //Displays image as icon
  oleObject1.DisplayAsIcon = true;

  //Set the location of the OleObject
  oleObject1.Location = sheet["K8"];

  //Reads icon as a image
  Image image1 = oleObject1.Picture;

  //Reads OleObject as a IPictureShape
  IPictureShape shape = oleObject1.Shape;

  //Set the size of the OleObject
  oleObject1.Size = new Size(30, 30);

  //Remove OleObjects
  oleObject2 = sheet.OleObjects[1];
  oleObject2.Shape.Remove();

  workbook.SaveAs("OleObjects.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Excel2013
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Icon image
  Dim image As Image = Image.FromFile("Images.png")

  'Select the object, image for the display icon and the type of the OLEObject to insert
  Dim oleObject1 As IOleObject = sheet.OleObjects.Add("Employee.docx", image, OleLinkType.Embed)

  'Select the object, image for the display icon and the type of the OLEObject to insert
  Dim oleObject2 As IOleObject = sheet.OleObjects.Add("Customer.docx", image, OleLinkType.Embed)

  'Displays image as icon
  oleObject1.DisplayAsIcon = True

  'Set the location of the OleObject
  oleObject1.Location = sheet("K8")

  'Reads icon as a image
  Dim image1 As Image = oleObject1.Picture

  'Reads OleObject as a IPictureShape
  Dim shape As IPictureShape = oleObject1.Shape

  'Set the size of the OleObject
  oleObject1.Size = New Size(30, 30)

  'Remove OleObjects
  oleObject2 = sheet.OleObjects(1)
  oleObject2.Shape.Remove()

  workbook.SaveAs("OleObjects.xlsx")
End Using