Create Excel Line Chart with C#, VB.NET

Submit Blog |

Blogs Home |

Ads

Line chart is a kind of chart used to display data information by a series of scatter data points connected by straight line segments. Line chart is the best choice for visualizing a trend in data over a period of time. People can apply line chart to view how things changes and make clear comparison for analyzing. So line chart is widely used in every field. This post will introduce a method to draw Excel line chart with C#, VB.NET.

How to draw Excel line chart with C#, VB.NET

In the method, I use an Excel component Spire.XLS to help me finish this task. Spire.XLS supports C#, VB.NET, ASP.NET, ASP.NET MVC and Silverlight. Please follow the below procedure step by step.

Spire.XLS Download Zip

Step1. Create a new project.

1.      Create a new project in Windows Forms Application.

2.      Set the project Target framework property to be .NET Framework 2 or above.

3.      Add a button and a checkBox in Form1 and set the button text property to be "Run".

Step2. Add reference.

1.      Add Spire.XLS.dll as reference in Project.

2.      Add below namespaces at the top of the method.

C#

using Spire.Xls;
using Spire.Xls.Charts;

VB.NET

Imports Spire.Xls
Imports Spire.Xls.Charts

Step3. Draw Excel line chart.

1. Create a new Excel workbook.

C#

            Workbook workbook = new Workbook();
            //Initailize worksheet
            workbook.CreateEmptySheets(1);
            Worksheet sheet = workbook.Worksheets[0];
            sheet.Name = "Chart data";
            sheet.GridLinesVisible = false;

VB.NET

            Dim workbook As New Workbook()
           'Initailize worksheet
            workbook.CreateEmptySheets(1)
            Dim sheet As Worksheet = workbook.Worksheets(0)
            sheet.Name = "Chart data"
            sheet.GridLinesVisible = False

2. Add a new chart worksheet to workbook

C# Code:

            //Add a new  chart worsheet to workbook
            Chart chart = sheet.Charts.Add();
            if (checkBox1.Checked)
            {
                chart.ChartType = ExcelChartType.Line3D;
            }
            else
            {
                chart.ChartType = ExcelChartType.Line;
            }

VB.NET Code:

          'Add a new  chart worsheet to workbook
           Dim chart As Chart = sheet.Charts.Add()
           If checkBox1.Checked Then
                  chart.ChartType = ExcelChartType.Line3D
           Else
                 chart.ChartType = ExcelChartType.Line
           End If

3. Draw chart data

C# Code:

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 = 3300;
            sheet.Range["B3"].NumberValue = 2300;
            sheet.Range["B4"].NumberValue = 4500;
            sheet.Range["B5"].NumberValue = 6700;
            //Jul
            sheet.Range["C1"].Value = "Jul";
            sheet.Range["C2"].NumberValue = 7500;
            sheet.Range["C3"].NumberValue = 2900;
            sheet.Range["C4"].NumberValue = 2300;
            sheet.Range["C5"].NumberValue = 4200;
              //Aug
            sheet.Range["D1"].Value = "Aug";
            sheet.Range["D2"].NumberValue = 7700;
            sheet.Range["D3"].NumberValue = 6900;
            sheet.Range["D4"].NumberValue = 8400;
            sheet.Range["D5"].NumberValue = 4200;
             //Sep
            sheet.Range["E1"].Value = "Sep";
            sheet.Range["E2"].NumberValue = 8000;
            sheet.Range["E3"].NumberValue = 7200;
            sheet.Range["E4"].NumberValue = 8100;
            sheet.Range["E5"].NumberValue = 5600;             
              //Style
            sheet.Range["A1:E1"].Style.Font.IsBold = true;
            sheet.Range["A2:E2"].Style.KnownColor = ExcelColors.LightYellow;
            sheet.Range["A3:E3"].Style.KnownColor = ExcelColors.LightGreen1;
            sheet.Range["A4:E4"].Style.KnownColor = ExcelColors.LightOrange;
            sheet.Range["A5:E5"].Style.KnownColor = ExcelColors.LightTurquoise;
            //Border
            sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeTop].Color = Color.FromArgb(0, 0, 128);
            sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
            sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeBottom].Color = Color.FromArgb(0, 0, 128);
            sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
            sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeLeft].Color = Color.FromArgb(0, 0, 128);
            sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
            sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeRight].Color = Color.FromArgb(0, 0, 128);
            sheet.Range["A1:E5"].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
            sheet.Range["B2:D5"].Style.NumberFormat = "\"$\"#,##0";
        }

VB.NET Code:


