Exemplo n.º 1
0
        protected override string ReplaceValuesInChartInSlide(ChartPart chartPart, string categoryTitle, int nSerie)
        {
            ChartSpace chartSpace = chartPart.ChartSpace;

            GraficoBarre myBarre = new GraficoBarre2d();

            myBarre.BarChartSpace = chartSpace;
            myBarre.getBarre();

            if (myBarre.Barre != null)
            {
                BarChartSeries barChartSeries1 = myBarre.barChartSeries1;

                SeriesText       seriesText1;
                CategoryAxisData categoryAxisData1;
                Values           values1;

                modificaChartData("0,0", "", out seriesText1, out categoryAxisData1, out values1);

                barChartSeries1.SeriesText = seriesText1;
                barChartSeries1.Append(categoryAxisData1);
                barChartSeries1.Append(values1);

                return("ok");
            }
            else
            {
                return("non trovo il grafico a barre!");
            }
        }
Exemplo n.º 2
0
        /// <summary>
        /// Updates the colour of a <see cref="BarChartSeries" /> so it matches a <see cref="SolidColorBrush" />
        /// </summary>
        /// <param name="series">The series.</param>
        /// <param name="brush">The brush.</param>
        private static void UpdateSeriesColour(this BarChartSeries series, SolidColorBrush brush)
        {
            var chartShapeProperties = series.Descendants <ChartShapeProperties>().FirstOrDefault();

            if (chartShapeProperties == null)
            {
                chartShapeProperties = new ChartShapeProperties();
                // if there's a series text then insert afterwards
                // the series title, this is the name of the column header
                var seriesText = series.Descendants <SeriesText>().FirstOrDefault();
                if (seriesText == null)
                {
                    series.InsertAt <ChartShapeProperties>(chartShapeProperties, 0);
                }
                else
                {
                    series.InsertAfter <ChartShapeProperties>(chartShapeProperties, seriesText);
                }
            }

            // Determine if the series has a SolidFill
            SolidFill seriesSolidFill = chartShapeProperties.Elements <SolidFill>().FirstOrDefault();

            if (seriesSolidFill == null)
            {
                // No fill, so create one so we can set the colour
                seriesSolidFill = new SolidFill();
                seriesSolidFill.UpdateSolidFill((SolidColorBrush)brush);
                chartShapeProperties.InsertAt(seriesSolidFill, 0);
            }
            else
            {
                seriesSolidFill.UpdateSolidFill((SolidColorBrush)brush);
            }
        }
        /// <summary>
        /// Modify/Add data into chart XML
        /// </summary>
        /// <param name="column_index">Corresponds to the column index that needs to be modified in chart spreadsheet (Ex: A, B, C, ...)</param>
        /// <param name="row_index">Corresponds to the column index that needs to be modified in excel </param>
        /// <param name="new_value">Corresponds to the new value we need to insert to the cell </param>
        protected virtual void ModifyChartXML_Data(string column_index, uint row_index, string new_value)
        {
            BarChartSeries barchart_series = chart_part.ChartSpace.Descendants <BarChartSeries>().Where(s => string.Compare(s.InnerText, worksheet_name + "!$" + column_index + "$1", true) > 0).First();

            DocumentFormat.OpenXml.Drawing.Charts.Values v = barchart_series.Descendants <DocumentFormat.OpenXml.Drawing.Charts.Values>().FirstOrDefault();
            NumberReference nr = v.Descendants <NumberReference>().First();
            NumberingCache  nc = nr.Descendants <NumberingCache>().First();

            try
            {
                NumericPoint np = nc.Descendants <NumericPoint>().ElementAt((int)row_index - 2);
                NumericValue nv = np.Descendants <NumericValue>().First();
                nv.Text = new_value;
            }
            catch (Exception)
            {
                // Create new data and append to previous XML
                nc.PointCount.Val = nc.PointCount.Val + 1;
                NumericValue nv = new NumericValue(new_value);
                NumericPoint np = new NumericPoint(nv);
                np.Index = (uint)nc.Descendants <NumericPoint>().ToList().Count;
                nc.Append(np);

                // Change fomula range
                DocumentFormat.OpenXml.Drawing.Charts.Formula f = nr.Descendants <DocumentFormat.OpenXml.Drawing.Charts.Formula>().FirstOrDefault();
                f.Text = worksheet_name + "!$" + column_index + "$2:$" + column_index + "$" + GetRowIndexByNum((int)row_index - 2).ToString();
            }
        }
