Python自动化操作Excel表格

一、Python打开及读取Excel表格内容

二、Python向Excel表格中写

三、批量调整字体、样式

四、编程生成Excel内图表

一、Python打开及读取Excel表格内容
#打开以及读取Excel表格内容
#列：column 行：row 格子：cell 表：sheet
#Excel中从1开始计数
from openpyxl import load_workbook
 
workbook = load_workbook(filename='aaa.xlsx')#只能打开存在表格
 
print (workbook.sheetnames)#获取Sheel名称
sheet = workbook['Sheet1']#通过Sheet名称获取表格
print(sheet.dimensions)#获得表格尺寸大小
 
sheet = workbook.active#打开活跃的/唯一的表格
#cell = sheet['A1']#获取A1格子
#.row行数
#.column列数
#.coordinate 坐标
cell = sheet.cell(row=1, column=1)#可用行数和列数定位某个格子
print(cell.value)#该格子的数据
print(cell.row,cell.column, cell.coordinate)#获得某个格子的行数、列数、坐标
cell = sheet['A1:A5']#获得一系列格子
print(cell)
 
#.iter_rows(min_row=最低行数,max_row=最高行数,min_col=最低列数,max_col=最高列数)
#指定行和列的范围，按行获取
#按行获取
for row in sheet.iter_rows(min_row=2,max_row=3,min_col=1,max_col=2):
    for cell in row:
        print(cell)#第二行第一列 到 第三行到第二列
print('-------------------------------------')
#按列获取
for col in sheet.iter_cols(min_row=2,max_row=3,min_col=1,max_col=2):
    for cell in col:
        print(cell)#第二行第一列 到 第三行到第二列
print('-------------------------------------')
#迭代整个表格的所有行 .rows
for row in sheet.rows:
    for cell in row:
        print(cell)
二、Python向Excel表格中写
from openpyxl.utils import FORMULAE#查看openpyxl支持的公式
from openpyxl import Workbook#创建新的Excel表格文件
用Python列表数据插入一行 sheet.append(Python列表) 接在表格内已有数据后面
插入公式 sheet['F1002'] = '=AVERAGE(F2:F1001)'
插入一列 .insert_cols(idx=数子编号)
插入多列 .insert_cols(idx=数子编号，amount=要插入的列数)
插入一行 .insert_rows(idx=数子编号)
插入多行 .insert_rows(idx=数子编号，amount=要插入的列数)
删除一个sheet workbook.remove(sheet实例)
创建新的sheet workbook.create_sheet(sheet名称)
冻结窗格 sheet.freeze_panes = "G2"
#向Excel表格中写
from openpyxl import load_workbook
from openpyxl.utils import FORMULAE#查看openpyxl支持的公式
from openpyxl import Workbook#创建新的Excel表格文件
 
workbook = load_workbook(filename='aaa.xlsx')
sheet = workbook.active
 
#一、
#向某个格子写入内容并保存
sheet['A1'] = '你好啊'
workbook.save(filename='aaa.xlsx')
 
#二、
#用Python列表数据插入一行
#sheet.append(Python列表) 接在表格内已有数据后面
data = [
    ['a', 1],
    ['b', 2],
    ['c', 3],
    ['d', 4],
]
for row in data:
    sheet.append(row)
workbook.save(filename='aaa.xlsx')
 
#三、
#插入公式
sheet['F1002'] = '=AVERAGE(F2:F1001)'
workbook.save(filename='阿里云天池电商婴儿用户数据.xlsx')
 
#四、
#查看openpyxl支持的公式
print(FORMULAE)
 
#五、
#插入一列 .insert_cols(idx=数子编号)
#插入多列 .insert_cols(idx=数子编号，amount=要插入的列数)
#插入一行 .insert_rows(idx=数子编号)
#插入多行 .insert_rows(idx=数子编号，amount=要插入的列数)
sheet.insert_cols(idx=2)
workbook.save(filename='阿里云天池电商婴儿用户数据.xlsx')
 
