Chart Data Labels in Excel document

14 Oct 202412 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(Path.GetFullPath(@"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 position
	chart.Series[0].DataPoints.DefaultDataPoint.DataLabels.Position = ExcelDataLabelPosition.Outside;
	chart.Series[1].DataPoints.DefaultDataPoint.DataLabels.Position = ExcelDataLabelPosition.Outside;

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

	//Saving the workbook as stream
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/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.