예제 #1
0
        public static void supprChart(WorksheetPart worksheetPart, string titre)
        {
            DrawingsPart a = worksheetPart.DrawingsPart;

            DrawingsPart            dp  = worksheetPart.DrawingsPart;
            IEnumerable <ChartPart> cps = a.ChartParts;

            ChartPart b = null;

            foreach (ChartPart cp in cps)
            {
                if (cp.ChartSpace.Descendants <Chart.Chart>().First().Descendants <Chart.Title>().First().Descendants <DocumentFormat.OpenXml.Drawing.Run>().First().Text.Text == titre)
                {
                    b = cp;
                }
            }

            //obtention id du modèle
            string id = a.GetIdOfPart(b);

            a.DeletePart(b);

            //Suppression de l'ancre associée
            DocumentFormat.OpenXml.Drawing.Spreadsheet.WorksheetDrawing wsd = a.WorksheetDrawing;
            foreach (Draw.TwoCellAnchor tca in wsd.Elements <Draw.TwoCellAnchor>())
            {
                string tmp = tca.Descendants <Chart.ChartReference>().First().Id;
                if (tmp == id)
                {
                    tca.Remove();
                }
            }
        }
예제 #2
0
        public void SetChartLocation(DrawingsPart drawingsPart, ChartPart chartPart, SpreadsheetLocation location)
        {
            drawingsPart.WorksheetDrawing = new WorksheetDrawing();
            TwoCellAnchor twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild <TwoCellAnchor>(new TwoCellAnchor());

            // Chart position
            twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker(new ColumnId(location.ColumnIndex.ToString()),
                                                                                           new ColumnOffset("0"),
                                                                                           new RowId(location.RowIndex.ToString()),
                                                                                           new RowOffset("114300")));
            twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.ToMarker(new ColumnId((location.ColumnIndex + 12).ToString()),
                                                                                         new ColumnOffset("0"),
                                                                                         new RowId((location.RowIndex + 15).ToString()),
                                                                                         new RowOffset("0")));

            DocumentFormat.OpenXml.Drawing.Spreadsheet.GraphicFrame graphicFrame =
                twoCellAnchor.AppendChild <DocumentFormat.OpenXml.
                                           Drawing.Spreadsheet.GraphicFrame>(new DocumentFormat.OpenXml.Drawing.
                                                                             Spreadsheet.GraphicFrame());
            graphicFrame.Macro = "";

            // Chart name
            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());
        }
        protected static void AppendGraphicFrame(DrawingsPart drawingsPart, ChartPart chartPart)
        {
            TwoCellAnchor twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild(new TwoCellAnchor());

            twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker(new ColumnId("2"),
                                                                                           new ColumnOffset("158233"),
                                                                                           new RowId("2"),
                                                                                           new RowOffset("16894")));
            twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.ToMarker(new ColumnId("17"),
                                                                                         new ColumnOffset("276225"),
                                                                                         new RowId("32"),
                                                                                         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(3u),
                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());
        }
