How to copy ExcelWorkSheet from template with new CodeModule

Question

I had a problem with copying ExcelWorkSheetand the corresponding CodeModulecopied sheet. Let me explain:

Scenario: I have an excel file with a macro (.xlsm) and I use this excel file as a template to create another excel file (the data will be copied to a new file).

So, first I take some data from the database and open the Excel ( .xlsm) file .

At some point, some entries should be in different sheets, this is where "WorkSheets.Add ()" is included.

 var newSheet = workbook.Worksheets.Add("someName", templateSheet);

After copying the template sheet (which contains the VBA code, which I also want to duplicate), I am having problems.

It seems that the "CodeModule" of the sheet just created is equal to one of the template by name AND by reference.

I.e; all property values ​​are the same and: workbook.VbaProject.Modulesonly contains the source modules of the template file code, and not new for the new one newSheet.

Even worse, if I want to link the new one CodeModuleas follows:

workbook.VbaProject.Modules.AddModule("test");
newSheet.CodeModule.Name = "test";

both options newSheet.CodeModuleand templateSheet.CodeModulemounted on null(well, Nothingactually, because I'm using VB.Net).

So the question is: is this a mistake, or am I doing something wrong here? And even better: can you steer a path to execute this scenario?

Answer 1

This seems to be a mistake. I created a problem in the center of the problems of the project epplus. It has been marked asresolved

https://epplus.codeplex.com/workitem/15095

. , .

.

Answer 2

, -. - EPP 3.1. vba xlsm , XML, bin, . , , , EPPlus . unit test ( , , EPP):

[TestMethod]
public void VBAWorksheetCopyTest()
{
    var sb = new StringBuilder();
    sb.AppendLine("Private Sub Test()");
    sb.AppendLine("    Range(\"G10\").Value = \"TEST\"");
    sb.AppendLine("End Sub");

    var existingFile = new FileInfo(@"c:\temp\temp1.xlsm");
    if (existingFile.Exists)
        existingFile.Delete();

    using (var package = new ExcelPackage(existingFile))
    {
        var workbook = package.Workbook;
        workbook.CreateVBAProject();

        var worksheet = workbook.Worksheets.Add("templateSheet");

        //Module saved in the workSHEET
        worksheet.CodeModule.Code = sb.ToString();
        worksheet.CodeModule.Name = "templateSheet";

        worksheet.Cells["A1"].Value = "Col1";
        worksheet.Cells["A2"].Value = "sdf";
        worksheet.Cells["A3"].Value = "wer";

        package.Save();
    }

    //Open temp1.xlsm and copy the sheet
    using (var package = new ExcelPackage(existingFile))
    {
        var workbook = package.Workbook;
        var templateSheet = workbook.Worksheets["templateSheet"];
        var someName = workbook.Worksheets.Add("someName", templateSheet);

        //VBA code does seem to copy but dose NOT match what is seen in excel
        Assert.IsTrue(templateSheet.CodeModule.Code.Length > 0);
        Assert.IsTrue(someName.CodeModule.Code.Length > 0);

        package.Save();
    }

    //Open temp1 and try to name the modules
    using (var package = new ExcelPackage(existingFile))
    {
        var workbook = package.Workbook;
        var templateSheet = workbook.Worksheets["templateSheet"];
        var someName = workbook.Worksheets["someName"];

        //Give it a name otherwise Excel autonames it 'newsheet1'
        someName.CodeModule.Name = "someName"; //BUT will cause both CodeModule objects to go null

        //These will now fail becuase codemodule is now null for both
        Assert.IsTrue(templateSheet.CodeModule.Code.Length > 0);
        Assert.IsTrue(someName.CodeModule.Code.Length > 0);
    }
}