Products

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.

  1. using Spire.Xls;  
  2. 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

  1. private void button1_Click(object sender, EventArgs e) {  
  2.     try {  
  3.         //Create a new workbook    
  4.         Workbook workbook = new Workbook();  
  5.         //Initialize worksheet    
  6.         workbook.CreateEmptySheets(1);  
  7.         Worksheet sheet = workbook.Worksheets[0];  
  8.         //Set sheet name    
  9.         sheet.Name = "Chart data";  
  10.         //Set the grid lines invisible    
  11.         sheet.GridLinesVisible = false;  
  12.         //Create a chart    
  13.         Chart chart = sheet.Charts.Add(ExcelChartType.Pie3D);  
  14.         //Set region of chart data    
  15.         chart.DataRange = sheet.Range["B2:B5"];  
  16.         chart.SeriesDataFromRange = false;  
  17.         //Set position of chart    
  18.         chart.LeftColumn = 1;  
  19.         chart.TopRow = 6;  
  20.         chart.RightColumn = 9;  
  21.         chart.BottomRow = 25;  
  22.         //Chart title    
  23.         chart.ChartTitle = "Sales by year";  
  24.         chart.ChartTitleArea.IsBold = true;  
  25.         chart.ChartTitleArea.Size = 12;  
  26.         //Initialize the chart series    
  27.         Spire.Xls.Charts.ChartSerie cs = chart.Series[0];  
  28.         //Chart Labels resource    
  29.         cs.CategoryLabels = sheet.Range["A2:A5"];  
  30.         //Chart value resource    
  31.         cs.Values = sheet.Range["B2:B5"];  
  32.         //Set the value visible in the chart    
  33.         cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;  
  34.         //Year    
  35.         sheet.Range["A1"].Value = "Year";  
  36.         sheet.Range["A2"].Value = "2002";  
  37.         sheet.Range["A3"].Value = "2003";  
  38.         sheet.Range["A4"].Value = "2004";  
  39.         sheet.Range["A5"].Value = "2005";  
  40.         //Sales    
  41.         sheet.Range["B1"].Value = "Sales";  
  42.         sheet.Range["B2"].NumberValue = 4000;  
  43.         sheet.Range["B3"].NumberValue = 6000;  
  44.         sheet.Range["B4"].NumberValue = 7000;  
  45.         sheet.Range["B5"].NumberValue = 8500;  
  46.         //Style    
  47.         sheet.Range["A1:B1"].Style.Font.IsBold = true;  
  48.         sheet.Range["A2:B2"].Style.KnownColor = ExcelColors.LightYellow;  
  49.         sheet.Range["A3:B3"].Style.KnownColor = ExcelColors.LightGreen1;  
  50.         sheet.Range["A4:B4"].Style.KnownColor = ExcelColors.LightOrange;  
  51.         sheet.Range["A5:B5"].Style.KnownColor = ExcelColors.LightTurquoise;  
  52.         //Border    
  53.         sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeTop].Color = Color.FromArgb(0, 0, 128);  
  54.         sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;  
  55.         sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeBottom].Color = Color.FromArgb(0, 0, 128);  
  56.         sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;  
  57.         sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeLeft].Color = Color.FromArgb(0, 0, 128);  
  58.         sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;  
  59.         sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeRight].Color = Color.FromArgb(0, 0, 128);  
  60.         sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;  
  61.         //Number format    
  62.         sheet.Range["B2:C5"].Style.NumberFormat = "\"$\"#,##0";  
  63.         chart.PlotArea.Fill.Visible = false;  
  64.         //Save the file    
  65.         workbook.SaveToFile("Sample.xls");  
  66.         //Launch the file    
  67.         System.Diagnostics.Process.Start("Sample.xls");  
  68.     } catch (Exception) {  
  69.         throw;  
  70.     }  
  71. }  

