OpenXML Excel file corruption, DataValidations

Question

I am creating an excel file using OpenXML SDK 2.0 from a WPF application. The excel file has multiple sheets, data validations for dropdown cells, Formulas, and StyleSheets applied. It works fine creating excel rows of around 17,500 in Sheet1 and 17,500 in Sheet2 as well. However, when the number of rows reaches 25,000 for both Sheet1 and Sheet2 the file gets corrupted i.e. while opening the file it gives an error "We found a problem with the content in file.xlsx. Do you want to recover as much as we can?" If I recover the file the data is still there for all 25,000 rows in both the sheets however the datavalidations i.e. dropdowns are not applied for any of the cells. The recovery log is as follows, while the repair message in the screenshot.

error946360_01.xmlErrors were detected in file 'D:\file.xlsx'Removed Feature: Data validation from /xl/worksheets/sheet.xml partRepaired Records: Cell information from /xl/worksheets/sheet.xml partRepaired Records: Cell information from /xl/worksheets/sheet2.xml part

Answer 1

For me, checking if a row is already existing before adding it did the trick. Somehow, in the xml schema of the xlsx file (for me it was the sheet1.xml), rows were already existing. After running my code, I added a row with an index, which was already existing. So try to open your excel with winrar, find sheet1.xml (and sheet2.xml in your case) and see if you have duplicated index for rows. Generate the excel file with your code and don't open it with excel, otherwise excel will remove the duplicates and you won't see the problem.

Another option would be to validate your excel file (before opening) with the Open Xml SDK 2.5 Productivity Tool