python的openpyxl增加sheet页 openpyxl添加边框

第三方模块  openpyxl

一、安装openpyxl模块

pip install openpyxl

  想要在文件中插入图片文件，需要安装pillow，安装文件：PIL-fork-1.1.7.win-amd64-py2.7.exe

　　· font(字体类)：字号、字体颜色、下划线等

　　· fill(填充类)：颜色等

　　· border(边框类)：设置单元格边框

　　· alignment(位置类)：对齐方式

　　· number_format(格式类)：数据格式

　　· protection(保护类)：写保护

2、 创建一个excel 文件，并写入内容

import random

class user:
    '''
    用户类
    '''
    def __init__(self, name, age, gender, hobby=None):
        self.name = name
        self.age = age
        self.gender = gender
        self.hobby = hobby

list1 = []
for i in range(10000): #实测，写入10000行，5列数据，耗时2s
    name = "用户%s" % i
    age = "%s岁" % (18 + i)
    gender = random.choice(["男", "女", '未知'])
    hobby = random.choice(["play basketball", "sing", "running"])
    obj_user = user(name, age, gender, hobby)
    list1.append(obj_user)

# =================================================================================
import datetime
from openpyxl import Workbook

# 1.创建工作薄对象
wb = Workbook()  # 创建工作薄文件对象

# 2.获取 工作表sheet
ws = wb.active  # 获取第一个sheet工作表

# 3.向工作表的单元格中填入数据
i = 0
for user in list1:
    ws['A%s' % (i + 1)] = datetime.datetime.now()  # 写入一个当前时间
    ws["B%s" % (i + 1)] = user.name
    ws['C%s' % (i + 1)] = user.age
    ws['D%s' % (i + 1)] = user.gender
    ws['E%s' % (i + 1)] = user.hobby
    i+=1

# 4.保存工作薄到文件
# wb.save("e:\\sample.xlsx")
wb.save("sample.xlsx")

效果如下:

 

三、sheet 工作表相关 操作 (创建、获取、复制)

from openpyxl import Workbook

# 1.实例化一个工作簿对象，工作薄里默认有一个标题是'sheet'的工作表
wb = Workbook()

# 2.创建多个指定名字的工作表(sheet)
ws1 = wb.create_sheet(title="New Title")  # 创建一个sheet (title,index默认都为None)
ws1.title = "New Title"  # 给sheet设定标题

ws2 = wb.create_sheet(title="Mysheet", index=0)  # index是设定sheet的插入位置 默认插在后面
ws2.title = u"用户信息"  # 给sheet设定中文标题时，必须是Unicode

ws1.sheet_properties.tabColor = "1072BA"  # 设定sheet的标签的背景颜色

# 获取某个指定标题名称的sheet对象
print("方式1:",wb.get_sheet_by_name(u"用户信息"))
print("方式2:",wb["New Title"])

# 复制一个sheet
wb["New Title"]["A1"] = "zeke" #向指定工作表的指定单元格，写入数据
source = wb["New Title"] # 获取指定标题名称的工作表
target = wb.copy_worksheet(source) #将指定的源工作表复制一个
target.title=u'复制表'

print("4:","*" * 50)

# 获取全部sheet的标题名称(列表)，遍历sheet名字
print(wb.sheetnames)  #工作薄里默认有一个标题是'sheet'的工作表

for sheet_name in wb.sheetnames: # 方式1
    print(sheet_name)

print("5:","*" * 50)
for sheet in wb: # 方式2
    print(sheet.title)


# 4.保存到文件
# wb.save("e:\\sample.xlsx")
wb.save("sample-2.xlsx")

 

 四、单元格 相关操作

from openpyxl import Workbook

wb = Workbook()
ws1 = wb.create_sheet("Mysheet")  #创建一个sheet

# 方式1: 指定单元格(如:A1)写入数据
ws1["A1"]=123.11
ws1["B2"]="test contest"

# 方式2: 指定行号、列号，向单元格中 写入数据
d = ws1.cell(row=4, column=2, value=10) #获取一个单元格对象 (value默认为None)
print (ws1["A1"].value) # 123.11
print (ws1["B2"].value) # test contest
print ("d更改前:",d.value)    # d: 10
d.value=20
print ("d更改后:",d.value)    # d: 20

