C#/VB.NET: Group or Ungroup Shapes in Excel
Sometimes, you may need to perform specific operations on multiple shapes in Excel, such as adding styles, resizing, or moving them around. Grouping shapes can help you accomplish this task more easily and efficiently. Once shapes are grouped, they are treated as a single entity and can be manipulated at the same time. In this article, you will learn how to programmatically group or ungroup shapes in Excel in C# and VB.NET using Spire.XLS for .NET.
Install Spire.XLS for .NET
To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.
PM> Install-Package Spire.XLS
Group Shapes in Excel using C# and VB.NET
To group certain shapes in an Excel worksheet, you need to use the Worksheet.GroupShapeCollection property to return a GroupShapeCollection object, and then call the GroupShapeCollection.Group() method. The following are the detailed steps:
- Initialize an instance of Workbook class.
- Get the first worksheet by its index through Workbook.Worksheets[int] property.
- Add several shapes to specific rows and columns in the worksheet using Worksheet.PrstGeomShapes.AddPrstGeomShape(int, int, int, int, PrstGeomShapeType) method.
- Get the group shape collection of the worksheet through Worksheet.GroupShapeCollection property.
- Group the shapes using GroupShapeCollection.Group(IShape[]) method.
- Save the result document using Workbook.SaveToFile(string) method.
- C#
- VB.NET
using Spire.Xls;
using Spire.Xls.Core;
using Spire.Xls.Core.MergeSpreadsheet.Collections;
using System.Drawing;
namespace GroupShapes
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook instance
Workbook workbook = new Workbook();
//Get the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
//Add four shapes
IPrstGeomShape shape1 = worksheet.PrstGeomShapes.AddPrstGeomShape(3, 3, 65, 50, PrstGeomShapeType.RoundRect);
shape1.Fill.FillType = ShapeFillType.SolidColor;
shape1.Fill.ForeColor = Color.Yellow;
shape1.Line.Weight = 0.1;
IPrstGeomShape shape2 = worksheet.PrstGeomShapes.AddPrstGeomShape(7, 3, 65, 50, PrstGeomShapeType.Ribbon);
shape2.Fill.FillType = ShapeFillType.SolidColor;
shape2.Fill.ForeColor = Color.Purple;
shape2.Line.Weight = 0.1;
IPrstGeomShape shape3 = worksheet.PrstGeomShapes.AddPrstGeomShape(3, 5, 65, 50, PrstGeomShapeType.Cloud);
shape3.Fill.FillType = ShapeFillType.SolidColor;
shape3.Fill.ForeColor = Color.LightGreen;
shape3.Line.Weight = 0.1;
IPrstGeomShape shape4 = worksheet.PrstGeomShapes.AddPrstGeomShape(7, 5, 65, 50, PrstGeomShapeType.Ellipse);
shape4.Fill.FillType = ShapeFillType.SolidColor;
shape4.Fill.ForeColor = Color.LightSkyBlue;
shape4.Line.Weight = 0.1;
//Group the shapes
GroupShapeCollection groupShapeCollection = worksheet.GroupShapeCollection;
groupShapeCollection.Group(new IShape[] { shape1, shape2, shape3, shape4});
//Save the result file
workbook.SaveToFile("GroupShapes.xlsx", ExcelVersion.Version2013);
}
}
}
Ungroup Shapes in Excel using C# and VB.NET
To ungroup the grouped shapes in an Excel worksheet, you can use the GroupShapeCollection. UnGroupAll() method. After the shapes are ungrouped, you can manipulate them individually. The following are the detailed steps:
- Initialize an instance of Workbook class.
- Load an Excel file using Workbook.LoadFromFile() method.
- Get a specific worksheet by its index through Workbook.Worksheets[int] property.
- Get the group shape collection of the worksheet through Worksheet.GroupShapeCollection property.
- Ungroup all the grouped shapes using GroupShapeCollection.UnGroupAll() method.
- Save the result document using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
using Spire.Xls.Core.MergeSpreadsheet.Collections;
namespace UngroupShapes
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook instance
Workbook workbook = new Workbook();
workbook.LoadFromFile("GroupShapes.xlsx");
//Get the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
//Ungroup the grouped shapes in the worksheet
GroupShapeCollection groupShapeCollection = worksheet.GroupShapeCollection;
groupShapeCollection.UnGroupAll();
//Save the result file
workbook.SaveToFile("UnGroupShapes.xlsx", ExcelVersion.Version2013);
}
}
}
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
C#/VB.NET Add lines to Excel worksheets through two points
This article will show you how to add lines to Excel worksheets through two points. We could set the point’s location via relative location and Absolute location in pixels.
using Spire.Xls;
using Spire.Xls.Core.Spreadsheet.Shapes;
using System.Drawing;
namespace Word
{
class Program
{
static void Main(string[] args)
{
//Initiate a Workbook object and get the first worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
//Add a line with relative location
XlsLineShape line = worksheet.TypedLines.AddLine() as XlsLineShape;
//set the column index of the starting point
line.LeftColumn = 2;
line.LeftColumnOffset = 2;
line.TopRow = 5;
line.TopRowOffset = 10;
//set the column index of the end point
line.RightColumn = 10;
line.RightColumnOffset = 10;
line.BottomRow =5;
line.BottomRowOffset = 10;
//Set the color
line.Color = Color.Red;
//Add a line with Absolute location in pixels
XlsLineShape line1 = worksheet.TypedLines.AddLine() as XlsLineShape;
//Set the start point and end point
line1.StartPoint = new Point(20, 30);
line1.EndPoint = new Point(200, 30);
//Set the color
line1.Color = Color.Blue;
workbook.SaveToFile("Addlines.xlsx", ExcelVersion.Version2013);
workbook.Dispose();
}
}
}
Imports Spire.Xls
Imports Spire.Xls.Core.Spreadsheet.Shapes
Imports System.Drawing
Namespace Word
Class Program
Private Shared Sub Main(ByVal args() As String)
'Initiate a Workbook object and get the first worksheet
Dim workbook As Workbook = New Workbook
Dim worksheet As Worksheet = workbook.Worksheets(0)
'Add a line with relative location
Dim line As XlsLineShape = CType(worksheet.TypedLines.AddLine,XlsLineShape)
'set the column index of the starting point
line.LeftColumn = 2
line.LeftColumnOffset = 2
line.TopRow = 5
line.TopRowOffset = 10
'set the column index of the end point
line.RightColumn = 10
line.RightColumnOffset = 10
line.BottomRow = 5
line.BottomRowOffset = 10
'Set the color
line.Color = Color.Red
'Add a line with Absolute location in pixels
Dim line1 As XlsLineShape = CType(worksheet.TypedLines.AddLine,XlsLineShape)
'Set the start point and end point
line1.StartPoint = New Point(20, 30)
line1.EndPoint = New Point(200, 30)
'Set the color
line1.Color = Color.Blue
workbook.SaveToFile("Addlines.xlsx", ExcelVersion.Version2013)
workbook.Dispose
End Sub
End Class
End Namespace
Effective screenshot:
Add arrow line to Excel worksheet in C#
With Spire.XLS for .NET, developers can easily use C# to add shapes to Excel worksheet. From version 9.8.11, Spire.XLS supports to add arrow lines to Excel worksheet. The following sample will show you how to insert arrow line, double Arrow, Elbow Arrow, Elbow Double-Arrow, Curved Arrow and Curved Double-Arrow to Excel worksheet in C#.
using Spire.Xls;
using System.Drawing;
namespace Add_Lines_to_Excel
{
class Program
{
static void Main(string[] args)
{
//Initiate a Workbook object and get the first worksheet
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
//Add a Double Arrow and fill the line with solid color
var line = sheet.TypedLines.AddLine();
line.Top = 10;
line.Left = 20;
line.Width = 100;
line.Height = 0;
line.Color = Color.Blue;
line.BeginArrowHeadStyle = ShapeArrowStyleType.LineArrow;
line.EndArrowHeadStyle = ShapeArrowStyleType.LineArrow;
//Add an Arrow and fill the line with solid color
var line_1 = sheet.TypedLines.AddLine();
line_1.Top = 50;
line_1.Left = 30;
line_1.Width = 100;
line_1.Height = 100;
line_1.Color = Color.Red;
line_1.BeginArrowHeadStyle = ShapeArrowStyleType.LineNoArrow;
line_1.EndArrowHeadStyle = ShapeArrowStyleType.LineArrow;
//Add an Elbow Arrow Connector
Spire.Xls.Core.Spreadsheet.Shapes.XlsLineShape line3 = sheet.TypedLines.AddLine() as Spire.Xls.Core.Spreadsheet.Shapes.XlsLineShape;
line3.LineShapeType = LineShapeType.ElbowLine;
line3.Width = 30;
line3.Height = 50;
line3.EndArrowHeadStyle = ShapeArrowStyleType.LineArrow;
line3.Top = 100;
line3.Left = 50;
//Add an Elbow Double-Arrow Connector
Spire.Xls.Core.Spreadsheet.Shapes.XlsLineShape line2 = sheet.TypedLines.AddLine() as Spire.Xls.Core.Spreadsheet.Shapes.XlsLineShape;
line2.LineShapeType = LineShapeType.ElbowLine;
line2.Width = 50;
line2.Height = 50;
line2.EndArrowHeadStyle = ShapeArrowStyleType.LineArrow;
line2.BeginArrowHeadStyle = ShapeArrowStyleType.LineArrow;
line2.Left = 120;
line2.Top = 100;
//Add a Curved Arrow Connector
line3 = sheet.TypedLines.AddLine() as Spire.Xls.Core.Spreadsheet.Shapes.XlsLineShape;
line3.LineShapeType = LineShapeType.CurveLine;
line3.Width = 30;
line3.Height = 50;
line3.EndArrowHeadStyle = ShapeArrowStyleType.LineArrowOpen;
line3.Top = 100;
line3.Left = 200;
//Add a Curved Double-Arrow Connector
line2 = sheet.TypedLines.AddLine() as Spire.Xls.Core.Spreadsheet.Shapes.XlsLineShape;
line2.LineShapeType = LineShapeType.CurveLine;
line2.Width = 30;
line2.Height = 50;
line2.EndArrowHeadStyle = ShapeArrowStyleType.LineArrowOpen;
line2.BeginArrowHeadStyle = ShapeArrowStyleType.LineArrowOpen;
line2.Left = 250;
line2.Top = 100;
//Save the file
workbook.SaveToFile("AddLines.xlsx", ExcelVersion.Version2013);
}
}
}
Set the internal margin of excel textbox in C#
With Spire.XLS, developers can add text or image to the textbox to Excel worksheet easily. From version 9.3.10, Spire.XLS supports to set the inner margin of contents on Excel text box. With this feature, we can adjust the position of the text contents on the textbox to make it beautiful. This article is going to introduce how to set the inner margins of the textbox in Excel worksheet in C#.
using Spire.Xls;
using Spire.Xls.Core.Spreadsheet.Shapes;
namespace SetInternalMargin
{
class Program
{
static void Main(string[] args)
{
{
//load the sample document
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx", ExcelVersion.Version2010);
//get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//add a textbox to the sheet and set its position and size
XlsTextBoxShape textbox = sheet.TextBoxes.AddTextBox(4, 2, 100, 300) as XlsTextBoxShape;
//set the text on the textbox
textbox.Text = "Insert TextBox in Excel and set the margin for the text";
textbox.HAlignment = CommentHAlignType.Center;
textbox.VAlignment = CommentVAlignType.Center;
//set the inner margins of the contents
textbox.InnerLeftMargin = 1;
textbox.InnerRightMargin = 3;
textbox.InnerTopMargin = 1;
textbox.InnerBottomMargin = 1;
//save the document to file
workbook.SaveToFile("Result.xlsx", ExcelVersion.Version2010);
}
}
}
}
Effective screenshot after setting the margins of the contents:
Hide or Unhide Shape in Excel in C#
Spire.XLS supports to hide or unhide certain shapes in Excel worksheet through IShape.Visible property. This article demonstrates the detail steps to hide or unhide a shape using Spire.XLS and C#.
Below is the screenshot of the example Excel file:
Detail steps:
Step 1: Instantiate a Workbook object and load the Excel file.
Workbook workbook = new Workbook();
workbook.LoadFromFile("Input.xlsx");
Step 2: Get the first worksheet.
Worksheet sheet = workbook.Worksheets[0];
Step 3: Hide the second shape in the worksheet.
//Hide the second shape in the worksheet sheet.PrstGeomShapes[1].Visible = false; //Show the second shape in the worksheet //sheet.PrstGeomShapes[1].Visible = true;
Step 4: Save the file.
workbook.SaveToFile("HideShape.xlsx", ExcelVersion.Version2013);
Output:
Full code:
using Spire.Xls;
namespace HideShape
{
class Program
{
static void Main(string[] args)
{
//Instantiate a Workbook object
Workbook workbook = new Workbook();
//Load the Excel file
workbook.LoadFromFile("Input.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Hide the second shape in the worksheet
sheet.PrstGeomShapes[1].Visible = false;
//Show the second shape in the worksheet
sheet.PrstGeomShapes[1].Visible = true;
//Save the file
workbook.SaveToFile("HideShape.xlsx", ExcelVersion.Version2013);
}
}
}
Delete shapes in an Excel Worksheet in C#
Spire.XLS supports to delete a specific shape as well as all shapes in an Excel worksheet. This article demonstrates how to use Spire.XLS to implement this function.
The example file we used for demonstration:
Detail steps:
Step 1: Initialize an object of Workbook class and load the Excel file.
Workbook workbook = new Workbook();
workbook.LoadFromFile("Input.xlsx");
Step 2: Get the first worksheet.
Worksheet sheet = workbook.Worksheets[0];
Step 3: Delete the first shape in the worksheet.
sheet.PrstGeomShapes[0].Remove();
To delete all shapes from the worksheet:
for (int i = sheet.PrstGeomShapes.Count-1; i >= 0; i--)
{
sheet.PrstGeomShapes[i].Remove();
}
Step 4: Save the file.
workbook.SaveToFile("DeleteShape.xlsx", ExcelVersion.Version2013);
Screenshot:
Full code:
using Spire.Xls;
namespace DeleteShape
{
class Program
{
static void Main(string[] args)
{
//Initialize an object of Workbook class
Workbook workbook = new Workbook();
//Load the Excel file
workbook.LoadFromFile("Input.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Delete the first shape in the worksheet
sheet.PrstGeomShapes[0].Remove();
//Delete all shapes in the worksheet
//for (int i = sheet.PrstGeomShapes.Count-1; i >= 0; i--)
//{
// sheet.PrstGeomShapes[i].Remove();
//}
workbook.SaveToFile("DeleteShape.xlsx", ExcelVersion.Version2013);
}
}
}
C#/VB.NET: Extract Text and Images from Excel Shapes
Shapes in Excel serve as visual elements that can decorate or optimize worksheets, including objects such as text boxes and images. By inserting shapes, users are able to present data in a more intuitive manner and emphasize vital information, ultimately improving the readability of the spreadsheets. When it becomes necessary to deal with the contents within the shapes independently, you can programmatically extract them from shapes for further processing. In this article, we will show you how to extract text and images from excel shapes by using Spire.XLS for .NET.
Install Spire.XLS for .NET
To begin with, you need to add the DLL files included in the Spire.XLS for.NET package as references in your .NET project. The DLLs files can be either downloaded from this link or installed via NuGet.
PM> Install-Package Spire.XLS
Extract Text from Excel Shapes
Spire.XLS for .NET allows users to extract text from shape object by using IPrstGeomShape.Text property and write it to a new .txt file. The following are detailed steps.
- Create a Workbook object.
- Load an Excel file using Workbook.LoadFromFile() method.
- Get the first worksheet by Workbook.Worksheets[] property.
- Get the second shape by Worksheet.PrstGeomShapes[] property.
- Extract text content from the second shape and save it to the string variable.
- Create a StringBuilder object and append the extracted text to it.
- Write the text to a .txt file using File.WriteAllText() method.
- C#
- VB.NET
using System.IO;
using System.Text;
using Spire.Xls;
using Spire.Xls.Core;
namespace Extracttext
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook workbook = new Workbook();
//Load the Excel file
workbook.LoadFromFile("sample.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Get the second shape and extract text from it
IPrstGeomShape shape1 = sheet.PrstGeomShapes[1];
string s = shape1.Text;
//Append the extracted text to StringBuilder object
StringBuilder sb = new StringBuilder();
sb.AppendLine(s);
//Write the text to a .txt file
File.WriteAllText("ShapeText.txt", sb.ToString());
workbook.Dispose();
}
}
}
Extract Images from Excel Shapes
Additionally, Spire.XLS for .NET also supports extracting the image by using IPrstGeomShape.Fill.Picture property and save it to a local folder. The related steps are as follows.
- Create a Workbook object.
- Load an Excel file using Workbook.LoadFromFile() method.
- Get the first worksheet by Workbook.Worksheets[] property.
- Get the first shape by Worksheet.PrstGeomShapes[] property.
- Extract the image from the first shape by its Fill and Picture property.
- Save the extracted image to a folder by using Image.Save() method.
- C#
- VB.NET
using System.Drawing;
using System.Drawing.Imaging;
using Spire.Xls;
using Spire.Xls.Core;
namespace Extractimage
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook workbook = new Workbook();
//Load the Excel file
workbook.LoadFromFile("sample.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Get the first shape and extract the image from it
IPrstGeomShape shape2 = sheet.PrstGeomShapes[0];
Image image = shape2.Fill.Picture;
//Save the extracted image to a folder
image.Save(@"Image\ShapeImage.png", ImageFormat.Png);
workbook.Dispose();
}
}
}
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
Set the shadow style for the shape on Excel worksheet in C#
With the help of Spire.XLS, we can easily add shapes to the Excel worksheet. This article will demonstrate how to format the shadow of shape on Excel in C#. We can use Spire.XLS to set the color, size, blur, angle, transparency and distance of the shadow for the shape on Excel worksheet.
Set the shadow style when we add new shape to the Excel worksheet:
using Spire.Xls;
using Spire.Xls.Core;
using System.Drawing;
namespace SetShadowStyle
{
class Program
{
static void Main(string[] args)
{
//instantiate a Workbook object and get the first worksheet
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
//add an ellipse shape
IPrstGeomShape ellipse = sheet.PrstGeomShapes.AddPrstGeomShape(5, 5, 150, 100, PrstGeomShapeType.Ellipse);
//set the shadow style for the ellipse
ellipse.Shadow.Angle = 90;
ellipse.Shadow.Distance = 10;
ellipse.Shadow.Size = 150;
ellipse.Shadow.Color = Color.Gray;
ellipse.Shadow.Blur = 30;
ellipse.Shadow.Transparency = 1;
ellipse.Shadow.HasCustomStyle = true;
//save the document to file
workbook.SaveToFile("Shapeshadow.xlsx", FileFormat.Version2010);
}
}
}
Effective screenshot after setting the shadow style for the shape on the Excel worksheet:
Set the shadow style when we loaded an Excel document with shape:
using Spire.Xls;
using Spire.Xls.Core;
using System.Drawing;
namespace SetShadowStyle
{
class Program
{
static void Main(string[] args)
{
//create an instance of workbook and load the document from file
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
//get the first worksheet from the sample document
Worksheet sheet = workbook.Worksheets[0];
//get the first shape from the worksheet
IPrstGeomShape shape = sheet.PrstGeomShapes[0];
//set the shadow style for the shape
shape.Shadow.Angle = 90;
shape.Shadow.Distance = 10;
shape.Shadow.Size = 120;
shape.Shadow.Color = Color.Yellow;
shape.Shadow.Blur = 30;
shape.Shadow.HasCustomStyle = true;
//save the document to file
workbook.SaveToFile("ShadowStyle.xlsx", FileFormat.Version2010);
}
}
}
Effective screenshot after setting the shadow style for the shape on the Excel worksheet:
Insert Shapes to Excel Worksheet in C#
Spire.XLS supports to add up to 186 kinds of ready-made shapes, such as triangles, arrows and callouts to Excel worksheet. This article demonstrates how to insert shapes to excel worksheet and fill the shapes with color and picture using Spire.XLS and C#.
Detail steps:
Step 1: Instantiate a Workbook object and get the first worksheet.
Workbook workbook = new Workbook(); Worksheet sheet = workbook.Worksheets[0];
Step 2: Add a triangle shape and fill the shape with solid color.
//Add a triangle shape IPrstGeomShape triangle = sheet.PrstGeomShapes.AddPrstGeomShape(2, 2, 100, 100, PrstGeomShapeType.Triangle); //Fill the triangle with solid color triangle.Fill.ForeColor = Color.Yellow; triangle.Fill.FillType = ShapeFillType.SolidColor;
Step 3: Add a heart shape and fill the shape with gradient color.
//Add a heart shape IPrstGeomShape heart = sheet.PrstGeomShapes.AddPrstGeomShape(2, 5, 100, 100, PrstGeomShapeType.Heart); //Fill the heart with gradient color heart.Fill.ForeColor = Color.Red; heart.Fill.FillType = ShapeFillType.Gradient;
Step 4: Add an arrow shape with default color.
IPrstGeomShape arrow = sheet.PrstGeomShapes.AddPrstGeomShape(10, 2, 100, 100, PrstGeomShapeType.CurvedRightArrow);
Step 5: Add a cloud shape and fill the shape with custom picture.
//Add a cloud shape
IPrstGeomShape cloud = sheet.PrstGeomShapes.AddPrstGeomShape(10, 5, 100, 100, PrstGeomShapeType.Cloud);
//Fill the cloud with picture
cloud.Fill.CustomPicture(Image.FromFile("Hydrangeas.jpg"), "Hydrangeas.jpg");
cloud.Fill.FillType = ShapeFillType.Picture;
Step 6: Save the file.
workbook.SaveToFile("AddShapes.xlsx", ExcelVersion.Version2013);
Screenshot:
Full code:
using System.Drawing;
using Spire.Xls;
using Spire.Xls.Core;
namespace Add_shapes_to_Excel
{
class Program
{
static void Main(string[] args)
{
//Instantiate a workbook object
Workbook workbook = new Workbook();
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Add a triangle shape
IPrstGeomShape triangle = sheet.PrstGeomShapes.AddPrstGeomShape(2, 2, 100, 100, PrstGeomShapeType.Triangle);
//Fill the triangle with solid color
triangle.Fill.ForeColor = Color.Yellow;
triangle.Fill.FillType = ShapeFillType.SolidColor;
//Add a heart shape
IPrstGeomShape heart = sheet.PrstGeomShapes.AddPrstGeomShape(2, 5, 100, 100, PrstGeomShapeType.Heart);
//Fill the heart with gradient color
heart.Fill.ForeColor = Color.Red;
heart.Fill.FillType = ShapeFillType.Gradient;
//Add an arrow shape with default color
IPrstGeomShape arrow = sheet.PrstGeomShapes.AddPrstGeomShape(10, 2, 100, 100, PrstGeomShapeType.CurvedRightArrow);
//Add a cloud shape
IPrstGeomShape cloud = sheet.PrstGeomShapes.AddPrstGeomShape(10, 5, 100, 100, PrstGeomShapeType.Cloud);
//Fill the cloud with custom picture
cloud.Fill.CustomPicture(Image.FromFile("Hydrangeas.jpg"), "Hydrangeas.jpg");
cloud.Fill.FillType = ShapeFillType.Picture;
//Save the file
workbook.SaveToFile("AddShapes.xlsx", ExcelVersion.Version2013);
}
}
}
Adding Picture to Excel Textbox in C#, VB.NET
Textbox in Excel is a special kind of graphic object, which allows users to add some text in it. Moreover, textbox can be filled with solid color, gradient, pattern or a picture so that it looks more attractive. This article presents how to add a picture fill to Excel textbox and adjust the position of the picture as well.
Code Snippets:
Step 1: Create a new instance of Workbook class and get the first worksheet.
Workbook workbook = new Workbook(); Worksheet sheet = workbook.Worksheets[0];
Step 2: Add a textbox shape into the worksheet.
ITextBoxShape shape = sheet.TextBoxes.AddTextBox(2, 2, 200, 300);
Step 3: Fill the textbox with a 160x160 pixels picture.
shape.Fill.CustomPicture(@"C:\Users\Administrator\Desktop\logo.png"); shape.Fill.FillType = ShapeFillType.Picture;
Step 4: Save the file.
workbook.SaveToFile("PicFill.xlsx", ExcelVersion.Version2013);
When we add a picture fill to a textbox using above code, the picture will stretch to fill the shape, like below screenshot.
If you want to prevent the image from stretching, you can adjust the size of the textbox or change the position of the picture, for example, place the image at the central position of the textbox. Following code snippet demonstrates how to center align the picture fill.
//If the height of textbox is larger than the height of original picture, set the picture into vertical center
if (textbox.Height > textbox.Fill.Picture.Height)
{
int difH = textbox.Height - textbox.Fill.Picture.Height;
(textbox.Fill as XlsShapeFill).PicStretch.Top = difH * 100 / (textbox.Height * 2);
(textbox.Fill as XlsShapeFill).PicStretch.Bottom = difH * 100 / (textbox.Height * 2);
}
//If the width of textbox is larger than the width of original picture, set the picture into horizontal center
if (textbox.Width > textbox.Fill.Picture.Width)
{
int difW = textbox.Width - textbox.Fill.Picture.Width;
(textbox.Fill as XlsShapeFill).PicStretch.Left = difW * 100 / (textbox.Width * 2);
(textbox.Fill as XlsShapeFill).PicStretch.Right = difW * 100 / (textbox.Width * 2);
}
Full Code:
using Spire.Xls;
using Spire.Xls.Core;
using Spire.Xls.Core.Spreadsheet.Shapes;
namespace AddingPicture
{
class Program
{
static void Main(string[] args)
{
//load Excel file
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
ITextBoxShape textbox = sheet.TextBoxes.AddTextBox(2, 2, 200, 300);
textbox.Fill.CustomPicture(@"C:\Users\Administrator\Desktop\logo.png");
textbox.Fill.FillType = ShapeFillType.Picture;
//If the height of textbox is larger than the height of original picture, set the picture into vertical center
if (textbox.Height > textbox.Fill.Picture.Height)
{
int difH = textbox.Height - textbox.Fill.Picture.Height;
(textbox.Fill as XlsShapeFill).PicStretch.Top = difH * 100 / (textbox.Height * 2);
(textbox.Fill as XlsShapeFill).PicStretch.Bottom = difH * 100 / (textbox.Height * 2);
}
//If the width of textbox is larger than the width of original picture, set the picture into horizontal center
if (textbox.Width > textbox.Fill.Picture.Width)
{
int difW = textbox.Width - textbox.Fill.Picture.Width;
(textbox.Fill as XlsShapeFill).PicStretch.Left = difW * 100 / (textbox.Width * 2);
(textbox.Fill as XlsShapeFill).PicStretch.Right = difW * 100 / (textbox.Width * 2);
}
workbook.SaveToFile("PicFill.xlsx", ExcelVersion.Version2013);
}
}
}
Imports Spire.Xls
Imports Spire.Xls.Core
Imports Spire.Xls.Core.Spreadsheet.Shapes
Namespace AddingPicture
Class Program
Private Shared Sub Main(args As String())
'load Excel file
Dim workbook As New Workbook()
Dim sheet As Worksheet = workbook.Worksheets(0)
Dim textbox As ITextBoxShape = sheet.TextBoxes.AddTextBox(2, 2, 200, 300)
textbox.Fill.CustomPicture("C:\Users\Administrator\Desktop\logo.png")
textbox.Fill.FillType = ShapeFillType.Picture
'If the height of textbox is larger than the height of original picture, set the picture into vertical center
If textbox.Height > textbox.Fill.Picture.Height Then
Dim difH As Integer = textbox.Height - textbox.Fill.Picture.Height
TryCast(textbox.Fill, XlsShapeFill).PicStretch.Top = difH * 100 / (textbox.Height * 2)
TryCast(textbox.Fill, XlsShapeFill).PicStretch.Bottom = difH * 100 / (textbox.Height * 2)
End If
'If the width of textbox is larger than the width of original picture, set the picture into horizontal center
If textbox.Width > textbox.Fill.Picture.Width Then
Dim difW As Integer = textbox.Width - textbox.Fill.Picture.Width
TryCast(textbox.Fill, XlsShapeFill).PicStretch.Left = difW * 100 / (textbox.Width * 2)
TryCast(textbox.Fill, XlsShapeFill).PicStretch.Right = difW * 100 / (textbox.Width * 2)
End If
workbook.SaveToFile("PicFill.xlsx", ExcelVersion.Version2013)
End Sub
End Class
End Namespace