Example #1
0
        public void Apply(IChartSpace chartSpace)
        {
            using (ISpreadsheetDocument spreadsheet = chartSpace.OpenSpreadsheetDocument())
            {
                IWorksheet seriesSheet    = spreadsheet.Workbook.AppendWorksheet("Series");
                IWorksheet connectorSheet = spreadsheet.Workbook.AppendWorksheet("Connector");

                double total = this.categories.Sum(c => c.CustomOffset.HasValue ? c.CustomOffset.Value : c.Values.Sum(s => s.Value));

                seriesSheet.Cells[0, 1].SetValue("Offset");

                uint column       = 1;
                uint seriesRow    = 2;
                uint connectorRow = 0;
                if (this.total != null)
                {
                    // set name of total category
                    seriesSheet.Cells[column, 0].SetValue(this.total);
                    connectorSheet.Cells[column, 0].SetValue(this.total);

                    seriesSheet.Cells[0, seriesRow].SetValue("Total");

                    seriesSheet.Cells[column, 1].SetValue(0);
                    seriesSheet.Cells[column, seriesRow].SetValue(total);

                    ++seriesRow;
                    ++column;
                    ++connectorRow;
                }

                double offset = total;
                foreach (BurndownChartCategoryConfig category in this.categories)
                {
                    // set name of category
                    seriesSheet.Cells[column, 0].SetValue(category.Name);
                    connectorSheet.Cells[column, 0].SetValue(category.Name);

                    // set connector of category
                    if (column > 1)
                    {
                        connectorSheet.Cells[column - 1, connectorRow].SetValue(offset);
                        connectorSheet.Cells[column, connectorRow].SetValue(offset);
                    }

                    // set offset of category
                    seriesSheet.Cells[column, 1].SetValue(offset - category.Values.Sum(s => s.Value));

                    if (category.CustomOffset.HasValue)
                    {
                        offset -= category.CustomOffset.Value;
                    }
                    else
                    {
                        offset -= category.Values.Sum(s => s.Value);
                    }

                    uint seriesIdx   = 0;
                    uint seriesCount = (uint)category.Values.Count;
                    foreach (BurndownChartValueConfig value in category.Values)
                    {
                        // set name of series
                        seriesSheet.Cells[0, seriesRow + seriesCount - seriesIdx - 1].SetValue(value.Name);

                        // set value of series
                        seriesSheet.Cells[column, seriesRow + seriesCount - seriesIdx - 1].SetValue(Math.Abs(value.Value));

                        ++seriesIdx;
                    }

                    seriesRow += (uint)category.Values.Count;

                    ++connectorRow;
                    ++column;
                }

                CartesianAxes axes = chartSpace.AppendCartesianAxes();

                ILineChart lineChart = chartSpace.InsertLineChart(axes)
                                       .InitializeFromRange(connectorSheet.GetRange(0, 1, 0, connectorRow - 1), connectorSheet.GetRange(1, 0, column - 1, 0));

                IBarChart barChart = chartSpace.InsertBarChart(axes)
                                     .InitializeFromRange(seriesSheet.GetRange(0, 1, 0, seriesRow - 1), seriesSheet.GetRange(1, 0, column - 1, 0))
                                     .SetDirection(BarChartDirection.Column)
                                     .SetGrouping(BarChartGrouping.Stacked)
                                     .SetOverlap(1.0);

                // hide offset series
                barChart.Series[0].Style.SetNoFill();

                if (this.total != null && this.totalStyle != null)
                {
                    this.totalStyle(barChart.Series[1].Values[1]);
                }

                if (this.connectorStyle != null)
                {
                    foreach (ILineChartValue value in lineChart.Series.SelectMany(s => s.Values))
                    {
                        this.connectorStyle(value);
                    }
                }

                int valueIndex  = 0;
                int seriesIndex = 1;
                if (this.total != null)
                {
                    ++valueIndex;
                    ++seriesIndex;
                }

                foreach (BurndownChartCategoryConfig category in this.categories)
                {
                    int seriesIdx = 0;
                    foreach (BurndownChartValueConfig value in category.Values)
                    {
                        if (value.Style != null)
                        {
                            IBarChartSeries s = barChart.Series[seriesIndex + category.Values.Count - seriesIdx - 1];
                            value.Style(s.Values[valueIndex]);
                        }

                        ++seriesIdx;
                    }

                    seriesIndex += category.Values.Count;
                    ++valueIndex;
                }

                axes.CategoryAxis
                .Text.SetFontSize(6)
                .MajorGridlines.Remove();

                axes.ValueAxis
                .SetVisibility(false)
                .MajorGridlines.Remove();
            }
        }
