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 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 {
-
- Workbook workbook = new Workbook();
-
- workbook.CreateEmptySheets(1);
- Worksheet sheet = workbook.Worksheets[0];
-
- sheet.Name = "Chart data";
-
- sheet.GridLinesVisible = false;
-
- Chart chart = sheet.Charts.Add(ExcelChartType.Pie3D);
-
- chart.DataRange = sheet.Range["B2:B5"];
- chart.SeriesDataFromRange = false;
-
- chart.LeftColumn = 1;
- chart.TopRow = 6;
- chart.RightColumn = 9;
- chart.BottomRow = 25;
-
- chart.ChartTitle = "Sales by year";
- chart.ChartTitleArea.IsBold = true;
- chart.ChartTitleArea.Size = 12;
-
- Spire.Xls.Charts.ChartSerie cs = chart.Series[0];
-
- cs.CategoryLabels = sheet.Range["A2:A5"];
-
- cs.Values = sheet.Range["B2:B5"];
-
- cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
-
- 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";
-
- 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;
-
- 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;
-
- 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;
-
- sheet.Range["B2:C5"].Style.NumberFormat = "\"$\"#,##0";
- chart.PlotArea.Fill.Visible = false;
-
- workbook.SaveToFile("Sample.xls");
-
- System.Diagnostics.Process.Start("Sample.xls");
- } catch (Exception) {
- throw;
- }
- }
VB.NET Code
-
- Dim workbook As New Workbook()
- workbook.LoadFromFile("E:\Sample.xlsx")
- Dim sheet As Worksheet = workbook.Worksheets(0)
-
- Dim chart As Chart = sheet.Charts.Add(ExcelChartType.ColumnClustered)
- chart.DataRange = sheet.Range("D1:E17")
- chart.SeriesDataFromRange = False
-
- chart.LeftColumn = 1
- chart.TopRow = 19
- chart.RightColumn = 8
- chart.BottomRow = 33
-
- chart.ChartArea.Border.Weight = ChartLineWeightType.Medium
- chart.ChartArea.Border.Color = Color.SandyBrown
-
- chart.ChartTitle = "Parts Sales Info"
- chart.ChartTitleArea.Font.FontName = "Calibri"
- chart.ChartTitleArea.Font.Size = 13
- chart.ChartTitleArea.Font.IsBold = True
-
- 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.Position = LegendPositionType.Right
-
- 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 {
-
- Workbook workbook = new Workbook();
- workbook.LoadFromFile(@
- "D:\Sample.xlsx");
- Worksheet sheet = workbook.Worksheets[0];
-
- Chart chart = sheet.Charts.Add(ExcelChartType.ColumnClustered);
- chart.DataRange = sheet.Range["D1:E17"];
- chart.SeriesDataFromRange = false;
-
- chart.LeftColumn = 1;
- chart.TopRow = 19;
- chart.RightColumn = 8;
- chart.BottomRow = 33;
-
- chart.ChartArea.Border.Weight = ChartLineWeightType.Medium;
- chart.ChartArea.Border.Color = Color.SandyBrown;
-
- chart.ChartTitle = "Parts Sales Info";
- chart.ChartTitleArea.Font.FontName = "Calibri";
- chart.ChartTitleArea.Font.Size = 13;
- chart.ChartTitleArea.Font.IsBold = true;
-
- 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.Position = LegendPositionType.Right;
-
- workbook.SaveToFile("ExcelColumnChart.xlsx", ExcelVersion.Version2010);
- System.Diagnostics.Process.Start("ExcelColumnChart.xlsx");
- } catch (Exception) {
- throw;
- }
- }
VB.NET Code
-
- Dim workbook As New Workbook()
- workbook.LoadFromFile("E:\Sample.xlsx")
- Dim sheet As Worksheet = workbook.Worksheets(0)
-
- Dim chart As Chart = sheet.Charts.Add(ExcelChartType.ColumnClustered)
- chart.DataRange = sheet.Range("D1:E17")
- chart.SeriesDataFromRange = False
-
- chart.LeftColumn = 1
- chart.TopRow = 19
- chart.RightColumn = 8
- chart.BottomRow = 33
-
- chart.ChartArea.Border.Weight = ChartLineWeightType.Medium
- chart.ChartArea.Border.Color = Color.SandyBrown
-
- chart.ChartTitle = "Parts Sales Info"
- chart.ChartTitleArea.Font.FontName = "Calibri"
- chart.ChartTitleArea.Font.Size = 13
- chart.ChartTitleArea.Font.IsBold = True
-
- 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.Position = LegendPositionType.Right
-
- 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();
-
- workbook.CreateEmptySheets(1);
- Worksheet sheet = workbook.Worksheets[0];
- sheet.Name = "Chart data";
- sheet.GridLinesVisible = false;
-
- CreateChartData(sheet);
-
- Chart chart = sheet.Charts.Add();
-
- chart.DataRange = sheet.Range["A1:C5"];
- chart.SeriesDataFromRange = false;
-
- chart.LeftColumn = 1;
- chart.TopRow = 6;
- chart.RightColumn = 11;
- chart.BottomRow = 29;
- chart.ChartType = ExcelChartType.Bar3DClustered;
-
- 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) {
-
- 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";
-
- 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;
-
- 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;
-
- 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;
-
- 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
Excel Bar Chart
As you can see it is very easy to provide styles and border to a chart.
-
- 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;
-
- 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 ImagesCreate 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