How to create and save pivot table?

[17521121]

I am struggling for creating and saving a pivot table, I can create the file but unable to open it, 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 code showed no error but I cannot open the generated file, got error

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.

I nearly finish my job, just need some more of operation working on pivot table, so I really do not want to reimplement all of the logic using other libs.

Please help!

[shxh13579]

I also have the same issue,however format .xls can output excel with pivot table correctly. But there are still some issue, file format .xls would be encoded to HSSFWorkbook in NPOI, and HSSFWorkbook or the other component with HSSF doesn't support manipulate pivot table.

[17521121]

@shxh13579 I ended up with using Microsoft,Office.Interop.Excel to create pivot table on xlsx file, very similar to as using macro on excel, but in a long run I still hope NPOI can give a fix for this, I could give you my code to create pivot table if repo owner accepts it @tonyqus

[tonyqus]

@17521121 do you mean you have a example code about how to create pivot table? How about contributing to npoi-examples

[quoctienkt]

@tonyqus I do have C# code to create pivot table, but that is COM reference usage, when running it needs Excel installed on the computer, so it is not a contribution to NPOI at all

[tonyqus]

Looks your company is using NPOI. Can you contribue your use case to https://github.com/nissl-lab/npoi/issues/705 ?