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')

Recommended Posts

A small memorandum of openpyxl
A memorandum of kernel compilation
A memorandum of using eigen3
A small sample note of list_head
A memorandum of files under conf.d
A memorandum of closure survey contents
Memorandum of sed
A memorandum of using Python's input function
A memorandum of speed of arbitrary degree diagonalization
A memorandum of understanding about django's QueryDict
A memorandum of python string deletion process
A memorandum of trouble when formatting data
A memorandum of calling Python from Common Lisp
A memorandum of studying and implementing deep learning
A memorandum of extraction by python bs4 request
[Linux command] A memorandum of frequently used commands
Memorandum of fastText (editing)
memorandum of vi command
A memorandum about matplotlib
A memorandum about Nan.
elasticsearch_dsl Memorandum of Understanding
A memorandum about the warning of the pylint output result
A memorandum of stumbling on my personal HEROKU & Python (Flask)
A simple sample of pivot_table.
A memorandum about correlation [Python]
[Introduction to AWS] A memorandum of building a web server on AWS
A memorandum about Python mock
A memorandum regarding γ conversion
A brief summary of Linux
A small note following printf
A memorandum of understanding for the Python package management tool ez_setup
A memorandum of scraping & machine learning [development technique] by Python (Chapter 4)
A memorandum of scraping & machine learning [development technique] by Python (Chapter 5)
A memorandum regarding the acquisition of the Python3 engineer certification basic exam
A record of patching a python package
A memorandum when using beautiful soup
Memorandum on Memoization of recursive series
A memorandum to change to Manjaro Linux
Implementation of a two-layer neural network 2
[Django] Memorandum of environment construction procedure
A brief summary of Python collections
Memorandum on Memoization of recursive functions
A collection of one-liner web servers
A rough summary of OS history
[Memo] Small story of pandas, numpy
A brief summary of qubits (beginners)
A Tour of Go Learning Summary
Memorandum of beginners Python "isdigit" movement
Generate a list of consecutive characters
The story of writing a program
A memorandum of JSON Schema notation for dictionaries that take arbitrary keys
Use shutil to delete all folders with a small number of files
I tried a formation flight of a small drone Tello with ESP32: DJI Tello drone formation flight
A memorandum of how to write pandas that I tend to forget personally
A memorandum of filter commands that you might forget in an instant