# 保存文件
# wb.save("e:\\sample.xlsx")
wb.save("sample-3.xlsx")

 五、操作批量的 单元格

from openpyxl import Workbook
wb = Workbook()

ws1 = wb.create_sheet("Mysheet")           #创建一个sheet

ws1["A1"]=1
ws1["A2"]=2
ws1["A3"]=3
ws1["A4"]=11

ws1["B1"]=4
ws1["B2"]=5
ws1["B3"]=6
ws1["b4"]=12

ws1["C1"]=7
ws1["C2"]=8
ws1["C3"]=9
ws1["C4"]=13

#操作单列
print("操作单列 ",'*'*50)
print (ws1["A"])
for cell in ws1["A"]:
    print (cell.value)

#操作多列,获取每一个单元格的值
print("操作多列 ",'*'*50)
print (ws1["A:C"]) # 从A列到C列
for column in ws1["A:C"]:
    for cell in column:
        print (cell.value)

#操作多行
print("操作多行 ",'*'*50)
row_range = ws1[1:3]
print (row_range)
for row in row_range:
    for cell in row:
        print (cell.value)

print ('根据限定的行、列 范围，获取数据 ',"*"*50)
for row in ws1.iter_rows(min_row=1, min_col=1, max_col=3, max_row=3): #根据限定的行、列 范围，获取数据 (除了.iter_rows,还有iter_cols)
    for cell in row:
        print (cell.value)

#获取所有行
print("获取所有行 ",'*'*50)
print (ws1.rows)
for row in ws1.rows:
    print (row)

#获取所有列
print("获取所有列 ",'*'*50)
print (ws1.columns)
for col in ws1.columns:
    print (col)

#获取 最大行数，最大列数
print("获取最大行数，最大列数 ",'*'*50)
print('max_row:',ws1.max_row)
print('max_column:',ws1.max_column)

wb.save("sample-4.xlsx")

View Code

 六、打开已经存在的excel文件，更改单元格的值

from openpyxl import Workbook
from openpyxl import load_workbook
wb = load_workbook('sample-5.xlsx') # 载入已经存在的excel文件
wb.guess_types = True # 猜测格式类型
ws=wb.active # 获取默认工作簿 <title为 'sheet'>
print(wb.get_sheet_names()) # 获取工作表sheet的title列表
ws["D1"]="12%"
print (ws["D1"].value) #12%

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

 

 七、获取所有 行/列 对象

from openpyxl import Workbook
from openpyxl import load_workbook

wb = load_workbook('e:\\sample.xlsx')
ws = wb.active
rows = []
for row in ws.iter_rows():  # iter_rows()的结果是 生成器对象。iter_cols():列
    rows.append(row)

print(rows)  # 所有行
print(rows[0])  # 获取第一行
print(rows[0][0])  # 获取第一行第一列的单元格对象
print(rows[0][0].value)  # 获取第一行第一列的单元格对象的值

print(rows[len(rows) - 1])  # 获取最后行 print rows[-1]
print(rows[len(rows) - 1][len(rows[0]) - 1])  # 获取第后一行和最后一列的单元格对象
print(rows[len(rows) - 1][len(rows[0]) - 1].value)  # 获取第后一行和最后一列的单元格对象的值

 

 八、单元格中 使用公式

from openpyxl import Workbook
from openpyxl import load_workbook

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

ws1["A1"] = 1
ws1["A2"] = 2
ws1["A3"] = 3

ws1["A4"] = "=SUM(1, 1)"
ws1["A5"] = "=SUM(A1:A3)"

print(ws1["A4"].value)  # 打印的是公式内容，不是公式计算后的值,程序无法取到计算后的值
print(ws1["A5"].value)  # 打印的是公式内容，不是公式计算后的值,程序无法取到计算后的值

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

九、合并单元格

from openpyxl import Workbook
from openpyxl import load_workbook

wb = load_workbook('sample.xlsx')
ws=wb.active # 获取 活动工作表 [当关闭excel文档时，最后显示的sheet就是活动工作表]

