guess_types turns negative percentages into positive

Issue #819 resolved
Mike Demenok
created an issue

The regex used to parse percentages out of strings currently drops the minus sign, turning negative numbers into positive.

Reproducible case:

import openpyxl, re
wb = openpyxl.Workbook(guess_types=True)
ws = wb.active

ws.cell(row=1, column=1, value="Broken value")
cell = ws.cell(row=1, column=2, value="-20%")
print("This should be negative, but isn't: {}".format(cell.value))

openpyxl.cell.cell.PERCENT_REGEX = re.compile(r'^(?P<number>-?[0-9]*\.?[0-9]*\s?)\%$')
ws.cell(row=2, column=1, value="Correct value")
cell = ws.cell(row=2, column=2, value="-20%")
print("This should be negative, and is: {}".format(cell.value))
wb.save("negative_percentages.xlsx")

It's a simple fix by moving the - sign into the capture group, i.e. change from:

PERCENT_REGEX = re.compile(r'^\-?(?P<number>[0-9]*\.?[0-9]*\s?)\%$')

to

PERCENT_REGEX = re.compile(r'^(?P<number>-?[0-9]*\.?[0-9]*\s?)\%$')

Please note that same problem exists in NUMBER_REGEX, but the capture group result is never actually used. Consider updating it as well in case this changes in the future.

Comments (3)

  1. CharlieC

    Thanks for the report. I think it's another example of why this feature was always a fool's errand and should be removed in future versions. It was really introduced for a specific project but client code should really have its own type inferencing code.

  2. Log in to comment