예제 #4
0
        private void LoadImage(Worksheet ws, WorksheetPart wsp, WorkbookPart wp)
        {
            string       path = System.IO.Path.Combine(Server.MapPath("~/Scripts/Images"), "unnamed.png").ToString();
            DrawingsPart dp   = wsp.AddNewPart <DrawingsPart>();
            ImagePart    imgp = dp.AddImagePart(ImagePartType.Png, wsp.GetIdOfPart(dp));

            using (FileStream fs = new FileStream(path, FileMode.Open))
            {
                imgp.FeedData(fs);
            }

            NonVisualDrawingProperties nvdp = new NonVisualDrawingProperties();

            nvdp.Id          = 1025;
            nvdp.Name        = "Picture 1";
            nvdp.Description = "polymathlogo";
            DocumentFormat.OpenXml.Drawing.PictureLocks picLocks = new DocumentFormat.OpenXml.Drawing.PictureLocks();
            picLocks.NoChangeAspect     = true;
            picLocks.NoChangeArrowheads = true;
            NonVisualPictureDrawingProperties nvpdp = new NonVisualPictureDrawingProperties();

            nvpdp.PictureLocks = picLocks;
            NonVisualPictureProperties nvpp = new NonVisualPictureProperties();

            nvpp.NonVisualDrawingProperties        = nvdp;
            nvpp.NonVisualPictureDrawingProperties = nvpdp;

            DocumentFormat.OpenXml.Drawing.Stretch stretch = new DocumentFormat.OpenXml.Drawing.Stretch();
            stretch.FillRectangle = new DocumentFormat.OpenXml.Drawing.FillRectangle();

            BlipFill blipFill = new BlipFill();

            DocumentFormat.OpenXml.Drawing.Blip blip = new DocumentFormat.OpenXml.Drawing.Blip();
            blip.Embed               = dp.GetIdOfPart(imgp);
            blip.CompressionState    = DocumentFormat.OpenXml.Drawing.BlipCompressionValues.Print;
            blipFill.Blip            = blip;
            blipFill.SourceRectangle = new DocumentFormat.OpenXml.Drawing.SourceRectangle();
            blipFill.Append(stretch);

            DocumentFormat.OpenXml.Drawing.Transform2D t2d    = new DocumentFormat.OpenXml.Drawing.Transform2D();
            DocumentFormat.OpenXml.Drawing.Offset      offset = new DocumentFormat.OpenXml.Drawing.Offset();
            offset.X   = 0;
            offset.Y   = 0;
            t2d.Offset = offset;
            System.Drawing.Bitmap bm = new System.Drawing.Bitmap(path);
            //http://en.wikipedia.org/wiki/English_Metric_Unit#DrawingML
            //http://stackoverflow.com/questions/1341930/pixel-to-centimeter
            //http://stackoverflow.com/questions/139655/how-to-convert-pixels-to-points-px-to-pt-in-net-c
            DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents();
            extents.Cx = (long)bm.Width * (long)((float)914400 / bm.HorizontalResolution);
            extents.Cy = (long)bm.Height * (long)((float)914400 / bm.VerticalResolution);
            bm.Dispose();
            t2d.Extents = extents;
            ShapeProperties sp = new ShapeProperties();

            sp.BlackWhiteMode = DocumentFormat.OpenXml.Drawing.BlackWhiteModeValues.Auto;
            sp.Transform2D    = t2d;
            DocumentFormat.OpenXml.Drawing.PresetGeometry prstGeom = new DocumentFormat.OpenXml.Drawing.PresetGeometry();
            prstGeom.Preset          = DocumentFormat.OpenXml.Drawing.ShapeTypeValues.Rectangle;
            prstGeom.AdjustValueList = new DocumentFormat.OpenXml.Drawing.AdjustValueList();
            sp.Append(prstGeom);
            sp.Append(new DocumentFormat.OpenXml.Drawing.NoFill());

            DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture picture = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture();
            picture.NonVisualPictureProperties = nvpp;
            picture.BlipFill        = blipFill;
            picture.ShapeProperties = sp;

            Position pos = new Position();

            pos.X = 0;
            pos.Y = 0;
            Extent ext = new Extent();

            ext.Cx = extents.Cx;
            ext.Cy = extents.Cy;
            AbsoluteAnchor anchor = new AbsoluteAnchor();

            anchor.Position = pos;
            anchor.Extent   = ext;
            anchor.Append(picture);
            anchor.Append(new ClientData());
            WorksheetDrawing wsd = new WorksheetDrawing();

            wsd.Append(anchor);
            Drawing drawing = new Drawing();

            drawing.Id = dp.GetIdOfPart(imgp);

            wsd.Save(dp);
            ws.Append(drawing);
        }
        private static void InsertChartInSpreadsheet(string docName, string worksheetName, string title,
                                                     Dictionary <string, int> data)
        {
            // Open the document for editing.
            using (SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true))
            {
                IEnumerable <Sheet> sheets = document.WorkbookPart.Workbook.Descendants <Sheet>().
                                             Where(s => s.Name == worksheetName);
                if (sheets.Count() == 0)
                {
                    // The specified worksheet does not exist.
                    return;
                }
                WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);

                // 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();

                // Add a new chart and set the chart language to English-US.
                ChartPart chartPart = drawingsPart.AddNewPart <ChartPart>();
                chartPart.ChartSpace = new ChartSpace();
                chartPart.ChartSpace.Append(new EditingLanguage()
                {
                    Val = new StringValue("en-US")
                });
                DocumentFormat.OpenXml.Drawing.Charts.Chart chart = chartPart.ChartSpace.AppendChild <DocumentFormat.OpenXml.Drawing.Charts.Chart>(
                    new DocumentFormat.OpenXml.Drawing.Charts.Chart());

                // Create a new clustered column chart.
                PlotArea plotArea = chart.AppendChild <PlotArea>(new PlotArea());
                Layout   layout   = plotArea.AppendChild <Layout>(new Layout());
                BarChart barChart = plotArea.AppendChild <BarChart>(new BarChart(new BarDirection()
                {
                    Val = new EnumValue <BarDirectionValues>(BarDirectionValues.Column)
                },
                                                                                 new BarGrouping()
                {
                    Val = new EnumValue <BarGroupingValues>(BarGroupingValues.Clustered)
                }));

                uint i = 0;

                // Iterate through each key in the Dictionary collection and add the key to the chart Series
                // and add the corresponding value to the chart Values.
                foreach (string key in data.Keys)
                {
                    BarChartSeries barChartSeries = barChart.AppendChild <BarChartSeries>(new BarChartSeries(new Index()
                    {
                        Val =
                            new UInt32Value(i)
                    },
                                                                                                             new Order()
                    {
                        Val = new UInt32Value(i)
                    },
                                                                                                             new SeriesText(new NumericValue()
                    {
                        Text = key
                    })));

                    StringLiteral strLit = barChartSeries.AppendChild <CategoryAxisData>(new CategoryAxisData()).AppendChild <StringLiteral>(new StringLiteral());
                    strLit.Append(new PointCount()
                    {
                        Val = new UInt32Value(1U)
                    });
                    strLit.AppendChild <StringPoint>(new StringPoint()
                    {
                        Index = new UInt32Value(0U)
                    }).Append(new NumericValue(key));

                    NumberLiteral numLit = barChartSeries.AppendChild <DocumentFormat.OpenXml.Drawing.Charts.Values>(
                        new DocumentFormat.OpenXml.Drawing.Charts.Values()).AppendChild <NumberLiteral>(new NumberLiteral());
                    numLit.Append(new FormatCode("General"));
                    numLit.Append(new PointCount()
                    {
                        Val = new UInt32Value(1U)
                    });
                    numLit.AppendChild <NumericPoint>(new NumericPoint()
                    {
                        Index = new UInt32Value(0u)
                    })
                    .Append(new NumericValue(data[key].ToString()));



                    i++;
                }

                barChart.Append(new AxisId()
                {
                    Val = new UInt32Value(48650112u)
                });
                barChart.Append(new AxisId()
                {
                    Val = new UInt32Value(48672768u)
                });

                //// Add the Category Axis.
                CategoryAxis catAx = 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.Right)
                },
                                                                      new Layout()));

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

                // Save the chart part.
                chartPart.ChartSpace.Save();

                // 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("1"),
                                                                                               new ColumnOffset("581025"),
                                                                                               new RowId("1"),
                                                                                               new RowOffset("114300")));
                twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.ToMarker(new ColumnId("10"),
                                                                                             new ColumnOffset("276225"),
                                                                                             new RowId("16"),
                                                                                             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());

                // Save the WorksheetDrawing object.
                drawingsPart.WorksheetDrawing.Save();
            }
        }
