EPPlus with a template is not working as expected

Question

I am currently using EPPlus project in order to manipulate some .xlsx files. The basic idea is that I have to create a new file from a given template.

But when I create the new file from a template, all calculated columns in the tables are messed up.

The code I am using is the following:

static void Main(string[] args)
{
  const string templatePath = "template_worksheet.xlsx"; // the path of the template
  const string resultPath = "result.xlsx"; // the path of our result

  using (var pck = new ExcelPackage(new FileInfo(resultPath), new FileInfo(templatePath))) // creating a package with the given template, and our result as the new stream
  {
    // note that I am not doing any work ...
    pck.Save(); // savin our work
  }
}

For example for a .xlsx file (that have a table with 3 columns, the last one is just the sum of the others) the program creates a .xlsx file where the last column have the same value (which is correct only for the first row) in all rows.

The following images shows the result:

Now the questions are: What is going on here ? Is my code wrong ? How can I accomplish this task without that unexpected behavior ?

Answer 1

Try this

var package = new ExcelPackage(excelFile)
var excelSheet = package.Workbook.Worksheets[1];
for (var i = 1; i < 5; i++){
    excelWorkSheet.InsertRow(i, 1, 1); // Use value of i or whatever is suitable for you
}
package.Workbook.Calculate(); 

Inserting new row copies previous row format and its formula if last prm is set to 1

Answer 2

That definitely on to something there. I was able to reproduce it myself. It has to do with the Table you created. if you open your file and remove it using the "Convert To Range" option in the Table Tools tab the problem goes away.

I looked at the source code and it extracts the xml files at the zip level and didnt see any indication that it was actually messing with them - seemed to be a straight copy.

Very strange because if we create and save the xlsx file including a table from EPPlus the problem is not there. This works just fine:

[TestMethod]
public void Template_Copy_Test()
{
    //http://stackoverflow.com/questions/28722945/epplus-with-a-template-is-not-working-as-expected
    const string templatePath = "c:\\temp\\testtemplate.xlsx"; // the path of the template
    const string resultPath = "c:\\temp\\result.xlsx"; // the path of our result

    //Throw in some data
    var dtdata = new DataTable("tblData");
    dtdata.Columns.Add(new DataColumn("Col1", typeof(string)));
    dtdata.Columns.Add(new DataColumn("Col2", typeof(int)));
    dtdata.Columns.Add(new DataColumn("Col3", typeof(int)));

    for (var i = 0; i < 20; i++)
    {
        var row = dtdata.NewRow();
        row["Col1"] = "String Data " + i;
        row["Col2"] = i * 10;
        row["Col3"] = i * 100;
        dtdata.Rows.Add(row);
    }

    var templateFile = new FileInfo(templatePath);
    if (templateFile.Exists)
        templateFile.Delete();

    using (var pck = new ExcelPackage(templateFile))
    {
        var ws = pck.Workbook.Worksheets.Add("Data");
        ws.Cells["A1"].LoadFromDataTable(dtdata, true);

        for (var i = 2; i <= dtdata.Rows.Count + 1; i++)
            ws.Cells[i, 4].Formula = String.Format("{0}*{1}", ExcelCellBase.GetAddress(i, 2), ExcelCellBase.GetAddress(i, 3));

        ws.Tables.Add(ws.Cells[1, 1, dtdata.Rows.Count + 1, 4], "TestTable");

        pck.Save();
    }

    using (var pck = new ExcelPackage(new FileInfo(resultPath), templateFile)) // creating a package with the given template, and our result as the new stream
    {
        // note that I am not doing any work ...
        pck.Save(); // savin our work
    }
}

BUT.....

If we open testtemplate.xlsx, remove the table, save/close the file, reopen, and reinsert the exact same table the problem shows up when you run this:

[TestMethod]
public void Template_Copy_Test2()
{
    //http://stackoverflow.com/questions/28722945/epplus-with-a-template-is-not-working-as-expected
    const string templatePath = "c:\\temp\\testtemplate.xlsx"; // the path of the template
    const string resultPath = "c:\\temp\\result.xlsx"; // the path of our result

    var templateFile = new FileInfo(templatePath);

    using (var pck = new ExcelPackage(new FileInfo(resultPath), templateFile)) // creating a package with the given template, and our result as the new stream
    {
        // note that I am not doing any work ...
        pck.Save(); // savin our work
    }
}

It has to be something burried in their zip copy methods but I nothing jumped out at me.

But at least you can see about working around it.

Ernie

Answer 3

Try to use the following code. This code takes the formatting and other rules and add them as xml node to another file. Ernie described it really well here Importing excel file with all the conditional formatting rules to epplus The best part of the solution is that you can also import formatting along with your other rules. It should take you close to what you need.

//File with your rules, can be your template
var existingFile = new FileInfo(@"c:\temp\temp.xlsx");

//Other file where you want the rules
var existingFile2 = new FileInfo(@"c:\temp\temp2.xlsx");

using (var package = new ExcelPackage(existingFile))
using (var package2 = new ExcelPackage(existingFile2))
{
    //Make sure there are document element for the source
    var worksheet = package.Workbook.Worksheets.First();
    var xdoc = worksheet.WorksheetXml;

    if (xdoc.DocumentElement == null)
        return;

    //Make sure there are document element for the destination
    var worksheet2 = package2.Workbook.Worksheets.First();
    var xdoc2 = worksheet2.WorksheetXml;

    if (xdoc2.DocumentElement == null)
        return;

    //get the extension list node 'extLst' from the ws with the formatting 
    var extensionlistnode = xdoc
        .DocumentElement
        .GetElementsByTagName("extLst")[0];

    //Create the import node and append it to the end of the xml document
    var newnode = xdoc2.ImportNode(extensionlistnode, true);
    xdoc2.LastChild.AppendChild(newnode);

    package2.Save();

}
}