示例#1
0
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)
示例#3
0
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")
示例#4
0
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)