Exemplo n.º 4
0
        private void BindBarChart()
        {
            zgSales.Visible = false;
            bcSales.Visible = true;
            if (salesByMonthData == null)
            {
                salesByMonthData = CommerceReport.GetSalesByYearMonthByModule(moduleGuid);
            }

            StringBuilder categories = new StringBuilder();

            string         comma   = string.Empty;
            List <decimal> revenue = new List <decimal>();

            // original data is sorted descending on Y, M resorting here
            DataRow[] result = salesByMonthData.Select(string.Empty, "Y ASC, M ASC");

            int spaceInterval = 0;
            int totalItems    = result.Length;
            int itemsAdded    = 0;

            if (totalItems > 12)
            {
                spaceInterval = 4;
            }
            int nextItemToShow = 0;

            foreach (DataRow row in result)
            {
                categories.Append(comma);

                if (itemsAdded == nextItemToShow)
                {
                    categories.Append(row["Y"].ToString());
                    categories.Append("-");
                    categories.Append(row["M"].ToString());
                    nextItemToShow = itemsAdded + spaceInterval;
                }

                comma = ",";

                revenue.Add(Convert.ToDecimal(row["Sales"]));
                itemsAdded += 1;
            }

            bcSales.ChartTitle = Resource.SalesByMonthChartLabel;


            bcSales.CategoriesAxis = categories.ToString();
            BarChartSeries series = new BarChartSeries();

            //series.Name = Resource.SalesByMonthChartSalesLabel;
            series.Data = revenue.ToArray();

            bcSales.Series.Add(series);

            //bcSales.CategoriesAxis
            //bcSales.Series.
        }
        /// <summary>
        /// Modify/Add series into chart XML
        /// </summary>
        /// <param name="column_index">Corresponds to the column index that needs to be modified in chart spreadsheet (Ex: A, B, C, ...)</param>
        /// <param name="row_index">Corresponds to the column index that needs to be modified in excel </param>
        /// <param name="new_value">Corresponds to the new value we need to insert to the cell </param>
        protected virtual void ModifyChartXML_Series(string column_index, uint row_index, string new_value)
        {
            BarChartSeries barchart_series = chart_part.ChartSpace.Descendants <BarChartSeries>().Where(s => string.Compare(s.InnerText, worksheet_name + "!$" + column_index + "$1", true) > 0).FirstOrDefault();

            if (barchart_series != null)    // There exists data on the series --> We only need to modify it
            {
                SeriesText      st = barchart_series.Descendants <SeriesText>().FirstOrDefault();
                StringReference sr = st.Descendants <StringReference>().First();
                StringCache     sc = sr.Descendants <StringCache>().First();
                StringPoint     sp = sc.Descendants <StringPoint>().First();
                NumericValue    nv = sp.Descendants <NumericValue>().First();
                nv.Text = new_value;
            }
            else    // No such series exists --> Consider create a new series
            {
                /*
                 * // Find location in XML to append the BarChartSeries
                 * Chart chart = chart_part.ChartSpace.Descendants<Chart>().FirstOrDefault();
                 * PlotArea plot = chart.PlotArea;
                 *
                 * // Create new BarChartSeries
                 * barchart_series = new BarChartSeries();
                 * uint index = (uint)plot.Descendants<BarChartSeries>().ToList().Count;
                 *
                 * barchart_series.Append(new Index() { Val = index });
                 * barchart_series.Append(new Order() { Val = index });
                 *
                 * SeriesText seriesText = new SeriesText();
                 * seriesText.Append(new NumericValue() { Text = new_value });
                 *
                 * barchart_series.Append(seriesText);
                 *
                 *
                 * // Append data
                 * Bar3DChart bar_3dchart = plot.Descendants<Bar3DChart>().FirstOrDefault();
                 * if (bar_3dchart != null)       // Chart is 3D
                 *  bar_3dchart.Append(barchart_series);
                 * else    // Chart is not 3d
                 * {
                 *  BarChart barchart = plot.Descendants<BarChart>().FirstOrDefault();
                 *  barchart.Append(barchart_series);
                 * }
                 *
                 * // Append other settings
                 * BarChartSeries barchart_series_template = chart_part.ChartSpace.Descendants<BarChartSeries>().LastOrDefault();
                 *
                 * CategoryAxisData cateAxisData = new CategoryAxisData();
                 * StringReference string_ref = new StringReference();
                 * string_ref.Append(new DocumentFormat.OpenXml.Drawing.Charts.Formula() { Text = barchart_series.Descendants<DocumentFormat.OpenXml.Drawing.Charts.Formula>().FirstOrDefault().Text});
                 * StringCache string_cache = new StringCache();
                 * string_cache.Append(new PointCount() { Val = count });
                 */
            }
        }
        /// <summary>
        /// Modify the worksheet (embedded into chart) range values
        /// </summary>
        /// <param name="result">The DataTable instance represent a table</param>
        protected void ModifyChartRange(System.Data.DataTable result)
        {
            ChartSpace chartspace = chart_part.ChartSpace;

            for (int i = 1; i < result.Columns.Count; i++)
            {
                BarChartSeries barchart_series = chart_part.ChartSpace.Descendants <BarChartSeries>().Where(s => string.Compare(s.InnerText, worksheet_name + "!$" + GetColumnIndexByNum(i) + "$1", true) > 0).First();
                DocumentFormat.OpenXml.Drawing.Charts.Values val = barchart_series.Descendants <DocumentFormat.OpenXml.Drawing.Charts.Values>().FirstOrDefault();
                NumberReference nr = val.Descendants <NumberReference>().First();
                DocumentFormat.OpenXml.Drawing.Charts.Formula f = nr.Descendants <DocumentFormat.OpenXml.Drawing.Charts.Formula>().First();

                f.Text = worksheet_name + "!$" + GetColumnIndexByNum(i) + "$2:$" + GetColumnIndexByNum(i) + "$" + GetRowIndexByNum(result.Rows.Count - 1);
            }
        }
