openpyxl操作excel表格

python 读写 excel 有好多选择， xlrd/xlwt、openpyxl。

之所以推荐两个库是因为这两个库分别操作的是不同版本的 excel，xlrd 操作的是 xls/xlxs 格式的 excel，
而 openpyxl 只支持 xlxs 格式的excel，openpyxl 使用起来会更方便一些，所以如果你只操作 xlxs 文件的话，
那么可以优先选择 openpyxl，如果要兼容 xls 的话，那就用 xlrd/xlwt 吧。
本处使用openpyxl来实现

# -*- coding: utf-8 -*-
from openpyxl import load_workbook
from openpyxl import Workbook
from openpyxl.chart import (PieChart , ProjectedPieChart, Reference)
from openpyxl.chart.series import DataPoint

data = [
    ['Pie', 'Sold'],
    ['Apple', 50],
    ['Cherry', 30],
    ['Pumpkin', 10],
    ['Chocolate', 40],
]

wb = Workbook()
ws = wb.active

for row in data:
    ws.append(row)

pie = PieChart()
labels = Reference(ws, min_col=1, min_row=2, max_row=5)
data = Reference(ws, min_col=2, min_row=1, max_row=5)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "Pies sold by category"

# Cut the first slice out of the pie
slice = DataPoint(idx=0, explosion=20)
pie.series[0].data_points = [slice]

ws.add_chart(pie, "D1")

ws = wb.create_sheet(title="Projection")

data = [
    ['Page', 'Views'],
    ['Search', 95],
    ['Products', 4],
    ['Offers', 0.5],
    ['Sales', 0.5],
]

for row in data:
    ws.append(row)

projected_pie = ProjectedPieChart()
projected_pie.type = "pie"
projected_pie.splitType = "val" # split by value
labels = Reference(ws, min_col=1, min_row=2, max_row=5)
data = Reference(ws, min_col=2, min_row=1, max_row=5)
projected_pie.add_data(data, titles_from_data=True)
projected_pie.set_categories(labels)

ws.add_chart(projected_pie, "A10")

from copy import deepcopy
projected_bar = deepcopy(projected_pie)
projected_bar.type = "bar"
projected_bar.splitType = 'pos' # split by position

ws.add_chart(projected_bar, "A27")

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

# -*- coding: utf-8 -*-
from openpyxl import load_workbook
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference, Series

wb = load_workbook('./sample.xlsx')
ws1=wb.active

wb = Workbook()
ws = wb.active
for i in range(10):
    ws.append([i])

values = Reference(ws, min_col=1, min_row=1, max_col=1, max_row=10)
chart = BarChart()
chart.add_data(values)
ws.add_chart(chart, "E15")

# Save the file
wb.save("./sample1.xlsx")

 

读取

from openpyxl import load_workbook

wb = load_workbook('balances.xlsx', read_only=False, data_only=True)
# 控制带有公式的单元格是否具有公式(默认值)   Excel上次读取工作表时存储的值

print(wb.sheetnames)  # ['New Title', 'Sheet', 'Mysheet']
wb1 = wb['New Title']
# ws = wb.active     #获取第一个sheet
# wb.get_sheet_by_name("New Title"  )

# wb1.guess_types = False
# wb1["D1"]="12%"
# wb1.guess_types = True    print ws["D1"].value会打印百分数b
# wb1.guess_types = False   print ws["D1"].value会打印小数

print(wb1['B4'].value)
print(wb1.cell(row=4, column=2).value)

print(wb1.max_row)  # 最大行数
print(wb1.max_column)  # 最大列数

# ######################## 获取行和列 ####################################
# 因为按行，所以返回A1, B1, C1这样的顺序
for row in wb1.rows:
    for cell in row:
        print(cell.value)

# A1, A2, A3这样的顺序
# for column in wb1.columns:    # read_only=False
#     for cell in column:
#         print(cell.value)

for row_cell in wb1['A1':'B3']:
    for cell in row_cell:
        print(cell.value)

# ######################## 设置字体 ####################################
from openpyxl.styles import Font, colors

bold_itatic_24_font = Font(name='等线', size=24, italic=True, color=colors.RED, bold=True, underline="single")
wb1['A1'].font = bold_itatic_24_font
wb1['A1'] = 'SX'

