openpyxl学习笔记（2020-08-09）——打开文件/简单写入/折叠隐藏

打开已存在文件及简单写入

昨天学了如何简单地使用openpyxl新建xlsx文件及创建/操作sheet。

先简单学习一下如何打开已存在xlsx文件

主要就是使用了load_workbook

from openpyxl import load_workbook

wb2 = load_workbook('existed.xlsx')
print(wb2.sheetnames)

在workbook中写入

from openpyxl import Workbook
from openpyxl.utils import get_column_letter

wb = Workbook()

dest_filename = 'emptybook.xlsx'  # 给新建的xlsx文件命名，在最后保存时指定给filename

ws1 = wb.active  # 直接使用默认的sheet
ws1.title = 'range name'

for row in range(1, 40):
	ws1.append(range(500))
# 遍历给1-39行分别写入500列数字

ws2 = wb.create_sheet(title='Pi')  # 自定义一个sheet并命名

ws2['F5'] = 3.14  # 给单元格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)))
	# 给指定单元格写入所在列的字母，即通过get_column_letter获取col的字母信息

wb.save(filename = dest_filename)

折叠隐藏

import openpyxl

wb = openpyxl.Workbook()
ws = wb.create_sheet()  # 如不指定sheet名，默认从sheet1开始

ws.column_dimensions.group('A', 'D', hidden=True)  # 折叠列A-D
ws.row_dimensions.group(1, 10, hidden=True)  # 折叠行1-10

wb.save('fold.xlsx')

插入图片

from openpyxl import Workbook
from openpyxl.drawing.image import Image

wb = Workbook()
ws = wb.active

ws['A1'] = 'SEE THE IMAGE BELOW'

img = Image('图片路径')

ws.add_image(img, 'A5')  # 将图片放置在A5单元格上

wb.save('insert image.xlsx')

合并单元格/取消合并

from openpyxl 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.merge_cells(start_row=6, start_column=7, end_row=8, end_column=9)  # 合并
ws.unmerge_cells(start_row=6, start_column=7, end_row=8, end_column=9)  #取消合并

wb.save('merge unmerge.xlsx')

注意：取消合并要和合并的单元格范围相同，如果要缩小或扩大范围需要先取消再合并

使用公式

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws['A1'] = "=SUM(1, 1)"

wb.save('formulae.xlsx')

查看公式是否存在

from openpyxl.utils import FORMULAE

print('HEX2DEC' in FORMULAE)

只读与只写

from openpyxl import load_workbook

wb = load_workbook('formulae.xlsx', read_only=True)
ws = wb['sheet']  # read_only模式下，需要指定已存在的sheet名

print(ws.['A1'].value)

from openpyxl import Workbook

wb = Workbook(write_only=True)
ws = wb.create_sheet()

for row in range(19):
	ws.append(['%d' % i for i in range(45)])
# 还可以设定写入内容的styles及comments
from openpyxl.cell import WriteOnlyCell
from openpyxl.comments import Comment
from openpyxl.styles import Font

cell_style = WriteOnlyCell(ws, value='hello')
cell_style.font = Font(name='Courier', size=36)
cell_style.comment = Comment(text='A comment', author="Authors' names")

ws.append([cell_style, 3.14, None])  # 这里是在上面内容后一行写入三个值：hello，3.14和None， 其中None即为空单元格

wb.save('read or write only.xlsx')

插入行列/删除行列

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

ws.insert_rows(3)  # 默认插入一行或一列
ws.insert_cols(3)

ws.delete_rows(4)  # 默认删除一行或一列
ws.delete_cols(4)

ws.move_range('D3') # 删除一个单元格
ws.move_range('D3', translate=True)  # 连同单元格里的公式也删除

# 因为是新建文件，没有数据，所以在这里并没有什么用

不积跬步，无以至千里，不积小流，无以致江海