VB.NET Code

  1. 'Load Workbook    
  2. Dim workbook As New Workbook()    
  3. workbook.LoadFromFile("E:\Sample.xlsx")    
  4. Dim sheet As Worksheet = workbook.Worksheets(0)    
  5. 'Add Chart and Set Chart Data Range    
  6. Dim chart As Chart = sheet.Charts.Add(ExcelChartType.ColumnClustered)    
  7. chart.DataRange = sheet.Range("D1:E17")    
  8. chart.SeriesDataFromRange = False    
  9. 'Chart Position    
  10. chart.LeftColumn = 1    
  11. chart.TopRow = 19    
  12. chart.RightColumn = 8    
  13. chart.BottomRow = 33    
  14. 'Chart Border    
  15. chart.ChartArea.Border.Weight = ChartLineWeightType.Medium    
  16. chart.ChartArea.Border.Color = Color.SandyBrown    
  17. 'Chart Title    
  18. chart.ChartTitle = "Parts Sales Info"    
  19. chart.ChartTitleArea.Font.FontName = "Calibri"    
  20. chart.ChartTitleArea.Font.Size = 13    
  21. chart.ChartTitleArea.Font.IsBold = True    
  22. 'Chart Axes    
  23. chart.PrimaryCategoryAxis.Title = "Parts"    
  24. chart.PrimaryCategoryAxis.Font.Color = Color.Blue    
  25. chart.PrimaryValueAxis.Title = "Amounts"    
  26. chart.PrimaryValueAxis.HasMajorGridLines = False    
  27. chart.PrimaryValueAxis.MaxValue = 350    
  28. chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90    
  29. 'Chart Legend    
  30. chart.Legend.Position = LegendPositionType.Right    
  31. 'Save and Launch    
  32. workbook.SaveToFile("ExcelColumnChart.xlsx", ExcelVersion.Version2010)    
  33. 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.

Create_Charts_Using_SpireXLS

Create_Charts_Using_SpireXLS_Output

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

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 

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

  1. private void button2_Click(object sender, EventArgs e) {  
  2.     try {  
  3.         //Load Workbook    
  4.         Workbook workbook = new Workbook();  
  5.         workbook.LoadFromFile(@  
  6.         "D:\Sample.xlsx");  
  7.         Worksheet sheet = workbook.Worksheets[0];  
  8.         //Add Chart and Set Chart Data Range    
  9.         Chart chart = sheet.Charts.Add(ExcelChartType.ColumnClustered);  
  10.         chart.DataRange = sheet.Range["D1:E17"];  
  11.         chart.SeriesDataFromRange = false;  
  12.         //Chart Position    
  13.         chart.LeftColumn = 1;  
  14.         chart.TopRow = 19;  
  15.         chart.RightColumn = 8;  
  16.         chart.BottomRow = 33;  
  17.         //Chart Border    
  18.         chart.ChartArea.Border.Weight = ChartLineWeightType.Medium;  
  19.         chart.ChartArea.Border.Color = Color.SandyBrown;  
  20.         //Chart Title    
  21.         chart.ChartTitle = "Parts Sales Info";  
  22.         chart.ChartTitleArea.Font.FontName = "Calibri";  
  23.         chart.ChartTitleArea.Font.Size = 13;  
  24.         chart.ChartTitleArea.Font.IsBold = true;  
  25.         //Chart Axes    
  26.         chart.PrimaryCategoryAxis.Title = "Parts";  
  27.         chart.PrimaryCategoryAxis.Font.Color = Color.Blue;  
  28.         chart.PrimaryValueAxis.Title = "Amounts";  
  29.         chart.PrimaryValueAxis.HasMajorGridLines = false;  
  30.         chart.PrimaryValueAxis.MaxValue = 350;  
  31.         chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;  
  32.         //Chart Legend    
  33.         chart.Legend.Position = LegendPositionType.Right;  
  34.         //Save and Launch    
  35.         workbook.SaveToFile("ExcelColumnChart.xlsx", ExcelVersion.Version2010);  
  36.         System.Diagnostics.Process.Start("ExcelColumnChart.xlsx");  
  37.     } catch (Exception) {  
  38.         throw;  
  39.     }  
  40. }  

 