# 方式一:
ws.merge_cells('A2:D3')   #合并单元格
# ws.unmerge_cells('A2:D3') #拆分单元格     # 脚本单独执行拆分操作会报错，需要重新执行合并操作再拆分

# 范式二:
ws.merge_cells(start_row=5,start_column=1,end_row=10,end_column=4)
# ws.unmerge_cells(start_row=5,start_column=1,end_row=10,end_column=4)

# Save the file
wb.save('sample.xlsx')

十、插入图片

from openpyxl import load_workbook
from openpyxl.drawing.image import Image #引入模块  # 提前安装好Pillow 模块。 pip install Pillow

wb = load_workbook('sample.xlsx')
ws1=wb.active # 获取活动工作表

img = Image('11.bmp') #实例化一个Image对象
ws1.add_image(img, 'C5') # 图片左上角定位点 C5单元格

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

 

十一、插入条形图/柱状图

 

from openpyxl import load_workbook
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference, Series

wb = load_workbook('sample.xlsx') #载入工作簿
ws=wb.active  # 获取活动工作表

# wb = Workbook()   # 实例化一个工作簿
# ws = wb.active    # 获取活动工作表

for i in range(20,50,2):
    ws.append([i]) # 在第一列里追加元素。.append()里必须是列表，或者 字典
# ws.append([111,112,113])  #在第一列里追加111，同行的下一列追加112，同行的再下一行，追加113
# ws.append({"D":23,"F":'value2'})  # 在D列里追加23，在F列里追加value2

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")  # 向工作表中插入 条形图/柱状图 ，定位点E15单元格

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

 

 

十二、插入饼状图

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 #获取活动工作表
ws.title="饼图"

for row in data:
    ws.append(row)  #给工作表里追加数据

# 实例化一个饼图对象
pie = PieChart()
# 创建饼图的标签数据源
labels = Reference(ws, min_col=1, min_row=2, max_row=5)
# 创建饼图的 data数据源
data = Reference(ws, min_col=2, min_row=1, max_row=5)
# 给饼图对象添加data数据源
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) #实例化一个DataPoint对象 作用??
# # 设置饼图的第一个分片的DataPoint
# pie.series[0].data_points = [slice]

# 把饼图，插入到工作表的指定位置
ws.add_chart(pie, "D1")


# 再创建一个指定标题的工作表 ========================================================================
ws1 = wb.create_sheet(title="Projection-饼图2")

# 各成员数值相差较大，使用饼图时，数值相对于最大值显得很小时，在饼图里就不容易分辨
data = [
    ['Page', 'Views'],
    ['Search', 95],
    ['Products', 4],
    ['Offers', 0.5],
    ['Sales', 0.5],
]

for row in data:
    ws1.append(row) #给工作表里追加数据

# 实例化一个ProjectedPieChart对象
projected_pie = ProjectedPieChart()
# 设置 ProjectedPieChart对象的type
projected_pie.type = "pie"    #可以设置:['pie', 'bar']
# 设置 ProjectedPieChart对象的 splitType
projected_pie.splitType = "val" # split by value   可以设置的有:['auto', 'cust', 'percent', 'pos', 'val']

# 创建饼图的标签数据源
labels = Reference(ws1, min_col=1, min_row=2, max_row=5)
# 创建饼图的 data数据源
data = Reference(ws1, min_col=2, min_row=1, max_row=5)
# 给饼图对象添加data数据源
projected_pie.add_data(data, titles_from_data=True)
# 给饼图 设置类别标签
projected_pie.set_categories(labels)

# 把饼图，插入到工作表的指定位置
ws1.add_chart(projected_pie, "A10")

from copy import deepcopy  # 深拷贝  ==============================================================================
projected_bar = deepcopy(projected_pie) # 复制一个 图表对象
projected_bar.type = "bar" #更改图表类型
projected_bar.splitType = 'val' # split by position  可以设置的有:['auto', 'cust', 'percent', 'pos', 'val']
# 把图，插入到工作表的指定位置
ws1.add_chart(projected_bar, "A27")

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

饼状图

 

        

 

 

 

 十三、创建表格区域，并设置样式

