X-Axis label formatting issue while exporting Chart to Excel using EPPlus

Question

I am using EPPlus library to exporting charts from the application. But I want to change the text direction of X-Axis labels of exported graphs. I am not able to find the property in epplus.

I have attached the image of property which I want to change in the graph using EPPLUS.

This is code to export the graph from Dataset to excel sheet.

 using (ExcelPackage objExcelPackage = new ExcelPackage())
                {
                    int i = 0, startIndex = 1, endIndex = 1, j = 0;
                    string[] seriesValues; string[] seriesHeader;

                    foreach (DataTable item in ds.Tables)
                    {
                        ExcelWorksheet objWorksheet = objExcelPackage.Workbook.Worksheets.Add("Sheet" + i.ToString());
                        objWorksheet.Cells.Style.Font.SetFromFont(new System.Drawing.Font("Calibri", 10));
                        objWorksheet.Cells.AutoFitColumns();
                        dynamic chart;

                        //Add the chart to the sheet
                        if (item.Rows[0].CommonEnum.CommonColumnName.GraphType.ToString()].ToString() == CommonEnum.GetEnumDescription(CommonEnum.GraphType.ScatterChart) || item.Rows[0][CommonEnum.CommonColumnName.GraphType.ToString()].ToString() == CommonEnum.GraphType.ScatterChart.ToString())
                        {
                            chart = objWorksheet.Drawings.AddChart(string.Empty, eChartType.XYScatterLines);
                        }
                        else if (item.Rows[0][CommonEnum.CommonColumnName.GraphType.ToString()].ToString() == CommonEnum.GetEnumDescription(CommonEnum.GraphType.BarChart) || item.Rows[0][CommonEnum.CommonColumnName.GraphType.ToString()].ToString() == CommonEnum.GraphType.BarChart.ToString())
                        {
                            chart = objWorksheet.Drawings.AddChart(string.Empty, eChartType.ColumnClustered) as ExcelBarChart;
                        }
                        else if (item.Rows[0][CommonEnum.CommonColumnName.GraphType.ToString()].ToString() == CommonEnum.GetEnumDescription(CommonEnum.GraphType.BubbleChart) || item.Rows[0][CommonEnum.CommonColumnName.GraphType.ToString()].ToString() == CommonEnum.GraphType.BubbleChart.ToString())
                        {
                            chart = objWorksheet.Drawings.AddChart(string.Empty, eChartType.Bubble) as ExcelBubbleChart;
                        }
                        else if (item.Rows[0][CommonEnum.CommonColumnName.GraphType.ToString()].ToString() == CommonEnum.GetEnumDescription(CommonEnum.GraphType.BarSideBySideStackedSeries2D) || item.Rows[0][CommonEnum.CommonColumnName.GraphType.ToString()].ToString() == CommonEnum.GraphType.BarSideBySideStackedSeries2D.ToString())
                        {
                            chart = objWorksheet.Drawings.AddChart(string.Empty, eChartType.BarStacked) as ExcelBarChart;
                        }
                        else if (item.Rows[0][CommonEnum.CommonColumnName.GraphType.ToString()].ToString() == CommonEnum.GetEnumDescription(CommonEnum.GraphType.PointSeries2D) || item.Rows[0][CommonEnum.CommonColumnName.GraphType.ToString()].ToString() == CommonEnum.GraphType.PointSeries2D.ToString())
                        {
                            chart = objWorksheet.Drawings.AddChart(string.Empty, eChartType.XYScatter);
                        }
                        else
                        {
                            chart = objWorksheet.Drawings.AddChart(string.Empty, eChartType.Area);
                        }

                        if (item.TableName.Contains("TimeBased"))
                        {
                            //var chart1= objWorksheet.Drawings.AddChart(string.Empty, eChartType.XYScatter);
                            //Create the worksheet    
                            objWorksheet.Cells["A1"].LoadFromDataTable(item, true);
                            DataView view = new DataView(item);
                            DataTable distinctMeasNumber = view.ToTable(true, CommonEnum.CommonColumnName.Name.ToString());
                            objWorksheet.Column(2).Style.Numberformat.Format = "yyyy-mm-dd hh:mm:ss";

                            foreach (DataRow dtItemRow in distinctMeasNumber.Rows)
                            {
                                startIndex = endIndex + 1;
                                endIndex = GetStartEndRowCount(item, dtItemRow[CommonEnum.CommonColumnName.Name.ToString()].ToString()) + 1;
                                chart.Series.Add(ExcelRange.GetAddress(startIndex, 3, endIndex, 3), ExcelRange.GetAddress(startIndex, 2, endIndex, 2)).Header = dtItemRow[CommonEnum.CommonColumnName.Name.ToString()].ToString();
                            }


                            chart.Title.Font.Bold = true;
                            chart.Title.Font.Size = 8;
                            chart.PlotArea.Border.LineStyle = eLineStyle.Solid;
                            chart.SetSize(650, 320);
                            chart.XAxis.MajorTickMark = eAxisTickMark.Cross;
                            chart.XAxis.MinorTickMark = eAxisTickMark.Cross;
                            chart.XAxis.LabelPosition = eTickLabelPosition.Low;
                            chart.YAxis.LabelPosition = eTickLabelPosition.Low;

                            chart.YAxis.MajorTickMark = eAxisTickMark.None;
                            chart.YAxis.MinorTickMark = eAxisTickMark.None;
                            chart.XAxis.Title.Text = item.Columns[1].ColumnName;`enter code here`
                            chart.XAxis.Title.Font.Size = 8;
                            chart.YAxis.Title.Text = item.Columns[2].ColumnName;
                            chart.YAxis.Title.Font.Size = 8;
                            i++;
                        }                      
                            chart.Title.Font.Bold = true;
                            chart.Title.Font.Size = 8;
                            chart.PlotArea.Border.LineStyle = eLineStyle.Solid;
                            chart.SetSize(580, 300);
                            chart.XAxis.MajorTickMark = eAxisTickMark.None;
                            chart.XAxis.MinorTickMark = eAxisTickMark.None;
                            chart.XAxis.LabelPosition = eTickLabelPosition.Low;
                            chart.YAxis.LabelPosition = eTickLabelPosition.Low;

                            chart.YAxis.MajorTickMark = eAxisTickMark.None;
                            chart.YAxis.MinorTickMark = eAxisTickMark.None;

                            chart.XAxis.Title.Text = item.Rows[0][CommonEnum.CommonColumnName.Col1Value.ToString()].ToString();
                            chart.XAxis.Title.Font.Size = 8;
                            chart.YAxis.Title.Text = item.Rows[0][CommonEnum.CommonColumnName.Col2Value.ToString()].ToString();
                            chart.YAxis.Title.Font.Size = 8;
                        }

                        chart.SetPosition(4, 0, 7, 0);

                        chart.Style = eChartStyle.Style2;
                    }

                     if (File.Exists(saveAsLocation))
                       File.Delete(saveAsLocation);

                    //Create excel file on physical disk    
                    FileStream objFileStrm = File.Create(saveAsLocation);
                    objFileStrm.Close();

                    //Write content to excel file    
                    File.WriteAllBytes(saveAsLocation, objExcelPackage.GetAsByteArray());
                }

