def set_border(ws, cell_range):
rows = ws[cell_range]
side = Side(border_style='thin', color="FF000000")
rows = list(
rows
) # we convert iterator to list for simplicity, but it's not memory efficient solution
max_y = len(rows) - 1 # index of the last row
for pos_y, cells in enumerate(rows):
max_x = len(cells) - 1 # index of the last cell
for pos_x, cell in enumerate(cells):
border = Border(left=cell.border.left,
right=cell.border.right,
top=cell.border.top,
bottom=cell.border.bottom)
border.left = side
border.right = side
border.top = side
border.bottom = side
#if pos_x == 0:
# border.left = side
#if pos_x == max_x:
# border.right = side
#if pos_y == 0:
# border.top = side
#if pos_y == max_y:
# border.bottom = side
# set new border only if it's one of the edge cells
if pos_x == 0 or pos_x == max_x or pos_y == 0 or pos_y == max_y:
cell.border = border
def createXlsx(csvPath, xlsxPath):
# Load csv
rows = []
with open(csvPath, encoding="utf-8") as f:
for row in csv.reader(f):
rows.append(row)
# Rearrange csv data
data = [["English", "Infinitive", "Yo", "Tú", "Él", "Nosotros", "Ellos"]]
for row in rows[1:]:
data += [[row[0], row[1], row[7], row[8], row[9], row[10],
row[11]]] # Present
data += [["", "", row[13], row[14], row[15], row[16],
row[17]]] # Preterite
data += [["", "", row[19], row[20], row[21], row[22],
row[23]]] # Imperfect
data += [["", "", row[25], row[26], row[27], row[28],
row[29]]] # Conditional
data += [["", "", row[31], row[32], row[33], row[34],
row[35]]] # Simple Future
data += [["", "", row[37], row[38], row[39], row[40],
row[41]]] # Present Subjunctive
data += [["", "", row[43], row[44], row[45], row[46],
row[47]]] # Imperfect Subjunctive
# Create spreadsheet
vk = openpyxl.Workbook()
# Get border styles
thick = Side(border_style="thick", color="FF000000")
thin = Side(border_style="thin", color="FF000000")
# Set data
sh = vk.active
sh.page_setup.fitToHeight = False
for row in range(len(data)):
for column in range(len(data[row])):
# Get cell
cell = sh.cell(row=row + 1, column=column + 1)
# Set cell value
cell.value = data[row][column]
# Get cell borders
border = Border(left=cell.border.left,
right=cell.border.right,
top=cell.border.top,
bottom=cell.border.bottom)
# Set inner borders
if column > 1:
# Conjugation columns only
border.top = thin
border.bottom = thin
if row % 7 == 1:
# Present tense rows only
border.top = thick
if row % 7 == 0:
# Present Subjunctive tense rows only
border.bottom = thick
border.left = thin
border.right = thin
# Set outside borders
if column == 0:
border.left = thick
if column == len(data[row]) - 1:
border.right = thick
if row == 0:
border.top = thick
if row == len(data) - 1:
border.bottom = thick
# Update cell borders
cell.border = border
# Set page margins
sh.page_margins.left = 0.25
sh.page_margins.right = 0.25
sh.page_margins.top = 0.75
sh.page_margins.bottom = 0.25
sh.page_margins.header = 0.3
sh.page_margins.footer = 0
# Set orientation to landscape
openpyxl.worksheet.worksheet.Worksheet.set_printer_settings(
sh, paper_size=1, orientation="landscape")
# Save spreadsheet
vk.save(xlsxPath)
import openpyxl
from openpyxl.styles.borders import (
Border,
Side,
BORDER_THIN,
BORDER_DOTTED,
BORDER_DOUBLE,
BORDER_MEDIUM,
)
book = openpyxl.Workbook()
active_sheet = book.active
active_sheet.cell(column=2, row=2, value="Write B2")
active_sheet["C3"] = "Write C3"
active_sheet["B2"].border = Border(
left=Side(style=BORDER_THIN),
right=Side(style=BORDER_THIN),
top=Side(style=BORDER_THIN),
bottom=Side(style=BORDER_THIN),
)
border_C3 = Border()
border_C3.left = Side(style=BORDER_DOTTED)
border_C3.right = Side(style=BORDER_DOUBLE)
border_C3.top = Side(style=BORDER_MEDIUM)
border_C3.bottom = Side(style=BORDER_THIN)
active_sheet["C3"].border = border_C3
book.save("library/use_file/openpyxl/xlsx/sample5.xlsx")
def createXlsx(csvPath, xlsxPath):
# Load csv
rows = []
with open(csvPath, encoding="utf-8") as f:
for row in csv.reader(f):
rows.append(row)
# Rearrange csv data
data = [["English", "Infinitive", "Yo", "Tú", "Él", "Nosotros", "Ellos"]]
for row in rows[1:]:
data += [["", "", row[5], row[6], row[7], row[8], row[9]]]
data += [[row[0], row[1], row[11], row[12], row[13], row[14], row[15]]]
data += [["", "", row[17], row[18], row[19], row[20], row[21]]]
# Create spreadsheet
vk = openpyxl.Workbook()
# Get border styles
thick = Side(border_style='thick', color="FF000000")
thin = Side(border_style='thin', color="FF000000")
# Set data
sh = vk.active
sh.page_setup.fitToHeight = False
for row in range(len(data)):
for column in range(len(data[row])):
# Get cell
cell = sh.cell(row=row + 1, column=column + 1)
# Set cell value
cell.value = data[row][column]
# Get cell borders
border = Border(left=cell.border.left,
right=cell.border.right,
top=cell.border.top,
bottom=cell.border.bottom)
# Set inner borders
if column > 1:
# Conjugation columns only
border.top = thin
border.bottom = thin
if row % 3 == 1:
# Present tense rows only
border.top = thick
border.left = thin
border.right = thin
# Set outside borders
if column == 0:
border.left = thick
if column == len(data[row]) - 1:
border.right = thick
if row == 0:
border.top = thick
if row == len(data) - 1:
border.bottom = thick
# Update cell borders
cell.border = border
# Set page margins
sh.page_margins.left = 0.25
sh.page_margins.right = 0.25
sh.page_margins.top = 0.75
sh.page_margins.bottom = 0.25
sh.page_margins.header = 0.3
sh.page_margins.footer = 0
# Save spreadsheet
vk.save(xlsxPath)
