Excel was unable to open the file... and it removed Data validation

[matthe...@gmail.com]

Hi,

I've made changes using openpyxl to an xlsx file originally created in Excel 2013 and resaved as an xlsx in openpyxls. Not a problem opening this file in Libreoffice Calc, however in Excel 2013, I get an error - 'Excel was able to open the file by repairing or removing the unreadable content', and then to items: 'Removed Feature: Data validation from /xl/worksheets/sheet1.xml part' and 'Removed Records: Named range from /xl/workbook.xml part (Workbool)'. It is the former of the two (data validation) that is the problem as the spreadsheet I'm using is full of dropdown validation lists on cells and I need them working.

Any idea how I can fix this?? Any help appreciated once again.

Many thanks,

Matt

[Charlie Clark]

Well, you can always try and validate the file using the OOXML
Productivity Tool. But it's more likely to be something minor in the way
the file is held together.

Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Kronenstr. 27a
Düsseldorf
D- 40217
Tel: +49-211-600-3657
Mobile: +49-178-782-6226

[matthe...@gmail.com]

Hi Charlie - yeah, I'm guessing it's something to do with the original file I'm opening and re-saving in openpyxl, which is a pretty cluttered, complex, multi-sheet Excel file which is used as a template for a user form. It's very old and had lots of people hacking at it over the years and there are buried links to external data sources, hidden cells, named regions and all kinds in there - so I'm guessing adding more Data Validation is having an negative effect.

One thing I noticed is that the xl/worksheets/sheet1.xml file containing data validation which is created by openpyxl contains the " escapes instead of quotation marks inside the the formula1 tag, where as testing shows that validation seems to be allowed in Excel if the formula string is surrounding in quotation marks (i.e. if the validation is created in Excel or Libreoffice Calc). I cannot figure out what is creating the HTML escapes - doesn't seem to be anything in your code.

Matt

[Charlie Clark]

Am .10.2016, 17:32 Uhr, schrieb <matthe...@gmail.com>:

> Hi Charlie - yeah, I'm guessing it's something to do with the original
> file I'm opening and re-saving in openpyxl, which is a pretty cluttered,
> complex, multi-sheet Excel file which is used as a template for a user
> form. It's very old and had lots of people hacking at it over the years
> and there are buried links to external data sources, hidden cells, named
> regions and all kinds in there - so I'm guessing adding more Data
> Validation is having an negative effect.
>
> One thing I noticed is that the xl/worksheets/sheet1.xml file containing
> data validation which is created by openpyxl contains the &quot; escapes
> instead of quotation marks inside the the formula1 tag, where as testing
> shows that validation seems to be allowed in Excel if the formula string
> is surrounding in quotation marks (i.e. if the validation is created in
> Excel or Libreoffice Calc). I cannot figure out what is creating the
> HTML escapes - doesn't seem to be anything in your code.

Easy enough to test for: try saving the file without adding the data
validation and see if Excel is happier. You can simply put the data
validation code into an empty file and see what happens then.

Any escaping will be handled by the underlying XML libraries. I don't
think this is the source of the problem.

[matthe...@gmail.com]

Ok, thanks for clarifying re the escaping. Had a deadline of today and managed to get round it by writing cell reference strings (e.g. 'Sheet Name'!$A$2:$A:$13') into the formula1 slot rather than summon data from a linked using openpyxl and the string as a list. Excel let this through, but then I had to "switch on" Data Validation on every cell manually in Excel, which was a pain.

Will do some more exploring and report back.  Thanks for your feedback once again.

Matt