Exemplo n.º 7
0
    private void loadGenderChart()
    {
        BarChartSeries Male = new BarChartSeries();

        Male.BarColor = "#0000FF";
        Male.Name     = "Male";


        BarChartSeries Female = new BarChartSeries();

        Female.BarColor = "#FF00FF";
        Female.Name     = "Female";


        string strCountMale   = "SELECT COUNT (*) AS 'COUNT' FROM Tenants WHERE Gender = 1";
        string strCountFemale = "SELECT COUNT (*) AS 'COUNT' FROM Tenants WHERE Gender = 2";

        int MaleCount   = int.Parse(DataAccess.ReturnData(strCountMale, conString, "COUNT"));
        int FemaleCount = int.Parse(DataAccess.ReturnData(strCountFemale, conString, "COUNT"));

        Decimal[] MaleNumbers =
        {
            Convert.ToDecimal(MaleCount)
        };
        Decimal[] FemaleNumbers =
        {
            Convert.ToDecimal(FemaleCount)
        };

        Male.Data   = MaleNumbers;
        Female.Data = FemaleNumbers;

        BarChartSeries[] GenderCountCollection =
        {
            Male, Female
        };

        barchartGender.Series.AddRange(GenderCountCollection);
        barchartGender.DataBind();

        lblTotalTenants.Text = (FemaleCount + MaleCount).ToString();
    }
Exemplo n.º 8
0
        private void BindCorpClassesChart()
        {
            BarChartSeries yearSeries;

            DEWSReference.DeptEstadoDataSoapClient DEWS = new DeptEstadoDataSoapClient();

            foreach (DataRow dRow in DEWS.GetCorpClassesByYearSeries().Rows)
            {
                yearSeries      = new BarChartSeries();
                yearSeries.Name = dRow["CorpClass"].ToString();
                yearSeries.Data = new decimal[]
                {
                    Convert.ToDecimal(dRow["CorpTotal2008"]), Convert.ToDecimal(dRow["CorpTotal2009"]),
                    Convert.ToDecimal(dRow["CorpTotal2010"]), Convert.ToDecimal(dRow["CorpTotal2011"]),
                    Convert.ToDecimal(dRow["CorpTotal2012"])
                };

                bcCorpClassesByYear.Series.Add(yearSeries);
            }
        }
Exemplo n.º 9
0
        //-----------------------------------------------------------------------------------
        //Tests sur les charts

        public void exec(string formuleVal, string formuleLegende)
        {
            string label = "Feuil1!D5:J5";
            string serie = "Feuil1!C12";
            string val   = "Feuil1!D12:J12";

            //euro	mmp	mut2m	cmav	sapem	quatrem	auxia

            System.IO.File.Copy(template, copie, true);
            SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(copie, true);

            ChartPart cc = XcelWin.cloneChart(XcelWin.getWorksheetPartByName(myWorkbook, "Feuil1"), "Tests", 0, 0, 10, 10);



            BarChart bc = cc.ChartSpace.Descendants <BarChart>().First();

            for (int i = 0; i < 6; i++)
            {
                BarChartSeries newSerie = (BarChartSeries)bc.Elements <BarChartSeries>().First().CloneNode(true);

                string form = val.Replace("12", (6 + i).ToString());

                newSerie.SeriesText.StringReference.Formula.Text = serie;
                //newSerie.Descendants<CategoryAxisData>().First().Remove();

                newSerie.Descendants <CategoryAxisData>().First().NumberReference.Formula.Text = label;
                newSerie.Descendants <Charts.Values>().First().NumberReference.Formula.Text    = form;


                newSerie.Index.Val = (uint)i;
                newSerie.Order.Val = (uint)i;

                bc.Append(newSerie);
            }

            bc.Elements <BarChartSeries>().First().Remove();

            myWorkbook.WorkbookPart.Workbook.Save();
            myWorkbook.Close();
        }
