2021-10-09
python学习
00
请注意,本文编写于 962 天前,最后修改于 962 天前,其中某些信息可能已经过时。

目录

1. 编写工作簿
2. 读取工作表
3. 使用数字格式
4. 使用公式
5. 合并/取消合并单元格
6. 插入图片
7. 折叠
8. 只读/只写模式
1. 只读
2. 只写

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

1. 编写工作簿

python
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. 读取工作表

python
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. 使用数字格式

python
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. 使用公式

python
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. 合并/取消合并单元格

python
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. 插入图片

python
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. 折叠

python
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. 只读

python
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. 只写

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 许可协议。转载请注明出处!