# ######################## 对齐方式 ####################################
from openpyxl.styles import Alignment

for row_cell in wb1['B2':'G10']:
    for cell in row_cell:
        cell.alignment = Alignment(horizontal='center', vertical='center')  # right,left

# ######################## 设置行高和列宽 ####################################
# 有时候数据太长显示不完，就需要拉长拉高单元格
# 第2行行高
wb1.row_dimensions[2].height = 40
# C列列宽
wb1.column_dimensions['C'].width = 30

# ######################## 合并和拆分单元格 ####################################
wb1.merge_cells('B1:G1')  # 合并一行中的几个单元格
"""
合并后只需要往第一个表格写数据
如果这些要合并的单元格都有数据，只会保留左上角的数据，其他则丢弃。
换句话说若合并前不是在左上角写入数据，合并后单元格中不会有数据。
"""
# wb1.unmerge_cells('A1:C3')


# ############# 设定单元格的边框、字体、颜色、大小和边框背景色 ###############
from openpyxl.styles import NamedStyle, Font, Border, Side, PatternFill

highlight = NamedStyle(name="highlight")
highlight.font = Font(name='微软雅黑', size=11, bold=False, italic=False, vertAlign=None, underline='none', strike=False,
                      color='FF000000')
highlight.fill = PatternFill("solid", fgColor="DDDDDD")  # 背景填充
bd = Side(style='thick', color="000000")
highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd)

print(dir(wb1["A1"]))
wb1["A1"].style = highlight
# ############# 常用的样式和属性设置 ###############


from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font

fill = PatternFill(fill_type="solid", start_color='FFEEFFFF', end_color='FF001100')

# 边框可以选择的值为：'hair', 'medium', 'dashDot', 'dotted', 'mediumDashDot', 'dashed', 'mediumDashed', 'mediumDashDotDot', 'dashDotDot', 'slantDashDot', 'double', 'thick', 'thin']
# diagonal 表示对角线
bd = Border(left=Side(border_style="thin", color='FF001000'),
            right=Side(border_style="thin", color='FF110000'),
            top=Side(border_style="thin", color='FF110000'),
            bottom=Side(border_style="thin", color='FF110000'),
            diagonal=Side(border_style=None, color='FF000000'),
            diagonal_direction=0, outline=Side(border_style=None, color='FF000000'),
            vertical=Side(border_style=None, color='FF000000'),
            horizontal=Side(border_style=None, color='FF110000'))

alignment = Alignment(horizontal='general', vertical='bottom', text_rotation=0, wrap_text=False, shrink_to_fit=False,
                      indent=0)
number_format = 'General'
protection = Protection(locked=True, hidden=False)

wb1["B5"].fill = fill
wb1["B5"].border = bd
wb1["B5"].alignment = alignment
wb1["B5"].number_format = number_format

wb.save('balances.xlsx')

　写

from openpyxl import Workbook

wb = Workbook()  # 默认有一个Sheet工作薄

# 创作一个新的工作薄
ws1 = wb.create_sheet('Mysheet')  # 在最后添加
ws2 = wb.create_sheet('Mysheeta', 0)  # 在第一个添加
ws2.sheet_properties.tabColor = "1072BA"  # 设定sheet的标签的背景颜色

target = wb.copy_worksheet(ws2)

# 修改工作薄的名称
ws2.title = "New Title"

# 查看工作薄的名称列表        ['New Title', 'Sheet', 'Mysheet']
print(wb.sheetnames)

# 写数据
ws2["A4"] = 4
ws2.cell(row=4, column=2, value=10)  # 按单元格来区分,从1开始

l = [1, 2, 3, 4, '', 6]
ws2.append(l)  # 在最下边,添加了一行
from openpyxl.drawing.image import Image

img = Image('e:\\1.png')
ws1.add_image(img, 'A1')

ws1.column_dimensions.group('A', 'D', hidden=True)  # 隐藏a到d列范围内的列

# Excel内置函数
"""
ws2['G5'] = '=SUM(A5:F5)'       #  一片区域:=SUM(A6:F10)
"""
# 保存
wb.save('balances.xlsx')