EPPlusTest/DrawingTest.cs

using System;
using System.Drawing;
using System.IO;
using System.Xml;
using EPPlusTest.Properties;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using OfficeOpenXml;
using OfficeOpenXml.Drawing;
using OfficeOpenXml.Drawing.Chart;
using OfficeOpenXml.Style;

namespace EPPlusTest
{
    /// <summary>
    /// Summary description for UnitTest1
    /// </summary>
    [TestClass]
    public class DrawingTest : TestBase
    {
        [TestMethod]
        public void RunDrawingTests()
        {
            BarChart();
            Column();
            Cone();
            Dougnut();
            Drawings();
            Line();
            LineMarker();
            PieChart();
            PieChart3D();
            Pyramid();
            Scatter();
            Bubble();
            Radar();
            Surface();
            Line2Test();
            MultiChartSeries();
            DrawingSizingAndPositioning();
            DeleteDrawing();
            
            SaveWorksheet("Drawing.xlsx");

            ReadDocument();
            ReadDrawing();
        }
        //[TestMethod]
        //[Ignore]
        public void ReadDrawing()
        {
            using (ExcelPackage pck = new ExcelPackage(new FileInfo(_worksheetPath + @"Drawing.xlsx")))
            {
                var ws = pck.Workbook.Worksheets["Pyramid"];
                Assert.AreEqual(ws.Cells["V24"].Value, 104D);
                ws = pck.Workbook.Worksheets["Scatter"];
                var cht = ws.Drawings["ScatterChart1"] as ExcelScatterChart;
                Assert.AreEqual(cht.Title.Text, "Header  Text");
                cht.Title.Text = "Test";
                Assert.AreEqual(cht.Title.Text, "Test");
            }
        }
        
        [TestMethod]
        public void Picture()
         {
            var ws = _pck.Workbook.Worksheets.Add("Picture");
            var pic = ws.Drawings.AddPicture("Pic1", Resources.Test1);

            pic = ws.Drawings.AddPicture("Pic2", Resources.Test1);
            pic.SetPosition(150, 200);
            pic.Border.LineStyle = eLineStyle.Solid;
            pic.Border.Fill.Color = Color.DarkCyan;
            pic.Fill.Style = eFillStyle.SolidFill;
            pic.Fill.Color = Color.White;
            pic.Fill.Transparancy = 50;

            pic = ws.Drawings.AddPicture("Pic3", Resources.Test1);
            pic.SetPosition(400, 200);
            pic.SetSize(150);

            pic = ws.Drawings.AddPicture("Pic4", new FileInfo(Path.Combine(_clipartPath, "Vector Drawing.wmf")));
            pic = ws.Drawings.AddPicture("Pic5", new FileInfo(Path.Combine(_clipartPath,"BitmapImage.gif")));
            pic.SetPosition(400, 200);
            pic.SetSize(150);

            ws.Column(1).Width = 53;
            ws.Column(4).Width = 58;

            pic = ws.Drawings.AddPicture("Pic6öäå", new FileInfo(Path.Combine(_clipartPath, "BitmapImage.gif")));
            pic.SetPosition(400, 400);
            pic.SetSize(100);

             var ws2 = _pck.Workbook.Worksheets.Add("Picture2");
            var fi = new FileInfo(@"C:\Program Files (x86)\Microsoft Office\CLIPART\PUB60COR\AG00021_.GIF");
            if (fi.Exists)
            {
                pic = ws2.Drawings.AddPicture("Pic7", fi);
            }
            else
            {
                TestContext.WriteLine("AG00021_.GIF does not exists. Skiping Pic7.");
            }

            var wsCopy = _pck.Workbook.Worksheets.Add("Picture3", ws2);
            _pck.Workbook.Worksheets.Delete(ws2);
         }
         //[TestMethod]
         //[Ignore]
         public void DrawingSizingAndPositioning()
         {
             var ws = _pck.Workbook.Worksheets.Add("DrawingPosSize");

             var pic = ws.Drawings.AddPicture("Pic1", Resources.Test1);
             pic.SetPosition(1, 0, 1, 0);

             pic = ws.Drawings.AddPicture("Pic2", Resources.Test1);
             pic.EditAs = eEditAs.Absolute;
             pic.SetPosition(10, 5, 1, 4);

             pic = ws.Drawings.AddPicture("Pic3", Resources.Test1);
             pic.EditAs = eEditAs.TwoCell;
             pic.SetPosition(20, 5, 2, 4);


             ws.Column(1).Width = 100;
             ws.Column(3).Width = 100;
         }

        //[TestMethod]
        // [Ignore]
         public void BarChart()
        {
            var ws = _pck.Workbook.Worksheets.Add("BarChart");            
            var chrt = ws.Drawings.AddChart("barChart", eChartType.BarClustered) as ExcelBarChart;
            chrt.SetPosition(50, 50);
            chrt.SetSize(800, 300);
            AddTestSerie(ws, chrt);
            chrt.VaryColors = true;
            chrt.XAxis.Orientation = eAxisOrientation.MaxMin;
            chrt.XAxis.MajorTickMark = eAxisTickMark.In;
            chrt.YAxis.Orientation = eAxisOrientation.MaxMin;
            chrt.YAxis.MinorTickMark = eAxisTickMark.Out;
            chrt.ShowHiddenData = true;
            chrt.DisplayBlanksAs = eDisplayBlanksAs.Zero;
            chrt.Title.RichText.Text = "Barchart Test";
            Assert.IsTrue(chrt.ChartType == eChartType.BarClustered, "Invalid Charttype");
            Assert.IsTrue(chrt.Direction == eDirection.Bar, "Invalid Bardirection");
            Assert.IsTrue(chrt.Grouping == eGrouping.Clustered, "Invalid Grouping");
            Assert.IsTrue(chrt.Shape == eShape.Box, "Invalid Shape");
        }

