如何使用Open xml或EPPLUS为Excel图表添加文本标签 [英] How to add to Text Label For excel Charts using Open xml or EPPLUS

问题描述

我完全不熟悉C＃中的Excel自动化

I am completely new to Excel automation in C#

实际上，我遇到了一些C＃.net中用于Excel生成的API，例如CLOSED XML，EEPLUS和sheetlightlight由vincent提供，由Microsoft提供的Open XML，由Microsoft提供的Interop excel

Actually I am came across some of API for Excel generation in C# .net like CLOSED XML , EEPLUS , and spreadsheetlight by vincent ,Open XML by Microsoft , Interop excel by Microsoft

根据我的研究

已关闭XML-否支持的图表

CLOSED XML -- No charts supported

EEPLUS-支持的图表

EEPLUS - Charts supported

散光灯-非常易于使用，还支持图表

Spread light- Very easy to use and Charts also supported

打开XML-复杂而难以工作

Open XML- complex hard to work

我对Spread light很好，但API很好，但我不是能够找到一种解决方案，如何在图表中添加标签

I was completely fine with Spread light light was good API , but i am not able to find a solution how to add label inside a Chart

我希望堆栈中的任何一个人都遇到相同的问题。

I hope any one in stack overflow came across with same problem.

我需要在图表内添加诸如文本之类的标签，例如图表中的公司。

I need to add label like text inside chart like for example Company in chart.

请让我知道如何找到这个免费API的解决方案

Please let me know how to find solution any one this Free API

谢谢

Ranjith

Thanks
Ranjith

推荐答案

您可以通过Epplus在标题处添加，但定位需要XML编辑：

You can add at title via Epplus but positioning will require XML editing:

    [TestMethod]
    public void Chart_Manual_Title_Test()
    {
        //http://stackoverflow.com/questions/37304860/how-to-add-to-text-label-for-excel-charts-using-open-xml-or-epplus
        //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_Manual_Title_Test.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"]);

            chart.Title.Text = "XYZ Corp";

            //Add custom layout
            var chartXml = chart.ChartXml;
            var nsm = new XmlNamespaceManager(chartXml.NameTable);

            var nsuri = chartXml.DocumentElement.NamespaceURI;
            nsm.AddNamespace("c", nsuri);
            nsm.AddNamespace("a", "http://schemas.openxmlformats.org/drawingml/2006/main");

            //Set the title overlay
            var overlayNode = chartXml.SelectSingleNode("c:chartSpace/c:chart/c:title/c:overlay", nsm);
            overlayNode.Attributes["val"].Value = "1";

            //Set the font size
            var defRPrNode = chartXml.SelectSingleNode("c:chartSpace/c:chart/c:title/c:tx/c:rich/a:p/a:pPr/a:defRPr", nsm);
            defRPrNode.Attributes["sz"].Value = "1200";

            //Get the title layout and add the manual section
            var layoutNode = chartXml.SelectSingleNode("c:chartSpace/c:chart/c:title/c:layout", nsm);
            var manualLayoutNode = chartXml.CreateElement("c:manualLayout", nsuri);
            layoutNode.AppendChild(manualLayoutNode);

            //Add coordinates
            var xModeNode = chartXml.CreateElement("c:xMode", nsuri);
            var attrib = chartXml.CreateAttribute("val");
            attrib.Value = "edge";
            xModeNode.Attributes.Append(attrib);
            manualLayoutNode.AppendChild(xModeNode);

            var yModeNode = chartXml.CreateElement("c:yMode", nsuri);
            attrib = chartXml.CreateAttribute("val");
            attrib.Value = "edge";
            yModeNode.Attributes.Append(attrib);
            manualLayoutNode.AppendChild(yModeNode);

            var xNode = chartXml.CreateElement("c:x", nsuri);
            attrib = chartXml.CreateAttribute("val");
            attrib.Value = "0.9";
            xNode.Attributes.Append(attrib);
            manualLayoutNode.AppendChild(xNode);

            var yNode = chartXml.CreateElement("c:y", nsuri);
            attrib = chartXml.CreateAttribute("val");
            attrib.Value = "0.95";
            yNode.Attributes.Append(attrib);
            manualLayoutNode.AppendChild(yNode);

            pck.Save();
        }
    }

在输出中将为您提供以下内容：

Which gives you this in the output:

好吧，这有点困难。正确的方法是使用 relSizeAnchor ，它可以放置在图表中并随其移动/调整大小。但是，您将不得不从头开始（或者最好是另一个库）。如果您在excel中激活图表并执行插入>文本框以查看其外观。

Ok, thats a little tougher. The right way would be to use a relSizeAnchor which can be placed inside the chart and moved/sized with it. But that you would have to do from scratch (or at best another library). If you activate a chart in excel and do an Insert > Text Box to see what that looks like.

另一种选择是通过使用未使用的标题来伪造它，例如说

Another option would be to fake it by using an unused title like say an Axis Title and moving it similar to how I did the chart title.

但最简单的选择是简单地添加形状。缺点是，如果您移动图表，则图表将不会随之移动：

But the easiest option would be to simply add a shape. The draw back is if you move the chart it will not move with it:

var tb1 = worksheet.Drawings.AddShape("tb1", eShapeStyle.Rect);
tb1.Text = "ABC Company";
tb1.SetPosition(1, 0, 2, 0);
tb1.SetSize(200, 20);
tb1.Font.Color = Color.Black;
tb1.TextAlignment = eTextAlignment.Center;
tb1.Fill.Color = Color.LightYellow;
tb1.Fill.Style = eFillStyle.SolidFill;
tb1.Border.Fill.Color = Color.Red;

将其作为输出与以上内容结合使用：

gives this as the output when combined with above:

这篇关于如何使用Open xml或EPPLUS为Excel图表添加文本标签的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！