How to Create Sparkline Charts in Excel with C#

Sometimes you may want to analyze and view trends in your data without creating an entire chart. Sparklines are miniature charts that fit into a single cell. In this post, I am going to introduce how to programmatically create a sparkline chart in Excel from scratch.

This solution is also depending on Spire.XLS component. Do remember to add the Spire.Xls.dll as reference in your project to get following snippets executed.

using Spire.Xls;

namespace SparklineChart
{
    class Program
    {
        static void Main(string[] args)
        {
            //create an object of Workbook
            Workbook wb = new Workbook();
            wb.Version = ExcelVersion.Version2010;
            Worksheet ws = wb.Worksheets[0];

            //insert data
            ws.Range["A1"].Text = "Value01";
            ws.Range["B1"].Text = "Value02";
            ws.Range["C1"].Text = "Value03";
            ws.Range["D1"].Text = "Value04";
            ws.Range["E1"].Text = "Value05";
            ws.Range["A2"].NumberValue = 542.65;
            ws.Range["B2"].NumberValue = 682.65;
            ws.Range["C2"].NumberValue = 254.26;
            ws.Range["D2"].NumberValue = 953.25;
            ws.Range["E2"].NumberValue = 654.21;
            ws.Range["A3"].NumberValue = 782.45;
            ws.Range["B3"].NumberValue = 354.94;
            ws.Range["C3"].NumberValue = 987.24;
            ws.Range["D3"].NumberValue = 214.96;
            ws.Range["E3"].NumberValue = 825.14;
            ws.Range["A4"].NumberValue = 547.47;
            ws.Range["B4"].NumberValue = 244.94;
            ws.Range["C4"].NumberValue = 687.24;
            ws.Range["D4"].NumberValue = 614.96;
            ws.Range["E4"].NumberValue = 325.14;
            CellStyle cs = wb.Styles.Add("style");
            cs.Font.Size = 9;
            cs.Font.FontName = "Aril";
            cs.HorizontalAlignment = HorizontalAlignType.Justify;
            ws.Range[1, 1, 4, 5].Style = cs;

            //create sparkline chart based the data
            SparklineGroup sparklineGroup = ws.SparklineGroups.AddGroup(SparklineType.Column);
            sparklineGroup.SparklineColor = System.Drawing.Color.Blue;
            SparklineCollection sparklines = sparklineGroup.Add();
            sparklines.Add(ws["A2:E2"], ws["F2"]);
            sparklines.Add(ws["A3:E3"], ws["F3"]);
            sparklines.Add(ws["A4:E4"], ws["F4"]);
            ws.SetColumnWidth(6, 15f);

            //save to file
            wb.SaveToFile("SparklineChart-1.xlsx", ExcelVersion.Version2010);
            System.Diagnostics.Process.Start("SparklineChart-1.xlsx");
        }
    }
}

Output

2016-05-04_171651

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.

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