Automatische Generierung eines Kochbuchs für Plätzchen
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
 
 

309 lines
11 KiB

#!/usr/bin/env python3
from openpyxl import Workbook
from openpyxl import load_workbook
import sys
import os
def escape_latex (content):
content=content.replace("","/")
content=content.replace("", "\ lb")
content=content.replace("½", "$\\frac{1}{2}$")
content=content.replace("¼", "$\\frac{1}{4}$")
content=content.replace("¾", "$\\frac{3}{4}$")
content=content.replace("\n", "\\\\\n")
content=content.replace("°", "$^\circ$")
content = content.replace("&", "\&")
content = content.replace("", "ö")
return content
# Define output file
f_latex = open("rezepte.tex","w+")
# Load cookbook
cb = load_workbook('/home/sebastian/Projekte/Kochbuch/kekse.xlsx', data_only=1)
sheets = cb.sheetnames
ws = None
# Find worksheet with recipes
ws = cb.get_sheet_by_name("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):
ingredients[cell.row] = (cell.value.split('(')[0].rstrip())
ingredients_unit[cell.row] = (cell.value.split('(')[1].split(')')[0].rstrip())
ingredients_comment[cell.row] = (cell.value.split(')')[1].rstrip())
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._get_cell(name_row, cell.column).value)
if(content != "None\n" and content != None):
f_latex.write(str("\\begin{minipage}{\\textwidth}\n"))
#if (content[-5:]=="(Oma)"):
# content="Omas " + content[:-6]
f_latex.write(str("\section{%s}\\vspace{0.5cm}\n" % content))
f_latex.write(str("\\begin{tabular}{lll}\n"))
# Aroma
content = (ws._get_cell(aroma_row, cell.column).value)
if(content != "None\n" and content != None):
f_latex.write(str("\\faHeart & \\textbf{Hauptzutat} & %s" % content))
f_latex.write(str("\\index{\\textbf{%s}}" % content.replace(", ", "}} \\index{\\textbf{")))
f_latex.write("\\\\\n")
# Type
content = (ws._get_cell(type_row, cell.column).value)
if(content != "None\n" and content != None):
f_latex.write(str("\\faEye & \\textbf{Form} & %s" % content))
f_latex.write(str("\\index[formen]{\\textbf{%s}}" % content.replace(", ", "}} \\index[formen]{\\textbf{")))
f_latex.write("\\\\\n")
# Source
content = (ws._get_cell(source_row, cell.column).value)
if(content != "None\n" and content != None):
f_latex.write(str("\\faBook & \\textbf{Quelle} & %s" % content))
f_latex.write("\\\\\n")
# Specials
content = (ws._get_cell(specials_row, cell.column).value)
if content == "(vegan)":
content="Vegan"
if(content != "None\n" and content != None):
f_latex.write(str("\\faStar & \\textbf{Besonderheiten} & %s" % content))
f_latex.write("\\\\\n")
# Baking
content1 = str(ws._get_cell(baking_time_row, cell.column).value)
content2 = str(ws._get_cell(baking_temperature_row, cell.column).value)
if((content1 != "None" and content1 != None and content1 != "0") or (content2 != "None" and content2 != None and content2 != "0")):
f_latex.write(str("\\Oven & \\textbf{Backzeit} &"))
if(content1 != "None" and content1 != None and content1 != "0"):
if(content1[-4:]=="Min."):
content1 = content1[:-4]
f_latex.write(str("%s Minuten bei " % content1))
else:
f_latex.write(str(" bei"))
if(content2 != "None" and content2 != None and content2 != "0"):
f_latex.write(str(" %s~$^\circ$C" % content2))
f_latex.write("\\\\\n")
# Resting time
content = str(ws._get_cell(resting_time_row, cell.column).value)
if(content != "None" and content != None and content != "0"):
f_latex.write(str("\\faClockO & \\textbf{Ruhezeit} &"))
if(content[-1:]=="h"):
f_latex.write(str(" %s Stunden" % content[:-1]))
else:
f_latex.write(str("%s Minuten" % content))
f_latex.write("\\\\\n")
# Amount
content = (ws._get_cell(number_row, cell.column).value)
if(content != "None\n" and content != None):
f_latex.write(str("\\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._get_cell(i, cell.column).value)
if(content != "None\n" and content != 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(str("\\faLineChart & \\textbf{Häufigkeit} & " + str(round(100/(year-first_year)*count))+"\\%\\\\\n"))
f_latex.write(str("\\end{tabular}\n"))
# List of Ingredients
f_latex.write(str("\\subsection*{Zutaten}\n"))
f_latex.write(str("\\begin{compactitem}\n"))
for i in range (ingredients_row_start, ingredients_other_row):
content = str(ws._get_cell(i, cell.column).value)
if(content != "None\n" and content != None and content != "None"):
#content = content + ingredients_unit[i-ingredients_row_start] + ingredients[i-ingredients_row_start]
# Add unit and whitespaces if needed
if (ingredients_unit[i] !="g"):
content = content + " "
if (ingredients_unit[i] != ""):
content = content + ingredients_unit[i] + " "
# Add Ingredient name
if (ingredients[i] == "Ei"):
if(content == "1 "):
content = content + "Ei"
else:
content = content + "Eier"
else:
content = content + ingredients[i]
# Add comment if available
if(ingredients_comment[i] != ""):
content = content + "; " + ingredients_unit[i]
content=escape_latex(content)
f_latex.write(str("\\item %s \n") % content)
f_latex.write(str("\\end{compactitem}\n\\vspace{0.2cm}\n"))
# Other ingredients
content = (ws._get_cell(ingredients_other_row, cell.column).value)
if(content != "None\n" and content != None and content != "None"):
content = escape_latex(content)
f_latex.write("\\textit{Außerdem:} %s \n" % content)
# Instructions
content = (ws._get_cell(instructions_row, cell.column).value)
if(content != "None\n" and content != None):
content=escape_latex(content)
f_latex.write(str("\\subsection*{Anleitung} %s\n" % content))
f_latex.write(str("\\end{minipage}\n\n"))
f_latex.write(str("\\vspace{2cm}\n"))
# That's all, so close files
f_latex.close()
os.system("latexmk -pdf main.tex")