        private static void AddTestSerie(ExcelWorksheet ws, ExcelChart chrt)
        {
            AddTestData(ws);
            chrt.Series.Add("'" + ws.Name + "'!V19:V24", "'" + ws.Name + "'!U19:U24");
        }

        private static void AddTestData(ExcelWorksheet ws)
        {
            ws.Cells["U19"].Value = new DateTime(2009, 12, 31);
            ws.Cells["U20"].Value = new DateTime(2010, 1, 1);
            ws.Cells["U21"].Value = new DateTime(2010, 1, 2);
            ws.Cells["U22"].Value = new DateTime(2010, 1, 3);
            ws.Cells["U23"].Value = new DateTime(2010, 1, 4);
            ws.Cells["U24"].Value = new DateTime(2010, 1, 5);
            ws.Cells["U19:U24"].Style.Numberformat.Format = "yyyy-mm-dd";

            ws.Cells["V19"].Value = 100;
            ws.Cells["V20"].Value = 102;
            ws.Cells["V21"].Value = 101;
            ws.Cells["V22"].Value = 103;
            ws.Cells["V23"].Value = 105;
            ws.Cells["V24"].Value = 104;

            ws.Cells["X19"].Value = "öäå";
            ws.Cells["X20"].Value = "ÖÄÅ";
            ws.Cells["X21"].Value = "üÛ";
            ws.Cells["X22"].Value = "&%#¤";
            ws.Cells["X23"].Value = "ÿ";
            ws.Cells["X24"].Value = "û";
        }
        //[TestMethod]
        //[Ignore]
        public void PieChart()
        {
            var ws = _pck.Workbook.Worksheets.Add("PieChart");
            var chrt = ws.Drawings.AddChart("pieChart", eChartType.Pie) as ExcelPieChart;
            
            AddTestSerie(ws, chrt);

            chrt.To.Row = 25;
            chrt.To.Column = 12;

            chrt.DataLabel.ShowPercent = true;
            chrt.Legend.Font.Color = Color.SteelBlue;
            chrt.Title.Border.Fill.Style = eFillStyle.SolidFill;
            chrt.Legend.Position = eLegendPosition.TopRight;
            Assert.IsTrue(chrt.ChartType == eChartType.Pie, "Invalid Charttype");
            Assert.IsTrue(chrt.VaryColors);
            chrt.Title.Text = "Piechart";
        }
        //[TestMethod]
        //[Ignore]
        public void PieChart3D()
        {
            var ws = _pck.Workbook.Worksheets.Add("PieChart3d");
            var chrt = ws.Drawings.AddChart("pieChart3d", eChartType.Pie3D) as ExcelPieChart;
            AddTestSerie(ws, chrt);

            chrt.To.Row = 25;
            chrt.To.Column = 12;

            chrt.DataLabel.ShowValue = true;
            chrt.Legend.Position = eLegendPosition.Left;
            chrt.ShowHiddenData = false;
            chrt.DisplayBlanksAs = eDisplayBlanksAs.Gap;
            chrt.Title.RichText.Add("Pie RT Title add");
            Assert.IsTrue(chrt.ChartType == eChartType.Pie3D, "Invalid Charttype");
            Assert.IsTrue(chrt.VaryColors);

        }
        //[TestMethod]
        //[Ignore]
        public void Scatter()
        {
            var ws = _pck.Workbook.Worksheets.Add("Scatter");
            var chrt = ws.Drawings.AddChart("ScatterChart1", eChartType.XYScatterSmoothNoMarkers) as ExcelScatterChart;
            AddTestSerie(ws, chrt);
           // chrt.Series[0].Marker = eMarkerStyle.Diamond;
            chrt.To.Row = 23;
            chrt.To.Column = 12;
            //chrt.Title.Text = "Header Text";
            var r1=chrt.Title.RichText.Add("Header");
            r1.Bold = true;
            var r2=chrt.Title.RichText.Add("  Text");
            r2.UnderLine = eUnderLineType.WavyHeavy;

            chrt.Title.Fill.Style = eFillStyle.SolidFill;
            chrt.Title.Fill.Color = Color.LightBlue;
            chrt.Title.Fill.Transparancy = 50;
            chrt.VaryColors = true;
            ExcelScatterChartSerie ser = chrt.Series[0] as ExcelScatterChartSerie;
            ser.DataLabel.Position = eLabelPosition.Center;
            ser.DataLabel.ShowValue = true;
            ser.DataLabel.ShowCategory = true;
            ser.DataLabel.Fill.Color = Color.BlueViolet;
            ser.DataLabel.Font.Color = Color.White;
            ser.DataLabel.Font.Italic = true;
            ser.DataLabel.Font.SetFromFont(new Font("bookman old style", 8));
            Assert.IsTrue(chrt.ChartType == eChartType.XYScatterSmoothNoMarkers, "Invalid Charttype");
            chrt.Series[0].Header = "Test serie";
            chrt = ws.Drawings.AddChart("ScatterChart2", eChartType.XYScatterSmooth) as ExcelScatterChart;
            chrt.Series.Add("U19:U24", "V19:V24");

            chrt.From.Column = 0;
            chrt.From.Row=25;
            chrt.To.Row = 53;
            chrt.To.Column = 12;
            chrt.Legend.Position = eLegendPosition.Bottom;
            
            ////chrt.Series[0].DataLabel.Position = eLabelPosition.Center;
            //Assert.IsTrue(chrt.ChartType == eChartType.XYScatter, "Invalid Charttype");

        }
        //[TestMethod]
        //[Ignore]
        public void Bubble()
        {
            var ws = _pck.Workbook.Worksheets.Add("Bubble");
            var chrt = ws.Drawings.AddChart("Bubble", eChartType.Bubble) as ExcelBubbleChart;
            AddTestData(ws);

            chrt.Series.Add("V19:V24", "U19:U24");

            chrt = ws.Drawings.AddChart("Bubble3d", eChartType.Bubble3DEffect) as ExcelBubbleChart;
            ws.Cells["W19"].Value = 1;
            ws.Cells["W20"].Value = 1;
            ws.Cells["W21"].Value = 2;
            ws.Cells["W22"].Value = 2;
            ws.Cells["W23"].Value = 3;
            ws.Cells["W24"].Value = 4;

            chrt.Series.Add("V19:V24", "U19:U24", "W19:W24");
            chrt.Style = eChartStyle.Style25;

            // chrt.Series[0].Marker = eMarkerStyle.Diamond;
            chrt.From.Row = 23;
            chrt.From.Column = 12;
            chrt.To.Row = 33;
            chrt.To.Column = 22;
            chrt.Title.Text = "Header Text";
            

        }
        //[TestMethod]
        //[Ignore]
        public void Radar()
        {
            var ws = _pck.Workbook.Worksheets.Add("Radar");
            AddTestData(ws);

            var chrt = ws.Drawings.AddChart("Radar1", eChartType.Radar) as ExcelRadarChart;
            var s=chrt.Series.Add("V19:V24", "U19:U24");
            s.Header = "serie1";
            // chrt.Series[0].Marker = eMarkerStyle.Diamond;
            chrt.From.Row = 23;
            chrt.From.Column = 12;
            chrt.To.Row = 38;
            chrt.To.Column = 22;
            chrt.Title.Text = "Radar Chart 1";

            chrt = ws.Drawings.AddChart("Radar2", eChartType.RadarFilled) as ExcelRadarChart;
            s = chrt.Series.Add("V19:V24", "U19:U24");
            s.Header = "serie1";
            // chrt.Series[0].Marker = eMarkerStyle.Diamond;
            chrt.From.Row = 43;
            chrt.From.Column = 12;
            chrt.To.Row = 58;
            chrt.To.Column = 22;
            chrt.Title.Text = "Radar Chart 2";

            chrt = ws.Drawings.AddChart("Radar3", eChartType.RadarMarkers) as ExcelRadarChart;
            var rs = (ExcelRadarChartSerie)chrt.Series.Add("V19:V24", "U19:U24");
            rs.Header = "serie1";
            rs.Marker = eMarkerStyle.Star;
            rs.MarkerSize = 14;

            // chrt.Series[0].Marker = eMarkerStyle.Diamond;
            chrt.From.Row = 63;
            chrt.From.Column = 12;
            chrt.To.Row = 78;
            chrt.To.Column = 22;
            chrt.Title.Text = "Radar Chart 3";
        }
        //[TestMethod]
        //[Ignore]
        public void Surface()
        {
            var ws = _pck.Workbook.Worksheets.Add("Surface");
            AddTestData(ws);

            var chrt = ws.Drawings.AddChart("Surface1", eChartType.Surface) as ExcelSurfaceChart;
            var s = chrt.Series.Add("V19:V24", "U19:U24");
            s.Header = "serie1";
            // chrt.Series[0].Marker = eMarkerStyle.Diamond;
            chrt.From.Row = 23;
            chrt.From.Column = 12;
            chrt.To.Row = 38;
            chrt.To.Column = 22;
            chrt.Title.Text = "Surface Chart 1";

            //chrt = ws.Drawings.AddChart("Surface", eChartType.RadarFilled) as ExcelRadarChart;
            //s = chrt.Series.Add("V19:V24", "U19:U24");
            //s.Header = "serie1";
            //// chrt.Series[0].Marker = eMarkerStyle.Diamond;
            //chrt.From.Row = 43;
            //chrt.From.Column = 12;
            //chrt.To.Row = 58;
            //chrt.To.Column = 22;
            //chrt.Title.Text = "Radar Chart 2";

            //chrt = ws.Drawings.AddChart("Radar3", eChartType.RadarMarkers) as ExcelRadarChart;
            //var rs = (ExcelRadarChartSerie)chrt.Series.Add("V19:V24", "U19:U24");
            //rs.Header = "serie1";
            //rs.Marker = eMarkerStyle.Star;
            //rs.MarkerSize = 14;

            //// chrt.Series[0].Marker = eMarkerStyle.Diamond;
            //chrt.From.Row = 63;
            //chrt.From.Column = 12;
            //chrt.To.Row = 78;
            //chrt.To.Column = 22;
            //chrt.Title.Text = "Radar Chart 3";
        }
        //[TestMethod]
        //[Ignore]
        public void Pyramid()
        {
            var ws = _pck.Workbook.Worksheets.Add("Pyramid");
            var chrt = ws.Drawings.AddChart("Pyramid1", eChartType.PyramidCol) as ExcelBarChart;
            AddTestSerie(ws, chrt);
            // chrt.Series[0].Marker = eMarkerStyle.Diamond;
            chrt.VaryColors = true;
            chrt.To.Row = 23;
            chrt.To.Column = 12;
            chrt.Title.Text = "Header Text";
            chrt.Title.Fill.Style= eFillStyle.SolidFill;
            chrt.Title.Fill.Color = Color.DarkBlue;
            chrt.DataLabel.ShowValue = true;
            //chrt.DataLabel.ShowSeriesName = true;
            //chrt.DataLabel.Separator = ",";
            chrt.Border.LineCap = eLineCap.Round;            
            chrt.Border.LineStyle = eLineStyle.LongDashDotDot;
            chrt.Border.Fill.Style = eFillStyle.SolidFill;
            chrt.Border.Fill.Color = Color.Blue;

            chrt.Fill.Color = Color.LightCyan;
            chrt.PlotArea.Fill.Color = Color.White;
            chrt.PlotArea.Border.Fill.Style = eFillStyle.SolidFill;
            chrt.PlotArea.Border.Fill.Color = Color.Beige;
            chrt.PlotArea.Border.LineStyle = eLineStyle.LongDash;

            chrt.Legend.Fill.Color = Color.Aquamarine;
            chrt.Legend.Position = eLegendPosition.Top;
            chrt.Axis[0].Fill.Style = eFillStyle.SolidFill;
            chrt.Axis[0].Fill.Color = Color.Black;
            chrt.Axis[0].Font.Color = Color.White;

            chrt.Axis[1].Fill.Style = eFillStyle.SolidFill;
            chrt.Axis[1].Fill.Color = Color.LightSlateGray;
            chrt.Axis[1].Font.Color = Color.DarkRed;

            chrt.DataLabel.Font.Bold = true;
            chrt.DataLabel.Fill.Color = Color.LightBlue;
            chrt.DataLabel.Border.Fill.Style=eFillStyle.SolidFill;
            chrt.DataLabel.Border.Fill.Color=Color.Black;
            chrt.DataLabel.Border.LineStyle = eLineStyle.Solid;
        }
        //[TestMethod]
        //[Ignore]
        public void Cone()
        {
            var ws = _pck.Workbook.Worksheets.Add("Cone");
            var chrt = ws.Drawings.AddChart("Cone1", eChartType.ConeBarClustered) as ExcelBarChart;
            AddTestSerie(ws, chrt);
            chrt.VaryColors = true;
            chrt.SetSize(200);
            chrt.Title.Text = "Cone bar";
            chrt.Series[0].Header = "Serie 1";
            chrt.Legend.Position = eLegendPosition.Right;
            chrt.Axis[1].DisplayUnit = 100000;
            Assert.AreEqual(chrt.Axis[1].DisplayUnit, 100000);
        }
        //[TestMethod]
        //[Ignore]
        public void Column()
        {
            var ws = _pck.Workbook.Worksheets.Add("Column");
            var chrt = ws.Drawings.AddChart("Column1", eChartType.ColumnClustered3D) as ExcelBarChart;
            AddTestSerie(ws, chrt);
            chrt.VaryColors = true;
            chrt.View3D.RightAngleAxes = true;
            chrt.View3D.DepthPercent = 99;
            //chrt.View3D.HeightPercent = 99;
            chrt.View3D.RightAngleAxes = true;
            chrt.SetSize(200);
            chrt.Title.Text = "Column";
            chrt.Series[0].Header = "Serie 1";
            chrt.Locked = false;
            chrt.Print = false;
            chrt.EditAs = eEditAs.TwoCell;
            chrt.Axis[1].DisplayUnit = 10020;
            Assert.AreEqual(chrt.Axis[1].DisplayUnit, 10020);
        }
        //[TestMethod]
        //[Ignore]
        public void Dougnut()
        {
            var ws = _pck.Workbook.Worksheets.Add("Dougnut");
            var chrt = ws.Drawings.AddChart("Dougnut1", eChartType.DoughnutExploded) as ExcelDoughnutChart;
            AddTestSerie(ws, chrt);
            chrt.SetSize(200);
            chrt.Title.Text = "Doughnut Exploded";
            chrt.Series[0].Header = "Serie 1";
            chrt.EditAs = eEditAs.Absolute;
        }
        //[TestMethod]
        //[Ignore]
        public void Line()
        {
            var ws = _pck.Workbook.Worksheets.Add("Line");
            var chrt = ws.Drawings.AddChart("Line1", eChartType.Line) as ExcelLineChart;
            AddTestSerie(ws, chrt);
            chrt.SetSize(150);
            chrt.VaryColors = true;
            chrt.Smooth = false;
            chrt.Title.Text = "Line 3D";
            chrt.Series[0].Header = "Line serie 1";
            var tl = chrt.Series[0].TrendLines.Add(eTrendLine.Polynomial);
            tl.Name = "Test";
            tl.DisplayRSquaredValue = true;
            tl.DisplayEquation = true;
            tl.Forward = 15;
            tl.Backward = 1;
            tl.Intercept = 6;
            //tl.Period = 12;
            tl.Order = 5;

            tl = chrt.Series[0].TrendLines.Add(eTrendLine.MovingAvgerage);
            chrt.Fill.Color = Color.LightSteelBlue;
            chrt.Border.LineStyle = eLineStyle.Dot;
            chrt.Border.Fill.Color=Color.Black;

            chrt.Legend.Font.Color = Color.Red;
            chrt.Legend.Font.Strike = eStrikeType.Double;
            chrt.Title.Font.Color = Color.DarkGoldenrod;
            chrt.Title.Font.LatinFont = "Arial";
            chrt.Title.Font.Bold = true;
            chrt.Title.Fill.Color = Color.White;
            chrt.Title.Border.Fill.Style = eFillStyle.SolidFill;
            chrt.Title.Border.LineStyle = eLineStyle.LongDashDotDot;
            chrt.Title.Border.Fill.Color = Color.Tomato;
            chrt.DataLabel.ShowSeriesName = true;
            chrt.DataLabel.ShowLeaderLines=true;
            chrt.EditAs = eEditAs.OneCell;
            chrt.DisplayBlanksAs = eDisplayBlanksAs.Span;
            chrt.Axis[0].Title.Text = "Axis 0";
            chrt.Axis[0].Title.Rotation = 90;
            chrt.Axis[0].Title.Overlay = true;
            chrt.Axis[1].Title.Text = "Axis 1";
            chrt.Axis[1].Title.AnchorCtr = true;
            chrt.Axis[1].Title.TextVertical = eTextVerticalType.Vertical270;
            chrt.Axis[1].Title.Border.LineStyle=eLineStyle.LongDashDotDot;

        }
        //[TestMethod]
        //[Ignore]
        public void LineMarker()
        {
            var ws = _pck.Workbook.Worksheets.Add("LineMarker1");
            var chrt = ws.Drawings.AddChart("Line1", eChartType.LineMarkers) as ExcelLineChart;
            AddTestSerie(ws, chrt);
            chrt.SetSize(150);
            chrt.Title.Text = "Line Markers";
            chrt.Series[0].Header = "Line serie 1";
            ((ExcelLineChartSerie)chrt.Series[0]).Marker = eMarkerStyle.Plus;

            var chrt2 = ws.Drawings.AddChart("Line2", eChartType.LineMarkers) as ExcelLineChart;
            AddTestSerie(ws, chrt2);
            chrt2.SetPosition(500,0);
            chrt2.SetSize(150);
            chrt2.Title.Text = "Line Markers";
            var serie = (ExcelLineChartSerie)chrt2.Series[0];
            serie.Marker = eMarkerStyle.X;

        }
        //[TestMethod]
        //[Ignore]
        public void Drawings()
        {
            var ws = _pck.Workbook.Worksheets.Add("Shapes");
            int y=100, i=1;
            foreach(eShapeStyle style in Enum.GetValues(typeof(eShapeStyle)))
            {
                var shape = ws.Drawings.AddShape("shape"+i.ToString(), style);
                shape.SetPosition(y, 100);
                shape.SetSize(300, 300);
                y += 400;
                shape.Text = style.ToString();
                i++;
            }

            (ws.Drawings["shape1"] as ExcelShape).TextAnchoring = eTextAnchoringType.Top;            
            var rt = (ws.Drawings["shape1"] as ExcelShape).RichText.Add("Added formated richtext");
            (ws.Drawings["shape1"] as ExcelShape).LockText = false;
            rt.Bold = true;
            rt.Color = Color.Aquamarine;
            rt.Italic = true;
            rt.Size = 17;
            (ws.Drawings["shape2"] as ExcelShape).TextVertical = eTextVerticalType.Vertical;
            rt = (ws.Drawings["shape2"] as ExcelShape).RichText.Add("\r\nAdded formated richtext");
            rt.Bold = true;
            rt.Color = Color.DarkGoldenrod ;
            rt.SetFromFont(new Font("Times new roman", 18, FontStyle.Underline));
            rt.UnderLineColor = Color.Green;


            (ws.Drawings["shape3"] as ExcelShape).TextAnchoring=eTextAnchoringType.Bottom;
            (ws.Drawings["shape3"] as ExcelShape).TextAnchoringControl=true ;

            (ws.Drawings["shape4"] as ExcelShape).TextVertical = eTextVerticalType.Vertical270;
            (ws.Drawings["shape4"] as ExcelShape).TextAnchoring = eTextAnchoringType.Top;

            (ws.Drawings["shape5"] as ExcelShape).Fill.Style=eFillStyle.SolidFill;
            (ws.Drawings["shape5"] as ExcelShape).Fill.Color=Color.Red;
            (ws.Drawings["shape5"] as ExcelShape).Fill.Transparancy = 50;

            (ws.Drawings["shape6"] as ExcelShape).Fill.Style = eFillStyle.NoFill;
            (ws.Drawings["shape6"] as ExcelShape).Font.Color = Color.Black;
            (ws.Drawings["shape6"] as ExcelShape).Border.Fill.Color = Color.Black;

            (ws.Drawings["shape7"] as ExcelShape).Fill.Style = eFillStyle.SolidFill;
            (ws.Drawings["shape7"] as ExcelShape).Fill.Color=Color.Gray;
            (ws.Drawings["shape7"] as ExcelShape).Border.Fill.Style=eFillStyle.SolidFill;
            (ws.Drawings["shape7"] as ExcelShape).Border.Fill.Color = Color.Black;
            (ws.Drawings["shape7"] as ExcelShape).Border.Fill.Transparancy=43;
            (ws.Drawings["shape7"] as ExcelShape).Border.LineCap=eLineCap.Round;
            (ws.Drawings["shape7"] as ExcelShape).Border.LineStyle = eLineStyle.LongDash;
            (ws.Drawings["shape7"] as ExcelShape).Font.UnderLineColor = Color.Blue;
            (ws.Drawings["shape7"] as ExcelShape).Font.Color = Color.Black;
            (ws.Drawings["shape7"] as ExcelShape).Font.Bold = true;
            (ws.Drawings["shape7"] as ExcelShape).Font.LatinFont = "Arial";
            (ws.Drawings["shape7"] as ExcelShape).Font.ComplexFont = "Arial";
            (ws.Drawings["shape7"] as ExcelShape).Font.Italic = true;
            (ws.Drawings["shape7"] as ExcelShape).Font.UnderLine = eUnderLineType.Dotted;

            (ws.Drawings["shape8"] as ExcelShape).Fill.Style = eFillStyle.SolidFill;
            (ws.Drawings["shape8"] as ExcelShape).Font.LatinFont = "Miriam";
            (ws.Drawings["shape8"] as ExcelShape).Font.UnderLineColor = Color.CadetBlue;
            (ws.Drawings["shape8"] as ExcelShape).Font.UnderLine = eUnderLineType.Single;

            (ws.Drawings["shape9"] as ExcelShape).TextAlignment = eTextAlignment.Right;

        }
        [TestMethod]
        [Ignore]
        public void DrawingWorksheetCopy()
        {
            var wsShapes = _pck.Workbook.Worksheets.Add("Copy Shapes", _pck.Workbook.Worksheets["Shapes"]);
            var wsScatterChart = _pck.Workbook.Worksheets.Add("Copy Scatter", _pck.Workbook.Worksheets["Scatter"]);
            var wsPicture = _pck.Workbook.Worksheets.Add("Copy Picture", _pck.Workbook.Worksheets["Picture"]);
        }    
        //[TestMethod]
        //[Ignore]
        public void Line2Test()
        {
           ExcelWorksheet worksheet = _pck.Workbook.Worksheets.Add("LineIssue");

           ExcelChart chart = worksheet.Drawings.AddChart("LineChart", eChartType.Line);
           
           worksheet.Cells["A1"].Value=1;
           worksheet.Cells["A2"].Value=2;
           worksheet.Cells["A3"].Value=3;
           worksheet.Cells["A4"].Value=4;
           worksheet.Cells["A5"].Value=5;
           worksheet.Cells["A6"].Value=6;

           worksheet.Cells["B1"].Value=10000;
           worksheet.Cells["B2"].Value=10100;
           worksheet.Cells["B3"].Value=10200;
           worksheet.Cells["B4"].Value=10150;
           worksheet.Cells["B5"].Value=10250;
           worksheet.Cells["B6"].Value=10200;

           chart.Series.Add(ExcelRange.GetAddress(1, 2, worksheet.Dimension.End.Row, 2),
                            ExcelRange.GetAddress(1, 1, worksheet.Dimension.End.Row, 1));

           var Series = chart.Series[0];
           
           chart.Series[0].Header = "Blah";
        }
        //[TestMethod]
        //[Ignore]
        public void MultiChartSeries()
        {
            ExcelWorksheet worksheet = _pck.Workbook.Worksheets.Add("MultiChartTypes");

            ExcelChart chart = worksheet.Drawings.AddChart("chtPie", eChartType.LineMarkers);
            chart.SetPosition(100, 100);
            chart.SetSize(800,600);
            AddTestSerie(worksheet, chart);
            chart.Series[0].Header = "Serie5";
            chart.Style = eChartStyle.Style27;
            worksheet.Cells["W19"].Value = 120;
            worksheet.Cells["W20"].Value = 122;
            worksheet.Cells["W21"].Value = 121;
            worksheet.Cells["W22"].Value = 123;
            worksheet.Cells["W23"].Value = 125;
            worksheet.Cells["W24"].Value = 124;

            worksheet.Cells["X19"].Value = 90;
            worksheet.Cells["X20"].Value = 52;
            worksheet.Cells["X21"].Value = 88;
            worksheet.Cells["X22"].Value = 75;
            worksheet.Cells["X23"].Value = 77;
            worksheet.Cells["X24"].Value = 99;
            
            var cs2 = chart.PlotArea.ChartTypes.Add(eChartType.ColumnClustered);
            var s = cs2.Series.Add(worksheet.Cells["W19:W24"], worksheet.Cells["U19:U24"]);
            s.Header = "Serie4";
            cs2.YAxis.MaxValue = 300;
            cs2.YAxis.MinValue = -5.5;
            var cs3 = chart.PlotArea.ChartTypes.Add(eChartType.Line);
            s=cs3.Series.Add(worksheet.Cells["X19:X24"], worksheet.Cells["U19:U24"]);
            s.Header = "Serie1";
            cs3.UseSecondaryAxis = true;
                        
            cs3.XAxis.Deleted = false;
            cs3.XAxis.MajorUnit = 20;
            cs3.XAxis.MinorUnit = 3;

            cs3.XAxis.TickLabelPosition = eTickLabelPosition.High;
            cs3.YAxis.LogBase = 10.2;

            var chart2 = worksheet.Drawings.AddChart("scatter1", eChartType.XYScatterSmooth);
            s=chart2.Series.Add(worksheet.Cells["W19:W24"], worksheet.Cells["U19:U24"]);
            s.Header = "Serie2";

            var c2ct2 = chart2.PlotArea.ChartTypes.Add(eChartType.XYScatterSmooth);
            s=c2ct2.Series.Add(worksheet.Cells["X19:X24"], worksheet.Cells["V19:V24"]);
            s.Header="Serie3";
            s=c2ct2.Series.Add(worksheet.Cells["W19:W24"], worksheet.Cells["V19:V24"]);
            s.Header = "Serie4";

            c2ct2.UseSecondaryAxis = true;
            c2ct2.XAxis.Deleted = false;
            c2ct2.XAxis.TickLabelPosition = eTickLabelPosition.High;

            ExcelChart chart3 = worksheet.Drawings.AddChart("chart", eChartType.LineMarkers);
            chart3.SetPosition(300, 1000);
            var s31=chart3.Series.Add(worksheet.Cells["W19:W24"], worksheet.Cells["U19:U24"]);
            s31.Header = "Serie1";

            var c3ct2 = chart3.PlotArea.ChartTypes.Add(eChartType.LineMarkers);
            var c32 = c3ct2.Series.Add(worksheet.Cells["X19:X24"], worksheet.Cells["V19:V24"]);
            c3ct2.UseSecondaryAxis = true;
            c32.Header = "Serie2";
            
            XmlNamespaceManager ns=new XmlNamespaceManager(new NameTable());
            ns.AddNamespace("c","http://schemas.openxmlformats.org/drawingml/2006/chart");
            var element = chart.ChartXml.SelectSingleNode("//c:plotVisOnly", ns);
            if (element!=null) element.ParentNode.RemoveChild(element);
        }
        //[TestMethod]
        //[Ignore]
        public void DeleteDrawing()
        {
            var ws=_pck.Workbook.Worksheets.Add("DeleteDrawing1");
            var chart1 = ws.Drawings.AddChart("Chart1", eChartType.Line);
            var chart2 = ws.Drawings.AddChart("Chart2", eChartType.Line);
            var shape1 = ws.Drawings.AddShape("Shape1", eShapeStyle.ActionButtonBackPrevious);
            var pic1 = ws.Drawings.AddPicture("Pic1", Resources.Test1);
            ws.Drawings.Remove(2);
            ws.Drawings.Remove(chart2);
            ws.Drawings.Remove("Pic1");

            ws = _pck.Workbook.Worksheets.Add("DeleteDrawing2");
            chart1 = ws.Drawings.AddChart("Chart1", eChartType.Line);
            chart2 = ws.Drawings.AddChart("Chart2", eChartType.Line);
            shape1 = ws.Drawings.AddShape("Shape1", eShapeStyle.ActionButtonBackPrevious);
            pic1 = ws.Drawings.AddPicture("Pic1", Resources.Test1);

            ws.Drawings.Remove("chart1");

            ws = _pck.Workbook.Worksheets.Add("ClearDrawing2");
            chart1 = ws.Drawings.AddChart("Chart1", eChartType.Line);
            chart2 = ws.Drawings.AddChart("Chart2", eChartType.Line);
            shape1 = ws.Drawings.AddShape("Shape1", eShapeStyle.ActionButtonBackPrevious);
            pic1 = ws.Drawings.AddPicture("Pic1", Resources.Test1);
            ws.Drawings.Clear();
        }
        //[TestMethod]
        //[Ignore]
        public void ReadDocument()
        {
            var fi=new FileInfo(_worksheetPath + "drawing.xlsx");
            if (!fi.Exists)
            {
                Assert.Inconclusive("Drawing.xlsx is not created. Skippng");
            }
            var pck = new ExcelPackage(fi, true);

            foreach(var ws in pck.Workbook.Worksheets)
            {
                foreach(ExcelDrawing d in pck.Workbook.Worksheets[1].Drawings)
                {
                    if (d is ExcelChart)
                    {
                        TestContext.WriteLine(((ExcelChart)d).ChartType.ToString());
                    }
                }
            }
            pck.Dispose();
        }
        [TestMethod]
        [Ignore]
        public void ReadMultiChartSeries()
        {
            ExcelPackage pck = new ExcelPackage(new FileInfo("c:\\temp\\chartseries.xlsx"), true);

            var ws = pck.Workbook.Worksheets[1];
            ExcelChart c = ws.Drawings[0] as ExcelChart;


            var p = c.PlotArea;
            p.ChartTypes[1].Series[0].Series = "S7:S15";

            var c2=ws.Drawings.AddChart("NewChart", eChartType.ColumnClustered);
            var serie1 = c2.Series.Add("R7:R15", "Q7:Q15");
            c2.SetSize(800, 800);
            serie1.Header = "Column Clustered";

            var subChart = c2.PlotArea.ChartTypes.Add(eChartType.LineMarkers);
            var serie2 = subChart.Series.Add("S7:S15", "Q7:Q15");
            serie2.Header = "Line";

            //var subChart2 = c2.PlotArea.ChartTypes.Add(eChartType.DoughnutExploded);
            //var serie3 = subChart2.Series.Add("S7:S15", "Q7:Q15");
            //serie3.Header = "Doughnut";

            var subChart3 = c2.PlotArea.ChartTypes.Add(eChartType.Area);
            var serie4 = subChart3.Series.Add("R7:R15", "Q7:Q15");
            serie4.Header = "Area";
            subChart3.UseSecondaryAxis = true;

            var serie5 = subChart.Series.Add("R7:R15","Q7:Q15");
            serie5.Header = "Line 2";

            pck.SaveAs(new FileInfo("c:\\temp\\chartseriesnew.xlsx"));
        }
        [Ignore]
        [TestMethod]
        public void ChartWorksheet()
        {
            _pck = new ExcelPackage();
            var wsChart = _pck.Workbook.Worksheets.AddChart("chart", eChartType.Bubble3DEffect);
            var ws = _pck.Workbook.Worksheets.Add("data");
            AddTestSerie(ws, wsChart.Chart);
            wsChart.Chart.Style = eChartStyle.Style23;
            wsChart.Chart.Title.Text = "Chart worksheet";
            wsChart.Chart.Series[0].Header = "Serie";
            _pck.SaveAs(new FileInfo(@"c:\temp\chart.xlsx"));
        }
        [Ignore]
        [TestMethod]
        public void ReadChartWorksheet()
        {
            _pck = new ExcelPackage(new FileInfo(@"c:\temp\chart.xlsx"));
            var chart = ((ExcelChartsheet)_pck.Workbook.Worksheets[1]).Chart;

            _pck.SaveAs(new FileInfo(@"c:\temp\chart.xlsx"));

        }
        [Ignore]
        [TestMethod]
        public void ReadWriteSmoothChart()
        {
            _pck = new ExcelPackage(new FileInfo(@"c:\temp\bug\Xds_2014_TEST.xlsx"));
            var chart = _pck.Workbook.Worksheets[1].Drawings[0] as ExcelChart;
            _pck.Workbook.Worksheets[1].Cells["B2"].Value = 33;
            _pck.SaveAs(new FileInfo(@"c:\temp\chart.xlsx"));

        }
        [TestMethod]
        public void TestHeaderaddress()
        {
            _pck = new ExcelPackage();
            var ws = _pck.Workbook.Worksheets.Add("Draw");
            var chart = ws.Drawings.AddChart("NewChart1",eChartType.Area) as ExcelChart;
            var ser1 = chart.Series.Add("A1:A2", "B1:B2");
            ser1.HeaderAddress = new ExcelAddress("A1:A2");
            ser1.HeaderAddress = new ExcelAddress("A1:B1");
            ser1.HeaderAddress = new ExcelAddress("A1");
            _pck.Dispose();
            _pck = null;
        }
        [Ignore]
        [TestMethod]
        public void AllDrawingsInsideMarkupCompatibility()
        {
            string workbooksDir = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"..\..\workbooks");
            // This is codeplex issue 15028: Making an unrelated change to an Excel file that contains drawings that ALL exist
            // inside MarkupCompatibility/Choice nodes causes the drawings.xml file to be incorrectly garbage collected
            // when an unrelated change is made.
            string path = Path.Combine(workbooksDir, "AllDrawingsInsideMarkupCompatibility.xlsm");