Example #2
0
        private static void GeneratePresentation(string[] args)
        {
            string destination;

            if (args.Length > 0)
            {
                destination = args[0];
            }
            else
            {
                Console.WriteLine("destination not specified");
                return;
            }

            IPresentation presentation = null;

            using (Stream source = typeof(Program).Assembly.GetManifestResourceStream("BinaryMesh.OpenXml.Explorer.Assets.ExamplePresentation.pptx"))
            {
                presentation = PresentationFactory.CreatePresentation(source);
            }

            using (presentation)
            {
                ISlide titleSlide = presentation.InsertSlide(presentation.SlideMasters[0].SlideLayouts[0]);
                (titleSlide.ShapeTree.Visuals["Titel 1"] as IShapeVisual).Text.SetText("Automated Presentation Documents made easy");
                (titleSlide.ShapeTree.Visuals["Untertitel 2"] as IShapeVisual).Text.SetText("BinaryMesh.OpenXml is an open-source library to easily and intuitively create OpenXml documents");
                (titleSlide.ShapeTree.Visuals["Datumsplatzhalter 3"] as IShapeVisual).Text.SetText("10.10.2020");

                ISlide       chartSlide     = presentation.InsertSlide(presentation.SlideMasters[0].SlideLayouts[6]);
                IChartVisual pieChartVisual = chartSlide.ShapeTree.AppendChartVisual("Chart 1")
                                              .Transform.SetOffset(2032000, 719666)
                                              .Transform.SetExtents(8128000, 5418667);

                IChartSpace pieChartSpace = pieChartVisual.ChartSpace;
                using (ISpreadsheetDocument spreadsheet = pieChartSpace.OpenSpreadsheetDocument())
                {
                    IWorkbook  workbook = spreadsheet.Workbook;
                    IWorksheet sheet    = workbook.AppendWorksheet("Sheet1");

                    string reference = sheet.Cells[0, 1].SetValue("Costs").Reference;

                    sheet.Cells[1, 0].SetValue("1. Quarter");
                    sheet.Cells[2, 0].SetValue("2. Quarter");
                    sheet.Cells[3, 0].SetValue("3. Quarter");
                    sheet.Cells[4, 0].SetValue("4. Quarter");

                    sheet.Cells[1, 1].SetValue(152306);
                    sheet.Cells[2, 1].SetValue(128742);
                    sheet.Cells[3, 1].SetValue(218737);
                    sheet.Cells[4, 1].SetValue(187025);

                    IPieChart pieChart = pieChartSpace.InsertPieChart()
                                         .SetFirstSliceAngle(Math.PI * 0.5)
                                         .SetExplosion(0.8)
                                         .SetHoleSize(0.5);

                    pieChart.Series
                    .SetText(workbook.GetRange("Sheet1!$A$2"))
                    .SetCategoryAxis(workbook.GetRange("Sheet1!$B$1:$E$1"))
                    .SetValueAxis(workbook.GetRange("Sheet1!B$2:$E$2"))
                    .DataLabel.SetShowValue(true)
                    .DataLabel.SetShowSeriesName(false)
                    .DataLabel.SetShowCategoryName(false)
                    .DataLabel.SetShowLegendKey(false)
                    .DataLabel.SetShowPercent(false)
                    .DataLabel.Text.SetFontColor(OpenXmlColor.Rgb(0xFFFFFF));

                    pieChart.Series.Values[0].Style.SetFill(OpenXmlColor.Rgb(0x00FFFF));
                    pieChart.Series.Values[1].Style.SetFill(OpenXmlColor.Rgb(0xFFFFFF));
                    pieChart.Series.Values[2].Style.SetFill(OpenXmlColor.Rgb(0xFFFF00));
                    pieChart.Series.Values[3].Style.SetFill(OpenXmlColor.Rgb(0xFF0000));
                }

                IChartVisual barChartVisual = chartSlide.ShapeTree.AppendChartVisual("Chart 2")
                                              .Transform.SetOffset(2032000, 719666)
                                              .Transform.SetExtents(8128000, 5418667);

                IChartSpace barChartSpace = barChartVisual.ChartSpace;
                using (ISpreadsheetDocument spreadsheet = barChartSpace.OpenSpreadsheetDocument())
                {
                    IWorkbook  workbook = spreadsheet.Workbook;
                    IWorksheet sheet    = workbook.AppendWorksheet("Sheet1");

                    string reference = sheet.Cells[0, 1].SetValue("Costs").Reference;

                    sheet.Cells["A2"].SetValue("Kategorie 1");
                    sheet.Cells["A3"].SetValue("Kategorie 2");
                    sheet.Cells["A4"].SetValue("Kategorie 3");
                    sheet.Cells["A5"].SetValue("Kategorie 4");

                    sheet.Cells["B1"].SetValue("Label 1");
                    sheet.Cells["C1"].SetValue("Label 2");
                    sheet.Cells["D1"].SetValue("Label 3");

                    sheet.Cells["B2"].SetValue(106);
                    sheet.Cells["B3"].SetValue(18742);
                    sheet.Cells["B4"].SetValue(237);
                    sheet.Cells["B5"].SetValue(1025);

                    sheet.Cells["C2"].SetValue(12306);
                    sheet.Cells["C3"].SetValue(3441);
                    sheet.Cells["C4"].SetValue(325234);
                    sheet.Cells["C5"].SetValue(123);

                    sheet.Cells["D2"].SetValue(25241);
                    sheet.Cells["D3"].SetValue(8345);
                    sheet.Cells["D4"].SetValue(132523);
                    sheet.Cells["D5"].SetValue(12345);

                    CartesianAxes axes = barChartSpace.AppendCartesianAxes();
                    axes.CategoryAxis.SetVisibility(false);

                    ILineChart lineChart = barChartSpace.InsertLineChart(axes)
                                           .InitializeFromRange(sheet.GetRange("B1:D1"), sheet.GetRange("A2:A5"));

                    IBarChart barChart = barChartSpace.InsertBarChart(axes)
                                         .SetDirection(BarChartDirection.Column)
                                         .SetGrouping(BarChartGrouping.Clustered)
                                         .InitializeFromRange(sheet.GetRange("B1:D1"), sheet.GetRange("A2:A5"));

                    barChart.Series[0]
                    .DataLabel.SetShowValue(true)
                    .DataLabel.SetShowSeriesName(false)
                    .DataLabel.SetShowCategoryName(false)
                    .DataLabel.SetShowLegendKey(false)
                    .DataLabel.Style.SetFill(OpenXmlColor.Rgb(0x000000).WithAlpha(0.3))
                    .DataLabel.Text.SetFontColor(OpenXmlColor.Rgb(0x00000));

                    barChart.Series[0].Values[0]
                    .DataLabel.SetDelete(true);

                    barChart.Series[0].Values[1]
                    .DataLabel.Text.SetFontColor(OpenXmlColor.Rgb(0xFF0000))
                    .DataLabel.Style.SetFill(OpenXmlColor.Accent4);

                    barChart.Series[1]
                    .DataLabel.SetShowValue(true)
                    .DataLabel.SetShowSeriesName(false)
                    .DataLabel.SetShowCategoryName(false)
                    .DataLabel.SetShowLegendKey(false)
                    .DataLabel.Style.SetFill(OpenXmlColor.Accent6.WithLuminanceModulation(0.75))
                    .DataLabel.Text.SetFontColor(OpenXmlColor.Rgb(0xFFFFFF));

                    barChart.Series[0].Values[1].Style.SetFill(OpenXmlColor.Accent2);
                    barChart.Series[1].Values[1].Style.SetFill(OpenXmlColor.Accent2);
                    barChart.Series[2].Values[1].Style.SetFill(OpenXmlColor.Accent2);

                    barChartSpace.CategoryAxes[0]
                    .Text.SetFontSize(8)
                    .MajorGridlines.Style.SetStroke(OpenXmlColor.Accent3)
                    .MajorGridlines.Style.SetStrokeWidth(2);

                    barChartSpace.ValueAxes[0]
                    .Text.SetFontSize(8)
                    .MajorGridlines.Remove();
                }

                chartSlide.ShapeTree.AppendShapeVisual("Shape 8")
                .Transform.SetOffset(OpenXmlUnit.Cm(3), OpenXmlUnit.Cm(3))
                .Transform.SetExtents(OpenXmlUnit.Cm(10), OpenXmlUnit.Cm(10))
                .Text.SetText("TEST 2")
                .Text.SetFontSize(8)
                .Text.SetTextAlign(TextAlignmentTypeValues.Center)
                .Text.SetTextAnchor(TextAnchoringTypeValues.Center)
                .Text.SetIsBold(true)
                .Style.SetFill(OpenXmlColor.Accent6.WithLuminanceModulation(0.75))
                .Style.SetStroke(OpenXmlColor.Rgb(0, 0, 255))
                .Style.SetStrokeWidth(0.5)
                .Style.SetStrokeDash(PresetLineDashValues.LargeDash)
                .Style.SetPresetGeometry(OpenXmlPresetGeometry.BuildChevron(28868));

                ISlide       burndownSlide       = presentation.InsertSlide(presentation.SlideMasters[0].SlideLayouts[6]);
                IChartVisual burndownChartVisual = burndownSlide.ShapeTree.AppendChartVisual("Burndown Chart 1")
                                                   .Transform.SetOffset(2032000, 719666)
                                                   .Transform.SetExtents(8128000, 5418667);

                /*IChartSpace burndownChartSpace = burndownChartVisual.ChartSpace;
                 * burndownChartSpace.BuildBurndownChart(new BurndownChartData(
                 *  new BurndownChartCategory(
                 *      "In Akquise",
                 *      new BurndownChartSeries("erfolgversprechend", 15, s => s.Style.SetFill(OpenXmlColor.Accent1)),
                 *      new BurndownChartSeries("unwahrscheinlich", 28, s => s.Style.SetFill(OpenXmlColor.Accent2))
                 *  ),
                 *  new BurndownChartCategory(
                 *      "Projektphase",
                 *      new BurndownChartSeries("in Arbeit", 32, s => s.Style.SetFill(OpenXmlColor.Accent3))
                 *  ),
                 *  new BurndownChartCategory(
                 *      "Abgeschlossen",
                 *      new BurndownChartSeries("erfolgreich", 86, s => s.Style.SetFill(OpenXmlColor.Accent4)),
                 *      new BurndownChartSeries("abgebrochen", 3, s => s.Style.SetFill(OpenXmlColor.Accent5))
                 *  )
                 * ))
                 *  .ConfigureConnector(c => c.Style.SetStroke(OpenXmlColor.Text1).Style.SetStrokeWidth(0.5))
                 *  .ConfigureTotal(t => t.Style.SetFill(OpenXmlColor.Accent5))
                 *  .ConfigureSeries(0, 0, s => s.Style.SetFill(OpenXmlColor.Rgb(0x00FF00)).DataLabel.SetShowValue(true).DataLabel.Text.SetFontColor(OpenXmlColor.Text1).DataLabel.Style.SetFill(OpenXmlColor.Light1))
                 *  .ConfigureSeries(0, 1, s => s.Style.SetFill(OpenXmlColor.Rgb(0xFFFF00)))
                 *  .ConfigureSeries(1, 0, s => s.Style.SetFill(OpenXmlColor.Rgb(0x0000FF)))
                 *  .ConfigureSeries(2, 0, s => s.Style.SetFill(OpenXmlColor.Rgb(0x00FF00)))
                 *  .ConfigureSeries(2, 1, s => s.Style.SetFill(OpenXmlColor.Rgb(0xFF0000)));*/

                new BurndownChartConfig()
                .AddCategory("In Akquise")
                .AddValue("erfolgsversprechend", 15)
                .WithStyle(s => s.Style.SetFill(OpenXmlColor.Rgb(0x00FF00)).DataLabel.SetShowValue(true).DataLabel.Text.SetFontColor(OpenXmlColor.Text1).DataLabel.Style.SetFill(OpenXmlColor.Light1))
                .AddValue("unwahrscheinlich", 28)
                .WithStyle(s => s.Style.SetFill(OpenXmlColor.Rgb(0xFFFF00)))
                .AddCategory("Projektphase")
                .AddValue("in Arbeit", 32)
                .AddCategory("Abgeschlossen")
                .WithCustomOffset(0)
                .AddValue("in Arbeit", 86)
                .AddCategory("Abgeschlossen - erfolgreich")
                .WithCustomOffset(0)
                .AddValue("in Arbeit", 56)
                .AddCategory("Abgeschlossen - abgebrochen")
                .WithCustomOffset(86)
                .AddValue("in Arbeit", 63)
                .WithConnectorStyle(c => c.Style.SetStroke(OpenXmlColor.Text1).Style.SetStrokeWidth(0.5))
                .WithTotal("Gesamt")
                .WithTotalStyle(t => t.Style.SetFill(OpenXmlColor.Accent5))
                .Apply(burndownChartVisual.ChartSpace);

                ISlide tableSlide = presentation.InsertSlide(presentation.SlideMasters[0].SlideLayouts[6]);

                ITableStyle tableStyle = CreateTableStyle(presentation);

                ITableVisual table = tableSlide.ShapeTree.AppendTableVisual("Table 1")
                                     .Transform.SetOffset(OpenXmlUnit.Cm(5), OpenXmlUnit.Cm(5))
                                     .SetStyle(tableStyle)
                                     .SetHasFirstRow(true)
                                     .SetHasBandRow(true);

                table.AppendColumn(OpenXmlUnit.Inch(1));
                table.AppendColumn(OpenXmlUnit.Cm(5));
                table.AppendRow(OpenXmlUnit.Cm(0));
                table.AppendRow(OpenXmlUnit.Cm(0));

                table.Cells[0, 0].Text.SetText("Hello World") /*.Text.SetFontSize(28).Text.SetFont("Arial")*/;
                table.Cells[1, 0].Text.SetText("World") /*.Text.SetIsBold(true).Text.SetFont("Comic Sans MS")*/;
                table.Cells[0, 1].Text.SetText("ABC") /*.Text.SetFontColor(OpenXmlColor.Accent2)*/;
                table.Cells[1, 1].Text.SetText("123") /*.Text.SetFontColor(OpenXmlColor.Rgb(25, 240, 120))*/;

                OpenXmlSize rowSize    = table.Rows[0].Measure();
                OpenXmlSize columnSize = table.Columns[0].Measure();
                OpenXmlSize tableSize  = table.Measure();

                using (Stream stream = new FileStream(destination, FileMode.Create, FileAccess.ReadWrite))
                {
                    presentation.Close(stream);
                }
            }

            new Process
            {
                StartInfo = new ProcessStartInfo(destination)
                {
                    UseShellExecute = true
                }
            }.Start();
        }
