Skip to content

Instantly share code, notes, and snippets.

@pybites
Created September 11, 2017 18:13
Show Gist options
  • Save pybites/e1c04368fd1bc994c6f2e3ef89e90dd4 to your computer and use it in GitHub Desktop.
Save pybites/e1c04368fd1bc994c6f2e3ef89e90dd4 to your computer and use it in GitHub Desktop.
#!python3
# my first VBA macro redone in Python
# related article: How I got fired up about programming
# https://bobbelderbos.com/2016/02/fired-up-about-programming/
# 1. download game here: http://juegosexcel.com/foro/viewtopic.php?t=6396
# 2. (to avoid loosing a lot of time using older excel libs) save file to xlsx
#
from collections import namedtuple
import re
from openpyxl import load_workbook
SPREADSHEET = 'ciudades.xlsx'
FIRST_SHEET = 'Hoja1'
SOLUTION_FORMULA = re.compile(r'=IF\((\S\d+)="([^"]+)".*')
Answer = namedtuple('Answer', 'cell city')
wb = load_workbook(SPREADSHEET)
ws1 = wb.get_sheet_by_name(FIRST_SHEET)
def get_answers():
''' returns tuple pairs of destination cell, right answer '''
for col in list('CFIL'):
for row in range(30, 160, 10):
cell = col + str(row)
formula = ws1[cell].value
m = SOLUTION_FORMULA.match(formula)
if not m:
raise ValueError('{} does not match exp formula'.format(cell))
coord, answer = m.groups()
yield Answer(cell=coord, city=answer)
def update_spreadsheet(answers):
''' updates the spreadsheet cells with the right city answers '''
for answer in answers:
ws1[answer.cell] = answer.city
def save_spreadsheet():
''' save solution to a new spreadsheet '''
save_to = SPREADSHEET.replace('.xls', '_solution.xls')
wb.save(filename=save_to)
if __name__ == '__main__':
answers = get_answers()
update_spreadsheet(answers)
save_spreadsheet()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment