When working with large data sets in Excel, it can be a challenge not only to calculate data, but also to find the relevant information. Luckily, we can add AutoFilter in a range of cells or table to display the data you want and hid the rest. This article will show you how to filter data in Excel using Spire.XLS with C# and VB.NET.

Code Snippets

Step 1: Create a Workbook object and load an existing Excel file.

Workbook workbook = new Workbook();
workbook.LoadFromFile(@"C:\Users\Administrator\Desktop\sample.xlsx");

Step 2: Get the first worksheet.

Worksheet sheet = workbook.Worksheets[0];

Step 3: Get the AutoFilters object.

AutoFiltersCollection filters = sheet.AutoFilters;

Step 4: Set the range to be filtered.

filters.Range = sheet.Range[1, 2, sheet.LastRow, 2];

Step 5: Add a filter criteria.

filters.AddFilter(0, "Laptop");

Step 6: Filter the data.

filters.Filter();

Step 7: Save to file.

workbook.SaveToFile("output.xlsx", ExcelVersion.Version2010);

Output:

How to Add Autofilter to Excel in C#, VB.NET

Full Code:

[C#]
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"C:\Users\Administrator\Desktop\sample.xlsx");
Worksheet sheet = workbook.Worksheets[0];
AutoFiltersCollection filters = sheet.AutoFilters;
filters.Range = sheet.Range[1, 2, sheet.LastRow, 2];   
filters.AddFilter(0, "Laptop"); 
filters.Filter(); 
workbook.SaveToFile("output.xlsx", ExcelVersion.Version2010); 
[VB.NET]
Dim workbook As Workbook = New Workbook()
workbook.LoadFromFile("C:\Users\Administrator\Desktop\sample.xlsx")
Dim sheet As Worksheet = workbook.Worksheets(0)
Dim filters As AutoFiltersCollection = sheet.AutoFilters
filters.Range = sheet.Range(1, 2, sheet.LastRow, 2)
filters.AddFilter(0, "Laptop")
filters.Filter()
workbook.SaveToFile("output.xlsx", ExcelVersion.Version2010)

This article demonstrates how to add a picture as a custom bullet style in a PowerPoint file using Spire.Presentation.

Below is the screenshot of the example PowerPoint file:

Add a Picture as a Custom Bullet Style in PowerPoint in C#

Detail steps:

Step 1: Instantiate a Presentation object and load the PowerPoint file.

Presentation ppt = new Presentation();
ppt.LoadFromFile(@"Input.pptx");

Step 2: Get the first shape on the first slide.

IAutoShape shape = ppt.Slides[0].Shapes[0] as IAutoShape;

Step 3: Add a picture as the bullet style of the paragraphs in the shape.

foreach (TextParagraph paragraph in shape.TextFrame.Paragraphs)
{
    paragraph.BulletType = TextBulletType.Picture;
    Image bulletPicture = Image.FromFile(@"timg.jpg");
    paragraph.BulletPicture.EmbedImage = ppt.Images.Append(bulletPicture);
}

Step 4: Save the file.

ppt.SaveToFile(@"Output.pptx", FileFormat.Pptx2010);

Screenshot:

Add a Picture as a Custom Bullet Style in PowerPoint in C#

Full code:

using System.Drawing;
using Spire.Presentation;

namespace Add_Picture_as_Custom_Bullet_Style_in_PPT
{
    class Program
    {
        static void Main(string[] args)
        {
            //Instantiate a Presentation object
            Presentation ppt = new Presentation();
            //Load the PowerPoint file
            ppt.LoadFromFile(@"Input.pptx");

            //Get the first shape on the first slide
            IAutoShape shape = ppt.Slides[0].Shapes[0] as IAutoShape;

            //Traverse through the paragraphs in the shape
            foreach (TextParagraph paragraph in shape.TextFrame.Paragraphs)
            {
                //Set the bullet style of paragraph as picture
                paragraph.BulletType = TextBulletType.Picture;
                //Load a picture
                Image bulletPicture = Image.FromFile(@"timg.jpg");
                //Add the picture as the bullet style of paragraph
                paragraph.BulletPicture.EmbedImage = ppt.Images.Append(bulletPicture);
            }

            //Save the file
            ppt.SaveToFile(@"Output.pptx", FileFormat.Pptx2010);
        }
    }
}

