title: Creating pivot table using ClosedXML
body: <p>I'm trying to create a Pivot table using ClosedXML V0.91.1, but I keep getting problems with my excel file having unreadable content and then the excel workbook removing my pivot table when clicking <code>Yes</code> below.</p>
  
  <p><a href="https://i.stack.imgur.com/qMSOw.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/qMSOw.png" alt="Error when opening excel file"></a></p>
  
  <p>Below is that shows when I hit <code>Yes</code>. It's removing my pivot table.</p>
  
  <p><a href="https://i.stack.imgur.com/sQxlO.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/sQxlO.png" alt="Excel removing unreadable content"></a></p>
  
  <p>My Pivot Table is getting data from a table that is created from a history of TFS <a href="https://docs.microsoft.com/en-us/previous-versions/visualstudio/visual-studio-2013/bb170151(v=vs.120)" rel="nofollow noreferrer">Changesets</a>. The changesets are set into a <code>IEnumerable&lt;Changeset&gt;</code> which is then converted into a <code>DataTable</code> object which include the column headings.</p>
  
  <p>A table is then created from the <code>DataTable</code> which is the source of the <code>PivotTable</code>. This is the code that I'm using:</p>
  
  <pre><code>public bool CreateReport(IEnumerable&lt;Changeset&gt; changesets)
  {
      workbook = new XLWorkbook();
      var sumSheet= workbook.Worksheets.Add("Summary");
      // Converting IEnumerable&lt;Changeset&gt; into DataTable
      DataTable changesetTable = ConvertToDataTable(changesets);
      // Table
      var sumTable = sumSheet.Cell(1, 1).InsertTable(changesetTable.AsEnumerable(), "SummaryTable", true);
      // Table - Formatting table
      tableWithData.Column("A").Cells(firstRow, lastRow).DataType = XLDataType.Number;
      tableWithData.Column("C").Cells(firstRow, lastRow).DataType = XLDataType.DateTime;
      tableWithData.Column("C").Cells(firstRow, lastRow).Style.DateFormat.Format = "d-MMM-yyy";
  
      sumSheet.Columns().AdjustToContents();
  
      // Pivot Table
      var header = sumTable.Range(1, 1, 1, 6); // A1 to F1
      var range = sumTable.DataRange;
      var dataRange = sumSheet.Range(header.FirstCell(), range.LastCell());
  
      var pivotSheet = workbook.Worksheets.Add("History Report");
  
      var pivotTable = pivotSheet.PivotTables.AddNew("PivotTable", pivotSheet.Cell(1, 1), dataRange);
  
      // Pivot Table - Formatting table
      pivotTable.ShowPropertiesInTooltips = false;
      pivotTable.RepeatRowLabels = false;
      pivotTable.ShowGrandTotalsColumns = false;
      pivotTable.ShowGrandTotalsRows = false;
      pivotTable.ShowEmptyItemsOnRows = true;
      pivotTable.ShowEmptyItemsOnColumns = true;
      pivotTable.ShowExpandCollapseButtons = false;
      pivotTable.Layout = XLPivotLayout.Tabular;
  
      pivotTable.RowLabels.Add("Changeset");
      pivotTable.RowLabels.Add("Committer");
      pivotTable.RowLabels.Add("Date");
      pivotTable.RowLabels.Add("Comment");
      pivotTable.RowLabels.Add("File Changes");
      pivotTable.RowLabels.Add("Source");
  
      // Go off and save the workbook.
      bool saved = SaveFile();
      return saved;
  }
  </code></pre>
  
  <p>I believe the problem is with how I am selecting the <code>dataRange</code> for the source of the Pivot Table.</p>
  
  <pre><code>    var header = sumTable.Range(1, 1, 1, 6); // A1 to F1
      var range = sumTable.DataRange;
      var dataRange = sumSheet.Range(header.FirstCell(), range.LastCell());
  </code></pre>
  
  <p>I followed the <a href="https://github.com/ClosedXML/ClosedXML/wiki/Pivot-Table-example" rel="nofollow noreferrer">example</a> found on their wiki, but it gives those errors with my implementation. The only difference between my problem and the example, is that I am getting my source for the Pivot Table from a <code>DataTable</code>, and I am only inserting values into <code>RowLabels</code> in my Pivot Table.</p>
  
  <p>If it helps, this is how I convert the <code>IEnumerable&lt;Changeset&gt;</code> to a <code>DataTable</code></p>
  
  <pre><code>private DataTable ConvertToDataTable(IEnumerable&lt;Changeset&gt; changesets)
  {
      DataTable table = new DataTable();
      table.Columns.Add("Changeset", typeof(int));
      table.Columns.Add("Committer", typeof(string));
      table.Columns.Add("Date", typeof(DateTime));
      table.Columns.Add("Comment", typeof(string));
      table.Columns.Add("File Changes", typeof(string));
      table.Columns.Add("Source", typeof(string));
  
      foreach(Changeset changeset in changesets) // Loop over all changesets
      {
          int changesetID = changeset.ChangesetId;
          string committer = changeset.CommitterDisplayName;
          DateTime creationDate = changeset.CreationDate;
          string comment = changeset.Comment;
          foreach(Change change in changeset.Changes) // Loop over all Changes in changeset
          {
              string filename = change.Item.ServerItem.Substring(change.Item.ServerItem.LastIndexOf("/") + 1);
              table.Rows.Add(changesetID, committer, creationDate, comment, filename, change.Item.ServerItem);
          }
      }
      return table;
  }
  </code></pre>