Some financial values being formatted as Date
Created originally on Bitbucket by robertkarl (Robert Karl)
BUG DESCRIPTION
If you have a numeric cell holding cryptocurrency values, like an ethereum quantity, you might want to format it like so:
3.0 ETH
while the value of the cell may be just 3.
Openpyxl looks at the format string ETH and decides it is a date format because it contains H. `cell.value` will be a date. In particular it will be Jan 3, 1900 for this cell.
MY FIX
I fixed this in my local fork of your project with the following in styles/numbers.py, hopefully without destroying too much else:
def is_date_format(fmt):
if fmt is None:
return False
fmt = fmt.split(";")[0] # only look at the first format
fmt = STRIP_RE.sub("", fmt)
import string
date_like = 'dmhysDMHYS'
others = set(string.ascii_letters)
for i in date_like:
others.discard(i)
others = "".join(others)
result = re.search("[dmhysDMHYS]", fmt)
oppo = re.search("[{}]".format(others), fmt)
result = (result is not None) and (oppo is None)
return result
I’d be happy to open a pull request or add more details when I have a bit more time. I think this occurs in 3.0.3 at least.
Single-cell workbook attached which should repro the problem.
Attachments: 28_eth.xlsx