EPPlusTest/WorkSheet.cs

using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using OfficeOpenXml;
using System.IO;
using OfficeOpenXml.Drawing;
using System.Drawing;
using OfficeOpenXml.Drawing.Chart;
using OfficeOpenXml.Drawing.Vml;
using OfficeOpenXml.Style;
using System.Data;
using OfficeOpenXml.Table.PivotTable;
using System.Reflection;

namespace EPPlusTest
{
    [TestClass]
    public class WorkSheetTest : TestBase
    {
        [TestMethod]
        public void RunWorksheetTests()
        {
            InsertDeleteTestRows();
            InsertDeleteTestColumns();
            LoadData();
            StyleFill();
            Performance();
            RichTextCells();
            TestComments();
            Hyperlink();
            PictureURL();
            CopyOverwrite();
            HideTest();
            VeryHideTest();
            PrinterSettings();
            Address();
            Merge();
            Encoding();
            LoadText();
            LoadDataReader();
            LoadDataTable();
            LoadFromCollectionTest();
            LoadFromEmptyCollectionTest();
            LoadArray();
            WorksheetCopy();
            DefaultColWidth();
            CopyTable();
            AutoFitColumns();
            CopyRange();
            CopyMergedRange();
            ValueError();
            FormulaOverwrite();
            FormulaError();
            StyleNameTest();
            NamedStyles();
            TableTest();
            DefinedName();
            CreatePivotTable();
            AddChartSheet();
            SetHeaderFooterImage();

            SaveWorksheet("Worksheet.xlsx");

            ReadWorkSheet();
            ReadStreamSaveAsStream();
        }

