.xlsx Created and Saved Using Template with EPPlus is Unreadable/Corrupt

Question

The Problem

I'm trying to create a small program that uses an Excel template to create an Excel document and then writes to several cells using EPPlus. Unfortunately, the files appear to be corrupt no matter what I try.

My code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using OfficeOpenXml;
using System.IO;

namespace ConsoleApplication9
{
    public sealed class ExcelSerialize
    {
        private readonly List<Tuple<string, string>> Results;
        private readonly string Directory;
        private ExcelPackage package;

        public ExcelSerialize(List<Tuple<string, string>> Results, string Directory)
        {
            this.Results = Results;
            this.Directory = Directory;
        }

        public bool WriteResults()
        {
            FileInfo template = new FileInfo(Directory);

            using (package = new ExcelPackage(template, true))
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets[1];

                //foreach (Tuple<string, string> Result in Results)
                //{
                //    worksheet.Cells[Result.Item1].Value = Result.Item2;
                //}

                string file = string.Format(System.AppDomain.CurrentDomain.BaseDirectory.ToString() + @"results\results" + System.DateTime.Now.ToString().Replace(" ", "").Replace("/", "_").Replace(":", "-") + ".xlsx");

                Byte[] bin = package.GetAsByteArray();
                File.WriteAllBytes(file, bin);

                return true;
            }
        }
    }
}

Things I've tried:

• Changing the values of various cells in the template.
• Saving an Excel document created from the template without writing any new data to it.
• Creating a basic template with the cells A1, A2, and A3 containing "TEST" and no other edits instead of the more complicated template I intend to use.
• Saving using Package.SaveAs().
• Saving using the Byte array seen in the example code.
• Compiling EPPlus from the latest source provided on Codeplex.

Things that work:

• Creating a new file using the following code:

using (package = new ExcelPackage(string.Format(System.AppDomain.CurrentDomain.BaseDirectory.ToString() + @"results\results" + System.DateTime.Now.ToString().Replace(" ", "").Replace("/", "_").Replace(":", "-") + ".xlsx"))
{
    ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Test");
    worksheet.Cells[A1].Value = "Test";
    package.Save();
}

Notes:

For whatever reason, the files saved still appear corrupt and can't be recovered. I'm currently using Microsoft Office 2010. The file formats I'm using are .xltx and .xlsx.

Solution 1

Short Answer:

The EPPlus library does not support creation of a file from an existing xltx Excel template.

Use a pre-formatted xlsx file instead.

Long Answer:

Dim excelFile As New FileInfo(filename)
Dim reportTemplate As New FileInfo(templatePath)
Dim xlFile As ExcelPackage = New ExcelPackage(excelFile, reportTemplate)

' Do Stuff

xlFile.Save()

When instantiating a new ExcelPackage object using EPPlus you're supposed to be able to provide a template as the second parameter (see code snippet above), however when providing it with an xltx file I kept getting a corrupt output file same as the user above. I eventually found that supplying a regular xlsx file as the template, rather than an actual template file, appeared to work.

This is an open source package, so I decided to take a quick look at the source.

It looks like the ExcelPackage constructors that accept the "template" parameter, call the CreateFromTemplate() method. The comments on the CreateFromTemplate() method states that it expects an existing xlsx file to use as a template, not an actual template file.

So while one might assume that the 'template' parameter refers to an actual xltx template file, it appears that EPPlus does not support that.

Solution 2

Similar problem faced, this worked by just giving me back the template

public ExcelPackage getSheet(string templatePath){
FileInfo template = new FileInfo(templatePath);
ExcelPackage p = new ExcelPackage(template, true);
ExcelWorksheet ws = p.Workbook.Worksheets[1]; //position of the worksheet
ws.Name = bookName;
p.Save();
ExcelPackage pck = new ExcelPackage(new System.IO.MemoryStream(), p.Stream);
return pck;

Then you call with this

string path = Server.MapPath(@"../../../Ex/template.xlsx")
try
{
    OfficeOpenXml.ExcelPackage pck = getSheet(path);
    Response.Clear();
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    Response.AddHeader("content-disposition", String.Format(System.Globalization.CultureInfo.InvariantCulture, "attachment; filename={0}", fileName + ".xlsx"));
    Response.BinaryWrite(pck.GetAsByteArray());
    Response.End();
}
catch { }