The height of headers and footers can be adjusted by using the HeaderDistance and the FooterDistance properties. The detail steps of how to adjust the height of headers and footers in a word document using Spire.Doc are shown below.

Detail steps:

Step 1: Instantiate a Document object and load the word document.

Document doc = new Document();
doc.LoadFromFile("Headers and Footers.docx");

Step 2: Get the first section.

Section section = doc.Sections[0];

Step 3: Adjust the height of headers and footers in the section.

section.PageSetup.HeaderDistance = 100;
section.PageSetup.FooterDistance = 100;

Step 4: Save the file.

doc.SaveToFile("Output.docx", FileFormat.Docx2013);

Screenshot:

Header:

Adjust the Height of Headers and Footers in a Word document in C#

Footer:

Adjust the Height of Headers and Footers in a Word document in C#

Full code:

//Instantiate a Document object
Document doc = new Document();
//Load the word document
doc.LoadFromFile("Headers and Footers.docx");

//Get the first section
Section section = doc.Sections[0];

//Adjust the height of headers in the section
section.PageSetup.HeaderDistance = 100;

//Adjust the height of footers in the section
section.PageSetup.FooterDistance = 100;

//Save the document
doc.SaveToFile("Output.docx", FileFormat.Docx2013);
Wednesday, 08 August 2018 09:06

Add a Total Row to Table in Excel in C#

We can quickly total data in an Excel table by adding a total row. This article demonstrates how to add a total row to a table in Excel using Spire.XLS.

Blow is the screenshot of the example file:

Add a Total Row to Table in Excel in C#

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: Create a table with the data from the specific cell range.

IListObject table = sheet.ListObjects.Create("Table", sheet.Range["A1:D4"]);

Step 4: Display total row.

table.DisplayTotalRow = true;

Step 5: Add a total row.

table.Columns[0].TotalsRowLabel = "Total";
table.Columns[1].TotalsCalculation = ExcelTotalsCalculation.Sum;
table.Columns[2].TotalsCalculation = ExcelTotalsCalculation.Sum;
table.Columns[3].TotalsCalculation = ExcelTotalsCalculation.Sum;

Step 6: Save the file.

workbook.SaveToFile("AddTotalRow.xlsx", ExcelVersion.Version2013);

Output:

Add a Total Row to Table in Excel in C#

Full code:

//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];

//Create a table with the data from the specific cell range
IListObject table = sheet.ListObjects.Create("Table", sheet.Range["A1:D4"]);

//Display total row
table.DisplayTotalRow = true;

//Add a total row
table.Columns[0].TotalsRowLabel = "Total";
table.Columns[1].TotalsCalculation = ExcelTotalsCalculation.Sum;
table.Columns[2].TotalsCalculation = ExcelTotalsCalculation.Sum;
table.Columns[3].TotalsCalculation = ExcelTotalsCalculation.Sum;

//Save the file
workbook.SaveToFile("AddTotalRow.xlsx", ExcelVersion.Version2013);

In Spire.Presentation, when we add a common animation effect that belongs to both entrance and exit types, it’s applied as entrance effect by default. This article is going to show you how to add exit animation effect to a shape in PowerPoint using Spire.Presentation.

Detail steps:

Step 1: Create a Presentation instance and get the first slide.

Presentation ppt = new Presentation();
ISlide slide = ppt.Slides[0];

Step 2: Add a shape to the slide.

IShape starShape = slide.Shapes.AppendShape(ShapeType.FivePointedStar, new RectangleF(100, 100, 200, 200));

Step 3: Add random bars effect to the shape.

AnimationEffect effect = slide.Timeline.MainSequence.AddEffect(starShape, AnimationEffectType.RandomBars);

Step 4: Change the type of the effect from entrance to exit.

effect.PresetClassType = TimeNodePresetClassType.Exit;

Step 5: Save the file.

ppt.SaveToFile("ExitAnimationEffect.pptx", FileFormat.Pptx2013);