Exemplo n.º 10
0
        /// <summary>
        /// Modify/Add categories into chart XML
        /// </summary>
        /// <param name="column_index">Corresponds to the column index that needs to be modified in chart spreadsheet (Ex: A, B, C, ...)</param>
        /// <param name="row_index">Corresponds to the column index that needs to be modified in excel </param>
        /// <param name="new_value">Corresponds to the new value we need to insert to the cell </param>
        protected override void ModifyChartXML_Categories(string column_index, uint row_index, string new_value)
        {
            foreach (LineChartSeries linechart_series in chart_part.ChartSpace.Descendants <LineChartSeries>().ToList())
            {
                CategoryAxisData category_axis_data = linechart_series.Descendants <CategoryAxisData>().FirstOrDefault();
                if (category_axis_data == null)
                {
                    // If no StringReference --> Clone one from the 1st (usually we go in this when we create a new BarChartSeries)
                    BarChartSeries   template_barchartseries   = chart_part.ChartSpace.Descendants <BarChartSeries>().FirstOrDefault();
                    CategoryAxisData template_categoryaxisdata = template_barchartseries.Descendants <CategoryAxisData>().FirstOrDefault();
                    CategoryAxisData new_categoryaxisdata      = new CategoryAxisData(template_categoryaxisdata.OuterXml);
                    linechart_series.Append(new_categoryaxisdata);
                }
                else
                {
                    StringReference sr = category_axis_data.Descendants <StringReference>().FirstOrDefault();
                    // If there is a StringReference --> Update its values
                    StringCache sc = sr.Descendants <StringCache>().First();
                    try
                    {
                        StringPoint  sp = sc.Descendants <StringPoint>().ElementAt((int)row_index - 2);
                        NumericValue nv = sp.Descendants <NumericValue>().First();
                        nv.Text = new_value;
                    }
                    catch (Exception)
                    {
                        // Create new data and append to previous XML
                        sc.PointCount.Val = sc.PointCount.Val + 1;
                        NumericValue nv = new NumericValue(new_value);
                        StringPoint  sp = new StringPoint(nv);
                        sp.Index = (uint)sc.Descendants <StringPoint>().ToList().Count;
                        sc.Append(sp);

                        // Change fomula range
                        DocumentFormat.OpenXml.Drawing.Charts.Formula f = sr.Descendants <DocumentFormat.OpenXml.Drawing.Charts.Formula>().FirstOrDefault();
                        f.Text = worksheet_name + "!$A$2:$A$" + GetRowIndexByNum((int)row_index - 2).ToString();
                    }
                }
            }
        }
Exemplo n.º 11
0
        private void SetWaterfallStructure(BarChartSeries seriesItem, Column dataColumn)
        {
            var  dataPoints = seriesItem.Elements <DataPoint>();
            var  labels     = seriesItem.FirstElement <DataLabels>();
            bool isFirstBar = dataColumn.Data[0] != null;

            if (!isFirstBar)
            {
                int       valueToHideInd = dataColumn.Data.IndexOf(dataColumn.Data.First(v => v != null));
                DataPoint dataPoint      = dataPoints.FirstOrDefault(p => p.Index != null && p.Index.Val != null && p.Index.Val.Value == valueToHideInd);
                dataPoint.RemoveAllChildren <ChartShapeProperties>();
                ChartShapeProperties chartShapeProperties = new ChartShapeProperties();
                chartShapeProperties.Append(new A.NoFill());
                dataPoint.Append(chartShapeProperties);
                DataLabel label = labels.Elements <DataLabel>().FirstOrDefault(p => p.Index != null && p.Index.Val != null && p.Index.Val.Value == valueToHideInd);
                if (label != null)
                {
                    label.FirstElement <ShowValue>().Val.Value = false;
                }
            }
            labels.FirstElement <ShowValue>().Val.Value = false;
        }
