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

Oct 11, 2013 at 5:45 PM

The Problem

I Posted this on Stack Overflow earlier. Thought I'd try here as well.

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:
</ br>
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.

Source: http://stackoverflow.com/q/19322625/2490143
Editor
Oct 11, 2013 at 6:14 PM
Edited Oct 11, 2013 at 6:14 PM
I will assume that will be a console app, so let´s do the basics... try this
   FileInfo tmpl = new FileInfo(@"c:\" + DateTime.Now.Ticks + ".xlsx");

            using (ExcelPackage pck = new ExcelPackage(tmpl))
            {
                var ws = pck.Workbook.Worksheets.Add("1");                      
                ws.Cells[2, 1].Value = "A";               
                pck.Save();
            }
If that works, try the same code with your template file passing by constructor.
Oct 13, 2013 at 4:58 PM
Dunno if you've read my post. I've tried the basics. I've simplified it down to manually creating a template in Microsoft Office Excel 2010 with nothing but the word "TEST" in two cells. I then open this, write test to a third cell using EPPlus, and save it. The new .xlsx file comes out corrupt. Creating a new file using your method and not using an existing file works, but templates appear completely broken.
Oct 17, 2013 at 6:55 PM
I was having the same problem. I was using .xltx as template and saving it as .xlsx. Now instead of taking .xltx file, I am taking blank .xlsx file with all the formatting ( style etc.) and after inserting the data in it, I am saving it as another .xlsx file. The original blank file(.xlsx) can be used as a template file. Hope this helps....
Aug 5, 2014 at 4:32 PM
When instantiating a new ExcelPackage object you're supposed to be able to provide a template as the second parameter, however when providing it with an xltx file I kept getting an exception. I eventually found that supplying a regular xlsx file as the template, rather than an actual template file, appeared to work.
Dim excelFile As New FileInfo(filename)
Dim reportTemplate As New FileInfo(templatePath)
Dim xlFile As ExcelPackage = New ExcelPackage(excelFile, reportTemplate)

' Do Stuff

xlFile.Save()
Bizarre behavior, but it worked.
Editor
Aug 5, 2014 at 7:59 PM
I'm pretty sure there is no xltx support, EPPlus only handles xlsx and xlsm files. Maybe there is a extension check in the code.
Aug 5, 2014 at 8:20 PM
Edited Aug 5, 2014 at 8:21 PM
You're probably right, but with little formal documentation, it's easy to assume that the template parameter means an actual template. That's what I did, and I got the same result: No exceptions on execution, but a corrupt and unusable output file.

The OP notes at the very end of his post that he was using an xltx file.

Taking a quick peek at the EPPlus 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.
Editor
Aug 5, 2014 at 8:35 PM
Edited Aug 5, 2014 at 8:36 PM
I think now I understand what this is about...I'm no advanced excel user =)

I didn't know there was a extension to excel template files (xltx). Maybe instead of calling "templateFile" the best is calling "existingFile", and make clear inside the docs that you can use an existing file as template and this file must be xlsx or xlsm.