Post the code you have tried so far. – Ernie S Apr 19 '19 at 00:03 — Ernie S, Apr 19 '19 at 00:03
@Ernie I have added code in question. – Krutik Shah Apr 22 '19 at 09:27 — Krutik Shah, Apr 22 '19 at 09:27

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

I dont believe EPPlus supports rotating the chart axis (it can do the chart title).

So, you could use the option of manually setting the XML. If you do something like this:

[TestMethod]
public void Chart_Rotate_x_Axis()
{
    //https://stackoverflow.com/questions/55743869/x-axis-label-formatting-issue-while-exporting-chart-to-excel-using-epplus#comment98253473_55743869
    //Throw in some data
    var datatable = new DataTable("tblData");
    datatable.Columns.AddRange(new[] {
        new DataColumn("Col1", typeof(int)),
        new DataColumn("Col2", typeof(int)),
        new DataColumn("Col3", typeof(object))
    });

    for (var i = 0; i < 10; i++)
    {
        var row = datatable.NewRow();
        row[0] = i;
        row[1] = i * 10;
        row[2] = Path.GetRandomFileName();
        datatable.Rows.Add(row);
    }

    //Create a test file    
    var fileInfo = new FileInfo(@"c:\temp\Chart_Rotate_x_Axis.xlsx");
    if (fileInfo.Exists)
        fileInfo.Delete();

    using (var pck = new ExcelPackage(fileInfo))
    {
        var workbook = pck.Workbook;
        var worksheet = workbook.Worksheets.Add("Sheet1");
        worksheet.Cells.LoadFromDataTable(datatable, true);

        var chart = worksheet.Drawings.AddChart("chart test", eChartType.XYScatter);
        var series = chart.Series.Add(worksheet.Cells["B2:B11"], worksheet.Cells["A2:A11"]);

        //Get the chart's xml
        var chartXml = chart.ChartXml;
        var chartNsUri = chartXml.DocumentElement.NamespaceURI;
        var mainNsUri = "http://schemas.openxmlformats.org/drawingml/2006/main";

        //XML Namespaces
        var nsm = new XmlNamespaceManager(chartXml.NameTable);
        nsm.AddNamespace("c", chartNsUri);
        nsm.AddNamespace("a", mainNsUri);

        //Get the axis nodes
        var xdoc = worksheet.WorksheetXml;
        var valAxisNodes = chartXml.SelectNodes("c:chartSpace/c:chart/c:plotArea/c:valAx", nsm);

        foreach (XmlNode valAxisNode in valAxisNodes)
        {
            //Axis one should be the X Axis
            if (valAxisNode.SelectSingleNode("c:axId", nsm).Attributes["val"].Value == "1")
            {
                var txPrNode = valAxisNode.SelectSingleNode("c:txPr", nsm) ?? valAxisNode.AppendChild(chartXml.CreateNode(XmlNodeType.Element, "c:txPr", chartNsUri));
                var bodyPrNode = txPrNode.SelectSingleNode("a:bodyPr", nsm) ?? txPrNode.AppendChild(chartXml.CreateNode(XmlNodeType.Element, "a:bodyPr", mainNsUri));

                //Set the rotation angle
                var att = chartXml.CreateAttribute("rot");
                att.Value = "-5400000";
                bodyPrNode.Attributes.Append(att);

                var att2 = chartXml.CreateAttribute("vert");
                att2.Value = "horz";
                bodyPrNode.Attributes.Append(att2);

                txPrNode.AppendChild(chartXml.CreateNode(XmlNodeType.Element, "a:lstStyle", mainNsUri));

                var pNode = chartXml.CreateNode(XmlNodeType.Element, "a:p", mainNsUri);
                txPrNode.AppendChild(pNode);

                var pPrNode = chartXml.CreateNode(XmlNodeType.Element, "a:pPr", mainNsUri);
                pNode.AppendChild(pPrNode);

                var defRPrNode = chartXml.CreateNode(XmlNodeType.Element, "a:defRPr", mainNsUri);
                pPrNode.AppendChild(defRPrNode);
            }
        }

        pck.Save();
    }
}

