Working with Charts using Spire.XLS

Today we will see an another in a series of new products, Spire.XLS, that helps us to create, manipulate and convert an Excel file to other formats, create charts dynamically and much more. This product has been introduced by the company E-Iceblue. I hope you have read my article of Spire.Doc andSpire.XLS. If you have not read it, I recommend you to read it here: Using Spire.XLS.

Please see this article in my blog.

Background

As you all know, charts are the graphical representations of our data. It is much easier to understand our data if it is in a graphical format. Am I right? It is easy to create a static chart, but what about a dynamic one? It will be bit tough, right? But by using Spire.XLS, we can create any kind of charts easily. In this article, we will see those implementations.

Download the files

You can always get the necessary files from Download Spire.XLS.

Install Spire.XLS

I am using evaluation version with a one month temporary license. There are free versions also available for spire.xls with some limitation. You can try that. Now click on the exe file after you extract the downloaded file. The installation will proceed.

Using Spire XLS

Using the code

Before starting with the coding you need to add the necessary namespaces as follows.

using Spire.Xls;
using System.Drawing;

First of all create a form and then a button, in the button click add the following lines of codes.

C# Code

private void button1_Click(object sender, EventArgs e) {
try {
//Create a new workbook
Workbook workbook = new Workbook();
//Initialize worksheet
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
//Set sheet name
sheet.Name = "Chart data";
//Set the grid lines invisible
sheet.GridLinesVisible = false;
//Create a chart
Chart chart = sheet.Charts.Add(ExcelChartType.Pie3D);
//Set region of chart data
chart.DataRange = sheet.Range["B2:B5"];
chart.SeriesDataFromRange = false;
//Set position of chart
chart.LeftColumn = 1;
chart.TopRow = 6;
chart.RightColumn = 9;
chart.BottomRow = 25;
//Chart title
chart.ChartTitle = "Sales by year";
chart.ChartTitleArea.IsBold = true;
chart.ChartTitleArea.Size = 12;
//Initialize the chart series
Spire.Xls.Charts.ChartSerie cs = chart.Series[0];
//Chart Labels resource
cs.CategoryLabels = sheet.Range["A2:A5"];
//Chart value resource
cs.Values = sheet.Range["B2:B5"];
//Set the value visible in the chart
cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
//Year
sheet.Range["A1"].Value = "Year";
sheet.Range["A2"].Value = "2002";
sheet.Range["A3"].Value = "2003";
sheet.Range["A4"].Value = "2004";
sheet.Range["A5"].Value = "2005";
//Sales
sheet.Range["B1"].Value = "Sales";
sheet.Range["B2"].NumberValue = 4000;
sheet.Range["B3"].NumberValue = 6000;
sheet.Range["B4"].NumberValue = 7000;
sheet.Range["B5"].NumberValue = 8500;
//Style
sheet.Range["A1:B1"].Style.Font.IsBold = true;
sheet.Range["A2:B2"].Style.KnownColor = ExcelColors.LightYellow;
sheet.Range["A3:B3"].Style.KnownColor = ExcelColors.LightGreen1;
sheet.Range["A4:B4"].Style.KnownColor = ExcelColors.LightOrange;
sheet.Range["A5:B5"].Style.KnownColor = ExcelColors.LightTurquoise;
//Border
sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeTop].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeBottom].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeLeft].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeRight].Color = Color.FromArgb(0, 0, 128);
sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
//Number format
sheet.Range["B2:C5"].Style.NumberFormat = "\"$\"#,##0";
chart.PlotArea.Fill.Visible = false;
//Save the file
workbook.SaveToFile("Sample.xls");
//Launch the file
System.Diagnostics.Process.Start("Sample.xls");
} catch (Exception) {
throw;
}
}

VB.NET Code

