Adding rows of data into existing xlsx file with pivot tables and charts result with unusable file.

[rafinewman]

Discussed in #1292

^{Originally posted by rafinewman March 13, 2024}
hi,

i have designed am excel file XLSX with a table of data in sheet1 and pivot table , pivot charts and slicer on sheet2
after it works fine. i have deleted the rows of data and save the file with no data. i have configured the objects to refresh with new data when file is opened.
when i open this empty file and paste rows of data to the empty table, all pivot objects are refreshed and i see new data as expected in the pivot tables and charts and slicer and it functions as expected
BUT,
When I use NPOI with .net 4.8 to fill in the rows ( i checked it separately and it worked fine) and the i open the file, i get an error msg from excel that "we found a problem with some content in file.xlsx. do you want to recover?"
if i answer no, the file is closed, when i answer yes, i get a none usable workbook while data exists in the table but slice and pivot table are not usable and sometimes slice even removed.

is it a known problem? can it be solved? maybe NPOI doesn't support such combination?

[Bykiev]

Hi, what is NPOI version?
Possible dupes #1155, #1201

[rafinewman]

latest 2.6.2

i adding the files.
before=original template file with pivot tables and slicer
after=file with data inserted by NPOI
before.xlsx
after.xlsx

it is very similar to #1201

[tonyqus]

Pivot part of NPOI is not so stable for now because very few NPOI users are using it. We are collecting feedbacks for pivot table. Hope we can stablize the pivot part before 2.9.0.

[rafinewman]

thanks. when this expected?

[tonyqus]

Not shortly, I guess 1-2 years since very few NPOI users uses Pivot in Excel. NPOI improvements are mainly based on bug report and feedbacks. It takes about 4 years to stablize OpenXML basic features (there used to a few file broken issue)

[phinheiro]

hey @rafinewman, im having a similar issue right now when I insert data in a existing excel template that have some sheets with pivot tables. Ive tried to open only the template file on excel and it works fine. Ive tried just to input the data in a new empty excel file and it worked too. But when i add some data, the formulas just stop working and this error message telling me to "repair" the file shows up. Did you found any workaround to solve it?

im using NPOI 2.7.2 with .NET 8

FYI: @tonyqus

EDIT: my workaround was to remove all the pivot tables and filters from my excel file and it worked. Though it was not the best scenario, at least it gonna be sufficient for now