CopyIssues.cs

using Microsoft.VisualStudio.TestTools.UnitTesting;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EPPlusTest.Issues
{
    [TestClass]
    public class CopyIssues : TestBase
    {
        [TestMethod]
        public void Issue1332()
        {
            // the error in this issue was that the intersect operator (SPACE)
            // was replaced with "isc" when a formulas was copied to a new destination
            using var package = new ExcelPackage();
            var sheet = package.Workbook.Worksheets.Add("Sheet1");
            sheet.Cells["A1"].Formula = "SUBTOTAL(109, _DATA _Quantity)";
            sheet.Cells["A1"].Copy(sheet.Cells["B1"]);
            Assert.AreEqual("SUBTOTAL(109,_DATA _Quantity)", sheet.Cells["B1"].Formula);
        }
        [TestMethod]
        public void s651()
        {
            using (var p = OpenTemplatePackage("s651.xlsx"))
            {
                using (var p2 = OpenPackage("s651-save.xlsx", true))
                {
                    ExcelWorksheet wOutsheet = p2.Workbook.Worksheets.Add("MergeSheet");
                    wOutsheet.Cells.Style.Font.Name = "ＭＳ Ｐゴシック";
                    wOutsheet.Cells.Style.Font.Size = 9;

                    var wAnswerSheet = p.Workbook.Worksheets["Answer Sheet"];

                    ExcelRange wAnswerCopyHeaderRange = wAnswerSheet.Cells[1, 1, 42, 39];
                    ExcelRange wOutHeaderRange = wOutsheet.Cells[1, 1, 42, 39];
                    wAnswerCopyHeaderRange.Copy(wOutHeaderRange);

                    p2.Workbook.Worksheets["MergeSheet"].Name = "Data Sheet";
                    //p2.Workbook.Calculate();
                    SaveAndCleanup(p2);
                }
            }
		}
		[TestMethod]
		public void s651_2()
		{
			using (var p = OpenTemplatePackage("s651-2.xlsx"))
			{
				using (var p2 = OpenPackage("s651-2-save.xlsx", true))
				{
					ExcelWorksheet wOutsheet = p2.Workbook.Worksheets.Add("MergeSheet");
					var ws = p.Workbook.Worksheets[0];

					ws.Cells["A1:B8"].Copy(wOutsheet.Cells["A1"]);
					wOutsheet.Calculate();
					SaveAndCleanup(p2);
				}
			}
		}
        [TestMethod]
        public void i1623()
        {
            using (var p = OpenTemplatePackage("i1623.xlsx"))
            {
                using (var p2 = OpenPackage("i1623-clone.xlsx", true))
                {
                    var src = p.Workbook.Worksheets.First();
                    ExcelWorksheet dup = p2.Workbook.Worksheets.Add("dup", src);
                    for (int col = 1; col < src.Dimension.End.Column; col++)
                    {
                        Console.WriteLine($"is equal? :{src.Cells[5, col].FormulaR1C1.Equals(dup.Cells[5, col].FormulaR1C1)},{src.Cells[5, col].FormulaR1C1} => {dup.Cells[5, col].FormulaR1C1}");
                    }
                    for (int col = 1; col < src.Dimension.End.Column; col++)
                    {
                        Assert.AreEqual(src.Cells[5, col].FormulaR1C1, dup.Cells[5, col].FormulaR1C1);
                    }
                    SaveAndCleanup(p2);
                }
            }
        }
        [TestMethod]
        public void i1645()
        {
            using (var package = OpenTemplatePackage("i1645.xlsx"))
            {
                var syncSht = package.Workbook.Worksheets["syncSht"];
                var snapSht = package.Workbook.Worksheets["snapSht"];
                var address = "B7:K16";
                snapSht.Cells[address].Copy(syncSht.Cells[address]);
                SaveAndCleanup(package);
            }
        }
        [TestMethod]
        public void i1945()
        {
            using (var p = OpenPackage("i1945.xlsx", true))
            {
                var ws=p.Workbook.Worksheets.Add("Sheet1");
                var comment = ws.Cells["A1"].AddComment("Test"); // Range1 = A1
                ws.Comments.Remove(comment);
                ws.Cells["A1"].AddComment("Test");
                ws.Cells["A1"].Copy(ws.Cells["A2"]);
            }
        }
        [TestMethod]
        public void i1656PasteSpecial_Formulas()
        {
            using (var p = OpenPackage("i1656.xlsx", true))
            {
                var ws = p.Workbook.Worksheets.Add("Sheet1");
                var srcCell = ws.Cells["D3"];
                var comment = srcCell.AddComment("Test");

                var testFormula = "ROW()+COLUMN()";
                srcCell.Formula = testFormula;

                ws.Calculate();

                var destCell = ws.Cells["F5"];
                srcCell.Copy(destCell, ExcelRangeCopyOnly.Formulas);

                Assert.AreEqual(testFormula, destCell.Formula);
                Assert.AreEqual(7d, destCell.Value);

                ws.Calculate();

                SaveAndCleanup(p);
            }
        }

        [TestMethod]
        public void i2350()
        {
            using (var p =OpenTemplatePackage("i2350.xlsx"))
            {
                var ws = p.Workbook.Worksheets[0];

                using(var p2 = OpenPackage("i2350Output.xlsx", true))
                {
                    p2.Workbook.Worksheets.Add("test", ws);
                    SaveAndCleanup(p2);
                }
            }
        }

        //One cell/range can be copied to multiple comma-separated ranges
        [TestMethod]
        public void i1656PasteSpecial_Formulas_OneToMany()
        {
            using (var p = OpenPackage("i1656_Many.xlsx", true))
            {
                var ws = p.Workbook.Worksheets.Add("Sheet1");
                var srcCell = ws.Cells["D3"];
                var comment = srcCell.AddComment("Test");

                var testFormula = "ROW()+COLUMN()";
                srcCell.Formula = testFormula;

                ws.Calculate();

                var destRangeDiscontinous = ws.Cells["J15:J20,G11:G20"];
                srcCell.Copy(destRangeDiscontinous, (ExcelRangeCopyOptionFlags)ExcelRangeCopyOnly.Formulas, ExcelRangeCopyOptionFlags.Fill);

                Assert.AreEqual(testFormula, ws.Cells["J15:J20"].Formula);
                Assert.AreEqual(testFormula, ws.Cells["G11:G20"].Formula);

                ws.Calculate();

                SaveAndCleanup(p);
            }
        }
    }
}