C# – Create Bar Chart in Excel

Excel provides a function to present data with image, which is called charts. Chart can show data information more intuitively. And readers can learn data more clearly by chart.

There are many types of chart offered by MS Excel. In this post, I want to talk about one kind of requently used, bar chart. Bar chart is to show data in row or column with bars. Generally speaking, it is focuses on comparison with each projects.

How to create bar chart in Excel with C#?

Steps:

1. Create a new project and name it ExcelBarChart.

2. Add reference for project.

Because I use Spire.XLS to realize this function, so firstly, I need to add dll file of Spire.XLS as reference.

Then, add system.drawing as reference for setting chart format.

3. Use the following code.

using System;

using System.Data;

using System.Data.OleDb;

using System.Drawing;

 

using Spire.Xls;

using Spire.Xls.Charts;

 

namespace ExcelBarChart

{

    class Program

    {

        static void Main(string[] args)

        {

            Workbook workbook = new Workbook();

           

            //Initialize 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 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");

            System.Diagnostics.Process.Start(workbook.FileName);

        }

 

        static 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;

 

            //Jul

            sheet.Range["C1"].Value = "Jul";

            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.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;

 

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

        }

    }

}

After running, we can get the chart as following.

 

Advertisements

Author: janewdaisy

.NET Program Beginner. Share methods about how to use C#/VB.NET to export data, operate Word, Excel, PDF and other useful skills.

3 thoughts on “C# – Create Bar Chart in Excel”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s