#六、
#删除列 .delete_cols(idx=数子编号，amount=要删除的列数)
#删除行 .delete_rows(idx=数子编号，amount=要删除的列数)
sheet.delete_cols(idx=2, amount=2)
workbook.save(filename='阿里云天池电商婴儿用户数据.xlsx')
 
#七、
#移动格子 .move_range("C2:D4",row=2,col=-2)向下移2行，向左移2行
#正数为向下或向右，负数为向左或向上
 
#八、
#创建新的sheet workbook.create_sheet(sheet名称)
print(workbook.sheetnames)
workbook.create_sheet('表格2')
print(workbook.sheetnames)
workbook.save(filename='aaa.xlsx')
 
#九、
#删除一个sheet workbook.remove(sheet实例)
print(workbook.sheetnames)
sheet = workbook['表格21']
workbook.remove(sheet)
print(workbook.sheetnames)
workbook.save(filename='aaa.xlsx')
 
#十、
#复制一个sheet workbook.copy_worksheet(sheet实例)
print(workbook.sheetnames)
workbook.create_sheet('表格1')
sheet = workbook['表格1']
workbook.copy_worksheet(sheet)
print(workbook.sheetnames)
workbook.save(filename='aaa.xlsx')
#十一、
#x修改表格名称 sheet.title=''
 
#十二、
#创建新的Excel表格文件
workbook = Workbook()
sheet = workbook.active
sheet.title = '表格1'
workbook.save(filename='这是一个新表格.xlsx')
 
 
#十三、
#冻结窗格 sheet.freeze_panes = "G2"
sheet.freeze_panes = "B2"
workbook.save(filename='aaa.xlsx')
 
#十四、
#添加筛选 sheet.auto_filter.ref = sheet.dimensions
sheet = workbook['(sample)sam_tianchi_mum_baby_tr']
sheet.auto_filter.ref = sheet.dimensions
workbook.save(filename='aaa.xlsx')
三、批量调整字体、样式
from openpyxl.styles import Alignment    #设置对其样式
from openpyxl.styles import Side,Border    #设置边框样式
from openpyxl.styles import PatternFill, GradientFill     #设置填充样式
Font(name=字体名称,size=字体大小,bold=是否加粗,italic=是否斜体,color=字体颜色)
获取表格中字体的样式 cell.font.属性
设置对其样式 Alignment(horizontal=水平对其模式,vertical=垂直对其模式,text_rotation=旋转角度,wrap_text=是否自动换行)
设置边框样式  Side(style=边线样式,color=边线颜色)
设置填充样式 PatternFill(fill_type=填充样式, fgColor=填充颜色)
设置行高和列宽 row_dimensions[行编号].height=行高
合并单元格
.merge_cells(待合并格子编号)
.merge_cells(start_row=起始行号,start_column=起始列号,end_row=结束行号,end_column=结束列号)
#批量调整字体、样式
 
from openpyxl.styles import Font
from openpyxl import load_workbook
from openpyxl.styles import Alignment#设置对其样式
from openpyxl.styles import Side,Border#设置边框样式
from openpyxl.styles import PatternFill, GradientFill#设置填充样式
 
workbook = load_workbook(filename='aaa.xlsx')
sheet = workbook.active
 
cell = sheet['A1']
#一、
#Font(name=字体名称,size=字体大小,bold=是否加粗,italic=是否斜体,color=字体颜色)
font = Font(name='思源黑体 Regular',size=12,bold=True, italic=True,color='FF0000')
cell.font = font
workbook.save(filename='aaa.xlsx')
 
#二、
#获取表格中字体的样式 cell.font.属性
font = cell.font
print(font.name, font.size, font.bold, font.italic)
 
 
#三、
#设置对其样式 Alignment(horizontal=水平对其模式,vertical=垂直对其模式,text_rotation=旋转角度,wrap_text=是否自动换行)
alignment = Alignment(horizontal='center',vertical='center',text_rotation=45)
cell.alignment = alignment
workbook.save(filename='aaa.xlsx')
 
