CODEWITHSUNDEEP
c#excelepplus
DanteTheEgregore
DanteTheEgregore

Reputation: 1540

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

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:

Things that work:

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.

Upvotes: 7

Views: 12562

Answers (3)

Tonto
Tonto

Reputation: 3102

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

Upvotes: 1

eXceLon
eXceLon

Reputation: 176

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.

Upvotes: 11

Cjconry
Cjconry

Reputation: 58

Explicitly close the File after you write the data to it. I know when you use EPPlus in a response on a web request closing the response removes that issue.

Upvotes: 0

Related Questions