The generated file does not open in Excel

[b0bi79]

I am working on introducing Pivot styles. For testing tests are used PivotTests.Simple. When performing the specified test with the tPivot5_Static.xlsx template, a file is generated that does not open in Excel.

Commit #81 compiled with ClosedXML ClosedXML/ClosedXML#1189

[igitur]

When you say the file doesn't open in Excel, do you mean that Excel complains that the file is corrupt?

[b0bi79]

Yes

[b0bi79]

I noticed that the problem does not occur if the PivotTable data is empty. From this I can assume that there may be an error related to the pivotCacheDefinition section.

[b0bi79]

I found. In pivotCacheRecords1.xml, the "Tax rate" field contains no data. If I add data to this field, the error disappears.
It was:

<x:cacheField name="Tax rate">
	<x:sharedItems containsSemiMixedTypes="0" containsString="0" containsNumber="1" minValue="0" maxValue="8.5" count="3" />
</x:cacheField>

Correction:

<x:cacheField name="Tax rate">
	<x:sharedItems containsSemiMixedTypes="0" containsString="0" containsNumber="1" minValue="0" maxValue="8.5" count="3">
		<x:n v="4.5" />
		<x:n v="0" />
		<x:n v="8.5" />
	</x:sharedItems>
</x:cacheField>

The "Tax rate" field participates in the PivotTable as a ColumnField. Scenario to reproduce:

1. Open the file in which there is a PivotTable
2. Modify the data referenced by the PivotTable
3. Save the file.

[b0bi79]

In fact, there are two problems. This solves only one. Solution for the second problem until has not yet found.

[b0bi79]

Found the second problem, because of which the file opens with an error.
In the pivotTable1.xml file, the location element has the property ref = "B3", if I change this value to ref = "B3: L211", then the file is opened without errors.

[b0bi79]

The reason that in the pivotCacheDefinition the TaxRate field was not filled in was that the file was generated based on a template in which two summary tables are constructed from data from the same data table. In this case Excel creates one pivotCacheDefinition file, and ClosedXML while caches the last Pivot overwrites the cache for the first Pivot.

[b0bi79]

Closed by ClosedXML/ClosedXML#1196