#四、
#设置边框样式
#Side(style=边线样式,color=边线颜色)
#Border(left=左边线样式,right=右边线样式,top=上边线样式,bottom=下边线样式)
side = Side(style='thin',color='FF000000')
border = Border(left=side,right=side,top=side,bottom=side)
cell.border = border
workbook.save(filename='aaa.xlsx')
 
#五、
#设置填充样式
#PatternFill(fill_type=填充样式, fgColor=填充颜色)
#GradientFill(stop=(渐变颜色1,渐变颜色2，.....))
cell_a3 = sheet['A3']
patternFill = PatternFill(fill_type='solid', fgColor='99ccff')
cell_a3.fill = patternFill
cell_a4 = sheet['A4']
gradient_fill = GradientFill(stop=('FFFFFF','99ccff','000000'))
cell_a4.fill = gradient_fill
workbook.save(filename='aaa.xlsx')
 
#六、
#设置行高和列宽
#row_dimensions[行编号].height=行高
#column_dimensions[列编号].width=列宽
sheet.row_dimensions[1].height = 50
sheet.column_dimensions['B'].width = 20
workbook.save(filename='aaa.xlsx')
 
#七、
#合并单元格
#.merge_cells(待合并格子编号)
#.merge_cells(start_row=起始行号,start_column=起始列号,end_row=结束行号,end_column=结束列号)
sheet.merge_cells('C1:D2')
sheet.merge_cells(start_row=7,start_column=1,end_row=8,end_column=4)
workbook.save(filename='aaa.xlsx')
四、编程生成Excel内图表
from openpyxl.drawing.image import Image #openpyxl 插入图片
from openpyxl.chart import BarChart, Reference#插入柱状图 
from openpyxl.chart import LineChart, Reference#插入条形图 
openpyxl 插入图片 openpyxl.drawing.image sheet.add_image()
插入柱状图 BarChart() Reference()
#编程生成Excel内图表
from openpyxl import load_workbook
from openpyxl.drawing.image import Image #openpyxl 插入图片
from openpyxl.chart import BarChart, Reference#插入柱状图 
from openpyxl.chart import LineChart, Reference#插入条形图 
workbook = load_workbook(filename="阿里云天池电商婴儿用户数据.xlsx")
sheet = workbook.active
 
#一、
#openpyxl 插入图片 openpyxl.drawing.image sheet.add_image()
logo = Image("每颗豆.png")
logo.height = 100
logo.width = 100
 
sheet.add_image(logo, "C1")
workbook.save(filename="aaa.xlsx")
 
#二、
#插入柱状图 BarChart() Reference()
chart = BarChart()
data = Reference(worksheet=sheet,min_row=2,max_row=1001,min_col=5,max_col=6)#设置数据引用范围
categories = Reference(sheet, min_col=10, min_row=10, max_row=10)#设定X轴项目名称
 
chart.add_data(data, titles_from_data=True)#给柱状图添加数据 数据源中有标题
chart.set_categories(categories)
sheet.add_chart(chart, "H2")
workbook.save(filename="阿里云天池电商婴儿用户数据.xlsx")
 
#三、
#插入条形图 BarChart() Reference()
chart = LineChart()
data = Reference(worksheet=sheet,min_row=20,max_row=21,min_col=1,max_col=13)#设置数据引用范围
categories = Reference(sheet, min_col=19, min_row=2, max_row=13)#设定X轴项目名称
 
chart.add_data(data, from_rows=True, titles_from_data=True)#给条形图添加数据 数据源中有标题
chart.set_categories(categories)
sheet.add_chart(chart, "E2")
workbook.save(filename="阿里云天池电商婴儿用户数据.xlsx")