/// <summary>
 /// Применить стили к круговой диаграмме EpPlus.
 /// </summary>
 /// <param name="chart">Круговая диаграмма EpPlus.</param>
 private static void ApplyPieChartStyles(ExcelPieChart chart)
 {
     chart.DataLabel.ShowPercent     = true;
     chart.DataLabel.ShowLeaderLines = true;
     chart.DataLabel.ShowCategory    = true;
     chart.VaryColors = true;
 }
Example #2
0
        public static void SetDataPointStyle(this ExcelPieChart chart, int dataPointIndex, Color color)
        {
            //Get the nodes
            var nsm     = chart.WorkSheet.Drawings.NameSpaceManager;
            var nschart = nsm.LookupNamespace("c");
            var nsa     = nsm.LookupNamespace("a");
            var node    = chart.ChartXml.SelectSingleNode("c:chartSpace/c:chart/c:plotArea/c:lineChart/c:ser", nsm);
            var doc     = chart.ChartXml;
            //Add the node
            //Create the data point node
            var dPt = doc.CreateElement("c:dPt", nschart);

            var idx       = dPt.AppendChild(doc.CreateElement("c:idx", nschart));
            var valattrib = idx.Attributes.Append(doc.CreateAttribute("val"));

            valattrib.Value = dataPointIndex.ToString(CultureInfo.InvariantCulture);
            node.AppendChild(dPt);

            //Add the solid fill node
            var spPr      = doc.CreateElement("c:spPr", nschart);
            var solidFill = spPr.AppendChild(doc.CreateElement("a:solidFill", nsa));
            var srgbClr   = solidFill.AppendChild(doc.CreateElement("a:srgbClr", nsa));

            valattrib = srgbClr.Attributes.Append(doc.CreateAttribute("val"));

            //Set the color
            valattrib.Value = color.ToHex().Substring(1);
            dPt.AppendChild(spPr);
        }