Private Sub CreateChartData(sheet As Worksheet)
         '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 = 3300
         sheet.Range("B3").NumberValue = 2300
         sheet.Range("B4").NumberValue = 4500
         sheet.Range("B5").NumberValue = 6700
         'Jul
         sheet.Range("C1").Value = "Jul"
         sheet.Range("C2").NumberValue = 7500
         sheet.Range("C3").NumberValue = 2900
         sheet.Range("C4").NumberValue = 2300
         sheet.Range("C5").NumberValue = 4200
         'Aug
         sheet.Range("D1").Value = "Aug"
         sheet.Range("D2").NumberValue = 7700
         sheet.Range("D3").NumberValue = 6900
         sheet.Range("D4").NumberValue = 8400
         sheet.Range("D5").NumberValue = 4200
         'Sep
         sheet.Range("E1").Value = "Sep"
         sheet.Range("E2").NumberValue = 8000
         sheet.Range("E3").NumberValue = 7200
         sheet.Range("E4").NumberValue = 8100
         sheet.Range("E5").NumberValue = 5600
         'Style
         sheet.Range("A1:E1").Style.Font.IsBold = True
         sheet.Range("A2:E2").Style.KnownColor = ExcelColors.LightYellow
         sheet.Range("A3:E3").Style.KnownColor = ExcelColors.LightGreen1
         sheet.Range("A4:E4").Style.KnownColor = ExcelColors.LightOrange
         sheet.Range("A5:E5").Style.KnownColor = ExcelColors.LightTurquoise
         'Border                   
         sheet.Range("A1:E5").Style.Borders(BordersLineType.EdgeTop).Color = Color.FromArgb(0, 0, 128)          
         sheet.Range("A1:E5").Style.Borders(BordersLineType.EdgeTop).LineStyle = LineStyleType.Thin
         sheet.Range("A1:E5").Style.Borders(BordersLineType.EdgeBottom).Color = Color.FromArgb(0, 0, 128)           
         sheet.Range("A1:E5").Style.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Thin
         sheet.Range("A1:E5").Style.Borders(BordersLineType.EdgeLeft).Color = Color.FromArgb(0, 0, 128)          
         sheet.Range("A1:E5").Style.Borders(BordersLineType.EdgeLeft).LineStyle = LineStyleType.Thin
         sheet.Range("A1:E5").Style.Borders(BordersLineType.EdgeRight).Color = Color.FromArgb(0, 0, 128)
         sheet.Range("A1:E5").Style.Borders(BordersLineType.EdgeRight).LineStyle = LineStyleType.Thin
         sheet.Range("B2:D5").Style.NumberFormat = """$""#,##0"
End Sub

4. Edit chart include chart data region, chart position and chart title.

C# Code:

            //Set region of chart data
            chart.DataRange = sheet.Range["A1:E5"];
            //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 = "Month";
            chart.PrimaryCategoryAxis.Font.IsBold = true;
            chart.PrimaryCategoryAxis.TitleArea.IsBold = true;
            chart.PrimaryValueAxis.Title = "Sales(in Dollars)";
            chart.PrimaryValueAxis.HasMajorGridLines = false;
            chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;
            chart.PrimaryValueAxis.MinValue = 1000;
            chart.PrimaryValueAxis.TitleArea.IsBold = true;
            foreach (Spire.Xls.Charts.ChartSerie cs in chart.Series)
            {
                cs.Format.Options.IsVaryColor = true;
                cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
                if (!checkBox1.Checked)
                    cs.DataFormat.MarkerStyle = ChartMarkerType.Circle;
            }
            chart.PlotArea.Fill.Visible = false;
            chart.Legend.Position = LegendPositionType.Top;
            workbook.SaveToFile("Sample.xls");
            ExcelDocViewer(workbook.FileName);

VB.NET Code:

         'Set region of chart data
         chart.DataRange = sheet.Range("A1:E5")
          '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 = "Month"
         chart.PrimaryCategoryAxis.Font.IsBold = True
         chart.PrimaryCategoryAxis.TitleArea.IsBold = True
         chart.PrimaryValueAxis.Title = "Sales(in Dollars)"
         chart.PrimaryValueAxis.HasMajorGridLines = False
         chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90
         chart.PrimaryValueAxis.MinValue = 1000
         chart.PrimaryValueAxis.TitleArea.IsBold = True
         For Each cs As Spire.Xls.Charts.ChartSerie In chart.Series
                 cs.Format.Options.IsVaryColor = True
                                   cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = True
                 If Not checkBox1.Checked Then
                          cs.DataFormat.MarkerStyle = ChartMarkerType.Circle
                 End If
         Next
         chart.PlotArea.Fill.Visible = False
         chart.Legend.Position = LegendPositionType.Top
         workbook.SaveToFile("Sample.xls")
         ExcelDocViewer(workbook.FileName)

Step4. Launch the project

C# Code:

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

VB.NET Code:

         Private Sub ExcelDocViewer(fileName As String)
                 Try
                          System.Diagnostics.Process.Start(fileName)
                 Catch
                 End Try
         End Sub

Preview

Code Source: http://www.e-iceblue.com/Knowledgebase/Spire.XLS/Program-Guide.html

Blog Source: http://everlasting129.weebly.com

Found interesting? Add this to:

| More | Bookmark It

More Blogs from Lacy

More ...

About Keating Arly

Experience:	1 year(s)
Home page:	http://www.e-iceblue.com
Member since:	Tuesday, March 27, 2012
Level:	Starter
Status:	[Member]
Biography:

>> Write Response - Respond to this post and get points

More Blogs

Submit Blog | More ...