            // Load example document.
            _pck = new ExcelPackage(new FileInfo(path));
            // Verify the drawing part exists:
            Uri partUri = new Uri("/xl/drawings/drawing1.xml", UriKind.Relative);
            Assert.IsTrue(_pck.Package.PartExists(partUri));

            // The Excel Drawings NamespaceManager from ExcelDrawing.CreateNSM:
            NameTable nt = new NameTable();
            var xmlNsm = new XmlNamespaceManager(nt);
            xmlNsm.AddNamespace("a", ExcelPackage.schemaDrawings);
            xmlNsm.AddNamespace("xdr", ExcelPackage.schemaSheetDrawings);
            xmlNsm.AddNamespace("c", ExcelPackage.schemaChart);
            xmlNsm.AddNamespace("r", ExcelPackage.schemaRelationships);
            xmlNsm.AddNamespace("mc", ExcelPackage.schemaMarkupCompatibility);

            XmlDocument drawingsXml = new XmlDocument();
            drawingsXml.PreserveWhitespace = false;
            XmlHelper.LoadXmlSafe(drawingsXml, _pck.Package.GetPart(partUri).GetStream());

            // Verify that there are the correct # of drawings:
            Assert.AreEqual(drawingsXml.SelectNodes("//*[self::xdr:twoCellAnchor or self::xdr:oneCellAnchor or self::xdr:absoluteAnchor]", xmlNsm).Count, 5);

