How to set graph Legend position to top left in Excel in epplus

Question

I need to set the Legend position of Graph to Top left side. But not able to set as no such property exists on eLegendPosition.

        var myChart = myWorksheet.Drawings.AddChart("Chart2", eChartType.ColumnClustered) as                  ExcelBarChart;
            **Adding an series to chart**
                  var serie2a = myChart.Series.Add(myWorksheet.Cells[2, 2, dataRow, 2], myWorksheet.Cells[2, 1,                 dataRow, 1]);
                    //serie2a.Header = myWorksheet.Cells[1, 2].Value.ToString();
                    serie2a.Header = "Plan";

                myChart.YAxis.Title.Text = "Daily MH %";
                    myChart.YAxis.Title.Font.Size = 8;

                    chart2b.UseSecondaryAxis = true; //Flip the axes

                    chart5e.UseSecondaryAxis = true; //Flip the axes

                    chart2b.YAxis.Title.Text = "Cumul Daily MH %";
                    chart2b.YAxis.Title.Font.Size = 8;
             **Here I am Facing an issue while setting it to TopLeft**
                    myChart.Legend.Position = eLegendPosition.Bottom;
                    myChart.Border.Fill.Color = System.Drawing.Color.Green;
                    myChart.Title.Text = "OVERALL S CURVE";
                    myChart.Title.Font.Size = 10;
                    myChart.SetSize(800, 400);

                    // Add to 7th row and to the 7th column
                    myChart.SetPosition(7, 0, 7, 0);

Answer 1

The enum that Epplus has matches excel so that setting alone will not do what you need. But you could manually set it in XML using a ManualLayout node:

https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.drawing.charts.manuallayout?view=openxml-2.8.1

Similar to how it was done in How to add to Text Label For excel Charts using Open xml or EPPLUS.

Here is a helper method to make it a bit more concise:

/// <summary>
/// Sets the chart's layout node <c>c:chartSpace/c:chart/c:legend/c:layout</c> for the
/// passed <see cref="ExcelBarChart"/>.
/// </summary>
/// <param name="chart">Chart to set </param>
/// <param name="x">Position X Value (percentage).</param>
/// <param name="y">Position Y Value (percentage).</param>
/// <param name="culture">Culture to use when converting the doubles to string based on excel settings.</param>
public static void SetChartManualLayout(this ExcelBarChart chart, double x, double y, CultureInfo culture)
{
    //Set layout via xml
    var chartXml = chart.ChartXml;
    var nsm = new XmlNamespaceManager(chartXml.NameTable);

    var nsuri = chartXml.DocumentElement.NamespaceURI;
    nsm.AddNamespace("c", nsuri);
    nsm.AddNamespace("a", "http://schemas.openxmlformats.org/drawingml/2006/main");

    //Get the title layout and add the manual section
    var layoutNode = chartXml.SelectSingleNode("c:chartSpace/c:chart/c:legend/c:layout", nsm);
    var manualLayoutNode = chartXml.CreateElement("c:manualLayout", nsuri);
    layoutNode.AppendChild(manualLayoutNode);

    //Add coordinates
    var xModeNode = chartXml.CreateElement("c:xMode", nsuri);
    var attrib = chartXml.CreateAttribute("val");
    attrib.Value = "edge";
    xModeNode.Attributes.Append(attrib);
    manualLayoutNode.AppendChild(xModeNode);

    var yModeNode = chartXml.CreateElement("c:yMode", nsuri);
    attrib = chartXml.CreateAttribute("val");
    attrib.Value = "edge";
    yModeNode.Attributes.Append(attrib);
    manualLayoutNode.AppendChild(yModeNode);

    var xNode = chartXml.CreateElement("c:x", nsuri);
    attrib = chartXml.CreateAttribute("val");
    attrib.Value = x.ToString(culture);
    xNode.Attributes.Append(attrib);
    manualLayoutNode.AppendChild(xNode);

    var yNode = chartXml.CreateElement("c:y", nsuri);
    attrib = chartXml.CreateAttribute("val");
    attrib.Value = x.ToString(culture);
    yNode.Attributes.Append(attrib);
    manualLayoutNode.AppendChild(yNode);
}

And to use it in your code, simply do this:

// Add to 7th row and to the 7th column
myChart.SetPosition(7, 0, 7, 0);
myChart.Legend.Position = eLegendPosition.Left;
myChart.SetChartManualLayout(0.015, 0.015, CultureInfo.CurrentCulture);

Provide the X and Y as percent. 0,0 would put it at the top-left and 1,1 would put it at the bottom-right. Also, make sure to account for Culture if Excel is set differently than your app.