C# EPPLUS setting the angle of the chart's X axis tick labels

Question

Is there a way to set the angle alignment of the axis tick labels in a chart using EPPLUS? I'm generating eChartType.XYScatterLinesNoMarkers charts and my X axis (having a lot of tick labels) looks very cluttered.

How the chart looks currently with a cluttered X axis: X axis horizontal alignment

How I would like the chart to look: X axis 45 degree alignment

If it's not possible to set the angle, is it possible to set the labels' orientation to a vertical orientation; i.e. 90°?

var chart = chartWorksheet.Drawings.AddChart(entry.Key, eChartType.XYScatterLinesNoMarkers);
                    chart.XAxis.MaxValue = businessDayDate.ToOADate();
                    chart.XAxis.MinValue = businessDayDate.AddDays(chartDayThreshold * -1).ToOADate();
                    chart.XAxis.MajorUnit = 20;

I'm able to edit the min, max, major/minor units of the axis but not the alignment of the labels.

Dont think there is an option for that in Epplus. You would have to add it via XML or use another library I am afraid. The path would be `c:chartSpace/c:chart/c:plotArea/c:valAx/c:txPr/a:bodyPr` and add the attribute to `rot="2700000"` to it. — Ernie S, Feb 18 '16 at 15:32
Thanks @Ernie, it sucks that this isn't available in Epplus. I checked out some other posts but couldn't find anything. It looks like the only way of doing this is by saving the `ExcelPackage package` by `package.save`, and reopening and formatting the sheet through XML, as you suggested, or through `Microsoft.Office.Interop.Excel`. I'll post an answer to this question to show how I did it using `Microsoft.Office.Interop.Excel`. — Nikita93, Feb 18 '16 at 20:21
You can do this now - see this answer https://stackoverflow.com/questions/39103496/c-sharp-change-excel-chart-label-orientation-using-epplus/74570591#74570591 — Liam, Nov 25 '22 at 09:29

score 2 · Accepted Answer · answered Feb 18 '16 at 20:39

I was able to set the 45 degree axis label alignment by saving the Epplus ExcelPackage, reopening the file through Microsoft.Office.Interop.Excel and then formatting it. Don't forget save, close and quit when you're done.

string fullFileNameWithPath = "C:\Temp\chartSheet.xlsx";
Application excelApp = new Application();
Workbook excelWorkbook = excelApp.Workbooks.Open(fullFileNameWithPath,
                0, false, 5, "", "", false, XlPlatform.xlWindows, "",
                true, false, 0, true, false, false);

//Get all sheets in workbook.
Sheets excelSheets = excelWorkbook.Worksheets;

//Get main chart sheet.
string currentSheet = "Chart Report";
Worksheet excelWorksheet = (Worksheet)excelSheets.get_Item(currentSheet);

//Access the chart.
ChartObject chartObject2 = (ChartObject)excelWorksheet.ChartObjects("Chart 1");
Microsoft.Office.Interop.Excel.Chart chartPage = chartObject2.Chart;
chartPage.Axes(XlAxisType.xlCategory).TickLabels.Orientation = -45;

excelWorkbook.Save();
excelApp.Workbooks.Close();
excelApp.Quit();

score 1 · Answer 2 · answered Jan 17 '18 at 06:48

In cases like this, I find using the OpenXML SDK preferable to using Excel Interop. The actual code is hideous, but at least it doesn't rely on opening a hidden copy of Excel, that must actually be installed on the user's computer or server, etc.

The actual magic is in the part that adds a BodyProperties with Rotation = 5400000. Everything else is OpenXML boilerplate/cruft, and generated automatically using the OpenXML Productivity Tool.

using (SpreadsheetDocument document = SpreadsheetDocument.Open(outputFile, true))
        {
            var chartSheet = document.WorkbookPart.Workbook.Descendants<Sheet>().SingleOrDefault(s => s.Name == "Geo Type Chart");
            if (chartSheet == null)
            {
                return;
            }

            WorksheetPart wsPart = (WorksheetPart)document.WorkbookPart.GetPartById(chartSheet.Id);
            DrawingsPart dp = wsPart.DrawingsPart;
            ChartPart cp = dp.ChartParts.FirstOrDefault();
            if (cp == null)
            {
                return;
            }

            C.ChartSpace chartSpace1 = cp.ChartSpace;
            C.Chart chart1=chartSpace1.GetFirstChild<C.Chart>();
            C.PlotArea plotArea1=chart1.GetFirstChild<C.PlotArea>();
            C.CategoryAxis categoryAxis1=plotArea1.GetFirstChild<C.CategoryAxis>();
            C.CrossingAxis crossingAxis1=categoryAxis1.GetFirstChild<C.CrossingAxis>();

            C.TextProperties textProperties1 = new C.TextProperties();
            A.BodyProperties bodyProperties1 = new A.BodyProperties(){ Rotation = 5400000 };
            A.ListStyle listStyle1 = new A.ListStyle();

            A.Paragraph paragraph1 = new A.Paragraph();

            A.ParagraphProperties paragraphProperties1 = new A.ParagraphProperties();
            A.DefaultRunProperties defaultRunProperties1 = new A.DefaultRunProperties();

            paragraphProperties1.Append(defaultRunProperties1);
            A.EndParagraphRunProperties endParagraphRunProperties1 = new A.EndParagraphRunProperties(){ Language = "en-US" };

            paragraph1.Append(paragraphProperties1);
            paragraph1.Append(endParagraphRunProperties1);

            textProperties1.Append(bodyProperties1);
            textProperties1.Append(listStyle1);
            textProperties1.Append(paragraph1);
            categoryAxis1.InsertBefore(textProperties1,crossingAxis1);
        }

score -1 · Answer 3 · answered Jan 03 '17 at 13:41

For do Somethings On Excel File That not Exist in Epplus framework Code...

You Can Use Add Macro That Execute in Open Excel File. view This Sample Code for Rotate All Label Data Text On Serie in Chart that not exist code in Epplus for do this.

package.Workbook.CreateVBAProject();
OfficeOpenXml.VBA.ExcelVBAModule excelVbaModule =
                   package.Workbook.VbaProject.Modules.AddModule("Module1");
System.Text.StringBuilder mac = new System.Text.StringBuilder();
mac.AppendLine("Sub Auto_Start()");
mac.AppendLine("Sheets(2).Select");
mac.AppendLine("ActiveSheet.ChartObjects(\"chartWeeklyReport\").Activate");
mac.AppendLine("ActiveChart.FullSeriesCollection(1).DataLabels.Select");
mac.AppendLine("Selection.Position = xlLabelPositionAbove");
mac.AppendLine("Selection.Orientation = xlUpward");
mac.AppendLine("Selection.Position = xlLabelPositionAbove");
mac.AppendLine("End Sub");
excelVbaModule.Code = mac.ToString();
package.Save();

For Create Other Macros You Can Use Record Macro Button in Excel and Create New Macro For your Use

C# EPPLUS setting the angle of the chart's X axis tick labels

3 Answers3