df.to_excel('test.xlsx',index=False)
from openpyxl import load_workbook
wb=load_workbook('test.xlsx')
ws=wb.active()
ws.merge_cells(start_row=2,end_row=8,start_column=1,end_column=1)
ws.merge_cells(start_row=2,end_row=8,start_column=2,end_column=2)
ws.merge_cells(start_row=9,end_row=14,start_column=1,end_column=1)
ws.merge_cells(start_row=9,end_row=14,start_column=2,end_column=2)
ws.merge_cells(start_row=15,end_row=18,start_column=1,end_column=1)
ws.merge_cells(start_row=15,end_row=18,start_column=2,end_column=2)
wb.save()

#-*- coding:utf-8 -*-
import tkinter as tk #使用Tkinter前需要先導(dǎo)入
from tkinter import filedialog,messagebox,ttk
from openpyxl import load_workbook
from openpyxl.styles import Alignment
import os
import pandas as pd
#建立窗口window
window = tk.Tk()
window.title('Excel合并單元格工具')
w_width=630
w_height=600
scn_width=window.maxsize()[0]
x_point=(scn_width-w_width)//2
window.geometry('%dx%d+%d+%d' %(w_width,w_height,x_point,100))
window.wm_attributes('-topmost',True)
window.tk_focusFollowsMouse()
window.bind("<Escape>",lambda event:window.iconify())

path_tar=tk.StringVar()
sheetvar=tk.StringVar()  #目標(biāo)工作表
#打開目標(biāo)文件
def getmergefile():
 file_path=filedialog.askopenfilename(title=u'選擇文件',filetype=[('Excel','.xlsx')])
 path_tar.set(file_path)
 alldata=pd.read_excel(file_path,None)
 ttk.Label(frame1,text="請選擇目標(biāo)工作表:").grid(row=1,column=0,sticky='w')
 global sheetvar
 chosen_sheet=ttk.Combobox(frame1,width=16,textvariable=sheetvar) 
 chosen_sheet['values']=list(alldata)
 chosen_sheet.grid(row=1,column=1,sticky='w')
 chosen_sheet.bind("<<ComboboxSelected>>",lambda event:getmergeseg(event,alldata,sheetvar.get()))
#勾選目標(biāo)字段
def getmergeseg(event,alldata,sheet):
 global frame2,segvars
 segvars=[] 
 try:
 frame2.destroy()
 except:
 pass
 frame2=tk.Frame(window,padx=15,pady=6)
 frame2.grid(row=1,column=0,sticky='w')
 ttk.Label(frame2,text="請勾選分組合并的目標(biāo)字段(第一個(gè)勾選框?yàn)榉纸M合并依據(jù)，必須事先進(jìn)行排序:").grid(row=0,column=0,columnspan=4,sticky='w')
 data=alldata[sheet]
 for index,item in enumerate(data.columns):
 segvars.append(tk.StringVar()) 
 ttk.Checkbutton(frame2,text=item,variable=segvars[-1],onvalue=item,offvalue='').grid(row=(index//4+1),column=index%4,sticky='w')
#合并字段單元格
def merging(file,sheet,segvars):
 selected=[i.get() for i in segvars if i.get()]
 df=pd.read_excel(file,sheet)
 wb=load_workbook(file)
 ws=wb[sheet]
 mergecells(ws,df,selected)
 try:
 wb.save(file)
 messagebox.showinfo('提示',file+'-'+sheet+'指定單元格合并完成')
 os.system('start '+os.path.dirname(file))
 except Exception as e:
 messagebox.showerror('警告',str(e))
#合并單元格函數(shù)
def mergecells(ws,df,cols):
 col=cols[0]
 gdic=df.groupby(col).groups 
 aligncenter=Alignment(horizontal='center',vertical='center')
 for gname in gdic:
 indexs=gdic[gname]+2
 indexs=indexs.sort_values()
 for col in cols: #每一個(gè)要合并的字段
  colindex=df.columns.tolist().index(col)+1
  ws.merge_cells(start_row=indexs[0],end_row=indexs[-1],start_column=colindex,end_column=colindex) #合并
  for i in range(1,ws.max_row+1): #實(shí)現(xiàn)居中
  ws.cell(row=i,column=colindex).alignment=aligncenter

def manual(): #使用說明
 info="""
 作用是合并單元格，把同樣內(nèi)容的單元格合并到一起，所以必須實(shí)現(xiàn)對目標(biāo)字段進(jìn)行排序，否則無法實(shí)現(xiàn)合并
 """
 messagebox.showinfo('提示',info)

frame1=tk.Frame(window,pady=6,padx=15)
frame1.grid(row=0,column=0,sticky='w')
ttk.Button(frame1,text="打開目標(biāo)文件",command=getmergefile).grid(row=0,column=0,sticky='w')
ttk.Entry(frame1,textvariable=path_tar,width=40).grid(row=0,column=1)
frame3=tk.Frame(window,pady=10,padx=15)
frame3.grid(row=2,column=0,sticky='w')
ttk.Button(frame3,text="點(diǎn)擊合并單元格",command=lambda:merging(path_tar.get(),sheetvar.get(),segvars)).grid(row=0,column=0,sticky='w')
ttk.Button(frame3,text="使用說明",command=manual).grid(row=0,column=1)

window.mainloop()