office

备注

将py-blog的tests/test_openpyxl内容全部迁到这里

quickstart

wb = Workbook()

# grab the active worksheet
ws = wb.active

# Data can be assigned directly to cells
ws['A1'] = 42

# Rows can also be appended
ws.append([1, 2, 3])

# Python types will automatically be converted
import datetime
ws['A2'] = datetime.datetime.now()

# Save the file
wb.save(p / "sample.xlsx")
return p / "sample.xlsx"

读取文件

# 标准模式
# ---------------------------
wb = load_workbook(filename)
print(wb)
# 只读模式
# ---------------------------
wb = openpyxl.load_workbook(filename='large_file.xlsx', read_only=True)
print(wb)
# 只写模式
# ---------------------------
wb = openpyxl.load_workbook(filename='large_file.xlsx', write_only=True)
print(wb)

# 读取sheet
print(wb.sheetnames)
# 选择工作表
ws = wb.active
print(ws.title)
print(ws.max_row)
print(ws.max_column)

# 设置缩放比例，设为85%
ws.views.sheetView[0].zoomScale = 85

# 修改工作表标签颜色
ws.sheet_properties.tabColor = "1072BA"

# 从表中选择单元格
cell = ws.cell(row=1, column=2)
# cell = ws["B1"]

ws = wb.active

# 获取A1到C3的单元格对象
print(ws['A1':'C3'])

# 获取C列
print(ws['C'])

# 获取多列
print(ws['C:D'])

# 获取第10行
print(ws[10])

# 获取多行
print(ws[10:20])

# 单元格取值
print(cell.value)

# 获取单元格行和列
print(cell.row, cell.column)

print(cell.coordinate)

assert cell.value is None
for i in range(1, 8, 2):
    assert ws.cell(row=i, column=2).value is None
# 获取有效单元格的最大行和最大列
assert ws.max_row == 20
assert ws.max_column == 4

# 把数据追加到sheet(使用dataframe_to_rows)
data = {'one': [1., 2., 3., 4.],
        'two': [4., 3., 2., 1.]}

df = pd.DataFrame(data)
for r in dataframe_to_rows(df, index=False, header=False):
    ws.append(r)

data = [(1, 2, 3, 4, 5, 6), (7, 8, 9, 10, 11, 12)]
for row in data:
    ws.append(row)

# 修改数据格式
font = Font(name='Microsoft Sans Serif', size=10, family=2, b=False, i=False, color=Color(theme=1))
alignment = Alignment(horizontal='center', vertical='center')

# 填充格式
fill = PatternFill(fill_type='solid', fgColor='8EA9DB')
border = Border(left=Side(border_style='thin', color='FF000000'),
                right=Side(border_style='thin', color='FF000000'),
                top=Side(border_style='thin', color='FF000000'),
                bottom=Side(border_style='thin', color='FF000000')
                )
for cols in ws.iter_cols(min_row=2, max_row=ws.max_row, min_col=20, max_col=ws.max_column):
    for cell in cols:
        cell.number_format = '0.00000000'
        # 填充颜色
        cell.font = font
        cell.alignment = alignment
        cell.fill = fill
        cell.border = border

# 内置数字格式
assert 'BUILTIN_FORMATS' in dir(numbers)

# 创建
wb.create_sheet(index=2, title='Middle Sheet')

# 删除
wb.remove(wb['Middle Sheet'])
# 或
# del wb['Middle Sheet']

# 创建工作表的副本
source = wb.active
target = wb.copy_worksheet(source)
# 如果工作簿以只读或只写模式打开，则无法复制工作表

# 在第7行插入一行
ws.insert_rows(7)
# 插入列
ws.insert_cols(7)

ws.delete_rows(7)
# 删除列
ws.delete_cols(7)

# 把D4:F10范围内的单元格上移一行，右移两列。单元格将覆盖任何现有的单元格。
ws.move_range("D4:F10", rows=-1, cols=2)

# 把公式中的相对引用移动一行和一列
ws.move_range("G4:H10", rows=1, cols=1, translate=True)

from openpyxl.utils import get_column_letter, column_index_from_string

# 数字转字母
get_column_letter(1)

# 字母转数字
column_index_from_string('A')

# 保存工作簿
wb.save(tmp_path / 'workbookName.xlsx')