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