예제 #6
0
        private static void GeneratePartContentPie(DrawingsPart part, ChartPart chartPart1, int row, bool newe)
        {
            Xdr.WorksheetDrawing worksheetDrawing1 = new Xdr.WorksheetDrawing();

            if (newe)
            {
                worksheetDrawing1.AddNamespaceDeclaration("xdr", "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing");
                worksheetDrawing1.AddNamespaceDeclaration("a", "http://schemas.openxmlformats.org/drawingml/2006/main");
                worksheetDrawing1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
                worksheetDrawing1.AddNamespaceDeclaration("c", "http://schemas.openxmlformats.org/drawingml/2006/chart");
                worksheetDrawing1.AddNamespaceDeclaration("cx", "http://schemas.microsoft.com/office/drawing/2014/chartex");
                worksheetDrawing1.AddNamespaceDeclaration("cx1", "http://schemas.microsoft.com/office/drawing/2015/9/8/chartex");
                worksheetDrawing1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
                worksheetDrawing1.AddNamespaceDeclaration("dgm", "http://schemas.openxmlformats.org/drawingml/2006/diagram");
            }

            Xdr.OneCellAnchor oneCellAnchor1 = new Xdr.OneCellAnchor();

            Xdr.FromMarker fromMarker1 = new Xdr.FromMarker();
            Xdr.ColumnId   columnId1   = new Xdr.ColumnId();
            columnId1.Text = "0";
            Xdr.ColumnOffset columnOffset1 = new Xdr.ColumnOffset();
            columnOffset1.Text = "0";
            Xdr.RowId rowId1 = new Xdr.RowId();
            rowId1.Text = row.ToString();
            Xdr.RowOffset rowOffset1 = new Xdr.RowOffset();
            rowOffset1.Text = "0";

            fromMarker1.Append(columnId1);
            fromMarker1.Append(columnOffset1);
            fromMarker1.Append(rowId1);
            fromMarker1.Append(rowOffset1);

            Xdr.Extent extent1 = new Xdr.Extent()
            {
                Cx = 5743575L, Cy = 2667000L
            };



            Xdr.GraphicFrame graphicFrame1 = new Xdr.GraphicFrame();

            Xdr.NonVisualGraphicFrameProperties nonVisualGraphicFrameProperties1 = new Xdr.NonVisualGraphicFrameProperties();
            Xdr.NonVisualDrawingProperties      nonVisualDrawingProperties1      = new Xdr.NonVisualDrawingProperties()
            {
                Id = (UInt32Value)(uint)row, Name = "Chart " + row.ToString()
            };
            Xdr.NonVisualGraphicFrameDrawingProperties nonVisualGraphicFrameDrawingProperties1 = new Xdr.NonVisualGraphicFrameDrawingProperties();

            nonVisualGraphicFrameProperties1.Append(nonVisualDrawingProperties1);
            nonVisualGraphicFrameProperties1.Append(nonVisualGraphicFrameDrawingProperties1);

            Xdr.Transform transform1 = new Xdr.Transform();
            A.Offset      offset1    = new A.Offset()
            {
                X = 0L, Y = 0L
            };
            A.Extents extents1 = new A.Extents()
            {
                Cx = 0L, Cy = 0L
            };

            transform1.Append(offset1);
            transform1.Append(extents1);

            A.Graphic graphic1 = new A.Graphic();

            A.GraphicData graphicData1 = new A.GraphicData()
            {
                Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart"
            };
            C.ChartReference chartReference1 = new C.ChartReference()
            {
                Id = part.GetIdOfPart(chartPart1)
            };

            graphicData1.Append(chartReference1);

            graphic1.Append(graphicData1);

            graphicFrame1.Append(nonVisualGraphicFrameProperties1);
            graphicFrame1.Append(transform1);
            graphicFrame1.Append(graphic1);
            Xdr.ClientData clientData1 = new Xdr.ClientData()
            {
                LockWithSheet = false
            };

            oneCellAnchor1.Append(fromMarker1);
            oneCellAnchor1.Append(extent1);
            oneCellAnchor1.Append(graphicFrame1);
            oneCellAnchor1.Append(clientData1);

            if (newe)
            {
                worksheetDrawing1.Append(oneCellAnchor1);

                part.WorksheetDrawing = worksheetDrawing1;
            }
            else
            {
                part.WorksheetDrawing.Append(oneCellAnchor1);
            }
        }
