Custom data labels using values from cells in C#

Excel version 2013 added a fantastic feature in Chart Data Label option that you can custom data labels from a column/row of data. The chart below uses labels from the data in cells C2: C5 next to the plotted values. This article will present how to add labels to data points using the values from cells in C#.

Code Snippets:

Step 1: Initialize a new instance of Workbook class and set the Excel version as 2013.

Workbook wb = new Workbook();
wb.Version = ExcelVersion.Version2013;

Step 2: Get the first sheet from workbook.

Worksheet ws = wb.Worksheets[0];

Step 3: Insert data.

ws.Range["A2"].Text = "Product 1";
ws.Range["A3"].Text = "Product 2";
ws.Range["A4"].Text = "Product 3";
ws.Range["A5"].Text = "Product 4";
ws.Range["B1"].Text = "Sales";
ws.Range["B1"].Style.Font.IsBold = true;
ws.Range["B2"].NumberValue = 251;
ws.Range["B3"].NumberValue = 515;
ws.Range["B4"].NumberValue = 454;
ws.Range["B5"].NumberValue = 874;
ws.Range["C1"].Text = "+/-\nPrevious\nPeriod";
ws.Range["C1"].Style.Font.IsBold = true;
ws.Range["C2"].NumberValue = -120;
ws.Range["C3"].NumberValue = 31;
ws.Range["C4"].NumberValue = -76;
ws.Range["C5"].NumberValue = 201;
ws.SetRowHeight(1, 40);

Step 4: Insert a Clustered Column Chart in Excel based on the data range from A1:B5.

Chart chart = ws.Charts.Add(ExcelChartType.ColumnClustered);
chart.DataRange = ws.Range["A1:B5"];
chart.SeriesDataFromRange = false;
chart.PrimaryValueAxis.HasMajorGridLines = false;

Step 5: Set chart position.

chart.LeftColumn = 5;
chart.TopRow = 2;
chart.RightColumn = 13;
chart.BottomRow = 22;

Step 6: Add labels to data points using the values from cell range C2:C5.

chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.ValueFromCell = ws.Range["C2:C5"];

Step 7: Save and launch the file.

wb.SaveToFile("result.xlsx",ExcelVersion.Version2010);
System.Diagnostics.Process.Start("result.xlsx");

Full Code:

using Spire.Xls;

namespace CustomLabels
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook wb = new Workbook();
            wb.Version = ExcelVersion.Version2013;
            Worksheet ws = wb.Worksheets[0];

            ws.Range["A2"].Text = "Product 1";
            ws.Range["A3"].Text = "Product 2";
            ws.Range["A4"].Text = "Product 3";
            ws.Range["A5"].Text = "Product 4";
            ws.Range["B1"].Text = "Sales";
            ws.Range["B1"].Style.Font.IsBold = true;
            ws.Range["B2"].NumberValue = 251;
            ws.Range["B3"].NumberValue = 515;
            ws.Range["B4"].NumberValue = 454;
            ws.Range["B5"].NumberValue = 874;
            ws.Range["C1"].Text = "+/-\nPrevious\nPeriod";
            ws.Range["C1"].Style.Font.IsBold = true;
            ws.Range["C2"].NumberValue = -120;
            ws.Range["C3"].NumberValue = 31;
            ws.Range["C4"].NumberValue = -76;
            ws.Range["C5"].NumberValue = 201;
            ws.SetRowHeight(1, 40);

            Chart chart = ws.Charts.Add(ExcelChartType.ColumnClustered);
            chart.DataRange = ws.Range["A1:B5"];
            chart.SeriesDataFromRange = false;
            chart.PrimaryValueAxis.HasMajorGridLines = false;

            chart.LeftColumn = 5;
            chart.TopRow = 2;
            chart.RightColumn = 13;
            chart.BottomRow = 22;

            chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.ValueFromCell = ws.Range["C2:C5"];

            wb.SaveToFile("result.xlsx",ExcelVersion.Version2010);
            System.Diagnostics.Process.Start("result.xlsx");
        }
    }
}

Custom data labels using values from cells in C#

See Also