Example #3
0
        //gavdcodeend 04

        //gavdcodebegin 05
        public static void ExcelEpplusInsertPieChart()
        {
            FileInfo myFileInfo = new FileInfo(@"C:\Temporary\ExcelEPPlus01.xlsx");

            using (ExcelPackage excelPackage = new ExcelPackage(myFileInfo))
            {
                // Create a WorkSheet
                ExcelWorksheet myWorksheet =
                    excelPackage.Workbook.Worksheets.Add("PieChartSheet");

                // Fill cell data with a loop. The row and column indexes start at 1
                Random rnd = new Random();
                for (int counter = 1; counter <= 10; counter++)
                {
                    myWorksheet.Cells[1, counter].Value = "Value " + counter;
                    myWorksheet.Cells[2, counter].Value = rnd.Next(5, 15);
                }

                // Create a new pie chart of type Pie3D
                ExcelPieChart myPieChart = myWorksheet.Drawings.AddChart("pieChart",
                                                                         eChartType.Pie3D) as ExcelPieChart;

                // Set the properties of the chart
                myPieChart.Title.Text = "PieChart Example";
                myPieChart.Series.Add(ExcelRange.GetAddress(2, 1, 2, 10),
                                      ExcelRange.GetAddress(1, 1, 1, 10));
                myPieChart.Legend.Position       = eLegendPosition.Bottom;
                myPieChart.DataLabel.ShowPercent = true;
                myPieChart.SetSize(500, 400);
                myPieChart.SetPosition(4, 0, 2, 0);

                excelPackage.SaveAs(myFileInfo);
            }
        }
        public static void Enable3DChartShadow(this ExcelPieChart chart, int serieIndex, int red, int green, int blue, int sizePer, int blurPt, int distancePt, int angleDgr, int transparencyPer)
        {
            var xdoc = chart.ChartXml;
            var nsm  = new XmlNamespaceManager(xdoc.NameTable);

            // "http://schemas.openxmlformats.org/drawingml/2006/chart"
            var schemaChart = xdoc.DocumentElement.NamespaceURI;

            if (nsm.HasNamespace("c") == false)
            {
                nsm.AddNamespace("c", schemaChart);
            }

            var schemaDrawings = "http://schemas.openxmlformats.org/drawingml/2006/main";

            if (nsm.HasNamespace("a") == false)
            {
                nsm.AddNamespace("a", schemaDrawings);
            }

            // https://msdn.microsoft.com/en-us/library/documentformat.openxml.drawing.charts.plotarea.aspx
            // https://msdn.microsoft.com/en-us/library/documentformat.openxml.drawing.charts.pie3dchart.aspx
            // https://msdn.microsoft.com/en-us/library/documentformat.openxml.drawing.charts.piechartseries.aspx
            // https://msdn.microsoft.com/en-us/library/documentformat.openxml.drawing.charts.index.aspx
            var ser = xdoc.SelectSingleNode("/c:chartSpace/c:chart/c:plotArea/c:pie3DChart/c:ser[c:idx[@val='" + serieIndex + "']]", nsm);

            // https://msdn.microsoft.com/en-us/library/documentformat.openxml.drawing.charts.chartshapeproperties.aspx
            var spPr = ser.SelectSingleNode("./c:spPr", nsm);

            if (spPr == null)
            {
                spPr = ser.AppendElement(schemaChart, "c:spPr");
            }

            // https://msdn.microsoft.com/en-us/library/documentformat.openxml.drawing.effectlist.aspx
            var effectLst = spPr.AppendElement(schemaDrawings, "a:effectLst");

            // https://msdn.microsoft.com/en-us/library/documentformat.openxml.drawing.outershadow.aspx
            var outerShdw = effectLst.AppendElement(schemaDrawings, "a:outerShdw");

            outerShdw.AppendAttribute("sx", (sizePer * SimpleTypes.ST_PERCENTAGE).ToString());             // horizontal scaling factor
            outerShdw.AppendAttribute("sy", (sizePer * SimpleTypes.ST_PERCENTAGE).ToString());             // vertical scaling factor
            outerShdw.AppendAttribute("blurRad", (blurPt * EMUUnits.EMU_PER_POINT).ToString());            // blur
            outerShdw.AppendAttribute("dist", (distancePt * EMUUnits.EMU_PER_POINT).ToString());           // distance
            outerShdw.AppendAttribute("dir", (angleDgr * SimpleTypes.ST_POSITIVE_FIXED_ANGLE).ToString()); // direction

            // https://msdn.microsoft.com/en-us/library/documentformat.openxml.drawing.rgbcolormodelhex.aspx
            var srgbClr = outerShdw.AppendElement(schemaDrawings, "a:srgbClr");

            srgbClr.AppendAttribute("val", string.Format("{0:X2}{1:X2}{2:X2}", red, green, blue));

            // https://msdn.microsoft.com/en-us/library/documentformat.openxml.drawing.alpha.aspx
            var alpha = srgbClr.AppendElement(schemaDrawings, "a:alpha");

            alpha.AppendAttribute("val", (100 - transparencyPer) + "%"); // alpha = 100% - transparency
        }
        public static void Enable3DChartFormat(this ExcelPieChart chart, int serieIndex, PresetMaterialTypeValues material, int topBevelWidthPt, int topBevelHeightPt, int bottomBevelWidthPt, int bottomBevelHeightPt)
        {
            var xdoc = chart.ChartXml;
            var nsm  = new XmlNamespaceManager(xdoc.NameTable);

            // "http://schemas.openxmlformats.org/drawingml/2006/chart"
            var schemaChart = xdoc.DocumentElement.NamespaceURI;

            if (nsm.HasNamespace("c") == false)
            {
                nsm.AddNamespace("c", schemaChart);
            }

            var schemaDrawings = "http://schemas.openxmlformats.org/drawingml/2006/main";

            if (nsm.HasNamespace("a") == false)
            {
                nsm.AddNamespace("a", schemaDrawings);
            }

            // https://msdn.microsoft.com/en-us/library/documentformat.openxml.drawing.charts.plotarea.aspx
            // https://msdn.microsoft.com/en-us/library/documentformat.openxml.drawing.charts.pie3dchart.aspx
            // https://msdn.microsoft.com/en-us/library/documentformat.openxml.drawing.charts.piechartseries.aspx
            // https://msdn.microsoft.com/en-us/library/documentformat.openxml.drawing.charts.index.aspx
            var ser = xdoc.SelectSingleNode("/c:chartSpace/c:chart/c:plotArea/c:pie3DChart/c:ser[c:idx[@val='" + serieIndex + "']]", nsm);

            // https://msdn.microsoft.com/en-us/library/documentformat.openxml.drawing.charts.chartshapeproperties.aspx
            var spPr = ser.SelectSingleNode("./c:spPr", nsm);

            if (spPr == null)
            {
                spPr = ser.AppendElement(schemaChart, "c:spPr");
            }

            // https://msdn.microsoft.com/en-us/library/documentformat.openxml.drawing.shape3dtype.aspx
            var sp3d = spPr.AppendElement(schemaDrawings, "a:sp3d");

            sp3d.AppendAttribute("prstMaterial", material.StringValue());

            // https://msdn.microsoft.com/en-us/library/documentformat.openxml.drawing.beveltop.aspx
            var bevelT = sp3d.AppendElement(schemaDrawings, "a:bevelT");

            bevelT.AppendAttribute("w", (topBevelWidthPt * EMUUnits.EMU_PER_POINT).ToString());
            bevelT.AppendAttribute("h", (topBevelHeightPt * EMUUnits.EMU_PER_POINT).ToString());

            // https://msdn.microsoft.com/en-us/library/documentformat.openxml.drawing.bevelbottom.aspx
            var bevelB = sp3d.AppendElement(schemaDrawings, "a:bevelB");

            bevelB.AppendAttribute("w", (bottomBevelWidthPt * EMUUnits.EMU_PER_POINT).ToString());
            bevelB.AppendAttribute("h", (bottomBevelHeightPt * EMUUnits.EMU_PER_POINT).ToString());
        }
        private static void SetLayout(ExcelPieChart chart, string path, decimal width)
        {
            var nsm     = chart.WorkSheet.Drawings.NameSpaceManager;
            var nschart = nsm.LookupNamespace("c");
            var nsa     = nsm.LookupNamespace("a");
            var node    = chart.ChartXml.SelectSingleNode(path, nsm);
            var doc     = chart.ChartXml;

            var layout       = doc.CreateElement("c:layout", nschart);
            var manualLayout = layout.AppendChild(doc.CreateElement("c:manualLayout", nschart));

            var xMode     = manualLayout.AppendChild(doc.CreateElement("c:w", nschart));
            var valattrib = xMode.Attributes.Append(doc.CreateAttribute("val"));

            valattrib.Value = width.ToString(CultureInfo.InvariantCulture);

            node.AppendChild(layout);
        }
 public static void SetPlotAreaWidth(this ExcelPieChart chart, decimal width)
 {
     SetLayout(chart, "c:chartSpace/c:chart/c:plotArea", width);
 }
 public static void SetLegendWidth(this ExcelPieChart chart, decimal width)
 {
     SetLayout(chart, "c:chartSpace/c:chart/c:legend", width);
 }
