python处理Excel基础

python处理Excel基础

1、openpyxl简介

（1）openpyxl简介

openpyxl模块是一个读写Excel 2010文档的Python库，如果要处理更早格式的Excel文档，需要用到额外的库，openpyxl是一个比较综合的工具，能够同时读取和修改Excel文档。其他很多的与Excel相关的项目基本只支持读或者写Excel一种功能。https://openpyxl.readthedocs.io/en/stable/

（2）安装openpyxl模块

openpyxl是一个开源项目，这里使用如下命令安装openpyxl模块

pip3 install openpyxl

 

2、Python打开及获取Excel表格内容

（1）Excel表格基本术语

列column；行row；单元格cell；表sheet    

 （2）打开Excel表格并获取表格名称

# 打开Excel表格并获取表格名称
from openpyxl import load_workbook

workbook = load_workbook('students.xlsx')
print(workbook.sheetnames)
sheet_x = workbook['xue']      #通过sheet名称获取指定表格
print(sheet_x.dimensions)      #获取表的尺寸大小

 

（3）当EXCEL表里只有一个sheet表时，获取一个单元格内容

# 当表中只有一个sheet时，读取某单元格的数据
from openpyxl import load_workbook
workbook = load_workbook('students.xlsx')
sheet = workbook.active
cell = sheet['B2']        #指定单元格
print(cell.value,cell.row,cell.column,cell.coordinate)
cell = sheet.cell(row=2,column=2)         #通过行号和列号来指定单元格
print(cell.value,cell.coordinate)

 

（3）当EXCEL表里只有一个sheet表时，获取一系列格子

# 当表中只有一个sheet时，获取一系列格子
from openpyxl import load_workbook

workbook = load_workbook('students.xlsx')
sheet = workbook.active
cells1 = sheet['A1:B2']
print(cells1)
cells2 = sheet['A']
print(cells2)
print(sheet['1:2'])

# 当表中只有一个sheet时，获取一系列格子
from openpyxl import load_workbook

for row in sheet.iter_rows(min_row=5,max_row=6,min_col=5,max_col=6):
    print(row)
    for cell in row:
        print(cell)

for col in sheet.iter_cols(min_row=5,max_row=6,min_col=5,max_col=6):
    print(col)
    for cell in col:
        print(cell)

（4）当EXCEL表里只有一个sheet表时，获取一系列格子

# 当表中只有一个sheet时，迭代获取所有行、列
from openpyxl import load_workbook

workbook = load_workbook('students.xlsx')
sheet = workbook.active
for row in sheet.rows:
    print(row)
for col in sheet.columns:
    print(col)

3、Python向Excel表格中写 

 （1）向某个单元格写入并保存

# 当表中只有一个sheet时，向某个单元格写入并保存
from openpyxl import load_workbook
workbook = load_workbook('students.xlsx')
sheet = workbook.active
sheet['a2']= '001'
workbook.save('students.xlsx')
cell = sheet['A6']
cell.value = '005'
workbook.save('students.xlsx')

 

（2）列表数据插入一行

# 插入一行数据
from openpyxl import load_workbook
workbook = load_workbook('students.xlsx')
sheet = workbook.active
data = [
    ['010','10','王甲'],
    ['011','11','王乙']
]
for row in data:
    sheet.append(row)
workbook.save('students.xlsx')

  

（3）插入公式

# 插入公式
from openpyxl import load_workbook
workbook = load_workbook('students.xlsx')
sheet = workbook.active
sheet['F18'] = '=AVERAGE(F2:F17)'
workbook.save('students.xlsx')

 

（4）插入一列（多列）

# 插入一列
from openpyxl import load_workbook
workbook = load_workbook('students.xlsx')
sheet = workbook.active
sheet.insert_cols(idx=2,amount=4)       #在第2列之前插入空列，数量4
workbook.save('students.xlsx')

 

（5）插入一行（多行）

# 插入一行（多行）
from openpyxl import load_workbook
workbook = load_workbook('students.xlsx')
sheet = workbook.active
sheet.insert_rows(idx=4,amount=4)       #在第4列之前插入空列，数量4
workbook.save('students.xlsx')

 （6）删除列，行

# 删除列、行
from openpyxl import load_workbook

workbook = load_workbook('students.xlsx')
sheet = workbook.active
sheet.delete_cols(idx=4,amount=2)       #在第4列之前删除列，数量2
workbook.save('students.xlsx')

sheet.delete_rows(idx=6,amount=2)       #在第6列之前删除行，数量2
workbook.save('students.xlsx')

 （7）移动格子

# 移动格子
from openpyxl import load_workbook

workbook = load_workbook('students.xlsx')
sheet = workbook.active
sheet.move_range("C2:D4",rows=2,cols=-2)       #指定移动格子的范围，移动方向（下、右为正）
workbook.save('students.xlsx')

 （8）创建新的sheet，删除sheet

