使用EPPLUS C＃更改Excel图表轴格式 [英] C# Change Excel Chart Axis Format using EPPLUS

问题描述

反正是有更改图表X或Y轴设置自定义标签的格式？

Is there anyway to change the chart X or Y axis to set a custom label formatting?

例如，在X轴我的标签日期时间，但格式化YYYY-MM-DD HH：MM：SS。我想改变它只是YYYY-MM-DD。

For example, in the XAxis my labels are datetime but the formatting is yyyy-mm-dd hh:mm:ss. I would like to change it to just yyyy-mm-dd.

想知道是否有可能在EPPLUS不使用互操作

Was wondering if it is possible in EPPLUS without using interop

推荐答案

这是一个有点用图表和EPPlus痛。他们已经暴露了 chart.XAxis.Format 属性来设置格式，但不占属性 souceLinked 其中，如果没有设置为0（默认）告知Excel只与源数据格式去。所以，你必须使用XML手动设置：

This is a bit of a pain with charts and EPPlus. They have exposed the chart.XAxis.Format property to set the format but does not account for the attribute souceLinked which, when not set to 0 (default) tells excel to just go with the source data format. So you have to set it manually with xml:

[TestMethod]
public void Chart_Row_Offset_Test()
{
    //Throw in some data
    var datatable = new DataTable("tblData");
    datatable.Columns.AddRange(new[]
    {
        new DataColumn("Col1", typeof (DateTime)), new DataColumn("Col2", typeof (int)), new DataColumn("Col3", typeof (object))
    });

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

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

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

        //This would be the axis label format if the XML Attribute is not set below
        worksheet.Cells["A2:A11"].Style.Numberformat.Format = "m/d/yyyy";

        const int EXCELCHARTWIDTH = 375;
        const int EXCELCHARTHEIGHT = 350;
        const double EXCELDEFAULTROWHEIGHT = 20.0; //Assuming standard screen dpi

        var startCell = (ExcelRangeBase)worksheet.Cells["A1"];
        for (var i = 0; i < 10; i++)
        {
            var chart = worksheet.Drawings.AddChart("chart " + i, eChartType.XYScatterLines);
            chart.SetSize(EXCELCHARTWIDTH, EXCELCHARTHEIGHT);
            chart.SetPosition(startCell.Start.Row, 0, startCell.Start.Column, 0);
            chart.XAxis.Format = "yyyy-mm-dd";

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

            var chartcellheight = (int)Math.Ceiling(EXCELCHARTHEIGHT / EXCELDEFAULTROWHEIGHT);
            startCell = startCell.Offset(chartcellheight, 0);

            //Get reference to the chart xml for proper namespace
            var xdoc = chart.ChartXml;
            var nsm = new XmlNamespaceManager(xdoc.NameTable);
            nsm.AddNamespace("default", xdoc.DocumentElement.NamespaceURI);

            //Add the attribute to not link to the source data format
            var att = xdoc.CreateAttribute("sourceLinked");
            att.Value = "0";
            var numFmtNode = xdoc.SelectSingleNode("/default:chartSpace/default:chart/default:plotArea/default:valAx/default:numFmt", nsm);
            numFmtNode.Attributes.Append(att);
        }

        pck.Save();
    }


}

这篇关于使用EPPLUS C＃更改Excel图表轴格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！