使用OpenXml在Excel上创建饼图 [英] Create Pie Chart on Excel using OpenXml

问题描述

我想使用OpenXml在Excel上创建饼图.我可以从哪里获得帮助?

I want to create pie chart on Excel using OpenXml. Is there any source from where i can get help?.

我已经开发了条形图.

推荐答案

这是我的参考:

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using C = DocumentFormat.OpenXml.Drawing.Charts;
using DocumentFormat.OpenXml.Drawing.Charts;
using DocumentFormat.OpenXml.Drawing.Spreadsheet;
using DocumentFormat.OpenXml.Drawing;

我在这里使用MemoryStream创建excel文件:

I'm using here MemoryStream to create excel file:

WorkbookPart workbookPart = null;
using (var memoryStream = new MemoryStream())
                {
                    using (var excel = SpreadsheetDocument.Create(memoryStream, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook, true))
                    {
                        workbookPart = excel.AddWorkbookPart();

                        workbookPart.Workbook = new Workbook();

                        WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>("rId1");

                        Worksheet worksheet = new Worksheet();

                        SheetData sheetData = new SheetData();
                        
                        worksheet.Append(sheetData);

                        worksheetPart.Worksheet = worksheet;

                        string relationshipId = "rId1";// workbookPart.GetIdOfPart(worksheetPart);

                        Sheets sheets = new Sheets();
                        

                        uint sheetId = 1;

                        string sheetName = "Sheet" + sheetId;

                        Sheet sheet1 = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
                        sheets.Append(sheet1);

                        workbookPart.Workbook.Append(sheets);

                      


                        //// Add a new drawing to the worksheet.
                        DrawingsPart drawingsPart = worksheetPart.AddNewPart<DrawingsPart>();
                        worksheetPart.Worksheet.Append(new DocumentFormat.OpenXml.Spreadsheet.Drawing()
                        {
                            Id = worksheetPart.GetIdOfPart(drawingsPart)
                        });
                        worksheetPart.Worksheet.Save();

                        string chartTitle = "Generator Utilisation";

                        Dictionary<string, int> data = new Dictionary<string, int>();
                        data.Add("Running", 92);
                        data.Add("Stopped", 8);


                        InsertPieChartInSpreadSheet(drawingsPart, chartTitle, data, 1, 1, 17, 8);

                        excel.Close();
                    }

                    FileStream fileStream = new FileStream(AppDomain.CurrentDomain.BaseDirectory + "Plant Summary Report.xlsx", FileMode.Create, FileAccess.Write);
                    memoryStream.WriteTo(fileStream);
                    fileStream.Close();
                    memoryStream.Close();
                }

这是InsertPieChartInSpreadSheet方法:

Here is InsertPieChartInSpreadSheet method:

private static void InsertPieChartInSpreadSheet(DrawingsPart drawingsPart,string chartTitle, Dictionary<string, int> data, int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex)
        {
            ChartPart chartPart = drawingsPart.AddNewPart<ChartPart>();


            ChartSpace chartSpace = new ChartSpace();
            chartSpace.Append(new EditingLanguage() { Val = new StringValue("en-US") });
            DocumentFormat.OpenXml.Drawing.Charts.Chart chart = chartSpace.AppendChild<DocumentFormat.OpenXml.Drawing.Charts.Chart>(
                new DocumentFormat.OpenXml.Drawing.Charts.Chart());

            PlotArea plotArea = chart.AppendChild<PlotArea>(new PlotArea());
            Layout layout = plotArea.AppendChild<Layout>(new Layout());

            ManualLayout manualLayout1 = new ManualLayout();
            LayoutTarget layoutTarget1 = new LayoutTarget() { Val = LayoutTargetValues.Inner };
            LeftMode leftMode1 = new LeftMode() { Val = LayoutModeValues.Edge };
            TopMode topMode1 = new TopMode() { Val = LayoutModeValues.Edge };
            Left left1 = new Left() { Val = 0.5D };
            Top top1 = new Top() { Val = 0.2D };

            Width width1 = new Width() { Val = 0.95622038461448768D };
            Height height1 = new Height() { Val = 0.54928769841269842D };

            manualLayout1.Append(layoutTarget1);
            manualLayout1.Append(leftMode1);
            manualLayout1.Append(topMode1);
            manualLayout1.Append(left1);
            manualLayout1.Append(top1);
            manualLayout1.Append(width1);
            manualLayout1.Append(height1);



            layout.Append(manualLayout1);

            NoFill noFill = new NoFill();
            C.ShapeProperties shapeProperties = new C.ShapeProperties();

            DocumentFormat.OpenXml.Drawing.Outline outline15 = new DocumentFormat.OpenXml.Drawing.Outline();
            DocumentFormat.OpenXml.Drawing.SolidFill noFill17 = new DocumentFormat.OpenXml.Drawing.SolidFill();

            RgbColorModelHex schemeColor29 = new RgbColorModelHex() { Val = "FFFFFF" };

            noFill17.Append(schemeColor29);
            outline15.Append(noFill17);

            shapeProperties.Append(noFill);
            shapeProperties.Append(outline15);
            plotArea.Append(shapeProperties);


            PieChart pieChart = plotArea.AppendChild<PieChart>(new PieChart());

            PieChartSeries pieChartSeries = pieChart.AppendChild<PieChartSeries>(new PieChartSeries(
                new Index() { Val = (UInt32Value)0U },
                new Order() { Val = (UInt32Value)0U },
                new SeriesText(new NumericValue() { Text = "PieChartSeries" })));

            CategoryAxisData catAx = new CategoryAxisData();


            StringReference stringReference = new StringReference();
            StringCache stringCache = new StringCache();

            PointCount pointCount = new PointCount() { Val = (uint)data.Count };

            stringCache.Append(pointCount);

            uint i = 0;
            foreach (var key in data.Keys)
            {
                stringCache.AppendChild<StringPoint>(new StringPoint() { Index = new UInt32Value(i) }).Append(new NumericValue(key));
                i++;
            }

            stringReference.Append(stringCache);
            catAx.Append(stringReference);
            pieChartSeries.Append(catAx);



            C.Values values = new C.Values();
            NumberReference numberReference = new NumberReference();
            NumberingCache numberingCache = new NumberingCache();

            i = 0;
            foreach (var key in data.Keys)
            {
                numberingCache.AppendChild<NumericPoint>(new NumericPoint() { Index = new UInt32Value(i) }).Append(new NumericValue(data[key].ToString()));
                i++;
            }

            numberReference.Append(numberingCache);
            values.Append(numberReference);
            pieChartSeries.Append(values);

            AddChartTitle(chart, chartTitle);
            pieChart.Append(new AxisId() { Val = new UInt32Value(48650112u) });
            pieChart.Append(new AxisId() { Val = new UInt32Value(48672768u) });


            CategoryAxis catAx1 = plotArea.AppendChild<CategoryAxis>(new CategoryAxis(new AxisId()
            { Val = new UInt32Value(48650112u) }, new Scaling(new Orientation()
            {
                Val = new EnumValue<DocumentFormat.OpenXml.Drawing.Charts.OrientationValues>(DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax)
            }),
                 new AxisPosition() { Val = new EnumValue<AxisPositionValues>(AxisPositionValues.Bottom) },
                 new TickLabelPosition() { Val = new EnumValue<TickLabelPositionValues>(TickLabelPositionValues.NextTo) },
                 new CrossingAxis() { Val = new UInt32Value(48672768U) },
                 new Crosses() { Val = new EnumValue<CrossesValues>(CrossesValues.AutoZero) },
                 new AutoLabeled() { Val = new BooleanValue(true) },
                 new LabelAlignment() { Val = new EnumValue<LabelAlignmentValues>(LabelAlignmentValues.Center) },
                 new LabelOffset() { Val = new UInt16Value((ushort)100) }));




            // Add the Value Axis.
            ValueAxis valAx = plotArea.AppendChild<ValueAxis>(new ValueAxis(new AxisId() { Val = new UInt32Value(48672768u) },
                new Scaling(new Orientation()
                {
                    Val = new EnumValue<DocumentFormat.OpenXml.Drawing.Charts.OrientationValues>(
                    DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax)
                }),
                new AxisPosition() { Val = new EnumValue<AxisPositionValues>(AxisPositionValues.Left) },
                new MajorGridlines(),
                new DocumentFormat.OpenXml.Drawing.Charts.NumberingFormat()
                {
                    FormatCode = new StringValue("General"),
                    SourceLinked = new BooleanValue(true)
                }, new TickLabelPosition()
                {
                    Val = new EnumValue<TickLabelPositionValues>
            (TickLabelPositionValues.NextTo)
                }, new CrossingAxis() { Val = new UInt32Value(48650112U) },
                new Crosses() { Val = new EnumValue<CrossesValues>(CrossesValues.AutoZero) },
                new CrossBetween() { Val = new EnumValue<CrossBetweenValues>(CrossBetweenValues.Between) }));

            // Add the chart Legend.
            Legend legend = chart.AppendChild<Legend>(new Legend(new LegendPosition() { Val = new EnumValue<LegendPositionValues>(LegendPositionValues.Bottom) },
                new Layout()));

            chart.Append(new PlotVisibleOnly() { Val = new BooleanValue(true) });

            chartPart.ChartSpace = chartSpace;

            PositionChart(chartPart, drawingsPart, startRowIndex, startColumnIndex, endRowIndex, endColumnIndex);
        }