# 创建新的sheet
from openpyxl import load_workbook

workbook = load_workbook('students.xlsx')
print(workbook.sheetnames)
workbook.create_sheet('表格2')
print(workbook.sheetnames)
workbook.save('students.xlsx')

sheet_sc = workbook['表格2']
workbook.remove(sheet_sc)
workbook.save('students.xlsx')
print(workbook.sheetnames)

 （9）修改sheet名称，创建新的Excel表格文件

#创建新的Excel表格文件

from openpyxl import Workbook

workbook = Workbook()
sheet = workbook.active
sheet.title = '表格1'
workbook.save('xinbiaoge.xlsx')

（10）冻结窗格

#冻结窗格
from openpyxl import load_workbook

workbook = load_workbook('students.xlsx')
sheet = workbook.active
sheet.freeze_panes = 'D6'
workbook.save('students.xlsx')

 

 （11）添加筛选

#添加筛选
from openpyxl import load_workbook

workbook = load_workbook('students.xlsx')
sheet = workbook['表格1']
sheet.auto_filter.ref = sheet.dimensions      #对整个表筛选
workbook.save('students.xlsx')

 

4.、批量调整字体、样式

（1）修改字体样式

#修改字体样式
from openpyxl.styles import Font
from openpyxl import load_workbook

workbook = load_workbook('students.xlsx')
sheet = workbook.active
cell = sheet['A1']
font = Font(name='黑体 Regular', size=12, bold=True,italic=True,color='FF0000')
cell.font = font
workbook.save('students.xlsx')

 （2）获取字体样式

#获取字体样式
from openpyxl.styles import Font
from openpyxl import load_workbook

workbook = load_workbook('studs.xlsx')
sheet = workbook.active
cell = sheet['A2']
font = cell.font
print(font)
print(font.name,font.size,font.bold,font.italic)

（3）设置对齐样式

#设置对齐样式
from openpyxl .styles import Alignment
from openpyxl import load_workbook

workbook = load_workbook('studs.xlsx')
sheet = workbook.active
cell = sheet['A4']
alignment = Alignment(horizontal='center',vertical='center',text_rotation='45')
cell.alignment = alignment
workbook.save('studs.xlsx')

 （4）设置边框样式

#设置边框样式
from openpyxl.styles import Side,Border
from openpyxl import load_workbook

workbook = load_workbook('studs.xlsx')
sheet = workbook.active
cell = sheet['A6']
side1 = Side(style='thin',color='FF0000')
side2 = Side(style='dotted',color='FFFF00')
border = Border(left=side1,right=side2,top=side1,bottom=side2)
cell.border = border
workbook.save("studs.xlsx")

 （5）设置填充样式

#设置填充样式
from openpyxl.styles import PatternFill,GradientFill
from openpyxl import load_workbook

workbook = load_workbook('studs.xlsx')
sheet = workbook.active
cell_a6 = sheet['A6']
pattern_fill = PatternFill(fill_type="solid",fgColor='99ccff')
cell_a6.fill = pattern_fill
cell_a8 = sheet['A8']
gradient_fill = GradientFill(stop=('FFFFFF','99CCFF','000000'))
cell_a8.fill = gradient_fill
workbook.save('studs.xlsx')

（6）设置行高、列宽

#设置行高、列宽
from openpyxl import load_workbook
import openpyxl

workbook = load_workbook('studs.xlsx')
sheet = workbook.active
sheet.row_dimensions[1].height = 50
sheet.column_dimensions['A'].width = 20
workbook.save('studs.xlsx')

 （7）合并单元格

#合并单元格
import openpyxl
from openpyxl import load_workbook

workbook = load_workbook('studs.xlsx')
sheet = workbook.active
sheet.merge_cells('A1:A2')
sheet.merge_cells(start_row=6,start_column=6,end_row=8,end_column=8)       #只保留左上第一个格子内容
workbook.save('studs.xlsx')

 （8）取消合并单元格

#取消合并单元格
from openpyxl import load_workbook
workbook = load_workbook(('studs.xlsx'))
sheet = workbook.active
sheet.unmerge_cells('A1:A2')
sheet.unmerge_cells(start_row=6,start_column=6,end_row=8,end_column=8)
workbook.save('studs.xlsx')

 5、生成Excel内图表

 （1）插入图片

#openpyxl插入图片
from openpyxl import load_workbook
from openpyxl.drawing.image import Image

workbook = load_workbook('studs.xlsx')
sheet = workbook.active

img_t1 = Image('tupian1.png')
img_t1.height = 100
img_t1.width = 100

sheet.add_image(img_t1,"A1")
workbook.save('studs.xlsx')

（2）图表

https://openpyxl.readthedocs.io/en/stable/charts/introduction.html