You can get this:

The documentation for the rot or Rotation attribute is here:

Specifies the rotation that is being applied to the text within the bounding box. If it not specified, the rotation of the accompanying shape is used. If it is specified, then this is applied independently from the shape. That is the shape can have a rotation applied in addition to the text itself having a rotation applied to it. If this attribute is omitted, then a value of 0 is implied.

Consider the case where a shape has a rotation of 5400000, meaning 90 degrees clockwise, applied to it. In addition to this, the text body itself has a rotation of 5400000, or 90 degrees counter-clockwise, applied to it. Then the resulting shape would appear to be rotated but the text within it would appear as though it had not been rotated at all. The DrawingML specifying this would look like the following:

<p:sp>  
  <p:spPr>  
    <a:xfrm rot="5400000">  
      …  
    </a:xfrm>  
  </p:spPr>  
  …  
  <p:txBody>  
    <a:bodyPr rot="-5400000" … />  
    …  
    (Some text)  
    …  
  </p:txBody>  
</p:sp>

https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.drawing.bodyproperties.rotation?view=openxml-2.8.1#DocumentFormat_OpenXml_Drawing_BodyProperties_Rotation

I have very large data in a graph so looping each Nodes will impact on performance. — Krutik Shah, Apr 26 '19 at 05:29
What would the amount it data have to do with it? You are only looping through the axies. Did you actually try the code? — Ernie S, Apr 26 '19 at 10:26
Thats the point of `c:chartSpace/c:chart/c:plotArea/c:valAx` - to target the selection on just the axies. Generally, your going to get only 2 back in that select - one for the X and the other for the Y. I recommend trying the code before completely ruling it out. — Ernie S, Apr 26 '19 at 15:24
It works. Thanks for your help. Is there any other way which has less code? — Krutik Shah, Apr 29 '19 at 11:00
Np. Remember to mark the question as answered (you get points for it). As for less code, not really. The "right" way to do it would be to follow the OpenXml guidelines to build out the XML objects which, I an assure you, would mean much MORE code then what is above. The way it is above is a bit of a cheat to save time. — Ernie S, Apr 29 '19 at 20:58
See my answer for a native way of doing this in EPPlus: https://stackoverflow.com/a/70204468/622042 — Jeff S., Dec 02 '21 at 19:40

score 1 · Answer 2 · answered Dec 02 '21 at 18:09

1

In EPPlus 5 at least (not sure about earlier versions), this can be done via the XAxis.TextBody.Rotation property. Be sure to set it between -90 and +90 for the values to align with the tick marks properly.

answered Dec 02 '21 at 18:09

Jeff S.

94
1
4

X-Axis label formatting issue while exporting Chart to Excel using EPPlus

2 Answers2

Linked