pip install openpyxl
from openpyxl import Workbook
wb = Workbook() # create a workbook with a worksheet
ws = wb.active # get the created worksheet
ws_1 = wb.create_sheet('Sheet_1') # insert at the end
ws_2 = wb.create_sheet('Sheet_2', 0) # insert at the beginning
ws_3 = wb.create_sheet('Sheet_3', -1) # insert at the second from the end
ws.title = '1' # set up title for a work sheet
ws.sheet_properties.tabColor = "1072BA" # set up tab color
wb['Sheet_1'] # access a specif work sheet by title
# access each seet
for sheet in wb:
print(sheet.title)
Sheet_2 Sheet Sheet_3 Sheet_1
# access a cell
cell = wb['Sheet1']['A2']
cell.value
# access by row and column, start from 1
cell = wb['Sheet1'].cell(row=2, column=1)
cell.value
'Mortage'
# set cell value
wb['Sheet1']['A2'] = 'Mortage_temp'
cell = wb['Sheet1']['A2']
cell.value = 10
cell = wb['Sheet1'].cell(row=2, column=1, value=10)
cell.value
10
# access column
column = wb['Sheet1']['A']
for cell in column:
print(cell.value)
# access row
row = wb['Sheet1'][1]
for cell in row:
print(cell.value)
# access range
cell_range = wb['Sheet1']['H2':'J8']
for row in cell_range:
for cell in row:
print(cell.value, end=', ')
print()
913.53, 913.53, 913.53, 65.9, 58.4, 64.38, 67.35, 47.1, 47.1, None, None, 55.5, 60.79, 60.79, 60.79, None, None, None, None, None, None,
# copy a sheet
ws_4 = wb.copy_worksheet(ws)
ws_4.title = 'Sheet_4'
# formula
wb['Sheet1']['A1'] = "=SUM(E2, E25)"
# hide columns
wb['Sheet1'].column_dimensions.group('A','D', hidden=True)
# hide rows
wb['Sheet1'].row_dimensions.group(1,10, hidden=True)
for row in range(1, 40):
wb['Sheet1'].append(range(600))
for row in range(1, 10):
for col in range(1, 5):
wb['Sheet1'].cell(row=row, column=col, value=row*col)
# insert a row
wb['Sheet1'].insert_rows(2)
row = wb['Sheet1'][2]
for cell in row:
cell.value = 10
# insert a column
wb['Sheet1'].insdrt_cols(2)
# delete rows
wb['Sheet1'].delete_rows(6, 3)
# delete columns
wb['Sheet1'].delete_cols(6, 3)
from openpyxl import load_workbook
wb = load_workbook('Payment.xlsx')
wb.save("sample.xlsx")
import pandas as pd
data = {
'apples': [3, 2, 0, 1],
'oranges': [0, 3, 7, 2]
}
df = pd.DataFrame(data)
# convert a DataFrame to a work book
from openpyxl.utils.dataframe import dataframe_to_rows
wb = Workbook()
ws = wb.active
for r in dataframe_to_rows(df, index=False, header=True):
ws.append(r)
wb.save("sample.xlsx")
# convert work sheet to DataFrame
ws = wb.active
df = pd.DataFrame(ws.values)
df.head()
| 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | None | Start | Pay Method | July | August | September | October | November | December | January | February | March | April |
| 1 | Mortage | 2020-06-26 00:00:00 | Online | None | 913.53 | 913.53 | 913.53 | 913.53 | 913.53 | 913.53 | 913.53 | 913.53 | None |
| 2 | Electricity | 2020-06-29 00:00:00 | Online | None | 57.28 | 114.58 | 100.38 | 65.9 | 58.4 | 64.38 | 36.75 | 33.27 | 30.03 |
| 3 | Water and Sewage | 2020-06-30 00:00:00 | None | 51.41 | 44.6 | 44.6 | 44.6 | 67.35 | 47.1 | 47.1 | 47.1 | 47.1 | None |
| 4 | Disposal | 2020-07-01 00:00:00 | Auto Charge to Capital One | 55.5 | None | None | 55.5 | None | None | 55.5 | None | None | None |
from openpyxl.chart import BarChart, Reference, Series
values = Reference(wb['Sheet1'], min_col=5, min_row=2, max_col=5, max_row=17)
chart = BarChart()
chart.add_data(values)
wb['Sheet1'].add_chart(chart, "E15")
wb.save("SampleChart.xlsx")
from openpyxl import Workbook
from openpyxl.worksheet.table import Table, TableStyleInfo
wb = Workbook()
ws = wb.active
data = [
['Apples', 10000, 5000, 8000, 6000],
['Pears', 2000, 3000, 4000, 5000],
['Bananas', 6000, 6000, 6500, 6000],
['Oranges', 500, 300, 200, 700],
]
# add column headings. NB. these must be strings
ws.append(["Fruit", "2011", "2012", "2013", "2014"])
for row in data:
ws.append(row)
tab = Table(displayName="Table1", ref="A1:E5")
# Add a default style with striped rows and banded columns
style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False,
showLastColumn=False, showRowStripes=True, showColumnStripes=True)
tab.tableStyleInfo = style
ws.add_table(tab)
wb.save("table.xlsx")