第一天的学习内容----Excel自动化处理

1.处理Excel文件的相关模块介绍及安装

处理Excel文件的相关模块介绍：openpyxl模块

可读取和写入文件

第三方模块，需要单独安装

处理Excel数据

处理excel样式

在表格内插入图片

安装方式：

（1）Windows用户：pip install openpyxl

（2）Mac用户：pip3 install openpyxl

测试：

Windows用户打开命令行输入：python

Mac用户打开终端输入python3

然后输入 import openpyxl

2.python打开及读取Excel表格内容

打开工作簿：load_workbook(路径+文件名）

获取工作表：workbook[sheet名称] workbook.active

获取表格尺寸：sheet.dimensions

import openpyxl
#在写表名的时候不要忘记.xlsx这个格式后缀
workbook=openpyxl.load_workbook('C:/Users/MEMENG/Desktop/办公自动化/chap3/京东鞋子评论信息.xlsx')
print(workbook.sheetnames)
sheet = workbook['评论信息'] #获取指定的表
#sheet = workbook.active #通常是只有一个sheet表的时候去使用
print(sheet.dimensions  #获取表的大小

cell=sheet['A1']
cells=sheet['A1:A5'] #结果以元组的形式返回
for cell in cells:
    print(cell[0].value)
    print('``````````````````````')

 打开和读取表格数据：

        获取表格内某个格子的数据：sheet['A1']  cell.value

        获取一系列格式：sheet['A1:A5'] ----得到的是一个元组， sheet['A']  sheet[5]

        指定行和列的范围，按行获取，按列获取：

        .iter_rows(min_row=最低行数，max_row=最高行数，min_col=最低列数，max_col=最高列

        数)

        .iter_cols(min_row=最低行数，max_row=最高行数，min_col=最低列数，max_col=最高列

        数)

import openpyxl
workbook=openpyxl.load_workbook('C:/Users/MEMENG/Desktop/办公自动化/chap3/京东鞋子评论信息.xlsx')
print(workbook.sheetnames)
sheet = workbook['评论信息']
rows=sheet.rows #获取所有行,行同理可得
for row in rows:
    for cell in row:
        print(cell)

import openpyxl
workbook=openpyxl.load_workbook('C:/Users/MEMENG/Desktop/办公自动化/chap3/京东鞋子评论信息.xlsx')
print(workbook.sheetnames)
sheet = workbook['评论信息']
rows=sheet.iter_rows(min_row=1,max_row=5,min_col=1,max_col=4)
for row in rows:
    for cell in row:
        print(cell.value)
    print('-----------------------------')
#按列读取自定义区域同理可得

#获取码数为41的鞋子的所在各自的位置
import openpyxl
workbook=openpyxl.load_workbook('C:/Users/MEMENG/Desktop/办公自动化/chap3/京东鞋子评论信息.xlsx')
print(workbook.sheetnames)
sheet = workbook['评论信息']
col=sheet['D']
for row in col:
    if row.value == '41':
        print('D'+ str(row.row)) #.row可以获取行号

 

3.python向Excel表格中写入内容

向某个格子写入内容：sheet['A1']='hello,python'

用某个格子写入内容：cell.value='hello,python'

使用python列表数据插入一行：sheet.append(python列表) 

插入公式：直接赋值公式字符串

import openpyxl
workbook=openpyxl.load_workbook('新表.xlsx')
sheet=workbook['Sheet1']
lst=['姓名','分数']
sheet.append(lst)
lst1=[['张一',90],['王二',98],['李三',77]]
for row in lst1:
    sheet.append(row)
sheet['B9']='=sum(B5:B8)'
workbook.save('新表.xlsx')

#用来查看python中支持使用的excel中的公式
from openpyxl.utils import FORMULAE
print(FORMULAE)

 插入一列： .insert_cols(idx=数字编号)

插入多列： .insert_cols(idx=数字编号，amount=要插入的列数)

插入一行： .insert_rows(idx=数字编号)

插入多行： .insert_rows(idx=数字编号，amount=要插入的行数)

import openpyxl
workbook=openpyxl.load_workbook('新表.xlsx')
sheet=workbook['Sheet1']
sheet.insert_cols(idx=2,amount=3)
workbook.save('新表.xlsx')

删除列：.delete_cols(idx=数字编号，amount=要插入的列数)

删除行：.delete_rows(idx=数字编号，amount=要插入的列数)

移动格子：.move_range('A3:B6',rows=2,cols=-2) #正整数为向下或向右，负整数为向左或向上

创建新的sheet: workbook.create_sheet(sheet名称)

删除一个sheet: workbook.remove(sheet实例)

复制一个sheet: workbook.copy_worksheet(sheet实例)

修改表格名称：sheet.title='mysheet'

冻结窗格：sheet.freeze_panes='D2'

添加筛选：sheet.auto_filter.ref=sheet.dimensions

4.使用python调整字体与样式

修改字体样式：Font(name=字体名称,size=字体大小,bolf=是加粗,italic=是否斜体,color=字体颜色)

获取表格中字体的样式：cell.font.属性

设置对齐样式：Alignment(horizontal=水平对齐方式,vertical=垂直对齐方式,text_rotation=旋转角度,wrap_text=是否自动换行)

import openpyxl
from openpyxl.styles import Font
from openpyxl.styles import PatternFill
from openpyxl.styles import GradientFill
workbook=openpyxl.load_workbook('新表.xlsx')
sheet=workbook['Sheet1']
cell=sheet['A1']
cell_c5=sheet['C5']
cell_c6=sheet['C6']
font=Font(name='微软雅黑',size=20,bold=True,color='ff0000')
cell.font=font
side=Side(style='thin',color='ff0000') #设置线条的形状与颜色
border=Border(left=side,top=side,right=side,bottom=side)
cell.border=border
pattern_fill=PatternFill(fill_type='solid',fgColor='ffff00')
cell_c5.fill=pattern_fill
gradient_fill=GradientFill(stop('ff00ff','00ff00'))
cell_c6.fill=gradient_fill
workbook.save('新表.xlsx')

Side(style=边线样式,color=边线颜色)

Border(left=左边线样式,right=右边线样式,top=上边线样式,bottom=下边线样式)

设置填充样式:

PatternFill(fill_type=填充样式,fgColor=填充颜色)-----这只能是纯色的填充

GradientFill(stop=(渐变颜色1,渐变颜色2,...))

设置行高及行宽：.row_dimensions[行编号].height=行高

                             .column_dimensions[列编号].width=列宽

合并单元格：.merge_cells(’D1:G3‘)

                      .merge_cells(start_row=起始行号,start_column=起始列号,end_row=结束行

                      号,end_column=结束列号)

取消合并单元格：.unmerge_cells(’D1:G3‘)

                             .unmerge_cells(start_row=起始行号,start_column=起始列号,end_row=结束行

                             号,end_column=结束列号)

5.编程生成Excel内图表

插入图片： openpyxl.drawing.image

                   sheet.add_image()

import openpyxl
from openpyxl.drawing.image import Image
workbook=openpyxl.load_workbook('新表.xlsx')
sheet=workbook.creat_sheet('imagesheet')
#创建图片对象
logo=Image('logo.png') 
logo.height=100 #设置logo的高度
logo.width=100 #设置logo的宽度
#右键图片文件，点击属性可看到图片的正常高宽数据，从而使得变形的图片回正
sheet.add_image(logo,'A1')
workbook.save('新表.xlsx')

插入柱状图：BarChart()      Reference()

import openpyxl
from openpyxl.chart import BarChart,Reference
workbook=openpyxl.load_workbook('新表.xlsx')
sheet=workbook['课时统计']
#创建柱状图的图表对象
chart=BarChart()
#数据的引用范围
data=Reference(worksheet=sheet,min_row=1,max_row=13,min_col=1,max_col=2)
#类别的引用范围
categories=Reference(sheet,min_row=2,max_row=13,min_col=1)
#将数据与类别添加到图表当中
chart.add_data(data,titles_from_data=True)
chart.set_categories(categories)
#将图表插入到工作表当中
sheet.add_chart(chart,'F12')
workbook.save('新表.xlsx')

条形图：LineChart() Reference()