Knowledgebase (2311)
Children categories
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");
}
}
}
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");
}
}
}
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
