Minor issue regarding whitespace parsing #136
Description
What happened and code sample
This doesn't seem like a big deal but wanted to document it. In a situation where we write a formula having an unnecessary space between the sheet name and range like =SUM('Sheet2'! A1:A5), Excel evaluates it, but pycel gives #NAME?
Checked other variations while I was at it:
from openpyxl import load_workbook
wb = load_workbook()
ws = wb['Sheet1']
ws['A1'] = '=SUM(\'Sheet2\'!A1:A5)' # no spaces
ws['A2'] = '=SUM(\'Sheet2\'! A1:A5)' # Excel can compute this, but pycel can't
ws['A3'] = '=SUM (\'Sheet2\'!A1:A5)' # this is the only one that Excel can't compute
ws['A4'] = '=SUM( \'Sheet2\'!A1:A5)'
ws['A5'] = '=SUM(\'Sheet2\'!A1: A5)'
ws['A6'] = '=SUM(\'Sheet2\'!A1 :A5)'Here's the wb saved:
test_tokenizer.xlsx
I'm not really familiar with the tokenizer, but something like this fixes it
def _items(self):
...
next_consumed = False
for prev_token, token, next_token in zip(t, t[1:], t[2:]):
if next_consumed:
next_consumed = False
continue
if token.type != Token.WSPACE or not prev_token or not next_token:
...
# testing: case when there's a whitespace like 'Sheet'! A1:A5
elif token.type == Token.WSPACE and (
prev_token.matches(type_=Token.OPERAND, subtype=Token.RANGE) and
next_token.matches(type_=Token.OPERAND, subtype=Token.RANGE)):
tokens.pop()
tokens.append(Token(prev_token.value + next_token.value, Token.OPERAND, Token.RANGE))
next_consumed = True
elif
...
return tokensThis is probably not the best way and might have unintended consequences though.