【Python】使用openpyxl简单处理表格

python处理表格，读取、遍历、写入数据，并自动生成图表

‘’’
注意：运行该脚本时
1、先创建需要被写入的.xlsx文件
2、运行时，被写入的表格为关闭状态，打开则报错，被读取的表格打开、关闭都可以
‘’’

# coding=gbk      
import openpyxl
from openpyxl.utils import column_index_from_string, get_column_letter
from openpyxl.chart import BarChart,Series,LineChart,Reference  
from openpyxl.styles import Font,colors,Alignment 


#打开excel
filename=r'G:\to.xlsx'		#被保存的文件目录	
filename1=r'G:\from.xlsx'	#想要处理的数据表格目录

wb_to=openpyxl.load_workbook(filename)
wb_from=openpyxl.load_workbook(filename1)

#打开sheet
ws_from=wb_from['sheet']

#创建sheet
ws1=wb_to.create_sheet('sheet2')
ws5=wb_to.create_sheet('sheet3',0)  #在首位置创建sheet3

#输入第一行
def ws_firstrow(a):
	a.append(['第一列','第二列','第三列'])

ws_firstrow(ws1)
ws_firstrow(ws2)

from_maxrow = ws_from.max_row      #被读取表格的max行
from_maxcol = ws_from.max_column   #被读取表格的max列
ws1_to_maxrow = ws1.max_row          #写入的表格的max行数
ws1_to_maxcol = ws1.max_column       #写入的表格的max例数

ws2_to_maxrow = ws2.max_row        
ws2_to_maxcol = ws2.max_column 

count_sheet1=0

#遍历、统计数据
for i in range (2, from_maxrow+1):
	count_sheet1 += 1

#写入函数	
def write_in_ws(ws_num,ws_maxr,i):
	ws_num.cell(ws_maxr+1,column_index_from_string('A')).value=ws_from.cell(i,column_index_from_string('A')).value
	#  column_index_from_string('A')等价于 1 ，第一列
	ws_num.cell(ws_maxr+1,column_index_from_string('B')).value= ws_from.cell(i,column_index_from_string('B')).value
	ws_num.cell(ws_maxr+1,column_index_from_string('C')).value= ws_from.cell(i,column_index_from_string('C')).value
	ws_maxr+=1

#调用写入函数，将数据写到ws1的sheet中
for i_index in range (2, from_maxrow+1):
	write_in_ws(ws1,ws1_to_maxrow,i_index)
	ws1_to_maxrow = ws1.max_row 
	

#-------图表---------
def generte_chart(a,b):
	chart = BarChart()         	#柱形图
	chart.type = "col"            #纵向排列
             
	chart.title = a
	chart.y_axis.title = '数量'
	chart.x_axis.title = '平台'
	
	data = Reference(b, min_col=10, max_col=12,min_row=1,max_row=2)		#数据区域
	cats = Reference(b, min_col=11, max_col=12, min_row=1)				#图表将显示的区域
	
	chart.add_data(data, titles_from_data=True)
	chart.set_categories(cats)
	return chart

ws1.add_chart(generte_chart("图表名称",ws1), "A3")

#-------样式---------
def ws_style(ws_num,ws_maxc,ws_max_row):	
	#字体
	first_row_font=Font(bold=True)
	for i in range(1,ws_maxc+1):
		ws_num.cell(row=1,column=i).fill = openpyxl.styles.PatternFill(fill_type='solid', fgColor="F8F8FF")
	#对齐
	ws_num.alignment = Alignment(horizontal='center', vertical='center',wrapText=True)	
	
	#行高
	ws_num.row_dimensions[1].height = 30
	for i in range(2,ws_max_row+1):
		ws_num.row_dimensions[i].height = 40
	
	#列宽
	def column_height(ws_num,column_num,per_height):
		ws_num.column_dimensions[column_num].width = per_height
		
	column_height(ws_num,'A',11)
	column_height(ws_num,'B',6)
	column_height(ws_num,'C',23.4)
	
ws_style(ws1,ws1_to_maxcol,ws1.max_row)
ws_style(ws2,ws2_to_maxcol,ws2.max_row)

			
wb_to.save(filename)