A package to read/write xlsx worksheet like dictionary, based on openpyxl.
- from pip
pip install pyxlsx- download package and run setup.py
python setup.py install- Create a new xlsx file and write to it
from pyxlsx import new_xlsx
with new_xlsx(filename) as wb:
ws = wb.create_sheet('sheet1')
# some operations
# or
wb = new_xlsx()
ws = wb.creat_sheet('sheet1') # create a new sheet with name 'sheet1'
# some operations
wb.save(filename)- Open an existing xlsx file
from pyxlsx import open_xlsx
with open_xlsx(filename) as wb:
ws1 = wb.active # get active sheet
ws2 = wb['sheet2']
# some operations
# or
wb = open_xlsx(filename)
ws = wb['sheet2']
# some operations
wb.save()
# to save as another file
wb.save(another_filename)- Append rows to a worksheet
ws = wb['sheet1']
ws.append(
["", "", "str('Unknown')", "float(4.5)", "int(500)"]
)
# keys can only be of type str
content1 = {
'id': '001',
'productName': 'pork',
'productType': 'meat',
'price': 2.5,
'weight': 1000,
}
content2 = {
'id': '002',
'productName': 'beef',
'productType': 'meat',
'price': 4.5,
'weight': 1000,
'origin': 'Australia'
}
# header is auto-generated from keys of the dict the first time append_by_header is called.
ws.append_by_header(content1)
# new header name will be append to header if append_header is True (default value)
ws.append_by_header(content2)
# below is the result of writing operation| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | str('Unknown') | float(30) | int(0) | |||
| 2 | id | productName | productType | price | weight | origin |
| 3 | 001 | pork | meat | 2.5 | 1000 | |
| 4 | 002 | beef | meat | 4.5 | 1000 | Australia |
- Read from / write to a worksheet by row Note: if there are duplicate header names, only the first would be used.
ws = wb['sheet1']
assert ws.header is None
ws.header_row = 2 # set the second row as worksheet header row
assert ws.header is not None
for row in ws.content_rows: # starting from row just below header row
print(row[1]) # row cell value can accessed by column number, if key is of type int
print(row['productName']) # row cell value can be accessed by header name, if key is of type of str
print(row['price'])
if row['productName'] == 'pork':
row[1] = '003' # change pork id to '003'
row['price'] = 3.5 # change pork price to 3.5
# output as below
# '001'
# 'pork'
# 2.5
# '002'
# 'beef'
# 4.5- Read from a worksheet by column
ws = wb['sheet1']
ws.header_row = 2
# get a full column
column_cells = ws['B']
for c in column_cells:
print(c.data) # 'pork', 'beef'
# get a content column (containing only cells below header) by header name,
# if key is of type str
name_column = ws.get_content_column('productName')
for c in name_column:
print(c.data) # 'pork', 'beef'
# get a content column by column number,
# if key is of type int
name_column = ws.get_content_column(2)
for c in name_column:
print(c.data) # 'pork', 'beef'- Read cell directly from Worksheet, Header, ContentRow
ws = wb['sheet1']
ws.header_row = 2
# access a cell by coordinate (row, column)
cell = ws.cell(2, 2)
print(cell.data) # 'productName'
# access a cell by header name if key is of type str
cell = ws.header.cell('productName')
print(cell.data) # 'productName'
# access a cell by column number
cell = ws.header.cell(1)
print(cell.data) # 'id'
for row in ws.content_rows:
cell = row.cell(1) # '001', '002'
print(cell.data)
cell = row.cell('productName')
print(cell.data) # 'pork', 'beef'- Read adjacent cells of a certain cell
cell = ws.cell(2, 2)
print(cell.top.data) # "str('Unknown')"
print(cell.left.data) # 'id'
print(cell.right.data) # 'productType'
print(cell.bottom.data) # 'pork'
for c in cell.vertical:
print(c.data) # 'productName', 'pork', 'beef'
for c in cell.horizontal:
print(c.data) # 'productName', 'productType', 'price', 'weigth', 'origin'