Objects (15)
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
2020-11-23 07:58:42 Written by support iceblueThis 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:
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); } } }
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:
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); } } }
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); } } }
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#
2017-12-19 07:39:28 Written by support iceblueWith 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:
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); } } }
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