예제 #7
0
        //gavdcodeend 10

        //gavdcodebegin 11
        private static void ExcelOpenXmlInsertChart()
        {
            Dictionary <string, int> chartData = new Dictionary <string, int>();

            chartData.Add("abc", 1);
            chartData.Add("def", 2);
            chartData.Add("ghi", 1);

            using (SpreadsheetDocument myExcelDoc =
                       SpreadsheetDocument.Open(@"C:\Temporary\ExcelDoc01.xlsx", true))
            {
                // Use the name of the sheet
                IEnumerable <Sheet> mySheets = myExcelDoc.WorkbookPart.Workbook.
                                               Descendants <Sheet>().Where(s => s.Name == "NewSheet");
                if (mySheets.Count() == 0)
                {
                    return;
                }
                WorksheetPart myWorksheetPart =
                    (WorksheetPart)myExcelDoc.WorkbookPart.
                    GetPartById(mySheets.First().Id);

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

                // Add a new chart and set the chart language to English-US
                ChartPart myChartPart = myDrawingsPart.AddNewPart <ChartPart>();
                myChartPart.ChartSpace = new DrCh.ChartSpace();
                myChartPart.ChartSpace.Append(new DrCh.EditingLanguage()
                {
                    Val = new StringValue("en-US")
                });
                DrCh.Chart myChart =
                    myChartPart.ChartSpace.AppendChild <DrCh.Chart>(new DrCh.Chart());

                // Create a new clustered column chart
                DrCh.PlotArea plotArea = myChart.AppendChild <DrCh.PlotArea>(
                    new DrCh.PlotArea());
                DrCh.Layout   layout   = plotArea.AppendChild <DrCh.Layout>(new DrCh.Layout());
                DrCh.BarChart barChart = plotArea.AppendChild <DrCh.BarChart>(
                    new DrCh.BarChart(new DrCh.BarDirection()
                {
                    Val = new EnumValue <DrCh.BarDirectionValues>
                              (DrCh.BarDirectionValues.Column)
                },
                                      new DrCh.BarGrouping()
                {
                    Val = new EnumValue <DrCh.BarGroupingValues>
                              (DrCh.BarGroupingValues.Clustered)
                }));

                uint myIndex = 0;

                foreach (string oneKey in chartData.Keys)
                {
                    DrCh.BarChartSeries barChartSeries = barChart.AppendChild
                                                         <DrCh.BarChartSeries>(new DrCh.BarChartSeries(new DrCh.Index()
                    {
                        Val = new UInt32Value(myIndex)
                    },
                                                                                                       new DrCh.Order()
                    {
                        Val = new UInt32Value(myIndex)
                    },
                                                                                                       new DrCh.SeriesText(new DrCh.NumericValue()
                    {
                        Text = oneKey
                    })));

                    DrCh.StringLiteral strLit = barChartSeries.AppendChild <DrCh.
                                                                            CategoryAxisData>(new DrCh.CategoryAxisData()).
                                                AppendChild <DrCh.StringLiteral>(new DrCh.StringLiteral());
                    strLit.Append(new DrCh.PointCount()
                    {
                        Val = new UInt32Value(1U)
                    });
                    // Use the title for the graphic
                    strLit.AppendChild <DrCh.StringPoint>(new DrCh.StringPoint()
                    {
                        Index = new UInt32Value(0U)
                    }).
                    Append(new DrCh.NumericValue("My New Graphic"));

                    DrCh.NumberLiteral numLit = barChartSeries.AppendChild
                                                <DocumentFormat.OpenXml.Drawing.Charts.Values>(
                        new DocumentFormat.OpenXml.Drawing.Charts.Values()).
                                                AppendChild <DrCh.NumberLiteral>(new DrCh.NumberLiteral());
                    numLit.Append(new DrCh.FormatCode("General"));
                    numLit.Append(new DrCh.PointCount()
                    {
                        Val = new UInt32Value(1U)
                    });
                    numLit.AppendChild <DrCh.NumericPoint>(new DrCh.NumericPoint()
                    {
                        Index = new UInt32Value(0u)
                    }).Append
                        (new DrCh.NumericValue(chartData[oneKey].ToString()));

                    myIndex++;
                }

                barChart.Append(new DrCh.AxisId()
                {
                    Val = new UInt32Value(48650112u)
                });
                barChart.Append(new DrCh.AxisId()
                {
                    Val = new UInt32Value(48672768u)
                });

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

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

                // Add the chart Legend.
                DrCh.Legend myLegend = myChart.AppendChild <DrCh.Legend>(
                    new DrCh.Legend(new DrCh.LegendPosition()
                {
                    Val = new EnumValue <DrCh.LegendPositionValues>
                              (DrCh.LegendPositionValues.Right)
                },
                                    new DrCh.Layout()));

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

                myChartPart.ChartSpace.Save();

                // Position the chart on the worksheet using a TwoCellAnchor object.
                myDrawingsPart.WorksheetDrawing = new DrSp.WorksheetDrawing();
                DrSp.TwoCellAnchor twoCellAnchor = myDrawingsPart.WorksheetDrawing.
                                                   AppendChild <DrSp.TwoCellAnchor>(
                    new DrSp.TwoCellAnchor());
                twoCellAnchor.Append(new DrSp.FromMarker(new DrSp.ColumnId("9"),
                                                         new DrSp.ColumnOffset("581025"),
                                                         new DrSp.RowId("17"),
                                                         new DrSp.RowOffset("114300")));
                twoCellAnchor.Append(new DrSp.ToMarker(new DrSp.ColumnId("17"),
                                                       new DrSp.ColumnOffset("276225"),
                                                       new DrSp.RowId("32"),
                                                       new DrSp.RowOffset("0")));

                // Append a GraphicFrame to the TwoCellAnchor object.
                DrSp.GraphicFrame myGraphicFrame =
                    twoCellAnchor.AppendChild <DrSp.GraphicFrame>(new DrSp.GraphicFrame());
                myGraphicFrame.Macro = "";

                myGraphicFrame.Append(new DrSp.NonVisualGraphicFrameProperties(
                                          new DrSp.NonVisualDrawingProperties()
                {
                    Id   = new UInt32Value(2u),
                    Name = "Chart 1"
                },
                                          new DrSp.NonVisualGraphicFrameDrawingProperties()));

                myGraphicFrame.Append(new DrSp.Transform(
                                          new DocumentFormat.OpenXml.Drawing.Offset()
                {
                    X = 0L,
                    Y = 0L
                },
                                          new DocumentFormat.OpenXml.Drawing.Extents()
                {
                    Cx = 0L,
                    Cy = 0L
                }));

                myGraphicFrame.Append(
                    new DocumentFormat.OpenXml.Drawing.Graphic(
                        new DocumentFormat.OpenXml.Drawing.GraphicData(
                            new DrCh.ChartReference()
                {
                    Id = myDrawingsPart.GetIdOfPart(myChartPart)
                })
                {
                    Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart"
                }));

                twoCellAnchor.Append(new DrSp.ClientData());

                myDrawingsPart.WorksheetDrawing.Save();
            }
        }
