python之excel读写报表统计入门

1. 环境准备

• python3.0+,下载地址
• 环境变量配置,将Python安装的路径添加值系统环境变量的path中,如下图所示
• 检测环境变量,window使用快捷键win+r输入cmd然后输入python,环境配置正确如下显示

2. 依赖相关

随着python版本升级, 版本在2.7以上的,在安装Python的时候,已经自动安装好了pip.pip是下载相关依赖的引擎,每个人的理解不一样.相当于java中的mvn,也相当于node中的npm,可以使用pip install 模块名 的方式下载所需的依赖.

比如我们这里学习使用python自动化处理excel的时候,会需要引用模块openpyxl,API等操作文档. 按照开发思路,第一步是需要引用这个依赖的

2.1 依赖下载

1. 在cmd或者vscode编辑器的终端中 输入pip install openpyxl,在刚安装好Python环境去执行这个命令的时候,可能会出现两种结果.

• 报错提示需要升级pip版本,根node不同的地方是,如果pip不是最新版本将会无法拉去模块文件,所以我们需要去升级pip模块
• 如果pip是新版本的话,你拉去模块的时候进度条加载慢或者过程中出现Timeout 字样.这是因为模块文件比较大,默认拉去的是python官网地址的模块文件,所以我们需要中转站(镜像)

2.2 镜像使用

3. excel操作相关

3.1 读excel报表数据

废话不多说,直接上代码.关于openpyxl的操作文档,上文已经写了.遇到问题或者有新创意可以参考官方文档. 假设需要读取的excel如下