VB.NET Code

  1. 'Load Workbook    
  2. Dim workbook As New Workbook()    
  3. workbook.LoadFromFile("E:\Sample.xlsx")    
  4. Dim sheet As Worksheet = workbook.Worksheets(0)    
  5. 'Add Chart and Set Chart Data Range    
  6. Dim chart As Chart = sheet.Charts.Add(ExcelChartType.ColumnClustered)    
  7. chart.DataRange = sheet.Range("D1:E17")    
  8. chart.SeriesDataFromRange = False    
  9. 'Chart Position    
  10. chart.LeftColumn = 1    
  11. chart.TopRow = 19    
  12. chart.RightColumn = 8    
  13. chart.BottomRow = 33    
  14. 'Chart Border    
  15. chart.ChartArea.Border.Weight = ChartLineWeightType.Medium    
  16. chart.ChartArea.Border.Color = Color.SandyBrown    
  17. 'Chart Title    
  18. chart.ChartTitle = "Parts Sales Info"    
  19. chart.ChartTitleArea.Font.FontName = "Calibri"    
  20. chart.ChartTitleArea.Font.Size = 13    
  21. chart.ChartTitleArea.Font.IsBold = True    
  22. 'Chart Axes    
  23. chart.PrimaryCategoryAxis.Title = "Parts"    
  24. chart.PrimaryCategoryAxis.Font.Color = Color.Blue    
  25. chart.PrimaryValueAxis.Title = "Amounts"    
  26. chart.PrimaryValueAxis.HasMajorGridLines = False    
  27. chart.PrimaryValueAxis.MaxValue = 350    
  28. chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90    
  29. 'Chart Legend    
  30. chart.Legend.Position = LegendPositionType.Right    
  31. 'Save and Launch    
  32. workbook.SaveToFile("ExcelColumnChart.xlsx", ExcelVersion.Version2010)    
  33. System.Diagnostics.Process.Start("ExcelColumnChart.xlsx")  

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

columnchart

Excel Bar Chart

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

  1. private void button1_Click(object sender, EventArgs e) {  
  2.     try {  
  3.         Workbook workbook = new Workbook();  
  4.         //Initailize worksheet  
  5.         workbook.CreateEmptySheets(1);  
  6.         Worksheet sheet = workbook.Worksheets[0];  
  7.         sheet.Name = "Chart data";  
  8.         sheet.GridLinesVisible = false;  
  9.         //Writes chart data  
  10.         CreateChartData(sheet);  
  11.         //Add a new chart worsheet to workbook  
  12.         Chart chart = sheet.Charts.Add();  
  13.         //Set region of chart data  
  14.         chart.DataRange = sheet.Range["A1:C5"];  
  15.         chart.SeriesDataFromRange = false;  
  16.         //Set position of chart  
  17.         chart.LeftColumn = 1;  
  18.         chart.TopRow = 6;  
  19.         chart.RightColumn = 11;  
  20.         chart.BottomRow = 29;  
  21.         chart.ChartType = ExcelChartType.Bar3DClustered;  
  22.         //Chart title  
  23.         chart.ChartTitle = "Sales market by country";  
  24.         chart.ChartTitleArea.IsBold = true;  
  25.         chart.ChartTitleArea.Size = 12;  
  26.         chart.PrimaryCategoryAxis.Title = "Country";  
  27.         chart.PrimaryCategoryAxis.Font.IsBold = true;  
  28.         chart.PrimaryCategoryAxis.TitleArea.IsBold = true;  
  29.         chart.PrimaryCategoryAxis.TitleArea.TextRotationAngle = 90;  
  30.         chart.PrimaryValueAxis.Title = "Sales(in Dollars)";  
  31.         chart.PrimaryValueAxis.HasMajorGridLines = false;  
  32.         chart.PrimaryValueAxis.MinValue = 1000;  
  33.         chart.PrimaryValueAxis.TitleArea.IsBold = true;  
  34.         foreach(ChartSerie cs in chart.Series) {  
  35.             cs.Format.Options.IsVaryColor = true;  
  36.             cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;  
  37.         }  
  38.         chart.Legend.Position = LegendPositionType.Top;  
  39.         workbook.SaveToFile("Sample.xls");  
  40.         ExcelDocViewer(workbook.FileName);  
  41.     } catch (Exception) {  
  42.         throw;  
  43.     }  
  44. }  

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.

  1. private void CreateChartData(Worksheet sheet) {  
  2.     //Country  
  3.     sheet.Range["A1"].Value = "Country";  
  4.     sheet.Range["A2"].Value = "Cuba";  
  5.     sheet.Range["A3"].Value = "Mexico";  
  6.     sheet.Range["A4"].Value = "France";  
  7.     sheet.Range["A5"].Value = "German";  
  8.     //Jun  
  9.     sheet.Range["B1"].Value = "Jun";  
  10.     sheet.Range["B2"].NumberValue = 6000;  
  11.     sheet.Range["B3"].NumberValue = 8000;  
  12.     sheet.Range["B4"].NumberValue = 9000;  
  13.     sheet.Range["B5"].NumberValue = 8500;  
  14.     //Jun  
  15.     sheet.Range["C1"].Value = "Aug";  
  16.     sheet.Range["C2"].NumberValue = 3000;  
  17.     sheet.Range["C3"].NumberValue = 2000;  
  18.     sheet.Range["C4"].NumberValue = 2300;  
  19.     sheet.Range["C5"].NumberValue = 4200;  
  20.     //Style  
  21.     sheet.Range["A1:C1"].Style.Font.IsBold = true;  
  22.     sheet.Range["A2:C2"].Style.KnownColor = ExcelColors.LightYellow;  
  23.     sheet.Range["A3:C3"].Style.KnownColor = ExcelColors.LightGreen1;  
  24.     sheet.Range["A4:C4"].Style.KnownColor = ExcelColors.LightOrange;  
  25.     sheet.Range["A5:C5"].Style.KnownColor = ExcelColors.LightTurquoise;  
  26.     //Border  
  27.     sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeTop].Color = Color.FromA #000080;  
  28. sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;  
  29.     sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeBottom].Color = Color.FromA #000080;  
  30. sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;  
  31.     sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeLeft].Color = Color.FromA #000080;  
  32. sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;  
  33.     sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeRight].Color = Color.FromA #000080;  
  34. sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;  
  35.     sheet.Range["B2:C5"].Style.NumberFormat = "\"$\"#,##0";  
  36. }  

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

  1. private void ExcelDocViewer(string fileName)  
  2. {  
  3.    try  
  4.    {  
  5.       System.Diagnostics.Process.Start(fileName);  
  6.    }  
  7.    catch { }  
  8. }  
