Excel Error when using pandas and openpyxl: Repaired Part: /xl/worksheets/sheet1.xml part with XML error. HRESULT 0x8000ffff Line 1, column 0

Question

I'm coming up with an error of opening up an excel file after writing to it. This is what I have so far:

#locate source document
Path = Path(r'C:\Users\username\Test\EXCEL_Test.xlsx')

# open doc and go to active sheet
wb = load_workbook(filename = Path)
ws = wb.active


#add drop down list to each cell in a certain column
dv_v = DataValidation(type="list", formula1='"Y,N"', allow_blank=True)
    for cell in ws['D']:
        cell = ws.add_data_validation(dv_v)
wb.save(Path)

And these are the two errors that comes up on opening the excel file:

First error popup: "We found a problem with some content in 'EXCEL_Test.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes."

Second error popup: "Repaired Part: /xl/worksheets/sheet1.xml part with XML error. HRESULT 0x8000ffff Line 1, column 0."

My data validation is not showing up, and the file has the above errors when attempting to open the file to view the openpyxl changes.

Maybe if someone can help me find out why these errors are popping up? Python finishes with exit code 0, and why the data validation is coming up as blanks in the recovered file?

Answer 1

I think you are using the ws.add_data_validation(dv) incorrectly. The data validations get assigned to the dv first then the dv gets added to the cell. Try doing it like this.

import openpyxl
from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation

#locate source document
Path = 'C:/Users/username/test/Excel_test.xlsx'

# open doc and go to active sheet
wb = openpyxl.load_workbook(filename = Path)
ws = wb['Sheet1']


#add drop down list to each cell in a certain column
dv = DataValidation(type="list", formula1='"Y,N"', allow_blank=True)
ws.add_data_validation(dv)

# This is the same as for the whole of column D
dv.add('D1:D1048576')
    
wb.save(Path)

Take a look at the Docs here: https://openpyxl.readthedocs.io/en/stable/validation.html