Unreadable content in Excel file generated with EPPlus

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.

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

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

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 !