c#epplus
X-Axis label formatting issue while exporting Chart to Excel using EPPlus
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());
}
Solution
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>
- Why does an empty preprocessor command still evaluate to something?
- How to implement variable sized array within C struct
- Character array typecasting to integer
- How can I exclude non-numeric keys? CS50 Caesar Pset2
- How to get the sign, mantissa and exponent of a floating point number
- Why do MCU libraries use logic operations instead of bitfield structs?
- What kind of implementation can I use for a static associative array on a vintage system with very limited resources?
- Determine libraries to link against for a windows library function?
- Passing macro values to arm linker that places variable at a specific location
- running a program with wildcards as arguments
- How to perform addition of two vectors of 8-bit integers with a single addition in C/C++
- GNU RISC-V Embedded GCC throws "x ISA extension `xw' must be set with the versions" error
- Counting pulses using a swiss flow meter with an Arduino, how is it done?
- How to create a folder in C (need to run on both Linux and Windows)
- Is there any way to compute the width of an integer type at compile-time?
- How can I initialize all members of an array to the same value?
- Is C notably faster than C++
- How to get the Windows SDK version number a program is compiling with at compile time
- Confused by difference between expression inside if and expression outside if
- Equivalent of atoi for unsigned integers
- k&r: Exercise 1-18. Program takes input but doesnt produce any output?
- Using in C thrd_sleep() to either wait for time or interrupt by signal. Example?
- How can I compute `exp(x)/2` when `x` is large?
- c programming: answer always equates to 0
- Is it possible to access a parameter of a function from another function in C?
- Will this expression evaluate to true or false (1 or 0) in C?
- What Is the Return Value of strcspn() When Str1 Does not Contain Str2?
- Mapping a numeric range onto another
- Signalled and non-signalled state of event
- Why is faster to do a branch than a lookup?