Packages with pivot tables can't be reopened and saved 4.5.1 - 4.5.3.2

[jamesSampica]

When adding, saving, reopening, and then saving packages with pivot tables Excel 2016 complains that there was unreadable content for the pivot table definitions.

The code itself does not throw any errors. Only when opening the file does the problem occur.

I created a test based on Sample12 highlighting the problem. Here is the output excel.zip

The file opens successfully and pivot tables are generated correctly in versions <=4.5.0.3rc but fail in versions >=4.5.1

[TestClass]
public class PivotTest
{
    [TestMethod]
    public void TestPivot()
    {
        var list = GetRandomData();

        byte[] bytes;
        using (ExcelPackage pck = new ExcelPackage())
        {
            // get the handle to the existing worksheet
            var wsData = pck.Workbook.Worksheets.Add("SalesData");

            var dataRange = wsData.Cells["A1"].LoadFromCollection(list, true, OfficeOpenXml.Table.TableStyles.Medium2);

            dataRange.AutoFitColumns();

            var wsPivot = pck.Workbook.Worksheets.Add("PivotSimple");
            var pivotTable1 = wsPivot.PivotTables.Add(wsPivot.Cells["A1"], dataRange, "PerEmploee");

            pivotTable1.RowFields.Add(pivotTable1.Fields[0]);
            var dataField = pivotTable1.DataFields.Add(pivotTable1.Fields[1]);

            //attempt 1
            //bytes = pck.GetAsByteArray();

            //attempt 2
            pck.SaveAs(new FileInfo($"C:\\temp\\testPivot_original_package.xlsx"));
        }

        //attempt 1
        //using (var memStream = new MemoryStream(bytes))
        //{
        //    using (var pck = new ExcelPackage(memStream))
        //    {
        //        pck.SaveAs(new FileInfo($"C:\\temp\\testPivot_resaved_package.xlsx"));
        //    }
        //}

        //attempt 2
        using (var pck = new ExcelPackage(new FileInfo($"C:\\temp\\testPivot_original_package.xlsx")))
        {
            pck.SaveAs(new FileInfo($"C:\\temp\\testPivot_resaved_package.xlsx"));
        }
    }

    public class SalesDTO
    {
        public string Name { get; set; }
        public decimal Total { get; set; }
    }

    private static List<SalesDTO> GetRandomData()
    {
        List<SalesDTO> ret = new List<SalesDTO>();
        var firstNames = new string[] { "John", "Gunnar", "Karl", "Alice" };
        var lastNames = new string[] { "Smith", "Johansson", "Lindeman" };
        Random r = new Random();
        for (int i = 0; i < 500; i++)
        {
            ret.Add(
                new SalesDTO()
                {
                    Name = firstNames[r.Next(4)] + " " + lastNames[r.Next(3)],
                    Total = r.Next(100, 10000)
                });
        }
        return ret;
    }
 }

[coxymla]

This just bit me also. We don't even use EPPlus to control any of the pivot tables, we merely open a template workbook and use EPPlus to write the raw data into it.

Thanks for the tip about downgrading to 4.5.0.3-rc (N.B. one will need to check the Include prerelease checkbox in NuGet.)

[coxymla]

I tried to reproduce this issue with a minimal document with a basic pivot table but that actually worked OK.
I can send through a cut-down version of my template workbook that does cause this problem if that will help.

[jamesSampica]

The unit test I provided is fairly simple. Is there something you can find in the api that triggers this problem?

[tretom2121]

I'm experiencing this problem also with EPPlus version 4.0.5.0

All I do is read the file into FileInfo, create a package, and save.
With no exception or anything.
Then by opening the file in Excel, it says:
It found a problem, and it might try to recover the file.
The result is a file with some removed parts:

Removed Part: /xl/pivotTables/pivotTable1.xml part. (PivotTable view)
Removed Feature: PivotTable report from /xl/pivotCache/pivotCacheDefinition1.xml part (PivotTable cache)
Removed Records: Workbook properties from /xl/workbook.xml part (Workbook)
Removed Records: Sparklines from /xl/slicerCaches/slicerCache1.xml part (Slicer Cache)
Removed Records: Sparklines from /xl/slicerCaches/slicerCache2.xml part (Slicer Cache)
Removed Records: Sparklines from /xl/slicerCaches/slicerCache3.xml part (Slicer Cache)
Removed Records: Slicer Cache from /xl/slicers/slicer1.xml part (Slicer)