'Load Workbook
Dim workbook As New Workbook()
workbook.LoadFromFile("E:\Sample.xlsx")
Dim sheet As Worksheet = workbook.Worksheets(0)
'Add Chart and Set Chart Data Range
Dim chart As Chart = sheet.Charts.Add(ExcelChartType.ColumnClustered)
chart.DataRange = sheet.Range("D1:E17")
chart.SeriesDataFromRange = False
'Chart Position
chart.LeftColumn = 1
chart.TopRow = 19
chart.RightColumn = 8
chart.BottomRow = 33
'Chart Border
chart.ChartArea.Border.Weight = ChartLineWeightType.Medium
chart.ChartArea.Border.Color = Color.SandyBrown
'Chart Title
chart.ChartTitle = "Parts Sales Info"
chart.ChartTitleArea.Font.FontName = "Calibri"
chart.ChartTitleArea.Font.Size = 13
chart.ChartTitleArea.Font.IsBold = True
'Chart Axes
chart.PrimaryCategoryAxis.Title = "Parts"
chart.PrimaryCategoryAxis.Font.Color = Color.Blue
chart.PrimaryValueAxis.Title = "Amounts"
chart.PrimaryValueAxis.HasMajorGridLines = False
chart.PrimaryValueAxis.MaxValue = 350
chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90
'Chart Legend
chart.Legend.Position = LegendPositionType.Right
'Save and Launch
workbook.SaveToFile("ExcelColumnChart.xlsx", ExcelVersion.Version2010)
System.Diagnostics.Process.Start("ExcelColumnChart.xlsx")

In the preceding code, we are creating a Pie chart by giving some settings and data and load it. Now if you run it you will get output as follows.

In the preceding code we are doing many processes, those processes are listed below.

Creating a new workbook
Initialize worksheet newly created
Set sheet name of worksheet
Set the grid lines invisible
Creating a chart
Set region of chart data
Set position of chart
Set Chart title
Initialize the chart series
Setting Chart Labels resource
Setting Chart value resource
Set the value visible in the chart
Apply Styles
Apply Borders
Provide Number format if necessary
Save the file
Finally Launch the file

Wow! That’s cool, right?

We are not yet finished! There are so many things you can try with your sheet object. I suggest you to try those. You will be surprised.

Working With Charts Using Spire XLS Sheet Object

Working With Charts Using Spire XLS Sheet Object

If you want, you can set various chart types too, it will give you a great design in your chart. The most useful chart types that I use are Bar3DClustered, 3DBubble, Bubble, Column3D and many more.

Working With Charts Using Spire XLS Chart Object

This product gives you many ways to make your chart in the way you like. There are plenty of options available, like you can set the Legend Position using the LegendPositionType property.

Next we will see how to implement a Column chart, whatever we have explained will be the same for every chart, but it may have some different properties that strictly depend on the chart type.

Column Chart

Now we will create an another button and name it Column Chart. And in the button click you need to add the following code.

C# Code

