search

nissl-lab / npoi

a .NET library that can read/write Office formats without Microsoft Office installed. No COM+, no interop.
Apache License 2.0
5.7k stars 1.43k forks source link

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

Open rafinewman opened 6 months ago

rafinewman commented 6 months ago

Discussed in https://github.com/nissl-lab/npoi/discussions/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 commented 6 months ago

Hi, what is NPOI version? Possible dupes https://github.com/nissl-lab/npoi/issues/1155, https://github.com/nissl-lab/npoi/issues/1201

rafinewman commented 6 months ago

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 https://github.com/nissl-lab/npoi/issues/1201

tonyqus commented 6 months ago

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 commented 6 months ago

thanks. when this expected?

tonyqus commented 6 months ago

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)