diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index bb05e45..6e9dddf 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -472,60 +472,70 @@ pt.ErrorValueReplacement = pivotTableDefinition.ErrorCaption.Value; // Row labels - foreach (var rf in pivotTableDefinition.RowFields.Cast()) + if (pivotTableDefinition.RowFields != null) { - if (rf.Index.Value == -2) - pt.RowLabels.Add(XLConstants.PivotTableValuesSentinalLabel); - else if (rf.Index < pivotTableDefinition.PivotFields.Count) + foreach (var rf in pivotTableDefinition.RowFields.Cast()) { - var pf = pivotTableDefinition.PivotFields.ElementAt(rf.Index.Value) as PivotField; - if (pf != null && pf.Name != null) pt.RowLabels.Add(pf.Name.Value); + if (rf.Index.Value == -2) + pt.RowLabels.Add(XLConstants.PivotTableValuesSentinalLabel); + else if (rf.Index < pivotTableDefinition.PivotFields.Count) + { + var pf = pivotTableDefinition.PivotFields.ElementAt(rf.Index.Value) as PivotField; + if (pf != null && pf.Name != null) pt.RowLabels.Add(pf.Name.Value); + } } } // Column labels - foreach (var cf in pivotTableDefinition.ColumnFields.Cast()) + if (pivotTableDefinition.ColumnFields != null) { - if (cf.Index.Value == -2) - pt.ColumnLabels.Add(XLConstants.PivotTableValuesSentinalLabel); - - else if (cf.Index < pivotTableDefinition.PivotFields.Count) + foreach (var cf in pivotTableDefinition.ColumnFields.Cast()) { - var pf = pivotTableDefinition.PivotFields.ElementAt(cf.Index.Value) as PivotField; - if (pf != null && pf.Name != null) pt.ColumnLabels.Add(pf.Name.Value); + if (cf.Index.Value == -2) + pt.ColumnLabels.Add(XLConstants.PivotTableValuesSentinalLabel); + + else if (cf.Index < pivotTableDefinition.PivotFields.Count) + { + var pf = pivotTableDefinition.PivotFields.ElementAt(cf.Index.Value) as PivotField; + if (pf != null && pf.Name != null) pt.ColumnLabels.Add(pf.Name.Value); + } } } // Values - foreach (var df in pivotTableDefinition.DataFields.Cast()) + if (pivotTableDefinition.DataFields != null) { - if ((int)df.Field.Value == -2) - pt.Values.Add(XLConstants.PivotTableValuesSentinalLabel); - - else if (df.Field.Value < pivotTableDefinition.PivotFields.Count) + foreach (var df in pivotTableDefinition.DataFields.Cast()) { - var pf = pivotTableDefinition.PivotFields.ElementAt((int)df.Field.Value) as PivotField; - if (pf != null && pf.Name != null) + if ((int)df.Field.Value == -2) + pt.Values.Add(XLConstants.PivotTableValuesSentinalLabel); + + else if (df.Field.Value < pivotTableDefinition.PivotFields.Count) { - var pv = pt.Values.Add(pf.Name.Value, df.Name.Value); - if (df.NumberFormatId != null) pv.NumberFormat.SetNumberFormatId((int)df.NumberFormatId.Value); - if (df.Subtotal != null) pv = pv.SetSummaryFormula(df.Subtotal.Value.ToClosedXml()); - if (df.ShowDataAs != null) + var pf = pivotTableDefinition.PivotFields.ElementAt((int)df.Field.Value) as PivotField; + if (pf != null && pf.Name != null) { - var calculation = pv.Calculation; - calculation = df.ShowDataAs.Value.ToClosedXml(); - pv = pv.SetCalculation(calculation); - } - if (df.BaseField != null) { - var col = pt.SourceRange.Column(df.BaseField.Value + 1); + var pv = pt.Values.Add(pf.Name.Value, df.Name.Value); + if (df.NumberFormatId != null) pv.NumberFormat.SetNumberFormatId((int)df.NumberFormatId.Value); + if (df.Subtotal != null) pv = pv.SetSummaryFormula(df.Subtotal.Value.ToClosedXml()); + if (df.ShowDataAs != null) + { + var calculation = pv.Calculation; + calculation = df.ShowDataAs.Value.ToClosedXml(); + pv = pv.SetCalculation(calculation); + } + if (df.BaseField != null) + { + var col = pt.SourceRange.Column(df.BaseField.Value + 1); - var items = col.CellsUsed() - .Select(c => c.Value) - .Skip(1) // Skip header column - .Distinct().ToList(); + var items = col.CellsUsed() + .Select(c => c.Value) + .Skip(1) // Skip header column + .Distinct().ToList(); - pv.BaseField = col.FirstCell().GetValue(); - if (df.BaseItem != null) pv.BaseItem = items[(int)df.BaseItem.Value].ToString(); + pv.BaseField = col.FirstCell().GetValue(); + if (df.BaseItem != null) pv.BaseItem = items[(int)df.BaseItem.Value].ToString(); + } } } } @@ -536,7 +546,6 @@ } #endregion - } #region Comment Helpers