Manual Layout for Data Labels

Introduction

Occasionally you may wish to manually position a data label within a chart.

To do so in e.g. a Column chart you may use the ManualLayout property. In order to generate this chart:

We write the following Epplus code:

using (var package = new ExcelPackage("manualLayoutChart.xlsx"))
{
    var ws = package.Workbook.Worksheets.Add("ManualLayout");

    //Create some values
    ws.Cells["A1:A2"].Value = 5;
    ws.Cells["B1:B2"].Value = 10;

    //Create a column chart
    var sChart = ws.Drawings.AddBarChart("ColumnChart", eBarChartType.ColumnClustered);

    //Add series (clustered columns) to the chart. In this case 2 per series
    var s1 = sChart.Series.Add(ws.Cells["A1:A2"]);
    var s2 = sChart.Series.Add(ws.Cells["B1:B2"]);

    //Add a general datalabel
    var label = s1.DataLabel;
    label.ShowValue = true;

    //Add a specific datalabel to the first column in the cluster
    var dl = label.DataLabels.Add(0);

    //Offset the data label 10% of the charts width to the left
    //AKA Remove 10 from x coordinate
    dl.Layout.ManualLayout.Left = -10;

    //Offset the data label 10% of the charts height to the top
    //AKA remove 10 from y coordinate
    dl.Layout.ManualLayout.Top = -10;

    //Save the package at a path
    package.SaveAs(@"C:\temp\manualLayoutChart.xlsx");
}

Keep in mind that the properties in s1.DataLabel.Datalabels[0] now mostly decide that labels behaviour. It will not change if e.g. label.ShowCategory = true; is set. It will however change for all other datalabels in the series that have not been added to DataLabel.Datalabels. In this case the data label on the blue column to the right would still be affected.

ORIGIN: Note that the origin point of the data label can be set via the s1.DataLabel.Datalabels[0].Position enum. Only a limited number of options are currently available for Manual Layout.

Multiple Labels Building off of our example above to access the second blue column we can write:

var dl2 = label.DataLabels.Add(1);

Simply put you can consider s1.Datalabel.Datalabels an array of the data labels for the blue columns in this chart. And s2.Datalabel.Datalabels an array of the orange ones. In essence:

//You can think of Series[x] where X defines the 'color' or position within each cluster
//And .DataLabel.DataLabels[y] as the cluster number

//I want the data label in the first cluster on the first column:
var firstClusterFirstColum = sChart.Series[0].DataLabel.DataLabels[0];

firstClusterFirstColum.Fill.Color = Color.LightBlue;

//I want the data label in the second cluster on the second column
var secondClusterSecondColumn = sChart.Series[1].DataLabel.DataLabels.Add(1);

secondClusterSecondColumn.Layout.ManualLayout.Left = 10;

secondClusterSecondColumn.Fill.Color = Color.DarkOrange;

Result:

Properties

The ExcelManualLayout class has a few different properties:

• Left
• Top
• Width
• Height

Where Left and Top refer to the data label's position relative to its origin. You can think of them as the (x, y) offset to the position. Where each value can go from -100 to 100. The units are in % of the total chart width for Left and total chart height for Top.

This means that in order to move a data label 1/3 of the chart's height upwards from it's origin you should set it to Top = -33.

Width and Height define the size of the label itself or rather it's 'Bounding Box'

Advanced properties

Each of Left, Top, Width and Height properties also have an eLayoutMode property. LeftMode, TopMode, etc.

By default these are set to eLayoutMode.Factor. Which defines the relevant property as an offset from the label default position. It is not recommended but it can also be set to eLayoutMode.Edge this defines the property as an offset from the relevant chart edge. For example, setting Top and Left mode to edge and their values to 0 would place them in the top left corner of the chart.

Do note that in Edge you are moving the actual edge of the box 'bounding box' the data label lies within. LegacyWidth would become the Right side of the box and LegacyHeight the Bottom.

(In Edge mode each property is a "line" or "side" of the label 'bounding box'. So it's important to ensure they do not overlap. For example, positioning the 'Right'(LegacyWidth) property further left than the rectangle's left side is not a good idea. Epplus will throw an error for most of these cases to avoid them.)

Because of this use of Edge mode is not currently recommended.

Example

Take for example the following scenario:

A bar chart with stacked columns have values so close together they become difficult to see. To fix this we can add a manual layout to some or all of our data labels.

In this stacked column chart there is only one stack of columns. In this case each column in the stack counts as one "series". so to move the bottom data label we can do something like this:

//Get the bar chart from drawings
var bChart = cSheet.Drawings[0].As.Chart.BarChart;

//Get the first Series. AKA the first row of columns.
var genLabel = bChart.Series[0].DataLabel;
//Add a new datalabel, all others will still adhere to the rules of genLabel
var label = genLabel.DataLabels.Add(0);

//Offset the label to 30% of the chartwidth to the left.
label.Layout.ManualLayout.Left = -30;

Which gives us:

Now, to apply it to all data labels in the chart we can do something like this:

public void AddManualLayoutToAll()
{
    //Get the bar chart from drawings
    var bChart = cSheet.Drawings[0].As.Chart.BarChart;

    //Remove gridlines to see our numbers clearer.
    bChart.XAxis.RemoveGridlines(true, true);
    bChart.YAxis.RemoveGridlines(true, true);

    for (int i = 0; i < bChart.Series.Count; i++)
    {
        //Get 'i' Series. AKA the current row of columns.
        var genLabel = bChart.Series[i].DataLabel;
        //Add a new datalabel, all other rows will still adhere to the rules of genLabel
        var label = bChart.Series[i].DataLabel.DataLabels.Add(0);
        label.Layout.ManualLayout.Left = -30;
    }
}

Result:

Now that we've added manual layout to all the datalabels in the chart, let's add some space for the clumped up labels:

//Spacing out clumped up labels
if (i == 3 || i == 2)
{
    label.Layout.ManualLayout.Top = 5;
}
else if (i == 4)
{
    label.Layout.ManualLayout.Top = 0;

}
else if (i == 5)
{
    label.Layout.ManualLayout.Top = -5;
}

Result:

Now we've got some much clearer data labels.