How to set and format data labels for Excel charts in C#
There are articles in our tutorials that introduce how to add trendline, error bars and data tables to Excel charts in C# using Spire.XLS. It's worthy of mention that Spire.XLS also supports data labels which are widely used to quickly identify a data series in a chart. In label options, we could set whether label contains series name, category name, value, percentages (pie chart) and legend key. This article is going to introduce the method to set and format data labels for Excel charts in C# using Spire.XLS.
Note: before start, please download the latest version of Spire.XLS and add the .dll in the bin folder as the reference of Visual Studio.
Step 1: Create an Excel document and add sample data.
Workbook workbook = new Workbook();
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Demo";
sheet.Range["A1"].Value = "Month";
sheet.Range["A2"].Value = "Jan";
sheet.Range["A3"].Value = "Feb";
sheet.Range["A4"].Value = "Mar";
sheet.Range["A5"].Value = "Apr";
sheet.Range["A6"].Value = "May";
sheet.Range["A7"].Value = "Jun";
sheet.Range["B1"].Value = "Peter";
sheet.Range["B2"].NumberValue = 25;
sheet.Range["B3"].NumberValue = 18;
sheet.Range["B4"].NumberValue = 8;
sheet.Range["B5"].NumberValue = 13;
sheet.Range["B6"].NumberValue = 22;
sheet.Range["B7"].NumberValue = 28;
Step 2: Create a line markers chart based on the sample data.
Chart chart = sheet.Charts.Add(ExcelChartType.LineMarkers);
chart.DataRange = sheet.Range["B1:B7"];
chart.PlotArea.Visible = false;
chart.SeriesDataFromRange = false;
chart.TopRow = 5;
chart.BottomRow = 26;
chart.LeftColumn = 2;
chart.RightColumn =11;
chart.ChartTitle = "Data Labels Demo";
chart.ChartTitleArea.IsBold = true;
chart.ChartTitleArea.Size = 12;
Spire.Xls.Charts.ChartSerie cs1 = chart.Series[0];
cs1.CategoryLabels = sheet.Range["A2:A7"];
Step 3: Set which parts are displayed in the data labels and the delimiter to separate them.
cs1.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
cs1.DataPoints.DefaultDataPoint.DataLabels.HasLegendKey = false;
cs1.DataPoints.DefaultDataPoint.DataLabels.HasPercentage = false;
cs1.DataPoints.DefaultDataPoint.DataLabels.HasSeriesName = true;
cs1.DataPoints.DefaultDataPoint.DataLabels.HasCategoryName = true;
cs1.DataPoints.DefaultDataPoint.DataLabels.Delimiter = ". ";
Step 4: Set the font, position and fill effects for data labels in the chart.
cs1.DataPoints.DefaultDataPoint.DataLabels.Size = 9;
cs1.DataPoints.DefaultDataPoint.DataLabels.Color = Color.Red;
cs1.DataPoints.DefaultDataPoint.DataLabels.FontName = "Calibri";
cs1.DataPoints.DefaultDataPoint.DataLabels.Position = DataLabelPositionType.Center;
cs1.DataPoints.DefaultDataPoint.DataLabels.FrameFormat.Fill.Texture = GradientTextureType.Papyrus;
Step 5: Save the document as Excel 2010.
workbook.SaveToFile("S3.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("S3.xlsx");
Effects:
Full Codes:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Spire.Xls;
using System.Drawing;
namespace ConsoleApplication2
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Demo";
sheet.Range["A1"].Value = "Month";
sheet.Range["A2"].Value = "Jan";
sheet.Range["A3"].Value = "Feb";
sheet.Range["A4"].Value = "Mar";
sheet.Range["A5"].Value = "Apr";
sheet.Range["A6"].Value = "May";
sheet.Range["A7"].Value = "Jun";
sheet.Range["B1"].Value = "Peter";
sheet.Range["B2"].NumberValue = 25;
sheet.Range["B3"].NumberValue = 18;
sheet.Range["B4"].NumberValue = 8;
sheet.Range["B5"].NumberValue = 13;
sheet.Range["B6"].NumberValue = 22;
sheet.Range["B7"].NumberValue = 28;
Chart chart = sheet.Charts.Add(ExcelChartType.LineMarkers);
chart.DataRange = sheet.Range["B1:B7"];
chart.PlotArea.Visible = false;
chart.SeriesDataFromRange = false;
chart.TopRow = 5;
chart.BottomRow = 26;
chart.LeftColumn = 2;
chart.RightColumn =11;
chart.ChartTitle = "Data Labels Demo";
chart.ChartTitleArea.IsBold = true;
chart.ChartTitleArea.Size = 12;
Spire.Xls.Charts.ChartSerie cs1 = chart.Series[0];
cs1.CategoryLabels = sheet.Range["A2:A7"];
cs1.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
cs1.DataPoints.DefaultDataPoint.DataLabels.HasLegendKey = false;
cs1.DataPoints.DefaultDataPoint.DataLabels.HasPercentage = false;
cs1.DataPoints.DefaultDataPoint.DataLabels.HasSeriesName = true;
cs1.DataPoints.DefaultDataPoint.DataLabels.HasCategoryName = true;
cs1.DataPoints.DefaultDataPoint.DataLabels.Delimiter = ". ";
cs1.DataPoints.DefaultDataPoint.DataLabels.Size = 9;
cs1.DataPoints.DefaultDataPoint.DataLabels.Color = Color.Red;
cs1.DataPoints.DefaultDataPoint.DataLabels.FontName = "Calibri";
cs1.DataPoints.DefaultDataPoint.DataLabels.Position = DataLabelPositionType.Center;
cs1.DataPoints.DefaultDataPoint.DataLabels.FrameFormat.Fill.Texture = GradientTextureType.Papyrus;
workbook.SaveToFile("S3.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("S3.xlsx");
}
}
}
How to set the background color for Excel Chart in C#
As a powerful Excel library, Spire.XLS supports to work with many kinds of charts and it also supports to set the performance for the chart. We have already shown you how to fill the excel chart with background image to make the chart more attractive. This article will show you how to fill the excel chart with background color in C#.
Please check the original Excel chart without any background color:
Code Snippet for Inserting Background color:
Step 1: Create a new workbook and load from file.
Workbook workbook = new Workbook();
workbook.LoadFromFile("sample.xlsx");
Step 2: Get the first worksheet from workbook and then get the first chart from the worksheet.
Worksheet ws = workbook.Worksheets[0]; Chart chart = ws.Charts[0];
Step 3: Set the property to ForeGroundColor for PlotArea to fill the background color for the chart.
chart.PlotArea.ForeGroundColor = System.Drawing.Color.LightYellow;
Step 4: Save the document to file.
workbook.SaveToFile("result.xlsx",ExcelVersion.Version2010);
Effective screenshot after fill the background color for Excel chart:
Full codes:
using Spire.Xls;
namespace SetBackgroundColor
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile("sample.xlsx");
Worksheet ws = workbook.Worksheets[0];
Chart chart = ws.Charts[0];
chart.PlotArea.ForeGroundColor = System.Drawing.Color.LightYellow;
workbook.SaveToFile("result.xlsx", ExcelVersion.Version2010);
}
}
}
How to Download a Word Document from URL in C#, VB.NET
Nowadays, we're facing a bigger chance to download a file from URL since more documents are electronically delivered by internet. In this article, I'll introduce how to download a Word document from URL programmatically using Spire.Doc in C#, VB.NET.
Spire.Doc does not provide a method to download a Word file directly from URL. However, you can download the file from URL into a MemoryStream and then use Spire.Doc to load the document from MemoryStream and save as a new Word document to local folder.
Code Snippet:
Step 1: Initialize a new Word document.
Document doc = new Document();
Step 2: Initialize a new instance of WebClient class.
WebClient webClient = new WebClient();
Step 3: Call WebClient.DownloadData(string address) method to load the data from URL. Save the data to a MemoryStream, then call Document.LoadFromStream() method to load the Word document from MemoryStream.
using (MemoryStream ms = new MemoryStream(webClient.DownloadData("http://www.e-iceblue.com/images/test.docx")))
{
doc.LoadFromStream(ms,FileFormat.Docx);
}
Step 4: Save the file.
doc.SaveToFile("result.docx",FileFormat.Docx);
Run the program, the targeted file will be downloaded and saved as a new Word file in Bin folder.
Full Code:
using Spire.Doc;
using System.IO;
using System.Net;
namespace DownloadfromURL
{
class Program
{
static void Main(string[] args)
{
Document doc = new Document();
WebClient webClient = new WebClient();
using (MemoryStream ms = new MemoryStream(webClient.DownloadData("http://www.e-iceblue.com/images/test.docx")))
{
doc.LoadFromStream(ms, FileFormat.Docx);
}
doc.SaveToFile("result.docx", FileFormat.Docx);
}
}
}
Imports Spire.Doc
Imports System.IO
Imports System.Net
Namespace DownloadfromURL
Class Program
Private Shared Sub Main(args As String())
Dim doc As New Document()
Dim webClient As New WebClient()
Using ms As New MemoryStream(webClient.DownloadData("http://www.e-iceblue.com/images/test.docx"))
doc.LoadFromStream(ms, FileFormat.Docx)
End Using
doc.SaveToFile("result.docx", FileFormat.Docx)
End Sub
End Class
End Namespace
How to add a data table to the chart in C#
In Excel, we could use charts to visualize and compare data. However, once the charts are created, it becomes much difficult for us to read the data precisely from charts. Adding a data table below the chart is a good solution for which the chart and data are on the same place. This article is going to introduce the method to add a data table to the chart that is based on the data in C# using Spire.XLS.
Note: before start, please download the latest version of Spire.XLS and add the .dll in the bin folder as the reference of Visual studio.
Step 1: Create a new workbook and add an empty sheet.
Workbook workbook = new Workbook();
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
Step 2: Fill cells with sample data.
sheet.Name = "Demo";
sheet.Range["A1"].Value = "Month";
sheet.Range["A2"].Value = "Jan.";
sheet.Range["A3"].Value = "Feb.";
sheet.Range["A4"].Value = "Mar.";
sheet.Range["A5"].Value = "Apr.";
sheet.Range["A6"].Value = "May.";
sheet.Range["A7"].Value = "Jun.";
sheet.Range["B1"].Value = "Peter";
sheet.Range["B2"].NumberValue = 3.3;
sheet.Range["B3"].NumberValue = 2.5;
sheet.Range["B4"].NumberValue = 2.0;
sheet.Range["B5"].NumberValue = 3.7;
sheet.Range["B6"].NumberValue = 4.5;
sheet.Range["B7"].NumberValue = 4.0;
sheet.Range["C1"].Value = "George";
sheet.Range["C2"].NumberValue = 3.8;
sheet.Range["C3"].NumberValue = 3.2;
sheet.Range["C4"].NumberValue = 1.7;
sheet.Range["C5"].NumberValue = 3.5;
sheet.Range["C6"].NumberValue = 4.5;
sheet.Range["C7"].NumberValue = 4.3;
sheet.Range["D1"].Value = "Macbeth";
sheet.Range["D2"].NumberValue = 3.0;
sheet.Range["D3"].NumberValue = 2.8;
sheet.Range["D4"].NumberValue = 3.5;
sheet.Range["D5"].NumberValue = 2.3;
sheet.Range["D6"].NumberValue = 3.3;
sheet.Range["D7"].NumberValue = 3.8;
Step 3: Create a Column3DClustered based on the sample data.
Chart chart = sheet.Charts.Add(ExcelChartType.Column3DClustered);
chart.DataRange = sheet.Range["B1:D7"];
chart.SeriesDataFromRange = false;
chart.TopRow = 7;
chart.BottomRow = 28;
chart.LeftColumn = 3;
chart.RightColumn =11;
chart.ChartTitle = "Chart with Data Table";
chart.ChartTitleArea.IsBold = true;
chart.ChartTitleArea.Size = 12;
Spire.Xls.Charts.ChartSerie cs1 = chart.Series[0];
cs1.CategoryLabels = sheet.Range["A2:A7"];
Step 4: Add a data table to the chart that is based on the data.
chart.HasDataTable = true;
Step 5: Save the document and launch to see effects.
workbook.SaveToFile("S3.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("S3.xlsx");
Effects:
Full codes:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Spire.Xls;
using System.Drawing;
namespace ConsoleApplication2
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Demo";
sheet.Range["A1"].Value = "Month";
sheet.Range["A2"].Value = "Jan.";
sheet.Range["A3"].Value = "Feb.";
sheet.Range["A4"].Value = "Mar.";
sheet.Range["A5"].Value = "Apr.";
sheet.Range["A6"].Value = "May.";
sheet.Range["A7"].Value = "Jun.";
sheet.Range["B1"].Value = "Peter";
sheet.Range["B2"].NumberValue = 3.3;
sheet.Range["B3"].NumberValue = 2.5;
sheet.Range["B4"].NumberValue = 2.0;
sheet.Range["B5"].NumberValue = 3.7;
sheet.Range["B6"].NumberValue = 4.5;
sheet.Range["B7"].NumberValue = 4.0;
sheet.Range["C1"].Value = "George";
sheet.Range["C2"].NumberValue = 3.8;
sheet.Range["C3"].NumberValue = 3.2;
sheet.Range["C4"].NumberValue = 1.7;
sheet.Range["C5"].NumberValue = 3.5;
sheet.Range["C6"].NumberValue = 4.5;
sheet.Range["C7"].NumberValue = 4.3;
sheet.Range["D1"].Value = "Macbeth";
sheet.Range["D2"].NumberValue = 3.0;
sheet.Range["D3"].NumberValue = 2.8;
sheet.Range["D4"].NumberValue = 3.5;
sheet.Range["D5"].NumberValue = 2.3;
sheet.Range["D6"].NumberValue = 3.3;
sheet.Range["D7"].NumberValue = 3.8;
Chart chart = sheet.Charts.Add(ExcelChartType.Column3DClustered);
chart.DataRange = sheet.Range["B1:D7"];
chart.SeriesDataFromRange = false;
chart.TopRow = 7;
chart.BottomRow = 28;
chart.LeftColumn = 3;
chart.RightColumn =11;
chart.ChartTitle = "Chart with Data Table";
chart.ChartTitleArea.IsBold = true;
chart.ChartTitleArea.Size = 12;
Spire.Xls.Charts.ChartSerie cs1 = chart.Series[0];
cs1.CategoryLabels = sheet.Range["A2:A7"];
chart.HasDataTable = true;
workbook.SaveToFile("S3.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("S3.xlsx");
}
}
}
How to add error bars to a chart in C#
Error bars are a graphical representation of the variability of data and helps us see margins of error and standard deviations immediately in charts with a standard error amount, a percentage, a standard deviation or a custom error amount. Error bars can be used in 2-D area, bar, column, line, scatter, and bubble charts, which are all supported by Spire.XLS. This article is going to introduce the method to add error bars to a chart in C# using Spire.XLS.
Note: before start, please download the latest version of Spire.XLS and add the .dll in the bin folder as the reference of Visual Studio.
Step 1: Create a workbook and fill the sample data in sheet.
Workbook workbook = new Workbook();
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Demo";
sheet.Range["A1"].Value = "Month";
sheet.Range["A2"].Value = "Jan.";
sheet.Range["A3"].Value = "Feb.";
sheet.Range["A4"].Value = "Mar.";
sheet.Range["A5"].Value = "Apr.";
sheet.Range["A6"].Value = "May.";
sheet.Range["A7"].Value = "Jun.";
sheet.Range["B1"].Value = "Planned";
sheet.Range["B2"].NumberValue = 3.3;
sheet.Range["B3"].NumberValue = 2.5;
sheet.Range["B4"].NumberValue = 2.0;
sheet.Range["B5"].NumberValue = 3.7;
sheet.Range["B6"].NumberValue = 4.5;
sheet.Range["B7"].NumberValue = 4.0;
sheet.Range["C1"].Value = "Actual";
sheet.Range["C2"].NumberValue = 3.8;
sheet.Range["C3"].NumberValue = 3.2;
sheet.Range["C4"].NumberValue = 1.7;
sheet.Range["C5"].NumberValue = 3.5;
sheet.Range["C6"].NumberValue = 4.5;
sheet.Range["C7"].NumberValue = 4.3;
Step 2: Add a line chart and then add percentage error bar to the chart. The direction of error bars can be set as both, minus and plus and the type of error bars can be set as fixed value, percentage, standard deviation, standard error or custom. After setting the direction and type, we can set the amount.
Chart chart = sheet.Charts.Add(ExcelChartType.Line);
chart.DataRange = sheet.Range["B1:B7"];
chart.SeriesDataFromRange = false;
chart.TopRow = 6;
chart.BottomRow = 25;
chart.LeftColumn = 2;
chart.RightColumn = 9;
chart.ChartTitle = "Error Bar 10% Plus";
chart.ChartTitleArea.IsBold = true;
chart.ChartTitleArea.Size = 12;
Spire.Xls.Charts.ChartSerie cs1 = chart.Series[0];
cs1.CategoryLabels = sheet.Range["A2:A7"];
cs1.ErrorBar(true, ErrorBarIncludeType.Plus, ErrorBarType.Percentage,10);
Step 3: Add a column chart with standard error bars as comparison.
Chart chart2 = sheet.Charts.Add(ExcelChartType.ColumnClustered);
chart2.DataRange = sheet.Range["B1:C7"];
chart2.SeriesDataFromRange = false;
chart2.TopRow = 6;
chart2.BottomRow = 25;
chart2.LeftColumn = 10;
chart2.RightColumn = 17;
chart2.ChartTitle = "Standard Error Bar";
chart2.ChartTitleArea.IsBold = true;
chart2.ChartTitleArea.Size = 12;
Spire.Xls.Charts.ChartSerie cs2 = chart2.Series[0];
cs2.CategoryLabels = sheet.Range["A2:A7"];
cs2.ErrorBar(true, ErrorBarIncludeType.Minus, ErrorBarType.StandardError, 0.3);
Spire.Xls.Charts.ChartSerie cs3 = chart2.Series[1];
cs3.ErrorBar(true, ErrorBarIncludeType.Both, ErrorBarType.StandardError, 0.5);
Step 4: Save the document and launch to see effects.
workbook.SaveToFile("S3.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("S3.xlsx");
Effects:
Full Codes:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Spire.Xls;
using System.Drawing;
namespace ConsoleApplication2
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Demo";
sheet.Range["A1"].Value = "Month";
sheet.Range["A2"].Value = "Jan.";
sheet.Range["A3"].Value = "Feb.";
sheet.Range["A4"].Value = "Mar.";
sheet.Range["A5"].Value = "Apr.";
sheet.Range["A6"].Value = "May.";
sheet.Range["A7"].Value = "Jun.";
sheet.Range["B1"].Value = "Planned";
sheet.Range["B2"].NumberValue = 3.3;
sheet.Range["B3"].NumberValue = 2.5;
sheet.Range["B4"].NumberValue = 2.0;
sheet.Range["B5"].NumberValue = 3.7;
sheet.Range["B6"].NumberValue = 4.5;
sheet.Range["B7"].NumberValue = 4.0;
sheet.Range["C1"].Value = "Actual";
sheet.Range["C2"].NumberValue = 3.8;
sheet.Range["C3"].NumberValue = 3.2;
sheet.Range["C4"].NumberValue = 1.7;
sheet.Range["C5"].NumberValue = 3.5;
sheet.Range["C6"].NumberValue = 4.5;
sheet.Range["C7"].NumberValue = 4.3;
Chart chart = sheet.Charts.Add(ExcelChartType.Line);
chart.DataRange = sheet.Range["B1:B7"];
chart.SeriesDataFromRange = false;
chart.TopRow = 6;
chart.BottomRow = 25;
chart.LeftColumn = 2;
chart.RightColumn = 9;
chart.ChartTitle = "Error Bar 10% Plus";
chart.ChartTitleArea.IsBold = true;
chart.ChartTitleArea.Size = 12;
Spire.Xls.Charts.ChartSerie cs1 = chart.Series[0];
cs1.CategoryLabels = sheet.Range["A2:A7"];
cs1.ErrorBar(true, ErrorBarIncludeType.Plus, ErrorBarType.Percentage,10);
Chart chart2 = sheet.Charts.Add(ExcelChartType.ColumnClustered);
chart2.DataRange = sheet.Range["B1:C7"];
chart2.SeriesDataFromRange = false;
chart2.TopRow = 6;
chart2.BottomRow = 25;
chart2.LeftColumn = 10;
chart2.RightColumn = 17;
chart2.ChartTitle = "Standard Error Bar";
chart2.ChartTitleArea.IsBold = true;
chart2.ChartTitleArea.Size = 12;
Spire.Xls.Charts.ChartSerie cs2 = chart2.Series[0];
cs2.CategoryLabels = sheet.Range["A2:A7"];
cs2.ErrorBar(true, ErrorBarIncludeType.Minus, ErrorBarType.StandardError, 0.3);
Spire.Xls.Charts.ChartSerie cs3 = chart2.Series[1];
cs3.ErrorBar(true, ErrorBarIncludeType.Both, ErrorBarType.StandardError, 0.5);
workbook.SaveToFile("S3.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("S3.xlsx");
}
}
}
How to Add an Image to Slide Master in C#, VB.NET
A slide master is the top slide that stores the information about the theme and slide layouts, which will be inherited by other slides in the presentation. In other words, when you modify the style of slide master, every slide in the presentation will be changed accordingly, including the ones added later.
This quality makes it possible that when you want to insert an image or watermark to every slide, you only need to insert the image in slide master. In this article, you'll learn how to add an image to slide master using Spire.Presenation in C#, VB.NET.
Screenshot of original file:
Detailed Steps:
Step 1: Initialize a new Presentation and load the sample file
Presentation presentation = new Presentation(); presentation.LoadFromFile(@"sample.pptx");
Step 2: Get the master collection.
IMasterSlide master = presentation.Masters[0];
Step 3: Insert an image to slide master.
String image = @"logo.png"; RectangleF rff = new RectangleF(40, 40, 100, 80); IEmbedImage pic=master.Shapes.AppendEmbedImage(ShapeType.Rectangle, image, rff); pic.Line.FillFormat.FillType = FillFormatType.None;
Step 4: Add a new blank slide to the presentation.
presentation.Slides.Append();
Step 5: Save and launch the file.
presentation.SaveToFile("result.pptx", FileFormat.Pptx2010);
System.Diagnostics.Process.Start("result.pptx");
Output:
Full Code:
using Spire.Presentation;
using Spire.Presentation.Drawing;
using System;
using System.Drawing;
namespace AddImage
{
class Program
{
static void Main(string[] args)
{
//initialize a new Presentation and load the sample file
Presentation presentation = new Presentation();
presentation.LoadFromFile(@"sample.pptx");
//get the master collection
IMasterSlide master = presentation.Masters[0];
//append image to slide master
String image = @"logo.png";
RectangleF rff = new RectangleF(40, 40, 100, 80);
IEmbedImage pic = master.Shapes.AppendEmbedImage(ShapeType.Rectangle, image, rff);
pic.Line.FillFormat.FillType = FillFormatType.None;
//add new slide to presentation
presentation.Slides.Append();
//save and launch the file
presentation.SaveToFile("result.pptx", FileFormat.Pptx2010);
System.Diagnostics.Process.Start("result.pptx");
}
}
}
Imports Spire.Presentation
Imports Spire.Presentation.Drawing
Imports System.Drawing
Namespace AddImage
Class Program
Private Shared Sub Main(args As String())
'initialize a new Presentation and load the sample file
Dim presentation As New Presentation()
presentation.LoadFromFile("sample.pptx")
'get the master collection
Dim master As IMasterSlide = presentation.Masters(0)
'append image to slide master
Dim image As [String] = "logo.png"
Dim rff As New RectangleF(40, 40, 100, 80)
Dim pic As IEmbedImage = master.Shapes.AppendEmbedImage(ShapeType.Rectangle, image, rff)
pic.Line.FillFormat.FillType = FillFormatType.None
'add new slide to presentation
presentation.Slides.Append()
'save and launch the file
presentation.SaveToFile("result.pptx", FileFormat.Pptx2010)
System.Diagnostics.Process.Start("result.pptx")
End Sub
End Class
End Namespace
How to format cells with borders in conditional formatting
Using conditional formatting in Excel, we could highlight interesting cells, emphasize unusual values and visualize data with Data Bars, Color Scales and Icon Sets based on criteria. In the two articles Alternate Row Colors in Excel with Conditional Formatting and Apply Conditional Formatting to a Data Range, we have introduce the method to set fill, font, data bars, color scales and icon sets in conditional formatting using Spire.XLS. This article is going to introduce the method to format cells with borders in conditional formatting.
Note: before start, please download the latest version of Spire.XLS and add the .dll in the bin folder as the reference of Visual Studio.
Step 1: Create a new workbook and add sample data.
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
sheet.Range["A1"].Value = "Name/Subject";
sheet.Range["A2"].Value = "Tom";
sheet.Range["A3"].Value = "Sam";
sheet.Range["A4"].Value = "Tina";
sheet.Range["A5"].Value = "Nancy";
sheet.Range["A6"].Value = "James";
sheet.Range["A7"].Value = "Victor";
sheet.Range["B1"].Value = "Math";
sheet.Range["C1"].Value = "French";
sheet.Range["D1"].Value = "English";
sheet.Range["E1"].Value = "Physics";
sheet.Range["B2"].NumberValue = 56;
sheet.Range["B3"].NumberValue = 73;
sheet.Range["B4"].NumberValue = 75;
sheet.Range["B5"].NumberValue = 89;
sheet.Range["B6"].NumberValue = 65;
sheet.Range["B7"].NumberValue = 90;
sheet.Range["C2"].NumberValue = 78;
sheet.Range["C3"].NumberValue = 99;
sheet.Range["C4"].NumberValue = 86;
sheet.Range["C5"].NumberValue = 45;
sheet.Range["C6"].NumberValue = 70;
sheet.Range["C7"].NumberValue = 83;
sheet.Range["D2"].NumberValue = 79;
sheet.Range["D3"].NumberValue = 70;
sheet.Range["D4"].NumberValue = 90;
sheet.Range["D5"].NumberValue = 87;
sheet.Range["D6"].NumberValue = 56;
sheet.Range["D7"].NumberValue = 78;
sheet.Range["E2"].NumberValue = 65;
sheet.Range["E3"].NumberValue = 55;
sheet.Range["E4"].NumberValue = 100;
sheet.Range["E5"].NumberValue = 85;
sheet.Range["E6"].NumberValue = 60;
sheet.Range["E7"].NumberValue = 75;
sheet.AllocatedRange.RowHeight = 17;
sheet.AllocatedRange.ColumnWidth = 17;
sheet.AllocatedRange.VerticalAlignment = VerticalAlignType.Center;
sheet.AllocatedRange.HorizontalAlignment = HorizontalAlignType.Center;
Step 2: Set the formatting rule using formula. Here the rule is the number values less than 60.
ConditionalFormatWrapper format1 = sheet.Range["B2:E7"].ConditionalFormats.AddCondition();
format1.FirstFormula = "60";
format1.Operator = ComparisonOperatorType.Less;
Step 3: Set border colors and styles for cells that match the condition.
format1.LeftBorderColor = Color.Red;
format1.RightBorderColor = Color.DarkBlue;
format1.TopBorderColor = Color.DeepSkyBlue;
format1.BottomBorderColor = Color.DeepSkyBlue;
format1.LeftBorderStyle = LineStyleType.Medium;
format1.RightBorderStyle = LineStyleType.Thick;
format1.TopBorderStyle = LineStyleType.Double;
format1.BottomBorderStyle = LineStyleType.Double;
Step 4: Save the document and launch to see effects.
workbook.SaveToFile("sample.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("sample.xlsx");
Effects:
Full Codes:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Spire.Xls;
using System.Drawing;
namespace Test
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
sheet.Range["A1"].Value = "Name/Subject";
sheet.Range["A2"].Value = "Tom";
sheet.Range["A3"].Value = "Sam";
sheet.Range["A4"].Value = "Tina";
sheet.Range["A5"].Value = "Nancy";
sheet.Range["A6"].Value = "James";
sheet.Range["A7"].Value = "Victor";
sheet.Range["B1"].Value = "Math";
sheet.Range["C1"].Value = "French";
sheet.Range["D1"].Value = "English";
sheet.Range["E1"].Value = "Physics";
sheet.Range["B2"].NumberValue = 56;
sheet.Range["B3"].NumberValue = 73;
sheet.Range["B4"].NumberValue = 75;
sheet.Range["B5"].NumberValue = 89;
sheet.Range["B6"].NumberValue = 65;
sheet.Range["B7"].NumberValue = 90;
sheet.Range["C2"].NumberValue = 78;
sheet.Range["C3"].NumberValue = 99;
sheet.Range["C4"].NumberValue = 86;
sheet.Range["C5"].NumberValue = 45;
sheet.Range["C6"].NumberValue = 70;
sheet.Range["C7"].NumberValue = 83;
sheet.Range["D2"].NumberValue = 79;
sheet.Range["D3"].NumberValue = 70;
sheet.Range["D4"].NumberValue = 90;
sheet.Range["D5"].NumberValue = 87;
sheet.Range["D6"].NumberValue = 56;
sheet.Range["D7"].NumberValue = 78;
sheet.Range["E2"].NumberValue = 65;
sheet.Range["E3"].NumberValue = 55;
sheet.Range["E4"].NumberValue = 100;
sheet.Range["E5"].NumberValue = 85;
sheet.Range["E6"].NumberValue = 60;
sheet.Range["E7"].NumberValue = 75;
sheet.AllocatedRange.RowHeight = 17;
sheet.AllocatedRange.ColumnWidth = 17;
sheet.AllocatedRange.VerticalAlignment = VerticalAlignType.Center;
sheet.AllocatedRange.HorizontalAlignment = HorizontalAlignType.Center;
ConditionalFormatWrapper format1 = sheet.Range["B2:E7"].ConditionalFormats.AddCondition();
format1.FirstFormula = "60";
format1.Operator = ComparisonOperatorType.Less;
format1.LeftBorderColor = Color.Red;
format1.RightBorderColor = Color.DarkBlue;
format1.TopBorderColor = Color.DeepSkyBlue;
format1.BottomBorderColor = Color.DeepSkyBlue;
format1.LeftBorderStyle = LineStyleType.Medium;
format1.RightBorderStyle = LineStyleType.Thick;
format1.TopBorderStyle = LineStyleType.Double;
format1.BottomBorderStyle = LineStyleType.Double;
workbook.SaveToFile("sample.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("sample.xlsx");
}
}
}
C#: Add a Watermark to an Excel Document
Adding a watermark to an Excel spreadsheet can be a useful way to brand your documents or indicate confidentiality. MS Excel does not provide a built-in feature to insert a watermark, however, there are ways to mimic the watermark effect by inserting an image into the header or footer of your Excel worksheet, or setting an image as the background of your spreadsheet.
In this article, you will learn how to add a header or background image watermark to Excel in C# 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
Header vs. Background Image Watermark
Header Image Watermark
Advantages:
- The watermark is preserved on the printed sheet, ensuring it appears in the final output.
Disadvantages:
- The watermark is invisible under the "Normal" view mode in Excel, only becoming visible in "Page Layout" or "Page Break Preview" views.
- To centrally position the watermark graphic on the Excel page, you need to carefully adjust the white margins, especially on the top and left sides of the image.
Background Image Watermark
Advantages:
- The watermark image covers the entire worksheet area, providing a consistent background appearance.
Disadvantages:
- The watermark is not preserved on the printed sheet, meaning it will not appear in the final printed output.
Add a Watermark to Excel Using a Header Image in C#
Spire.XLS for .NET provides the PageSetup class, which allows you to control various settings related to the appearance and layout of the printed worksheet. This class includes the CenterHeader and CenterHeaderImage properties, which enable you to set an image for the center section of the header.
Below are the steps to add a watermark to Excel using a header image in C#.
- Create a Workbook object.
- Load an Excel document from a give file path.
- Load an image using Image.FromFile() method.
- Get a specific worksheet from the workbook.
- Add an image field to the header center by setting Worksheet.PageSetup.CenterHeader property to "&G".
- Apply the image to the header center through Worksheet.PageSetup.CenterHeaderImage property.
- Save the workbook to a different Excel file.
- C#
using Spire.Xls;
using System.Drawing;
namespace AddWatermarkToExcelUsingHeader
{
class Program
{
static void Main(string[] args)
{
// Create a Workbook object
Workbook workbook = new Workbook();
// Load an Excel document
workbook.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");
// Load an image file
Image image = Image.FromFile("C:\\Users\\Administrator\\Desktop\\confidential.png");
// Loop through all worksheets in the file
for (int i = 0; i < workbook.Worksheets.Count; i++)
{
// Get a specific worksheet
Worksheet worksheet = workbook.Worksheets[i];
// Add an image field to the header center
worksheet.PageSetup.CenterHeader = "&G";
// Add the image to the header center
worksheet.PageSetup.CenterHeaderImage = image;
}
// Save the result file
workbook.SaveToFile("AddWatermark.xlsx", ExcelVersion.Version2016);
// Dispose resources
workbook.Dispose();
}
}
}
Add a Watermark to Excel Using a Background Image in C#
The PageSetup class also includes a property named BackgroundImage, which allows you to get or set the image used for the worksheet background.
Here are the steps to add a watermark to Excel using a background image in C#.
- Create a Workbook object.
- Load an Excel document from a give file path.
- Load an image file and convert it into a Bitmap image.
- Get a specific worksheet from the workbook.
- Apply the image to the worksheet as the background through Worksheet.PageSetup.BackgroundImage property.
- Save the workbook to a different Excel file.
- C#
using Spire.Xls;
using System.Drawing;
namespace AddWatermarkToExcelUsingBackground
{
class Program
{
static void Main(string[] args)
{
// Create a Workbook object
Workbook workbook = new Workbook();
// Load an Excel document
workbook.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");
// Load an image file
Bitmap bitmapImage = new Bitmap(Image.FromFile("C:\\Users\\Administrator\\Desktop\\sample.png"));
// Loop through all worksheets in the file
for (int i = 0; i < workbook.Worksheets.Count; i++)
{
// Get a specific worksheet
Worksheet worksheet = workbook.Worksheets[i];
// Set the image as the background of the worksheet
worksheet.PageSetup.BackgoundImage = bitmapImage;
}
// Save the result file
workbook.SaveToFile("AddWatermark.xlsx", ExcelVersion.Version2016);
// Dispose resources
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 color for different levels bookmark in word documents
We have already demonstrated how to insert single bookmark to word document by using Spire.Doc. When you need to insert many bookmarks to long word document, you can also use Spire.Doc to add multiple levels bookmarks and set different colors for them. Spire.Doc Version 5.5.71 adds a new method of BookmarkLayout to enable developers to set the different color for the different levels of bookmarks. This article will show you how to set the different color for the different levels of bookmarks.
Here comes to the code snippet:
Step 1: Create a new word document and load a file with nested level bookmarks.
Document document = new Document();
document.LoadFromFile("sample.docx");
Step 2: Save the word document into PDF to view the effects clearly and add the event of BookmarkLayout before saving to PDF.
ToPdfParameterList toPdf = new ToPdfParameterList();
toPdf.CreateWordBookmarks = true;
toPdf.WordBookmarksTitle = "Changed bookmark";
toPdf.WordBookmarksColor = Color.Gray;
//the event of BookmarkLayout occurs when draw a bookmark
document.BookmarkLayout += new Spire.Doc.Documents.Rendering.BookmarkLevelHandler(document_BookmarkLayout);
document.SaveToFile("result.pdf", toPdf);
Step 3: Call the method of BookmarkLayout to set the different color for the different levels of bookmarks.
static void document_BookmarkLayout(object sender, Spire.Doc.Documents.Rendering.BookmarkLevelEventArgs args)
{
//set the different color for different levels of bookmarks
if (args.BookmarkLevel.Level == 2)
{
args.BookmarkLevel.Color = Color.Red;
args.BookmarkLevel.Style = BookmarkTextStyle.Bold;
}
else if (args.BookmarkLevel.Level == 3)
{
args.BookmarkLevel.Color = Color.Gray;
args.BookmarkLevel.Style = BookmarkTextStyle.Italic;
}
else
{
args.BookmarkLevel.Color = Color.Green;
args.BookmarkLevel.Style = BookmarkTextStyle.Regular;
}
Please check the effective screenshot of multiple levels bookmarks with different colors:
Full codes:
using Spire.Doc;
using System.Drawing;
namespace SetColor
{
class Program
{
static void Main(string[] args)
{
Document document = new Document();
document.LoadFromFile("sample.docx");
ToPdfParameterList toPdf = new ToPdfParameterList();
toPdf.CreateWordBookmarks = true;
toPdf.WordBookmarksTitle = "Changed bookmark";
toPdf.WordBookmarksColor = Color.Gray;
//the event of BookmarkLayout occurs when draw a bookmark
document.BookmarkLayout += new Spire.Doc.Documents.Rendering.BookmarkLevelHandler(document_BookmarkLayout);
document.SaveToFile("result.pdf", toPdf);
}
static void document_BookmarkLayout(object sender, Spire.Doc.Documents.Rendering.BookmarkLevelEventArgs args)
{
if (args.BookmarkLevel.Level == 2)
{
args.BookmarkLevel.Color = Color.Red;
args.BookmarkLevel.Style = BookmarkTextStyle.Bold;
}
else if (args.BookmarkLevel.Level == 3)
{
args.BookmarkLevel.Color = Color.Gray;
args.BookmarkLevel.Style = BookmarkTextStyle.Italic;
}
else
{
args.BookmarkLevel.Color = Color.Green;
args.BookmarkLevel.Style = BookmarkTextStyle.Regular;
}
}
}
}
How to set 3-D format for shapes in slides
3-D is the abbreviation for three-dimensional. After adding a shape into the slide, we could set its format as 3-D, which looks more fresh and attractive. We could use options like Bevel, Contours, and Surface Material to customize 3-D shapes. This article is going to introduce the method to set 3-D shapes in C# using Spire.Presentation.
Note: before start, please download the latest version of Spire.Presentation and add the .dll in the bin folder as the reference of Visual Studio.
Step 1: Create a new presentation document.
Presentation presentation = new Presentation();
Step 2: Add shape1 and fill it with color.
IAutoShape shape1 = presentation.Slides[0].Shapes.AppendShape(ShapeType.RoundCornerRectangle, new RectangleF(150, 150, 150, 150)); shape1.Fill.FillType = FillFormatType.Solid; shape1.Fill.SolidColor.KnownColor = KnownColors.RoyalBlue;
Step 3: Initialize a new instance of the 3-D class for shape1 and set its properties.
ShapeThreeD Demo1 = shape1.ThreeD.ShapeThreeD;
Demo1.PresetMaterial = PresetMaterialType.Powder;
Demo1.TopBevel.PresetType = BevelPresetType.ArtDeco;
Demo1.TopBevel.Height = 4;
Demo1.TopBevel.Width = 12;
Demo1.BevelColorMode = BevelColorType.Contour;
Demo1.ContourColor.KnownColor = KnownColors.LightBlue;
Demo1.ContourWidth = 3.5;
Step 4: Set 3-D format for shape2 as comparison.
IAutoShape shape2 = presentation.Slides[0].Shapes.AppendShape(ShapeType.Pentagon, new RectangleF(400, 150, 150, 150));
shape2.Fill.FillType = FillFormatType.Solid;
shape2.Fill.SolidColor.KnownColor = KnownColors.LawnGreen;
ShapeThreeD Demo2 = shape2.ThreeD.ShapeThreeD;
Demo2.PresetMaterial = PresetMaterialType.SoftEdge;
Demo2.TopBevel.PresetType = BevelPresetType.SoftRound;
Demo2.TopBevel.Height = 12;
Demo2.TopBevel.Width = 12;
Demo2.BevelColorMode = BevelColorType.Contour;
Demo2.ContourColor.KnownColor = KnownColors.LawnGreen;
Demo2.ContourWidth = 5;
Step 5: Save the document and launch to see effects.
presentation.SaveToFile("result.pptx", FileFormat.Pptx2010);
System.Diagnostics.Process.Start("result.pptx");
Effects:
Full Codes:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Spire.Presentation;
using Spire.Presentation.Drawing;
using System.Drawing;
namespace test
{
class Program
{
static void Main(string[] args)
{
Presentation presentation = new Presentation();
IAutoShape shape1 = presentation.Slides[0].Shapes.AppendShape(ShapeType.RoundCornerRectangle, new RectangleF(150, 150, 150, 150));
shape1.Fill.FillType = FillFormatType.Solid;
shape1.Fill.SolidColor.KnownColor = KnownColors.RoyalBlue;
ShapeThreeD Demo1 = shape1.ThreeD.ShapeThreeD;
Demo1.PresetMaterial = PresetMaterialType.Powder;
Demo1.TopBevel.PresetType = BevelPresetType.ArtDeco;
Demo1.TopBevel.Height = 4;
Demo1.TopBevel.Width = 12;
Demo1.BevelColorMode = BevelColorType.Contour;
Demo1.ContourColor.KnownColor = KnownColors.LightBlue;
Demo1.ContourWidth = 3.5;
IAutoShape shape2 = presentation.Slides[0].Shapes.AppendShape(ShapeType.Pentagon, new RectangleF(400, 150, 150, 150));
shape2.Fill.FillType = FillFormatType.Solid;
shape2.Fill.SolidColor.KnownColor = KnownColors.LawnGreen;
ShapeThreeD Demo2 = shape2.ThreeD.ShapeThreeD;
Demo2.PresetMaterial = PresetMaterialType.SoftEdge;
Demo2.TopBevel.PresetType = BevelPresetType.SoftRound;
Demo2.TopBevel.Height = 12;
Demo2.TopBevel.Width = 12;
Demo2.BevelColorMode = BevelColorType.Contour;
Demo2.ContourColor.KnownColor = KnownColors.LawnGreen;
Demo2.ContourWidth = 5;
presentation.SaveToFile("result.pptx", FileFormat.Pptx2010);
System.Diagnostics.Process.Start("result.pptx");
}
}
}