Unreadable content in Excel file generated with EPPlus

Question

I'm having a little problem when I generate an Excel file from a template, using the EPPlus library. The file has a first spreadsheet that contains data that is used for populating pivot tables in the following sheets.

When I open the generated file, I get the following error message : "Excel found unreadable content in 'sampleFromTemplate.xlsx'. Do you want to recover the contents of this workbook ? I you trust the source of this workbook, click Yes."

I obviously click yes, then get a summary of repairs done to the file, and a link to an xml formatted log file containing this :

<?xml version="1.0" encoding="UTF-8" standalone="true"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <logFileName>error095080_01.xml</logFileName>
    <summary>Errors were detected in file  'C:\TEMP\sampleFromTemplate.xlsx'</summary>
    <repairedRecords summary="Following is a list of repairs:">
        <repairedRecord>Repaired Records: Table from /xl/tables/table1.xml part (Table)</repairedRecord>
    </repairedRecords>
</recoveryLog>

This is apparently caused by a named range ("Table1") that I define in my code to indicate the data to be used for the pivot tables. There already is a "Table Name" in the template called "Table1", but I can't seem to access it through the ExcelPackage.Worksheet.Names collection. Being new to EPPlus and not very experimented with Excel, I don't understand where I'm doing wrong. Here's the bit of code where I generate the file :

private string GenerateFromTemplate(string fileName, string templateName, DataTable tab)
{
    FileInfo newFile = new FileInfo(string.Format("C:\\MyPath\\{0}.xlsx", fileName));
    FileInfo templateFile = new FileInfo(string.Format("C:\\MyPath\\{0}.xlsx", templateName));

    try
    {
        using (ExcelPackage pkg = new ExcelPackage(newFile, templateFile))
        {
            ExcelWorksheet sheet = pkg.Workbook.Worksheets["MyDataSheet"];
            ExcelRange range = sheet.Cells[string.Format("A1:U{0}", dt.Rows.Count)];
            pkg.Workbook.Names.Add("Table1", range as ExcelRangeBase);

            int sheetRowIndex = 2;

            foreach (DataRow row in this.dt.Rows)
            {
                sheet.Cells[sheetRowIndex, 1].Value = row["Row1"];
                sheet.Cells[sheetRowIndex, 2].Value = row["Row2"];
                [...]
                sheet.Cells[sheetRowIndex, 21].Value = row["Row21"];

                sheetRowIndex++;
            }

            pkg.Save();
            return newFile.FullName;
        }
    }
    catch (IOException ex) { return ex.Message; }
}

Note that the pivot tables are populated correctly anyway, so why is this happening ?

Thanks :-)

Answer 1

I just ran into this problem myself and fixed it, putting my solution here should someone else run into it:

This was using asp.net, for obvious reasons it's not applicable otherwise.

My problem wasn't the table range, Epplus generated the file just fine, but rather that the server response was appending the page response to the excel file, obviously making the file invalid. Ending the server response immediately after sending the file fixed my problem, something to the tune of:

Response.BinaryWrite(pck.GetAsByteArray());  // send the file
Response.End();