Working with Drawing Objects
26 Aug 202424 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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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