[PYTHON] A small memorandum of openpyxl

Overview

Since there are many functions and it is easier to list them later, they are listed. This is not all because it is only the one I used myself. Please refer to Official for details.

Make a new book

Make a book and make one sheet. After that, it is assumed that this is done.

python

import openpyxl

book = openpyxl.Workbook()
sheet = book.active

Sheet name setting

python

sheet.name = "Sit"

Book save

python

try:
    book.save("book.xlsx")
except exception as e:
    #Permission Error when excel is open. There may be others.
finally:
    book.close()

Make excel from csv

python

import csv

with open("file.csv", "r") as f:
    for l in csv.reader(f):
        sheet.append(l)

Draw a border in the cell

This example is when pulling to the outer circumference of the cell. Cell specifications (num_row, num_column) are R1C1 numbers (same below)

python

from openpyxl.styles.borders import Side

border = openpyxl.styles.borders.Border()
border.top = Side(style='hair', color='000000')
border.bottom = Side(style='thin', color='ff0000')
border.left = Side(style='thick', color='00ff00')
border.right = Side(style='medium', color='0000ff')
sheet.cell(row=num_row, column=num_column).border = border

color is a common RGB value in hexadecimal.

If you want to keep the existing ruled lines and change only a part

python

border = copy(sheet.cell(row=num_row, column=num_col).border)

After getting the state of the current ruled line with this, change it with the above one.

Font settings

python

font = openpyxl.styles.fonts.Font()
font.name = 'Font name'
font.size = 10
font.color = 'deadbeef'
#Specific cell
sheet.cell(row=num_row, column=num_col).font = font
#The entire value input range
for r in sheet:
    for c in r:
        sheet[c.coordinate].font = font

color is an abbreviation for hexadecimal numbers.

Cell width setting

It seems that it cannot be used because it is an internal function of Excel that adjusts automatically. It seems that the only way to make it look like that is to calculate from the contents of the column.

python

column = openpyxl.utils.get_column_letter(num_column)
length = len(sheet.cell(row=num_row, column=num_column)
sheet.column_dimensions[column].width = length * 1.5

Print configuration

When printing the first line as the print title, the paper orientation as horizontal, the number of horizontal pages as 1, and the number of vertical pages as unspecified.

python

sheet.print_title_rows = '1:1'              #Print title line
sheet.page_setup.orientation = 'landscape'  #Vertical'portrait'
sheet.page_setup.fitToWidth = 1
sheet.page_setup.fitToHeight = 0
sheet.sheet_properties.pageSetUpPr.fitToPage = True

Cell coloring

In the case of fill, there are of course other patterns as well.

paint_cell.py

from openpyxl.styles.fills import (PatternFill, FILL_SOLID)
fill = PatternFill(fgColor='aabbcc', patternType=FILL_SOLID)
sheet.cell(row=num_row, column=num_column).fill = fill

fgColor is a hexadecimal number (ry)

In-cell character alignment

In the case of bottom and centering.

python

align = openpyxl.styles.Alignment()
align.horizontal = 'center'
align.vertical = 'bottom'
sheet.cell(row=num_row, column=num_column).alignment = align

Filter settings

Note that the cell specification is the range of data to which the filter applies. The cell specification in this case is in A1 format.

python

sheet.auto_filter.ref = "B2:D10"

Digitization of column names

This is when you want to get the column number for R1C1.

column_number.py

num_column = openpyxl.utils.column_index_from_string('D')