Simple Usage (Excel for Python)

[jackjindtnt]

Simple usage Write a workbook

from openpyxl import Workbook from openpyxl.compat import range from openpyxl.utils import get_column_letter

wb = Workbook()

dest_filename = 'empty_book.xlsx'

ws1 = wb.active ws1.title = "range names"

for row in range(1, 40): ... ws1.append(range(600))

ws2 = wb.create_sheet(title="Pi")

ws2['F5'] = 3.14

ws3 = wb.createsheet(title="Data") for row in range(10, 20): ... for col in range(27, 54): ... = ws3.cell(column=col, row=row, value="{0}".format(get_column_letter(col))) print(ws3['AA10'].value) AA wb.save(filename = dest_filename) Read an existing workbook from openpyxl import load_workbook wb = load_workbook(filename = 'empty_book.xlsx') sheet_ranges = wb['range names'] print(sheet_ranges['D18'].value) 3 Note

There are several flags that can be used in load_workbook.

guess_types will enable or disable (default) type inference when reading cells. data_only controls whether cells with formulae have either the formula (default) or the value stored the last time Excel read the sheet. keep_vba controls whether any Visual Basic elements are preserved or not (default). If they are preserved they are still not editable. Warning

openpyxl does currently not read all possible items in an Excel file so images and charts will be lost from existing files if they are opened and saved with the same name. Using number formats

import datetime from openpyxl import Workbook wb = Workbook() ws = wb.active

set date using a Python datetime

ws['A1'] = datetime.datetime(2010, 7, 21)

ws['A1'].number_format 'yyyy-mm-dd h:mm:ss'

You can enable type inference on a case-by-case basis

wb.guess_types = True

set percentage using a string followed by the percent sign

ws['B1'] = '3.14%' wb.guess_types = False ws['B1'].value 0.031400000000000004

ws['B1'].number_format '0%' Using formulae from openpyxl import Workbook wb = Workbook() ws = wb.active

add a simple formula

ws["A1"] = "=SUM(1, 1)" wb.save("formula.xlsx") Warning

NB you must use the English name for a function and function arguments must be separated by commas and not other punctuation such as semi-colons. openpyxl never evaluates formula but it is possible to check the name of a formula:

from openpyxl.utils import FORMULAE "HEX2DEC" in FORMULAE True If you’re trying to use a formula that isn’t known this could be because you’re using a formula that was not included in the initial specification. Such formulae must be prefixed with xlfn. to work.

Merge / Unmerge cells When you merge cells all cells but the top-left one are removed from the worksheet. See Styling Merged Cells for information on formatting merged cells.

from openpyxl.workbook import Workbook

wb = Workbook() ws = wb.active

ws.merge_cells('A1:B1') ws.unmerge_cells('A1:B1')

or

ws.merge_cells(start_row=2,start_column=1,end_row=2,end_column=4) ws.unmerge_cells(start_row=2,start_column=1,end_row=2,end_column=4) Inserting an image from openpyxl import Workbook from openpyxl.drawing.image import Image

wb = Workbook() ws = wb.active ws['A1'] = 'You should see three logos below'

create an image

img = Image('logo.png')

add to worksheet and anchor next to cells

ws.add_image(img, 'A1') wb.save('logo.xlsx') Fold columns (outline) import openpyxl wb = openpyxl.Workbook() ws = wb.create_sheet() ws.column_dimensions.group('A','D', hidden=True) wb.save('group.xlsx')

[jackjindtnt]

NumPy Support openpyxl has builtin support for the NumPy types float, integer and boolean. DateTimes are supported using the Pandas’ Timestamp type.

Working with Pandas Dataframes The openpyxl.utils.dataframe.dataframe_to_rows() function provides a simple way to work with Pandas Dataframes:

from openpyxl.utils.dataframe import dataframe_to_rows wb = Workbook() ws = wb.active

for r in dataframe_to_rows(df, index=True, header=True): ws.append(r) While Pandas itself supports conversion to Excel, this gives client code additional flexibility including the ability to stream dataframes straight to files.

To convert a dataframe into a worksheet highlighting the header and index:

wb = Workbook() ws = wb.active

for r in dataframe_to_rows(df, index=True, header=True): ws.append(r)

for cell in ws['A'] + ws[1]: cell.style = 'Pandas'

wb.save("pandas_openpyxl.xlsx") Alternatively, if you just want to convert the data you can use write-only mode:

from openpyxl.cell.cell import WriteOnlyCell wb = Workbook(write_only=True) ws = wb.create_sheet()

cell = WriteOnlyCell(ws) cell.style = 'Pandas'

def format_first_row(row, cell):

for c in row:
    cell.value = c
    yield cell

rows = dataframe_to_rows(df) first_row = format_first_row(next(rows), cell) ws.append(first_row)

for row in rows: row = list(row) cell.value = row[0] row[0] = cell ws.append(row)

wb.save("openpyxl_stream.xlsx") This code will work just as well with a standard workbook.

Converting a worksheet to a Dataframe To convert a worksheet to a Dataframe you can use the values property. This is very easy if the worksheet has no headers or indices:

df = DataFrame(ws.values) If the worksheet does have headers or indices, such as one created by Pandas, then a little more work is required:

data = ws.values cols = next(data)[1:] data = list(data) idx = [r[0] for r in data] data = (islice(r, 1, None) for r in data) df = DataFrame(data, index=idx, columns=cols)