Chart Data Labels in Excel document

26 Aug 202413 minutes to read

Data Labels on a chart make it easier to understand. They show important information about the lines or points on the chart. Using XlsIO, you can customize the data labels in the chart.

Add

The following code snippet illustrates how to add the data label.

C# [Cross-platform]

//Add the datalabel
chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.IsValue = true;

C# [Windows-specific]

//Add the datalabel
chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.IsValue = true;

VB.NET [Windows-specific]

'Add the datalabel
chart.Series(0).DataPoints.DefaultDataPoint.DataLabels.IsValue = True

Add from the range of cells

The following code snippet illustrates how to add the data label from the range of cells.

C# [Cross-platform]

//Add the datalabel from the range of cells
chart.Series[1].DataPoints.DefaultDataPoint.DataLabels.ValueFromCellsRange = worksheet["I1:I5"];
chart.Series[1].DataPoints.DefaultDataPoint.DataLabels.IsValueFromCells = true;

C# [Windows-specific]

//Add the datalabel from the range of cells
chart.Series[1].DataPoints.DefaultDataPoint.DataLabels.ValueFromCellsRange = worksheet["I1:I5"];
chart.Series[1].DataPoints.DefaultDataPoint.DataLabels.IsValueFromCells = true;

VB.NET [Windows-specific]

'Add the datalabel from the range of cells
chart.Series(1).DataPoints.DefaultDataPoint.DataLabels.ValueFromCellsRange = worksheet("I1:I5")
chart.Series(1).DataPoints.DefaultDataPoint.DataLabels.IsValueFromCells = true

Formatting

Color

The following code snippet illustrates how to format the color of the data label.

C# [Cross-platform]

//Set the color
chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.Color = ExcelKnownColors.Blue;

C# [Windows-specific]

//Set the color
chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.Color = ExcelKnownColors.Blue;

VB.NET [Windows-specific]

'Set the color
chart.Series(0).DataPoints.DefaultDataPoint.DataLabels.Color = ExcelKnownColors.Blue

Font

The following code snippet illustrates how to format the font of the data label.

C# [Cross-platform]

//Set the font
chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.Size = 10;
chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.FontName = "calibri";
chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.Bold = true;

C# [Windows-specific]

//Set the font
chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.Size = 10;
chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.FontName = "calibri";
chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.Bold = true;

VB.NET [Windows-specific]

'Set the font
chart.Series(0).DataPoints.DefaultDataPoint.DataLabels.Size = 10;
chart.Series(0).DataPoints.DefaultDataPoint.DataLabels.FontName = "calibri";
chart.Series(0).DataPoints.DefaultDataPoint.DataLabels.Bold = true;

Number Format

The following code snippet illustrates how to set the number format in the data label.

C# [Cross-platform]

//Set the number format
IChartDataLabels dataLabel = chart.Series[0].DataPoints.DefaultDataPoint.DataLabels;
(dataLabel as ChartDataLabelsImpl).NumberFormat = "#,##0.00";

C# [Windows-specific]

//Set the number format
IChartDataLabels dataLabel = chart.Series[0].DataPoints.DefaultDataPoint.DataLabels;
(dataLabel as ChartDataLabelsImpl).NumberFormat = "#,##0.00";

VB.NET [Windows-specific]

'Set the number format
Dim dataLabel As IChartDataLabels = chart.Series(0).DataPoints.DefaultDataPoint.DataLabels
CType(dataLabel, ChartDataLabelsImpl).NumberFormat = "#,##0.00"

Set Position

The following code snippet illustrates how to set the position of the data label.

C# [Cross-platform]

//Set the position
chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.Position = ExcelDataLabelPosition.Outside;

C# [Windows-specific]

//Set the position
chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.Position = ExcelDataLabelPosition.Outside;

VB.NET [Windows-specific]

'Set the position
chart.Series(0).DataPoints.DefaultDataPoint.DataLabels.Position = ExcelDataLabelPosition.Outside

The complete code snippet illustrating the above options is shown below.