private void button2_Click(object sender, EventArgs e) {
try {
//Load Workbook
Workbook workbook = new Workbook();
workbook.LoadFromFile(@
"D:\Sample.xlsx");
Worksheet sheet = workbook.Worksheets[0];
//Add Chart and Set Chart Data Range
Chart chart = sheet.Charts.Add(ExcelChartType.ColumnClustered);
chart.DataRange = sheet.Range["D1:E17"];
chart.SeriesDataFromRange = false;
//Chart Position
chart.LeftColumn = 1;
chart.TopRow = 19;
chart.RightColumn = 8;
chart.BottomRow = 33;
//Chart Border
chart.ChartArea.Border.Weight = ChartLineWeightType.Medium;
chart.ChartArea.Border.Color = Color.SandyBrown;
//Chart Title
chart.ChartTitle = "Parts Sales Info";
chart.ChartTitleArea.Font.FontName = "Calibri";
chart.ChartTitleArea.Font.Size = 13;
chart.ChartTitleArea.Font.IsBold = true;
//Chart Axes
chart.PrimaryCategoryAxis.Title = "Parts";
chart.PrimaryCategoryAxis.Font.Color = Color.Blue;
chart.PrimaryValueAxis.Title = "Amounts";
chart.PrimaryValueAxis.HasMajorGridLines = false;
chart.PrimaryValueAxis.MaxValue = 350;
chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;
//Chart Legend
chart.Legend.Position = LegendPositionType.Right;
//Save and Launch
workbook.SaveToFile("ExcelColumnChart.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("ExcelColumnChart.xlsx");
} catch (Exception) {
throw;
}
}

VB.NET Code

'Load Workbook
Dim workbook As New Workbook()
workbook.LoadFromFile("E:\Sample.xlsx")
Dim sheet As Worksheet = workbook.Worksheets(0)
'Add Chart and Set Chart Data Range
Dim chart As Chart = sheet.Charts.Add(ExcelChartType.ColumnClustered)
chart.DataRange = sheet.Range("D1:E17")
chart.SeriesDataFromRange = False
'Chart Position
chart.LeftColumn = 1
chart.TopRow = 19
chart.RightColumn = 8
chart.BottomRow = 33
'Chart Border
chart.ChartArea.Border.Weight = ChartLineWeightType.Medium
chart.ChartArea.Border.Color = Color.SandyBrown
'Chart Title
chart.ChartTitle = "Parts Sales Info"
chart.ChartTitleArea.Font.FontName = "Calibri"
chart.ChartTitleArea.Font.Size = 13
chart.ChartTitleArea.Font.IsBold = True
'Chart Axes
chart.PrimaryCategoryAxis.Title = "Parts"
chart.PrimaryCategoryAxis.Font.Color = Color.Blue
chart.PrimaryValueAxis.Title = "Amounts"
chart.PrimaryValueAxis.HasMajorGridLines = False
chart.PrimaryValueAxis.MaxValue = 350
chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90
'Chart Legend
chart.Legend.Position = LegendPositionType.Right
'Save and Launch
workbook.SaveToFile("ExcelColumnChart.xlsx", ExcelVersion.Version2010)
System.Diagnostics.Process.Start("ExcelColumnChart.xlsx")

Now if you run the code, you can see output as follows.

Excel Bar Chart

Before going through you must add an new namespace as follows.

private void button1_Click(object sender, EventArgs e) {
try {
Workbook workbook = new Workbook();
//Initailize worksheet
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Chart data";
sheet.GridLinesVisible = false;
//Writes chart data
CreateChartData(sheet);
//Add a new chart worsheet to workbook
Chart chart = sheet.Charts.Add();
//Set region of chart data
chart.DataRange = sheet.Range["A1:C5"];
chart.SeriesDataFromRange = false;
//Set position of chart
chart.LeftColumn = 1;
chart.TopRow = 6;
chart.RightColumn = 11;
chart.BottomRow = 29;
chart.ChartType = ExcelChartType.Bar3DClustered;
//Chart title
chart.ChartTitle = "Sales market by country";
chart.ChartTitleArea.IsBold = true;
chart.ChartTitleArea.Size = 12;
chart.PrimaryCategoryAxis.Title = "Country";
chart.PrimaryCategoryAxis.Font.IsBold = true;
chart.PrimaryCategoryAxis.TitleArea.IsBold = true;
chart.PrimaryCategoryAxis.TitleArea.TextRotationAngle = 90;
chart.PrimaryValueAxis.Title = "Sales(in Dollars)";
chart.PrimaryValueAxis.HasMajorGridLines = false;
chart.PrimaryValueAxis.MinValue = 1000;
chart.PrimaryValueAxis.TitleArea.IsBold = true;
foreach(ChartSerie cs in chart.Series) {
cs.Format.Options.IsVaryColor = true;
cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
}
chart.Legend.Position = LegendPositionType.Top;
workbook.SaveToFile("Sample.xls");
ExcelDocViewer(workbook.FileName);
} catch (Exception) {
throw;
}
}

Now we will create an another button in our form and in the click event we will write the preceding lines of codes.

As you can see we are calling a function CreateChartData to generate the data, so now we will write the function body.

private void CreateChartData(Worksheet sheet) {
//Country
sheet.Range["A1"].Value = "Country";
sheet.Range["A2"].Value = "Cuba";
sheet.Range["A3"].Value = "Mexico";
sheet.Range["A4"].Value = "France";
sheet.Range["A5"].Value = "German";
//Jun
sheet.Range["B1"].Value = "Jun";
sheet.Range["B2"].NumberValue = 6000;
sheet.Range["B3"].NumberValue = 8000;
sheet.Range["B4"].NumberValue = 9000;
sheet.Range["B5"].NumberValue = 8500;
//Jun
sheet.Range["C1"].Value = "Aug";
sheet.Range["C2"].NumberValue = 3000;
sheet.Range["C3"].NumberValue = 2000;
sheet.Range["C4"].NumberValue = 2300;
sheet.Range["C5"].NumberValue = 4200;
//Style
sheet.Range["A1:C1"].Style.Font.IsBold = true;
sheet.Range["A2:C2"].Style.KnownColor = ExcelColors.LightYellow;
sheet.Range["A3:C3"].Style.KnownColor = ExcelColors.LightGreen1;
sheet.Range["A4:C4"].Style.KnownColor = ExcelColors.LightOrange;
sheet.Range["A5:C5"].Style.KnownColor = ExcelColors.LightTurquoise;
//Border
sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeTop].Color = Color.FromA #000080;
sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeBottom].Color = Color.FromA #000080;
sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeLeft].Color = Color.FromA #000080;
sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeRight].Color = Color.FromA #000080;
sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
sheet.Range["B2:C5"].Style.NumberFormat = "\"$\"#,##0";
}