Exemplo n.º 12
0
        void setChartData(ChartPart chartPart1, ChartDataHolder[] ChartDataSlide)
        {
            ChartSpace chartSpace = chartPart1.ChartSpace;

            DocumentFormat.OpenXml.Drawing.Charts.Chart chart1 = chartSpace.GetFirstChild <DocumentFormat.OpenXml.Drawing.Charts.Chart>();
            PlotArea plotArea1 = chart1.GetFirstChild <PlotArea>();
            BarChart barChart1 = plotArea1.GetFirstChild <BarChart>();

            //BarChartSeries barChartSeries1 = barChart1.Elements<BarChartSeries>().ElementAtOrDefault(2);
            for (int i = 0; i < barChart1.Elements <BarChartSeries>().Count(); i++)
            {
                BarChartSeries barChartSeries = barChart1.Elements <BarChartSeries>().ElementAtOrDefault(i);
                ChartDataSlide dataModel      = ChartDataSlide.ElementAtOrDefault(i).chartData;

                SeriesText seriesText = barChartSeries.Elements <SeriesText>().ElementAtOrDefault(0);
                if (seriesText != null)
                {
                    var stringReference = seriesText.Descendants <StringReference>().FirstOrDefault();
                    var stringCache     = stringReference.Descendants <StringCache>().FirstOrDefault();
                    var stringPoint     = stringCache.Descendants <StringPoint>().FirstOrDefault();
                    var barLabel        = stringPoint.GetFirstChild <NumericValue>();
                    barLabel.Text = ChartDataSlide.ElementAtOrDefault(i).seriesText;
                }

                if (barChartSeries != null)
                {
                    Values          values1          = barChartSeries.GetFirstChild <Values>();
                    NumberReference numberReference1 = values1.GetFirstChild <NumberReference>();
                    NumberingCache  numberingCache1  = numberReference1.GetFirstChild <NumberingCache>();

                    NumericPoint numericPoint1 = numberingCache1.GetFirstChild <NumericPoint>();
                    NumericPoint numericPoint2 = numberingCache1.Elements <NumericPoint>().ElementAt(1);
                    NumericPoint numericPoint3 = numberingCache1.Elements <NumericPoint>().ElementAt(2);

                    NumericValue numericValue1 = numericPoint1.GetFirstChild <NumericValue>();
                    //numericValue1.Text = ".50";
                    if (numericValue1 != null)
                    {
                        numericValue1.Text = dataModel.Interaction.ToString();
                    }


                    NumericValue numericValue2 = numericPoint2.GetFirstChild <NumericValue>();
                    //numericValue2.Text = ".10";
                    if (numericValue2 != null)
                    {
                        numericValue2.Text = dataModel.Knowlegde.ToString();
                    }


                    NumericValue numericValue3 = numericPoint3.GetFirstChild <NumericValue>();
                    //numericValue3.Text = ".40";
                    if (numericValue3 != null)
                    {
                        numericValue3.Text = dataModel.Image.ToString();
                    }
                }
            }

            chartSpace.Save();
        }
        //Here be dragons
        internal override void AddData(Statistics stat)
        {
            // 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.
            var chartPart = CreateChartPart(drawingsPart);

            DocumentFormat.OpenXml.Drawing.Charts.Chart chart = chartPart.ChartSpace.AppendChild(new DocumentFormat.OpenXml.Drawing.Charts.Chart());

            // Create a new clustered column chart.
            PlotArea plotArea = chart.AppendChild <PlotArea>(new PlotArea());

            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)
            },
                        new VaryColors()
            {
                Val = false
            }
                        ));

            // 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.

            BarChartSeries barChartSeries = barChart.AppendChild <BarChartSeries>(new BarChartSeries(new Index()
            {
                Val = new UInt32Value((uint)0)
            },
                                                                                                     new Order()
            {
                Val = new UInt32Value((uint)0)
            },
                                                                                                     new SeriesText(new NumericValue()
            {
                Text = "Timeline"
            })));

            StringLiteral strLit =
                barChartSeries.AppendChild <CategoryAxisData>(new CategoryAxisData())
                .AppendChild <StringLiteral>(new StringLiteral());

            strLit.Append(new PointCount()
            {
                Val = new UInt32Value((uint)stat.Diffs.Count)
            });

            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((uint)stat.Diffs.Count)
            });

            uint i = 0;

            foreach (var diff in stat.Diffs)
            {
                strLit.AppendChild <StringPoint>(new StringPoint()
                {
                    Index = new UInt32Value(i)
                })
                .Append(new NumericValue(diff.TimeStamp.ToString()));
                numLit.AppendChild <NumericPoint>(new NumericPoint()
                {
                    Index = new UInt32Value(i)
                })
                .Append(new NumericValue(diff.Value.ToString()));
                i++;
            }

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

            AppendCategoryAxis(plotArea, 48650112u, "Time, ms", 48672768U);
            AppendValueAxis(plotArea, 48672768u, "Duration, ms", 48650112U);

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

            // Position the chart on the worksheet using a TwoCellAnchor object.
            drawingsPart.WorksheetDrawing = new WorksheetDrawing();

            AppendGraphicFrame(drawingsPart, chartPart);

            // Save the WorksheetDrawing object.
            drawingsPart.WorksheetDrawing.Save();
        }