这是设置图表位置的另一种方法:

Here's an additional method which will set chart position:

private static void PositionChart(ChartPart chartPart, DrawingsPart drawingsPart, int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex)
        {
            // Position the chart on the worksheet using a TwoCellAnchor object.
            drawingsPart.WorksheetDrawing = new WorksheetDrawing();
            TwoCellAnchor twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild<TwoCellAnchor>(new TwoCellAnchor());
            twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker(new ColumnId(startColumnIndex.ToString()),
                                            new ColumnOffset("581025"),
                                            new RowId(startRowIndex.ToString()),
                                            new RowOffset("114300")));
            twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.ToMarker(new ColumnId(endColumnIndex.ToString()),
                new ColumnOffset("276225"),
                new RowId(endRowIndex.ToString()),
                new RowOffset("0")));

            // Append a GraphicFrame to the TwoCellAnchor object.
            DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame graphicFrame = 
                twoCellAnchor.AppendChild<DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame>(new DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame());
            graphicFrame.Macro = "";

            graphicFrame.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualGraphicFrameProperties(
                new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties() { Id = new UInt32Value(2u), Name = "Chart 1" },
                new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualGraphicFrameDrawingProperties()));

            graphicFrame.Append(new Transform(new Offset() { X = 0L, Y = 0L },
                                                                    new Extents() { Cx = 0L, Cy = 0L }));

            graphicFrame.Append(new Graphic(new GraphicData(new ChartReference() { Id = drawingsPart.GetIdOfPart(chartPart) })
            { Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart" }));

            twoCellAnchor.Append(new ClientData());
        }

此方法将添加图表标题:

This method will add chart title:

private static void AddChartTitle(DocumentFormat.OpenXml.Drawing.Charts.Chart chart, string title)
        {
            var ctitle = chart.AppendChild(new Title());
            var chartText = ctitle.AppendChild(new ChartText());
            var richText = chartText.AppendChild(new RichText());

            var bodyPr = richText.AppendChild(new BodyProperties());
            var lstStyle = richText.AppendChild(new ListStyle());
            var paragraph = richText.AppendChild(new Paragraph());

            var apPr = paragraph.AppendChild(new ParagraphProperties());
            apPr.AppendChild(new DefaultRunProperties());

            var run = paragraph.AppendChild(new DocumentFormat.OpenXml.Drawing.Run());
            run.AppendChild(new DocumentFormat.OpenXml.Drawing.RunProperties() { Language = "en-CA" });
            run.AppendChild(new DocumentFormat.OpenXml.Drawing.Text() { Text = title });
            //ctitle.AppendChild(new Overlay() { Val = new BooleanValue(false) });

        }

这篇关于使用OpenXml在Excel上创建饼图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！