修改excel,openpyxl

from openpyxl import load_workbook
from openpyxl import Workbook
from openpyxl.chart import BarChart, Series, Reference, BarChart3D
from openpyxl.styles import Color, Font, Alignment
from openpyxl.styles.colors import BLUE, RED, GREEN, YELLOW
class Write_excel(object):
    
  def __init__(self,filename,sheet=0):
    self.filename = filename
    self.wb = load_workbook(self.filename)
    if isinstance(sheet,int):
        self.ws = self.wb.worksheets[sheet] # 按索引选择工作表
    elif isinstance(sheet,str):
        self.ws = self.wb[sheet] # 按表名选择工作表
#     self.ws = self.wb.active  #激活工作表 

  # 赋值单元格数据
  def write(self, coord, value):
    # eg: coord:(2,3)
    if isinstance(coord,tuple):
        self.ws.cell(*coord).value = value
    else:
    # eg: coord:A1
        self.ws[coord] = value
    self.wb.save(self.filename)
    
  # 合并单元格
  def merge(self, rangstring):
    # eg: rangstring:A1:E1
    self.ws.merge_cells(rangstring)
    self.wb.save(self.filename)
    
  # 设置单元格样式
  def cellstyle(self, coord, font, align):
    # eg: coord:(2,3)
    if isinstance(coord,tuple):
        cell = self.ws.cell(*coord)
    else:
    # eg: coord:A1
        cell = self.ws[coord]
    cell.font = font
    cell.alignment = align

# 使用上面的类
wr = Write_excel('t1.xlsx',sheet=0) 
wr.merge('A1:B3')  # 合并单元格
font = Font(name=u'宋体', size=14, color=RED, bold=True)  # 设置字体样式
align = Alignment(horizontal='center', vertical='center')  # 居中
wr.cellstyle('A1', font, align)  # 应该先设置样式,再写内容
wr.write("A1","朱一龙站子数据")

# 单独使用
from openpyxl import Workbook
wb = Workbook()#创建工作簿 
ws = wb.active#激活工作表 
ws1 = wb.create_sheet("Mysheet")#创建mysheet表 
ws.title = "New Title"#表明改为New Title 
ws.sheet_properties.tabColor = "1072BA"#表名的颜色 
ws['A4'] = 4#赋值 
d = ws.cell(row=4, column=2, value=10)#赋值 
cell_range = ws['A1':'C2']#选择单元格区域 
ws1["A1"] = "aaa"
wb.save('test.xlsx')

# 生成图表
# 详见http://openpyxl.readthedocs.io/en/stable/charts/bar.html
from openpyxl import Workbook
from openpyxl.chart import BarChart, Series, Reference
 
wb = Workbook(write_only=True)
ws = wb.create_sheet()
 
rows = [
    ('Number', 'Batch 1', 'Batch 2'),
    (2, 10, 30),
    (3, 40, 60),
    (4, 50, 70),
    (5, 20, 10),
    (6, 10, 40),
    (7, 50, 30),
]
 
for row in rows:
    ws.append(row)
 
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "Bar Chart"
chart1.y_axis.title = 'Test number'
chart1.x_axis.title = 'Sample length (mm)'
 
data = Reference(ws, min_col=2, min_row=1, max_row=7, max_col=3)  # 纵坐标的数据区
cats = Reference(ws, min_col=1, min_row=2, max_row=7) # 横坐标的数据区
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
ws.add_chart(chart1, "A10")
 
from copy import deepcopy
 
chart2 = deepcopy(chart1)
chart2.style = 11
chart2.type = "bar"
chart2.title = "Horizontal Bar Chart"
ws.add_chart(chart2, "G10")
 
chart3 = deepcopy(chart1)
chart3.type = "col"
chart3.style = 12
chart3.grouping = "stacked"
chart3.overlap = 100
chart3.title = 'Stacked Chart'
ws.add_chart(chart3, "A27")
 
chart4 = deepcopy(chart1)
chart4.type = "bar"
chart4.style = 13
chart4.grouping = "percentStacked"
chart4.overlap = 100
chart4.title = 'Percent Stacked Chart'
ws.add_chart(chart4, "G27")
 
wb.save("bar.xlsx")

# 从已有excel中读数据生成图表
from openpyxl import Workbook,load_workbook
from openpyxl.chart import BarChart, Series, Reference
 
wb = load_workbook("bar.xlsx")
ws = wb.worksheets[0]
 

 
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "测试图表"
chart1.y_axis.title = '数据'
chart1.x_axis.title = '姓名'
 
data = Reference(ws, min_col=2, min_row=1, max_row=7, max_col=4)  # 纵坐标的数据区,包括标题
cats = Reference(ws, min_col=1, min_row=2, max_row=7) # 横坐标的数据区,不包括标题
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
ws.add_chart(chart1, "A10")
 
 
wb.save("bar.xlsx")

image.png

from openpyxl import Workbook,load_workbook
from openpyxl.chart import BarChart, Series, Reference,PieChart
import pandas as pd

df = pd.read_excel("t1.xlsx",sheet_name=0,header=1,index_col="序号")

wb = load_workbook("t1.xlsx")
ws = wb.worksheets[0]


# 将 个人kol、个人KOL、kol统一成 个人kol
df["小分类"][df["小分类"].str.lower().str.contains("kol")] = "个人kol"


# 大分类画图
type1_dic = dict(df["大分类"].value_counts())

# 将个人kol放在最后行,且不在图表中显示
ws.cell(2,7).value = "类型"
ws.cell(2,8).value = "数量"

index = 3
for key,value in type1_dic.items():
    ws.cell(index,7).value = key
    ws.cell(index,8).value = type1_dic[key]
    index +=1



# 柱状图
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "站子大分类柱状图"
chart1.y_axis.title = '类型'
chart1.x_axis.title = '数量'
data = Reference(ws,  min_col=8, min_row=2,max_col=8, max_row=index-1)  # 纵坐标的数据区,包括标题
cats = Reference(ws, min_col=7, min_row=3, max_row=index-1) # 横坐标的数据区,不包括标题
chart1.add_data(data, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4
ws.add_chart(chart1, "K2")
 


# 饼图
pie = PieChart()
labels = Reference(ws, min_col=7, min_row=3, max_row=index-1)  # 横坐标的数据区,不包括标题(类别)
data =Reference(ws, min_col=8, min_row=2, max_row=index-1) # 纵坐标的数据区,包括标题(值)
pie.add_data(data, titles_from_data=True)
pie.set_categories(labels)
pie.title = "站子大分类饼图"
ws.add_chart(pie, "V2")



wb.save("t1.xlsx")

image.png