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:
//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);
Set the border type and color for the table on Presentation slides
With the help of Spire.Presentation, we can easily set the border type and color of a whole table on the presentation slides. This article will focus on demonstrating how to set the border for the table in C#.
Firstly, view the 12 border types for the table on PowerPoint file:
How to set the border type and color for an existing table on presentation slide:
//create a presentation document and load the file from disk
Presentation presentation = new Presentation();
presentation.LoadFromFile("Sample.pptx");
//get the table from the first slide of the sample document
ISlide slide = presentation.Slides[0];
ITable table = slide.Shapes[1] as ITable;
//set the border type as Inside and the border color as blue
table.SetTableBorder(TableBorderType.Inside, 1, Color.Blue);
//save the document to file
presentation.SaveToFile("Insideborder.pptx", FileFormat.Pptx2010);
Effective screenshot after set the border type for an existing table on presentation slide:
How to set the border type and color for newly added tables on presentation slide:
//create a presentation document
Presentation presentation = new Presentation();
//set the table width and height for each table cell
double[] tableWidth = new double[] { 100, 100, 100, 100, 100 };
double[] tableHeight = new double[] { 20, 20 };
//traverse all the border type of the table
foreach (TableBorderType item in Enum.GetValues(typeof(TableBorderType)))
//add a table to the presentation slide with the setting width and height
{ ITable itable = presentation.Slides.Append().Shapes.AppendTable(100, 100, tableWidth, tableHeight);
//add some text to the table cell
itable.TableRows[0][0].TextFrame.Text = "Row";
itable.TableRows[1][0].TextFrame.Text = "Column";
//set the border type, border width and the border color for the table
itable.SetTableBorder(item, 1.5, Color.Red);
}
//save the document to file
presentation.SaveToFile("Addtablewithborder.pptx", FileFormat.Pptx2010);
Retrieve Style Names of all TextRanges in a Word Document in C#, VB.NET
Programmers may need to determine the style name of a section of text, or find the text in a document that appear in a specified style name, such as “Heading 1”. This article will show you how to retrieve style names that are applied in a Word document by using Spire.Doc with C# and VB.NET.
Step 1: Create a Document instance.
Document doc = new Document();
Step 2: Load a sample Word file.
doc.LoadFromFile("Sample.docx");
Step 3: Traverse all TextRanges in the document and get their style names through StyleName property.
foreach (Section section in doc.Sections)
{
foreach (Paragraph paragraph in section.Paragraphs)
{
foreach (DocumentObject docObject in paragraph.ChildObjects)
{
if (docObject.DocumentObjectType == DocumentObjectType.TextRange)
{
TextRange text = docObject as TextRange;
Console.WriteLine(text.StyleName);
}
}
}
}
Result:
Full Code:
Document doc = new Document();
doc.LoadFromFile("Sample.docx");
foreach (Section section in doc.Sections)
{
foreach (Paragraph paragraph in section.Paragraphs)
{
foreach (DocumentObject docObject in paragraph.ChildObjects)
{
if (docObject.DocumentObjectType == DocumentObjectType.TextRange)
{
TextRange text = docObject as TextRange;
Console.WriteLine(text.StyleName);
}
}
Console.WriteLine();
}
}
Document doc = New Document()
doc.LoadFromFile("Sample.docx")
Dim section As Section
For Each section In doc.Sections
Dim paragraph As Paragraph
For Each paragraph In section.Paragraphs
Dim docObject As DocumentObject
For Each docObject In paragraph.ChildObjects
If docObject.DocumentObjectType = DocumentObjectType.TextRange Then
Dim text As TextRange = docObject as TextRange
Console.WriteLine(text.StyleName)
End If
Next
Console.WriteLine()
Next
Next
How to reset the position of the date time and slide number for the presentation slides
We have already demonstrated whether to display the additional information for presentation slides on header and footer area, such as hide or display date and time, the slide number, and the footer with the help of Spire.Presentation. This article will show you how to reset the position of the slide number and the date time in C#. We will also demonstrate how to reset the display format for the date and time from MM/dd/yyyy to yy.MM.yyyy on the presentation slides.
Firstly, view the default position of the date time at the left and the slide number at the right.
Step 1: Create a presentation document and load the file from disk.
Presentation presentation = new Presentation();
presentation.LoadFromFile("Sample.pptx", FileFormat.Pptx2013);
Step 2: Get the first slide from the sample document.
ISlide slide = presentation.Slides[0];
Step 3: Reset the position of the slide number to the left and date time to the center, and reset the date time display style.
foreach (IShape shapeToMove in slide.Shapes)
{
if (shapeToMove.Name.Contains("Slide Number Placeholder"))
{
shapeToMove.Left =0;
}
else if (shapeToMove.Name.Contains("Date Placeholder"))
{
shapeToMove.Left = presentation.SlideSize.Size.Width / 2;
(shapeToMove as IAutoShape).TextFrame.TextRange.Paragraph.Text = DateTime.Now.ToString("dd.MM.yyyy");
(shapeToMove as IAutoShape).TextFrame.IsCentered = true;
}
}
Step 4: Save the document to file.
presentation.SaveToFile("Result.pptx", FileFormat.Pptx2013);
Effective screenshot after reset the position and the format for the date time and slide number.
Full codes of how to reset the position of slide number and date time:
Presentation presentation = new Presentation();
presentation.LoadFromFile("Sample.pptx", FileFormat.Pptx2013);
ISlide slide = presentation.Slides[0];
foreach (IShape shapeToMove in slide.Shapes)
{
if (shapeToMove.Name.Contains("Slide Number Placeholder"))
{
shapeToMove.Left = 0;
}
else if (shapeToMove.Name.Contains("Date Placeholder"))
{
shapeToMove.Left = presentation.SlideSize.Size.Width / 2;
(shapeToMove as IAutoShape).TextFrame.TextRange.Paragraph.Text = DateTime.Now.ToString("dd.MM.yyyy");
(shapeToMove as IAutoShape).TextFrame.IsCentered = true;
}
}
presentation.SaveToFile("Result.pptx", FileFormat.Pptx2013);
Highlight Top and Bottom Ranked Values in Excel Using C#
One of the best uses of conditional formatting is to quickly highlight top or bottom ranked values in a large set of data. In this article, we’re going to show you how to highlight top and bottom ranked values in an Excel worksheet using Spire.XLS and conditional formatting.
The input.xlsx 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: Apply conditional formatting to range "C2:C5" to highlight the top 2 ranked values.
ConditionalFormatWrapper format1 = sheet.Range["C2:C5"].ConditionalFormats.AddCondition(); format1.FormatType = ConditionalFormatType.TopBottom; format1.TopBottom.Type = TopBottomType.Top; format1.TopBottom.Rank = 2; format1.BackColor = Color.Red;
Step 4: Apply conditional formatting to range "D2:D5" to highlight the bottom 2 ranked values.
ConditionalFormatWrapper format2 = sheet.Range["D2:D5"].ConditionalFormats.AddCondition(); format2.FormatType = ConditionalFormatType.TopBottom; format2.TopBottom.Type = TopBottomType.Bottom; format2.TopBottom.Rank = 2; format2.BackColor = Color.ForestGreen;
Step 5: Save the file.
workbook.SaveToFile("TopBottomValues.xlsx", ExcelVersion.Version2013);
Screenshot:
Full code:
//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];
//Apply conditional formatting to range “C2:C5” to highlight the top 2 values
ConditionalFormatWrapper format1 = sheet.Range["C2:C5"].ConditionalFormats.AddCondition();
format1.FormatType = ConditionalFormatType.TopBottom;
format1.TopBottom.Type = TopBottomType.Top;
format1.TopBottom.Rank = 2;
format1.BackColor = Color.Red;
//Apply conditional formatting to range “D2:D5” to highlight the bottom 2 values
ConditionalFormatWrapper format2 = sheet.Range["D2:D5"].ConditionalFormats.AddCondition();
format2.FormatType = ConditionalFormatType.TopBottom;
format2.TopBottom.Type = TopBottomType.Bottom;
format2.TopBottom.Rank = 2;
format2.BackColor = Color.ForestGreen;
//Save the file
workbook.SaveToFile("TopBottomValues.xlsx", ExcelVersion.Version2013);
Create a Cross-Reference to Bookmark in Word in C#, VB.NET
A cross-reference refers to related information elsewhere in the same document. You can create cross-references to any existing items such as headings, footnotes, bookmarks, captions, and numbered paragraphs. This article will show you how to create a cross-reference to bookmark using Spire.Doc with C# and VB.NET.
Step 1: Create a Document instance.
Document doc = new Document(); Section section = doc.AddSection();
Step 2: Insert a bookmark.
Paragraph paragraph = section.AddParagraph();
paragraph.AppendBookmarkStart("MyBookmark");
paragraph.AppendText("Text inside a bookmark");
paragraph.AppendBookmarkEnd("MyBookmark");
Step 3: Create a cross-reference field, and link it to the bookmark through bookmark name.
Field field = new Field(doc); field.Type = FieldType.FieldRef; field.Code = @"REF MyBookmark \p \h";
Step 4: Add a paragraph, and insert the field to the paragraph.
paragraph = section.AddParagraph();
paragraph.AppendText("For more information, see ");
paragraph.ChildObjects.Add(field);
Step 5: Insert a FieldSeparator object to the paragraph, which works as separator in a field.
FieldMark fieldSeparator= new FieldMark(doc, FieldMarkType.FieldSeparator); paragraph.ChildObjects.Add(fieldSeparator);
Step 6: Set the display text of the cross-reference field.
TextRange tr = new TextRange(doc); tr.Text = "above"; paragraph.ChildObjects.Add(tr);
Step 7: Insert a FieldEnd object to the paragraph, which is used to mark the end of a field.
FieldMark fieldEnd = new FieldMark(doc, FieldMarkType.FieldEnd); paragraph.ChildObjects.Add(fieldEnd);
Step 8: Save to file.
doc.SaveToFile("output.docx", FileFormat.Docx2013);
Output:
The cross-reference appears as a link that takes the reader to the referenced item.
Full Code:
Document doc = new Document();
Section section = doc.AddSection();
//create a bookmark
Paragraph paragraph = section.AddParagraph();
paragraph.AppendBookmarkStart("MyBookmark");
paragraph.AppendText("Text inside a bookmark");
paragraph.AppendBookmarkEnd("MyBookmark");
//insert line breaks
for (int i = 0; i < 4; i++)
{
paragraph.AppendBreak(BreakType.LineBreak);
}
//create a cross-reference field, and link it to bookmark
Field field = new Field(doc);
field.Type = FieldType.FieldRef;
field.Code = @"REF MyBookmark \p \h";
//insert field to paragraph
paragraph = section.AddParagraph();
paragraph.AppendText("For more information, see ");
paragraph.ChildObjects.Add(field);
//insert FieldSeparator object
FieldMark fieldSeparator = new FieldMark(doc, FieldMarkType.FieldSeparator);
paragraph.ChildObjects.Add(fieldSeparator);
//set display text of the field
TextRange tr = new TextRange(doc);
tr.Text = "above";
paragraph.ChildObjects.Add(tr);
//insert FieldEnd object to mark the end of the field
FieldMark fieldEnd = new FieldMark(doc, FieldMarkType.FieldEnd);
paragraph.ChildObjects.Add(fieldEnd);
//save file
doc.SaveToFile("output.docx", FileFormat.Docx2013);
Document doc = New Document()
Dim section As Section = doc.AddSection()
'create a bookmark
Dim paragraph As Paragraph = section.AddParagraph()
paragraph.AppendBookmarkStart("MyBookmark")
paragraph.AppendText("Text inside a bookmark")
paragraph.AppendBookmarkEnd("MyBookmark")
'insert line breaks
Dim i As Integer
For i = 0 To 4- 1 Step i + 1
paragraph.AppendBreak(BreakType.LineBreak)
Next
'create a cross-reference field, and link it to bookmark
Dim field As Field = New Field(doc)
field.Type = FieldType.FieldRef
field.Code = "REF MyBookmark \p \h"
'insert field to paragraph
paragraph = section.AddParagraph()
paragraph.AppendText("For more information, see ")
paragraph.ChildObjects.Add(field)
'insert FieldSeparator object
Dim fieldSeparator As FieldMark = New FieldMark(doc, FieldMarkType.FieldSeparator)
paragraph.ChildObjects.Add(fieldSeparator)
'set display text of the field
Dim tr As TextRange = New TextRange(doc)
tr.Text = "above"
paragraph.ChildObjects.Add(tr)
'insert FieldEnd object to mark the end of the field
Dim fieldEnd As FieldMark = New FieldMark(doc, FieldMarkType.FieldEnd)
paragraph.ChildObjects.Add(fieldEnd)
'save file
doc.SaveToFile("output.docx", FileFormat.Docx2013)
Extract text and image from Excel shape in C#
An excel shape can be filled with text or image, sometimes we need to read the text and image information in the shape. In this article, we are going to introduce how to extract text and image from shapes in Excel using Spire.XLS and C#.
Below is the screenshot of the sample document 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: Extract text from the first shape and save to a txt file.
IPrstGeomShape shape1 = sheet.PrstGeomShapes[0];
string s = shape1.Text;
StringBuilder sb = new StringBuilder();
sb.AppendLine(s);
File.WriteAllText("ShapeText.txt", sb.ToString());
Step 4: Extract image from the second shape and save to a local folder.
IPrstGeomShape shape2 = sheet.PrstGeomShapes[1]; Image image = shape2.Fill.Picture; image.Save(@"Image\ShapeImage.png", ImageFormat.Png);
Screeshot:
Extracted text:
Extracted image:
Full code:
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;
using System.Text;
using Spire.Xls;
using Spire.Xls.Core;
namespace Extract_text_and_image_from_Excel_shape
{
class Program
{
static void Main(string[] args)
{
//Load the Excel file
Workbook workbook = new Workbook();
workbook.LoadFromFile("Input.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Extract text from the first shape and save to a txt file
IPrstGeomShape shape1 = sheet.PrstGeomShapes[0];
string s = shape1.Text;
StringBuilder sb = new StringBuilder();
sb.AppendLine(s);
File.WriteAllText("ShapeText.txt", sb.ToString());
//Extract image from the second shape and save to a local folder
IPrstGeomShape shape2 = sheet.PrstGeomShapes[1];
Image image = shape2.Fill.Picture;
image.Save(@"Image\ShapeImage.png", ImageFormat.Png);
}
}
}
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:
//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:
//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:
Apply Transparency to Image in PowerPoint in C#, VB.NET
When you position some text over an image shape, you may want to make the image lighter or transparent so it doesn’t interfere with text. This article will demonstrate how to apply transparency to the image inside of a shape using Spire.Presentation with C# and VB.NET.
Step 1: Create a Presentation instance.
Presentation presentation = new Presentation();
Step 2: Create an Image from the specified file.
string imagePath = "logo.png"; Image image = Image.FromFile(imagePath);
Step 3: Add a shape to the first slide.
float width = image.Width; float height = image.Height; RectangleF rect = new RectangleF(50, 50, width, height); IAutoShape shape = presentation.Slides[0].Shapes.AppendShape(ShapeType.Rectangle, rect); shape.Line.FillType = FillFormatType.None;
Step 4: Fill the shape with image.
shape.Fill.FillType = FillFormatType.Picture; shape.Fill.PictureFill.Picture.Url = imagePath; shape.Fill.PictureFill.FillType = PictureFillType.Stretch;
Step 5: Set transparency on the image.
shape.Fill.PictureFill.Picture.Transparency = 50;
Step 6: Save to file.
presentation.SaveToFile("output.pptx", FileFormat.Pptx2013);
Output:
Full Code:
//create a PowerPoint document
Presentation presentation = new Presentation();
string imagePath = "logo.png";
Image image = Image.FromFile(imagePath);
float width = image.Width;
float height = image.Height;
RectangleF rect = new RectangleF(50, 50, width, height);
//add a shape
IAutoShape shape = presentation.Slides[0].Shapes.AppendShape(ShapeType.Rectangle, rect);
shape.Line.FillType = FillFormatType.None;
//fill shape with image
shape.Fill.FillType = FillFormatType.Picture;
shape.Fill.PictureFill.Picture.Url = imagePath;
shape.Fill.PictureFill.FillType = PictureFillType.Stretch;
//set transparency on image
shape.Fill.PictureFill.Picture.Transparency = 50;
//save file
presentation.SaveToFile("output.pptx", FileFormat.Pptx2013);
'create a PowerPoint document
Dim presentation As Presentation = New Presentation()
Dim imagePath As String = "logo.png"
Dim image As Image = Image.FromFile(imagePath)
Dim width As single = image.Width
Dim height As single = image.Height
Dim rect As RectangleF = New RectangleF(50,50, width, height)
'add a shape
Dim shape As IAutoShape = presentation.Slides(0).Shapes.AppendShape(ShapeType.Rectangle, rect)
shape.Line.FillType = FillFormatType.None
'fill shape with image
shape.Fill.FillType = FillFormatType.Picture
shape.Fill.PictureFill.Picture.Url = imagePath
shape.Fill.PictureFill.FillType = PictureFillType.Stretch
'set transparency on image
shape.Fill.PictureFill.Picture.Transparency = 50
'save file
presentation.SaveToFile("output.pptx", FileFormat.Pptx2013)
Highlight Duplicate and Unique Values in Excel Using C#
Using Excel conditional formatting, we can quickly find and highlight the duplicate and unique values in a selected cell range. In this article, we’re going to show you how to programmatically highlight duplicate and unique values with different colors using Spire.XLS and conditional formatting.
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: Use conditional formatting to highlight duplicate values in range "A2:A10" with IndianRed color.
ConditionalFormatWrapper format1 = sheet.Range["A2:A10"].ConditionalFormats.AddCondition(); format1.FormatType = ConditionalFormatType.DuplicateValues; format1.BackColor = Color.IndianRed;
Step 4: Use conditional formatting to highlight unique values in range "A2:A10" with Yellow color.
ConditionalFormatWrapper format2 = sheet.Range["A2:A10"].ConditionalFormats.AddCondition(); format2.FormatType = ConditionalFormatType.UniqueValues; format2.BackColor = Color.Yellow;
Step 5: Save the file.
workbook.SaveToFile("HighlightDuplicates.xlsx", ExcelVersion.Version2013);
Screenshot:
Full code:
//Load the Excel file
Workbook workbook = new Workbook();
workbook.LoadFromFile("Input.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Use conditional formatting to highlight duplicate values in range "A2:A10" with IndianRed color
ConditionalFormatWrapper format1 = sheet.Range["A2:A10"].ConditionalFormats.AddCondition();
format1.FormatType = ConditionalFormatType.DuplicateValues;
format1.BackColor = Color.IndianRed;
//Use conditional formatting to highlight unique values in range "A2:A10" with Yellow color
ConditionalFormatWrapper format2 = sheet.Range["A2:A10"].ConditionalFormats.AddCondition();
format2.FormatType = ConditionalFormatType.UniqueValues;
format2.BackColor = Color.Yellow;
//Save the file
workbook.SaveToFile("HighlightDuplicates.xlsx", ExcelVersion.Version2013);