예제 #8
0
        public void CreateExcelDoc(string fileName)
        {
            List <Student> students = new List <Student>();

            Initizalize(students);

            using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
            {
                WorkbookPart workbookPart = document.AddWorkbookPart();
                workbookPart.Workbook = new Workbook();

                WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
                worksheetPart.Worksheet = new Worksheet();

                Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());

                Sheet sheet = new Sheet()
                {
                    Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Students"
                };

                SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());

                // Add drawing part to WorksheetPart
                DrawingsPart drawingsPart = worksheetPart.AddNewPart <DrawingsPart>();
                worksheetPart.Worksheet.Append(new Drawing()
                {
                    Id = worksheetPart.GetIdOfPart(drawingsPart)
                });
                worksheetPart.Worksheet.Save();

                drawingsPart.WorksheetDrawing = new WorksheetDrawing();

                sheets.Append(sheet);

                workbookPart.Workbook.Save();

                // Add a new chart and set the chart language
                ChartPart chartPart = drawingsPart.AddNewPart <ChartPart>();
                chartPart.ChartSpace = new ChartSpace();
                chartPart.ChartSpace.AppendChild(new EditingLanguage()
                {
                    Val = "en-US"
                });
                Chart chart = chartPart.ChartSpace.AppendChild(new Chart());
                chart.AppendChild(new AutoTitleDeleted()
                {
                    Val = true
                });                                                       // We don't want to show the chart title

                // Create a new Clustered Column Chart
                PlotArea plotArea = chart.AppendChild(new PlotArea());
                Layout   layout   = plotArea.AppendChild(new Layout());

                BarChart barChart = plotArea.AppendChild(new BarChart(
                                                             new BarDirection()
                {
                    Val = new EnumValue <BarDirectionValues>(BarDirectionValues.Column)
                },
                                                             new BarGrouping()
                {
                    Val = new EnumValue <BarGroupingValues>(BarGroupingValues.Clustered)
                },
                                                             new VaryColors()
                {
                    Val = false
                }
                                                             ));

                // Constructing header
                Row row      = new Row();
                int rowIndex = 1;

                row.AppendChild(ConstructCell(string.Empty, CellValues.String));

                foreach (var month in Months.Short)
                {
                    row.AppendChild(ConstructCell(month, CellValues.String));
                }

                // Insert the header row to the Sheet Data
                sheetData.AppendChild(row);
                rowIndex++;

                // Create chart series
                for (int i = 0; i < students.Count; i++)
                {
                    BarChartSeries barChartSeries = barChart.AppendChild(new BarChartSeries(
                                                                             new Index()
                    {
                        Val = (uint)i
                    },
                                                                             new Order()
                    {
                        Val = (uint)i
                    },
                                                                             new SeriesText(new NumericValue()
                    {
                        Text = students[i].Name
                    })
                                                                             ));

                    // Adding category axis to the chart
                    CategoryAxisData categoryAxisData = barChartSeries.AppendChild(new CategoryAxisData());

                    // Category
                    // Constructing the chart category
                    string formulaCat = "Students!$B$1:$G$1";

                    StringReference stringReference = categoryAxisData.AppendChild(new StringReference()
                    {
                        Formula = new DocumentFormat.OpenXml.Drawing.Charts.Formula()
                        {
                            Text = formulaCat
                        }
                    });

                    StringCache stringCache = stringReference.AppendChild(new StringCache());
                    stringCache.Append(new PointCount()
                    {
                        Val = (uint)Months.Short.Length
                    });

                    for (int j = 0; j < Months.Short.Length; j++)
                    {
                        stringCache.AppendChild(new NumericPoint()
                        {
                            Index = (uint)j
                        }).Append(new NumericValue(Months.Short[j]));
                    }
                }

                var chartSeries = barChart.Elements <BarChartSeries>().GetEnumerator();

                for (int i = 0; i < students.Count; i++)
                {
                    row = new Row();

                    row.AppendChild(ConstructCell(students[i].Name, CellValues.String));

                    chartSeries.MoveNext();

                    string formulaVal = string.Format("Students!$B${0}:$G${0}", rowIndex);
                    DocumentFormat.OpenXml.Drawing.Charts.Values values = chartSeries.Current.AppendChild(new DocumentFormat.OpenXml.Drawing.Charts.Values());

                    NumberReference numberReference = values.AppendChild(new NumberReference()
                    {
                        Formula = new DocumentFormat.OpenXml.Drawing.Charts.Formula()
                        {
                            Text = formulaVal
                        }
                    });

                    NumberingCache numberingCache = numberReference.AppendChild(new NumberingCache());
                    numberingCache.Append(new PointCount()
                    {
                        Val = (uint)Months.Short.Length
                    });

                    for (uint j = 0; j < students[i].Values.Length; j++)
                    {
                        var value = students[i].Values[j];

                        row.AppendChild(ConstructCell(value.ToString(), CellValues.Number));

                        numberingCache.AppendChild(new NumericPoint()
                        {
                            Index = j
                        }).Append(new NumericValue(value.ToString()));
                    }

                    sheetData.AppendChild(row);
                    rowIndex++;
                }

                barChart.AppendChild(new DataLabels(
                                         new ShowLegendKey()
                {
                    Val = false
                },
                                         new ShowValue()
                {
                    Val = false
                },
                                         new ShowCategoryName()
                {
                    Val = false
                },
                                         new ShowSeriesName()
                {
                    Val = false
                },
                                         new ShowPercent()
                {
                    Val = false
                },
                                         new ShowBubbleSize()
                {
                    Val = false
                }
                                         ));

                barChart.Append(new AxisId()
                {
                    Val = 48650112u
                });
                barChart.Append(new AxisId()
                {
                    Val = 48672768u
                });

                // Adding Category Axis
                plotArea.AppendChild(
                    new CategoryAxis(
                        new AxisId()
                {
                    Val = 48650112u
                },
                        new Scaling(new Orientation()
                {
                    Val = new EnumValue <DocumentFormat.OpenXml.Drawing.Charts.OrientationValues>(DocumentFormat.OpenXml.Drawing.Charts.OrientationValues.MinMax)
                }),
                        new Delete()
                {
                    Val = false
                },
                        new AxisPosition()
                {
                    Val = new EnumValue <AxisPositionValues>(AxisPositionValues.Bottom)
                },
                        new TickLabelPosition()
                {
                    Val = new EnumValue <TickLabelPositionValues>(TickLabelPositionValues.NextTo)
                },
                        new CrossingAxis()
                {
                    Val = 48672768u
                },
                        new Crosses()
                {
                    Val = new EnumValue <CrossesValues>(CrossesValues.AutoZero)
                },
                        new AutoLabeled()
                {
                    Val = true
                },
                        new LabelAlignment()
                {
                    Val = new EnumValue <LabelAlignmentValues>(LabelAlignmentValues.Center)
                }
                        ));

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

                chart.Append(
                    new PlotVisibleOnly()
                {
                    Val = true
                },
                    new DisplayBlanksAs()
                {
                    Val = new EnumValue <DisplayBlanksAsValues>(DisplayBlanksAsValues.Gap)
                },
                    new ShowDataLabelsOverMaximum()
                {
                    Val = false
                }
                    );

                chartPart.ChartSpace.Save();

                // Positioning the chart on the spreadsheet
                TwoCellAnchor twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild(new TwoCellAnchor());

                twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.FromMarker(
                                         new ColumnId("0"),
                                         new ColumnOffset("0"),
                                         new RowId((rowIndex + 2).ToString()),
                                         new RowOffset("0")
                                         ));

                twoCellAnchor.Append(new DocumentFormat.OpenXml.Drawing.Spreadsheet.ToMarker(
                                         new ColumnId("8"),
                                         new ColumnOffset("0"),
                                         new RowId((rowIndex + 12).ToString()),
                                         new RowOffset("0")
                                         ));

                // Append GraphicFrame to TwoCellAnchor
                GraphicFrame graphicFrame = twoCellAnchor.AppendChild(new GraphicFrame());
                graphicFrame.Macro = string.Empty;

                graphicFrame.Append(new NonVisualGraphicFrameProperties(
                                        new NonVisualDrawingProperties()
                {
                    Id   = 2u,
                    Name = "Sample Chart"
                },
                                        new NonVisualGraphicFrameDrawingProperties()
                                        ));

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

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

                twoCellAnchor.Append(new ClientData());

                drawingsPart.WorksheetDrawing.Save();

                worksheetPart.Worksheet.Save();
            }
        }