Example #3
0
        public static BurndownChartWizard BuildBurndownChart(IChartSpace chartSpace, BurndownChartData data)
        {
            using (ISpreadsheetDocument spreadsheet = chartSpace.OpenSpreadsheetDocument())
            {
                IWorksheet seriesSheet    = spreadsheet.Workbook.AppendWorksheet("Series");
                IWorksheet connectorSheet = spreadsheet.Workbook.AppendWorksheet("Connector");

                double total = data.Categories.Sum(c => c.Series.Sum(s => s.Value));

                seriesSheet.Cells[0, 1].SetValue("Offset");
                seriesSheet.Cells[0, 2].SetValue("Total");

                seriesSheet.Cells[1, 0].SetValue("Total");
                connectorSheet.Cells[1, 0].SetValue("Total");

                seriesSheet.Cells[1, 1].SetValue(0);
                seriesSheet.Cells[1, 2].SetValue(total);

                double offset       = total;
                uint   column       = 2;
                uint   seriesRow    = 3;
                uint   connectorRow = 1;
                foreach (BurndownChartCategory category in data.Categories)
                {
                    // set name of category
                    seriesSheet.Cells[column, 0].SetValue(category.Name);
                    connectorSheet.Cells[column, 0].SetValue(category.Name);

                    // set connector of category
                    connectorSheet.Cells[column - 1, connectorRow].SetValue(offset);
                    connectorSheet.Cells[column, connectorRow].SetValue(offset);

                    // set offset of category
                    offset -= category.Series.Sum(s => s.Value);
                    seriesSheet.Cells[column, 1].SetValue(offset);

                    uint seriesIdx   = 0;
                    uint seriesCount = (uint)category.Series.Length;
                    foreach (BurndownChartSeries series in category.Series)
                    {
                        // set name of series
                        seriesSheet.Cells[0, seriesRow + seriesCount - seriesIdx - 1].SetValue(series.Name);

                        // set value of series
                        seriesSheet.Cells[column, seriesRow + seriesCount - seriesIdx - 1].SetValue(Math.Abs(series.Value));

                        ++seriesIdx;
                    }

                    seriesRow += (uint)category.Series.Length;

                    ++connectorRow;
                    ++column;
                }

                CartesianAxes axes = chartSpace.AppendCartesianAxes();

                ILineChart lineChart = chartSpace.InsertLineChart(axes)
                                       .InitializeFromRange(connectorSheet.GetRange(0, 1, 0, connectorRow - 1), connectorSheet.GetRange(1, 0, column - 1, 0));

                IBarChart barChart = chartSpace.InsertBarChart(axes)
                                     .InitializeFromRange(seriesSheet.GetRange(0, 1, 0, seriesRow - 1), seriesSheet.GetRange(1, 0, column - 1, 0))
                                     .SetDirection(BarChartDirection.Column)
                                     .SetGrouping(BarChartGrouping.Stacked)
                                     .SetOverlap(1.0);

                // hide offset series
                barChart.Series[0].Style.SetNoFill();

                if (data.TotalCallback != null)
                {
                    data.TotalCallback(barChart.Series[1].Values[1]);
                }

                int valueIndex  = 1;
                int seriesIndex = 2;
                foreach (BurndownChartCategory category in data.Categories)
                {
                    int seriesIdx = 0;
                    foreach (BurndownChartSeries series in category.Series)
                    {
                        if (series.Callback != null)
                        {
                            IBarChartSeries s = barChart.Series[seriesIndex + category.Series.Length - seriesIdx - 1];
                            series.Callback(s.Values[valueIndex]);
                        }

                        ++seriesIdx;
                    }

                    seriesIndex += category.Series.Length;
                    ++valueIndex;
                }

                axes.CategoryAxis
                .Text.SetFontSize(6)
                .MajorGridlines.Remove();

                axes.ValueAxis
                .SetVisibility(false)
                .MajorGridlines.Remove();

                return(new BurndownChartWizard(chartSpace, data, axes, barChart, lineChart));
            }
        }