Error dialog displayed when opening an excel file generated with EPPlus

Error dialog displayed when opening an excel file generated with EPPlus

I am creating an Excel file using the EPPlus library. When I create file and open up the file, the following pop up message shows:

We found a problem with some content in 'ExcelDemo.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, Click Yes

I am using following code

using (ExcelPackage pck = new ExcelPackage())
{
    //Create the worksheet
    ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Demo");

    //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
    ws.Cells[1, 2].Value = "Excel Download";

    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    Response.AddHeader("content-disposition", "attachment;  filename=ExcelDemo.xlsx");
    Response.BinaryWrite(pck.GetAsByteArray());
}

Is there problem in my code or is this an Excel issue?

In my case the problem was in calling

package.Save();

and using

Response.BinaryWrite(package.GetAsByteArray());

at the same time.

When you call package.GetAsByteArray() it perfoms following operations internally:

this.Workbook.Save();
this._package.Close();
this._package.Save(this._stream);

So, calling package.Save two times leads to this error when opening in Excel.

At the start, you need to add in a:

Response.Clear();

Then at the end add a

Response.End();

I will share my solution. I am using a template excel file and then create new excel from it.

Receiving the same error. My code was

 using (Stream newFileStream = File.Open(this.tempFilePath, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite))
 using (Stream originalFile = File.Open(this.initialFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
 using (ExcelPackage excelPackage = new ExcelPackage(newFile, template))
 {
      // ... Do work here
 }

I had to change code to:

FileInfo intialInfo = new FileInfo(this.initialFilePath);
FileInfo tempFileInfo = new FileInfo(this.tempFilePath);
using (ExcelPackage excelPackage = new ExcelPackage(tempFileInfo, intialInfo))
{
     //... Do work here
}

Also I am using ASP MVC and the response is:

byte[] result = exporter.GetBytesFromGeneratedExcel();
return this.File(result, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Test.xlsx");

In my case, problem was data table name which I did not set earlier.

Try this one,

dt.TableName = "ExcelSheet1";

I was having this problem because I was writing a string larger than 32,767 characters into a single cell. Excel doesn't like this, but EPPlus won't stop you from doing it.

My code was updated... and was getting the same error.... and I finally found my solution

Original Code:

public static void ExportToExcel(HttpContext ctx, DataTable tbl, string fileName)
        {
            try
            {
                using (ExcelPackage pck = new ExcelPackage())
                {
                    //Create the worksheet
                    ExcelWorksheet ws = pck.Workbook.Worksheets.Add(fileName);

                    //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
                    ws.Cells["A1"].LoadFromDataTable(tbl, true);

                    int rowCount = tbl.Rows.Count;
                    List<int> dateColumns = new List<int>();
                    foreach (DataColumn d in tbl.Columns)
                    {
                        if (d.DataType == typeof(DateTime))
                            dateColumns.Add(d.Ordinal + 1);
                    }

                    CultureInfo info = new CultureInfo(ctx.Session["Language"].ToString());

                    foreach (int dc in dateColumns)
                        ws.Cells[2, dc, rowCount + 1, dc].Style.Numberformat.Format = info.DateTimeFormat.ShortDatePattern;

                    //Write it back to the client
                    ctx.Response.Clear();
                    ctx.Response.AddHeader("content-disposition", "attachment;  filename=" + fileName + ".xlsx");
                    ctx.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    ctx.Response.Buffer = false;
                    ctx.Response.BufferOutput = false;
                    ctx.Response.BinaryWrite(pck.GetAsByteArray());
                    ctx.Response.End();
                }
            }
            catch (Exception EX)
            {
                ctx.Response.Write(EX.ToString());
            }
        }

Code update:

catch (Exception EX)
{
    if (!(EX is System.Threading.ThreadAbortException))
    {
        ctx.Response.Write(EX.ToString());
    }
}

IT WORKED!

A solution I came up with was just returning the file object in the controller. The first argument should be the byte array (file), the second argument is the content type, and the last argument is the filename (in my case "report.xlsx").

return File(file, "application/octet-stream", fileName);

Hope this helped.

ReferenceURL : https://stackoverflow.com/questions/25750616/error-dialog-displayed-when-opening-an-excel-file-generated-with-epplus