Example #9
0
        private void WriteWorkSheetChart_chart_val(ExcelPieChart excelPieChart, XElement chartTypeElement)
        {
            if (excelPieChart.CategoryAxisValue != null)
            {
                XElement valElement = new XElement(XName.Get("val", ExcelCommon.Schema_Chart));
                XElement numRefElement = new XElement(XName.Get("numRef", ExcelCommon.Schema_Chart));
                XElement fElement = new XElement(XName.Get("f", ExcelCommon.Schema_Chart));
                fElement.Value = excelPieChart.CategoryAxisValue.NumberReference.Formula;
                numRefElement.Add(fElement);
                valElement.Add(numRefElement);



                XElement numCacheElement = new XElement(XName.Get("numCache", ExcelCommon.Schema_Chart));
                XElement ptCountElement = new XElement(XName.Get("ptCount", ExcelCommon.Schema_Chart));
                ptCountElement.Add(new XAttribute(XName.Get("val"), excelPieChart.CategoryAxisData.StringReference.StringPointCollection.Count));
                numCacheElement.Add(ptCountElement);
                foreach (var item in excelPieChart.CategoryAxisValue.NumberReference.NumericPointCollection)
                {
                    XElement ptElement = new XElement(XName.Get("pt", ExcelCommon.Schema_Chart));

                    XAttribute ptAttribute = new XAttribute(XName.Get("idx"), item.PointIndex);
                    ptElement.Add(ptAttribute);

                    XElement vElement = new XElement(XName.Get("v", ExcelCommon.Schema_Chart));
                    vElement.Value = item.PointValue;
                    ptElement.Add(vElement);
                    numCacheElement.Add(ptElement);
                }
                numRefElement.Add(numCacheElement);

                chartTypeElement.Add(valElement);
            }
        }
Example #10
0
        private void WriteWorkSheetChart_chart_dLbls(ExcelPieChart excelPieChart, XElement chartTypeElement)
        {
            if (excelPieChart._DataLabel != null)
            {
                XElement dLblsElement = new XElement(XName.Get("dLbls", ExcelCommon.Schema_Chart));
                dLblsElement.Add(new XElement(XName.Get("showLegendKey", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("val"), excelPieChart._DataLabel.ShowLegendKey ? "1" : "0")));
                dLblsElement.Add(new XElement(XName.Get("showVal", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("val"), excelPieChart._DataLabel.ShowValue ? "1" : "0")));
                dLblsElement.Add(new XElement(XName.Get("showCatName", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("val"), excelPieChart._DataLabel.ShowCategory ? "1" : "0")));
                dLblsElement.Add(new XElement(XName.Get("showSerName", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("val"), excelPieChart._DataLabel.ShowSeriesName ? "1" : "0")));
                dLblsElement.Add(new XElement(XName.Get("showPercent", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("val"), excelPieChart._DataLabel.ShowPercent ? "1" : "0")));
                dLblsElement.Add(new XElement(XName.Get("showBubbleSize", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("val"), excelPieChart._DataLabel.ShowBubbleSize ? "1" : "0")));
                if (string.IsNullOrEmpty(excelPieChart._DataLabel.Separator) == false)
                {
                    XElement separatorElement = new XElement(XName.Get("separator", ExcelCommon.Schema_Chart));
                    separatorElement.Value = excelPieChart._DataLabel.Separator;
                    dLblsElement.Add(separatorElement);
                }

                dLblsElement.Add(new XElement(XName.Get("showLeaderLines", ExcelCommon.Schema_Chart), new XAttribute(XName.Get("val"), excelPieChart._DataLabel.ShowLeaderLines ? "1" : "0")));
                chartTypeElement.Add(dLblsElement);
            }
        }
Example #11
0
        private void WriteWorkSheetChart_chart_ser(ExcelPieChart excelPieChart, XElement chartTypeElement)
        {

            XElement serElement = new XElement(XName.Get("ser", ExcelCommon.Schema_Chart));
            WriteWorkSheetChart_chart_cat(excelPieChart, serElement);
            WriteWorkSheetChart_chart_val(excelPieChart, serElement);
            chartTypeElement.Add(serElement);
        }