from openpyxl import load_workbook
from openpyxl import Workbook
from openpyxl.worksheet.table import Table, TableStyleInfo

wb = Workbook()
ws = wb.active

data = [
    ['Apples', 10000, 5000, 8000, 6000],
    ['Pears',   2000, 3000, 4000, 5000],
    ['Bananas', 6000, 6000, 6500, 6000],
    ['Oranges',  500,  300,  200,  700],
]

# add column headings. NB. these must be strings
ws.append(["Fruit", "2011", "2012", "2013", "2014"])
for row in data:
    ws.append(row)

# 实例化一个Table对象，并 指定范围
tab = Table(displayName="Table1", ref="A1:E5")

# Add a default style with striped rows and banded columns
style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=True,
                       showLastColumn=True, showRowStripes=True, showColumnStripes=True)
#第一列是否和样式第一行颜色一行，第二列是否···
#是否隔行换色，是否隔列换色
tab.tableStyleInfo = style

# 把table添加进工作表中
ws.add_table(tab)

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

表格

 

 

十四、给单元格设置 字体、颜色

from openpyxl import Workbook
from openpyxl.styles import colors
from openpyxl.styles import Font

wb = Workbook()
ws = wb.active

# 获取指定的单元格对象
a1 = ws['A1']
d4 = ws['D4']

#  实例化 字体对象   （实例化时，可指定字体名称，字体颜色，是否倾斜，underline:下划线）
'''
name 字体名称: 如 '微软雅黑'
color 字体颜色: 如:color.RED 或者  color="FFBB00"，颜色编码也可以设定颜色
underline 下划线:  {'singleAccounting', 'doubleAccounting', 'single', 'double'}
bold 加粗: True/False
italic 字体倾斜: True/False
'''
ft = Font(color=colors.RED,name='微软雅黑',underline='single',bold=True)
# ft = Font(color='08ACC5')

# 给单元格设置字体
d4.font = ft
d4.value="test"

# If you want to change the color of a Font, you need to reassign it::

#italic 倾斜字体
a1.font = Font(color=colors.RED, italic=True)
a1.value = "abc"

# Save the file
wb.save("sample-7 单元格.xlsx")

View Code

 

十五、设置 行和列的字体

(测试失败!)

 

十六、设置单元格 的边框、字体、颜色、大小和边框背景色

from openpyxl.styles import NamedStyle, Font, Border, Side,PatternFill
from openpyxl import Workbook
from openpyxl import load_workbook

wb = load_workbook('sample.xlsx') # 载入已经存在的excel文件

# wb = Workbook()
ws = wb.active

highlight = NamedStyle(name="highlight")

# 字体
highlight.font = Font(bold=True, size=15,color= "ff0100")
# 填充
highlight.fill = PatternFill("solid", fgColor="DDDDDD")#背景填充
# 边框
bd = Side(style='thick', color="000000")
highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd)

# 设置单元格 样式
ws["A1"].style =highlight

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

View Code

 

 

 十七、常用样式和属性设置

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

wb = Workbook()
ws = wb.active

# 字体
ft = Font(name=u'微软雅黑',
          size=11,
          bold=False,
          italic=False,
          vertAlign=None,
          underline='none',
          strike=False,
          color='FF000000')

# 背景填充
fill = PatternFill(fill_type="solid",
                   start_color=colors.GREEN,
                   ) #end_color=colors.BLUE

# 边框可以选择的值为：'hair', 'medium', 'dashDot', 'dotted', 'mediumDashDot', 'dashed', 'mediumDashed', 'mediumDashDotDot', 'dashDotDot', 'slantDashDot', 'double', 'thick', 'thin']
# diagonal 表示对角线
bd = Border(left=Side(border_style="thin",
                      color=colors.RED),
            right=Side(border_style="thick",
                       color='FF110000'),
            top=Side(border_style="thin",
                     color=colors.GREEN),
            bottom=Side(border_style="thick",
                        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)

ws["B5"].font = ft
ws["B5"].fill = fill
ws["B5"].border = bd
ws["B5"].alignment = alignment
ws["B5"].number_format = number_format

ws["B5"].value = "zeke"

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

View Code