313 lines
12 KiB
Python
Executable File
313 lines
12 KiB
Python
Executable File
#!/usr/bin/env python3
|
||
|
||
import os
|
||
import re
|
||
|
||
from openpyxl import load_workbook, Workbook
|
||
from openpyxl.worksheet.worksheet import Worksheet
|
||
|
||
|
||
def escape_latex(content):
|
||
content = content.replace("⁄", "/")
|
||
# content = content.replace("℔", r"\ lb")
|
||
content = content.replace("½", "$\\frac{1}{2}$")
|
||
content = content.replace("¼", "$\\frac{1}{4}$")
|
||
content = content.replace("¾", "$\\frac{3}{4}$")
|
||
content = content.replace("\n", "\n\n")
|
||
content = content.replace("°", r"$^\circ$")
|
||
content = content.replace("&", r"\&")
|
||
content = content.replace("ö", "ö")
|
||
|
||
content = re.sub(r'(\d) (\d+)/(\d+)', r'$\1\\frac{\2}{\3}$', content)
|
||
content = re.sub(r'(\d+)/(\d+)', r'$\\frac{\1}{\2}$', content)
|
||
content = re.sub(r'([\d$]g?) ?- ?([\d$])', '\\1--\\2', content)
|
||
content = content.replace("$ ", r"$\,")
|
||
|
||
return content
|
||
|
||
|
||
# Define output file
|
||
f_latex = open("rezepte.tex", "w+")
|
||
f_latex.write("%!TEX ROOT = ./main.tex\n\n")
|
||
|
||
# Load cookbook
|
||
cb: Workbook = load_workbook('kekse.xlsx', data_only=True)
|
||
sheets = cb.sheetnames
|
||
|
||
# Find worksheet with recipes
|
||
ws: Worksheet = cb["Rezepte"]
|
||
|
||
# Variables to store information while parsing
|
||
|
||
# Legend gives the column with descriptions and starting row for the recipes
|
||
legend_row = 0
|
||
legend_col = 0
|
||
|
||
# Ingredients
|
||
# Find the corresponding rows to all properties of recipes
|
||
ingredients_row_start = 0
|
||
|
||
in_ingredients = False
|
||
in_recipe = False
|
||
|
||
ingredients = {}
|
||
ingredients_unit = {}
|
||
ingredients_comment = {}
|
||
|
||
ingredients_other_row = 0
|
||
|
||
# Other fields
|
||
type_row = 0
|
||
instructions_row = 0
|
||
baking_time_row = 0
|
||
baking_temperature_row = 0
|
||
number_row = 0
|
||
resting_time_row = 0
|
||
specials_row = 0
|
||
|
||
# Years
|
||
first_year_row = 0
|
||
last_year_row = 0
|
||
first_year = 0
|
||
|
||
#####
|
||
|
||
# Go through worksheet and parse all information
|
||
for column in ws.columns:
|
||
for cell in column:
|
||
|
||
# Find row of type of recipe
|
||
if cell.value == "Art":
|
||
type_row = cell.row
|
||
legend_row = cell.row
|
||
legend_col = cell.column
|
||
|
||
if cell.column == legend_col:
|
||
# Find row of recipe name, this is in the row with 'Zutaten'
|
||
if cell.value == "Zutaten":
|
||
name_row = cell.row
|
||
ingredients_row_start = cell.row + 1
|
||
in_ingredients = True
|
||
|
||
# Find position of ingredients
|
||
if cell.value == "Hauptaromen":
|
||
aroma_row = cell.row
|
||
|
||
# Find position of source
|
||
if cell.value == "Quelle":
|
||
source_row = cell.row
|
||
|
||
# Instructions
|
||
if cell.value == "Anleitung":
|
||
instructions_row = cell.row
|
||
|
||
# Check if we are at end of ingredients
|
||
if cell.value == "Sonstiges (Deko)":
|
||
ingredients_other_row = cell.row
|
||
in_ingredients = False
|
||
|
||
# Make list of all ingredients and their units
|
||
if cell.row >= ingredients_row_start and in_ingredients:
|
||
string = cell.value
|
||
|
||
if "(" in string and ")" in string:
|
||
m = re.match(r'^([^(\n]*)(?: \((\S+ [^)]+)\))?(?: \(([^)]+)\))?$', cell.value)
|
||
assert m
|
||
ingredients[cell.row] = m.group(1)
|
||
ingredients_unit[cell.row] = m.group(3)
|
||
ingredients_comment[cell.row] = m.group(2)
|
||
else:
|
||
ingredients[cell.row] = string
|
||
ingredients_unit[cell.row] = ""
|
||
ingredients_comment[cell.row] = ""
|
||
|
||
# Further instructions
|
||
if cell.value == "Backzeit (Minuten)":
|
||
baking_time_row = cell.row
|
||
if cell.value == "Backtemperatur Umluft (Grad)":
|
||
baking_temperature_row = cell.row
|
||
if cell.value == "Ruhezeit (Minuten)":
|
||
resting_time_row = cell.row
|
||
if cell.value == "Anzahl (ca.)":
|
||
number_row = cell.row
|
||
if cell.value == "Besonderheiten":
|
||
specials_row = cell.row
|
||
|
||
# Years
|
||
if cell.value == 2013:
|
||
first_year_row = cell.row
|
||
last_year_row = cell.row
|
||
first_year = 2013
|
||
|
||
if first_year_row > 0 and cell.value == "Kommentare zur Bearbeitung.":
|
||
# cell.row -2 instead of -1 to ignore current year 2021
|
||
last_year_row = cell.row - 2
|
||
|
||
# By now, we should have found everything in the legend
|
||
|
||
#####
|
||
|
||
# Parse recipe
|
||
# Go through worksheet vertically, starting with the first column
|
||
# If right of legend, then it must be a recipe
|
||
if cell.column > legend_col:
|
||
|
||
# Obtain information of recipe in the right order, so that we can directly write it to the tex-file.
|
||
|
||
# Name of recipe
|
||
content = ws.cell(name_row, cell.column).value
|
||
if content != "None\n" and content is not None:
|
||
f_latex.write("\\newpage\n")
|
||
|
||
# if (content[-5:]=="(Oma)"):
|
||
# content="Omas " + content[:-6]
|
||
f_latex.write("\\section{%s}\\vspace{0.5cm minus 0.5cm}\n" % content)
|
||
|
||
f_latex.write("\\begin{tabular}{lll}\n")
|
||
|
||
# Aroma
|
||
content = ws.cell(aroma_row, cell.column).value
|
||
if content != "None\n" and content is not None:
|
||
f_latex.write("\\faHeart & \\textbf{Hauptzutat} & %s" % content)
|
||
|
||
f_latex.write("\\index{\\textbf{%s}}" % content.replace(", ", "}} \\index{\\textbf{"))
|
||
f_latex.write("\\\\\n")
|
||
|
||
# Type
|
||
content = ws.cell(type_row, cell.column).value
|
||
if content != "None\n" and content is not None:
|
||
f_latex.write("\\faEye & \\textbf{Form} & %s" % content)
|
||
f_latex.write("\\index[formen]{\\textbf{%s}}" % content.replace(", ", "}} \\index[formen]{\\textbf{"))
|
||
f_latex.write("\\\\\n")
|
||
|
||
# Source
|
||
content = ws.cell(source_row, cell.column).value
|
||
if content != "None\n" and content is not None:
|
||
f_latex.write("\\faBook & \\textbf{Quelle} & %s" % content)
|
||
f_latex.write("\\\\\n")
|
||
|
||
# Specials
|
||
content = ws.cell(specials_row, cell.column).value
|
||
if content == "(vegan)" or content == "(Vegan)":
|
||
content = "Vegan möglich"
|
||
if content == "(zuckerfrei)" or content == "(Zuckerfrei)":
|
||
content = "Zuckerfrei möglich"
|
||
if content != "None\n" and content is not None:
|
||
f_latex.write("\\faStar & \\textbf{Besonderheiten} & %s" % content)
|
||
f_latex.write("\\index[specials]{\\textbf{%s}}" % content.replace(", ", "}} \\index[specials]{\\textbf{"))
|
||
f_latex.write("\\\\\n")
|
||
|
||
# Baking
|
||
content1 = str(ws.cell(baking_time_row, cell.column).value)
|
||
content2 = str(ws.cell(baking_temperature_row, cell.column).value)
|
||
|
||
if (content1 != "None" and content1 is not None and content1 != "0") or (content2 != "None" and content2 is not None and content2 != "0"):
|
||
f_latex.write("\\Oven & \\textbf{Backzeit} &")
|
||
if content1 != "None" and content1 is not None and content1 != "0":
|
||
if content1[-4:] == "Min.":
|
||
content1 = content1[:-4]
|
||
content1 = re.sub(r'(\d) ?- ?(\d)', '\\1--\\2', content1)
|
||
f_latex.write("%s Minuten bei " % content1)
|
||
else:
|
||
f_latex.write(" bei")
|
||
if content2 != "None" and content2 is not None and content2 != "0":
|
||
f_latex.write(" %s~$^\circ$C" % content2)
|
||
f_latex.write("\\\\\n")
|
||
|
||
# Resting time
|
||
content = str(ws.cell(resting_time_row, cell.column).value)
|
||
if content != "None" and content is not None and content != "0":
|
||
f_latex.write("\\faClockO & \\textbf{Ruhezeit} &")
|
||
if content[-1:] == "h":
|
||
f_latex.write(" %s Stunden" % content[:-1])
|
||
else:
|
||
f_latex.write("%s Minuten" % content)
|
||
f_latex.write("\\\\\n")
|
||
|
||
# Amount
|
||
content = ws.cell(number_row, cell.column).value
|
||
if content != "None\n" and content is not None:
|
||
f_latex.write("\\faHashtag & \\textbf{Anzahl} & %s Stück\\\\\n" % content)
|
||
|
||
# Years
|
||
year = first_year
|
||
count = 0
|
||
output = "\\faCalendar & \\textbf{Gebacken} &"
|
||
for i in range(first_year_row, last_year_row + 1):
|
||
content = ws.cell(i, cell.column).value
|
||
if content != "None\n" and content is not None:
|
||
# Cookie was baked in year
|
||
output = output + " " + str(year) + ","
|
||
count += 1
|
||
year += 1
|
||
if count == 0:
|
||
output = output + " Noch nie gebacken"
|
||
else:
|
||
output = output[:-1]
|
||
f_latex.write(str(output + "\\\\\n"))
|
||
|
||
if count > 0:
|
||
f_latex.write("\\faLineChart & \\textbf{Häufigkeit} & " + str(round(100 / (year - first_year) * count)) + "\\,\\%\\\\\n")
|
||
|
||
f_latex.write("\\end{tabular}\n")
|
||
|
||
# List of Ingredients
|
||
f_latex.write("\\subsection*{Zutaten}\n")
|
||
|
||
f_latex.write("\\begin{multicols}2\\raggedright\n")
|
||
f_latex.write("\\begin{compactitem}\n")
|
||
for i in range(ingredients_row_start, ingredients_other_row):
|
||
number = str(ws.cell(i, cell.column).value)
|
||
if number != "None\n" and number is not None and number != "None":
|
||
|
||
# Add unit and whitespaces
|
||
if not ingredients_unit[i]:
|
||
unit = ""
|
||
elif ingredients_unit[i][0].islower():
|
||
unit = "\\," + ingredients_unit[i]
|
||
else:
|
||
unit = "~" + ingredients_unit[i]
|
||
|
||
# Add Ingredient name
|
||
label = ingredients[i]
|
||
if ingredients[i] == "Ei":
|
||
unit = ""
|
||
if number == "1":
|
||
label = "Ei"
|
||
else:
|
||
label = "Eier"
|
||
|
||
# Add comment if available
|
||
if ingredients_comment[i]:
|
||
label += r" \penalty-10(" + ingredients_comment[i] + ")"
|
||
|
||
label = escape_latex(label)
|
||
f_latex.write("\\item %s%s %s \n" % (number, unit, label))
|
||
|
||
f_latex.write("\\end{compactitem}\n")
|
||
f_latex.write("\\end{multicols}\n")
|
||
f_latex.write("\\vspace{0.2cm minus 0.2cm}\n")
|
||
|
||
# Other ingredients
|
||
content = ws.cell(ingredients_other_row, cell.column).value
|
||
|
||
if content != "None\n" and content is not None and content != "None":
|
||
content = escape_latex(content)
|
||
f_latex.write("\\textit{Außerdem:} %s \n" % content)
|
||
|
||
# Instructions
|
||
content = ws.cell(instructions_row, cell.column).value
|
||
if content != "None\n" and content is not None:
|
||
content = escape_latex(content)
|
||
content = re.sub(r'/([a-z])', r'/\\allowbreak{}\1', content, flags=re.I)
|
||
content = re.sub(r'(\d) ([a-z])', r'\1~\2', content, flags=re.I)
|
||
|
||
f_latex.write("\\subsection*{Anleitung} %s\n" % content)
|
||
|
||
f_latex.write("\\vspace{2cm minus 1cm}\n")
|
||
|
||
# That's all, so close files
|
||
f_latex.close()
|
||
|
||
os.system("latexmk -pdf main.tex")
|