Exemplo n.º 14
0
        public IBarChart InitializeFromRange(IRange labelRange, IRange categoryRange)
        {
            uint orderStart = (uint)this.chartSpace.Charts.TakeWhile(c => c != this).Sum(c => c.SeriesCount);

            this.barChart.RemoveAllChildren <BarChartSeries>();

            IWorksheet worksheet = labelRange.Worksheet;

            if (labelRange.Width == 1 && labelRange.Height > 0)
            {
                if (categoryRange.Height == 1 && categoryRange.Width > 0)
                {
                    for (uint labelIndex = 0; labelIndex < labelRange.Height; ++labelIndex)
                    {
                        BarChartSeries series = this.barChart.AppendChild(new BarChartSeries()
                        {
                            Index = new Index()
                            {
                                Val = labelIndex
                            }, Order = new Order()
                            {
                                Val = orderStart + labelIndex
                            }
                        });
                        series.AppendChild(
                            new SeriesText().AppendChildFluent(
                                new StringReference()
                        {
                            Formula     = new Formula(labelRange[0, labelIndex].Reference),
                            StringCache = new StringCache()
                                          .AppendChildFluent(new PointCount()
                            {
                                Val = 1
                            })
                                          .AppendChildFluent(new StringPoint()
                            {
                                Index = 0, NumericValue = new NumericValue()
                                {
                                    Text = labelRange[0, labelIndex].InnerValue
                                }
                            })
                        }
                                )
                            );

                        series.AppendChild(
                            new CategoryAxisData().AppendChildFluent(
                                new StringReference()
                        {
                            Formula     = new Formula(categoryRange.Formula),
                            StringCache = new StringCache()
                                          .AppendChildFluent(new PointCount()
                            {
                                Val = (uint)categoryRange.Width.Value
                            })
                                          .AppendFluent(Enumerable.Range(0, categoryRange.Width.Value).Select(categoryIndex => new StringPoint()
                            {
                                Index = (uint)categoryIndex, NumericValue = new NumericValue()
                                {
                                    Text = categoryRange[(uint)categoryIndex, 0].InnerValue
                                }
                            }))
                        }
                                )
                            );

                        string valuesFormula = ReferenceEncoder.EncodeRangeReference(
                            worksheet.Name,
                            categoryRange.StartColumn, false,
                            labelRange.StartRow + labelIndex, false,
                            categoryRange.EndColumn, false,
                            labelRange.StartRow + labelIndex, false
                            );

                        series.AppendChild(
                            new Values().AppendChildFluent(
                                new NumberReference()
                        {
                            Formula        = new Formula(valuesFormula),
                            NumberingCache = new NumberingCache()
                                             .AppendChildFluent(new PointCount()
                            {
                                Val = (uint)categoryRange.Width.Value
                            })
                                             .AppendFluent(Enumerable.Range(0, categoryRange.Width.Value).Select(categoryIndex => new NumericPoint()
                            {
                                Index = (uint)categoryIndex, NumericValue = new NumericValue()
                                {
                                    Text = worksheet.Cells[categoryRange.StartColumn.Value + (uint)categoryIndex, labelRange.StartRow.Value + labelIndex].InnerValue
                                }
                            }))
                        }
                                )
                            );
                    }
                }
                else
                {
                    throw new ArgumentException();
                }
            }
            else if (labelRange.Height == 1 && labelRange.Width > 0)
            {
                if (categoryRange.Width == 1 && categoryRange.Height > 0)
                {
                    for (uint labelIndex = 0; labelIndex < labelRange.Width; ++labelIndex)
                    {
                        BarChartSeries series = this.barChart.AppendChild(new BarChartSeries()
                        {
                            Index = new Index()
                            {
                                Val = labelIndex
                            }, Order = new Order()
                            {
                                Val = orderStart + labelIndex
                            }
                        });
                        series.AppendChild(
                            new SeriesText().AppendChildFluent(
                                new StringReference()
                        {
                            Formula     = new Formula(labelRange[labelIndex, 0].Reference),
                            StringCache = new StringCache()
                                          .AppendChildFluent(new PointCount()
                            {
                                Val = 1
                            })
                                          .AppendChildFluent(new StringPoint()
                            {
                                Index = 0, NumericValue = new NumericValue()
                                {
                                    Text = labelRange[labelIndex, 0].InnerValue
                                }
                            })
                        }
                                )
                            );

                        series.AppendChild(
                            new CategoryAxisData().AppendChildFluent(
                                new StringReference()
                        {
                            Formula     = new Formula(categoryRange.Formula),
                            StringCache = new StringCache()
                                          .AppendChildFluent(new PointCount()
                            {
                                Val = (uint)categoryRange.Height.Value
                            })
                                          .AppendFluent(Enumerable.Range(0, categoryRange.Height.Value).Select(categoryIndex => new StringPoint()
                            {
                                Index = (uint)categoryIndex, NumericValue = new NumericValue()
                                {
                                    Text = categoryRange[0, (uint)categoryIndex].InnerValue
                                }
                            }))
                        }
                                )
                            );

                        string valuesFormula = ReferenceEncoder.EncodeRangeReference(
                            worksheet.Name,
                            labelRange.StartColumn + labelIndex, false,
                            categoryRange.StartRow, false,
                            labelRange.StartColumn + labelIndex, false,
                            categoryRange.EndRow, false
                            );

                        series.AppendChild(
                            new Values().AppendChildFluent(
                                new NumberReference()
                        {
                            Formula        = new Formula(valuesFormula),
                            NumberingCache = new NumberingCache()
                                             .AppendChildFluent(new PointCount()
                            {
                                Val = (uint)categoryRange.Height.Value
                            })
                                             .AppendFluent(Enumerable.Range(0, categoryRange.Height.Value).Select(categoryIndex => new NumericPoint()
                            {
                                Index = (uint)categoryIndex, NumericValue = new NumericValue()
                                {
                                    Text = worksheet.Cells[labelRange.StartColumn.Value + labelIndex, categoryRange.StartRow.Value + (uint)categoryIndex].InnerValue
                                }
                            }))
                        }
                                )
                            );
                    }
                }
                else
                {
                    throw new ArgumentException();
                }
            }
            else
            {
                throw new ArgumentException();
            }

            return(this);
        }
