1

I am trying to add an extra sheet to an existing xlsx workbook using openpyxl while keeping the data validation rules. However, when I load the workbook I get the warning:

openpyxl\worksheet_reader.py:312: UserWarning: Data Validation extension is not supported and will be removed

When I add the extra worksheet using this code:

excel_book = openpyxl.load_workbook('SomeSpreadsheet.xlsx' )

with pd.ExcelWriter('SomeSpreadsheet.xlsx', engine='openpyxl') as writer:
    writer.book = excel_book
    writer.sheets = {
        worksheet.title: worksheet
        for worksheet in excel_book.worksheets
    }
    secondMockData = { 'c': [10,20], 'd': [30,40] }
    secondMockDF = pd.DataFrame(secondMockData)
    secondMockDF.to_excel(writer, 'sheetB', index=False)
    writer.save()

The code runs successfully but, as the warning message states, all data validation rules after saving the file. Is there any way around that, or any other way this task can be accomplished (eg, adding the extra worksheet) without losing data validation?

Many thanks in advance

Marc
  • 73
  • 6
  • [This answer](https://stackoverflow.com/a/72559583/13843268) might be helpful. – sj95126 Oct 11 '22 at 13:44
  • Thanks for the reply, but it seems to me that this is only suppressing the warning message, right? Not actually getting around the actual issue of removing data validation – Marc Oct 11 '22 at 14:00
  • Sorry, based on "the code runs successfully" it wasn't clear whether you wanted to preserve data validation. – sj95126 Oct 11 '22 at 16:51
  • You're quite right, I have amended the question such that this is clear. Many thanks – Marc Oct 12 '22 at 06:21
  • A warning **isn't** an error. The data validations in the workbook are not covered by the OOXML specification and as such not supported by openpyxl. – Charlie Clark Oct 12 '22 at 15:33
  • That's fair enough. I was looking for a way to keep those data validations, but if this is simply not supported for now, such is life. – Marc Oct 14 '22 at 09:42

0 Answers0