And we will use the preceding function for viewing the chart we created.

private void ExcelDocViewer(string fileName)
{
try
{
System.Diagnostics.Process.Start(fileName);
}
catch { }
}

Now it is time to run our program. You will see the output as follows.

Excel Bar Chart

As you can see it is very easy to provide styles and border to a chart.

//Style
sheet.Range["A1:C1"].Style.Font.IsBold = true;
sheet.Range["A2:C2"].Style.KnownColor = ExcelColors.LightYellow;
sheet.Range["A3:C3"].Style.KnownColor = ExcelColors.LightGreen1;
sheet.Range["A4:C4"].Style.KnownColor = ExcelColors.LightOrange;
sheet.Range["A5:C5"].Style.KnownColor = ExcelColors.LightTurquoise;
//Border
sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeTop].Color = Color.FromA#000080;
sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeBottom].Color = Color.FromA#000080;
sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeLeft].Color = Color.FromA#000080;
sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeRight].Color = Color.FromA#000080;
sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
sheet.Range["B2:C5"].Style.NumberFormat = "\"$\"#,##0";

Like that we can create a Line Chart, Radar Chart and many more. Please try those too. Now we will see how to save Excel charts as images.

How to Save Excel Charts as Images

Create another button and write the preceding code in the button click.

private void button2_Click(object sender, EventArgs e) {
Workbook workbook = new Workbook();
workbook.LoadFromFile("D:\\Sample.xlsx", ExcelVersion.Version2010);
Worksheet sheet = workbook.Worksheets[0];
Image[] imgs = workbook.SaveChartAsImage(sheet);
for (int i = 0; i < imgs.Length; i++) {
imgs[i].Save(string.Format("img-{0}.png", i), System.Drawing.Imaging.ImageFormat.Png);
}
}

Here we are taking a file called Sample.xlsx and loop through the charts inside the file and save the images with a few lines codes. Sounds good, right?

VB.Net Code

Dim workbook As New Workbook()
workbook.LoadFromFile("D:\\Sample.xlsx", ExcelVersion.Version2010)
Dim sheet As Worksheet = workbook.Worksheets(0)
Dim imgs As Image() = workbook.SaveChartAsImage(sheet)
For i As Integer = 0 To imgs.Length - 1
imgs(i).Save(String.Format("img-{0}.png", i), ImageFormat.Png)
Next

Conclusion

I hope you liked this article. Please share with me your valuable suggestions and feedback.

Kindest Regards,
Sibeesh Venu

22 Comments

Join the conversation! Your thoughts help the community grow.

Bharat Dev3y ago
Good article. Can you also please explain how to add error bars to a column chart with ErrorBarType.Custom/ErrorBarType.StandardError/ErrorBarType.StandardDeviation values. THank you
Budak Seronok8y ago
How to Switch Row/Column like in excel ? Is there any function for do that in spire ?
Santhakumar Munuswamy11y ago
Good Article. Thanks for sharing
Gopi Chand11y ago
Well done Sibeesh :)
Shridhar Sharma11y ago
nice demo :)
Nilesh Jadav11y ago
Nice post sir :)
Sibeesh Venu11y ago
Ankit Bansal Thank you
Ankit Bansal11y ago
good share...
Sibeesh Venu11y ago
Gowtham K Thank you :)
Gowtham K11y ago
Great Work
Sibeesh Venu11y ago
Van Lai Thank you so much
Van Lai11y ago
Thank you so much for your efforts in the informative introduction.
Sibeesh Venu11y ago
Pankaj Kumar Choudhary Thank you
Sibeesh Venu11y ago
Rajeesh Menoth Thank you
Sibeesh Venu11y ago
Rakesh Chavda Thanks buddy
Sibeesh Venu11y ago
Vaikesh K P Thank you
Sibeesh Venu11y ago
Karthikeyan K Thank you
Pankaj Kumar Choudhary11y ago
Nice work Sir...........
Rajeesh Menoth11y ago
Good One....
Rakesh11y ago
Good work buddy
Vaikesh K P11y ago
Good One
Karthikeyan K11y ago
Awesome one sir...Thanks for share