News Category

C#/VB.NET: Create Bar Chart in Excel

2022-05-12 03:51:00 Written by  support iceblue
Rate this item
(0 votes)

A bar chart in Excel is a data visualization tool that presents data using horizontal bars. The length of each bar in the chart is proportional to the value it represents. Using a bar chart, you can easily compare values across two or more categories. In this article, you will learn how to create bar 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 Bar Chart in Excel in C# and VB.NET

The following are the main steps to create a bar chart:

  • Create an instance of Workbook class.
  • Get the first worksheet by its index (zero-based) though Workbook.Worksheets[sheetIndex] property.
  • Add some data to the worksheet.
  • Add a clustered bar chart to the worksheet using Worksheet.Charts.Add(ExcelChartType.BarClustered) method.
  • Set data range for the chart through Chart.DataRange property.
  • Set position, title, category axis title and value axis title for the chart.
  • Loop through the data series of the chart, show data labels for the data points of each data series by setting the ChartSerie.DataPoints.DefaultDataPoint.DataLabels.HasValue property as true.
  • Set the position of chart legend through Chart.Legend.Position property.
  • Save the result file using Workbook.SaveToFile() method.
  • C#
  • VB.NET
using Spire.Xls;
using Spire.Xls.Charts;
using System.Drawing;

namespace CreateBarChart
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook instance
            Workbook workbook = new Workbook();

            //Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];
            //Set sheet name
            sheet.Name = "Bar Chart";
            //Hide gridlines
            sheet.GridLinesVisible = false;

            //Add data to the the worksheet
            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;

            //Set cell styles
            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;

            //Set cell borders
            sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeTop].Color = Color.FromArgb(0, 0, 128);
            sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
            sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeBottom].Color = Color.FromArgb(0, 0, 128);
            sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
            sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeLeft].Color = Color.FromArgb(0, 0, 128);
            sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
            sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeRight].Color = Color.FromArgb(0, 0, 128);
            sheet.Range["A1:C5"].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;

            //Set number format
            sheet.Range["B2:C5"].Style.NumberFormat = "\"$\"#,##0";

            //Add a clustered bar chart to the worksheet
            Chart chart = sheet.Charts.Add(ExcelChartType.BarClustered);

            //Set data range for the chart
            chart.DataRange = sheet.Range["A1:C5"];
            chart.SeriesDataFromRange = false;

            //Set position of the chart
            chart.LeftColumn = 1;
            chart.TopRow = 6;
            chart.RightColumn = 11;
            chart.BottomRow = 29;            

            //Set and format chart title
            chart.ChartTitle = "Sales Report";
            chart.ChartTitleArea.IsBold = true;
            chart.ChartTitleArea.Size = 12;

            //Set and format category axis title
            chart.PrimaryCategoryAxis.Title = "Country";
            chart.PrimaryCategoryAxis.Font.IsBold = true;
            chart.PrimaryCategoryAxis.TitleArea.IsBold = true;
            chart.PrimaryCategoryAxis.TitleArea.TextRotationAngle = 90;

            //Set and format value axis title
            chart.PrimaryValueAxis.Title = "Sales(in USD)";
            chart.PrimaryValueAxis.HasMajorGridLines = false;
            chart.PrimaryValueAxis.MinValue = 1000;
            chart.PrimaryValueAxis.TitleArea.IsBold = true;

            //Show data labels for data points 
            foreach (ChartSerie cs in chart.Series)
            {
                cs.Format.Options.IsVaryColor = true;
                cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
            }

            //Set position of chart legend
            chart.Legend.Position = LegendPositionType.Top;

            //Save the result file
            workbook.SaveToFile("CreateBarChart.xlsx", ExcelVersion.Version2016);
        }    
    }
}

C#/VB.NET: Create Bar Chart in Excel

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.

Additional Info

  • tutorial_title:
Last modified on Thursday, 12 May 2022 08:50