Epplus insert chart ColumnStacked3D switch row/column

Question

I'm using epplus to create excel in my program! I need insert a column chart. This is my code:

//Add the chart to the sheet
var chart = sheet.Drawings.AddChart(chartTitle, eChartType.ColumnStacked3D);
chart.SetPosition(positionRow, 2, positionCol, 2);
chart.Title.Text = chartTitle;
chart.Title.Font.Bold = true;
chart.Title.Font.Size = 18;
chart.SetSize(width, height);

//Set the data range
chart.Series.Add("D17:D22", "B17:B22");
chart.Series.Add("P17:P22", "B17:B22");

And I get result:

But I want result as:

After I created excel file from program, I open it and change the chart: Right click in the chart/Select data/Switch row/column. How can I Switch row/column in my code? Or how to insert the chart like the below picture?

Sorry for not good in English

Thank you very much!

Answer 1

That button in excel just switches the data and rebuilds the chart. Rather then try to mimic it better to build the chart the right way from the start.

What I mean is your original chart is treating the data as 2 data series but what you really want is 6 series.

The only problem is values in the x axis - there is no direct way with Epplus it seems to get to the category (horizontal) axis labels of the series. So you have to do it through XML manipulation as below.

So change your code to this:

//Set the data range
//chart.Series.Add("D17:D22", "B17:B22");
//chart.Series.Add("P17:P22", "B17:B22");

for (var i = 0; i < opt.Count; i++)
{
    var datarange = sheet.Cells[$"Bar!D{17 + i},Bar!P{17 + i}"];
    var ser = chart.Series.Add(datarange.Address, $"B{17 + i}:B{17 + i}");
    ser.HeaderAddress = sheet.Cells[$"$B{17 + i}"];
}

//have to remove cat nodes from each series so excel autonums 1 and 2 in xaxis
var chartXml = chart.ChartXml;
var nsm = new XmlNamespaceManager(chartXml.NameTable);

var nsuri = chartXml.DocumentElement.NamespaceURI;
nsm.AddNamespace("c", nsuri);

//Get the Series ref and its cat
var serNodes = chartXml.SelectNodes("c:chartSpace/c:chart/c:plotArea/c:bar3DChart/c:ser", nsm);
foreach (XmlNode serNode in serNodes)
{
    //Cell any cell reference and replace it with a string literal list
    var catNode = serNode.SelectSingleNode("c:cat", nsm);
    catNode.RemoveAll();

    //Create the string list elements
    var ptCountNode = chartXml.CreateElement("c:ptCount", nsuri);
    ptCountNode.Attributes.Append(chartXml.CreateAttribute("val", nsuri));
    ptCountNode.Attributes[0].Value = "2";

    var v0Node = chartXml.CreateElement("c:v", nsuri);
    v0Node.InnerText = "opening";
    var pt0Node = chartXml.CreateElement("c:pt", nsuri);
    pt0Node.AppendChild(v0Node);
    pt0Node.Attributes.Append(chartXml.CreateAttribute("idx", nsuri));
    pt0Node.Attributes[0].Value = "0";

    var v1Node = chartXml.CreateElement("c:v", nsuri);
    v1Node.InnerText = "closing";
    var pt1Node = chartXml.CreateElement("c:pt", nsuri);
    pt1Node.AppendChild(v1Node);
    pt1Node.Attributes.Append(chartXml.CreateAttribute("idx", nsuri));
    pt1Node.Attributes[0].Value = "1";

    //Create the string list node
    var strLitNode = chartXml.CreateElement("c:strLit", nsuri);
    strLitNode.AppendChild(ptCountNode);
    strLitNode.AppendChild(pt0Node);
    strLitNode.AppendChild(pt1Node);
    catNode.AppendChild(strLitNode);
}

pck.Save();

Which gives this as the output in my unit test (made up the numbers):