Shapes, Pictures, Controls and Charts - DON-PHAM/EPPlus GitHub Wiki

Shapes, Pictures, Controls and Charts

Shapes, Pictures, Form Controls and Charts are all added via the Drawings collection of the ExcelWorksheet class. EPPlus 5 supports several new styling features for Drawing objects like shadows, glows, blurs and 3D effects. For example

            //Add the textbox
            var shape = worksheet.Drawings.AddShape("myShape", eShapeStyle.Rect);
            shape.SetPosition(1, 5, 6, 5);       //Position Row, RowOffsetPixels, Column, ColumnOffsetPixels
            shape.SetSize(400, 200);             //Size in pixels
            //Add some effects and set the text
            shape.Effect.SetPresetShadow(ePresetExcelShadowType.OuterBottomRight);
            shape.Effect.OuterShadow.Distance = 10;
            shape.Effect.SetPresetSoftEdges(ePresetExcelSoftEdgesType.SoftEdge5Pt);
            shape.Text = "This is a rectangular shape.";

Adding an image to the workbook will work very similar way.

            //Add an jpg image and apply some effects (EPPlus 6+ interface).
            var pic = worksheet.Drawings.AddPicture("Landscape", new FileInfo("c:\\temp\\LandscapeView.jpg"));
            pic.SetPosition(2, 0, 1, 0);
            pic.Effect.SetPresetShadow(ePresetExcelShadowType.OuterBottomRight);
            pic.Effect.OuterShadow.Distance = 10;
            pic.Effect.SetPresetSoftEdges(ePresetExcelSoftEdgesType.SoftEdge5Pt);

To add a chart use the AddChart method or even better its typed variant...

            //Add the piechart
            var pieChart = worksheet.Drawings.AddPieChart("crtExtensionsSize", ePieChartType.PieExploded3D);
            //Set top left corner to row 1 column 2
            pieChart.SetPosition(1, 0, 2, 0);
            pieChart.SetSize(400, 400);
            pieChart.Series.Add(ExcelRange.GetAddress(3, 2, row-1, 2), ExcelRange.GetAddress(3, 1, row-1, 1));

            pieChart.Title.Text = "Extension Size";
            //Set datalabels and remove the legend
            pieChart.DataLabel.ShowCategory = true;
            pieChart.DataLabel.ShowPercent = true;
            pieChart.DataLabel.ShowLeaderLines = true;
            pieChart.Legend.Remove();

From EPPlus 5.2, all chart types are supported, even newer types like Sunburst Charts and Region Maps.

Here's a piece of code from the sample project showing how to add a Sunburst Chart:

            var ws = package.Workbook.Worksheets.Add("Sunburst & Treemap Chart");
            var range = await LoadSalesFromDatabase(connectionString, ws);

            var sunburstChart = ws.Drawings.AddSunburstChart("SunburstChart1");
            var sbSerie = sunburstChart.Series.Add(ws.Cells[2, 4, range.Rows, 4], ws.Cells[2, 1, range.Rows, 3]);
            sbSerie.HeaderAddress = ws.Cells["D1"];
            sunburstChart.SetPosition(1, 0, 6, 0);
            sunburstChart.SetSize(800, 800);
            sunburstChart.Title.Text = "Sales";            
            sunburstChart.Legend.Add();
            sunburstChart.Legend.Position = eLegendPosition.Bottom;
            sbSerie.DataLabel.Add(true, true);
            sunburstChart.StyleManager.SetChartStyle(ePresetChartStyle.SunburstChartStyle3);

Shapes

Supports adding 187 different types of shapes with multiple way of formatting. See Sample 14 for more detailed examples.

Pictures

EPPlus supports adding images from a file, a stream or via an image object. See Sample 14 for more detailed examples.

Formats supported by EPPlus 5.x

• Jpeg
• Gif
• Png
• Tiff
• Bmp
• Emf (Windows)
• Wmf (Windows)

Formats supported by EPPlus 6+

• Jpeg
• Gif
• Png
• Tiff
• Bmp
• Emf (all platforms)
• Wmf (all platforms)
• ico
• svg
• webp

Charts

Supports all Excel 2019 chart types with modern chart styling. Also see Supported-Charts

Form Controls

From EPPlus 5.5 adding, removing and modifying form controls is supported.
Supported form controls are:

• Buttons
• Drop-Downs
• List Boxes
• Check Boxes
• Radio Buttons
• Spin Buttons
• Scroll Bars
• Labels
• Group Boxes For controls can be linked to a cell or connected to a macro. See Form-Controls for more details

For more details have a look at sample 14 & 15 in the sample project Sample-.NET Framework or Sample-.NET Framework.