Operate Excel with Python openpyxl

Create and write Excel files with Python.

There are various packages, but openpyxl was the easiest to use so far. official document (http://openpyxl.readthedocs.io/en/default/)

import

import openpyxl as px

Create / Save

Create New

wb = px.Workbook()

Read existing file

wb = px.load_workbook('FILEPATH')

Save

wb.save('FILEPATH')

Seat operation

Active seat

ws = wb.active

Create a new sheet

ws = wb.create_sheet()  #Created on the far right
ws = wb.create_sheet(0)  #Created on the far left
ws = wb.create_sheet(title='SHEETNAME')  #Specify the sheet name

Select from sheet name

ws = wb.get_sheet_by_name('SHEETNAME')

Worksheet name list (list)

sheetnames = wb.get_sheet_names()

Sheet name change

ws.title = 'New Title'

Basic cell operation

Cell writing

ws['A1'].value = 'Hello World'
from datetime import datetime as dt
ws.cell(row=2, column=1).value = dt.now()

Write multiple cells

i = 1234.5678
for row in ws.iter_rows('C1:E3'):
    for cell in row:
        cell.value = i
        i = i * 2

Cell selection (iterator)

ws.rows
ws.columns

Row or column selection

ws.column_dimensions['A']
ws.row_dimensions[1]

Change cell settings

Display format

ws['A2'].number_format = 'yyyy-mm-dd hh:mm:ss'
ws['C1'].number_format = u'#,##0.00;[Red]-#,##0.00'
ws['D1'].number_format = u'_ ¥* #,##0_ ;[Red]_ ¥* -#,##0_ '

Cell merge

ws.merge_cells('A5:B5')
ws.unmerge_cells('A5:B5')
ws.merge_cells(start_row=5,start_column=1,end_row=5,end_column=6)
ws.unmerge_cells(start_row=5,start_column=1,end_row=5,end_column=6)

comment

from openpyxl.comments import Comment
ws['C1'].comment = Comment(
    'This is the comment text',
    'Comment Author'
)

ws['C1'].comment.text  #Comment content
ws['C1'].comment.author  #Comment creator

font

from openpyxl.styles import Font
ws['A1'].font = Font(
    name='Calibri',
    size=12,
    bold=True,
    italic=False,
    color='FF000000'
)

Cell pattern

from openpyxl.styles import PatternFill
ws['A1'].fill = PatternFill(
    fill_type=None,
    start_color='FFFFFFFF',
    end_color='FF000000'
)

Cell border

from openpyxl.styles import Border, Side

#Enclosure
ws['B10'].border = Border(
    outline=True,
    left=Side(style='thin', color='FF000000'),
    right=Side(style='thick', color='FF000000'),
    top=Side(style='mediam, color='FF000000'),
    bottom=Side(style=None, color='FF000000')
)

#Diagonal
ws['B12'].border = Border(
    outline=True,
    diagonalUp=True,
    diagonalDown=True,
    diagonal=Side(style=None, color='FF000000')
)

style = [
    'dashDot', 'dashDotDot', 'dashed', 'dotted',
    'double', 'hair',
    'medium', 'mediumDashDot', 'mediumDashDotDot', 'mediumDashed',
    'slantDashDot',
    'thick', 'thin'
]

Cell character display method

from openpyxl.styles import Alignment
ws['A1'].alignment = Alignment(
    wrap_text=False,  #Wrap line break
    horizontal='general',  #Horizontal position
    vertical='bottom'  #Vertical position
)