Screenshot:

Add Exit Animation Effect to a Shape in PowerPoint in C#

Full code:

//Create a Presentation instance
Presentation ppt = new Presentation();

//Get the first slide
ISlide slide = ppt.Slides[0];

//Add a shape to the slide
IShape starShape = slide.Shapes.AppendShape(ShapeType.FivePointedStar, new RectangleF(100, 100, 200, 200));

//Add random bars effect to the shape
AnimationEffect effect = slide.Timeline.MainSequence.AddEffect(starShape, AnimationEffectType.RandomBars);

//Change effect type from entrance to exit
effect.PresetClassType = TimeNodePresetClassType.Exit;

//Save the file
ppt.SaveToFile("ExitAnimationEffect.pptx", FileFormat.Pptx2013);
Friday, 27 July 2018 10:13

How to use Spire.Doc on .NET Core

This program guide focus on helping you how to make Spire.Doc workable on Windows and Linux.

Starts from V 6.7.4, Spire.Doc supports .NET Core 2.0. Spire.Doc for netstandard2.0 uses System.Drawing.Common, which depends on Libgdiplus library. So when you use Spire.Doc on .NET core, you need to install Libgdiplus library.

On Windows, we recommend you to get Spire.Doc package via Nuget. The Libgdiplus library will be installed automatically.

How to use Spire.Doc on .NET Core

On Linux, you need to install Libgdiplus library independently. Please follow the below three steps to install it successfully.

Step1: Open xxxx.csproj file and write the code snippet into it.

<ItemGroup>
<PackageReference Include=”Spire.Doc” Version=”6.7.13”/>
</ItemGroup>

How to use Spire.Doc on .NET Core

Step 2. Execute "dotnet build" command on terminal window of Visual Studio Code. It will install Spire.Doc package.

How to use Spire.Doc on .NET Core

Step 3. Open terminal window on Linux and execute Libgdiplus library installation command, for example, the command on Linux Ubuntu is "sudo apt-get install libgdiplus".

How to use Spire.Doc on .NET Core

Then you can use Spire.Doc on .NET core.

If you have any question, please feel free to contact us.

Tuesday, 24 July 2018 07:10

Replace Text with a Word document in C#

Spire.Doc provides several overloaded Replace methods to replace text in different scenarios. This article is going to show you how to replace a specified text in a template document with another document using Spire.Doc.

The template document:

Replace Text with a Word document in C#

The document to replace text:

Replace Text with a Word document in C#

Detail steps:

Step 1: Load a template document.

Document document = new Document("Template.docx");

Step 2: Load another document to replace text.

IDocument replaceDocument = new Document("Document1.docx");

Step 3: Replace specified text with the other document.

document.Replace("Document 1", replaceDocument, false, true);  

Step 4: Save the file.

document.SaveToFile("Output.docx", FileFormat.Docx2013);

Output:

Replace Text with a Word document in C#

Full code:

using Spire.Doc;
using Spire.Doc.Interface;

namespace Replace_Text_With_Document
{
    class Program
    {
        static void Main(string[] args)
        {
            //Load a template document 
            Document document = new Document("Template.docx");

            //Load another document to replace text
            IDocument replaceDocument = new Document("Document1.docx");

            //Replace specified text with the other document
            document.Replace("Document 1", replaceDocument, false, true);

            //Save the file
            document.SaveToFile("Output.docx", FileFormat.Docx2013);
        }
    }
}

The following code snippets show you how we can find x and y coordinates of an image in a PDF document.

Step 1: Create a PdfDocument object and load a sample PDF file.

PdfDocument doc = new PdfDocument();
doc.LoadFromFile("sample.pdf");

Step 2: Get all the image information from the specified page.

PdfImageInfo[] imageInfo = doc.Pages[0].ImagesInfo;

Step 3: Get the x and y coordinates of the first image through Bounds property.

RectangleF rect = imageInfo[0].Bounds;
Console.WriteLine("The image is located at({0},{1})", rect.X, rect.Y);

Output:

How to Get X/Y Position of Image in PDF in C#, VB.NET

Full Code:

