Articles in this section
Category / Section

Add Excel chart data labels with formula in C#, VB.NET using XlsIO

3 mins read

Syncfusion Excel (XlsIO) library is a .NET Excel library used to create, read, and edit Excel documents. Also, converts Excel documents to PDF files. This article explains how to add Excel chart data labels with formula in C#, VB.NET using XlsIO.

Steps to add Excel chart with formula, programmatically:

Step 1: Create a new C# console application project.

Create C# console application

Create a new C# console application

Step 2: Install the Syncfusion.XlsIO.WinForms NuGet package (versions after 18.2.0.44) as reference to your .NET Framework application from NuGet.org.

Install XlsIO NuGet package

Install XlsIO NuGet package

Step 3: Include the following namespaces in the Program.cs file.

C#

using Syncfusion.XlsIO;

 

VB.NET

Imports Syncfusion.XlsIO

 

Step 4: Use the following code snippet to set a formula as data label text in Excel using C# and VB.NET.

C#

//Create an instance of ExcelEngine
using (ExcelEngine excelEngine = new ExcelEngine())
{
    //Instantiate the application object
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Excel2016;
 
    //Create a workbook
    IWorkbook workbook = application.Workbooks.Create(1);
    IWorksheet sheet = workbook.Worksheets[0];
 
    //Enter values to the cells from A1 to C6
    sheet.Range["A1"].Text = "Month";
    sheet.Range["B1"].Text = "Product A";
    sheet.Range["C1"].Text = "Product B";
 
    sheet.Range["A2"].Text = "Jan";
    sheet.Range["A3"].Text = "Feb";
    sheet.Range["A4"].Text = "Mar";
    sheet.Range["A5"].Text = "Apr";
    sheet.Range["A6"].Text = "May";
 
    sheet.Range["B2"].Number = 111;
    sheet.Range["B3"].Number = 361;
    sheet.Range["B4"].Number = 336;
    sheet.Range["B5"].Number = 40;
    sheet.Range["B6"].Number = 219;
 
    sheet.Range["C2"].Number = 182;
    sheet.Range["C3"].Number = 283;
    sheet.Range["C4"].Number = 406;
    sheet.Range["C5"].Number = 449;
    sheet.Range["C6"].Number = 337;
 
    //Create a chart
    IChartShape chart = sheet.Charts.Add();
    chart.ChartType = ExcelChartType.Line;
    chart.DataRange = sheet.Range["A1:C6"];
 
    //Set formula as data label text
    chart.Series[0].DataPoints[0].DataLabels.IsFormula = true;
    chart.Series[0].DataPoints[0].DataLabels.Text = "='Sheet1'!$A$2";
 
    //Save the workbook
    workbook.SaveAs("Output.xlsx");
    workbook.Close();
}

 

VB.NET

'Create an instance of ExcelEngine
Using excelEngine As ExcelEngine = New ExcelEngine()
 
    'Instantiate the application object
    Dim application As IApplication = excelEngine.Excel
    application.DefaultVersion = ExcelVersion.Excel2016
 
    'Create a workbook
    Dim workbook As IWorkbook = application.Workbooks.Create(1)
    Dim sheet As IWorksheet = workbook.Worksheets(0)
 
    'Enter values to the cells from A1 to C6
    sheet.Range("A1").Text = "Month"
    sheet.Range("B1").Text = "Product A"
    sheet.Range("C1").Text = "Product B"
 
    sheet.Range("A2").Text = "Jan"
    sheet.Range("A3").Text = "Feb"
    sheet.Range("A4").Text = "Mar"
    sheet.Range("A5").Text = "Apr"
    sheet.Range("A6").Text = "May"
 
    sheet.Range("B2").Number = 111
    sheet.Range("B3").Number = 361
    sheet.Range("B4").Number = 336
    sheet.Range("B5").Number = 40
    sheet.Range("B6").Number = 219
 
    sheet.Range("C2").Number = 182
    sheet.Range("C3").Number = 283
    sheet.Range("C4").Number = 406
    sheet.Range("C5").Number = 449
    sheet.Range("C6").Number = 337
 
    'Create a chart
    Dim chart As IChartShape = sheet.Charts.Add()
    chart.ChartType = ExcelChartType.Line
    chart.DataRange = sheet.Range("A1:C6")
 
    'Set formula as data label text
    chart.Series(0).DataPoints(0).DataLabels.IsFormula = True
    chart.Series(0).DataPoints(0).DataLabels.Text = "='Sheet1'!$A$2"
 
    'Save the workbook
    workbook.SaveAs("Output.xlsx")
    workbook.Close()
End Using

 

A complete working example to add Excel chart data labels with formula in C#, VB.NET using XlsIO can be downloaded from Add Excel chart data labels with formula.zip.

By executing the program, you will get the output as below.

Set a formula as data label text in Excel using C# and VB.NET

Output Excel document

Refer here to explore the rich set of Syncfusion Excel (XlsIO) library features.

See Also:

How to convert Excel to PDF in C#, VB.NET

How to Export and Save Excel Chart as Image

How to convert Syncfusion’s WinForms chart to Excel chart using XlsIO

How to copy and insert a chart in the same worksheet using C#,VB.NET?

How to create Excel line with markers chart in C#, VB.NET?

Format Excel chart data label in C#, VB.NET

How can I change the font color of the title in chart?

Add image to Excel header in C#, VB.NET

How to create Excel combination chart in C#, VB.NET?

Note:

Starting with v16.2.0.x, if you reference Syncfusion assemblies from trial setup or from the NuGet feed, include a license key in your projects. Refer the link to learn about generating and registering Syncfusion license key in your application to use the components without trial message.

 

Did you find this information helpful?
Yes
No
Help us improve this page
Please provide feedback or comments
Comments (0)
Please  to leave a comment
Access denied
Access denied