xlsx-builder.py (4491B)
1 # Import dependencies 2 import csv 3 import openpyxl 4 from openpyxl.styles.borders import Border, Side 5 from os import path 6 7 # Get type abbreveation 8 def getTypeAbbr(text): 9 return text.replace("Regular", "R").replace("Reflexive", "X").replace("Stem Changing", "S").replace("Orthographic", "O").replace("Irregular", "I") 10 11 # Converts a csv of verbs to a spreadsheet 12 def createXlsx(csvPath, xlsxPath): 13 # Load csv 14 rows = [] 15 with open(csvPath, encoding="utf-8") as f: 16 for row in csv.reader(f): 17 rows.append(row) 18 19 # Sort verbs by infinitive 20 rows = sorted(rows[1:], key=lambda x: x[1]) 21 22 # Rearrange csv data 23 data = [["Infinitive", "English & Participles", "Tense & Type", "Yo", "Tú", "Él", "Nosotros", "Ellos"]] 24 for row in rows: 25 data += [[row[1], row[0], f"Present ({getTypeAbbr(row[6])})", row[7], row[8], row[9], row[10], row[11]]] # Present 26 data += [["", "", f"Preterite ({getTypeAbbr(row[12])})", row[13], row[14], row[15], row[16], row[17]]] # Preterite 27 data += [["", "", f"Imperfect ({getTypeAbbr(row[18])})", row[19], row[20], row[21], row[22], row[23]]] # Imperfect 28 data += [["", f"Pres. Participle ({getTypeAbbr(row[2])}):", f"Conditional ({getTypeAbbr(row[24])})", row[25], row[26], row[27], row[28], row[29]]] # Conditional 29 data += [["", row[3], f"Future ({getTypeAbbr(row[30])})", row[31], row[32], row[33], row[34], row[35]]] # Simple Future 30 data += [["", f"Past Participle ({getTypeAbbr(row[4])}):", f"Pres. Subj. ({getTypeAbbr(row[36])})", row[37], row[38], row[39], row[40], row[41]]] # Present Subjunctive 31 data += [["", row[5], f"Imp. Subj. ({getTypeAbbr(row[42])})", row[43], row[44], row[45], row[46], row[47]]] # Imperfect Subjunctive 32 33 # Create spreadsheet 34 vk = openpyxl.Workbook() 35 36 # Get border styles 37 thick = Side(border_style="thick", color="FF000000") 38 thin = Side(border_style="thin", color="FF000000") 39 40 # Set data 41 sh = vk.active 42 sh.page_setup.fitToHeight = False 43 for row in range(len(data)): 44 for column in range(len(data[row])): 45 # Get cell 46 cell = sh.cell(row=row + 1, column=column + 1) 47 48 # Set cell value 49 cell.value = data[row][column] 50 51 # Get cell borders 52 border = Border( 53 left=cell.border.left, 54 right=cell.border.right, 55 top=cell.border.top, 56 bottom=cell.border.bottom 57 ) 58 59 # Set inner borders 60 if column > 1: 61 # Conjugation columns only 62 border.top = thin 63 border.bottom = thin 64 if column == 1 and row % 7 > 3: 65 # Participles only 66 border.top = thin 67 border.bottom = thin 68 if row % 7 == 1: 69 # Present tense rows only 70 border.top = thick 71 if row % 7 == 0: 72 # Present Subjunctive tense rows only 73 border.bottom = thick 74 border.left = thin 75 border.right = thin 76 77 # Set outside borders 78 if column == 0: 79 border.left = thick 80 if column == len(data[row]) - 1: 81 border.right = thick 82 if row == 0: 83 border.top = thick 84 if row == len(data) - 1: 85 border.bottom = thick 86 87 # Update cell borders 88 cell.border = border 89 90 # Add page break 91 for i in range(36, len(data), 35): 92 sh.row_breaks.append(openpyxl.worksheet.pagebreak.Break(id=i)) 93 94 # Repeat first row every page 95 sh.print_title_rows = "1:1" 96 97 # Set page margins 98 sh.page_margins.left = 0.25 99 sh.page_margins.right = 0.25 100 sh.page_margins.top = 0.75 101 sh.page_margins.bottom = 0.25 102 sh.page_margins.header = 0.3 103 sh.page_margins.footer = 0 104 105 # Set orientation to landscape 106 openpyxl.worksheet.worksheet.Worksheet.set_printer_settings(sh, paper_size=1, orientation="landscape") 107 108 # Save spreadsheet 109 vk.save(xlsxPath) 110 111 112 113 # Create spreadsheet from Verbs.csv 114 if (__name__ == "__main__"): 115 createXlsx(path.join(path.dirname(__file__), "../data/verbs.csv"), "verbs.xlsx")