예제 #9
0
        private void CreateDrawingBySAX(DrawingsPart drawingsPart)
        {
            var data    = _data as IReadOnlyList <Sheet3Data>;
            var dataDic = new Dictionary <C.Values, C.SeriesText>();

            for (uint i = 1; i <= data.First().DataDic.Count; i++)
            {
                var columnName = OpenXMLExcels.GetColumnNameByIndex(i);
                dataDic[new C.Values()
                        {
                            NumberReference = new C.NumberReference()
                            {
                                Formula = new C.Formula($"{_sheetName}!${columnName}$2:${columnName}${data.Count + 2}")
                            }
                        }]
                    = new C.SeriesText()
                    {
                    StringReference = new C.StringReference()
                    {
                        Formula = new C.Formula($"{_sheetName}!${columnName}$1")
                    }
                    };
            }

            var chartPart = drawingsPart.AddNewPart <ChartPart>();

            drawingsPart.WorksheetDrawing = new Xdr.WorksheetDrawing();
            var twoCellAnchor = drawingsPart.WorksheetDrawing.AppendChild(new Xdr.TwoCellAnchor()
            {
                FromMarker = new Xdr.FromMarker()
                {
                    ColumnId     = new Xdr.ColumnId("5"),
                    ColumnOffset = new Xdr.ColumnOffset("581025"),
                    RowId        = new Xdr.RowId("4"),
                    RowOffset    = new Xdr.RowOffset("114300")
                },
                ToMarker = new Xdr.ToMarker()
                {
                    ColumnId     = new Xdr.ColumnId("13"),
                    ColumnOffset = new Xdr.ColumnOffset("276225"),
                    RowId        = new Xdr.RowId("19"),
                    RowOffset    = new Xdr.RowOffset("0")
                }
            });

            twoCellAnchor.Append(new Xdr.GraphicFrame()
            {
                NonVisualGraphicFrameProperties = new Xdr.NonVisualGraphicFrameProperties()
                {
                    NonVisualDrawingProperties = new Xdr.NonVisualDrawingProperties()
                    {
                        Id = 2, Name = "Chart 1", Title = "产品每月产量折线图"
                    },
                    NonVisualGraphicFrameDrawingProperties = new Xdr.NonVisualGraphicFrameDrawingProperties()
                },
                Transform = new Xdr.Transform()
                {
                    Offset = new Offset()
                    {
                        X = 0, Y = 0
                    },
                    Extents = new Extents()
                    {
                        Cx = 0, Cy = 0
                    }
                },
                Graphic = new Graphic(new GraphicData(new C.ChartReference()
                {
                    Id = drawingsPart.GetIdOfPart(chartPart)
                })
                {
                    Uri = "http://schemas.openxmlformats.org/drawingml/2006/chart",
                })
            });
            twoCellAnchor.Append(new Xdr.ClientData());

            using (var writer = OpenXmlWriter.Create(chartPart))
            {
                //S: ChartSpace
                writer.WriteStartElement(new C.ChartSpace());
                writer.WriteElement(new C.EditingLanguage()
                {
                    Val = "zh-CN"
                });

                //S: Chart
                writer.WriteStartElement(new C.Chart());

                //S: PlotArea
                writer.WriteStartElement(new C.PlotArea());
                writer.WriteElement(new C.Layout());

                //S: LineChart
                writer.WriteStartElement(new C.LineChart());
                writer.WriteElement(new C.Grouping()
                {
                    Val = C.GroupingValues.Standard
                });

                uint index = 0;
                foreach (var dataKvp in dataDic)
                {
                    //S: LineChartSeries
                    writer.WriteStartElement(new C.LineChartSeries());
                    writer.WriteElement(dataKvp.Key);
                    writer.WriteElement(new C.Index()
                    {
                        Val = index
                    });
                    writer.WriteElement(new C.Order()
                    {
                        Val = index
                    });
                    writer.WriteElement(dataKvp.Value);
                    if (index++ == 0)
                    {
                        var axisData = new C.CategoryAxisData()
                        {
                            StringReference = new C.StringReference()
                            {
                                Formula = new C.Formula($"{_sheetName}!$A$2:$A${data.Count + 2}")
                            }
                        };

                        writer.WriteElement(axisData);
                    }
                    //E: LineChartSeries
                    writer.WriteEndElement();
                }

                writer.WriteElement(new C.AxisId()
                {
                    Val = 0
                });
                writer.WriteElement(new C.AxisId()
                {
                    Val = 1
                });

                //E: LineChart
                writer.WriteEndElement();

                writer.WriteElement(new C.CategoryAxis(
                                        new C.Crosses()
                {
                    Val = C.CrossesValues.AutoZero
                },
                                        new C.AutoLabeled()
                {
                    Val = true
                },
                                        new C.LabelAlignment()
                {
                    Val = C.LabelAlignmentValues.Center
                },
                                        new C.LabelOffset()
                {
                    Val = 100
                })
                {
                    AxisId = new C.AxisId()
                    {
                        Val = 0
                    },
                    Scaling = new C.Scaling(new C.Orientation()
                    {
                        Val = C.OrientationValues.MinMax
                    }),
                    AxisPosition = new C.AxisPosition()
                    {
                        Val = C.AxisPositionValues.Bottom
                    },
                    TickLabelPosition = new C.TickLabelPosition()
                    {
                        Val = C.TickLabelPositionValues.NextTo
                    },
                    CrossingAxis = new C.CrossingAxis()
                    {
                        Val = 1
                    },
                });

                writer.WriteElement(new C.ValueAxis(
                                        new C.Crosses()
                {
                    Val = C.CrossesValues.AutoZero
                },
                                        new C.CrossBetween()
                {
                    Val = C.CrossBetweenValues.Between
                })
                {
                    AxisId = new C.AxisId()
                    {
                        Val = 1
                    },
                    Scaling = new C.Scaling(new C.Orientation()
                    {
                        Val = C.OrientationValues.MinMax
                    }),
                    AxisPosition = new C.AxisPosition()
                    {
                        Val = C.AxisPositionValues.Left
                    },
                    MajorGridlines  = new C.MajorGridlines(),
                    NumberingFormat = new C.NumberingFormat()
                    {
                        FormatCode = "General", SourceLinked = true
                    },
                    TickLabelPosition = new C.TickLabelPosition()
                    {
                        Val = C.TickLabelPositionValues.NextTo
                    },
                    CrossingAxis = new C.CrossingAxis()
                    {
                        Val = 0
                    },
                });
                //E: PlotArea
                writer.WriteEndElement();

                writer.WriteElement(new C.Legend(
                                        new C.LegendPosition()
                {
                    Val = C.LegendPositionValues.Right
                },
                                        new C.Layout()
                                        ));
                writer.WriteElement(new C.PlotVisibleOnly()
                {
                    Val = true
                });
                //E: Chart
                writer.WriteEndElement();
                //E: ChartSpace
                writer.WriteEndElement();

                writer.Close();
            }
        }
