python处理excel（1）

本文内容全是根据官方文档整理。

1. 编写工作簿

from 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

2. 读取工作表

from openpyxl import load_workbook
# 加载
wb = load_workbook(filename = 'empty_book.xlsx')

# 获取sheet
sheet_ranges = wb['range names']

# 打印值
print(sheet_ranges['D18'].value)

3

3. 使用数字格式

import 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

4. 使用公式

from 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")

5. 合并/取消合并单元格

from 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")

6. 插入图片

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('1.jpg')
# add to worksheet and anchor next to cells
ws.add_image(img, 'A1')
wb.save('logo.xlsx')

7. 折叠

import 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')

8. 只读/只写模式

1. 只读

from 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

2. 只写

>>> 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

>>> 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')