.NET (1327)
Children categories
We usually add lots of images to make the PowerPoint presentation obvious and attractive. With Spire.Presentation, we can easily extract the text from the presentation slides, and we can extract all the images from the whole PowerPoint document file. From Spire.Presentation v 2.5.21, now it supports to get all images from a single specific slide. This article will demonstrate you how to extract all images from a slide.
Firstly, please view the whole PowerPoint slides with images and these images need to be extracted.
Note: Before Start, please download the latest version of Spire.Presentation and add Spire.Presentaion.dll in the bin folder as the reference of Visual Studio.
Here come to the code snippet of how to extract the images from a slide.
Step 1: Create a Presentation document and load from file.
Presentation PPT = new Presentation();
PPT.LoadFromFile("sample.pptx");
Step 2: Get the pictures on the first slide and save them to image file.
int i = 0;
foreach (IShape s in PPT.Slides[0].Shapes)
{
if (s is SlidePicture)
{
SlidePicture ps = s as SlidePicture;
ps.PictureFill.Picture.EmbedImage.Image.Save(string.Format("{0}.png", i));
i++;
}
if (s is PictureShape)
{
PictureShape ps = s as PictureShape;
ps.EmbedImage.Image.Save(string.Format("{0}.png", i));
i++;
}
}
Effective screenshots of the extracted images:
Full codes:
using Spire.Presentation;
namespace ExtractImage
{
class Program
{
static void Main(string[] args)
{
Presentation PPT = new Presentation();
PPT.LoadFromFile("sample.pptx");
int i = 0;
foreach (IShape s in PPT.Slides[0].Shapes)
{
if (s is SlidePicture)
{
SlidePicture ps = s as SlidePicture;
ps.PictureFill.Picture.EmbedImage.Image.Save(string.Format("{0}.png", i));
i++;
}
if (s is PictureShape)
{
PictureShape ps = s as PictureShape;
ps.EmbedImage.Image.Save(string.Format("{0}.png", i));
i++;
}
}
}
}
}
In PDF, an attachment is an additional file that is attached to a PDF document. There are many kinds of attachments, such as a document, an image file or other supported file types.
Spire.PDF, as a professional library, enables us to add various attachments as per our needs without having Adobe Acrobat been installed on system. This article demonstrates how to add attachments - a word document and an image to PDF in WPF applications using Spire.PDF for WPF.
Please see the following effective screenshot after adding attachments:
Double click the attachment icon, the attached file will be open automatically.
Detail steps:
Use following namespace:
using System; using System.Drawing; using System.IO; using System.Windows; using Spire.Pdf; using Spire.Pdf.Annotations; using Spire.Pdf.Graphics;
Step 1: Load the original PDF document and add a new page to it.
PdfDocument doc = new PdfDocument("Sales Report.pdf");
PdfPageBase page = section.Pages.Add();
Step 2: In the new page, draw a title for the attachments.
float y = 10;
PdfBrush brush1 = PdfBrushes.CornflowerBlue;
PdfTrueTypeFont font1 = new PdfTrueTypeFont(new Font("Arial", 16f, System.Drawing.FontStyle.Bold));
PdfStringFormat format1 = new PdfStringFormat(PdfTextAlignment.Center);
page.Canvas.DrawString("Attachments", font1, brush1, page.Canvas.ClientSize.Width / 2, y, format1);
Step 3: Load the file that needs to be attached using the File.ReadAllBytes(string path) method, then add the file to the specified page of the PDF document as attachment.
Add Word document attachment with attachment annotation:
PdfTrueTypeFont font2 = new PdfTrueTypeFont(new Font("Arial", 12f, System.Drawing.FontStyle.Bold));
PointF location = new PointF(50, y);
String label = "Sales Report";
byte[] data = File.ReadAllBytes("Sales Report.docx");
SizeF size = font2.MeasureString(label);
RectangleF bounds = new RectangleF(location, size);
page.Canvas.DrawString(label, font2, PdfBrushes.MediumPurple, bounds);
bounds = new RectangleF(bounds.Right + 3, bounds.Top, font2.Height / 2, font2.Height);
PdfAttachmentAnnotation annotation1 = new PdfAttachmentAnnotation(bounds, "Sales Report.docx", data);
annotation1.Color = Color.Teal;
annotation1.Flags = PdfAnnotationFlags.NoZoom;
annotation1.Icon = PdfAttachmentIcon.Graph;
annotation1.Text = "Sales Report.docx";
(page as PdfNewPage).Annotations.Add(annotation1);
Add image attachment is similar with adding Word attachment, refer to following codes:
location = new PointF(50, y);
label = "Vendors Info";
data = File.ReadAllBytes("Vendors Info.png");
size = font2.MeasureString(label);
bounds = new RectangleF(location, size);
page.Canvas.DrawString(label, font2, PdfBrushes.DarkOrange, bounds);
bounds = new RectangleF(bounds.Right + 3, bounds.Top, font2.Height / 2, font2.Height);
PdfAttachmentAnnotation annotation2 = new PdfAttachmentAnnotation(bounds, "Vendors Info.png", data);
annotation2.Color = Color.Orange;
annotation2.Flags = PdfAnnotationFlags.ReadOnly;
annotation2.Icon = PdfAttachmentIcon.PushPin;
annotation2.Text = "Vendors info image";
(page as PdfNewPage).Annotations.Add(annotation2);
Step 4: Save and launch the file.
doc.SaveToFile("Attachment.pdf");
System.Diagnostics.Process.Start("Attachment.pdf");
Full codes:
private void button1_Click(object sender, RoutedEventArgs e)
{
//Load the original PDF document.
PdfDocument doc = new PdfDocument("Sales Report.pdf");
//Add a new page
PdfPageBase page = doc.Pages.Add();
float y = 10;
//Set title text
PdfBrush brush1 = PdfBrushes.CornflowerBlue;
PdfTrueTypeFont font1 = new PdfTrueTypeFont(new Font("Arial", 16f, System.Drawing.FontStyle.Bold));
PdfStringFormat format1 = new PdfStringFormat(PdfTextAlignment.Center);
page.Canvas.DrawString("Attachments", font1, brush1, page.Canvas.ClientSize.Width / 2, y, format1);
y = y + font1.MeasureString("Attachments", format1).Height;
y = y + 5;
//Add Word document attachment
PdfTrueTypeFont font2 = new PdfTrueTypeFont(new Font("Arial", 12f, System.Drawing.FontStyle.Bold));
PointF location = new PointF(50, y);
String label = "Sales Report";
byte[] data = File.ReadAllBytes("Sales Report.docx");
SizeF size = font2.MeasureString(label);
RectangleF bounds = new RectangleF(location, size);
page.Canvas.DrawString(label, font2, PdfBrushes.MediumPurple, bounds);
bounds = new RectangleF(bounds.Right + 3, bounds.Top, font2.Height / 2, font2.Height);
PdfAttachmentAnnotation annotation1 = new PdfAttachmentAnnotation(bounds, "Sales Report.docx", data);
annotation1.Color = Color.Teal;
annotation1.Flags = PdfAnnotationFlags.NoZoom;
annotation1.Icon = PdfAttachmentIcon.Graph;
annotation1.Text = "Sales Report.docx";
(page as PdfNewPage).Annotations.Add(annotation1);
y = y + size.Height + 2;
//Add image attachment
location = new PointF(50, y);
label = "Vendors Info";
data = File.ReadAllBytes("Vendors Info.png");
size = font2.MeasureString(label);
bounds = new RectangleF(location, size);
page.Canvas.DrawString(label, font2, PdfBrushes.DarkOrange, bounds);
bounds = new RectangleF(bounds.Right + 3, bounds.Top, font2.Height / 2, font2.Height);
PdfAttachmentAnnotation annotation2 = new PdfAttachmentAnnotation(bounds, "Vendors Info.png", data);
annotation2.Color = Color.Orange;
annotation2.Flags = PdfAnnotationFlags.ReadOnly;
annotation2.Icon = PdfAttachmentIcon.PushPin;
annotation2.Text = "Vendors info image";
(page as PdfNewPage).Annotations.Add(annotation2);
//Save and launch the file.
doc.SaveToFile("Attachment.pdf");
System.Diagnostics.Process.Start("Attachment.pdf");
}
Usually, when you open a PDF document, the blank background looks a little drab. You might want to create background for your PDF document to make them more visually appealing through the using of a background image.
In the following sections, I will demonstrate how to insert PDF background image in WPF.
Here is the original PDF document without background image.
The code snippets are as followed:
Step 1: Initialize a new instance of PdfDocument class and load the PDF document from the file.
PdfDocument doc = new PdfDocument();
doc.LoadFromFile("To a Skylark.pdf");
Step 2: In this example, we choose the first page of PDF file to insert the background image.
PdfPageBase page = doc.Pages[0];
Step 3: Load the image from file and set it as background image.
System.Drawing.Image backgroundImage = System.Drawing.Image.FromFile("Sky.jpg");
page.BackgroundImage = backgroundImage;
Step 4: Save the PDF document and launch the file.
doc.SaveToFile("With Background Image.pdf");
System.Diagnostics.Process.Start("With Background Image.pdf");
Effective screenshot:
Full Codes:
using System.Windows;
using Spire.Pdf;
namespace Poetic_Works
{
///
/// Interaction logic for MainWindow.xaml
///
public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
}
private void button2_Click(object sender, RoutedEventArgs e)
{
PdfDocument doc = new PdfDocument();
doc.LoadFromFile("To a Skylark.pdf");
PdfPageBase page = doc.Pages[0];
System.Drawing.Image backgroundImage = System.Drawing.Image.FromFile("Sky.jpg");
page.BackgroundImage = backgroundImage;
doc.SaveToFile("With Background Image.pdf");
System.Diagnostics.Process.Start("With Background Image.pdf");
}
}
}
Imports System.Windows
Imports Spire.Pdf
Namespace Poetic_Works
'''
''' Interaction logic for MainWindow.xaml
'''
Public Partial Class MainWindow
Inherits Window
Public Sub New()
InitializeComponent()
End Sub
Private Sub button2_Click(sender As Object, e As RoutedEventArgs)
Dim doc As New PdfDocument()
doc.LoadFromFile("To a Skylark.pdf")
Dim page As PdfPageBase = doc.Pages(0)
Dim backgroundImage As System.Drawing.Image = System.Drawing.Image.FromFile("Sky.jpg")
page.BackgroundImage = backgroundImage
doc.SaveToFile("With Background Image.pdf")
System.Diagnostics.Process.Start("With Background Image.pdf")
End Sub
End Class
End Namespace
Tooltip is a message box that appears when users hover the pointer over the form field, providing users with instructions about the field, for instance, some extra information that users may find helpful in filling in the form field. This article presents how to add a tooltip to PDF form field using Spire.PDF in C# and VB.NET.
Code Snippet
Step 1: Initialize a new object of PdfDcoument class, add a page to it.
PdfDocument doc = new PdfDocument(); PdfPageBase page = doc.Pages.Add(PdfPageSize.A4, new PdfMargins(0));
Step 2: Add some text and a textbox on the PDF page.
PdfFont font = new PdfFont(PdfFontFamily.Helvetica, 12f,PdfFontStyle.Bold); PdfBrush brush = PdfBrushes.Black; float x = 50; float y = 50; float tempX = 0; string text = "E-mail: "; page.Canvas.DrawString(text, font, brush, x, y); tempX = font.MeasureString(text).Width + x+15; PdfTextBoxField textbox = new PdfTextBoxField(page, "TextBox"); textbox.Bounds = new RectangleF(tempX, y, 100, 15); textbox.BorderWidth = 0.75f; textbox.BorderStyle = PdfBorderStyle.Solid; doc.Form.Fields.Add(textbox);
Step 3: The PdfDocument class provides a Fields collection which represents all form fields in the PDF document. You can get the specific field by its index or name, then set the value of ToolTip property to add a tooltip.
doc.Form.Fields["TextBox"].ToolTip = "Please insert a valid email address";
Step 4: Save and launch the file.
doc.SaveToFile("sample.pdf", FileFormat.PDF);
System.Diagnostics.Process.Start("sample.pdf");
Output:
Full Code:
using Spire.Pdf;
using Spire.Pdf.Fields;
using Spire.Pdf.Graphics;
using System.Drawing;
namespace AddTooltip
{
class Program
{
static void Main(string []args)
{
PdfDocument doc = new PdfDocument();
PdfPageBase page = doc.Pages.Add(PdfPageSize.A4, new PdfMargins(0));
PdfFont font = new PdfFont(PdfFontFamily.Helvetica, 12f, PdfFontStyle.Bold);
PdfBrush brush = PdfBrushes.Black;
float x = 50;
float y = 50;
float tempX = 0;
string text = "E-mail: ";
page.Canvas.DrawString(text, font, brush, x, y);
tempX = font.MeasureString(text).Width + x + 15;
PdfTextBoxField textbox = new PdfTextBoxField(page, "TextBox");
textbox.Bounds = new RectangleF(tempX, y, 100, 15);
textbox.BorderWidth = 0.75f;
textbox.BorderStyle = PdfBorderStyle.Solid;
doc.Form.Fields.Add(textbox);
doc.Form.Fields["TextBox"].ToolTip = "Please insert a valid email address";
doc.SaveToFile("sample.pdf", FileFormat.PDF);
System.Diagnostics.Process.Start("sample.pdf");
}
}
}
Imports Spire.Pdf
Imports Spire.Pdf.Fields
Imports Spire.Pdf.Graphics
Imports System.Drawing
Namespace AddTooltip
Class Program
Private Shared Sub Main(args As String())
Dim doc As New PdfDocument()
Dim page As PdfPageBase = doc.Pages.Add(PdfPageSize.A4, New PdfMargins(0))
Dim font As New PdfFont(PdfFontFamily.Helvetica, 12F, PdfFontStyle.Bold)
Dim brush As PdfBrush = PdfBrushes.Black
Dim x As Single = 50
Dim y As Single = 50
Dim tempX As Single = 0
Dim text As String = "E-mail: "
page.Canvas.DrawString(text, font, brush, x, y)
tempX = font.MeasureString(text).Width + x + 15
Dim textbox As New PdfTextBoxField(page, "TextBox")
textbox.Bounds = New RectangleF(tempX, y, 100, 15)
textbox.BorderWidth = 0.75F
textbox.BorderStyle = PdfBorderStyle.Solid
doc.Form.Fields.Add(textbox)
doc.Form.Fields("TextBox").ToolTip = "Please insert a valid email address"
doc.SaveToFile("sample.pdf", FileFormat.PDF)
System.Diagnostics.Process.Start("sample.pdf")
End Sub
End Class
End Namespace
We always use chart in Excel documents to make the data more clear and visible. With the help of Spire.XLS, developers can easily create different kinds of charts, such as Pie chart, column chart, bar chart, radar chart and so on. This article will focus on demonstrate how to create Excel pie chart on WPF applications.
Note: Before Start, please download the latest version of Spire.XLS and add Spire.Xls.Wpf.dll in the bin folder as the reference of Visual Studio.
Here comes to the code snippets of how to create Excel pie chart:
Step 1: Create a new Excel workbook and load from file.
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Step 2: Get the first worksheet from workbook.
Worksheet sheet = workbook.Worksheets[0];
Step 3: Add Chart and set chart type.
Chart chart = sheet.Charts.Add(ExcelChartType.Pie);
Step 4: Set chart data range.
chart.DataRange = sheet.Range["B2:B8"]; chart.SeriesDataFromRange = false;
Step 5: Set the position, border, title and legend for the chart.
//Chart Position chart.LeftColumn = 1; chart.TopRow =12; chart.RightColumn = 8; chart.BottomRow = 26; //Chart Border chart.ChartArea.Border.Weight = ChartLineWeightType.Medium; chart.ChartArea.Border.Color = System.Drawing.Color.SandyBrown; //Chart Title chart.ChartTitle = "Parts Sales Info"; chart.ChartTitleArea.Font.FontName = "Calibri"; chart.ChartTitleArea.Font.Size = 10; chart.ChartTitleArea.Font.IsBold = true; //Chart Legend chart.Legend.Position = LegendPositionType.Right;
Step 6: Save the document to file and launch to preview it.
workbook.SaveToFile("ExcelPieChart.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("ExcelPieChart.xlsx");
Effective screenshot of the generated pie chart by Spire.XLS for WPF:
Full codes:
using Spire.Xls;
using System.Windows;
namespace WpfApplication1
{
public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
}
private void button2_Click(object sender, RoutedEventArgs e)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet sheet = workbook.Worksheets[0];
Chart chart = sheet.Charts.Add(ExcelChartType.Pie);
chart.DataRange = sheet.Range["B2:B8"];
chart.SeriesDataFromRange = false;
chart.LeftColumn = 1;
chart.TopRow = 12;
chart.RightColumn = 8;
chart.BottomRow = 26;
chart.ChartArea.Border.Weight = ChartLineWeightType.Medium;
chart.ChartArea.Border.Color = System.Drawing.Color.SandyBrown;
chart.ChartTitle = "Parts Sales Info";
chart.ChartTitleArea.Font.FontName = "Calibri";
chart.ChartTitleArea.Font.Size = 10;
chart.ChartTitleArea.Font.IsBold = true;
chart.Legend.Position = LegendPositionType.Right;
workbook.SaveToFile("ExcelPieChart.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("ExcelPieChart.xlsx");
}
}
}
An annotation is a note or comment added to a pdf file, it can be an explanation or a reference to the specific part of the original data. Add annotations can make the file becomes easier to understand for readers.
Spire.PDF enables us to add, edit and delete annotation from a PDF document. This section will demonstrate how to add text annotation and edit annotation in pdf from WPF Applications using Spire.PDF for WPF.
This is the effective screenshot after adding and editing annotation:
After editing, the text content of the annotation changed from “Demo” to “This is an annotation”.
At first, use the following namespace:
using System.Drawing; using System.Windows; using Spire.Pdf; using Spire.Pdf.Annotations; using Spire.Pdf.Graphics;
Then refer to the detail steps below:
Step 1: Create a new PDF document and add a new page to it.
PdfDocument doc = new PdfDocument(); PdfPageBase page = doc.Pages.Add();
Step 2: Draw text in the page and set text font, font size, color and location.
PdfFont font = new PdfFont(PdfFontFamily.TimesRoman, 15); string text = "Spire.PDF"; PdfSolidBrush brush = new PdfSolidBrush(Color.DeepSkyBlue); PointF point = new PointF(50, 100); page.Canvas.DrawString(text, font, brush, point);
Step 3: Add annotation.
First, create a text annotation using the PdfTextMarkupAnnotation class:
PdfTextMarkupAnnotation annotation = new PdfTextMarkupAnnotation("Author", "Demo", text, new PointF(0, 0), font);
There are four parameters of the annotation:
“Author”: the author of annotation. “Demo”: text content of annotation. text: the text which will be marked. new PointF(0, 0): not supported at present. font: the font of the text which will be marked.
Second, Set Border, TextMarkupColor and location of the annotation:
annotation.Border = new PdfAnnotationBorder(0.50f); annotation.TextMarkupColor = Color.LemonChiffon; annotation.Location = new PointF(point.X + doc.PageSettings.Margins.Left, point.Y + doc.PageSettings.Margins.Left);
Third, add the annotation to the specified page:
(page as PdfNewPage).Annotations.Add(annotation);
If you need to edit the text of the annotation, then you can use following code:
(page as PdfNewPage).Annotations[0].Text = "This is an annotation";
Step 4: Save and launch the file.
doc.SaveToFile("Annotation.pdf");
System.Diagnostics.Process.Start("Annotation.pdf");
Full codes:
private void button1_Click(object sender, RoutedEventArgs e)
{
PdfDocument doc = new PdfDocument();
PdfPageBase page = doc.Pages.Add();
PdfFont font = new PdfFont(PdfFontFamily.TimesRoman, 15);
string text = "Spire.PDF";
PdfSolidBrush brush = new PdfSolidBrush(Color.DeepSkyBlue);
PointF point = new PointF(50, 100);
page.Canvas.DrawString(text, font, brush, point);
//Add annotation
PdfTextMarkupAnnotation annotation = new PdfTextMarkupAnnotation("Author", "Demo", text, new PointF(0, 0), font);
annotation.Border = new PdfAnnotationBorder(0.50f);
annotation.TextMarkupColor = Color.LemonChiffon;
annotation.Location = new PointF(point.X + doc.PageSettings.Margins.Left, point.Y + doc.PageSettings.Margins.Left);
(page as PdfNewPage).Annotations.Add(annotation);
//Edit the text of the annotation
(page as PdfNewPage).Annotations[0].Text = "This is an annotation";
doc.SaveToFile("Annotation.pdf");
System.Diagnostics.Process.Start("Annotation.pdf");
}
Whenever you are working with lots of data, it can be difficult to compare information in your workbook. You may want to see certain rows or columns all the time in your worksheet, especially header cells. By freezing rows or columns in place, you can keep rows or columns visible while scrolling through the rest of the worksheet.
In the following sections, I will demonstrate how to freeze Excel panes in WPF.
Step 1: Initialize a new instance of Workbook class. Load the word document from the file.
Workbook workbook = new Workbook();
workbook.LoadFromFile("SalesReport.xlsx");
Step 2: In our example, my workbook has several worksheets. We want to check the data from the second worksheet. Therefore, we select the second worksheet.
Worksheet sheet = workbook.Worksheets[1];
Step 3: In this case, we want to fix the first two rows and the leftmost column. The row and column will be frozen in place, as indicated by the solid grey line.
sheet.FreezePanes(2, 1);
Step 4: Save the workbook and launch the file.
workbook.SaveToFile("SalesReport Result.xlsx");
System.Diagnostics.Process.Start("SalesReport Result.xlsx");
Effective screenshot:
Full Codes:
using System.Windows;
using Spire.Xls;
namespace SalesReport
{
///
/// Interaction logic for MainWindow.xaml
///
public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
}
private void button1_Click(object sender, RoutedEventArgs e)
{
//Load Excel File
Workbook workbook = new Workbook();
workbook.LoadFromFile("SalesReport.xlsx");
//Select the second worksheet
Worksheet sheet = workbook.Worksheets[1];
//Select to freeze the first two rows and the leftmost column
sheet.FreezePanes(3, 2);
//Save and Launch
workbook.SaveToFile("SalesReport Result.xlsx");
System.Diagnostics.Process.Start("SalesReport Result.xlsx");
}
}
}
Imports System.Windows
Imports Spire.Xls
Namespace SalesReport
'''
''' Interaction logic for MainWindow.xaml
'''
Public Partial Class MainWindow
Inherits Window
Public Sub New()
InitializeComponent()
End Sub
Private Sub button1_Click(sender As Object, e As RoutedEventArgs)
'Load Excel File
Dim workbook As New Workbook()
workbook.LoadFromFile("SalesReport.xlsx")
'Select the second worksheet
Dim sheet As Worksheet = workbook.Worksheets(1)
'Select to freeze the first two rows and the leftmost column
sheet.FreezePanes(3, 2)
'Save and Launch
workbook.SaveToFile("SalesReport Result.xlsx")
System.Diagnostics.Process.Start("SalesReport Result.xlsx")
End Sub
End Class
End Namespace
A formula is an expression which performs calculations or other actions on the data in your worksheet. Using Spire.XLS, programmers can easily add, modify and calculate formulas in Excel cells. This guide will present how to add commonly used formulas to Excel with C# in WPF application.
Spire.XLS provides a class named CellRange, containing properties such as Formula, FormulaValue and HasFormula which allow users to work with formulas in specific cells. For adding a formula, the syntax could be as easy as follows:
sheet.Range[int row, int column].Formula= "=NOW()";
Apart from adding a formula for date and time, Spire.XLS also supports text, math, logical, statistical, lookup, reference and etc. Following section shows a list of over 40 formula examples (partially shown in the below screenshot) created by Spire.XLS.
Entire Code:
using Spire.Xls;
using System.Windows;
namespace WpfApplication1
{
public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
}
private void button1_Click(object sender, RoutedEventArgs e)
{
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
int currentRow = 1;
string currentFormula = string.Empty;
sheet.SetColumnWidth(1, 32);
sheet.SetColumnWidth(2, 16);
sheet.SetColumnWidth(3, 16);
sheet.Range[currentRow++, 1].Value = "Examples of formulas :";
sheet.Range[++currentRow, 1].Value = "Test data:";
CellRange range = sheet.Range["A1"];
range.Style.Font.IsBold = true;
range.Style.FillPattern = ExcelPatternType.Solid;
range.Style.KnownColor = ExcelColors.LightGreen1;
range.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium;
//test data
sheet.Range[currentRow, 2].NumberValue = 7.3;
sheet.Range[currentRow, 3].NumberValue = 5; ;
sheet.Range[currentRow, 4].NumberValue = 8.2;
sheet.Range[currentRow, 5].NumberValue = 4;
sheet.Range[currentRow, 6].NumberValue = 3;
sheet.Range[currentRow, 7].NumberValue = 11.3;
sheet.Range[++currentRow, 1].Value = "Formulas"; ;
sheet.Range[currentRow, 2].Value = "Results";
range = sheet.Range[currentRow, 1, currentRow, 2];
//range.Value = "Formulas";
range.Style.Font.IsBold = true;
range.Style.KnownColor = ExcelColors.LightGreen1;
range.Style.FillPattern = ExcelPatternType.Solid;
range.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium;
//str.
currentFormula = "=\"hello\"";
sheet.Range[++currentRow, 1].Text = "=\"hello\"";
sheet.Range[currentRow, 2].Formula = currentFormula;
sheet.Range[currentRow, 3].Formula = "=\"" + new string(new char[] { '\u4f60', '\u597d' }) + "\"";
//int.
currentFormula = "=300";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
// float
currentFormula = "=3389.639421";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
//bool.
currentFormula = "=false";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
currentFormula = "=1+2+3+4+5-6-7+8-9";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
currentFormula = "=33*3/4-2+10";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
// sheet reference
currentFormula = "=Sheet1!$B$3";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
// sheet area reference
currentFormula = "=AVERAGE(Sheet1!$D$3:G$3)";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
// Functions
currentFormula = "=Count(3,5,8,10,2,34)";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
currentFormula = "=NOW()";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow, 2].Formula = currentFormula;
sheet.Range[currentRow, 2].Style.NumberFormat = "yyyy-MM-DD";
currentFormula = "=SECOND(11)";
sheet.Range[++currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=MINUTE(12)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=MONTH(9)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=DAY(10)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=TIME(4,5,7)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=DATE(6,4,2)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=RAND()";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=HOUR(12)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=MOD(5,3)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=WEEKDAY(3)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=YEAR(23)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=NOT(true)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=OR(true)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=AND(TRUE)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=VALUE(30)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=LEN(\"world\")";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=MID(\"world\",4,2)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=ROUND(7,3)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=SIGN(4)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=INT(200)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=ABS(-1.21)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=LN(15)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=EXP(20)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=SQRT(40)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=PI()";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=COS(9)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=SIN(45)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=MAX(10,30)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=MIN(5,7)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=AVERAGE(12,45)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=SUM(18,29)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=IF(4,2,2)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
currentFormula = "=SUBTOTAL(3,Sheet1!B2:E3)";
sheet.Range[currentRow, 1].Text = currentFormula;
sheet.Range[currentRow++, 2].Formula = currentFormula;
workbook.SaveToFile(@"..\..\Sample.xls");
System.Diagnostics.Process.Start(@"..\..\Sample.xls");
}
}
}
To view the large amount of Excel data's easily, Spire.XLS for .NET supports create Pivot table and add excel table with filters. Spire.XLS also offers a method of pivotTable.ReportFilters.Add(); to enable developers to add the report filter to the pivot table.
This article will show you how to add a report filter to the Excel Pivot table in C#.
Note: Before Start, please download the latest version of Spire.XLS and add Spire.xls.dll in the bin folder as the reference of Visual Studio.
Firstly, please check the original screenshot of excel pivot table.
Step 1: Create a new workbook and load from file.
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Step 2: Get the PivotTable from the Excel worksheet.
Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable pivotTable = workbook.Worksheets["Pivot Table"].PivotTables[0] as Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable;
Step 3: Add a filter to the pivot table.
PivotReportFilter filter = new PivotReportFilter("JAN", true);
pivotTable.ReportFilters.Add(filter);
Step 4: Save the document to file and launch to preview it.
workbook.SaveToFile("Result.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("result.xlsx");
Effective screenshot after add a filter to the pivot table:
Full codes:
using Spire.Xls;
using Spire.Xls.Core.Spreadsheet.PivotTables;
namespace AddReportFilter
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable pivotTable = workbook.Worksheets["Pivot Table"].PivotTables[0] as Spire.Xls.Core.Spreadsheet.PivotTables.XlsPivotTable;
PivotReportFilter filter = new PivotReportFilter("JAN", true);
pivotTable.ReportFilters.Add(filter);
workbook.SaveToFile("Result.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("result.xlsx");
}
}
}
Generally we add predefined information or insert elements such as data, time and file name to Excel header or footer for printing purpose. Header or footer can be manually inserted or modified in Page Layout View or from Page Setup dialog box.
This article will present how to insert header and footer at runtime using Spire.XLS for WPF. Spire.XLS provides a class of PageSetup that contains all the page setup settings including header and footer, and a set of script commands that are used to set header and footer formatting.
Script Commands:
| Commands | Description |
| &G | A picture |
| &D | The current data |
| &T | The current time |
| &A | Worksheet name |
| &F | File name |
| &B | Make text bold |
| &I | Italicize text |
| &<Font name> | Font name. For example: &"Arial" |
| &K<Html color code> | Font color. For example: &KFFFFF0 |
Code Snippets:
Step 1: Initialize a new instance of Workbook class and get the first sheet from workbook.
Workbook wb = new Workbook(); Worksheet sheet = wb.Worksheets[0];
Step 2: Insert header text with formatting (specified font name, size and color) to the left part of Excel header.
sheet.PageSetup.LeftHeader ="&\"Showcard Gothic\"&14&K8B2252 Header and Footer Sample";
Step 3: Insert footer with formatting to the center part of Excel footer.
sheet.PageSetup.CenterFooter = "&B Copyright © 2016 e-iceblue. All Rights Reserved.";
Step 4: Save and launch the file.
wb.SaveToFile(@"..\Sample.xls"); System.Diagnostics.Process.Start(@"..\Sample.xls");
Output:
Header
Footer
Full Code:
using System.Drawing;
using System.Windows;
namespace WpfApplication1
{
public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
}
private void button1_Click(object sender, RoutedEventArgs e)
{
Workbook wb = new Workbook();
Worksheet sheet = wb.Worksheets[0];
sheet.PageSetup.LeftHeader = "&\"Showcard Gothic\"&14&K8B2252 Header and Footer Sample";
sheet.PageSetup.CenterFooter = "&B Copyright © 2016 e-iceblue. All Rights Reserved.";
wb.SaveToFile(@"..\Sample.xls");
System.Diagnostics.Process.Start(@"..\Sample.xls");
}
}
}
Imports System.Drawing
Imports System.Windows
Namespace WpfApplication1
Public Partial Class MainWindow
Inherits Window
Public Sub New()
InitializeComponent()
End Sub
Private Sub button1_Click(sender As Object, e As RoutedEventArgs)
Dim wb As New Workbook()
Dim sheet As Worksheet = wb.Worksheets(0)
sheet.PageSetup.LeftHeader = "&""Showcard Gothic""&14&K8B2252 Header and Footer Sample"
sheet.PageSetup.CenterFooter = "&B Copyright © 2016 e-iceblue. All Rights Reserved."
wb.SaveToFile("..\Sample.xls")
System.Diagnostics.Process.Start("..\Sample.xls")
End Sub
End Class
End Namespace
