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

Solution 1

The problem is not solved but now I know exactly why. This "Table1" thing wasn't a named range but a table, which I can access through the "Tables" collection of the worksheet.

Now, the problem is that both the Tables' collection and Table objects in EPPlus are readonly so I can't define the table's dimension from my code, and neither can I remove it or add a new one to fit my needs. EPPlus's author has already mentionned that it might someday be implemented (here and here) bus as the messages are almost 3 years old, I guess there is little hope to see that happen...

Anyway, I hope this will help anyone encountering the same issue.

[EDIT] I finally came up with a way to bypass the problem : the ExcelTable object has a writable property called "TableXml" which contains the xml definition of the table with - of course - its range. Here's its content in my case :

<?xml version="1.0" encoding="UTF-8" standalone="true"?>
    <table dataCellStyle="Normal 2" headerRowCellStyle="Normal 2" headerRowDxfId="70" totalsRowShown="0" insertRow="1" ref="A1:U2" displayName="Table1" name="Table1" id="1" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
        <autoFilter ref="A1:U2"/>
        <tableColumns count="21">
            <tableColumn dataCellStyle="Normal 2" name="Activity" id="1"/>
            <tableColumn dataCellStyle="Normal 2" name="Category" id="21"/>
            [...]
            <tableColumn dataCellStyle="Normal 2" name="Closed Year" id="20" dataDxfId="62"/>
        </tableColumns>
        <tableStyleInfo name="TableStyleMedium9" showColumnStripes="0" showRowStripes="1" showLastColumn="0" showFirstColumn="0"/>
</table>

What interests us here are the "ref" attributes in the "table" and "autoFilter" nodes, as changing their values allows to redefine the range of our table.

I proceeded this way :

XmlDocument tabXml = sheet.Tables(0).TableXml;
XmlNode tableNode = tabXml.ChildNodes[1];
tableNode.Attributes["ref"].Value = string.Format("A1:U{0}", dt.Rows.Count + 1);
XmlNode autoFilterNode = tableNode.ChildNodes[0];
autoFilterNode.Attributes["ref"].Value = string.Format("A1:U{0}", dt.Rows.Count + 1);

And now my Excel file is properly generated with "Table1" fitting the actual range of my data !

Solution 2

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();

Solution 3

I spent about 4 hours solving this issue, As my problem & solution are not in the post, I am writing it for any future visitor,

My problem was caused by duplicate columns in excel sheet. After adding space to one column, the problem solved. The interesting part is, The error never came when i generated pivot table through MS excel, it only came when I used epplus to generate pivot table in excel file. Making the bug harder to find

Solution 4

I ran into this when I had a bug that added an extra column delimiter after each row:

head1{tab}head2{tab}
col11{tab}col21{tab}
col22{tab}col22{tab}

That extra tab after the last column broke the resulting Excel spreadsheet in this same way, and removing it fixed the problem. Note I'm using the LoadFromText to load the whole sheet in one go from text data. This may not be the OP's issue, but maybe future searchers will find this helpful.

Solution 5

Had this issue when editing workbooks with Tables that had special formatting (Windings font was used to show a special symbol) in their headers. Had to remove the formatting to fix the message.

View more solutions

Comments

[jmcnamara]

Defined names like Table1 are usually reserved for Excel tables (although it is possible to use names like that for non-table ranges). What happens if you call the range something like Foo1.

[ZipionLive]

I don't get the error but my pivot tables don't work anymore. I forgot to tell that the "Table1" named range already exists in the template. I'll edit my question right away...

[Hardwareguy]

Response.End() fixed my problem. Thanks for the answer.

[brechtvhb]

Solved my problem too!

[MindLoggedOut]

Thanks, that was great.

[nazia]

Solved my problem :)