예제 #10
0
        public static ChartPart cloneChart(WorksheetPart worksheetPart, string chartTitle, int posLi, int posCol, int largeLi, int largeCol)
        {
            //Obtention de tous les ChartParts dans une enumeration
            DrawingsPart            dp  = worksheetPart.DrawingsPart;
            IEnumerable <ChartPart> cps = dp.ChartParts;

            //Recherche la ChartPart qui porte le titre correspondant
            ChartPart b = null;

            foreach (ChartPart cp in cps)
            {
                if (cp.ChartSpace.Descendants <Chart.Chart>().First().Descendants <Chart.Title>().First().Descendants <DocumentFormat.OpenXml.Drawing.Run>().First().Text.Text == chartTitle)
                {
                    b = cp;
                }
            }

            DrawingsPart a = worksheetPart.DrawingsPart;

            //Ajout de la nouvelle partie et copie
            ChartPart x      = a.AddNewPart <ChartPart>();
            Stream    stream = b.GetStream();

            x.FeedData(stream);

            string id = a.GetIdOfPart(b);

            //Copie de l'ancre associée au graph original
            Draw.TwoCellAnchor newAnchor = null;
            DocumentFormat.OpenXml.Drawing.Spreadsheet.WorksheetDrawing wsd = a.WorksheetDrawing;
            foreach (Draw.TwoCellAnchor tca in wsd.Elements <Draw.TwoCellAnchor>())
            {
                string tmp = tca.Descendants <Chart.ChartReference>().First().Id;
                if (tmp == id)
                {
                    newAnchor = (Draw.TwoCellAnchor)tca.CloneNode(true);
                }
            }

            //positionnement de la nouvelle ancre
            int r = Convert.ToInt32(newAnchor.ToMarker.RowId.Text) - Convert.ToInt32(newAnchor.FromMarker.RowId.Text);
            int c = Convert.ToInt32(newAnchor.ToMarker.ColumnId.Text) - Convert.ToInt32(newAnchor.FromMarker.ColumnId.Text);

            newAnchor.FromMarker.ColumnId = new Draw.ColumnId()
            {
                Text = "" + largeCol * posCol
            };
            newAnchor.ToMarker.ColumnId = new Draw.ColumnId()
            {
                Text = "" + (c + largeCol * posCol)
            };

            newAnchor.FromMarker.RowId = new Draw.RowId()
            {
                Text = "" + largeLi * posLi
            };
            newAnchor.ToMarker.RowId = new Draw.RowId()
            {
                Text = "" + (r + largeLi * posLi)
            };
            newAnchor.Descendants <Chart.ChartReference>().First().Id = a.GetIdOfPart(x);
            wsd.Append(newAnchor);

            return(x);
        }
예제 #11
0
        /// <summary>
        /// draw the 2D bar chart
        /// index start from 1
        /// </summary>
        /// <param name="startx">index start from 1 for row</param>
        /// <param name="starty">index start from 1 for column</param>
        /// <param name="columnCount"></param>
        /// <param name="rowCount"></param>
        public void InsertChartInSpreadsheet(WorksheetPart sheetpart, string sheetName, int startx, int starty, int columnCount, int rowCount, int chart_pointx, int chart_pointy)
        {
            WorksheetPart worksheetPart = CurrentWorksheetPart;

            #region SDK How to example code
            // 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();
            // Add a new chart and set the chart language to English-US.
            ChartPart chartPart = drawingsPart.AddNewPart <ChartPart>();
            chartPart.ChartSpace = new ChartSpace();
            chartPart.ChartSpace.Append(new EditingLanguage()
            {
                Val = new StringValue("en-US")
            });
            DocumentFormat.OpenXml.Drawing.Charts.Chart chart = chartPart.ChartSpace.AppendChild <DocumentFormat.OpenXml.Drawing.Charts.Chart>(
                new DocumentFormat.OpenXml.Drawing.Charts.Chart());
            // Create a new clustered column chart.
            PlotArea plotArea = chart.AppendChild <PlotArea>(new PlotArea());
            Layout   layout   = plotArea.AppendChild <Layout>(new Layout());
            BarChart barChart = plotArea.AppendChild <BarChart>(new BarChart(new BarDirection()
            {
                Val = new EnumValue <BarDirectionValues>(BarDirectionValues.Column)
            },
                                                                             new BarGrouping()
            {
                Val = new EnumValue <BarGroupingValues>(BarGroupingValues.Clustered)
            }));
            #endregion
            string           sheetName     = GetCurrentSheetName();
            string           columnName    = GetColumnName(starty - 1);
            string           formulaString = string.Format("{0}!${1}${2}:${3}${4}", sheetName, columnName, startx + 1, columnName, startx + rowCount - 1);
            CategoryAxisData cad           = new CategoryAxisData();
            cad.StringReference = new StringReference()
            {
                Formula = new DocumentFormat.OpenXml.Drawing.Charts.Formula(formulaString)
            };
            uint i = 0;
            for (int sIndex = 1; sIndex < columnCount; sIndex++)
            {
                columnName    = GetColumnName(starty + sIndex - 1);
                formulaString = string.Format("{0}!${1}${2}", sheetName, columnName, startx);
                SeriesText st = new SeriesText();
                st.StringReference = new StringReference()
                {
                    Formula = new DocumentFormat.OpenXml.Drawing.Charts.Formula(formulaString)
                };
                formulaString = string.Format("{0}!${1}${2}:${3}${4}", sheetName, columnName, startx + 1, columnName, startx + rowCount - 1);
                DocumentFormat.OpenXml.Drawing.Charts.Values v = new DocumentFormat.OpenXml.Drawing.Charts.Values();
                v.NumberReference = new NumberReference()
                {
                    Formula = new DocumentFormat.OpenXml.Drawing.Charts.Formula(formulaString)
                };
                BarChartSeries barChartSeries = barChart.AppendChild <BarChartSeries>(new BarChartSeries(new Index()
                {
                    Val = new UInt32Value(i)
                },
                                                                                                         new Order()
                {
                    Val = new UInt32Value(i)
                }, st, v));
                if (sIndex == 1)
                {
                    barChartSeries.AppendChild(cad);
                }
                i++;
            }
            #region SDK how to  example Code
            barChart.Append(new AxisId()
            {
                Val = new UInt32Value(48650112u)
            });
            barChart.Append(new AxisId()
            {
                Val = new UInt32Value(48672768u)
            });
            // Add the Category Axis.
            CategoryAxis catAx = 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.NumberFormat()
            {
                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.Right)
            },
                                                                  new Layout()));
            chart.Append(new PlotVisibleOnly()
            {
                Val = new BooleanValue(true)
            });
            // Save the chart part.
            chartPart.ChartSpace.Save();
            // 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 FromMarker(new ColumnId("9"),
                                                new ColumnOffset("581025"),
                                                new RowId("17"),
                                                new RowOffset("114300")));
            twoCellAnchor.Append(new ToMarker(new ColumnId("17"),
                                              new ColumnOffset("276225"),
                                              new RowId("32"),
                                              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());
            // Save the WorksheetDrawing object.
            drawingsPart.WorksheetDrawing.Save();
            #endregion
        }
        /// <summary>
        /// Creates a chartsheet part from given data
        /// </summary>
        /// <param name="chartType">Type of chart to generate</param>
        /// <param name="values">Values to represent in the chart</param>
        /// <param name="headerReference">Columns to be used as series</param>
        /// <param name="categoryReference">Column to be used as category</param>
        /// <returns>Chartsheet part with contents related</returns>
        public static ChartsheetPart Create(SpreadsheetDocument parentDocument, ChartType chartType, List <string> values, List <string> headerReference, string categoryReference)
        {
            //Creates base content and associates it to a new chartsheet part
            WorkbookPart   workbook           = parentDocument.WorkbookPart;
            ChartsheetPart chartsheetPart     = workbook.AddNewPart <ChartsheetPart>();
            XDocument      chartsheetDocument = CreateEmptyChartsheet();

            chartsheetPart.PutXDocument(chartsheetDocument);

            //Creates a base drawings part and associates it to the chartsheet part
            DrawingsPart drawingsPart    = chartsheetPart.AddNewPart <DrawingsPart>();
            XDocument    drawingDocument = CreateEmptyDrawing();

            drawingsPart.PutXDocument(drawingDocument);

            //Adds content to chartsheet document to reference drawing document
            chartsheetDocument
            .Element(ns + "chartsheet")
            .Add(
                new XElement(ns + "drawing",
                             new XAttribute(relationshipsns + "id", chartsheetPart.GetIdOfPart(drawingsPart))
                             )
                );

            //creates the chart part and associates it to the drawings part
            ChartPart chartPart     = drawingsPart.AddNewPart <ChartPart>();
            XDocument chartDocument = CreateChart(chartType, values, headerReference, categoryReference);

            chartPart.PutXDocument(chartDocument);

            //Adds content to drawing document to reference chart document
            drawingDocument
            .Descendants(drawingns + "graphicData")
            .First()
            .Add(
                new XAttribute("uri", chartns),
                new XElement(chartns + "chart",
                             new XAttribute(XNamespace.Xmlns + "c", chartns),
                             new XAttribute(XNamespace.Xmlns + "r", relationshipsns),
                             new XAttribute(relationshipsns + "id", drawingsPart.GetIdOfPart(chartPart))
                             )
                );

            //Associates the chartsheet part to the workbook part
            XDocument document = parentDocument.WorkbookPart.GetXDocument();

            int sheetId = document.Root.Element(ns + "sheets").Elements(ns + "sheet").Count() + 1;

            int chartsheetCount =
                document.Root
                .Element(ns + "sheets")
                .Elements(ns + "sheet")
                .Where(
                    t =>
                    t.Attribute("name").Value.StartsWith("chart")
                    )
                .Count() + 1;

            //Adds content to workbook document to reference chartsheet document
            document.Root
            .Element(ns + "sheets")
            .Add(
                new XElement(ns + "sheet",
                             new XAttribute("name", string.Format("chart{0}", chartsheetCount)),
                             new XAttribute("sheetId", sheetId),
                             new XAttribute(relationshipsns + "id", workbook.GetIdOfPart(chartsheetPart))
                             )
                );

            chartsheetPart.PutXDocument();
            drawingsPart.PutXDocument();
            parentDocument.WorkbookPart.PutXDocument();

            return(chartsheetPart);
        }