[C#]
PdfDocument doc = new PdfDocument();
doc.LoadFromFile("sample.pdf");
PdfImageInfo[] imageInfo = doc.Pages[0].ImagesInfo;
RectangleF rect = imageInfo[0].Bounds;
Console.WriteLine("The image is located at({0},{1})", rect.X, rect.Y);
[VB.NET]
Dim doc As PdfDocument = New PdfDocument()
doc.LoadFromFile("sample.pdf")
Dim imageInfo As PdfImageInfo() = doc.Pages(0).ImagesInfo
Dim rect As RectangleF = imageInfo(0).Bounds
Console.WriteLine("The image is located at({0},{1})", rect.X, rect.Y)

This article elaborates the steps to apply soft edges effect to an excel chart using Spire.XLS.

The example Excel file we used for demonstration:

Apply Soft Edges effect to Excel Chart in C#

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: Get the chart.

IChart chart = sheet.Charts[0];

Step 4: Specify the size of the soft edge. Value can be set from 0 to 100.

chart.ChartArea.Shadow.SoftEdge = 10;

Step 5: Save the file.

workbook.SaveToFile("Output.xlsx", ExcelVersion.Version2013);

Output:

Apply Soft Edges effect to Excel Chart in C#

Full code:

using Spire.Xls;
using Spire.Xls.Core;

namespace Soft_Edges_in_Excel_Chart
{
    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];

            //Get the chart
            IChart chart = sheet.Charts[0];

            //Specify the size of the soft edge. Value can be set from 0 to 100
            chart.ChartArea.Shadow.SoftEdge = 10;

            //Save the file
            workbook.SaveToFile("Output.xlsx", ExcelVersion.Version2013);
        }
    }
}

Hyperlinks in a PowerPoint file not only can be linked to external URLs, but also to the specific slide within the document. This article will show you how to create a hyperlink that links to a specified slide using Spire.Presentation.

Step 1: Create a PowerPoint file and append a slide to it.

Presentation presentation = new Presentation();
presentation.Slides.Append();

Step 2: Add a shape to the second slide.

IAutoShape shape = presentation.Slides[1].Shapes.AppendShape(ShapeType.Rectangle, new RectangleF(10, 50, 200, 50));
shape.TextFrame.Text = "Jump to the first slide"; 

Step 3: Create a hyperlink based on the shape and the text on it, linking to the first slide .

ClickHyperlink hyperlink = new ClickHyperlink(presentation.Slides[0]);
shape.Click = hyperlink;
shape.TextFrame.TextRange.ClickAction = hyperlink;

Step 4: Save the file.

presentation.SaveToFile("hyperlink.pptx", FileFormat.Pptx2010);

Output:

How to Link to a Specific Slide in PowerPoint in C#, VB.NET

Full Code:

[C#]
Presentation presentation = new Presentation();
presentation.Slides.Append();
IAutoShape shape = presentation.Slides[1].Shapes.AppendShape(ShapeType.Rectangle, new RectangleF(10, 50, 200, 50));
shape.Fill.FillType = FillFormatType.None;
shape.Line.FillType = FillFormatType.None;
shape.TextFrame.Text = "Jump to the first slide";
ClickHyperlink hyperlink = new ClickHyperlink(presentation.Slides[0]);
shape.Click = hyperlink;
shape.TextFrame.TextRange.ClickAction = hyperlink;
presentation.SaveToFile("output.pptx", FileFormat.Pptx2010);
[VB.NET]
Dim presentation As Presentation = New Presentation()
presentation.Slides.Append()
Dim shape As IAutoShape = presentation.Slides(1).Shapes.AppendShape(ShapeType.Rectangle, New RectangleF(10, 50, 200, 50))
shape.Fill.FillType = FillFormatType.None
shape.Line.FillType = FillFormatType.None
shape.TextFrame.Text = "Jump to the first slide"
Dim hyperlink As ClickHyperlink = New ClickHyperlink(presentation.Slides(0))
shape.Click = hyperlink
shape.TextFrame.TextRange.ClickAction = hyperlink
presentation.SaveToFile("output.pptx", FileFormat.Pptx2010)