        private void AddChartSheet()
        {
            var chart = _pck.Workbook.Worksheets.AddChart("ChartSheet", eChartType.ColumnClustered);
            foreach (var _n in _pck.Workbook.Names)
            {

            }
            //Iterate all collection and make sure no exception is thrown.
            foreach (var worksheet in _pck.Workbook.Worksheets)
            {
                if (!(worksheet is ExcelChartsheet))
                {
                    foreach (var d in worksheet.Drawings)
                    {

                    }
                    foreach (var d in worksheet.Tables)
                    {

                    }
                    foreach (var d in worksheet.PivotTables)
                    {

                    }
                    foreach (var d in worksheet.Names)
                    {

                    }
                    foreach (var d in worksheet.Comments)
                    {

                    }
                    foreach (var d in worksheet.ConditionalFormatting)
                    {

                    }
                }
            }
        }
        //[Ignore]
        //[TestMethod]
        public void ReadWorkSheet()
        {
            FileStream instream = new FileStream(_worksheetPath + @"Worksheet.xlsx", FileMode.Open, FileAccess.ReadWrite);
            using (ExcelPackage pck = new ExcelPackage(instream))
            {
                var ws = pck.Workbook.Worksheets["Perf"];
                Assert.AreEqual(ws.Cells["H6"].Formula, "B5+B6");

                ws = pck.Workbook.Worksheets["Comment"];
                var comment = ws.Cells["B2"].Comment;

                Assert.AreNotEqual(comment, null);
                Assert.AreEqual(comment.Author, "Jan Källman");
                ws = pck.Workbook.Worksheets["Hidden"];
                Assert.AreEqual<eWorkSheetHidden>(ws.Hidden, eWorkSheetHidden.Hidden);

                ws = pck.Workbook.Worksheets["VeryHidden"];
                Assert.AreEqual<eWorkSheetHidden>(ws.Hidden, eWorkSheetHidden.VeryHidden);

                ws = pck.Workbook.Worksheets["RichText"];
                Assert.AreEqual("Room 02 & 03", ws.Cells["G1"].RichText.Text);

                ws = pck.Workbook.Worksheets["HeaderImage"];
                //Assert.AreEqual(ws.HeaderFooter.Pictures.Count, 3);

                ws = pck.Workbook.Worksheets["newsheet"];
                Assert.AreEqual(ws.Cells["F2"].Style.Font.UnderLine, true);
                Assert.AreEqual(ws.Cells["F2"].Style.Font.UnderLineType, ExcelUnderLineType.Double);
                Assert.AreEqual(ws.Cells["F3"].Style.Font.UnderLineType, ExcelUnderLineType.SingleAccounting);
                Assert.AreEqual(ws.Cells["F5"].Style.Font.UnderLineType, ExcelUnderLineType.None);
                Assert.AreEqual(ws.Cells["F5"].Style.Font.UnderLine, false);
            }
            instream.Close();
        }
        [Ignore]
        [TestMethod]
        public void ReadStreamWithTemplateWorkSheet()
        {
            FileStream instream = new FileStream(_worksheetPath + @"\Worksheet.xlsx", FileMode.Open, FileAccess.Read);
            MemoryStream stream = new MemoryStream();
            using (ExcelPackage pck = new ExcelPackage(stream, instream))
            {
                var ws = pck.Workbook.Worksheets["Perf"];
                Assert.AreEqual(ws.Cells["H6"].Formula, "B5+B6");

                ws = pck.Workbook.Worksheets["newsheet"];
                Assert.AreEqual(ws.GetValue<DateTime>(20, 21), new DateTime(2010, 1, 1));

                ws = pck.Workbook.Worksheets["Loaded DataTable"];
                Assert.AreEqual(ws.GetValue<string>(2, 1), "Row1");
                Assert.AreEqual(ws.GetValue<int>(2, 2), 1);
                Assert.AreEqual(ws.GetValue<bool>(2, 3), true);
                Assert.AreEqual(ws.GetValue<double>(2, 4), 1.5);

                ws = pck.Workbook.Worksheets["RichText"];

                var r1 = ws.Cells["A1"].RichText[0];
                Assert.AreEqual(r1.Text, "Test");
                Assert.AreEqual(r1.Bold, true);

                ws = pck.Workbook.Worksheets["Pic URL"];
                Assert.AreEqual(((ExcelPicture)ws.Drawings["Pic URI"]).Hyperlink, "http://epplus.codeplex.com");

                Assert.AreEqual(pck.Workbook.Worksheets["Address"].GetValue<string>(40, 1), "\b\t");

                pck.SaveAs(new FileInfo(@"Test\Worksheet2.xlsx"));
            }
            instream.Close();
        }
        //[Ignore]
        //[TestMethod]
        public void ReadStreamSaveAsStream()
        {
            if (!File.Exists(_worksheetPath + @"Worksheet.xlsx"))
            {
                Assert.Inconclusive("Worksheet.xlsx does not exists");
            }
            FileStream instream = new FileStream(_worksheetPath + @"Worksheet.xlsx", FileMode.Open, FileAccess.ReadWrite);
            MemoryStream stream = new MemoryStream();
            using (ExcelPackage pck = new ExcelPackage(instream))
            {
                var ws = pck.Workbook.Worksheets["Names"];
                Assert.AreEqual(ws.Names["FullCol"].Start.Row, 1);
                Assert.AreEqual(ws.Names["FullCol"].End.Row, ExcelPackage.MaxRows);
                pck.SaveAs(stream);
            }
            instream.Close();
        }
        //
        // You can use the following additional attributes as you write your tests:
        //
        // Use ClassInitialize to run code before running the first test in the class
        // Use ClassCleanup to run code after all tests in a class have run
        //[Ignore]
        //[TestMethod]
        public void LoadData()
        {
            ExcelWorksheet ws = _pck.Workbook.Worksheets.Add("newsheet");
            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["v19:v24"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
            ws.Cells["v19:v24"].Style.Numberformat.Format = @"$#,##0.00_);($#,##0.00)";

            ws.Cells["X19"].Value = 210;
            ws.Cells["X20"].Value = 212;
            ws.Cells["X21"].Value = 221;
            ws.Cells["X22"].Value = 123;
            ws.Cells["X23"].Value = 135;
            ws.Cells["X24"].Value = 134;

            // add autofilter
            ws.Cells["U19:X24"].AutoFilter = true;
            ExcelPicture pic = ws.Drawings.AddPicture("Pic1", Properties.Resources.Test1);
            pic.SetPosition(150, 140);

            ws.Cells["A30"].Value = "Text orientation 45";
            ws.Cells["A30"].Style.TextRotation = 45;
            ws.Cells["B30"].Value = "Text orientation 90";
            ws.Cells["B30"].Style.TextRotation = 90;
            ws.Cells["C30"].Value = "Text orientation 180";
            ws.Cells["C30"].Style.TextRotation = 180;
            ws.Cells["D30"].Value = "Text orientation 38";
            ws.Cells["D30"].Style.TextRotation = 38;
            ws.Cells["D30"].Style.Font.Bold = true;
            ws.Cells["D30"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Right;

            ws.Workbook.Names.Add("TestName", ws.Cells["B30:E30"]);
            ws.Workbook.Names["TestName"].Style.Font.Color.SetColor(Color.Red);


            ws.Workbook.Names["TestName"].Offset(1, 0).Value = "Offset test 1";
            ws.Workbook.Names["TestName"].Offset(2, -1, 2, 2).Value = "Offset test 2";

            //Test vertical align
            ws.Cells["E19"].Value = "Subscript";
            ws.Cells["E19"].Style.Font.VerticalAlign = ExcelVerticalAlignmentFont.Subscript;
            ws.Cells["E20"].Value = "Subscript";
            ws.Cells["E20"].Style.Font.VerticalAlign = ExcelVerticalAlignmentFont.Superscript;
            ws.Cells["E21"].Value = "Superscript";
            ws.Cells["E21"].Style.Font.VerticalAlign = ExcelVerticalAlignmentFont.Superscript;
            ws.Cells["E21"].Style.Font.VerticalAlign = ExcelVerticalAlignmentFont.None;


            ws.Cells["E22"].Value = "Indent 2";
            ws.Cells["E22"].Style.Indent = 2;
            ws.Cells["E23"].Value = "Shrink to fit";
            ws.Cells["E23"].Style.ShrinkToFit = true;

            ws.Cells["e24"].Value = "ReadingOrder LeftToRight";
            ws.Cells["e24"].Style.ReadingOrder = ExcelReadingOrder.LeftToRight;
            ws.Cells["e25"].Value = "ReadingOrder RightToLeft";
            ws.Cells["e25"].Style.ReadingOrder = ExcelReadingOrder.RightToLeft;
            ws.Cells["e26"].Value = "ReadingOrder Context";
            ws.Cells["e26"].Style.ReadingOrder = ExcelReadingOrder.ContextDependent;
            ws.Cells["e27"].Value = "Default Readingorder";

            //Underline

            ws.Cells["F1:F7"].Value = "Underlined";
            ws.Cells["F1"].Style.Font.UnderLineType = ExcelUnderLineType.Single;
            ws.Cells["F2"].Style.Font.UnderLineType = ExcelUnderLineType.Double;
            ws.Cells["F3"].Style.Font.UnderLineType = ExcelUnderLineType.SingleAccounting;
            ws.Cells["F4"].Style.Font.UnderLineType = ExcelUnderLineType.DoubleAccounting;
            ws.Cells["F5"].Style.Font.UnderLineType = ExcelUnderLineType.None;
            ws.Cells["F6:F7"].Style.Font.UnderLine = true;
            ws.Cells["F7"].Style.Font.UnderLine = false;

            ws.Cells["E24"].Value = 0;
            Assert.AreEqual(ws.Cells["E24"].Text, "0");
            ws.Cells["F7"].Style.Font.UnderLine = false;
            ws.Names.Add("SheetName", ws.Cells["A1:A2"]);
            ws.View.FreezePanes(3, 5);

            foreach (ExcelRangeBase cell in ws.Cells["A1"])
            {
                Assert.Fail("A1 is not set");
            }

            foreach (ExcelRangeBase cell in ws.Cells[ws.Dimension.Address])
            {
                System.Diagnostics.Debug.WriteLine(cell.Address);
            }

            ////Linq test
            var res = from c in ws.Cells[ws.Dimension.Address] where c.Value != null && c.Value.ToString() == "Offset test 1" select c;

            foreach (ExcelRangeBase cell in res)
            {
                System.Diagnostics.Debug.WriteLine(cell.Address);
            }

            _pck.Workbook.Properties.Author = "Jan Källman";
            _pck.Workbook.Properties.Category = "Category";
            _pck.Workbook.Properties.Comments = "Comments";
            _pck.Workbook.Properties.Company = "Adventure works";
            _pck.Workbook.Properties.Keywords = "Keywords";
            _pck.Workbook.Properties.Title = "Title";
            _pck.Workbook.Properties.Subject = "Subject";
            _pck.Workbook.Properties.Status = "Status";
            _pck.Workbook.Properties.HyperlinkBase = new Uri("http://serversideexcel.com", UriKind.Absolute);
            _pck.Workbook.Properties.Manager = "Manager";

            _pck.Workbook.Properties.SetCustomPropertyValue("DateTest", new DateTime(2008, 12, 31));
            TestContext.WriteLine(_pck.Workbook.Properties.GetCustomPropertyValue("DateTest").ToString());
            _pck.Workbook.Properties.SetCustomPropertyValue("Author", "Jan Källman");
            _pck.Workbook.Properties.SetCustomPropertyValue("Count", 1);
            _pck.Workbook.Properties.SetCustomPropertyValue("IsTested", false);
            _pck.Workbook.Properties.SetCustomPropertyValue("LargeNo", 123456789123);
            _pck.Workbook.Properties.SetCustomPropertyValue("Author", 3);
        }
        const int PERF_ROWS = 5000;
        //[Ignore]
        //[TestMethod]
        public void Performance()
        {
            ExcelWorksheet ws = _pck.Workbook.Worksheets.Add("Perf");
            TestContext.WriteLine("StartTime {0}", DateTime.Now);

            Random r = new Random();
            for (int i = 1; i <= PERF_ROWS; i++)
            {
                ws.Cells[i, 1].Value = string.Format("Row {0}\n.Test new row\"' öäåü", i);
                ws.Cells[i, 2].Value = i;
                ws.Cells[i, 2].Style.WrapText = true;
                ws.Cells[i, 3].Value = DateTime.Now;
                ws.Cells[i, 4].Value = r.NextDouble() * 100000;
            }
            ws.Cells[1, 2, PERF_ROWS, 2].Style.Numberformat.Format = "#,##0";
            ws.Cells[1, 3, PERF_ROWS, 3].Style.Numberformat.Format = "yyyy-MM-dd HH:mm:ss";
            ws.Cells[1, 4, PERF_ROWS, 4].Style.Numberformat.Format = "#,##0.00";
            ws.Cells[PERF_ROWS + 1, 2].Formula = "SUM(B1:B" + PERF_ROWS.ToString() + ")";
            ws.Column(1).Width = 12;
            ws.Column(2).Width = 8;
            ws.Column(3).Width = 20;
            ws.Column(4).Width = 14;

            ws.Cells["A1:C1"].Merge = true;
            ws.Cells["A2:A5"].Merge = true;
            ws.DeleteRow(1, 1);
            ws.InsertRow(1, 1);
            ws.InsertRow(3, 1);

            ws.DeleteRow(1000, 3, true);
            ws.DeleteRow(2000, 1, true);

            ws.InsertRow(2001, 4);

            ws.InsertRow(2010, 1, 2010);

            ws.InsertRow(20000, 2);

            ws.DeleteRow(20005, 4, false);

            //Single formula
            ws.Cells["H3"].Formula = "B2+B3";
            ws.DeleteRow(2, 1, true);

            //Shared formula
            ws.Cells["H5:H30"].Formula = "B4+B5";
            ws.Cells["H5:H30"].Style.Numberformat.Format = "_(\"$\"* # ##0.00_);_(\"$\"* (# ##0.00);_(\"$\"* \"-\"??_);_(@_)";
            ws.InsertRow(7, 3);
            ws.InsertRow(2, 1);
            ws.DeleteRow(30, 3, true);

            ws.DeleteRow(15, 2, true);
            ws.Cells["a1:B100"].Style.Locked = false;
            ws.Cells["a1:B12"].Style.Hidden = true;
            TestContext.WriteLine("EndTime {0}", DateTime.Now);
        }
        //[Ignore]
        //[TestMethod]
        public void InsertDeleteTestRows()
        {
            ExcelWorksheet ws = _pck.Workbook.Worksheets.Add("InsertDelete");
            //ws.Cells.Value = 0;
            ws.Cells["A1:C5"].Value = 1;
            Assert.AreEqual(((object[,])ws.Cells["A1:C5"].Value)[1, 1], 1);
            ws.Cells["A1:B3"].Merge = true;
            ws.Cells["D3"].Formula = "A2+C5";
            ws.InsertRow(2, 1);

            ws.Cells["A10:C15"].Value = 1;
            ws.Cells["A11:B13"].Merge = true;
            ws.DeleteRow(12, 1, true);

            ws.Cells["a1:B100"].Style.Locked = false;
            ws.Cells["a1:B12"].Style.Hidden = true;
            ws.Protection.IsProtected = true;
            ws.Protection.SetPassword("Password");


            var range = ws.Cells["B2:D100"];

            ws.PrinterSettings.PrintArea = null;
            ws.PrinterSettings.PrintArea = ws.Cells["B2:D99"];
            ws.PrinterSettings.PrintArea = null;
            ws.Row(15).PageBreak = true;
            ws.Column(3).PageBreak = true;
            ws.View.ShowHeaders = false;
            ws.View.PageBreakView = true;

            ws.Row(200).Height = 50;
            ws.Workbook.CalcMode = ExcelCalcMode.Automatic;

            Assert.AreEqual(range.Start.Column, 2);
            Assert.AreEqual(range.Start.Row, 2);
            Assert.AreEqual(range.Start.Address, "B2");

            Assert.AreEqual(range.End.Column, 4);
            Assert.AreEqual(range.End.Row, 100);
            Assert.AreEqual(range.End.Address, "D100");

            ExcelAddress addr = new ExcelAddress("B1:D3");

            Assert.AreEqual(addr.Start.Column, 2);
            Assert.AreEqual(addr.Start.Row, 1);
            Assert.AreEqual(addr.End.Column, 4);
            Assert.AreEqual(addr.End.Row, 3);
        }
        //[Ignore]
        //[TestMethod]
        public void InsertDeleteTestColumns()
        {
            ExcelWorksheet ws = _pck.Workbook.Worksheets.Add("InsertDeleteColumns");
            //ws.Cells.Value = 0;
            ws.Cells["A1:C5"].Value = 1;
            Assert.AreEqual(((object[,])ws.Cells["A1:C5"].Value)[1, 1], 1);
            ws.Cells["A1:B3"].Merge = true;
            ws.Cells["D3"].Formula = "A2+C5";
            ws.InsertColumn(1, 1);

            ws.Cells["K10:M15"].Value = 1;
            ws.Cells["K11:L13"].Merge = true;
            ws.DeleteColumn(12, 1);

            ws.Cells["X1:Y100"].Style.Locked = false;
            ws.Cells["C1:Y12"].Style.Hidden = true;
            ws.Protection.IsProtected = true;
            ws.Protection.SetPassword("Password");


            var range = ws.Cells["X2:Z100"];

            ws.PrinterSettings.PrintArea = null;
            ws.PrinterSettings.PrintArea = ws.Cells["X2:Z99"];
            ws.PrinterSettings.PrintArea = null;
            ws.Row(15).PageBreak = true;
            ws.Column(3).PageBreak = true;
            ws.View.ShowHeaders = false;
            ws.View.PageBreakView = true;

            ws.Row(200).Height = 50;
            ws.Workbook.CalcMode = ExcelCalcMode.Automatic;

            //Assert.AreEqual(range.Start.Column, 2);
            //Assert.AreEqual(range.Start.Row, 2);
            //Assert.AreEqual(range.Start.Address, "B2");

            //Assert.AreEqual(range.End.Column, 4);
            //Assert.AreEqual(range.End.Row, 100);
            //Assert.AreEqual(range.End.Address, "D100");

            //ExcelAddress addr = new ExcelAddress("B1:D3");

            //Assert.AreEqual(addr.Start.Column, 2);
            //Assert.AreEqual(addr.Start.Row, 1);
            //Assert.AreEqual(addr.End.Column, 4);
            //Assert.AreEqual(addr.End.Row, 3);
        }
        //[Ignore]
        //[TestMethod]
        public void RichTextCells()
        {
            ExcelWorksheet ws = _pck.Workbook.Worksheets.Add("RichText");
            var rs = ws.Cells["A1"].RichText;

            var r1 = rs.Add("Test");
            r1.Bold = true;
            r1.Color = Color.Pink;

            var r2 = rs.Add(" of");
            r2.Size = 14;
            r2.Italic = true;

            var r3 = rs.Add(" rich");
            r3.FontName = "Arial";
            r3.Size = 18;
            r3.Italic = true;

            var r4 = rs.Add("text.");
            r4.Size = 8.25f;
            r4.Italic = true;
            r4.UnderLine = true;

            rs = ws.Cells["A3:A4"].RichText;

            var r5 = rs.Add("Double");
            r5.Color = Color.PeachPuff;
            r5.FontName = "times new roman";
            r5.Size = 16;

            var r6 = rs.Add(" cells");
            r6.Color = Color.Red;
            r6.UnderLine = true;


            rs = ws.Cells["C8"].RichText;
            r1 = rs.Add("Blue ");
            r1.Color = Color.Blue;

            r2 = rs.Add("Red");
            r2.Color = Color.Red;

            ws.Cells["G1"].RichText.Add("Room 02 & 03");
            ws.Cells["G2"].RichText.Text = "Room 02 & 03";

            ws = ws = _pck.Workbook.Worksheets.Add("RichText2");
            ws.Cells["A1"].RichText.Text = "Room 02 & 03";
            ws.TabColor = Color.PowderBlue;

            r1 = ws.Cells["G3"].RichText.Add("Test");
            r1.Bold = true;
            ws.Cells["G3"].RichText.Add(" a new t");
            ws.Cells["G3"].RichText[1].Bold = false; ;
        }
        //[Ignore]
        //[TestMethod]
        public void TestComments()
        {
            var ws = _pck.Workbook.Worksheets.Add("Comment");
            var comment = ws.Comments.Add(ws.Cells["C3"], "Jan Källman\r\nAuthor\r\n", "JK");
            comment.RichText[0].Bold = true;
            comment.RichText[0].PreserveSpace = true;
            var rt = comment.RichText.Add("Test comment");
            comment.VerticalAlignment = eTextAlignVerticalVml.Center;
            comment = ws.Comments.Add(ws.Cells["A2"], "Jan Källman\r\nAuthor\r\n1", "JK");

            comment = ws.Comments.Add(ws.Cells["A1"], "Jan Källman\r\nAuthor\r\n2", "JK");
            comment.AlternativeText = "Test of AlternetiveText2";
            comment = ws.Comments.Add(ws.Cells["C2"], "Jan Källman\r\nAuthor\r\n3", "JK");
            comment = ws.Comments.Add(ws.Cells["C1"], "Jan Källman\r\nAuthor\r\n5", "JK");
            comment = ws.Comments.Add(ws.Cells["B1"], "Jan Källman\r\nAuthor\r\n7", "JK");

            ws.Comments.Remove(ws.Cells["A2"].Comment);
            //comment.HorizontalAlignment = eTextAlignHorizontalVml.Center;
            //comment.Visible = true;
            //comment.BackgroundColor = Color.Green;
            //comment.To.Row += 4;
            //comment.To.Column += 2;
            //comment.LineStyle = eLineStyleVml.LongDash;
            //comment.LineColor = Color.Red;
            //comment.LineWidth = (Single)2.5;
            //rt.Color = Color.Red;

            var rt2 = ws.Cells["B2"].AddComment("Range Added Comment test test test test test test test test test test testtesttesttesttesttesttesttesttesttesttest", "Jan Källman");
            ws.Cells["c3"].Comment.AutoFit = true;

        }
        //[Ignore]
        //[TestMethod]
        public void Address()
        {
            var ws = _pck.Workbook.Worksheets.Add("Address");
            ws.Cells["A1:A4,B5:B7"].Value = "AddressTest";
            ws.Cells["A1:A4,B5:B7"].Style.Font.Color.SetColor(Color.Red);
            ws.Cells["A2:A3,B4:B8"].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.LightUp;
            ws.Cells["A2:A3,B4:B8"].Style.Fill.BackgroundColor.SetColor(Color.LightGreen);
            ws.Cells["2:2"].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
            ws.Cells["2:2"].Style.Fill.BackgroundColor.SetColor(Color.LightGreen);
            ws.Cells["B:B"].Style.Font.Name = "Times New Roman";

            ws.Cells["C4:G4,H8:H30,B15"].FormulaR1C1 = "RC[-1]+R1C[-1]";
            ws.Cells["C4:G4,H8:H30,B15"].Style.Numberformat.Format = "#,##0.000";
            ws.Cells["G1,G3"].Hyperlink = new ExcelHyperLink("Comment!$A$1", "Comment");
            ws.Cells["G1,G3"].Style.Font.Color.SetColor(Color.Blue);
            ws.Cells["G1,G3"].Style.Font.UnderLine = true;

            ws.Cells["A1:G5"].Copy(ws.Cells["A50"]);

            var ws2 = _pck.Workbook.Worksheets.Add("Copy Cells");
            ws.Cells["1:4"].Copy(ws2.Cells["1:1"]);

            ws.Cells["H1:J5"].Merge = true;
            ws.Cells["2:3"].Copy(ws.Cells["50:51"]);

            ws.Cells["A40"].Value = new string(new char[] { (char)8, (char)9 });

            ExcelRange styleRng = ws.Cells["A1"];
            ExcelStyle tempStyle = styleRng.Style;
            var namedStyle = _pck.Workbook.Styles.CreateNamedStyle("HyperLink", tempStyle);
            namedStyle.Style.Font.UnderLineType = ExcelUnderLineType.Single;
            namedStyle.Style.Font.Color.SetColor(Color.Blue);
        }
        //[Ignore]
        //[TestMethod]
        public void Encoding()
        {
            var ws = _pck.Workbook.Worksheets.Add("Encoding");
            ws.Cells["A1"].Value = "_x0099_";
            ws.Cells["A2"].Value = " Test \b" + (char)1 + " end\"";
            ws.Cells["A3"].Value = "_x0097_ test_x001D_1234";
            ws.Cells["A4"].Value = "test" + (char)31;   //Bug issue 14689 //Fixed
        }
        //[Ignore]
        //[TestMethod]
        public void WorksheetCopy()
        {
            var ws = _pck.Workbook.Worksheets.Add("Copied Address", _pck.Workbook.Worksheets["Address"]);
            var wsCopy = _pck.Workbook.Worksheets.Add("Copied Comment", _pck.Workbook.Worksheets["Comment"]);

            ExcelPackage pck2 = new ExcelPackage();
            pck2.Workbook.Worksheets.Add("Copy From other pck", _pck.Workbook.Worksheets["Address"]);
            pck2.SaveAs(new FileInfo(_worksheetPath + "copy.xlsx"));
            pck2 = null;
            Assert.AreEqual(6, wsCopy.Comments.Count);
        }
        [Ignore]
        [TestMethod]
        public void TestDelete()
        {
            string file = _worksheetPath + "test.xlsx";

            if (File.Exists(file))
                File.Delete(file);

            Create(file);

            ExcelPackage pack = new ExcelPackage(new FileInfo(file));
            ExcelWorksheet w = pack.Workbook.Worksheets["delete"];
            w.DeleteRow(1, 2);

            pack.Save();
        }
        //[Ignore]
        //[TestMethod]
        public void LoadFromCollectionTest()
        {
            var ws = _pck.Workbook.Worksheets.Add("LoadFromCollection");
            List<TestDTO> list = new List<TestDTO>();
            list.Add(new TestDTO() { Id = 1, Name = "Item1", Boolean = false, Date = new DateTime(2011, 1, 1), dto = null, NameVar = "Field 1" });
            list.Add(new TestDTO() { Id = 2, Name = "Item2", Boolean = true, Date = new DateTime(2011, 1, 15), dto = new TestDTO(), NameVar = "Field 2" });
            list.Add(new TestDTO() { Id = 3, Name = "Item3", Boolean = false, Date = new DateTime(2011, 2, 1), dto = null, NameVar = "Field 3" });
            list.Add(new TestDTO() { Id = 4, Name = "Item4", Boolean = true, Date = new DateTime(2011, 4, 19), dto = list[1], NameVar = "Field 4" });
            list.Add(new TestDTO() { Id = 5, Name = "Item5", Boolean = false, Date = new DateTime(2011, 5, 8), dto = null, NameVar = "Field 5" });
            list.Add(new TestDTO() { Id = 6, Name = "Item6", Boolean = true, Date = new DateTime(2010, 3, 27), dto = null, NameVar = "Field 6" });
            list.Add(new TestDTO() { Id = 7, Name = "Item7", Boolean = false, Date = new DateTime(2009, 1, 5), dto = list[3], NameVar = "Field 7" });
            list.Add(new TestDTO() { Id = 8, Name = "Item8", Boolean = true, Date = new DateTime(2018, 12, 31), dto = null, NameVar = "Field 8" });
            list.Add(new TestDTO() { Id = 9, Name = "Item9", Boolean = false, Date = new DateTime(2010, 2, 1), dto = null, NameVar = "Field 9" });

            ws.Cells["A1"].LoadFromCollection(list, true);
            ws.Cells["A30"].LoadFromCollection(list, true, OfficeOpenXml.Table.TableStyles.Medium9, BindingFlags.Instance | BindingFlags.Instance, typeof(TestDTO).GetFields());

            ws.Cells["A45"].LoadFromCollection(list, true, OfficeOpenXml.Table.TableStyles.Light1, BindingFlags.Instance | BindingFlags.Instance, new MemberInfo[] { typeof(TestDTO).GetMethod("GetNameID"), typeof(TestDTO).GetField("NameVar") });
            ws.Cells["J1"].LoadFromCollection(from l in list where l.Boolean orderby l.Date select new { Name = l.Name, Id = l.Id, Date = l.Date, NameVariable = l.NameVar }, true, OfficeOpenXml.Table.TableStyles.Dark4);

            var ints = new int[] { 1, 3, 4, 76, 2, 5 };
            ws.Cells["A15"].Value = ints;
        }
        //[TestMethod]
        public void LoadFromEmptyCollectionTest()
        {
            if (_pck == null) _pck = new ExcelPackage();
            var ws = _pck.Workbook.Worksheets.Add("LoadFromEmpyCollection");
            List<TestDTO> listDTO = new List<TestDTO>(0);
            //List<int> list = new List<int>(0);

            ws.Cells["A1"].LoadFromCollection(listDTO, true);
            ws.Cells["A5"].LoadFromCollection(listDTO, true, OfficeOpenXml.Table.TableStyles.Medium9, BindingFlags.Instance | BindingFlags.Instance, typeof(TestDTO).GetFields());

            ws.Cells["A10"].LoadFromCollection(listDTO, true, OfficeOpenXml.Table.TableStyles.Light1, BindingFlags.Instance | BindingFlags.Instance, new MemberInfo[] { typeof(TestDTO).GetMethod("GetNameID"), typeof(TestDTO).GetField("NameVar") });
            ws.Cells["A15"].LoadFromCollection(from l in listDTO where l.Boolean orderby l.Date select new { Name = l.Name, Id = l.Id, Date = l.Date, NameVariable = l.NameVar }, true, OfficeOpenXml.Table.TableStyles.Dark4);

            ws.Cells["A20"].LoadFromCollection(listDTO, false);
        }
        [TestMethod]
        public void LoadFromOneCollectionTest()
        {
            if (_pck == null) _pck = new ExcelPackage();
            var ws = _pck.Workbook.Worksheets.Add("LoadFromEmpyCollection");
            List<TestDTO> listDTO = new List<TestDTO>(0) { new TestDTO() { Name = "Single" } };
            //List<int> list = new List<int>(0);

            var r = ws.Cells["A1"].LoadFromCollection(listDTO, true);
            Assert.AreEqual(2, r.Rows);
            var r2 = ws.Cells["A5"].LoadFromCollection(listDTO, false);
            Assert.AreEqual(1, r2.Rows);
        }
        static void Create(string file)
        {
            ExcelPackage pack = new ExcelPackage(new FileInfo(file));
            ExcelWorksheet w = pack.Workbook.Worksheets.Add("delete");
            w.Cells[1, 1].Value = "test";
            w.Cells[1, 2].Value = "test";
            w.Cells[2, 1].Value = "to delete";
            w.Cells[2, 2].Value = "to delete";
            w.Cells[3, 1].Value = "3Left";
            w.Cells[3, 2].Value = "3Left";
            w.Cells[4, 1].Formula = "B3+C3";
            w.Cells[4, 2].Value = "C3+D3";
            pack.Save();
        }
        [Ignore]
        [TestMethod]
        public void RowStyle()
        {
            FileInfo newFile = new FileInfo(_worksheetPath + @"sample8.xlsx");
            if (newFile.Exists)
            {
                newFile.Delete();  // ensures we create a new workbook
                //newFile = new FileInfo(dir + @"sample8.xlsx");
            }

            ExcelPackage package = new ExcelPackage();
            //Load the sheet with one string column, one date column and a few random numbers.
            var ws = package.Workbook.Worksheets.Add("First line test");

            ws.Cells[1, 1].Value = "1; 1";
            ws.Cells[2, 1].Value = "2; 1";
            ws.Cells[1, 2].Value = "1; 2";
            ws.Cells[2, 2].Value = "2; 2";

            ws.Row(1).Style.Font.Bold = true;
            ws.Column(1).Style.Font.Bold = true;
            package.SaveAs(newFile);

        }
        //[Ignore]
        //[TestMethod]
        public void HideTest()
        {
            var ws = _pck.Workbook.Worksheets.Add("Hidden");
            ws.Cells["A1"].Value = "This workbook is hidden";
            ws.Hidden = eWorkSheetHidden.Hidden;
        }
        //[Ignore]
        //[TestMethod]
        public void Hyperlink()
        {
            var ws = _pck.Workbook.Worksheets.Add("HyperLinks");
            var hl = new ExcelHyperLink("G1", "Till G1");
            hl.ToolTip = "Link to cell G1";
            ws.Cells["A1"].Hyperlink = hl;
            //ws.Cells["A2"].Hyperlink = new ExcelHyperLink("mailto:ecsomany@google:huszar", UriKind.Absolute); //Invalid URL will throw an Exception
        }
        //[Ignore]
        //[TestMethod]
        public void VeryHideTest()
        {
            var ws = _pck.Workbook.Worksheets.Add("VeryHidden");
            ws.Cells["a1"].Value = "This workbook is hidden";
            ws.Hidden = eWorkSheetHidden.VeryHidden;
        }
        //[Ignore]
        //[TestMethod]
        public void PrinterSettings()
        {
            var ws = _pck.Workbook.Worksheets.Add("Sod/Hydroseed");

            ws.Cells[1, 1].Value = "1; 1";
            ws.Cells[2, 1].Value = "2; 1";
            ws.Cells[1, 2].Value = "1; 2";
            ws.Cells[2, 2].Value = "2; 2";
            ws.Cells[1, 1, 1, 2].AutoFilter = true;
            ws.PrinterSettings.BlackAndWhite = true;
            ws.PrinterSettings.ShowGridLines = true;
            ws.PrinterSettings.ShowHeaders = true;
            ws.PrinterSettings.PaperSize = ePaperSize.A4;

            ws.PrinterSettings.RepeatRows = new ExcelAddress("1:1");
            ws.PrinterSettings.RepeatColumns = new ExcelAddress("A:A");

            ws.PrinterSettings.Draft = true;
            var r = ws.Cells["A26"];
            r.Value = "X";
            r.Worksheet.Row(26).PageBreak = true;
            ws.PrinterSettings.PrintArea = ws.Cells["A1:B2"];
            ws.PrinterSettings.HorizontalCentered = true;
            ws.PrinterSettings.VerticalCentered = true;

            ws.Select(new ExcelAddress("3:4,E5:F6"));

            ws = _pck.Workbook.Worksheets["RichText"];
            ws.PrinterSettings.RepeatColumns = ws.Cells["A:B"];
            ws.PrinterSettings.RepeatRows = ws.Cells["1:11"];
            ws.PrinterSettings.TopMargin = 1M;
            ws.PrinterSettings.LeftMargin = 1M;
            ws.PrinterSettings.BottomMargin = 1M;
            ws.PrinterSettings.RightMargin = 1M;
            ws.PrinterSettings.Orientation = eOrientation.Landscape;
            ws.PrinterSettings.PaperSize = ePaperSize.A4;
        }
        //[Ignore]
        //[TestMethod]
        public void StyleNameTest()
        {
            var ws = _pck.Workbook.Worksheets.Add("StyleNameTest");

            ws.Cells[1, 1].Value = "R1 C1";
            ws.Cells[1, 2].Value = "R1 C2";
            ws.Cells[1, 3].Value = "R1 C3";
            ws.Cells[2, 1].Value = "R2 C1";
            ws.Cells[2, 2].Value = "R2 C2";
            ws.Cells[2, 3].Value = "R2 C3";
            ws.Cells[3, 1].Value = double.PositiveInfinity;
            ws.Cells[3, 2].Value = double.NegativeInfinity;
            ws.Cells[4, 1].CreateArrayFormula("A1+B1");
            var ns = _pck.Workbook.Styles.CreateNamedStyle("TestStyle");
            ns.Style.Font.Bold = true;

            ws.Cells.Style.Locked = true;
            ws.Cells["A1:C1"].StyleName = "TestStyle";
            ws.DefaultRowHeight = 35;
            ws.Cells["A1:C4"].Style.Locked = false;
            ws.Protection.IsProtected = true;
        }
        //[Ignore]
        //[TestMethod]
        public void ValueError()
        {
            var ws = _pck.Workbook.Worksheets.Add("ValueError");

            ws.Cells[1, 1].Value = "Domestic Violence&#xB; and the Professional";
            var rt = ws.Cells[1, 2].RichText.Add("Domestic Violence&#xB; and the Professional 2");
            TestContext.WriteLine(rt.Bold.ToString());
            rt.Bold = true;
            TestContext.WriteLine(rt.Bold.ToString());
        }
        //[Ignore]
        //[TestMethod]
        public void FormulaError()
        {
            var ws = _pck.Workbook.Worksheets.Add("FormulaError");

            ws.Cells["D5"].Formula = "COUNTIF(A1:A100,\"Miss\")";
            ws.Cells["A1:K3"].Formula = "A3+A4";
            ws.Cells["A4"].FormulaR1C1 = "+ROUNDUP(RC[1]/10,0)*10";

            ws = _pck.Workbook.Worksheets.Add("Sheet-RC1");
            ws.Cells["A4"].FormulaR1C1 = "+ROUNDUP('Sheet-RC1'!RC[1]/10,0)*10";

            //ws.Cells["B2:I2"].Formula = "";   //Error
        }
        [TestMethod, Ignore]
        public void FormulaArray()
        {
            _pck = new ExcelPackage();
            var ws = _pck.Workbook.Worksheets.Add("FormulaError");

            ws.Cells["E2:E5"].CreateArrayFormula("FREQUENCY(B2:B18,C2:C5)");
            _pck.SaveAs(new FileInfo("c:\\temp\\arrayformula.xlsx"));
        }
        //[Ignore]
        //[TestMethod]
        public void PictureURL()
        {
            var ws = _pck.Workbook.Worksheets.Add("Pic URL");

            ExcelHyperLink hl = new ExcelHyperLink("http://epplus.codeplex.com");
            hl.ToolTip = "Screen Tip";

            ws.Drawings.AddPicture("Pic URI", Properties.Resources.Test1, hl);
        }


        [TestMethod]
        public void PivotTableTest()
        {
            _pck = new ExcelPackage();
            var ws = _pck.Workbook.Worksheets.Add("PivotTable");
            ws.Cells["A1"].LoadFromArrays(new object[][] { new[] { "A", "B", "C", "D" } });
            ws.Cells["A2"].LoadFromArrays(new object[][]
            {
                new object [] { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 },
                new object [] { 9, 8, 7 ,6, 5, 4, 3, 2, 1, 0 },
                new object [] { 1, 1, 2, 3, 5, 8, 13, 21, 34, 55}
            });
            var table = ws.Tables.Add(ws.Cells["A1:D4"], "PivotData");
            ws.PivotTables.Add(ws.Cells["G1"], ws.Cells["A1:D4"], "PivotTable");
            Assert.AreEqual("PivotStyleMedium9", ws.PivotTables["PivotTable"].StyleName);
        }


        [TestMethod]
        [ExpectedException(typeof(ArgumentException))]
        public void TestTableNameCanNotStartsWithNumber()
        {
            var ws = _pck.Workbook.Worksheets.Add("Table");
            var tbl = ws.Tables.Add(ws.Cells["A1"], "5TestTable");
        }

        [TestMethod]
        [ExpectedException(typeof(ArgumentException))]
        public void TestTableNameCanNotContainWhiteSpaces()
        {
            var ws = _pck.Workbook.Worksheets.Add("Table");
            var tbl = ws.Tables.Add(ws.Cells["A1"], "Test Table");
        }

        [TestMethod]
        public void TestTableNameCanStartsWithBackSlash()
        {
            var ws = _pck.Workbook.Worksheets.Add("Table");
            var tbl = ws.Tables.Add(ws.Cells["A1"], "\\TestTable");
        }

        [TestMethod]
        public void TestTableNameCanStartsWithUnderscore()
        {
            var ws = _pck.Workbook.Worksheets.Add("Table");
            var tbl = ws.Tables.Add(ws.Cells["A1"], "_TestTable");
        }

        //[Ignore]
        //[TestMethod]
        public void TableTest()
        {
            var ws = _pck.Workbook.Worksheets.Add("Table");
            ws.Cells["B1"].Value = 123;
            var tbl = ws.Tables.Add(ws.Cells["B1:P12"], "TestTable");
            tbl.TableStyle = OfficeOpenXml.Table.TableStyles.Custom;

            tbl.ShowFirstColumn = true;
            tbl.ShowTotal = true;
            tbl.ShowHeader = true;
            tbl.ShowLastColumn = true;
            tbl.ShowFilter = false;
            Assert.AreEqual(tbl.ShowFilter, false);
            ws.Cells["K2"].Value = 5;
            ws.Cells["J3"].Value = 4;

            tbl.Columns[8].TotalsRowFunction = OfficeOpenXml.Table.RowFunctions.Sum;
            tbl.Columns[9].TotalsRowFormula = string.Format("SUM([{0}])", tbl.Columns[9].Name);
            tbl.Columns[14].CalculatedColumnFormula = "TestTable[[#This Row],[123]]+TestTable[[#This Row],[Column2]]";
            ws.Cells["B2"].Value = 1;
            ws.Cells["B3"].Value = 2;
            ws.Cells["B4"].Value = 3;
            ws.Cells["B5"].Value = 4;
            ws.Cells["B6"].Value = 5;
            ws.Cells["B7"].Value = 6;
            ws.Cells["B8"].Value = 7;
            ws.Cells["B9"].Value = 8;
            ws.Cells["B10"].Value = 9;
            ws.Cells["B11"].Value = 10;
            ws.Cells["B12"].Value = 11;
            ws.Cells["C7"].Value = "Table test";
            ws.Cells["C8"].Style.Fill.PatternType = ExcelFillStyle.Solid;
            ws.Cells["C8"].Style.Fill.BackgroundColor.SetColor(Color.Red);

            tbl = ws.Tables.Add(ws.Cells["a12:a13"], "");

            tbl = ws.Tables.Add(ws.Cells["C16:Y35"], "");
            tbl.TableStyle = OfficeOpenXml.Table.TableStyles.Medium14;
            tbl.ShowFirstColumn = true;
            tbl.ShowLastColumn = true;
            tbl.ShowColumnStripes = true;
            Assert.AreEqual(tbl.ShowFilter, true);
            tbl.Columns[2].Name = "Test Column Name";

            ws.Cells["G50"].Value = "Timespan";
            ws.Cells["G51"].Value = new DateTime(new TimeSpan(1, 1, 10).Ticks); //new DateTime(1899, 12, 30, 1, 1, 10);
            ws.Cells["G52"].Value = new DateTime(1899, 12, 30, 2, 3, 10);
            ws.Cells["G53"].Value = new DateTime(1899, 12, 30, 3, 4, 10);
            ws.Cells["G54"].Value = new DateTime(1899, 12, 30, 4, 5, 10);

            ws.Cells["G51:G55"].Style.Numberformat.Format = "HH:MM:SS";
            tbl = ws.Tables.Add(ws.Cells["G50:G54"], "");
            tbl.ShowTotal = true;
            tbl.ShowFilter = false;
            tbl.Columns[0].TotalsRowFunction = OfficeOpenXml.Table.RowFunctions.Sum;
        }

        [TestMethod]
        public void TableDeleteTest()
        {
            using (var pkg = new ExcelPackage())
            {
                var wb = pkg.Workbook;
                var sheets = new[]
                {
                    wb.Worksheets.Add("WorkSheet A"),
                    wb.Worksheets.Add("WorkSheet B")
                };
                for (int i = 1; i <= 4; i++)
                {
                    var cell = sheets[0].Cells[1, i];
                    cell.Value = cell.Address + "_";
                    cell = sheets[1].Cells[1, i];
                    cell.Value = cell.Address + "_";
                }

                for (int i = 6; i <= 11; i++)
                {
                    var cell = sheets[0].Cells[3, i];
                    cell.Value = cell.Address + "_";
                    cell = sheets[1].Cells[3, i];
                    cell.Value = cell.Address + "_";
                }
                var tables = new[]
                {
                    sheets[1].Tables.Add(sheets[1].Cells["A1:D73"], "Tablea"),
                    sheets[0].Tables.Add(sheets[0].Cells["A1:D73"], "Table2"),
                    sheets[1].Tables.Add(sheets[1].Cells["F3:K10"], "Tableb"),
                    sheets[0].Tables.Add(sheets[0].Cells["F3:K10"], "Table3"),
                };
                Assert.AreEqual(5, wb._nextTableID);
                Assert.AreEqual(1, tables[0].Id);
                Assert.AreEqual(2, tables[1].Id);
                try
                {
                    sheets[0].Tables.Delete("Tablea");
                    Assert.Fail("ArgumentException should have been thrown.");
                }
                catch (ArgumentOutOfRangeException) { }
                sheets[1].Tables.Delete("Tablea");
                Assert.AreEqual(1, tables[1].Id);
                Assert.AreEqual(2, tables[2].Id);

                try
                {
                    sheets[1].Tables.Delete(4);
                    Assert.Fail("ArgumentException should have been thrown.");
                }
                catch (ArgumentOutOfRangeException) { }
                var range = sheets[0].Cells[sheets[0].Tables[1].Address.Address];
                sheets[0].Tables.Delete(1, true);
                foreach (var cell in range)
                {
                    Assert.IsNull(cell.Value);
                }
            }
        }

        //[Ignore]
        //[TestMethod]
        public void CopyTable()
        {
            _pck.Workbook.Worksheets.Copy("File4", "Copied table");
        }
        //[Ignore]
        //[TestMethod]
        public void CopyRange()
        {
            var ws = _pck.Workbook.Worksheets.Add("CopyTest");

            ws.Cells["A1"].Value = "Single Cell";
            ws.Cells["A2"].Value = "Merged Cells";
            ws.Cells["A2:D30"].Merge = true;
            ws.Cells["A1"].Style.Font.Bold = true;
            ws.Cells["G4:H5"].Merge = true;
            ws.Cells["B3:C5"].Copy(ws.Cells["G4"]);
        }
        //[Ignore]
        //[TestMethod]
        public void CopyMergedRange()
        {
            var ws = _pck.Workbook.Worksheets.Add("CopyMergedRangeTest");

            ws.Cells["A11:C11"].Merge = true;
            ws.Cells["A12:C12"].Merge = true;

            var source = ws.Cells["A11:C12"];
            var target = ws.Cells["A21"];

            source.Copy(target);

            var a21 = ws.Cells[21, 1];
            var a22 = ws.Cells[22, 1];

            Assert.IsTrue(a21.Merge);
            Assert.IsTrue(a22.Merge);

            //Assert.AreNotEqual(a21.MergeId, a22.MergeId);
        }
        [Ignore]
        [TestMethod]
        public void CopyPivotTable()
        {
            _pck.Workbook.Worksheets.Copy("Pivot-Group Date", "Copied Pivottable 1");
            _pck.Workbook.Worksheets.Copy("Pivot-Group Number", "Copied Pivottable 2");
        }
        [Ignore]
        [TestMethod]
        public void Stylebug()
        {
            ExcelPackage p = new ExcelPackage(new FileInfo(@"c:\temp\FullProjecte.xlsx"));

            var ws = p.Workbook.Worksheets.First();
            ws.Cells[12, 1].Value = 0;
            ws.Cells[12, 2].Value = new DateTime(2010, 9, 14);
            ws.Cells[12, 3].Value = "Federico Lois";
            ws.Cells[12, 4].Value = "Nakami";
            ws.Cells[12, 5].Value = "Hores";
            ws.Cells[12, 7].Value = 120;
            ws.Cells[12, 8].Value = "A definir";
            ws.Cells[12, 9].Value = new DateTime(2010, 9, 14);
            ws.Cells[12, 10].Value = new DateTime(2010, 9, 14);
            ws.Cells[12, 11].Value = "Transferència";

            ws.InsertRow(13, 1, 12);
            ws.Cells[13, 1].Value = 0;
            ws.Cells[13, 2].Value = new DateTime(2010, 9, 14);
            ws.Cells[13, 3].Value = "Federico Lois";
            ws.Cells[13, 4].Value = "Nakami";
            ws.Cells[13, 5].Value = "Hores";
            ws.Cells[13, 7].Value = 120;
            ws.Cells[13, 8].Value = "A definir";
            ws.Cells[13, 9].Value = new DateTime(2010, 9, 14);
            ws.Cells[13, 10].Value = new DateTime(2010, 9, 14);
            ws.Cells[13, 11].Value = "Transferència";

            ws.InsertRow(14, 1, 13);

            ws.InsertRow(19, 1, 19);
            ws.InsertRow(26, 1, 26);
            ws.InsertRow(33, 1, 33);
            p.SaveAs(new FileInfo(@"c:\temp\FullProjecte_new.xlsx"));
        }
        [Ignore]
        [TestMethod]
        public void ReadBug()
        {
            using (var package = new ExcelPackage(new FileInfo(@"c:\temp\error.xlsx")))
            {
                var fulla = package.Workbook.Worksheets.FirstOrDefault();
                var r = fulla == null ? null : fulla.Cells["a:a"]
                .Where(t => !string.IsNullOrWhiteSpace(t.Text)).Select(cell => cell.Value.ToString())
                .ToList();
            }
        }
        //[Ignore]
        //[TestMethod]
        public void FormulaOverwrite()
        {
            var ws = _pck.Workbook.Worksheets.Add("FormulaOverwrite");
            //Inside
            ws.Cells["A1:G12"].Formula = "B1+C1";
            ws.Cells["B2:C3"].Formula = "G2+E1";


            //Top bottom overwrite
            ws.Cells["A14:G26"].Formula = "B1+C1+D1";
            ws.Cells["B13:C28"].Formula = "G2+E1";

            //Top bottom overwrite
            ws.Cells["B30:E42"].Formula = "B1+C1+$D$1";
            ws.Cells["A32:H33"].Formula = "G2+E1";

            ws.Cells["A50:A59"].CreateArrayFormula("C50+D50");
            ws.Cells["A1"].Value = "test";
            ws.Cells["A15"].Value = "Värde";
            ws.Cells["C12"].AddComment("Test", "JJOD");
            ws.Cells["D12:I12"].Merge = true;
            ws.Cells["D12:I12"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;
            ws.Cells["D12:I12"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Top;
            ws.Cells["D12:I12"].Style.WrapText = true;

            ws.Cells["F1:F3"].Formula = "F2+F3";
            ws.Cells["J1:J3"].Formula = "F2+F3";
            ws.Cells["F1:F3"].Formula = "F5+F6";    //Overwrite same range
        }
        //[Ignore]
        //[TestMethod]
        public void DefinedName()
        {
            var ws = _pck.Workbook.Worksheets.Add("Names");
            ws.Names.Add("RefError", ws.Cells["#REF!"]);

            ws.Cells["A1"].Value = "Test";
            ws.Cells["A1"].Style.Font.Size = 8.5F;

            ws.Names.Add("Address", ws.Cells["A2:A3"]);
            ws.Cells["Address"].Value = 1;
            ws.Names.AddValue("Value", 5);
            ws.Names.Add("FullRow", ws.Cells["2:2"]);
            ws.Names.Add("FullCol", ws.Cells["A:A"]);
            //ws.Names["Value"].Style.Border.Bottom.Color.SetColor(Color.Black);
            ws.Names.AddFormula("Formula", "Names!A2+Names!A3+Names!Value");
        }
        [Ignore]
        [TestMethod]
        public void URL()
        {
            var p = new ExcelPackage(new FileInfo(@"c:\temp\url.xlsx"));
            foreach (var ws in p.Workbook.Worksheets)
            {

            }
            p.SaveAs(new FileInfo(@"c:\temp\urlsaved.xlsx"));
        }
        //[TestMethod]
        public void LoadDataReader()
        {
            if (_pck == null) _pck = new ExcelPackage();
            var ws = _pck.Workbook.Worksheets.Add("Loaded DataReader");
            ExcelRangeBase range;
            using (var dt = new DataTable())
            {
                dt.Columns.Add("String", typeof(string));
                dt.Columns.Add("Int", typeof(int));
                dt.Columns.Add("Bool", typeof(bool));
                dt.Columns.Add("Double", typeof(double));

                var dr = dt.NewRow();
                dr[0] = "Row1";
                dr[1] = 1;
                dr[2] = true;
                dr[3] = 1.5;
                dt.Rows.Add(dr);

                dr = dt.NewRow();
                dr[0] = "Row2";
                dr[1] = 2;
                dr[2] = false;
                dr[3] = 2.25;
                dt.Rows.Add(dr);

                //dr = dt.NewRow();
                //dr[0] = "Row3";
                //dr[1] = 3;
                //dr[2] = true;
                //dr[3] = 3.125;
                //dt.Rows.Add(dr);

                using (var reader = dt.CreateDataReader())
                {
                    range = ws.Cells["A1"].LoadFromDataReader(reader, true, "My_Table",
                                                              OfficeOpenXml.Table.TableStyles.Medium5);
                }
                Assert.AreEqual(1, range.Start.Column);
                Assert.AreEqual(4, range.End.Column);
                Assert.AreEqual(1, range.Start.Row);
                Assert.AreEqual(3, range.End.Row);

                using (var reader = dt.CreateDataReader())
                {
                    range = ws.Cells["A5"].LoadFromDataReader(reader, false, "My_Table2",
                                                              OfficeOpenXml.Table.TableStyles.Medium5);
                }
                Assert.AreEqual(1, range.Start.Column);
                Assert.AreEqual(4, range.End.Column);
                Assert.AreEqual(5, range.Start.Row);
                Assert.AreEqual(6, range.End.Row);
            }
        }


        //[TestMethod, Ignore]
        public void LoadDataTable()
        {
            if (_pck == null) _pck = new ExcelPackage();
            var ws = _pck.Workbook.Worksheets.Add("Loaded DataTable");

            var dt = new DataTable();
            dt.Columns.Add("String", typeof(string));
            dt.Columns.Add("Int", typeof(int));
            dt.Columns.Add("Bool", typeof(bool));
            dt.Columns.Add("Double", typeof(double));


            var dr = dt.NewRow();
            dr[0] = "Row1";
            dr[1] = 1;
            dr[2] = true;
            dr[3] = 1.5;
            dt.Rows.Add(dr);

            dr = dt.NewRow();
            dr[0] = "Row2";
            dr[1] = 2;
            dr[2] = false;
            dr[3] = 2.25;
            dt.Rows.Add(dr);

            dr = dt.NewRow();
            dr[0] = "Row3";
            dr[1] = 3;
            dr[2] = true;
            dr[3] = 3.125;
            dt.Rows.Add(dr);

            ws.Cells["A1"].LoadFromDataTable(dt, true, OfficeOpenXml.Table.TableStyles.Medium5);

            //worksheet.Cells[startRow, 7, worksheet.Dimension.End.Row, 7].FormulaR1C1 = "=IF(RC[-2]=0,0,RC[-1]/RC[-2])";

            ws.Tables[0].Columns[1].TotalsRowFunction = OfficeOpenXml.Table.RowFunctions.Sum;
            ws.Tables[0].ShowTotal = true;
        }

        [Ignore]
        [TestMethod]
        public void LoadEmptyDataTable()
        {
            if (_pck == null) _pck = new ExcelPackage();
            var ws = _pck.Workbook.Worksheets.Add("Loaded Empty DataTable");

            var dt = new DataTable();
            dt.Columns.Add(new DataColumn("col1"));
            dt.Columns.Add(new DataColumn("col2"));
            ws.Cells["A1"].LoadFromDataTable(dt, true);

            ws.Cells["D1"].LoadFromDataTable(dt, false);
        }

        [TestMethod]
        public void LoadText_Bug15015()
        {
            var package = new ExcelPackage();
            var ws = package.Workbook.Worksheets.Add("Loaded Text");
            ws.Cells["A1"].LoadFromText("\"text with eol,\r\n in a cell\",\"other value\"", new ExcelTextFormat { TextQualifier = '"', EOL = ",\r\n", Delimiter = ',' });
        }

        [TestMethod]
        public void LoadText_Bug15015_Negative()
        {
            var package = new ExcelPackage();
            var ws = package.Workbook.Worksheets.Add("Loaded Text");
            bool exceptionThrown = false;
            try
            {
                ws.Cells["A1"].LoadFromText("\"text with eol,\r\n",
                                            new ExcelTextFormat { TextQualifier = '"', EOL = ",\r\n", Delimiter = ',' });
            }
            catch (Exception e)
            {
                Assert.AreEqual("Text delimiter is not closed in line : \"text with eol", e.Message, "Exception message");
                exceptionThrown = true;
            }
            Assert.IsTrue(exceptionThrown, "Exception thrown");
        }

        //[Ignore]
        //[TestMethod]
        public void LoadText()
        {
            var ws = _pck.Workbook.Worksheets.Add("Loaded Text");

            ws.Cells["A1"].LoadFromText("1.2");
            ws.Cells["A2"].LoadFromText("1,\"Test av data\",\"12,2\",\"\"Test\"\"");
            ws.Cells["A3"].LoadFromText("\"1,3\",\"Test av \"\"data\",\"12,2\",\"Test\"\"\"", new ExcelTextFormat() { TextQualifier = '"' });

            ws = _pck.Workbook.Worksheets.Add("File1");
            // ws.Cells["A1"].LoadFromText(new FileInfo(@"c:\temp\csv\et1c1004.csv"), new ExcelTextFormat() {SkipLinesBeginning=3,SkipLinesEnd=1, EOL="\n"});

            ws = _pck.Workbook.Worksheets.Add("File2");
            //ws.Cells["A1"].LoadFromText(new FileInfo(@"c:\temp\csv\etiv2812.csv"), new ExcelTextFormat() { SkipLinesBeginning = 3, SkipLinesEnd = 1, EOL = "\n" });

            //ws = _pck.Workbook.Worksheets.Add("File3");
            //ws.Cells["A1"].LoadFromText(new FileInfo(@"c:\temp\csv\last_gics.txt"), new ExcelTextFormat() { SkipLinesBeginning = 1, Delimiter='|'});

            ws = _pck.Workbook.Worksheets.Add("File4");
            //ws.Cells["A1"].LoadFromText(new FileInfo(@"c:\temp\csv\20060927.custom_open_positions.cdf.SPP"), new ExcelTextFormat() { SkipLinesBeginning = 2, SkipLinesEnd=2, TextQualifier='"', DataTypes=new eDataTypes[] {eDataTypes.Number,eDataTypes.String, eDataTypes.Number, eDataTypes.Number, eDataTypes.Number, eDataTypes.String, eDataTypes.Number, eDataTypes.Number, eDataTypes.String, eDataTypes.String, eDataTypes.Number, eDataTypes.Number, eDataTypes.Number}},
            //    OfficeOpenXml.Table.TableStyles.Medium27, true);

            ws.Cells["A1"].LoadFromText("1,\"Test\",\"\",\"\"\"\",3", new ExcelTextFormat() { TextQualifier = '\"' });

            var style = _pck.Workbook.Styles.CreateNamedStyle("RedStyle");
            style.Style.Fill.PatternType = ExcelFillStyle.Solid;
            style.Style.Fill.BackgroundColor.SetColor(Color.Red);

            //var tbl = ws.Tables[ws.Tables.Count - 1];
            //tbl.ShowTotal = true;
            //tbl.TotalsRowCellStyle = "RedStyle";
            //tbl.HeaderRowCellStyle = "RedStyle";
        }
        [TestMethod]
        public void TestRepeatRowsAndColumnsTest()
        {
            var p = new ExcelPackage();

            var w = p.Workbook.Worksheets.Add("RepeatRowsAndColumnsTest");

            w.PrinterSettings.RepeatColumns = new ExcelAddress("A:A");
            w.PrinterSettings.RepeatRows = new ExcelAddress("1:1");

            Assert.IsNotNull(w.PrinterSettings.RepeatColumns);
            Assert.IsNotNull(w.PrinterSettings.RepeatRows); // Fails!
        }
        //[Ignore]
        //[TestMethod]
        public void Merge()
        {
            var ws = _pck.Workbook.Worksheets.Add("Merge");
            ws.Cells["A1:A4"].Merge = true;
            ws.Cells["C1:C4,C8:C12"].Merge = true;
            ws.Cells["D13:E18,G5,U32:U45"].Merge = true;
            ws.Cells["D13:E18,G5,U32:U45"].Style.WrapText = true;
            //ws.Cells["50:52"].Merge = true;
            ws.Cells["AA:AC"].Merge = true;
            ws.SetValue(13, 4, "Merged\r\nnew row");
        }
        //[Ignore]
        //[TestMethod]
        public void DefaultColWidth()
        {
            var ws = _pck.Workbook.Worksheets.Add("DefColWidth");
            ws.DefaultColWidth = 45;
        }
        //[Ignore]
        //[TestMethod]
        public void LoadArray()
        {
            var ws = _pck.Workbook.Worksheets.Add("Loaded Array");
            List<object[]> testArray = new List<object[]>() { new object[] { 3, 4, 5, 6 }, new string[] { "Test1", "test", "5", "6" } };
            ws.Cells["A1"].LoadFromArrays(testArray);
        }
        [Ignore]
        [TestMethod]
        public void DefColWidthBug()
        {
            ExcelWorkbook book = _pck.Workbook;
            ExcelWorksheet sheet = book.Worksheets.Add("Gebruikers");

            sheet.DefaultColWidth = 25d;
            //sheet.defaultRowHeight = 15d; // needed to make sure the resulting file is valid!

            // Create the header row
            sheet.Cells[1, 1].Value = "Afdeling code";
            sheet.Cells[1, 2].Value = "Afdeling naam";
            sheet.Cells[1, 3].Value = "Voornaam";
            sheet.Cells[1, 4].Value = "Tussenvoegsel";
            sheet.Cells[1, 5].Value = "Achternaam";
            sheet.Cells[1, 6].Value = "Gebruikersnaam";
            sheet.Cells[1, 7].Value = "E-mail adres";
            ExcelRange headerRow = sheet.Cells[1, 1, 1, 7];
            headerRow.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
            headerRow.Style.Font.Size = 12;
            headerRow.Style.Font.Bold = true;

            //// Create a context for retrieving the users
            //using (PalauDataContext context = new PalauDataContext())
            //{
            //    int currentRow = 2;

            //    // iterate through all users in the export and add their info
            //    // to the worksheet.
            //    foreach (vw_ExportUser user in
            //      context.vw_ExportUsers
            //      .OrderBy(u => u.DepartmentCode)
            //      .ThenBy(u => u.AspNetUserName))
            //    {
            //        sheet.Cells[currentRow, 1].Value = user.DepartmentCode;
            //        sheet.Cells[currentRow, 2].Value = user.DepartmentName;
            //        sheet.Cells[currentRow, 3].Value = user.UserFirstName;
            //        sheet.Cells[currentRow, 4].Value = user.UserInfix;
            //        sheet.Cells[currentRow, 5].Value = user.UserSurname;
            //        sheet.Cells[currentRow, 6].Value = user.AspNetUserName;
            //        sheet.Cells[currentRow, 7].Value = user.AspNetEmail;

            //        currentRow++;
            //    }
            //}

            // return the filled Excel workbook
            //  return pkg

        }
        [Ignore]
        [TestMethod]
        public void CloseProblem()
        {
            ExcelPackage pck = new ExcelPackage();
            var ws = pck.Workbook.Worksheets.Add("Manual Receipts");

            ws.Cells["A1"].Value = " SpaceNeedle Manual Receipt Form";

            using (ExcelRange r = ws.Cells["A1:F1"])
            {
                r.Merge = true;
                r.Style.Font.SetFromFont(new Font("Arial", 18, FontStyle.Italic));
                r.Style.Font.Color.SetColor(Color.DarkRed);
                r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
                //r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                //r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(23, 55, 93));
            }
            //			ws.Column(1).BestFit = true;
            ws.Column(1).Width = 17;
            ws.Column(5).Width = 20;


            ws.Cells["A2"].Value = "Date Produced";

            ws.Cells["A2"].Style.Font.Bold = true;
            ws.Cells["B2"].Value = DateTime.Now.ToShortDateString();
            ws.Cells["D2"].Value = "Quantity";
            ws.Cells["D2"].Style.Font.Bold = true;
            ws.Cells["E2"].Value = "txt";

            ws.Cells["C4"].Value = "Receipt Number";
            ws.Cells["C4"].Style.WrapText = true;
            ws.Cells["C4"].Style.Font.Bold = true;

            int rowNbr = 5;
            for (int entryNbr = 1; entryNbr <= 1; entryNbr += 1)
            {
                ws.Cells["B" + rowNbr].Value = entryNbr;
                ws.Cells["C" + rowNbr].Value = 1 + entryNbr - 1;
                rowNbr += 1;
            }
            pck.SaveAs(new FileInfo(".\\test.xlsx"));
        }
        [Ignore]
        [TestMethod]
        public void OpenXlsm()
        {
            ExcelPackage p = new ExcelPackage(new FileInfo("c:\\temp\\cs1.xlsx"));
            int c = p.Workbook.Worksheets.Count;
            p.Save();
        }
        [Ignore]
        [TestMethod]
        public void Mergebug()
        {
            var xlPackage = new ExcelPackage();
            var xlWorkSheet = xlPackage.Workbook.Worksheets.Add("Test Sheet");
            var Cells = xlWorkSheet.Cells;
            var TitleCell = Cells[1, 1, 1, 3];

            TitleCell.Merge = true;
            TitleCell.Value = "Test Spreadsheet";
            Cells[2, 1].Value = "Test Sub Heading\r\ntest" + (char)22;
            for (int i = 0; i < 256; i++)
            {
                Cells[3, i + 1].Value = (char)i;
            }
            Cells[2, 1].Style.WrapText = true;
            xlWorkSheet.Row(1).Height = 50;
            xlPackage.SaveAs(new FileInfo("c:\\temp\\Mergebug.xlsx"));
        }
        [Ignore]
        [TestMethod]
        public void OpenProblem()
        {
            var xlPackage = new ExcelPackage();
            var ws = xlPackage.Workbook.Worksheets.Add("W1");
            xlPackage.Workbook.Worksheets.Add("W2");

            ws.Cells["A1:A10"].Formula = "W2!A1+C1";
            ws.Cells["B1:B10"].FormulaR1C1 = "W2!R1C1+C1";
            xlPackage.SaveAs(new FileInfo("c:\\temp\\Mergebug.xlsx"));
        }
        [Ignore]
        [TestMethod]
        public void ProtectionProblem()
        {
            var xlPackage = new ExcelPackage(new FileInfo("c:\\temp\\CovenantsCheckReportTemplate.xlsx"));
            var ws = xlPackage.Workbook.Worksheets.First();
            ws.Protection.SetPassword("Test");
            xlPackage.SaveAs(new FileInfo("c:\\temp\\Mergebug.xlsx"));
        }
        [Ignore]
        [TestMethod]
        public void Nametest()
        {
            var pck = new ExcelPackage(new FileInfo("c:\\temp\\names.xlsx"));
            var ws = pck.Workbook.Worksheets.First();
            ws.Cells["H37"].Formula = "\"Test\"";
            pck.SaveAs(new FileInfo(@"c:\\temp\\nametest_new.xlsx"));
        }
        //[Ignore]
        //[TestMethod]
        public void CreatePivotTable()
        {
            var wsPivot1 = _pck.Workbook.Worksheets.Add("Rows-Data on columns");
            var wsPivot2 = _pck.Workbook.Worksheets.Add("Rows-Data on rows");
            var wsPivot3 = _pck.Workbook.Worksheets.Add("Columns-Data on columns");
            var wsPivot4 = _pck.Workbook.Worksheets.Add("Columns-Data on rows");
            var wsPivot5 = _pck.Workbook.Worksheets.Add("Columns/Rows-Data on columns");
            var wsPivot6 = _pck.Workbook.Worksheets.Add("Columns/Rows-Data on rows");
            var wsPivot7 = _pck.Workbook.Worksheets.Add("Rows/Page-Data on Columns");
            var wsPivot8 = _pck.Workbook.Worksheets.Add("Pivot-Group Date");
            var wsPivot9 = _pck.Workbook.Worksheets.Add("Pivot-Group Number");

            var ws = _pck.Workbook.Worksheets.Add("Data");
            ws.Cells["K1"].Value = "Item";
            ws.Cells["L1"].Value = "Category";
            ws.Cells["M1"].Value = "Stock";
            ws.Cells["N1"].Value = "Price";
            ws.Cells["O1"].Value = "Date for grouping";

            ws.Cells["K2"].Value = "Crowbar";
            ws.Cells["L2"].Value = "Hardware";
            ws.Cells["M2"].Value = 12;
            ws.Cells["N2"].Value = 85.2;
            ws.Cells["O2"].Value = new DateTime(2010, 1, 31);

            ws.Cells["K3"].Value = "Crowbar";
            ws.Cells["L3"].Value = "Hardware";
            ws.Cells["M3"].Value = 15;
            ws.Cells["N3"].Value = 12.2;
            ws.Cells["O3"].Value = new DateTime(2010, 2, 28);

            ws.Cells["K4"].Value = "Hammer";
            ws.Cells["L4"].Value = "Hardware";
            ws.Cells["M4"].Value = 550;
            ws.Cells["N4"].Value = 72.7;
            ws.Cells["O4"].Value = new DateTime(2010, 3, 31);

            ws.Cells["K5"].Value = "Hammer";
            ws.Cells["L5"].Value = "Hardware";
            ws.Cells["M5"].Value = 120;
            ws.Cells["N5"].Value = 11.3;
            ws.Cells["O5"].Value = new DateTime(2010, 4, 30);

            ws.Cells["K6"].Value = "Crowbar";
            ws.Cells["L6"].Value = "Hardware";
            ws.Cells["M6"].Value = 120;
            ws.Cells["N6"].Value = 173.2;
            ws.Cells["O6"].Value = new DateTime(2010, 5, 31);

            ws.Cells["K7"].Value = "Hammer";
            ws.Cells["L7"].Value = "Hardware";
            ws.Cells["M7"].Value = 1;
            ws.Cells["N7"].Value = 4.2;
            ws.Cells["O7"].Value = new DateTime(2010, 6, 30);

            ws.Cells["K8"].Value = "Saw";
            ws.Cells["L8"].Value = "Hardware";
            ws.Cells["M8"].Value = 4;
            ws.Cells["N8"].Value = 33.12;
            ws.Cells["O8"].Value = new DateTime(2010, 6, 28);

            ws.Cells["K9"].Value = "Screwdriver";
            ws.Cells["L9"].Value = "Hardware";
            ws.Cells["M9"].Value = 1200;
            ws.Cells["N9"].Value = 45.2;
            ws.Cells["O9"].Value = new DateTime(2010, 8, 31);

            ws.Cells["K10"].Value = "Apple";
            ws.Cells["L10"].Value = "Groceries";
            ws.Cells["M10"].Value = 807;
            ws.Cells["N10"].Value = 1.2;
            ws.Cells["O10"].Value = new DateTime(2010, 9, 30);

            ws.Cells["K11"].Value = "Butter";
            ws.Cells["L11"].Value = "Groceries";
            ws.Cells["M11"].Value = 52;
            ws.Cells["N11"].Value = 7.2;
            ws.Cells["O11"].Value = new DateTime(2010, 10, 31);
            ws.Cells["O2:O11"].Style.Numberformat.Format = "yyyy-MM-dd";

            var pt = wsPivot1.PivotTables.Add(wsPivot1.Cells["A1"], ws.Cells["K1:N11"], "Pivottable1");
            pt.GrandTotalCaption = "Total amount";
            pt.RowFields.Add(pt.Fields[1]);
            pt.RowFields.Add(pt.Fields[0]);
            pt.DataFields.Add(pt.Fields[3]);
            pt.DataFields.Add(pt.Fields[2]);
            pt.DataFields[0].Function = DataFieldFunctions.Product;
            pt.DataOnRows = false;

            pt = wsPivot2.PivotTables.Add(wsPivot2.Cells["A1"], ws.Cells["K1:N11"], "Pivottable2");
            pt.RowFields.Add(pt.Fields[1]);
            pt.RowFields.Add(pt.Fields[0]);
            pt.DataFields.Add(pt.Fields[3]);
            pt.DataFields.Add(pt.Fields[2]);
            pt.DataFields[0].Function = DataFieldFunctions.Average;
            pt.DataOnRows = true;

            pt = wsPivot3.PivotTables.Add(wsPivot3.Cells["A1"], ws.Cells["K1:N11"], "Pivottable3");
            pt.ColumnFields.Add(pt.Fields[1]);
            pt.ColumnFields.Add(pt.Fields[0]);
            pt.DataFields.Add(pt.Fields[3]);
            pt.DataFields.Add(pt.Fields[2]);
            pt.DataOnRows = false;

            pt = wsPivot4.PivotTables.Add(wsPivot4.Cells["A1"], ws.Cells["K1:N11"], "Pivottable4");
            pt.ColumnFields.Add(pt.Fields[1]);
            pt.ColumnFields.Add(pt.Fields[0]);
            pt.DataFields.Add(pt.Fields[3]);
            pt.DataFields.Add(pt.Fields[2]);
            pt.DataOnRows = true;

            pt = wsPivot5.PivotTables.Add(wsPivot5.Cells["A1"], ws.Cells["K1:N11"], "Pivottable5");
            pt.ColumnFields.Add(pt.Fields[1]);
            pt.RowFields.Add(pt.Fields[0]);
            pt.DataFields.Add(pt.Fields[3]);
            pt.DataFields.Add(pt.Fields[2]);
            pt.DataOnRows = false;

            pt = wsPivot6.PivotTables.Add(wsPivot6.Cells["A1"], ws.Cells["K1:N11"], "Pivottable6");
            pt.ColumnFields.Add(pt.Fields[1]);
            pt.RowFields.Add(pt.Fields[0]);
            pt.DataFields.Add(pt.Fields[3]);
            pt.DataFields.Add(pt.Fields[2]);
            pt.DataOnRows = true;
            wsPivot6.Drawings.AddChart("Pivotchart6", OfficeOpenXml.Drawing.Chart.eChartType.BarStacked3D, pt);

            pt = wsPivot7.PivotTables.Add(wsPivot7.Cells["A3"], ws.Cells["K1:N11"], "Pivottable7");
            pt.PageFields.Add(pt.Fields[1]);
            pt.RowFields.Add(pt.Fields[0]);
            pt.DataFields.Add(pt.Fields[3]);
            pt.DataFields.Add(pt.Fields[2]);
            pt.DataOnRows = false;

            pt.Fields[0].SubTotalFunctions = eSubTotalFunctions.Sum | eSubTotalFunctions.Max;
            Assert.AreEqual(pt.Fields[0].SubTotalFunctions, eSubTotalFunctions.Sum | eSubTotalFunctions.Max);

            pt.Fields[0].SubTotalFunctions = eSubTotalFunctions.Sum | eSubTotalFunctions.Product | eSubTotalFunctions.StdDevP;
            Assert.AreEqual(pt.Fields[0].SubTotalFunctions, eSubTotalFunctions.Sum | eSubTotalFunctions.Product | eSubTotalFunctions.StdDevP);

            pt.Fields[0].SubTotalFunctions = eSubTotalFunctions.None;
            Assert.AreEqual(pt.Fields[0].SubTotalFunctions, eSubTotalFunctions.None);

            pt.Fields[0].SubTotalFunctions = eSubTotalFunctions.Default;
            Assert.AreEqual(pt.Fields[0].SubTotalFunctions, eSubTotalFunctions.Default);

            pt.Fields[0].Sort = eSortType.Descending;
            pt.TableStyle = OfficeOpenXml.Table.TableStyles.Medium14;

            pt = wsPivot8.PivotTables.Add(wsPivot8.Cells["A3"], ws.Cells["K1:O11"], "Pivottable8");
            pt.RowFields.Add(pt.Fields[1]);
            pt.RowFields.Add(pt.Fields[4]);
            pt.Fields[4].AddDateGrouping(eDateGroupBy.Years | eDateGroupBy.Months | eDateGroupBy.Days | eDateGroupBy.Quarters, new DateTime(2010, 01, 31), new DateTime(2010, 11, 30));
            pt.RowHeaderCaption = "År";
            pt.Fields[4].Name = "Dag";
            pt.Fields[5].Name = "Månad";
            pt.Fields[6].Name = "Kvartal";
            pt.GrandTotalCaption = "Totalt";

            pt.DataFields.Add(pt.Fields[3]);
            pt.DataFields.Add(pt.Fields[2]);
            pt.DataOnRows = true;

            pt = wsPivot9.PivotTables.Add(wsPivot9.Cells["A3"], ws.Cells["K1:N11"], "Pivottable9");
            pt.PageFields.Add(pt.Fields[1]);
            pt.RowFields.Add(pt.Fields[3]);
            pt.RowFields[0].AddNumericGrouping(-3.3, 5.5, 4.0);
            pt.DataFields.Add(pt.Fields[2]);
            pt.DataOnRows = false;
            pt.TableStyle = OfficeOpenXml.Table.TableStyles.Medium14;

            pt = wsPivot8.PivotTables.Add(wsPivot8.Cells["H3"], ws.Cells["K1:O11"], "Pivottable10");
            pt.RowFields.Add(pt.Fields[1]);
            pt.RowFields.Add(pt.Fields[4]);
            pt.Fields[4].AddDateGrouping(7, new DateTime(2010, 01, 31), new DateTime(2010, 11, 30));
            pt.RowHeaderCaption = "Veckor";
            pt.GrandTotalCaption = "Totalt";

            pt = wsPivot8.PivotTables.Add(wsPivot8.Cells["A60"], ws.Cells["K1:O11"], "Pivottable11");
            pt.RowFields.Add(pt.Fields["Category"]);
            pt.RowFields.Add(pt.Fields["Item"]);
            pt.RowFields.Add(pt.Fields["Date for grouping"]);

            pt.DataFields.Add(pt.Fields[3]);
            pt.DataFields.Add(pt.Fields[2]);
            pt.DataOnRows = true;
        }
        [Ignore]
        [TestMethod]
        public void ReadPivotTable()
        {
            ExcelPackage pck = new ExcelPackage(new FileInfo(@"c:\temp\pivot\pivotforread.xlsx"));

            var pivot1 = pck.Workbook.Worksheets[2].PivotTables[0];

            Assert.AreEqual(pivot1.Fields.Count, 24);
            Assert.AreEqual(pivot1.RowFields.Count, 3);
            Assert.AreEqual(pivot1.DataFields.Count, 7);
            Assert.AreEqual(pivot1.ColumnFields.Count, 0);

            Assert.AreEqual(pivot1.DataFields[1].Name, "Sum of n3");
            Assert.AreEqual(pivot1.Fields[2].Sort, eSortType.Ascending);

            Assert.AreEqual(pivot1.DataOnRows, false);

            var pivot2 = pck.Workbook.Worksheets[2].PivotTables[0];
            var pivot3 = pck.Workbook.Worksheets[3].PivotTables[0];

            var pivot4 = pck.Workbook.Worksheets[4].PivotTables[0];
            var pivot5 = pck.Workbook.Worksheets[5].PivotTables[0];
            pivot5.CacheDefinition.SourceRange = pck.Workbook.Worksheets[1].Cells["Q1:X300"];

            var pivot6 = pck.Workbook.Worksheets[6].PivotTables[0];

            pck.Workbook.Worksheets[6].Drawings.AddChart("chart1", OfficeOpenXml.Drawing.Chart.eChartType.ColumnStacked3D, pivot6);

            pck.SaveAs(new FileInfo(@"c:\temp\pivot\pivotforread_new.xlsx"));
        }
        [Ignore]
        [TestMethod]
        public void CreatePivotMultData()
        {
            FileInfo fi = new FileInfo(@"c:\temp\test.xlsx");
            ExcelPackage pck = new ExcelPackage(fi);

            var ws = pck.Workbook.Worksheets.Add("Data");
            var pv = pck.Workbook.Worksheets.Add("Pivot");

            ws.Cells["A1"].Value = "Data1";
            ws.Cells["B1"].Value = "Data2";

            ws.Cells["A2"].Value = "1";
            ws.Cells["B2"].Value = "2";

            ws.Cells["A3"].Value = "3";
            ws.Cells["B3"].Value = "4";

            ws.Select("A1:B3");

            var pt = pv.PivotTables.Add(pv.SelectedRange, ws.SelectedRange, "Pivot");

            pt.RowFields.Add(pt.Fields["Data2"]);

            var df = pt.DataFields.Add(pt.Fields["Data1"]);
            df.Function = DataFieldFunctions.Count;

            df = pt.DataFields.Add(pt.Fields["Data1"]);
            df.Function = DataFieldFunctions.Sum;

            df = pt.DataFields.Add(pt.Fields["Data1"]);
            df.Function = DataFieldFunctions.StdDev;
            df.Name = "DatA1_2";

            pck.Save();
        }
        //[Ignore]
        [TestMethod]
        public void SetBackground()
        {
            var ws = _pck.Workbook.Worksheets.Add("backimg");

            ws.BackgroundImage.Image = Properties.Resources.Test1;
            ws = _pck.Workbook.Worksheets.Add("backimg2");
            ws.BackgroundImage.SetFromFile(new FileInfo(Path.Combine(_clipartPath,"Vector Drawing.wmf")));
        }
        //[Ignore]
        [TestMethod]
        public void SetHeaderFooterImage()
        {

            var ws = _pck.Workbook.Worksheets.Add("HeaderImage");
            ws.HeaderFooter.OddHeader.CenteredText = "Before ";
            var img = ws.HeaderFooter.OddHeader.InsertPicture(Properties.Resources.Test1, PictureAlignment.Centered);
            img.Title = "Renamed Image";
            img.GrayScale = true;
            img.BiLevel = true;
            img.Gain = .5;
            img.Gamma = .35;

            Assert.AreEqual(img.Width, 426);
            img.Width /= 4;
            Assert.AreEqual(img.Height, 49.5);
            img.Height /= 4;
            Assert.AreEqual(img.Left, 0);
            Assert.AreEqual(img.Top, 0);
            ws.HeaderFooter.OddHeader.CenteredText += " After";


            img = ws.HeaderFooter.EvenFooter.InsertPicture(new FileInfo(Path.Combine(_clipartPath,"Vector Drawing.wmf")), PictureAlignment.Left);
            img.Title = "DiskFile";

            img = ws.HeaderFooter.FirstHeader.InsertPicture(new FileInfo(Path.Combine(_clipartPath, "Vector Drawing2.WMF")), PictureAlignment.Right);
            img.Title = "DiskFile2";
            ws.Cells["A1:A400"].Value = 1;

            _pck.Workbook.Worksheets.Copy(ws.Name, "Copied HeaderImage");
        }
        //[Ignore]
        //[TestMethod]
        public void NamedStyles()
        {
            var wsSheet = _pck.Workbook.Worksheets.Add("NamedStyles");

            var firstNamedStyle =
                _pck.Workbook.Styles.CreateNamedStyle("templateFirst");

            var s = firstNamedStyle.Style;

            s.Fill.PatternType = ExcelFillStyle.Solid;
            s.Fill.BackgroundColor.SetColor(Color.LightGreen);
            s.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
            s.VerticalAlignment = ExcelVerticalAlignment.Center;

            var secondNamedStyle = _pck.Workbook.Styles.CreateNamedStyle("first", firstNamedStyle.Style).Style;
            secondNamedStyle.Font.Bold = true;
            secondNamedStyle.Font.SetFromFont(new Font("Arial Black", 8));
            secondNamedStyle.Border.Bottom.Style = ExcelBorderStyle.Medium;
            secondNamedStyle.Border.Left.Style = ExcelBorderStyle.Medium;

            wsSheet.Cells["B2"].Value = "Text Center";
            wsSheet.Cells["B2"].StyleName = "first";
            _pck.Workbook.Styles.NamedStyles[0].Style.Font.Name = "Arial";

            var rowStyle = _pck.Workbook.Styles.CreateNamedStyle("RowStyle", firstNamedStyle.Style).Style;
            rowStyle.Fill.BackgroundColor.SetColor(Color.Pink);
            wsSheet.Cells.StyleName = "templateFirst";
            wsSheet.Cells["C5:H15"].Style.Fill.PatternType = ExcelFillStyle.Solid;
            wsSheet.Cells["C5:H15"].Style.Fill.BackgroundColor.SetColor(Color.OrangeRed);

            wsSheet.Cells["30:35"].StyleName = "RowStyle";
            var colStyle = _pck.Workbook.Styles.CreateNamedStyle("columnStyle", firstNamedStyle.Style).Style;
            colStyle.Fill.BackgroundColor.SetColor(Color.CadetBlue);

            wsSheet.Cells["D:E"].StyleName = "ColumnStyle";
        }
        //[Ignore]
        //[TestMethod]
        public void StyleFill()
        {
            var ws = _pck.Workbook.Worksheets.Add("Fills");
            ws.Cells["A1:C3"].Style.Fill.Gradient.Type = ExcelFillGradientType.Linear;
            ws.Cells["A1:C3"].Style.Fill.Gradient.Color1.SetColor(Color.Red);
            ws.Cells["A1:C3"].Style.Fill.Gradient.Color2.SetColor(Color.Blue);

            ws.Cells["A1"].Style.Fill.PatternType = ExcelFillStyle.MediumGray;
            ws.Cells["A1"].Style.Fill.BackgroundColor.SetColor(Color.ForestGreen);
            var r = ws.Cells["A2:A3"];
            r.Style.Fill.Gradient.Type = ExcelFillGradientType.Path;
            r.Style.Fill.Gradient.Left = 0.7;
            r.Style.Fill.Gradient.Right = 0.7;
            r.Style.Fill.Gradient.Top = 0.7;
            r.Style.Fill.Gradient.Bottom = 0.7;

            ws.Cells[4, 1, 4, 360].Style.Fill.Gradient.Type = ExcelFillGradientType.Path;

            for (double col = 1; col < 360; col++)
            {
                r = ws.Cells[4, Convert.ToInt32(col)];
                r.Style.Fill.Gradient.Degree = col;
                r.Style.Fill.Gradient.Left = col / 360;
                r.Style.Fill.Gradient.Right = col / 360;
                r.Style.Fill.Gradient.Top = col / 360;
                r.Style.Fill.Gradient.Bottom = col / 360;
            }
            r = ws.Cells["A5"];
            r.Style.Fill.Gradient.Left = .50;

            ws = _pck.Workbook.Worksheets.Add("FullFills");
            ws.Cells.Style.Fill.Gradient.Left = 0.25;
            ws.Cells["A1"].Value = "test";
            ws.Cells["A1"].RichText.Add("Test rt");
            ws.Cells.AutoFilter = true;
            Assert.AreNotEqual(ws.Cells["A1:D5"].Value, null);
        }
        [Ignore]
        [TestMethod]
        public void BuildInStyles()
        {
            var pck = new ExcelPackage();
            var ws = pck.Workbook.Worksheets.Add("Default");
            ws.Cells.Style.Font.Name = "Arial";
            ws.Cells.Style.Font.Size = 15;
            ws.Cells.Style.Border.Bottom.Style = ExcelBorderStyle.MediumDashed;
            var n = pck.Workbook.Styles.NamedStyles[0];
            n.Style.Numberformat.Format = "yyyy";
            n.Style.Font.Name = "Arial";
            n.Style.Font.Size = 15;
            n.Style.Border.Bottom.Style = ExcelBorderStyle.Dotted;
            n.Style.Border.Bottom.Color.SetColor(Color.Red);
            n.Style.Fill.PatternType = ExcelFillStyle.Solid;
            n.Style.Fill.BackgroundColor.SetColor(Color.Blue);
            n.Style.Border.Bottom.Color.SetColor(Color.Red);
            n.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            n.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            n.Style.TextRotation = 90;
            ws.Cells["a1:c3"].StyleName = "Normal";
            //  n.CustomBuildin = true;
            pck.SaveAs(new FileInfo(@"c:\temp\style.xlsx"));
        }
        //[Ignore]
        //[TestMethod]
        public void AutoFitColumns()
        {
            var ws = _pck.Workbook.Worksheets.Add("Autofit");
            ws.Cells["A1:H1"].Value = "Auto fit column that is veeery long...";
            ws.Cells["B1"].Style.TextRotation = 30;
            ws.Cells["C1"].Style.TextRotation = 45;
            ws.Cells["D1"].Style.TextRotation = 75;
            ws.Cells["E1"].Style.TextRotation = 90;
            ws.Cells["F1"].Style.TextRotation = 120;
            ws.Cells["G1"].Style.TextRotation = 135;
            ws.Cells["H1"].Style.TextRotation = 180;
            ws.Cells["A1:H1"].AutoFitColumns(0);
        }

        [TestMethod, Ignore]
        public void Moveissue()
        {
            _pck = new ExcelPackage(new FileInfo(@"C:\temp\bug\FormulaIssue\PreDelete.xlsx"));
            _pck.Workbook.Worksheets[1].DeleteRow(2, 4);
            _pck.SaveAs(new FileInfo(@"c:\temp\move.xlsx"));
        }
        [TestMethod, Ignore]
        public void DelCol()
        {
            _pck = new ExcelPackage(new FileInfo(@"C:\temp\bug\FormulaIssue\PreDeleteCol.xlsx"));
            _pck.Workbook.Worksheets[1].DeleteColumn(5, 1);
            _pck.SaveAs(new FileInfo(@"c:\temp\move.xlsx"));
        }
        [TestMethod, Ignore]
        public void InsCol()
        {
            _pck = new ExcelPackage(new FileInfo(@"C:\temp\bug\FormulaIssue\PreDeleteCol.xlsx"));
            _pck.Workbook.Worksheets[1].InsertColumn(4, 5);
            _pck.SaveAs(new FileInfo(@"c:\temp\move.xlsx"));
        }
        [Ignore]
        [TestMethod]
        public void FileLockedProblem()
        {
            using (ExcelPackage pck = new ExcelPackage(new FileInfo(@"c:\temp\url.xlsx")))
            {
                pck.Workbook.Worksheets[1].DeleteRow(1, 1);
                pck.Save();
                pck.Dispose();
            }
        }
        //[Ignore]
        //[TestMethod]
        public void CopyOverwrite()
        {
            var ws = _pck.Workbook.Worksheets.Add("CopyOverwrite");

            for (int col = 1; col < 15; col++)
            {
                for (int row = 1; row < 30; row++)
                {
                    ws.SetValue(row, col, "cell " + ExcelAddressBase.GetAddress(row, col));
                }
            }
            ws.Cells["A1:P30"].Copy(ws.Cells["B1"]);
        }
        [Ignore]
        [TestMethod]
        public void RunSample0()
        {
            FileInfo newFile = new FileInfo(@"c:\temp\bug\sample0.xlsx");
            using (ExcelPackage package = new ExcelPackage(newFile))
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
                worksheet.InsertColumn(1, 1);

                ExcelColumn entireColumn = worksheet.Column(1);

                var last = worksheet.Column(6);
                last.Style.Fill.PatternType = ExcelFillStyle.Solid;
                last.Style.Fill.BackgroundColor.SetColor(Color.Blue);
                last.ColumnMax = 7;
                worksheet.InsertColumn(7, 1);

                //save our new workbook and we are done!
                package.Save();
            }
        }
        [Ignore]
        [TestMethod]
        public void Deletews()
        {
            FileInfo newFile = new FileInfo(@"c:\temp\bug\worksheet error.xlsx");
            using (ExcelPackage package = new ExcelPackage(newFile))
            {
                var ws1 = package.Workbook.Worksheets.Add("sheet1");
                var ws2 = package.Workbook.Worksheets.Add("sheet2");
                var ws3 = package.Workbook.Worksheets.Add("sheet3");

                package.Workbook.Worksheets.MoveToStart(ws3.Name);
                //save our new workbook and we are done!
                package.Save();
            }
            using (ExcelPackage package = new ExcelPackage(newFile))
            {
                package.Workbook.Worksheets.Delete(1);
                var ws3 = package.Workbook.Worksheets.Add("sheet3");
                package.SaveAs(new FileInfo(@"c:\temp\bug\worksheet error_save.xlsx"));
            }
        }

        [TestMethod, Ignore]
        public void Issue15207()
        {
            using (ExcelPackage ep = new ExcelPackage(new FileInfo(@"c:\temp\bug\worksheet error.xlsx")))
            {
                ExcelWorkbook wb = ep.Workbook;

                if (wb != null)
                {
                    ExcelWorksheet ws = null;

                    ws = wb.Worksheets[1];

                    if (ws != null)
                    {
                        //do something with the worksheet
                        ws.Dispose();
                    }

                    wb.Dispose();

                } //if wb != null

                wb = null;

                //do some other things

                //running through this next line now throws the null reference exception
                //so the inbuilt dispose method doesn't work properly.
            } //using (ExcelPackage ep = new ExcelPackage(new FileInfo(some_file))
        }
        #region Date1904 Test Cases
        [TestMethod]
        public void TestDate1904WithoutSetting()
        {
            string file = "test1904.xlsx";
            DateTime dateTest1 = new DateTime(2008, 2, 29);
            DateTime dateTest2 = new DateTime(1950, 11, 30);

            if (File.Exists(file))
                File.Delete(file);

            ExcelPackage pack = new ExcelPackage(new FileInfo(file));
            ExcelWorksheet w = pack.Workbook.Worksheets.Add("test");
            w.Cells[1, 1, 2, 1].Style.Numberformat.Format = ExcelNumberFormat.GetFromBuildInFromID(14);
            w.Cells[1, 1].Value = dateTest1;
            w.Cells[2, 1].Value = dateTest2;
            pack.Save();


            ExcelPackage pack2 = new ExcelPackage(new FileInfo(file));
            ExcelWorksheet w2 = pack2.Workbook.Worksheets["test"];

            Assert.AreEqual(dateTest1, w2.Cells[1, 1].Value);
            Assert.AreEqual(dateTest2, w2.Cells[2, 1].Value);
        }

        [TestMethod]
        public void TestDate1904WithSetting()
        {
            string file = "test1904.xlsx";
            DateTime dateTest1 = new DateTime(2008, 2, 29);
            DateTime dateTest2 = new DateTime(1950, 11, 30);

            if (File.Exists(file))
                File.Delete(file);

            ExcelPackage pack = new ExcelPackage(new FileInfo(file));
            pack.Workbook.Date1904 = true;

            ExcelWorksheet w = pack.Workbook.Worksheets.Add("test");
            w.Cells[1, 1, 2, 1].Style.Numberformat.Format = ExcelNumberFormat.GetFromBuildInFromID(14);
            w.Cells[1, 1].Value = dateTest1;
            w.Cells[2, 1].Value = dateTest2;
            pack.Save();


            ExcelPackage pack2 = new ExcelPackage(new FileInfo(file));
            ExcelWorksheet w2 = pack2.Workbook.Worksheets["test"];

            Assert.AreEqual(dateTest1, w2.Cells[1, 1].Value);
            Assert.AreEqual(dateTest2, w2.Cells[2, 1].Value);
        }

        [TestMethod]
        public void TestDate1904SetAndRemoveSetting()
        {
            string file = "test1904.xlsx";
            DateTime dateTest1 = new DateTime(2008, 2, 29);
            DateTime dateTest2 = new DateTime(1950, 11, 30);

            if (File.Exists(file))
                File.Delete(file);

            ExcelPackage pack = new ExcelPackage(new FileInfo(file));
            pack.Workbook.Date1904 = true;

            ExcelWorksheet w = pack.Workbook.Worksheets.Add("test");
            w.Cells[1, 1, 2, 1].Style.Numberformat.Format = ExcelNumberFormat.GetFromBuildInFromID(14);
            w.Cells[1, 1].Value = dateTest1;
            w.Cells[2, 1].Value = dateTest2;
            pack.Save();


            ExcelPackage pack2 = new ExcelPackage(new FileInfo(file));
            pack2.Workbook.Date1904 = false;
            pack2.Save();


            ExcelPackage pack3 = new ExcelPackage(new FileInfo(file));
            ExcelWorksheet w3 = pack3.Workbook.Worksheets["test"];

            Assert.AreEqual(dateTest1.AddDays(365.5 * -4), w3.Cells[1, 1].Value);
            Assert.AreEqual(dateTest2.AddDays(365.5 * -4), w3.Cells[2, 1].Value);
        }

        [TestMethod]
        public void TestDate1904SetAndSetSetting()
        {
            string file = "test1904.xlsx";
            DateTime dateTest1 = new DateTime(2008, 2, 29);
            DateTime dateTest2 = new DateTime(1950, 11, 30);

            if (File.Exists(file))
                File.Delete(file);

            ExcelPackage pack = new ExcelPackage(new FileInfo(file));
            pack.Workbook.Date1904 = true;

            ExcelWorksheet w = pack.Workbook.Worksheets.Add("test");
            w.Cells[1, 1, 2, 1].Style.Numberformat.Format = ExcelNumberFormat.GetFromBuildInFromID(14);
            w.Cells[1, 1].Value = dateTest1;
            w.Cells[2, 1].Value = dateTest2;
            pack.Save();


            ExcelPackage pack2 = new ExcelPackage(new FileInfo(file));
            pack2.Workbook.Date1904 = true;  // Only the cells must be updated when this change, if set the same nothing must change
            pack2.Save();


            ExcelPackage pack3 = new ExcelPackage(new FileInfo(file));
            ExcelWorksheet w3 = pack3.Workbook.Worksheets["test"];

            Assert.AreEqual(dateTest1, w3.Cells[1, 1].Value);
            Assert.AreEqual(dateTest2, w3.Cells[2, 1].Value);
        }
        [TestMethod, Ignore]
        public void SaveToStream()
        {
            var stream = new MemoryStream(File.ReadAllBytes(@"c:\temp\book1.xlsx"));
            var excelPackage = new ExcelPackage(stream);
            excelPackage.Workbook.Worksheets.Add("test");
            excelPackage.Save();
            var s = stream.ToArray();
        }
        [TestMethod, Ignore]
        public void ColumnsTest()
        {
            var excelPackage = new ExcelPackage();
            var ws = excelPackage.Workbook.Worksheets.Add("ColumnTest");


            for (var c = 4; c <= 20; c += 4)
            {
                var col = ws.Column(c);
                col.ColumnMax = c + 3;
            }

            ws.Column(3).Hidden = true;
            ws.Column(6).Hidden = true;
            ws.Column(9).Hidden = true;
            ws.Column(15).Hidden = true;
            ws.Cells["a1:Z1"].Value = "Test";
            ws.Cells["a1:FF33"].AutoFitColumns(0);
            ws.Column(26).ColumnMax = ExcelPackage.MaxColumns;
            excelPackage.SaveAs(new FileInfo(@"c:\temp\autofit.xlsx"));
        }

        [TestMethod]
        public void Comment()
        {
            InitBase();
            var pck = new ExcelPackage();
            var ws1 = pck.Workbook.Worksheets.Add("Comment1");
            ws1.Cells[1, 1].AddComment("Testing", "test1");

            pck.SaveAs(new FileInfo(_worksheetPath + "comment.xlsx"));

            pck = new ExcelPackage(new FileInfo(_worksheetPath + "comment.xlsx"));
            var ws2 = pck.Workbook.Worksheets[1];
            ws2.Cells[1, 2].AddComment("Testing", "test1");
            pck.Save();
        }
        #endregion
    }
}