Exemplo n.º 15
0
        internal override void CreateChart(OpenXmlWriter writer, WorksheetPart part, SpreadsheetLocation location)
        {
            DrawingsPart drawingsPart = part.AddNewPart <DrawingsPart>();

            writer.WriteStartElement(new Drawing()
            {
                Id = part.GetIdOfPart(drawingsPart)
            });
            writer.WriteEndElement();

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

            chartPart.ChartSpace = new ChartSpace();
            chartPart.ChartSpace.Append(new EditingLanguage()
            {
                Val = new StringValue("en-US")
            });

            Chart chartContainer = chartPart.ChartSpace.AppendChild <Chart>(new Chart());

            chartContainer.AppendChild <AutoTitleDeleted>(new AutoTitleDeleted()
            {
                Val = false
            });

            // Create a new clustered column chart.
            PlotArea plotArea = chartContainer.AppendChild <PlotArea>(new PlotArea());
            Layout   layout1  = plotArea.AppendChild <Layout>(new Layout());
            BarChart barChart = plotArea.AppendChild <BarChart>(new BarChart());

            barChart.Append(new BarDirection()
            {
                Val = BarDirectionValues.Bar
            });
            barChart.Append(new BarGrouping()
            {
                Val = BarGroupingValues.Stacked
            });
            barChart.Append(new GapWidth()
            {
                Val = (UInt16Value)75U
            });
            barChart.Append(new Overlap()
            {
                Val = 100
            });

            GanttTypeChart ganttChart = new GanttTypeChart(UserSettings);

            var groupedData = GanttData
                              .GroupBy(x => x.Name);

            List <GanttDataPairedSeries> ganttDataWithSeries = new List <GanttDataPairedSeries>();

            for (int i = 0; i < groupedData.Max(x => x.Count()); i++)
            {
                // For each series create a hidden one for spacing.
                BarChartSeries barChartSeriesHidden = barChart.AppendChild <BarChartSeries>(new BarChartSeries(
                                                                                                new Index()
                {
                    Val = new UInt32Value((uint)(i * 2))
                },
                                                                                                new Order()
                {
                    Val = new UInt32Value((uint)(i * 2))
                },
                                                                                                new SeriesText(new NumericValue()
                {
                    Text = "Not Active"
                })));

                BarChartSeries barChartSeriesValue = barChart.AppendChild <BarChartSeries>(new BarChartSeries(
                                                                                               new Index()
                {
                    Val = new UInt32Value((uint)(i * 2) + 1)
                },
                                                                                               new Order()
                {
                    Val = new UInt32Value((uint)(i * 2) + 1)
                },
                                                                                               new SeriesText(new NumericValue()
                {
                    Text = "Time Spent"
                })));

                ganttChart.SetChartShapeProperties(barChartSeriesHidden, visible: false);
                ganttChart.SetChartShapeProperties(barChartSeriesValue, colorPoints: (uint)GanttData.Count);

                var ganttData = new List <GanttData>();
                foreach (var data in groupedData.Where(x => x.Count() >= i + 1))
                {
                    ganttData.Add(data.ElementAt(i));
                }

                ganttDataWithSeries.Add(new GanttDataPairedSeries()
                {
                    BarChartSeriesHidden = barChartSeriesHidden,
                    BarChartSeriesValue  = barChartSeriesValue,
                    Values = ganttData
                });
            }

            ganttChart.SetChartAxis(ganttDataWithSeries, groupedData.ToList());

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

            // Add the Category Axis (X axis).
            ganttChart.SetGanttCategoryAxis(plotArea);

            // Add the Value Axis (Y axis).
            ganttChart.SetGanttValueAxis(plotArea, GanttData.Min(x => x.Start), GanttData.Max(x => x.End));

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

            ganttChart.SetChartLocation(drawingsPart, chartPart, location);
        }
Exemplo n.º 16
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();
            }
        }
Exemplo n.º 17
0
        /// <summary>
        /// Sets the color of the series using a solidcolor brush
        /// If a null brush is supplied any color is removed so the color will be automatic
        /// </summary>
        /// <param name="line">The line.</param>
        /// <param name="brush">The brush.</param>
        public static void UpdateLineBrush(this OpenXmlCompositeElement line, Brush brush)
        {
            if (line == null)
            {
                return;
            }

            // If we have a BarChart, we really want tp update the SolidFill (not the Outline.SolidFill)
            BarChartSeries barChartSeries = line as BarChartSeries;

            if (barChartSeries != null)
            {
                // For BarCharts, we update the SolidFill
                barChartSeries.UpdateSeriesColour((SolidColorBrush)brush);
            }
            else
            {
                // Update the Outline.SolidFill + set the SolidFill to the same colour
                var chartShapeProperties = line.Descendants <ChartShapeProperties>().FirstOrDefault();

                if (brush == null && !(brush is SolidColorBrush))
                {
                    if (chartShapeProperties != null)
                    {
                        line.RemoveChild <ChartShapeProperties>(chartShapeProperties);
                    }
                    return;
                }

                // the series title, this is the name of the column header
                var seriesText = line.Descendants <SeriesText>().FirstOrDefault();

                if (chartShapeProperties == null)
                {
                    chartShapeProperties = new ChartShapeProperties();
                    // if there's a series text then insert afterwards
                    if (seriesText == null)
                    {
                        line.InsertAt <ChartShapeProperties>(chartShapeProperties, 0);
                    }
                    else
                    {
                        line.InsertAfter <ChartShapeProperties>(chartShapeProperties, seriesText);
                    }
                }

                var outline = chartShapeProperties.Descendants <Outline>().FirstOrDefault();
                if (outline == null)
                {
                    outline = new Outline();
                    chartShapeProperties.InsertAt(outline, 0);
                }

                var outlineSolidFill = outline.Descendants <SolidFill>().FirstOrDefault();
                if (outlineSolidFill == null)
                {
                    outlineSolidFill = new SolidFill();
                    outline.Append(outlineSolidFill);
                }

                // Update the fill with the supplied brush colour
                outlineSolidFill.UpdateSolidFill((SolidColorBrush)brush);

                // Clones the OutlineSolidFill as the SolidFill of the series...
                var solidFill = chartShapeProperties.GetFirstChild <SolidFill>();
                if (solidFill != null)
                {
                    chartShapeProperties.RemoveChild(solidFill);
                }
                chartShapeProperties.InsertAt(outlineSolidFill.CloneNode(true), 0);
            }
        }
        private void CreateHistogram(PlotArea plotArea, Statistics stat, uint index, uint categoryAxisId, uint valueAxisId)
        {
            BarChart barChart =
                plotArea.AppendChild <BarChart>(
                    new BarChart(
                        new BarDirection()
            {
                Val = new EnumValue <BarDirectionValues>(BarDirectionValues.Column)
            },
                        new BarGrouping()
            {
                Val = new EnumValue <BarGroupingValues>(BarGroupingValues.Clustered)
            },
                        new VaryColors()
            {
                Val = false
            }
                        ));

            BarChartSeries barChartSeries = barChart.AppendChild(new BarChartSeries(new Index()
            {
                Val = new UInt32Value(index)
            },
                                                                                    new Order()
            {
                Val = new UInt32Value(index)
            },
                                                                                    new SeriesText(new NumericValue()
            {
                Text = "Histogram"
            })));

            StringLiteral strLit =
                barChartSeries.AppendChild <CategoryAxisData>(new CategoryAxisData())
                .AppendChild <StringLiteral>(new StringLiteral());

            strLit.Append(new PointCount()
            {
                Val = new UInt32Value((uint)stat.Frequencies.Count)
            });

            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((uint)stat.Frequencies.Count)
            });

            for (uint i = 0; i < stat.Frequencies.Count; i++)
            {
                strLit.AppendChild <StringPoint>(new StringPoint()
                {
                    Index = new UInt32Value(i)
                })
                .Append(new NumericValue(stat.Frequencies[(int)i].Value.ToString()));
                numLit.AppendChild <NumericPoint>(new NumericPoint()
                {
                    Index = new UInt32Value(i)
                })
                .Append(new NumericValue(stat.Frequencies[(int)i].Count.ToString()));
            }

            barChart.Append(new AxisId()
            {
                Val = new UInt32Value(categoryAxisId)
            });
            barChart.Append(new AxisId()
            {
                Val = new UInt32Value(valueAxisId)
            });

            AppendCategoryAxis(plotArea, categoryAxisId, "Time, ms", valueAxisId);
            AppendValueAxis(plotArea, valueAxisId, "Number of samples", categoryAxisId);
        }
        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();
            }
        }
Exemplo n.º 20
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
        }
Exemplo n.º 21
0
        private void FillPoints(string baseFormula, String mode, List <String> data)
        {
            int idx = data.Count;


            ChartPart cp       = Document.MainDocumentPart.ChartParts.FirstOrDefault();
            Chart     chart    = cp.ChartSpace.Elements <Chart>().FirstOrDefault();
            BarChart  barchart = chart.PlotArea.Elements <BarChart>().FirstOrDefault();

            BarChartSeries series = barchart.Elements <BarChartSeries>().FirstOrDefault();

            CategoryAxisData labels = new CategoryAxisData();

            DocumentFormat.OpenXml.Drawing.Charts.Values values = new DocumentFormat.OpenXml.Drawing.Charts.Values();

            NumberReference nref    = new NumberReference();
            string          formula = baseFormula + (idx + 1);

            DocumentFormat.OpenXml.Drawing.Charts.Formula f = new DocumentFormat.OpenXml.Drawing.Charts.Formula();
            f.Text = formula;
            Log.Info(formula);
            nref.Formula = f;
            NumberingCache nc = new NumberingCache();//nref.Descendants<NumberingCache>().First();

            nc.PointCount     = new PointCount();
            nc.PointCount.Val = (uint)idx;
            int pointIndex = 0;

            foreach (string val in data)
            {
                NumericPoint point = new NumericPoint();
                point.Index = (uint)pointIndex;
                NumericValue value = new NumericValue();
                if ("LABELS".Equals(mode))
                {
                    value.Text = val;
                }
                else if ("VALUES".Equals(mode))
                {
                    if (val != "0")
                    {
                        float valuePerc = float.Parse(val, CultureInfo.InvariantCulture.NumberFormat) * 100;
                        value.Text = valuePerc.ToString(CultureInfo.InvariantCulture);
                    }
                    else
                    {
                        value.Text = "";
                    }
                }
                point.AppendChild(value);
                nc.AppendChild(point);
                pointIndex++;
            }
            nref.AppendChild(nc);

            if ("LABELS".Equals(mode))
            {
                labels.AppendChild(nref);
                series.ReplaceChild <CategoryAxisData>(labels, series.Elements <CategoryAxisData>().FirstOrDefault());
            }
            else if ("VALUES".Equals(mode))
            {
                values.AppendChild(nref);
                series.ReplaceChild <DocumentFormat.OpenXml.Drawing.Charts.Values>(values, series.Elements <DocumentFormat.OpenXml.Drawing.Charts.Values>().FirstOrDefault());
            }
            ;
        }