Data Validation /commas and semicolons

[Stanisław Sypniewski (stansy)]

The data validator does not work properly for the non-English version. In cases where commas are used as a decimal point and semicolons as a separator, using openpyxl removes formulas in all workbooks. For example:

Excel data validator (list) : "1,1;1,2;1,3"

wb = load_workbook(filename)

wb.save( filename)

wb [sheetname].data_validations.dataValidation[0]:

<openpyxl.worksheet.datavalidation.DataValidation object> Parameters: sqref=<MultiCellRange [D2:D9]>, showDropDown=False, showInputMessage=True, showErrorMessage=True, allowBlank=False, errorTitle=None, error=None, promptTitle=None, prompt=None, type='list', errorStyle=None, imeMode=None, operator=None, formula1=None, formula2=None

[Charlie Clark]

On 16 Jun 2023, at 12:51, Stanisław Sypniewski wrote:

The data validator does not work properly for the non-English version. In
cases where commas are used as a decimal point and semicolons as a
separator, using openpyxl removes formulas in all workbooks. For example:

There is no "non-English" version: you must use English settings. This is covered in the documentation.

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Sengelsweg 34
Düsseldorf
D- 40489
Tel: +49-203-3925-0390
Mobile: +49-178-782-6226

[Stanisław Sypniewski (stansy)]

Thank you very much for the interesting answer. Your solution is a big limitation of using Openpyxl in such cases - it will usually be impossible.

A better way would be to use Xlwings without having to change the language version.

[Stanisław Sypniewski (stansy)]

My simple solution:

import re
from io import BytesIO
from zipfile import ZipFile
from openpyxl import load_workbook

RE_WS = re.compile('<worksheet[^>]*')
RE_DV = re.compile('<dataValidations.*</dataValidations>')


src_filename = ...
dst_filename =  ...

with ZipFile(src_filename) as zf:
    origin_paths = [f.filename for f in zf.filelist if re.match(r'xl/worksheets/sheet\d+\.xml', f.filename)]
   

wb = load_workbook(src_filename)
ws = wb.create_sheet("NewOpenpyxlSheet")
ws['A1'].value = 'hello openpyxl'
bio = BytesIO()
wb.save(filename=bio)

new_zip = BytesIO()
with ZipFile(new_zip, 'w') as fz:
    with ZipFile(bio, 'r') as fs:
        for info in fs.filelist:
            path = info.filename
            if path in origin_paths:
                ws = ZipFile(src_filename, 'r').read(path).decode()
                m1 = RE_WS.search(ws)
                m2 = RE_DV.search(ws)
                if m1 and m2:
                    ws = fs.read(path).decode()
                    ws = RE_WS.sub( m1[0], ws)
                    ws = RE_DV.sub( m2[0], ws)
                    fz.writestr(path, ws.encode())
                else:
                    fz.writestr(path, fs.read(path))
            else:
                fz.writestr(path, fs.read(path))

with open(dst_filename, 'wb') as f:
    f.write(new_zip.getbuffer())
new_zip.close()               

Results (from original sheets ):

<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="x14ac xr xr2 xr3" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" xmlns:xr2="http://schemas.microsoft.com/office/spreadsheetml/2015/revision2" xmlns:xr3="http://schemas.microsoft.com/office/spreadsheetml/2016/revision3" xr:uid="{00000000-0001-0000-0000-000000000000}">

<dataValidations count="1"><dataValidation type="list" showInputMessage="1" showErrorMessage="1" sqref="D2:D9" xr:uid="{00000000-0002-0000-0000-000000000000}"><mc:AlternateContent xmlns:x12ac="http://schemas.microsoft.com/office/spreadsheetml/2011/1/ac" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"><mc:Choice Requires="x12ac"><x12ac:list>"2,0","3,0","3,5","4,0","4,5","5,0"</x12ac:list></mc:Choice><mc:Fallback><formula1>"2,0,3,0,3,5,4,0,4,5,5,0"</formula1></mc:Fallback></mc:AlternateContent></dataValidation></dataValidations>

[Stanisław Sypniewski (stansy)]

forgot to add (pardonable mistake): bio.close()

[Charlie Clark]

On 16 Jun 2023, at 20:36, Stanisław Sypniewski wrote:

Thank you very much for the interesting answer. Your solution is a big
limitation of using Openpyxl in such cases - it will usually be impossible.
A better way would be to use Xlwings without having to change the language
version.

You misunderstand: openpyxl is a library for the file format. Formulae are always stored using English names and conventions; all the localisation happens in Excel.

Charlie Clark

[Charlie Clark]

On 18 Jun 2023, at 17:14, Stanisław Sypniewski wrote:

> My simple solution:

Looks like a sledgehammer cracking a walnut but if it works for you that's fine.

Charlie Clark

[Stanisław Sypniewski (stansy)]

In my opinion, openpyxl is a library that uses old namespaces. As I showed above, it has nothing to do with the language version you are using.

The list of problems to use commas in a data validator on the Internet is long.

Regards

Stan

PS. Yes, sledgehammer that does not destroy the original worksheets lol.

[Charlie Clark]

On 19 Jun 2023, at 12:07, Stanisław Sypniewski wrote:

> In my opinion, openpyxl is a library that uses old namespaces. As I showed
> above, it has nothing to do with the language version you are using.
> The list of problems to use commas in a data validator on the Internet is
> long.

As I said, you don't seem to have understood how Excel works, so I don't think your opinion counts for much. If you're validating numbers against a list, then the validator will have to use the decimal point as separator. This was your original question.

You seem to be confusing this with Microsoft's switch to using an optional extension for dealing with lists in validations. This is just in addition to the list of integers.

> Regards
> Stan
>
> PS. Yes, sledgehammer that does not destroy the original worksheets lol.

You're using brute force string manipulation on XML. So, yes, this is a sledgehammer. It would be easier and safer to adapt openpyxl code to do what you want, but you don't seem interested in a robust solution.

Good luck.

Charlie