none
OpenXML Excel file corruption, DataValidations RRS feed

  • 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 errror "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.

    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error946360_01.xml</logFileName><summary>Errors were detected in file 'D:\file.xlsx'</summary><removedFeatures summary="Following is a list of removed features:"><removedFeature>Removed Feature: Data validation from /xl/worksheets/sheet.xml part</removedFeature></removedFeatures><repairedRecords summary="Following is a list of repairs:"><repairedRecord>Repaired Records: Cell information from /xl/worksheets/sheet.xml part</repairedRecord><repairedRecord>Repaired Records: Cell information from /xl/worksheets/sheet2.xml part</repairedRecord></repairedRecords></recoveryLog>

    What I am surprised is that 25,000 is not a huge number for corrupting an excel file. The content is the same across all the rows i.e. it has the same content for all the rows, so it should not be something to do with the content.

    The file size reaches approximately 1.8 MB

    Any ideas/suggestions what could be going wrong here?


    Wednesday, March 9, 2016 5:45 AM

Answers

  • Hi,

    I was able to resolve the issue. Actually the problem is that I was creating data validation for each cell that required a dropdown. Whereas we should be creating a dataValidation object for a range of cells which will have the same data validation (i.e. dropdown values in my case).

    Also, it probably explains that it used to work for a smaller set of data in the excel however, when the number of items in the excel sheet i.e. the corresponding number of data validations for each cell also increased. I am not sure what is the maximum number of allowed dataValidation objects in a sheet.

    However, we should be creating a dataValidation object for a range of cells in the sheet instead of each cell.

    Hope this helps someone, and saves their time.

    Regards,

    Deven

    • Marked as answer by Devendra Singh Wednesday, March 30, 2016 11:44 AM
    Wednesday, March 30, 2016 11:44 AM

All replies

  • >>>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 errror "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.<<<

    According to your descirption, I suggest that you could create an Excel file that have the number of rows reaches 25,000 for Sheet1, then add Data validation manually, could you reproduce this issue? If not, you could use open xml sdk 2.0 productivity tool to open this Excel file, then click reflect code to get C# code.

    In addition could you provide more information about your issue, for example sample code, Excel version, screenshot etc., that will help us reproduce and resolve it.

    Thanks for your understanding.

    Thursday, March 10, 2016 5:53 AM
  • Hi Devendra Singh,

    Could you please provide more information for us to reproduce your issue?

    What data validations are your using? which cells are you adding data validations for, etc...

    Monday, March 21, 2016 7:13 AM
  • Hi,

    I was able to resolve the issue. Actually the problem is that I was creating data validation for each cell that required a dropdown. Whereas we should be creating a dataValidation object for a range of cells which will have the same data validation (i.e. dropdown values in my case).

    Also, it probably explains that it used to work for a smaller set of data in the excel however, when the number of items in the excel sheet i.e. the corresponding number of data validations for each cell also increased. I am not sure what is the maximum number of allowed dataValidation objects in a sheet.

    However, we should be creating a dataValidation object for a range of cells in the sheet instead of each cell.

    Hope this helps someone, and saves their time.

    Regards,

    Deven

    • Marked as answer by Devendra Singh Wednesday, March 30, 2016 11:44 AM
    Wednesday, March 30, 2016 11:44 AM