How to create pivot table and save it using NPOI C# package?

Question

I am struggling for creating and saving a pivot table, I ran the code below with no error, but failed to open the excel file. Here is my code:

public void Execute()
{
  IWorkbook workbook = new XSSFWorkbook();
  XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet("sample");

  IRow row0 = sheet.CreateRow(0);
  row0.CreateCell(0).SetCellValue("test");
  row0.CreateCell(1).SetCellValue("a");
  row0.CreateCell(2).SetCellValue("b");

  IRow row1 = sheet.CreateRow(1);
  row1.CreateCell(0).SetCellValue("asd");
  row1.CreateCell(1).SetCellValue("das");
  row1.CreateCell(2).SetCellValue("fgh");

  var tl = row0.GetCell(0);
  var br = row1.GetCell(2);
  CellReference topLeft = new CellReference(tl);
  CellReference botRight = new CellReference(br);

  AreaReference aref = new AreaReference(topLeft, botRight);
  CellReference pos = new CellReference(3, 3);


  XSSFPivotTable pivotTable = sheet.CreatePivotTable(aref, pos);

  using (FileStream fileWritter = new FileStream("test.xlsx", FileMode.Create, FileAccess.Write))
  {
      workbook.Write(fileWritter);
  }
}

The error of opening the file:

Removed Part: /xl/pivotCache/pivotCacheDefinition1.xml part with XML error. (PivotTable cache) HRESULT 0x8000ffff Line 1, column 0. Removed Part: /xl/pivotTables/pivotTable1.xml part with XML error. (PivotTable view) HRESULT 0x8000ffff Line 1, column 0.

Please help!

Answer 1

I found the answer on my own: using Microsoft.Office.Interop.Excel to do boring steps in Excel.

I used macro recorder in Excel to generate source code, and then just easily edited the code in any language that I want.