本文内容全是根据官方文档整理。
pythonfrom openpyxl import Workbook
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.create_sheet(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)
wb.save(filename = dest_filename)
AA
pythonfrom openpyxl import load_workbook
# 加载
wb = load_workbook(filename = 'empty_book.xlsx')
# 获取sheet
sheet_ranges = wb['range names']
# 打印值
print(sheet_ranges['D18'].value)
3
pythonimport datetime
from openpyxl import Workbook
wb_name = 'my_one1.xlsx'
wb = Workbook()
ws = wb.active
# set date using a Python datetime
ws['A1'] = datetime.datetime(2019, 7, 21)
ws['A2'] = str(datetime.datetime.strptime("21/11/19","%d/%m/%y"))[:10]
print(ws['A1'].value)
print(ws['A2'].value)
ws['A1'].number_format
wb.save(filename = wb_name)
2019-07-21 00:00:00 2019-11-21
pythonfrom openpyxl import Workbook
wb = Workbook()
ws = wb.active
# add a simple formula
ws['A1'] = 1
ws['A2'] = 2
ws["A3"] = "=SUM(A1, A2)/2"
wb.save("formula.xlsx")
pythonfrom openpyxl.workbook import Workbook
wb = Workbook()
ws = wb.active
# 单列合并
# ws.merge_cells('A2:D2')
# ws.unmerge_cells('A2:D2')
# 多列合并
ws.merge_cells(start_row=2, start_column=1, end_row=4, end_column=4)
# ws.unmerge_cells(start_row=2, start_column=1, end_row=4, end_column=4)
wb.save("formula.xlsx")
pythonfrom 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('1.jpg')
# add to worksheet and anchor next to cells
ws.add_image(img, 'A1')
wb.save('logo.xlsx')
pythonimport openpyxl
wb = openpyxl.Workbook()
ws = wb.create_sheet()
ws.title = "big_data"
ws['A1'] = 234
ws['B1'] = 3234
ws['A2'] = 1234
ws['A4'] = 'A4'
ws['b9'] = 'b9'
ws.column_dimensions.group('C','D', hidden=True)
ws.row_dimensions.group(1,10, hidden=True)
wb.save('group.xlsx')
pythonfrom openpyxl import load_workbook
wb = load_workbook(filename='group.xlsx', read_only=True)
ws = wb['big_data']
for row in ws.rows:
print(row)
if len(row) >= 1:
print(row[1].value)
(<ReadOnlyCell 'big_data'.A1>, <ReadOnlyCell 'big_data'.B1>) 3234 (<ReadOnlyCell 'big_data'.A2>, <EmptyCell>) None () (<ReadOnlyCell 'big_data'.A4>, <EmptyCell>) None () () () () (<EmptyCell>, <ReadOnlyCell 'big_data'.B9>) b9
python>>> from openpyxl import Workbook
>>> wb = Workbook(write_only=True)
>>> ws = wb.create_sheet()
>>>
>>> # now we'll fill it with 100 rows x 200 columns
>>>
>>> for irow in range(100):
... ws.append(['%d' % i for i in range(200)])
>>> # save the file
>>> wb.save('new_big_file.xlsx') # doctest: +SKIP
python>>> from openpyxl import Workbook
>>> wb = Workbook(write_only = True)
>>> ws = wb.create_sheet()
>>> from openpyxl.cell import WriteOnlyCell
>>> from openpyxl.comments import Comment
>>> from openpyxl.styles import Font
>>> cell = WriteOnlyCell(ws, value="hello world")
>>> cell.font = Font(name='Courier', size=36)
>>> cell.comment = Comment(text="A comment", author="Author's Name")
>>> ws.append([cell, 3.14, None])
>>> wb.save('write_only_file.xlsx')
本文作者:mykernel
本文链接:
版权声明:本博客所有文章除特别声明外,均采用 BY-NC-SA 许可协议。转载请注明出处!