python处理excel表格入门_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 importload_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 importload_workbookfor row in sheet.iter_rows(min_row=5,max_row=6,min_col=5,max_col=6):print(row)for cell inrow:print(cell)for col in sheet.iter_cols(min_row=5,max_row=6,min_col=5,max_col=6):print(col)for cell incol:print(cell)

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

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

from openpyxl importload_workbook

workbook= load_workbook('students.xlsx')

sheet=workbook.activefor row insheet.rows:print(row)for col insheet.columns:print(col)

3、Python向Excel表格中写

(1)向某个单元格写入并保存

#当表中只有一个sheet时，向某个单元格写入并保存

from openpyxl importload_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 importload_workbook

workbook= load_workbook('students.xlsx')

sheet=workbook.active

data=[

['010','10','王甲'],

['011','11','王乙']

]for row indata:

sheet.append(row)

workbook.save('students.xlsx')

(3)插入公式

#插入公式

from openpyxl importload_workbook

workbook= load_workbook('students.xlsx')

sheet=workbook.active

sheet['F18'] = '=AVERAGE(F2:F17)'workbook.save('students.xlsx')

(4)插入一列(多列)

#插入一列

from openpyxl importload_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 importload_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 importload_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 importload_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 importWorkbook

workbook=Workbook()

sheet=workbook.active

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

(10)冻结窗格

#冻结窗格

from openpyxl importload_workbook

workbook= load_workbook('students.xlsx')

sheet=workbook.active

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

(11)添加筛选

#添加筛选

from openpyxl importload_workbook

workbook= load_workbook('students.xlsx')

sheet= workbook['表格1']

sheet.auto_filter.ref= sheet.dimensions #对整个表筛选

workbook.save('students.xlsx')

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

(1)修改字体样式

#修改字体样式

from openpyxl.styles importFontfrom openpyxl importload_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 importFontfrom openpyxl importload_workbook

workbook= load_workbook('studs.xlsx')

sheet=workbook.active

cell= sheet['A2']

font=cell.fontprint(font)print(font.name,font.size,font.bold,font.italic)

(3)设置对齐样式

#设置对齐样式

from openpyxl .styles importAlignmentfrom openpyxl importload_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 importSide,Borderfrom openpyxl importload_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 importPatternFill,GradientFillfrom openpyxl importload_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 importload_workbookimportopenpyxl

workbook= load_workbook('studs.xlsx')

sheet=workbook.active

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

(7)合并单元格

#合并单元格

importopenpyxlfrom openpyxl importload_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 importload_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 importload_workbookfrom openpyxl.drawing.image importImage

workbook= load_workbook('studs.xlsx')

sheet=workbook.active

img_t1= Image('tupian1.png')

img_t1.height= 100img_t1.width= 100sheet.add_image(img_t1,"A1")

workbook.save('studs.xlsx')

(2)图表