In Sample13, LoadFromDataTable + AutoFilter = Unreadable Content Error

Jan 31, 2013 at 1:24 PM
In Sample13.cs, I added one line
        wsDt.Cells[wsDt.Dimension.Address].AutoFitColumns();
        wsDt.Cells[wsDt.Dimension.Address].AutoFilter = true; // this line added
When I run the SampleApp and then try to open Sample13.xlsx, I get a warning about unreadable content. Here is the recovery log:

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
  • <recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <logFileName>error100440_01.xml</logFileName>
    <summary>Errors were detected in file 'D:\EPPlus\SampleApp\bin\SampleApp\Sample13.xlsx'</summary>
  • <removedFeatures summary="Following is a list of removed features:">
    <removedFeature>Removed Feature: AutoFilter from /xl/tables/table1.xml part (Table)</removedFeature>
    <removedFeature>Removed Feature: Table from /xl/tables/table1.xml part (Table)</removedFeature>
    </removedFeatures>
    </recoveryLog>
I tried it with the LoadFromCollection worksheets, and they gave the error as well.

I then tried removing the LoadFromDataTable and replaced that with manually iterating across the columns and rows to directly set cell values from the data table and then add the AutoFilter (similar to what Sample1 does), and that worked!

In playing with this, I noticed that LoadFromDataTable does result in autofiltering being enabled; that is, the column headers have the dropdown on them, whereas in the manual approach, that only happens if I set AutoFilter = true.

So, I tried setting AutoFilter = false with LoadFromDataTable, and that also gives me the unreadable content error.

Looking through the source, it looks like LoadFromDataTable basically does what I did, but then goes ahead and creates an ExcelTable from the populated addresses, whereas the manual approach does not have that ExcelTable in the mix.

So performance-wise, looks like it should be a wash and I can just use the manual approach. But it does seem like a bug.
Feb 1, 2013 at 2:45 AM
Edited Feb 1, 2013 at 3:03 AM
googles what Autofilter does.. I think you can achieve what Autofilter does with a TableStyle. However when using the .LoadFromDataTable() method and specifying a Tablestyle is bugged and can produce a corrupted Excel package file. See discussion http://epplus.codeplex.com/discussions/349770. It shows how to create a TableStyle seperate from the .LoadFromDataTable method that has a Autofilter ability on the column headers.