Now it is time to run our program. You will see the output as follows.
 
Excel Bar Chart

Excel Bar Chart

Excel Bar Chart

Excel Bar Chart

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

  1. //Style  
  2. sheet.Range["A1:C1"].Style.Font.IsBold = true;  
  3. sheet.Range["A2:C2"].Style.KnownColor = ExcelColors.LightYellow;  
  4. sheet.Range["A3:C3"].Style.KnownColor = ExcelColors.LightGreen1;  
  5. sheet.Range["A4:C4"].Style.KnownColor = ExcelColors.LightOrange;  
  6. sheet.Range["A5:C5"].Style.KnownColor = ExcelColors.LightTurquoise;  
  7. //Border  
  8. sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeTop].Color = Color.FromA#000080;  
  9. sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;  
  10. sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeBottom].Color = Color.FromA#000080;  
  11. sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;  
  12. sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeLeft].Color = Color.FromA#000080;  
  13. sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;  
  14. sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeRight].Color = Color.FromA#000080;  
  15. sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;  
  16. 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.
  1. private void button2_Click(object sender, EventArgs e) {  
  2.     Workbook workbook = new Workbook();  
  3.     workbook.LoadFromFile("D:\\Sample.xlsx", ExcelVersion.Version2010);  
  4.     Worksheet sheet = workbook.Worksheets[0];  
  5.     Image[] imgs = workbook.SaveChartAsImage(sheet);  
  6.     for (int i = 0; i < imgs.Length; i++) {  
  7.         imgs[i].Save(string.Format("img-{0}.png", i), System.Drawing.Imaging.ImageFormat.Png);  
  8.     }  
  9. }  

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

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

Conclusion

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

Kindest Regards,
Sibeesh Venu

Up Next
    Ebook Download
    View all
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs