Filter cells by cell color in Excel in C#
In Excel, cells can be filtered based on the cell color. This article is going to show you how to filter rows by cell color using Spire.XLS.
The example Excel file:
Detail steps:
Step 1: Instantiate a Workbook object and load the Excel file.
Workbook workbook = new Workbook();
workbook.LoadFromFile("sample.xlsx");
Step 2: Get the first worksheet.
Worksheet sheet = workbook.Worksheets[0];
Step 3: Add a color filter to filter cells based on cell color.
//Create an auto filter in the sheet and specify the range to be filterd sheet.AutoFilters.Range = sheet.Range["A1:A9"]; //Get the coloumn to be filterd FilterColumn filtercolumn = (FilterColumn)sheet.AutoFilters[0]; //Add a color filter to filter the column based on cell color sheet.AutoFilters.AddFillColorFilter(filtercolumn, Color.Red);
Step 4: Filter the data.
sheet.AutoFilters.Filter();
Step 5: Save the file.
workbook.SaveToFile("ColorFilter.xlsx", ExcelVersion.Version2013);
Screenshot:
Full code:
//Instantiate a Workbook object
Workbook workbook = new Workbook();
//Load the Excel file
workbook.LoadFromFile("sample.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Create an auto filter in the sheet and specify the range to be filterd
sheet.AutoFilters.Range = sheet.Range["A1:A9"];
//Get the coloumn to be filterd
FilterColumn filtercolumn = (FilterColumn)sheet.AutoFilters[0];
//Add a color filter to filter the column based on cell color
sheet.AutoFilters.AddFillColorFilter(filtercolumn, Color.Red);
//Filter the data
sheet.AutoFilters.Filter();
//Save the file
workbook.SaveToFile("ColorFilter.xlsx", ExcelVersion.Version2013);
How to Add Autofilter to Excel in C#, VB.NET
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:
Full Code:
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);
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)
Add a Picture as a Custom Bullet Style in PowerPoint in C#
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:
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:
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);
}
}
}
Adjust the Height of Headers and Footers in a Word document in C#
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:
Footer:
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);
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:
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:
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);
Add Exit Animation Effect to a Shape in PowerPoint in C#
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:
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);
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.
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>
Step 2. Execute "dotnet build" command on terminal window of Visual Studio Code. It will install Spire.Doc package.
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".
Then you can use Spire.Doc on .NET core.
If you have any question, please feel free to contact us.
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:
The document to replace text:
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:
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);
}
}
}
Get X/Y Position of Image in PDF in C#, VB.NET
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:
Full Code:
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);
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)
Apply Soft Edges effect to Excel Chart in C#
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:
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:
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);
}
}
}