# 引入所需的依赖   
import openpyxl
# 读取excel文件,方便操作建议放在和python文件同目录,或者使用绝的路径的方式,需要注意盘符
wb = openpyxl.load_workbook('helloword.xlsx')
# 获取该文件下所有sheet页 ,获取的api有多种
names = wb.sheetnames
# wb.get_sheet_by_name(name) 已经废弃,使用wb[name] 获取指定工作表
sheet = wb[names[0]]
# 获取最大行数
maxRow = sheet.max_row
# 获取最大列数
maxColumn = sheet.max_column
# 获取当前活动表
current_sheet = wb.active
# 获取当前活动表名称
current_name = sheet.title
# 打印sheet数组
print(names)
---------------------------------------------------------------------------------
PS D:\python-workplace> & D:/python/python.exe d:/python-workplace/excel-demo1.py
['Sheet1', '测试', '活动页']
---------------------------------------------------------------------------------
# 获取指定的sheet页 对象
sh1 = wb['Sheet1']
# 读取指定单元格值 
cell_v = sh1['A1'].value
# 通过行列确定数据 
cell_RL = sh1.cell(row=1,column=2).value
print("读取指定单元格值: {}" .format(cell_v)
print("通过行列读取指定数据:{}".format(cell_RL))
---------------------------------------------------------------------------------
PS D:\python-workplace> & D:/python/python.exe d:/python-workplace/excel-demo1.py
读取指定单元格值: Pie
通过行列读取指定数据:sold
---------------------------------------------------------------------------------
# 获取指定类 和指定行的数据
col_range = ws['A:B']
row_range = ws['1:5']

#  按照列读(一列一列)
for col in col_range:
    for cell in col:
        print(cell.value)
        
---------------------------------------------------------------------------------
PS D:\python-workplace> & D:/python/python.exe d:/python-workplace/excel-demo1.py
Pie
apple
balana
Pumkin
orange
sold
50
30
10
10
---------------------------------------------------------------------------------

# 按照行读 (一行一行)
for row in row_range:
    for cell in row:
        print(cell.value)
---------------------------------------------------------------------------------
PS D:\python-workplace> & D:/python/python.exe d:/python-workplace/excel-demo1.py
Pie
sold
apple
50
balana
30
Pumkin
10
orange
10
---------------------------------------------------------------------------------

# 合并上面 
for row in ws.iter_rows(min_row=1,max_row=5,max_col=2):
    for cell in row:
        print(cell.value)
---------------------------------------------------------------------------------
PS D:\python-workplace> & D:/python/python.exe d:/python-workplace/excel-demo1.py
Pie
sold
apple
50
balana
30
Pumkin
10
orange
10
---------------------------------------------------------------------------------

3.2 写入数据到excel

import openpyxl
from openpyxl.utils import get_column_letter

""" wb = openpyxl.Workbook()
sheet = wb.active

print(sheet.title)

#  修改标题名称 
sheet.title ="修改sheet名称"
print(wb.get_sheet_names())
# 文件保存到磁盘 
#wb.save("excel_writer.xlsx")


# 按照指定顺序新建sheet页  
wb.create_sheet(index=0,title='First Sheet')
wb.create_sheet(index=1,title='Second Sheet')
print(wb.get_sheet_names()) """


# 往excel单元个写入数据 
wb = openpyxl.Workbook()
sheet = wb.active

# 指定单元列填充数值
sheet['A1'] = "hello python 2021"
print(sheet['A1'].value)
# 行数据填充  
ws = wb.create_sheet('range rows')
for i in range(1,39):
    ws.append(range(17))

ws1 = wb.create_sheet('List')
rows = [
    ['Number','Batch 1','Batch2'],
    [2,100,500],
    [3,200,11],
    [4,12,777],
    [5,22,666],
    [6,543,544],
]
for row in rows:
   ws1.append(row) 

ws2 = wb.create_sheet("Date")
# 按照行列的方式填充数值
for row in range(5,30):
    for col in range(10,45):
        ws2.cell(column=col,row=row,value=get_column_letter(col))

print(ws2['AA10'])
# 保存到磁盘
wb.save('excel_writer.xlsx')
# 读取sheet  
sts = wb.get_sheet_names()
print(sts)

sh1 = wb.get_sheet_by_name('Sheet')
print(sh1['A1'].value)

sh2 = wb.get_sheet_by_name('range rows')
print(sh2.rows)

3.3 excel字体,颜色,宽高,跨行跨列,简单图表等设置

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


# excel 操作相关的  字体 样式 大小 等等的修改 

wb = openpyxl.Workbook()

# font  相关  
ws = wb.active
ws.title='Font'
# 设置字体 24px  斜体
italic24Font = Font(size=24,italic=True)
ws['B3'].font = italic24Font
ws['B3'] = '24 px itailc '
# 设置字体 Times New Roman 加粗 颜色为红色
boldRedFont = Font(name='Times New Roman',bold=True,color=colors.COLOR_INDEX[2])
ws['A1'].font = boldRedFont
ws['A1'] = 'bold red font'

#  Formula  公式  求和等 
ws = wb.create_sheet('Formula')
ws['A1'] = 200
ws['A2'] =300
ws['A3'] ='=SUM(A1:A2)'

# 高宽  设置行列或者单元格的高宽
ws = wb.create_sheet('dimensions')
ws['A1'] =  'Tall row'
ws.row_dimensions[1].height = 70
ws['B2'] = 'Width column'
ws.column_dimensions['B'].Width = 20

# 单元格合并  
ws = wb.create_sheet('megred')
ws.merge_cells('A1:D3')
ws['A1'] = 'Twelve cells megred together'


# 拆分单元格 
ws = wb.copy_worksheet(wb['megred'])
ws.title ='unmegerd'
ws.unmerge_cells('A1:D3')


# 图标操作   绘制图标  
from openpyxl.chart import(
    AreaChart,
    Reference,
    Series,
    PieChart,
)
from openpyxl.chart.label import DataLabelList
from openpyxl.chart.text import RichText
from openpyxl.drawing.text import (
    Paragraph, 
    ParagraphProperties, 
    CharacterProperties
)
ws = wb.create_sheet('AreaChart')


data = [
    ['Pie', 'sold'],
    ['apple',50],
    ['balana',30],
    ['Pumkin',10],
    ['orange',10]
]


for row in data:
    ws.append(row)
# 创建折线图实例
pie = AreaChart()
# 设置标题
pie.title='pie sold chart'
# 统计图样式 参加官网
pie.style =13
# x周标题
pie.x_axis.title ='Test'
# y轴标题
pie.y_axis.title ='Percentage'

# 创建统计项
cats = Reference(ws,min_col=1,min_row=2,max_row=6)
# 添加数据
datas = Reference(ws,min_col=2,min_row=1,max_row=5)
# 插入数据并从表中读取标题
pie.add_data(datas,titles_from_data=True)
pie.set_categories(cats)
# 插入指定位置
ws.add_chart(pie,"A10")


p = PieChart()
p.title='水果销售统计'
p.add_data(datas,titles_from_data=True)
p.set_categories(cats)
p.style =10 
p.height =10
# 获取数据实例
s1 = p.series[0]
# 获取数据并设置百分比
s1.dLbls = DataLabelList()
s1.dLbls.showCatName = True     # 标签显示
s1.dLbls.showVal = False     # 数量显示
s1.dLbls.showPercent = True     # 百分比显示
axis = CharacterProperties(sz=1200)     # 图表中字体大小 *100
s1.dLbls.txPr = RichText(p=[Paragraph(pPr=ParagraphProperties(defRPr=axis), endParaRPr=axis)])
ws.add_chart(p,'J10')
wb.save('excel_style.xlsx')