C# [Cross-platform]

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    FileStream inputStream = new FileStream("../../../Data/InputTemplate.xlsx", FileMode.Open, FileAccess.Read);
    IWorkbook workbook = application.Workbooks.Open(inputStream);
    IWorksheet worksheet = workbook.Worksheets[0];
    IChartShape chart = worksheet.Charts[0];

    //Add the datalabel
    chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.IsValue = true;

    //Add the datalabel from the range of cells
    chart.Series[1].DataPoints.DefaultDataPoint.DataLabels.ValueFromCellsRange = worksheet["I1:I5"];
    chart.Series[1].DataPoints.DefaultDataPoint.DataLabels.IsValueFromCells = true;

    //Set the color
    chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.Color = ExcelKnownColors.Blue;
    chart.Series[1].DataPoints.DefaultDataPoint.DataLabels.Color = ExcelKnownColors.Black;

    //Set the font
    chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.Size = 10;
    chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.FontName = "calibri";
    chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.Bold = true;

    chart.Series[1].DataPoints.DefaultDataPoint.DataLabels.Size = 10;
    chart.Series[1].DataPoints.DefaultDataPoint.DataLabels.FontName = "calibri";
    chart.Series[1].DataPoints.DefaultDataPoint.DataLabels.Bold = true;

    //Set the number format
    IChartDataLabels dataLabel = chart.Series[0].DataPoints.DefaultDataPoint.DataLabels;
    (dataLabel as ChartDataLabelsImpl).NumberFormat = "#,##0.00";

    //Set the position
    chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.Position = ExcelDataLabelPosition.Outside;
    chart.Series[1].DataPoints.DefaultDataPoint.DataLabels.Position = ExcelDataLabelPosition.Outside;

    //Saving the workbook as stream
    FileStream outputStream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
    workbook.SaveAs(outputStream);

    //Dispose streams
    outputStream.Dispose();
    inputStream.Dispose();
}

C# [Windows-specific]

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Open("InputTemplate.xlsx");
    IWorksheet worksheet = workbook.Worksheets[0];
    IChartShape chart = worksheet.Charts[0];

    //Add the datalabel
    chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.IsValue = true;

    //Add the datalabel from the range of cells
    chart.Series[1].DataPoints.DefaultDataPoint.DataLabels.ValueFromCellsRange = worksheet["I1:I5"];
    chart.Series[1].DataPoints.DefaultDataPoint.DataLabels.IsValueFromCells = true;

    //Set the color
    chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.Color = ExcelKnownColors.Blue;
    chart.Series[1].DataPoints.DefaultDataPoint.DataLabels.Color = ExcelKnownColors.Black;

    //Set the font
    chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.Size = 10;
    chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.FontName = "calibri";
    chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.Bold = true;

    chart.Series[1].DataPoints.DefaultDataPoint.DataLabels.Size = 10;
    chart.Series[1].DataPoints.DefaultDataPoint.DataLabels.FontName = "calibri";
    chart.Series[1].DataPoints.DefaultDataPoint.DataLabels.Bold = true;

    //Set the number format
    IChartDataLabels dataLabel = chart.Series[0].DataPoints.DefaultDataPoint.DataLabels;
    (dataLabel as ChartDataLabelsImpl).NumberFormat = "#,##0.00";

    //Set the position
    chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.Position = ExcelDataLabelPosition.Outside;
    chart.Series[1].DataPoints.DefaultDataPoint.DataLabels.Position = ExcelDataLabelPosition.Outside;

    //Saving the workbook
    workbook.SaveAs("Output.xlsx");
}

VB.NET [Windows-specific]

Using excelEngine As New ExcelEngine()
    Dim application As IApplication = excelEngine.Excel
    application.DefaultVersion = ExcelVersion.Xlsx
    Dim workbook As IWorkbook = application.Workbooks.Open("InputTemplate.xlsx")
    Dim worksheet As IWorksheet = workbook.Worksheets(0)
    Dim chart As IChartShape = worksheet.Charts(0)

    'Add the datalabel
    chart.Series(0).DataPoints.DefaultDataPoint.DataLabels.IsValue = True

    'Add the datalabel from the range of cells
    chart.Series(1).DataPoints.DefaultDataPoint.DataLabels.ValueFromCellsRange = worksheet("I1:I5")
    chart.Series(1).DataPoints.DefaultDataPoint.DataLabels.IsValueFromCells = True

    'Set the color
    chart.Series(0).DataPoints.DefaultDataPoint.DataLabels.Color = ExcelKnownColors.Blue
    chart.Series(1).DataPoints.DefaultDataPoint.DataLabels.Color = ExcelKnownColors.Black

    'Set the font
    chart.Series(0).DataPoints.DefaultDataPoint.DataLabels.Size = 10
    chart.Series(0).DataPoints.DefaultDataPoint.DataLabels.FontName = "calibri"
    chart.Series(0).DataPoints.DefaultDataPoint.DataLabels.Bold = True

    chart.Series(1).DataPoints.DefaultDataPoint.DataLabels.Size = 10
    chart.Series(1).DataPoints.DefaultDataPoint.DataLabels.FontName = "calibri"
    chart.Series(1).DataPoints.DefaultDataPoint.DataLabels.Bold = True

    'Set the number format
    Dim dataLabel As IChartDataLabels = chart.Series(0).DataPoints.DefaultDataPoint.DataLabels
    CType(dataLabel, ChartDataLabelsImpl).NumberFormat = "#,##0.00"

    'Set the position
    chart.Series(0).DataPoints.DefaultDataPoint.DataLabels.Position = ExcelDataLabelPosition.Outside
    chart.Series(1).DataPoints.DefaultDataPoint.DataLabels.Position = ExcelDataLabelPosition.Outside

    'Saving the workbook
    workbook.SaveAs("Output.xlsx")
End Using

A complete working example for the chart data label in C# is present on this GitHub page.