            // Make unrelated change. (in this case a useless additional worksheet)
            _pck.Workbook.Worksheets.Add("NewWorksheet");

            // Save it out.
            string savedPath = Path.Combine(workbooksDir, "AllDrawingsInsideMarkupCompatibility2.xlsm");
            _pck.SaveAs(new FileInfo(savedPath));
            _pck.Dispose();
            
            // Reload the new saved file.
            _pck = new ExcelPackage(new FileInfo(savedPath));

            // Verify the drawing part still exists.
            Assert.IsTrue(_pck.Package.PartExists(new Uri("/xl/drawings/drawing1.xml", UriKind.Relative)));

            drawingsXml = new XmlDocument();
            drawingsXml.PreserveWhitespace = false;
            XmlHelper.LoadXmlSafe(drawingsXml, _pck.Package.GetPart(partUri).GetStream());

            // Verify that there are the correct # of drawings:
            Assert.AreEqual(drawingsXml.SelectNodes("//*[self::xdr:twoCellAnchor or self::xdr:oneCellAnchor or self::xdr:absoluteAnchor]", xmlNsm).Count, 5);
            // Verify that the new worksheet exists:
            Assert.IsNotNull(_pck.Workbook.Worksheets["NewWorksheet"]);
            // Cleanup:
            File.Delete(savedPath);
        }
    }
}