.xlsx Created and saved using a template with EPPlus is unreadable / corrupt

Question

Problem

I am 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 seem to be corrupted no matter what I try to do.

My code is:

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 tried:

• Change the values ​​of different cells in a template.
• Saving an Excel document created from a template without writing any new data.
• Creating a basic template with cells A1, A2 and A3 containing "TEST" and no other changes, instead of the more complex template that I intend to use.
• Saving with Package.SaveAs() .
• Saving using the Byte array shown in the code example.
• Compilation of EPPlus from the latest source provided on Codeplex.

Things that work:

• Create 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 some reason, the saved files still look corrupted and cannot be restored. I am currently using Microsoft Office 2010. The file formats that I use are .xltx and .xlsx .

Answer 1

Short answer:

EPPlus does not support creating a file from an existing Excel xltx template.

Instead, use a pre-formatted xlsx file.

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 creating an instance of a new ExcelPackage object using EPPlus, you must provide the template as the second parameter (see the code snippet above), however, by providing it with the xltx file, I continued to receive the corrupt output file in the same way as the user above. In the end, I found that providing the regular xlsx file as a template, rather than the actual template file, seemed 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 of the CreateFromTemplate () method indicate that it expects the existing xlsx file to be used as a template, and not the actual template file.

So, although it can be assumed that the template parameter refers to the actual xltx template file, it seems that EPPlus does not support this.

Answer 2

A similar problem, it worked just returning the template to me

 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 { } 

Answer 3

Explicitly close the file after you write the data to it. I know when you use EPPlus in a response to a web request closing the response, removes this problem.