'python之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
---------------------------------------------------------------------------------

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)

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')