Chart (45)
Waterfall charts in Excel are graphs that visually show how a series of consecutive positive or negative values contribute to the final outcome. They are a useful tool for tracking company profits or cash flow, comparing product revenues, analyzing sales and inventory changes over time, etc. In this article, you will learn how to create a waterfall chart in Excel in C# and VB.NET 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
Create a Waterfall Chart in Excel in C# and VB.NET
Waterfall/bridge charts are ideal for analyzing financial statements. To add a waterfall chart to an Excel worksheet, Spire.XLS for .NET provides the Worksheet.Charts.Add(ExcelChartType.WaterFall) method. The following are the detailed steps.
- Create a Workbook instance.
- Load a sample Excel document using Workbook.LoadFromFile() method.
- Get a specified worksheet by its index using Workbook.Worksheets[sheetIndex] property.
- Add a waterfall chart to the worksheet using Worksheet.Charts.Add(ExcelChartType.WaterFall) method.
- Set data range for the chart using Chart.DataRange property.
- Set position and title of the chart.
- Get a specified data series of the chart and then set specific data points in the chart as totals or subtotals using ChartSerie.DataPoints[int index].SetAsTotal property.
- Show the connector lines between data points by setting the ChartSerie.Format.ShowConnectorLines property to true.
- Show data labels for data points, and set the legend position of the chart.
- Save the result document using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
namespace WaterfallChart
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load a sample Excel document
workbook.LoadFromFile("Data.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Add a waterfall chart to the the worksheet
Chart chart = sheet.Charts.Add(ExcelChartType.WaterFall);
//Set data range for the chart
chart.DataRange = sheet["A2:B11"];
//Set position of the chart
chart.LeftColumn = 4;
chart.TopRow = 2;
chart.RightColumn = 15;
chart.BottomRow = 23;
//Set the chart title
chart.ChartTitle = "Income Statement";
//Set specific data points in the chart as totals or subtotals
chart.Series[0].DataPoints[2].SetAsTotal = true;
chart.Series[0].DataPoints[7].SetAsTotal = true;
chart.Series[0].DataPoints[9].SetAsTotal = true;
//Show the connector lines between data points
chart.Series[0].Format.ShowConnectorLines = true;
//Show data labels for data points
chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.Size = 8;
//Set the legend position of the chart
chart.Legend.Position = LegendPositionType.Top;
//Save the result document
workbook.SaveToFile("WaterfallChart.xlsx");
}
}
}
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.
This article will show you how to use Spire.XLS for .NET to create a bubble chart in Excel in C# and VB.NET.
using Spire.Xls;
namespace BubbleChart
{
class Program
{
static void Main(string[] args)
{
//Create a new workbook
Workbook workbook = new Workbook();
//Add a worksheet and set name
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Chart data";
//Initialize chart and set its type
Chart chart = sheet.Charts.Add(ExcelChartType.Bubble);
//Set the position of the chart in the worksheet
chart.LeftColumn = 1;
chart.RightColumn = 10;
chart.TopRow = 1;
chart.BottomRow = 20;
//Set title for the chart and values
Spire.Xls.Charts.ChartSerie cs1 = chart.Series.Add("Bubble Chart");
cs1.EnteredDirectlyValues = new object[] { 2.2, 5.6 };
cs1.EnteredDirectlyCategoryLabels = new object[] { 1.1, 4.4 };
cs1.EnteredDirectlyBubbles = new object[] { 3, 6 };
//Save the document to file
workbook.SaveToFile("Output.xlsx", ExcelVersion.Version2010);
}
}
}
Imports Spire.Xls
Namespace BubbleChart
Class Program
Private Shared Sub Main(ByVal args() As String)
'Create a new workbook
Dim workbook As Workbook = New Workbook
'Add a worksheet and set name
workbook.CreateEmptySheets(1)
Dim sheet As Worksheet = workbook.Worksheets(0)
sheet.Name = "Chart data"
'Initialize chart and set its type
Dim chart As Chart = sheet.Charts.Add(ExcelChartType.Bubble)
'Set the position of the chart in the worksheet
chart.LeftColumn = 1
chart.RightColumn = 10
chart.TopRow = 1
chart.BottomRow = 20
'Set title for the chart and values
Dim cs1 As Spire.Xls.Charts.ChartSerie = chart.Series.Add("Bubble Chart")
cs1.EnteredDirectlyValues = New Object() {2.2, 5.6}
cs1.EnteredDirectlyCategoryLabels = New Object() {1.1, 4.4}
cs1.EnteredDirectlyBubbles = New Object() {3, 6}
'Save the document to file
workbook.SaveToFile("Output.xlsx", ExcelVersion.Version2010)
End Sub
End Class
End Namespace
Effective screenshot of Excel Bubble chart:
Starting from version 9.8.5, Spire.XLS supports creating pivot chart based on pivot table. This article is going to demonstrate how we can use Spire.XLS to implement this feature.
The input.xlsx Excel file:
Sample Code
using Spire.Xls;
using Spire.Xls.Core;
namespace CreatePivotChart
{
class Program
{
static void Main(string[] args)
{
//load the Excel file
Workbook workbook = new Workbook();
workbook.LoadFromFile("Input.xlsx");
//get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//get the first pivot table in the worksheet
IPivotTable pivotTable = sheet.PivotTables[0];
//create a clustered column chart based on the pivot table
Chart chart = sheet.Charts.Add(ExcelChartType.ColumnClustered, pivotTable);
//set chart position
chart.TopRow = 19;
chart.BottomRow = 38;
//set chart title
chart.ChartTitle = "Pivot Chart";
//save the resultant file
workbook.SaveToFile("CreatPivotChart.xlsx", ExcelVersion.Version2013);
}
}
}
Screenshot of the created pivot chart:
Create Chart without Using Worksheet Data Range in C#
2019-02-15 08:27:44 Written by support iceblueThis article demonstrates how to create a chart without reference to the worksheet data range using Spire.XLS.
Detail steps:
Step 1: Create a workbook and get the first worksheet.
Workbook wb = new Workbook(); Worksheet sheet = wb.Worksheets[0];
Step 2: Add a chart to the worksheet.
Chart chart = sheet.Charts.Add();
Step 3: Add a series to the chart.
var series = chart.Series.Add();
Step 4: Add data.
series.EnteredDirectlyValues = new object[] { 10, 20, 30 };
Step 5: Save the file.
wb.SaveToFile("result.xlsx", ExcelVersion.Version2013);
Output:
Full code:
using Spire.Xls;
namespace Create_chart
{
class Program
{
static void Main(string[] args)
{
//Create a workbook
Workbook wb = new Workbook();
//Get the first worksheet
Worksheet sheet = wb.Worksheets[0];
//Add a chart to the worksheet
Chart chart = sheet.Charts.Add();
//Add a series to the chart
var series = chart.Series.Add();
//Add data
series.EnteredDirectlyValues = new object[] { 10, 20, 30 };
//Save the file
wb.SaveToFile("result.xlsx", ExcelVersion.Version2013);
}
}
}
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);
}
}
}
Delete Legend and Specific Legend Entries from Excel Chart in C#
2018-03-12 07:55:52 Written by support iceblueA legend is displayed in the chart area by default. However it can be removed from the chart. With Spire.XLS, we can delete the whole legend as well as specific legend entries from Excel chart. This article is going to demonstrate how we can use Spire.XLS to accomplish this function.
Below screenshot shows the Excel chart we used for demonstration:
Delete the whole legend
using Spire.Xls;
namespace DeleteLegend
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load the Excel file
workbook.LoadFromFile("sample.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Get the chart
Chart chart = sheet.Charts[0];
//Delete legend from the chart
chart.Legend.Delete();
//Save the file
workbook.SaveToFile("DeleteLegend.xlsx", ExcelVersion.Version2013);
}
}
}
Screenshot:
Delete specific legend entries
using Spire.Xls;
namespace DeleteLegend
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load the Excel file
workbook.LoadFromFile("sample.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Get the chart
Chart chart = sheet.Charts[0];
//Delete the first and the second legend entries from the chart
chart.Legend.LegendEntries[0].Delete();
chart.Legend.LegendEntries[1].Delete();
//Save the file
workbook.SaveToFile("DeleteLegendEntries.xlsx", ExcelVersion.Version2013);
}
}
}
Screenshot:
Create Multi-Level Category Chart in Excel in C#, VB.NET
2018-03-12 07:12:02 Written by support iceblueMulti-level category chart is a chart type that has both main category and subcategory labels. This type of chart is useful when you have figures for items that belong to different categories. In this article, you will learn how to create a multi-level category chart in Excel using Spire.XLS with C# and VB.NET.
Step 1: Create a Workbook instance and get the first worksheet.
Workbook wb = new Workbook(); Worksheet sheet = wb.Worksheets[0];
Step 2: Write data to cells.
sheet.Range["A1"].Text = "Main Category"; sheet.Range["A2"].Text = "Fruit"; sheet.Range["A6"].Text = "Vegies"; sheet.Range["B1"].Text = "Sub Category"; sheet.Range["B2"].Text = "Bananas"; sheet.Range["B3"].Text = "Oranges"; sheet.Range["B4"].Text = "Pears"; sheet.Range["B5"].Text = "Grapes"; sheet.Range["B6"].Text = "Carrots"; sheet.Range["B7"].Text = "Potatoes"; sheet.Range["B8"].Text = "Celery"; sheet.Range["B9"].Text = "Onions"; sheet.Range["C1"].Text = "Value"; sheet.Range["C2"].Value = "52"; sheet.Range["C3"].Value = "65"; sheet.Range["C4"].Value = "50"; sheet.Range["C5"].Value = "45"; sheet.Range["C6"].Value = "64"; sheet.Range["C7"].Value = "62"; sheet.Range["C8"].Value = "89"; sheet.Range["C9"].Value = "57";
Step 3: Vertically merge cells from A2 to A5, A6 to A9.
sheet.Range["A2:A5"].Merge(); sheet.Range["A6:A9"].Merge();
Step 4: Add a clustered bar chart to worksheet.
Chart chart = sheet.Charts.Add(ExcelChartType.BarClustered); chart.ChartTitle = "Value"; chart.PlotArea.Fill.FillType = ShapeFillType.NoFill; chart.Legend.Delete();
Step 5: Set the data source of series data.
chart.DataRange = sheet.Range["C2:C9"]; chart.SeriesDataFromRange = false;
Step 6: Set the data source of category labels.
ChartSerie serie = chart.Series[0]; serie.CategoryLabels = sheet.Range["A2:B9"];
Step 7: Show multi-level category labels.
chart.PrimaryCategoryAxis.MultiLevelLable = true;
Step 8: Save the file.
wb.SaveToFile("output.xlsx", ExcelVersion.Version2013);
Output:
Full Code:
using Spire.Xls;
using Spire.Xls.Charts;
namespace CreateMutilLevelChart
{
class Program
{
static void Main(string[] args)
{
Workbook wb = new Workbook();
Worksheet sheet = wb.Worksheets[0];
sheet.Range["A1"].Text = "Main Category";
sheet.Range["A2"].Text = "Fruit";
sheet.Range["A6"].Text = "Vegies";
sheet.Range["B1"].Text = "Sub Category";
sheet.Range["B2"].Text = "Bananas";
sheet.Range["B3"].Text = "Oranges";
sheet.Range["B4"].Text = "Pears";
sheet.Range["B5"].Text = "Grapes";
sheet.Range["B6"].Text = "Carrots";
sheet.Range["B7"].Text = "Potatoes";
sheet.Range["B8"].Text = "Celery";
sheet.Range["B9"].Text = "Onions";
sheet.Range["C1"].Text = "Value";
sheet.Range["C2"].Value = "52";
sheet.Range["C3"].Value = "65";
sheet.Range["C4"].Value = "50";
sheet.Range["C5"].Value = "45";
sheet.Range["C6"].Value = "64";
sheet.Range["C7"].Value = "62";
sheet.Range["C8"].Value = "89";
sheet.Range["C9"].Value = "57";
sheet.Range["A2:A5"].Merge();
sheet.Range["A6:A9"].Merge();
sheet.AutoFitColumn(1);
sheet.AutoFitColumn(2);
Chart chart = sheet.Charts.Add(ExcelChartType.BarClustered);
chart.ChartTitle = "Value";
chart.PlotArea.Fill.FillType = ShapeFillType.NoFill;
chart.Legend.Delete();
chart.LeftColumn = 5;
chart.TopRow = 1;
chart.RightColumn = 14;
chart.DataRange = sheet.Range["C2:C9"];
chart.SeriesDataFromRange = false;
ChartSerie serie = chart.Series[0];
serie.CategoryLabels = sheet.Range["A2:B9"];
chart.PrimaryCategoryAxis.MultiLevelLable = true;
wb.SaveToFile("output.xlsx", ExcelVersion.Version2013);
}
}
}
Imports Spire.Xls
Imports Spire.Xls.Charts
Namespace CreateMutilLevelChart
Class Program
Private Shared Sub Main(args As String())
Dim wb As New Workbook()
Dim sheet As Worksheet = wb.Worksheets(0)
sheet.Range("A1").Text = "Main Category"
sheet.Range("A2").Text = "Fruit"
sheet.Range("A6").Text = "Vegies"
sheet.Range("B1").Text = "Sub Category"
sheet.Range("B2").Text = "Bananas"
sheet.Range("B3").Text = "Oranges"
sheet.Range("B4").Text = "Pears"
sheet.Range("B5").Text = "Grapes"
sheet.Range("B6").Text = "Carrots"
sheet.Range("B7").Text = "Potatoes"
sheet.Range("B8").Text = "Celery"
sheet.Range("B9").Text = "Onions"
sheet.Range("C1").Text = "Value"
sheet.Range("C2").Value = "52"
sheet.Range("C3").Value = "65"
sheet.Range("C4").Value = "50"
sheet.Range("C5").Value = "45"
sheet.Range("C6").Value = "64"
sheet.Range("C7").Value = "62"
sheet.Range("C8").Value = "89"
sheet.Range("C9").Value = "57"
sheet.Range("A2:A5").Merge()
sheet.Range("A6:A9").Merge()
sheet.AutoFitColumn(1)
sheet.AutoFitColumn(2)
Dim chart As Chart = sheet.Charts.Add(ExcelChartType.BarClustered)
chart.ChartTitle = "Value"
chart.PlotArea.Fill.FillType = ShapeFillType.NoFill
chart.Legend.Delete()
chart.LeftColumn = 5
chart.TopRow = 1
chart.RightColumn = 14
chart.DataRange = sheet.Range("C2:C9")
chart.SeriesDataFromRange = False
Dim serie As ChartSerie = chart.Series(0)
serie.CategoryLabels = sheet.Range("A2:B9")
chart.PrimaryCategoryAxis.MultiLevelLable = True
wb.SaveToFile("output.xlsx", ExcelVersion.Version2013)
End Sub
End Class
End Namespace
How to set the rotation for the 3D chart on Excel in C#
2018-02-27 07:37:25 Written by support iceblueWith Spire.XLS, we can set the 3-D rotation easily in C#. The following code example explains how to set the rotation of the 3D chart view in C#. We will use 3D pie chart for example. Firstly, view the original 3D pie chart:
Code snippet of how to set the 3D rotation for Excel Chart:
Step 1: Create a new instance of workbook and load the sample document from file.
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Step 2: Get the chart from the first worksheet.
Worksheet sheet = workbook.Worksheets[0]; Chart chart = sheet.Charts[0];
Step 3: Set Rotation of the 3D chart view for X and Y.
//X rotation: chart.Rotation=30; //Y rotation: chart.Elevation = 20;
Step 4: Save the document to file.
workbook.SaveToFile("Result.xlsx", ExcelVersion.Version2010);
Effective screenshot of the Excel 3D chart rotation:
Full codes:
using Spire.Xls;
namespace SetRotation
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet sheet = workbook.Worksheets[0];
Chart chart = sheet.Charts[0];
//X rotation:
chart.Rotation = 30;
//Y rotation:
chart.Elevation = 20;
workbook.SaveToFile("Result.xlsx", ExcelVersion.Version2010);
}
}
}
In the previous article, we have demonstrated how to explode a pie chart via Spire.XLS. This article we will explain how to explode a doughnut chart in C#.
When we need to explode a doughnut chart, we can set the chart type as DoughnutExploded when we create a doughnut chart from scratch. For the existing doughnut chart, we can easily change the chart type to explode the doughnut chart.
Step 1: Create an instance of Excel workbook and load the document from file.
Workbook workbook = new Workbook();
workbook.LoadFromFile("DoughnutChart.xlsx",ExcelVersion.Version2010);
Step 2: Get the first worksheet from the sample workbook.
Worksheet sheet = workbook.Worksheets[0];
Step 3: Get the first chart from the first worksheet.
Chart chart = sheet.Charts[0];
Step 4: Set the chart type as DoughnutExploded to explode the doughnut chart.
chart.ChartType = ExcelChartType.DoughnutExploded;
Step 5: Save the document to file.
workbook.SaveToFile("ExplodedDoughnutChart.xlsx", ExcelVersion.Version2010);
Effective screenshot of the explode doughnut chart:
Full codes of how to explode a doughnut chart:
using Spire.Xls;
namespace ExplodeDoughnut
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile("DoughnutChart.xlsx", ExcelVersion.Version2010);
Worksheet sheet = workbook.Worksheets[0];
Chart chart = sheet.Charts[0];
chart.ChartType = ExcelChartType.DoughnutExploded;
workbook.SaveToFile("ExplodedDoughnutChart.xlsx", ExcelVersion.Version2010);
}
}
}
How to remove chart from Excel worksheet in C#, VB.NET
2017-09-13 09:21:02 Written by support iceblueWith Spire.XLS for .NET, we could easily add different kinds of charts to Excel worksheet, such as Pie Chart, Column Chart, bar chart, line chart, radar chart, Doughnut chart, pyramid chart, etc. In this article, we'll show you how to remove chart from Excel worksheet by using Spire.XLS.
Below is a sample document which contains a chart and table from the Excel worksheet, then we'll remove the chart from the slide.
C# Code Snippet of how to remove the chart from Excel:
Step 1: Create an instance of Excel workbook and load the document from file.
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Step 2: Get the first worksheet from the workbook.
Worksheet sheet = workbook.Worksheets[0];
Step 3: Get the first chart from the first worksheet.
IChartShape chart = sheet.Charts[0];
Step 4: Remove the chart.
chart.Remove();
Step 5: Save the document to file.
workbook.SaveToFile("RemoveChart.xlsx");
Effective screenshot:
Full code:
using Spire.Xls;
using Spire.Xls.Core;
namespace RemoveChart
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet sheet = workbook.Worksheets[0];
IChartShape chart = sheet.Charts[0];
chart.Remove();
workbook.SaveToFile("RemoveChart.xlsx");
}
}
}
Imports Spire.Xls
Imports Spire.Xls.Core
Namespace RemoveChart
Class Program
Private Shared Sub Main(args As String())
Dim workbook As New Workbook()
workbook.LoadFromFile("Sample.xlsx")
Dim sheet As Worksheet = workbook.Worksheets(0)
Dim chart As IChartShape = sheet.Charts(0)
chart.Remove()
workbook.SaveToFile("RemoveChart.xlsx")
End Sub
End Class
End Namespace
