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.67k stars 1.42k forks source link

Formula openxml gets corrupted after opened and saved by NPOI 2.7.0 #1315

Closed Quark321 closed 4 months ago

Quark321 commented 5 months ago

NPOI Version

2.7.0

File Type

Upload the Excel File

I cannot upload file as it is my company IP

Reproduce Steps

After update to 2.7.0 from 2.6.2 the same file gives the corrupted file error after the file is opened and saved by NPOI.

error805920_01.xml Errors were detected in file 'xxxxx.xlsm' Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded. Removed Records: Formula from /xl/calcChain.xml part (Calculation properties) Repaired Records: Cell information from /xl/worksheets/sheet3.xml part

Issue Description

After downgrade to 2.6.2 it works as expected. I was not able to indentify cells or formulas affected apart the log file I have added

Bykiev commented 5 months ago

Fixed in https://github.com/nissl-lab/npoi/pull/1291

tonyqus commented 4 months ago

@Bykiev Why do you believe this issue is fixed by #1291? I don't see the clues.

tonyqus commented 4 months ago

@Quark321 Without the original Excel file, it's very hard to figure out what the issue is.

Bykiev commented 4 months ago

@Bykiev Why do you believe this issue is fixed by #1291? I don't see the clues.

The issue is not reproduced in 2.6.2 and the same /xl/calcChain.xml corruption occured. 100% sure it's a shared formula bug, which was fixed. I believe we should release a patch version ASAP.

Quark321 commented 4 months ago

@Quark321 Without the original Excel file, it's very hard to figure out what the issue is.

Hi Tony,

Unfortunately I can't share the file as macros and data are IP of the company. To be honest I am not a familiar with the OpenXML format but if you navigate me to the required xml nodes within the excel file, I can review those and share only affected fragments.

ahnochen1029 commented 4 months ago

We are encountering the same issue. We set "SUM(A1:A2)" at position A3, "SUM(B1:B2)" at position B3, and "SUM(C1:C2)" at position C3 in sheet 3, and use it as a template. Then, we use NPOI to write data to sheet1 and sheet2, instead of sheet 3. The exported Excel file ends up having a corrupted /xl/calcChain.xml. Additionally, the formula in the C3 column of sheet 3 is lost.

Bykiev commented 4 months ago

Can you guys reproduce this issue with 2.6.2?

ahnochen1029 commented 4 months ago

@Bykiev This issue does not occur in 2.6.2. It only occurs in 2.7.0.

Bykiev commented 4 months ago

Can you please check with main branch?

tonyqus commented 4 months ago

I think it can be a regression bug. But I need an original Excel to help reproduce this and figure out what's changed.

ahnochen1029 commented 4 months ago

template.xlsx @tonyqus Hi, maybe you can try this template, sheet3 have "SUM" formula.

tonyqus commented 4 months ago

This regression is caused by a change in #913 fix